图书信息管理系统设计实验报告

1、图书信息管理系统实验报告 目 录 TOC o 1-3 h z u HYPERLINK l _Toc186256526 1实验1实验环境搭建 PAGEREF _Toc186256526 h 3 HYPERLINK l _Toc186256527 实验环境概述 PAGEREF _Toc186256527 h 3 HYPERLINK l _Toc186256528 ORACLE 10G安装 PAGEREF _Toc186256528 h 3 HYPERLINK l _Toc186256529 表空间创立 PAGEREF _Toc186256529 h 4 HYPERLINK l _Toc186256

2、530 数据库用户创立 PAGEREF _Toc186256530 h 4 HYPERLINK l _Toc186256531 系统实体表结构 PAGEREF _Toc186256531 h 5 HYPERLINK l _Toc186256532 2实验2基表操作 PAGEREF _Toc186256532 h 8 HYPERLINK l _Toc186256533 基表关系图 PAGEREF _Toc186256533 h 8 HYPERLINK l _Toc186256534 实体表数据操作 PAGEREF _Toc186256534 h 9 HYPERLINK l _Toc1862565

3、35 使用SQL*LOADER插入原始数据 PAGEREF _Toc186256535 h 9 HYPERLINK l _Toc186256536 使用SQL*PLUS插入原始数据 PAGEREF _Toc186256536 h 10 HYPERLINK l _Toc186256537 数据查询 PAGEREF _Toc186256537 h 11 HYPERLINK l _Toc186256538 数据修改和删除 PAGEREF _Toc186256538 h 11 HYPERLINK l _Toc186256539 3实验3SQL PLUS学习 PAGEREF _Toc186256539

4、h 12 HYPERLINK l _Toc186256540 执行SQL脚本文件 PAGEREF _Toc186256540 h 12 HYPERLINK l _Toc186256541 显示错误信息 PAGEREF _Toc186256541 h 12 HYPERLINK l _Toc186256542 显示表结构 PAGEREF _Toc186256542 h 12 HYPERLINK l _Toc186256543 改变缺省的列标题 PAGEREF _Toc186256543 h 13 HYPERLINK l _Toc186256544 改变列的显示长度 PAGEREF _Toc1862

5、56544 h 13 HYPERLINK l _Toc186256545 设置每页行数 PAGEREF _Toc186256545 h 13 HYPERLINK l _Toc186256546 显示每个SQL语句花费的执行时间 PAGEREF _Toc186256546 h 14 HYPERLINK l _Toc186256547 显示SQL buffer中的SQL语句 PAGEREF _Toc186256547 h 14 HYPERLINK l _Toc186256548 编辑执行SQL buffer中的SQL语句 PAGEREF _Toc186256548 h 15 HYPERLINK l

6、 _Toc186256549 4实验4创立基表 PAGEREF _Toc186256549 h 16 HYPERLINK l _Toc186256550 基表创立脚本 PAGEREF _Toc186256550 h 16 HYPERLINK l _Toc186256551 图书信息表 BOOK_INFO实体表 PAGEREF _Toc186256551 h 16 HYPERLINK l _Toc186256552 作者信息表 AUTHOR_INFO实体表 PAGEREF _Toc186256552 h 16 HYPERLINK l _Toc186256553 图书作者关系表 BOOK_AUTH

7、OR PAGEREF _Toc186256553 h 17 HYPERLINK l _Toc186256554 出版社信息表 PUBLISHER_INFO PAGEREF _Toc186256554 h 18 HYPERLINK l _Toc186256555 图书出版社关系表 BOOK_PUBLISHER PAGEREF _Toc186256555 h 18 HYPERLINK l _Toc186256556 出版社分类参数信息表 PUBLISHER_CLASS_INFO实体表 PAGEREF _Toc186256556 h 19 HYPERLINK l _Toc186256557 出版社分

8、类关系表 PUBLISHER_CLASS PAGEREF _Toc186256557 h 19 HYPERLINK l _Toc186256558 图书分类参数信息表 BOOK_CLASS_INFO实体表 PAGEREF _Toc186256558 h 20 HYPERLINK l _Toc186256559 图书分类关系表 BOOK_CLASS PAGEREF _Toc186256559 h 21 HYPERLINK l _Toc186256560 角色信息表 ROLE_INFO PAGEREF _Toc186256560 h 21 HYPERLINK l _Toc186256561 用户信

9、息表 USER_INFO实体表，依赖角色表 PAGEREF _Toc186256561 h 22 HYPERLINK l _Toc186256562 用户借书关系表 USER_BOOK PAGEREF _Toc186256562 h 23 HYPERLINK l _Toc186256563 预定关系表 USER_RESERVE PAGEREF _Toc186256563 h 23 HYPERLINK l _Toc186256564 书本评论表 BOOK_COMMENT PAGEREF _Toc186256564 h 24 HYPERLINK l _Toc186256565 5实验5PL/SQL

10、根底 PAGEREF _Toc186256565 h 26 HYPERLINK l _Toc186256566 按出版社查询购书金额的平均值 PAGEREF _Toc186256566 h 26 HYPERLINK l _Toc186256567 按出版社查询购书金额的最高及最低购书金额 PAGEREF _Toc186256567 h 27 HYPERLINK l _Toc186256568 查询出版社购书金额超过平均值的出版社数 PAGEREF _Toc186256568 h 28 HYPERLINK l _Toc186256569 查询购书金额高于1万元的出版社数 PAGEREF _Toc

11、186256569 h 29 HYPERLINK l _Toc186256570 查询本月借出书的数量 PAGEREF _Toc186256570 h 29 HYPERLINK l _Toc186256571 6实验6基表数据操作 PAGEREF _Toc186256571 h 31 HYPERLINK l _Toc186256572 在TOAD中插入记录 PAGEREF _Toc186256572 h 31 HYPERLINK l _Toc186256573 SQL PLUS在基表中插入记录 PAGEREF _Toc186256573 h 32 HYPERLINK l _Toc1862565

12、74 一年内无借阅记录的图书在备注栏中注明 PAGEREF _Toc186256574 h 33 HYPERLINK l _Toc186256575 删除图书借阅信息中超过两年且已归还的记录 PAGEREF _Toc186256575 h 33 HYPERLINK l _Toc186256576 7实验7索引和完整性 PAGEREF _Toc186256576 h 34 HYPERLINK l _Toc186256577 建立出版社名称不能重名的索引 PAGEREF _Toc186256577 h 34 HYPERLINK l _Toc186256578 建立图书借阅信息中书号加借阅日期加借书

13、卡号的索引 PAGEREF _Toc186256578 h 34 HYPERLINK l _Toc186256579 完善域完整性、实体完整性和参照完整性 PAGEREF _Toc186256579 h 35 HYPERLINK l _Toc186256580 8实验8数据库的查询和视图 PAGEREF _Toc186256580 h 36 HYPERLINK l _Toc186256581 建立查询数据视图 PAGEREF _Toc186256581 h 36 HYPERLINK l _Toc186256582 实现对各基表及多表的数据查询 PAGEREF _Toc186256582 h 3

14、6 HYPERLINK l _Toc186256583 9实验9PL/SQL编程 PAGEREF _Toc186256583 h 38 HYPERLINK l _Toc186256584 统计本月图书借出总数量、总金额 PAGEREF _Toc186256584 h 38 HYPERLINK l _Toc186256585 使用循环结构求和 PAGEREF _Toc186256585 h 39 HYPERLINK l _Toc186256586 使用CASE语句查图书的所属种类 PAGEREF _Toc186256586 h 40 HYPERLINK l _Toc186256587 使用游标统

15、计当前未归还图书的总金额 PAGEREF _Toc186256587 h 41 HYPERLINK l _Toc186256588 10实验10存储过程与触发器 PAGEREF _Toc186256588 h 42 HYPERLINK l _Toc186256589 存储过程 PAGEREF _Toc186256589 h 42 HYPERLINK l _Toc186256590 触发器 PAGEREF _Toc186256590 h 44 HYPERLINK l _Toc186256591 BOOK_INFO删除操作审计 PAGEREF _Toc186256591 h 44 HYPERLIN

16、K l _Toc186256592 自动更新出版社购书数量触发器 PAGEREF _Toc186256592 h 44 HYPERLINK l _Toc186256593 11实验11函数与包 PAGEREF _Toc186256593 h 47 HYPERLINK l _Toc186256594 函数 PAGEREF _Toc186256594 h 47 HYPERLINK l _Toc186256595 借出图书数 PAGEREF _Toc186256595 h 47 HYPERLINK l _Toc186256596 归还图书数 PAGEREF _Toc186256596 h 48 HY

17、PERLINK l _Toc186256597 包 PAGEREF _Toc186256597 h 49 HYPERLINK l _Toc186256598 12实验12用户、角色和概要文件 PAGEREF _Toc186256598 h 53 HYPERLINK l _Toc186256599 用户 PAGEREF _Toc186256599 h 53 HYPERLINK l _Toc186256600 角色 PAGEREF _Toc186256600 h 54 HYPERLINK l _Toc186256601 概要文件 PAGEREF _Toc186256601 h 55 HYPERLI

18、NK l _Toc186256602 13实验13系统数据维护 PAGEREF _Toc186256602 h 57 HYPERLINK l _Toc186256603 逻辑备份 PAGEREF _Toc186256603 h 58 HYPERLINK l _Toc186256604 逻辑导出 PAGEREF _Toc186256604 h 58 HYPERLINK l _Toc186256605 逻辑导入 PAGEREF _Toc186256605 h 59实验1实验环境搭建实验环境概述操作系统：Windows XP SP2数 据 库： Oracle 10g 企业版数据库管理工具：Toad

19、for Oracle Version 9.0.1数据库客户端：Oracle 10g ClientORACLE 10G安装Oracle 10g对软硬件环境要求：内存最小需要：512MB系统交换区SWAP最小需要：1GB 或2倍内存容量的空间，如果内存大于等于2GB那么SWAP在1倍或2倍内 存容量之间。/tmp最小需要：400MB磁盘空间最小需要：3.7GB 其中2.5GB用于oracle software files ，1 .2GB用于database files。系统结构必须是：64位表空间创立创立的表空间名为BOOK_DATA，由于本数据库规模不大，故无需单独建立索引表空间BOOK_IND

20、EX。create tablespace book_data datafile D:oracleproduct10.1.0oradataorclbook_data.dbf size 200m autoextend on next 10m maxsize 2000mextent management local uniform ;数据库用户创立首先用SYS用户登录数据库BOOK，然后添加本系统得管理员用户BOOK，并给其赋予DBA权限。同时，要建立供其他人员使用的用户，如TEACHER和STUDENT这些用户只有修改和查询的权限，其它用户的创立及权限详见实验12。create user book

21、 identified by book default tablespace book_datatemporary tablespace temp;grant dba to book;关联用户和表空间alter user bookidentified by bookdefault tablespace book_datatemporary tablespace temp;系统实体表结构图书信息表 BOOK_INFO字段名注释类型约束备注BOOK_ID书本编号NUMBER(10)非空，主键BOOK_NAME书本名VARCHAR2(20 CHAR)BOOK_PUBDATE出版日期DATEBOOK_

22、PRICE书本价格NUMBER(10,2)BOOK_QUANTITY库存数量NUMBER(10)BOOK_INFO书本简介VARCHAR2(100 CHAR)BOOK_RESNUMBER预订数量NUMBER(6)BOOK_REMARK备注VARCHAR2(100 CHAR)作者信息表 AUTHOR_INFO字段名注释类型约束备注AUTHOR_ID作者编号NUMBER(10)非空，主键AUTHOR_NAME作者名VARCHAR2(20 CHAR)AUTHOR_INFO作者简介VARCHAR2(100 CHAR)图书作者关系表 BOOK_AUTHOR字段名注释类型约束备注BOOK_ID书本编号NU

23、MBER(10)非空，外键AUTHOR_ID作者编号NUMBER(10)非空，外键出版社信息表 PUBLISHER_INFO字段名注释类型约束备注PUBLISHER_ID出版社编号NUMBER(10)非空，主键PUBLISHER_NAME出版社名字VARCHAR2(20 CHAR)非空PUBLISHER_ADD出版社地址VARCHAR2(100 CHAR)PUBLISHER_BOOK_NUMBER出版社图书数量NUMBER(10)图书出版社关系表 BOOK_PUBLISHER字段名注释类型约束备注BOOK_ID书本编号NUMBER(10)非空，外键PUBLISHER_ID出版社编号NUMBER

24、(10)非空，外键出版社分类参数信息表 PUBLISHER_CLASS_INFO字段名注释类型约束备注PUBLISHER_CLASS_ID出版社分类编号NUMBER(10)非空，主键PUBLISHER_CLASS_NAME出版社分类名字VARCHAR2(20 CHAR)出版社分类关系表 PUBLISHER_CLASS字段名注释类型约束备注PUBLISHER_ID出版社编号NUMBER(10)非空，外键PUBLISHER_CLASS_ID出版社分类编号NUMBER(10)非空，外键图书分类参数信息表 BOOK_CLASS_INFO字段名注释类型约束备注BOOK_CLASS_ID书本分类编号NUM

25、BER(10)非空，主键BOOK_CLASS_NAME书本分类名称VARCHAR2(20CHAR)非空图书分类关系表 BOOK_CLASS字段名注释类型约束备注BOOK_ID书本编号NUMBER(10)非空，外键BOOK_CLASS_ID书本分类编号NUMBER(10)非空，外键角色信息表 ROLE_INFO字段名注释类型约束备注ROLE_ID角色编号NUMBER(10)非空，主键ROLE_NAME角色名称VARCHAR2(10 CHAR)非空MAX_BORROW_COUNT最大借书数量NUMBER(2)非空ADMIN是否管理员NUMBER(1)非空用户信息表 USER_INFO字段名注释类型

26、约束备注USER_ID用户编号NUMBER(10非空，主键USER_NAME登入账号VARCHAR2(15 CHAR)非空USER_REALNAME真实姓名VARCHAR2(20 CHAR)非空USER_PASSWORD登入密码VARCHAR2(15 CHAR)非空USER_SEX性别NUMBER(1)男0女1USER_EMAIL邮箱VARCHAR2(20 CHAR)USER_TELEPHONE NUMBER(11)ROLE_ID角色编号NUMBER(5)非空，外键USER_BORROW_COUNT已经借书数量NUMBER(2)用户借书关系表 USER_BOOK字段名注释类型约束备注USER_

27、ID用户编号NUMBER(10)非空，外键BOOK_ID书本编号NUMBER(10)非空，外键BORROW_DATE借书数量DATERETURN_DATE归还日期DATE预定关系表 USER_RESERVE字段名注释类型约束备注USER_ID用户编号NUMBER(10)非空，外键BOOK_ID书本编号NUMBER(10)非空，外键RESERVE_DATE预约日期DATE非空书本评论表 BOOK_COMMENT字段名注释类型约束备注COMMENT_ID评论编号NUMBER(12)非空，主键COMMENT_TEXT评论内容VARCHAR2(200 CHAR)COMMENT_DATE评论日期DATE

28、BOOK_ID书本编号NUMBER(10)非空，外键USER_ID用户编号NUMBER(10)非空，外键实验2基表操作基表关系图实体表数据操作使用SQL*LOADER插入原始数据下面以书本信息表为例：整理Excel数据原始文件，“另存为“文本文件制表符分隔，取名为book10.txt 。 编辑一个和Excel 数据相对应的表。编辑控制文件，控制文件取名为： 其中格式如下： Load datainfile d:book10.txtAppend into table BOOK_INFOfields terminated by X09(book_id,book_name,book_pubdate D

29、ate yyyy-mm-dd ,book_price,book_quantity,book_info,book_resnumber)调用SQL*LOADER，加载数据S 添加数据结果：使用SQL*PLUS插入原始数据其中图书信息表数据较多，采用SQL LOAD方法导入数据，其他表采用SQL语句插入数据，以图书种类关系表为例：添加图书种类关系信息insert into book_classselect book_id,10 from book_infowhere book_id like 10%;insert into book_classselect book_id,20 from book_

30、infowhere book_id like 20%;insert into book_classselect book_id,30 from book_infowhere book_id like 30%;insert into book_classselect book_id,40 from book_infowhere book_id like 40%;commit;数据查询查询书本信息表中编号为10开头的全部书籍：Select * from book_info where book_id like 10%;数据修改和删除 删除用户借阅表里编号是“3006用户的所有借阅情况。图中显示已经

31、删除了6条信息。实验3SQL PLUS学习执行SQL脚本文件SQL conn book/bookConnected.SQL alter user book 2 identified by book 3 default tablespace book_data 4 identified by bookSQL create tablespace book_data 2 datafile D:oracleproduct10.1.0oradataorclbook_data.dbf size 200m 3 autoextend on next 10m maxsize 2000m 4 extent man

32、agement local uniform ;create tablespace book_data显示错误信息ERROR at line 1:ORA-01119: error in creating database fileD:oracleproduct10.1.0oradataorclbook_data.dbfORA-27038: created file already existsOSD-04010: option specified, file already exists 显示表结构显示书本信息表的结构：desc book_info改变缺省的列标题修改书本信息表的列名name为“

33、书本名Select book_name “书本名 from book_info Where book_id between 100001 and 100005；改变列的显示长度设置一行可以容纳的字符数SQLSETLINESIZE80|n如果一行的输出内容大于设置的一行可容纳的字符数，那么折行显示SQL set lin 30设置每页行数设置一页有多少行数SQLSETPAGESIZE24|nSQL set pagesize 6显示每个SQL语句花费的执行时间显示每个sql语句花费的执行时间setTIMINGON|OFFSQL set timing on显示SQL buffer中的SQL语句显示sq

34、l buffer中的sql语句，list n显示sql buffer中的第n行，并使第n行成为当前行 LIST n 编辑执行SQL buffer中的SQL语句编辑sql buffer中的sql语句 EDIT 显示sqlbuffer中的sql语句，listn显示sqlbuffer中的第n行，并使第n行成为当前行LISTn将sqlbuffer中的sql语句保存到一个文件中SAVEfile_name将一个文件中的sql语句导入到sqlbuffer中GETfile_name再次执行刚刚已经执行的sql语句RUNor /实验4创立基表基表创立脚本图书信息表 BOOK_INFO实体表CREATE TABL

35、E BOOK_INFO( BOOK_ID NUMBER(10) NOT NULL, BOOK_NAME VARCHAR2(20 CHAR) NOT NULL, BOOK_PUBDATE DATE, BOOK_PRICE NUMBER(10,2), BOOK_QUANTITY NUMBER(10), BOOK_INFO VARCHAR2(100 CHAR), BOOK_RESNUMBER NUMBER(6)TABLESPACE BOOK_DATA;CREATE UNIQUE INDEX BOOK_INFO_PK ON BOOK_INFO(BOOK_ID)LOGGINGTABLESPACE BOO

36、K_DATA;ALTER TABLE BOOK_INFO ADD ( CONSTRAINT BOOK_INFO_PK PRIMARY KEY (BOOK_ID) USING INDEX TABLESPACE BOOK_DATA);作者信息表 AUTHOR_INFO实体表CREATE TABLE AUTHOR_INFO( AUTHOR_ID NUMBER(10) NOT NULL, AUTHOR_NAME VARCHAR2(20 CHAR), AUTHOR_INFO VARCHAR2(100 CHAR)TABLESPACE BOOK_DATA;CREATE UNIQUE INDEX AUTHOR

37、_INFO_PK ON AUTHOR_INFO(AUTHOR_ID)LOGGINGTABLESPACE BOOK_DATA;ALTER TABLE AUTHOR_INFO ADD ( CONSTRAINT AUTHOR_INFO_PK PRIMARY KEY (AUTHOR_ID) USING INDEX TABLESPACE BOOK_DATA); 图书作者关系表 BOOK_AUTHORCREATE TABLE BOOK_AUTHOR( BOOK_ID NUMBER(10) NOT NULL, AUTHOR_ID NUMBER(10) NOT NULL)TABLESPACE BOOK_DAT

38、A;ALTER TABLE BOOK_AUTHOR ADD ( PRIMARY KEY (BOOK_ID, AUTHOR_ID) USING INDEX TABLESPACE BOOK_DATA);ALTER TABLE BOOK_AUTHOR ADD ( FOREIGN KEY (BOOK_ID) REFERENCES BOOK_INFO (BOOK_ID);ALTER TABLE BOOK_AUTHOR ADD ( CONSTRAINT BOOK_AUTHOR_R01 FOREIGN KEY (AUTHOR_ID) REFERENCES AUTHOR_INFO (AUTHOR_ID);出版

39、社信息表 PUBLISHER_INFO CREATE TABLE PUBLISHER_INFO( PUBLISHER_ID NUMBER(10) NOT NULL, PUBLISHER_NAME VARCHAR2(20 CHAR) NOT NULL, PUBLISHER_ADD VARCHAR2(100 CHAR)TABLESPACE BOOK_DATA;CREATE UNIQUE INDEX PUBLISHER_INFO_PK ON PUBLISHER_INFO(PUBLISHER_ID)LOGGINGTABLESPACE BOOK_DATA;ALTER TABLE PUBLISHER_IN

40、FO ADD ( CONSTRAINT PUBLISHER_INFO_PK PRIMARY KEY (PUBLISHER_ID) USING INDEX TABLESPACE BOOK_DATA); 图书出版社关系表 BOOK_PUBLISHERCREATE TABLE BOOK_PUBLISHER( BOOK_ID NUMBER(10) NOT NULL, PUBLISHER_ID NUMBER(10) NOT NULL)TABLESPACE BOOK_DATA;CREATE UNIQUE INDEX BOOK_PUBLISHER_PK ON BOOK_PUBLISHER(BOOK_ID,

41、PUBLISHER_ID)LOGGINGTABLESPACE BOOK_DATA;ALTER TABLE BOOK_PUBLISHER ADD ( CONSTRAINT BOOK_PUBLISHER_PK PRIMARY KEY (BOOK_ID, PUBLISHER_ID) USING INDEX TABLESPACE BOOK_DATA);ALTER TABLE BOOK_PUBLISHER ADD ( CONSTRAINT BOOK_PUBLISHER_R01 FOREIGN KEY (BOOK_ID) REFERENCES BOOK_INFO (BOOK_ID);ALTER TABLE

42、 BOOK_PUBLISHER ADD ( CONSTRAINT BOOK_PUBLISHER_R02 FOREIGN KEY (PUBLISHER_ID) REFERENCES PUBLISHER_INFO (PUBLISHER_ID); 出版社分类参数信息表 PUBLISHER_CLASS_INFO实体表 CREATE TABLE PUBLISHER_CLASS_INFO( PUBLISHER_CLASS_ID NUMBER(10) NOT NULL, PUBLISHER_CLASS_NAME VARCHAR2(20 CHAR) NOT NULL)TABLESPACE BOOK_DATA;

43、CREATE UNIQUE INDEX PUBLISHER_CLASS_INFO_PK ON PUBLISHER_CLASS_INFO(PUBLISHER_CLASS_ID)LOGGINGTABLESPACE BOOK_DATA;ALTER TABLE PUBLISHER_CLASS_INFO ADD ( CONSTRAINT PUBLISHER_CLASS_INFO_PK PRIMARY KEY (PUBLISHER_CLASS_ID) USING INDEX TABLESPACE BOOK_DATA); 出版社分类关系表 PUBLISHER_CLASSCREATE TABLE PUBLIS

44、HER_CLASS( PUBLISHER_ID NUMBER(10) NOT NULL, PUBLISHER_CLASS_ID NUMBER(10) NOT NULL)TABLESPACE BOOK_DATA;CREATE UNIQUE INDEX PUBLISHER_CLASS_PK ON PUBLISHER_CLASS(PUBLISHER_ID, PUBLISHER_CLASS_ID)LOGGINGTABLESPACE BOOK_DATA;ALTER TABLE PUBLISHER_CLASS ADD ( CONSTRAINT PUBLISHER_CLASS_PK PRIMARY KEY

45、(PUBLISHER_ID, PUBLISHER_CLASS_ID) USING INDEX TABLESPACE BOOK_DATA);ALTER TABLE PUBLISHER_CLASS ADD ( CONSTRAINT PUBLISHER_CLASS_R02 FOREIGN KEY (PUBLISHER_ID) REFERENCES PUBLISHER_INFO (PUBLISHER_ID);ALTER TABLE PUBLISHER_CLASS ADD ( CONSTRAINT PUBLISHER_CLASS_R01 FOREIGN KEY (PUBLISHER_CLASS_ID)

46、REFERENCES PUBLISHER_CLASS_INFO (PUBLISHER_CLASS_ID); 图书分类参数信息表 BOOK_CLASS_INFO实体表CREATE TABLE BOOK_CLASS_INFO( BOOK_CLASS_ID NUMBER(10) NOT NULL, BOOK_CLASS_NAME VARCHAR2(20 CHAR) NOT NULL)TABLESPACE BOOK_DATA;CREATE UNIQUE INDEX BOOK_CLASS_INFO_PK ON BOOK_CLASS_INFO(BOOK_CLASS_ID)LOGGINGTABLESPACE

47、 BOOK_DATA;ALTER TABLE BOOK_CLASS_INFO ADD ( CONSTRAINT BOOK_CLASS_INFO_PK PRIMARY KEY (BOOK_CLASS_ID) USING INDEX TABLESPACE BOOK_DATA);图书分类关系表 BOOK_CLASSCREATE TABLE BOOK_CLASS( BOOK_ID NUMBER(10) NOT NULL, BOOK_CLASS_ID NUMBER(10) NOT NULL)TABLESPACE BOOK_DATA;CREATE UNIQUE INDEX BOOK_CLASS_PK ON

48、 BOOK_CLASS(BOOK_ID, BOOK_CLASS_ID)LOGGINGTABLESPACE BOOK_DATA;ALTER TABLE BOOK_CLASS ADD ( CONSTRAINT BOOK_CLASS_PK PRIMARY KEY (BOOK_ID, BOOK_CLASS_ID) USING INDEX TABLESPACE BOOK_DATA); ALTER TABLE BOOK_CLASS ADD ( CONSTRAINT BOOK_CLASS_R01 FOREIGN KEY (BOOK_ID) REFERENCES BOOK_INFO (BOOK_ID);ALT

49、ER TABLE BOOK_CLASS ADD ( CONSTRAINT BOOK_CLASS_R02 FOREIGN KEY (BOOK_CLASS_ID) REFERENCES BOOK_CLASS_INFO (BOOK_CLASS_ID);角色信息表 ROLE_INFO CREATE TABLE ROLE_INFO( ROLE_ID NUMBER(10) NOT NULL, ROLE_NAME VARCHAR2(10 CHAR) NOT NULL, MAX_BORROW_COUNT NUMBER(2) NOT NULL, ADMIN NUMBER(1) NOT NULL)TABLESPA

50、CE BOOK_DATA;CREATE UNIQUE INDEX ROLE_INFO_PK ON ROLE_INFO(ROLE_ID)LOGGINGTABLESPACE BOOK_DATA;ALTER TABLE ROLE_INFO ADD ( CONSTRAINT ROLE_INFO_PK PRIMARY KEY (ROLE_ID) USING INDEX TABLESPACE BOOK_DATA); 用户信息表 USER_INFO实体表，依赖角色表CREATE TABLE USER_INFO( USER_ID NUMBER(10) NOT NULL, USER_NAME VARCHAR2(

51、15 CHAR) NOT NULL, USER_REALNAME VARCHAR2(20 CHAR) NOT NULL, USER_PASSWORD VARCHAR2(15 CHAR) NOT NULL, USER_SEX NUMBER(1), USER_EMAIL VARCHAR2(20 CHAR), USER_TELEPHONE NUMBER(11), ROLE_ID NUMBER(5) NOT NULL, USER_BORROW_COUNT NUMBER(2)TABLESPACE BOOK_DATA;CREATE UNIQUE INDEX USER_INFO_PK ON USER_INF

52、O(USER_ID)LOGGINGTABLESPACE BOOK_DATA;ALTER TABLE USER_INFO ADD ( CONSTRAINT USER_INFO_PK PRIMARY KEY (USER_ID) USING INDEX TABLESPACE BOOK_DATA);ALTER TABLE USER_INFO ADD ( CONSTRAINT USER_INFO_R01 FOREIGN KEY (ROLE_ID) REFERENCES ROLE_INFO (ROLE_ID);用户借书关系表 USER_BOOKCREATE TABLE USER_BOOK( USER_ID

53、 NUMBER(10) NOT NULL, BOOK_ID NUMBER(10) NOT NULL, BORROW_DATE DATE, RETURN_DATE DATE)TABLESPACE BOOK_DATA;CREATE UNIQUE INDEX USER_BOOK_PK ON USER_BOOK(USER_ID, BOOK_ID)LOGGINGTABLESPACE BOOK_DATA;ALTER TABLE USER_BOOK ADD ( CONSTRAINT USER_BOOK_PK PRIMARY KEY (USER_ID, BOOK_ID) USING INDEX TABLESP

54、ACE BOOK_DATA);ALTER TABLE USER_BOOK ADD ( CONSTRAINT USER_BOOK_R01 FOREIGN KEY (USER_ID) REFERENCES USER_INFO (USER_ID);ALTER TABLE USER_BOOK ADD ( CONSTRAINT USER_BOOK_R02 FOREIGN KEY (BOOK_ID) REFERENCES BOOK_INFO (BOOK_ID);预定关系表 USER_RESERVECREATE TABLE USER_RESERVE( USER_ID NUMBER(10) NOT NULL,

55、 BOOK_ID NUMBER(10) NOT NULL, RESERVE_DATE DATE NOT NULL)TABLESPACE BOOK_DATA;CREATE UNIQUE INDEX RESERVE_PK ON USER_RESERVE(USER_ID, BOOK_ID)LOGGINGTABLESPACE BOOK_DATA;ALTER TABLE USER_RESERVE ADD ( CONSTRAINT RESERVE_PK PRIMARY KEY (USER_ID, BOOK_ID) USING INDEX TABLESPACE BOOK_DATA); ALTER TABLE

56、 USER_RESERVE ADD ( CONSTRAINT RESERVE_R01 FOREIGN KEY (USER_ID) REFERENCES USER_INFO (USER_ID);ALTER TABLE USER_RESERVE ADD ( CONSTRAINT RESERVE_R02 FOREIGN KEY (BOOK_ID) REFERENCES BOOK_INFO (BOOK_ID);书本评论表 BOOK_COMMENT CREATE TABLE BOOK_COMMENT( COMMENT_ID NUMBER(12) NOT NULL, COMMENT_TEXT VARCHA

57、R2(200 CHAR), COMMENT_DATE DATE, BOOK_ID NUMBER(10) NOT NULL, USER_ID NUMBER(10) NOT NULL)TABLESPACE BOOK_DATA;CREATE UNIQUE INDEX BOOK_COMMENT_PK ON BOOK_COMMENT(COMMENT_ID)LOGGINGTABLESPACE BOOK_DATA;ALTER TABLE BOOK_COMMENT ADD ( CONSTRAINT BOOK_COMMENT_PK PRIMARY KEY (COMMENT_ID) USING INDEX TAB

58、LESPACE BOOK_DATA);ALTER TABLE BOOK_COMMENT ADD ( CONSTRAINT BOOK_COMMENT_R01 FOREIGN KEY (BOOK_ID) REFERENCES BOOK_INFO (BOOK_ID);ALTER TABLE BOOK_COMMENT ADD ( CONSTRAINT BOOK_COMMENT_R02 FOREIGN KEY (USER_ID) REFERENCES USER_INFO (USER_ID);实验5PL/SQL根底按出版社查询购书金额的平均值DECLARE CURSOR c_price_avg IS SE

59、LECT c.publisher_name, AVG (book_price) FROM book_info a, book_publisher b, publisher_info c GROUP BY c.publisher_name; v_price_avg NUMBER (10,2); v_publisher VARCHAR2 (30);BEGIN OPEN c_price_avg; LOOP FETCH c_price_avg INTO v_publisher, v_price_avg; EXIT WHEN c_price_avg%NOTFOUND; DBMS_OUTPUT.put_l

60、ine (v_publisher | 的平均购书金额- | v_price_avg); END LOOP; CLOSE c_price_avg;END;运行结果如下：按出版社查询购书金额的最高及最低购书金额DECLARE v_max_price NUMBER (10,2); v_min_price NUMBER (10,2);BEGIN SELECT MAX (sum_book_price), MIN (sum_book_price) INTO v_max_price, v_min_price FROM (SELECT c.publisher_name AS publisher_name, S