圖書信息管理系統(tǒng)設(shè)計實(shí)驗(yàn)報告

1、圖書信息管理系統(tǒng)實(shí)驗(yàn)報告 目 錄 TOC o 1-3 h z u HYPERLINK l _Toc186256526 1實(shí)驗(yàn)1實(shí)驗(yàn)環(huán)境搭建 PAGEREF _Toc186256526 h 3 HYPERLINK l _Toc186256527 實(shí)驗(yàn)環(huán)境概述 PAGEREF _Toc186256527 h 3 HYPERLINK l _Toc186256528 ORACLE 10G安裝 PAGEREF _Toc186256528 h 3 HYPERLINK l _Toc186256529 表空間創(chuàng)立 PAGEREF _Toc186256529 h 4 HYPERLINK l _Toc186256

2、530 數(shù)據(jù)庫用戶創(chuàng)立 PAGEREF _Toc186256530 h 4 HYPERLINK l _Toc186256531 系統(tǒng)實(shí)體表結(jié)構(gòu) PAGEREF _Toc186256531 h 5 HYPERLINK l _Toc186256532 2實(shí)驗(yàn)2基表操作 PAGEREF _Toc186256532 h 8 HYPERLINK l _Toc186256533 基表關(guān)系圖 PAGEREF _Toc186256533 h 8 HYPERLINK l _Toc186256534 實(shí)體表數(shù)據(jù)操作 PAGEREF _Toc186256534 h 9 HYPERLINK l _Toc1862565

3、35 使用SQL*LOADER插入原始數(shù)據(jù) PAGEREF _Toc186256535 h 9 HYPERLINK l _Toc186256536 使用SQL*PLUS插入原始數(shù)據(jù) PAGEREF _Toc186256536 h 10 HYPERLINK l _Toc186256537 數(shù)據(jù)查詢 PAGEREF _Toc186256537 h 11 HYPERLINK l _Toc186256538 數(shù)據(jù)修改和刪除 PAGEREF _Toc186256538 h 11 HYPERLINK l _Toc186256539 3實(shí)驗(yàn)3SQL PLUS學(xué)習(xí) PAGEREF _Toc186256539

4、h 12 HYPERLINK l _Toc186256540 執(zhí)行SQL腳本文件 PAGEREF _Toc186256540 h 12 HYPERLINK l _Toc186256541 顯示錯誤信息 PAGEREF _Toc186256541 h 12 HYPERLINK l _Toc186256542 顯示表結(jié)構(gòu) PAGEREF _Toc186256542 h 12 HYPERLINK l _Toc186256543 改變?nèi)笔〉牧袠?biāo)題 PAGEREF _Toc186256543 h 13 HYPERLINK l _Toc186256544 改變列的顯示長度 PAGEREF _Toc1862

5、56544 h 13 HYPERLINK l _Toc186256545 設(shè)置每頁行數(shù) PAGEREF _Toc186256545 h 13 HYPERLINK l _Toc186256546 顯示每個SQL語句花費(fèi)的執(zhí)行時間 PAGEREF _Toc186256546 h 14 HYPERLINK l _Toc186256547 顯示SQL buffer中的SQL語句 PAGEREF _Toc186256547 h 14 HYPERLINK l _Toc186256548 編輯執(zhí)行SQL buffer中的SQL語句 PAGEREF _Toc186256548 h 15 HYPERLINK l

6、 _Toc186256549 4實(shí)驗(yàn)4創(chuàng)立基表 PAGEREF _Toc186256549 h 16 HYPERLINK l _Toc186256550 基表創(chuàng)立腳本 PAGEREF _Toc186256550 h 16 HYPERLINK l _Toc186256551 圖書信息表 BOOK_INFO實(shí)體表 PAGEREF _Toc186256551 h 16 HYPERLINK l _Toc186256552 作者信息表 AUTHOR_INFO實(shí)體表 PAGEREF _Toc186256552 h 16 HYPERLINK l _Toc186256553 圖書作者關(guān)系表 BOOK_AUTH

7、OR PAGEREF _Toc186256553 h 17 HYPERLINK l _Toc186256554 出版社信息表 PUBLISHER_INFO PAGEREF _Toc186256554 h 18 HYPERLINK l _Toc186256555 圖書出版社關(guān)系表 BOOK_PUBLISHER PAGEREF _Toc186256555 h 18 HYPERLINK l _Toc186256556 出版社分類參數(shù)信息表 PUBLISHER_CLASS_INFO實(shí)體表 PAGEREF _Toc186256556 h 19 HYPERLINK l _Toc186256557 出版社分

8、類關(guān)系表 PUBLISHER_CLASS PAGEREF _Toc186256557 h 19 HYPERLINK l _Toc186256558 圖書分類參數(shù)信息表 BOOK_CLASS_INFO實(shí)體表 PAGEREF _Toc186256558 h 20 HYPERLINK l _Toc186256559 圖書分類關(guān)系表 BOOK_CLASS PAGEREF _Toc186256559 h 21 HYPERLINK l _Toc186256560 角色信息表 ROLE_INFO PAGEREF _Toc186256560 h 21 HYPERLINK l _Toc186256561 用戶信

9、息表 USER_INFO實(shí)體表，依賴角色表 PAGEREF _Toc186256561 h 22 HYPERLINK l _Toc186256562 用戶借書關(guān)系表 USER_BOOK PAGEREF _Toc186256562 h 23 HYPERLINK l _Toc186256563 預(yù)定關(guān)系表 USER_RESERVE PAGEREF _Toc186256563 h 23 HYPERLINK l _Toc186256564 書本評論表 BOOK_COMMENT PAGEREF _Toc186256564 h 24 HYPERLINK l _Toc186256565 5實(shí)驗(yàn)5PL/SQL

10、根底 PAGEREF _Toc186256565 h 26 HYPERLINK l _Toc186256566 按出版社查詢購書金額的平均值 PAGEREF _Toc186256566 h 26 HYPERLINK l _Toc186256567 按出版社查詢購書金額的最高及最低購書金額 PAGEREF _Toc186256567 h 27 HYPERLINK l _Toc186256568 查詢出版社購書金額超過平均值的出版社數(shù) PAGEREF _Toc186256568 h 28 HYPERLINK l _Toc186256569 查詢購書金額高于1萬元的出版社數(shù) PAGEREF _Toc

11、186256569 h 29 HYPERLINK l _Toc186256570 查詢本月借出書的數(shù)量 PAGEREF _Toc186256570 h 29 HYPERLINK l _Toc186256571 6實(shí)驗(yàn)6基表數(shù)據(jù)操作 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 一年內(nèi)無借閱記錄的圖書在備注欄中注明 PAGEREF _Toc186256574 h 33 HYPERLINK l _Toc186256575 刪除圖書借閱信息中超過兩年且已歸還的記錄 PAGEREF _Toc186256575 h 33 HYPERLINK l _Toc186256576 7實(shí)驗(yàn)7索引和完整性 PAGEREF _Toc186256576 h 34 HYPERLINK l _Toc186256577 建立出版社名稱不能重名的索引 PAGEREF _Toc186256577 h 34 HYPERLINK l _Toc186256578 建立圖書借閱信息中書號加借閱日期加借書

13、卡號的索引 PAGEREF _Toc186256578 h 34 HYPERLINK l _Toc186256579 完善域完整性、實(shí)體完整性和參照完整性 PAGEREF _Toc186256579 h 35 HYPERLINK l _Toc186256580 8實(shí)驗(yàn)8數(shù)據(jù)庫的查詢和視圖 PAGEREF _Toc186256580 h 36 HYPERLINK l _Toc186256581 建立查詢數(shù)據(jù)視圖 PAGEREF _Toc186256581 h 36 HYPERLINK l _Toc186256582 實(shí)現(xiàn)對各基表及多表的數(shù)據(jù)查詢 PAGEREF _Toc186256582 h 3

14、6 HYPERLINK l _Toc186256583 9實(shí)驗(yàn)9PL/SQL編程 PAGEREF _Toc186256583 h 38 HYPERLINK l _Toc186256584 統(tǒng)計本月圖書借出總數(shù)量、總金額 PAGEREF _Toc186256584 h 38 HYPERLINK l _Toc186256585 使用循環(huán)結(jié)構(gòu)求和 PAGEREF _Toc186256585 h 39 HYPERLINK l _Toc186256586 使用CASE語句查圖書的所屬種類 PAGEREF _Toc186256586 h 40 HYPERLINK l _Toc186256587 使用游標(biāo)統(tǒng)

15、計當(dāng)前未歸還圖書的總金額 PAGEREF _Toc186256587 h 41 HYPERLINK l _Toc186256588 10實(shí)驗(yàn)10存儲過程與觸發(fā)器 PAGEREF _Toc186256588 h 42 HYPERLINK l _Toc186256589 存儲過程 PAGEREF _Toc186256589 h 42 HYPERLINK l _Toc186256590 觸發(fā)器 PAGEREF _Toc186256590 h 44 HYPERLINK l _Toc186256591 BOOK_INFO刪除操作審計 PAGEREF _Toc186256591 h 44 HYPERLIN

16、K l _Toc186256592 自動更新出版社購書數(shù)量觸發(fā)器 PAGEREF _Toc186256592 h 44 HYPERLINK l _Toc186256593 11實(shí)驗(yàn)11函數(shù)與包 PAGEREF _Toc186256593 h 47 HYPERLINK l _Toc186256594 函數(shù) PAGEREF _Toc186256594 h 47 HYPERLINK l _Toc186256595 借出圖書數(shù) PAGEREF _Toc186256595 h 47 HYPERLINK l _Toc186256596 歸還圖書數(shù) PAGEREF _Toc186256596 h 48 HY

17、PERLINK l _Toc186256597 包 PAGEREF _Toc186256597 h 49 HYPERLINK l _Toc186256598 12實(shí)驗(yàn)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實(shí)驗(yàn)13系統(tǒng)數(shù)據(jù)維護(hù) PAGEREF _Toc186256602 h 57 HYPERLINK l _Toc186256603 邏輯備份 PAGEREF _Toc186256603 h 58 HYPERLINK l _Toc186256604 邏輯導(dǎo)出 PAGEREF _Toc186256604 h 58 HYPERLINK l _Toc186256605 邏輯導(dǎo)入 PAGEREF _Toc186256605 h 59實(shí)驗(yàn)1實(shí)驗(yàn)環(huán)境搭建實(shí)驗(yàn)環(huán)境概述操作系統(tǒng)：Windows XP SP2數(shù) 據(jù) 庫： Oracle 10g 企業(yè)版數(shù)據(jù)庫管理工具：Toad

19、for Oracle Version 9.0.1數(shù)據(jù)庫客戶端：Oracle 10g ClientORACLE 10G安裝Oracle 10g對軟硬件環(huán)境要求：內(nèi)存最小需要：512MB系統(tǒng)交換區(qū)SWAP最小需要：1GB 或2倍內(nèi)存容量的空間，如果內(nèi)存大于等于2GB那么SWAP在1倍或2倍內(nèi) 存容量之間。/tmp最小需要：400MB磁盤空間最小需要：3.7GB 其中2.5GB用于oracle software files ，1 .2GB用于database files。系統(tǒng)結(jié)構(gòu)必須是：64位表空間創(chuàng)立創(chuàng)立的表空間名為BOOK_DATA，由于本數(shù)據(jù)庫規(guī)模不大，故無需單獨(dú)建立索引表空間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 ;數(shù)據(jù)庫用戶創(chuàng)立首先用SYS用戶登錄數(shù)據(jù)庫BOOK，然后添加本系統(tǒng)得管理員用戶BOOK，并給其賦予DBA權(quán)限。同時，要建立供其他人員使用的用戶，如TEACHER和STUDENT這些用戶只有修改和查詢的權(quán)限，其它用戶的創(chuàng)立及權(quán)限詳見實(shí)驗(yàn)12。create user book

21、 identified by book default tablespace book_datatemporary tablespace temp;grant dba to book;關(guān)聯(lián)用戶和表空間alter user bookidentified by bookdefault tablespace book_datatemporary tablespace temp;系統(tǒng)實(shí)體表結(jié)構(gòu)圖書信息表 BOOK_INFO字段名注釋類型約束備注BOOK_ID書本編號NUMBER(10)非空，主鍵BOOK_NAME書本名VARCHAR2(20 CHAR)BOOK_PUBDATE出版日期DATEBOOK_

22、PRICE書本價格NUMBER(10,2)BOOK_QUANTITY庫存數(shù)量NUMBER(10)BOOK_INFO書本簡介VARCHAR2(100 CHAR)BOOK_RESNUMBER預(yù)訂數(shù)量NUMBER(6)BOOK_REMARK備注VARCHAR2(100 CHAR)作者信息表 AUTHOR_INFO字段名注釋類型約束備注AUTHOR_ID作者編號NUMBER(10)非空，主鍵AUTHOR_NAME作者名VARCHAR2(20 CHAR)AUTHOR_INFO作者簡介VARCHAR2(100 CHAR)圖書作者關(guān)系表 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出版社圖書數(shù)量NUMBER(10)圖書出版社關(guān)系表 BOOK_PUBLISHER字段名注釋類型約束備注BOOK_ID書本編號NUMBER(10)非空，外鍵PUBLISHER_ID出版社編號NUMBER

24、(10)非空，外鍵出版社分類參數(shù)信息表 PUBLISHER_CLASS_INFO字段名注釋類型約束備注PUBLISHER_CLASS_ID出版社分類編號NUMBER(10)非空，主鍵PUBLISHER_CLASS_NAME出版社分類名字VARCHAR2(20 CHAR)出版社分類關(guān)系表 PUBLISHER_CLASS字段名注釋類型約束備注PUBLISHER_ID出版社編號NUMBER(10)非空，外鍵PUBLISHER_CLASS_ID出版社分類編號NUMBER(10)非空，外鍵圖書分類參數(shù)信息表 BOOK_CLASS_INFO字段名注釋類型約束備注BOOK_CLASS_ID書本分類編號NUM

25、BER(10)非空，主鍵BOOK_CLASS_NAME書本分類名稱VARCHAR2(20CHAR)非空圖書分類關(guān)系表 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最大借書數(shù)量NUMBER(2)非空ADMIN是否管理員NUMBER(1)非空用戶信息表 USER_INFO字段名注釋類型

26、約束備注USER_ID用戶編號NUMBER(10非空，主鍵USER_NAME登入賬號VARCHAR2(15 CHAR)非空USER_REALNAME真實(shí)姓名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已經(jīng)借書數(shù)量NUMBER(2)用戶借書關(guān)系表 USER_BOOK字段名注釋類型約束備注USER_

27、ID用戶編號NUMBER(10)非空，外鍵BOOK_ID書本編號NUMBER(10)非空，外鍵BORROW_DATE借書數(shù)量DATERETURN_DATE歸還日期DATE預(yù)定關(guān)系表 USER_RESERVE字段名注釋類型約束備注USER_ID用戶編號NUMBER(10)非空，外鍵BOOK_ID書本編號NUMBER(10)非空，外鍵RESERVE_DATE預(yù)約日期DATE非空書本評論表 BOOK_COMMENT字段名注釋類型約束備注COMMENT_ID評論編號NUMBER(12)非空，主鍵COMMENT_TEXT評論內(nèi)容VARCHAR2(200 CHAR)COMMENT_DATE評論日期DATE

28、BOOK_ID書本編號NUMBER(10)非空，外鍵USER_ID用戶編號NUMBER(10)非空，外鍵實(shí)驗(yàn)2基表操作基表關(guān)系圖實(shí)體表數(shù)據(jù)操作使用SQL*LOADER插入原始數(shù)據(jù)下面以書本信息表為例：整理Excel數(shù)據(jù)原始文件，“另存為“文本文件制表符分隔，取名為book10.txt 。 編輯一個和Excel 數(shù)據(jù)相對應(yīng)的表。編輯控制文件，控制文件取名為： 其中格式如下： 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)調(diào)用SQL*LOADER，加載數(shù)據(jù)S 添加數(shù)據(jù)結(jié)果：使用SQL*PLUS插入原始數(shù)據(jù)其中圖書信息表數(shù)據(jù)較多，采用SQL LOAD方法導(dǎo)入數(shù)據(jù)，其他表采用SQL語句插入數(shù)據(jù)，以圖書種類關(guān)系表為例：添加圖書種類關(guān)系信息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;數(shù)據(jù)查詢查詢書本信息表中編號為10開頭的全部書籍：Select * from book_info where book_id like 10%;數(shù)據(jù)修改和刪除 刪除用戶借閱表里編號是“3006用戶的所有借閱情況。圖中顯示已經(jīng)

31、刪除了6條信息。實(shí)驗(yàn)3SQL PLUS學(xué)習(xí)執(zhí)行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 顯示表結(jié)構(gòu)顯示書本信息表的結(jié)構(gòu)：desc book_info改變?nèi)笔〉牧袠?biāo)題修改書本信息表的列名name為“

33、書本名Select book_name “書本名 from book_info Where book_id between 100001 and 100005；改變列的顯示長度設(shè)置一行可以容納的字符數(shù)SQLSETLINESIZE80|n如果一行的輸出內(nèi)容大于設(shè)置的一行可容納的字符數(shù)，那么折行顯示SQL set lin 30設(shè)置每頁行數(shù)設(shè)置一頁有多少行數(shù)SQLSETPAGESIZE24|nSQL set pagesize 6顯示每個SQL語句花費(fèi)的執(zhí)行時間顯示每個sql語句花費(fèi)的執(zhí)行時間setTIMINGON|OFFSQL set timing on顯示SQL buffer中的SQL語句顯示sq

34、l buffer中的sql語句，list n顯示sql buffer中的第n行，并使第n行成為當(dāng)前行 LIST n 編輯執(zhí)行SQL buffer中的SQL語句編輯sql buffer中的sql語句 EDIT 顯示sqlbuffer中的sql語句，listn顯示sqlbuffer中的第n行，并使第n行成為當(dāng)前行LISTn將sqlbuffer中的sql語句保存到一個文件中SAVEfile_name將一個文件中的sql語句導(dǎo)入到sqlbuffer中GETfile_name再次執(zhí)行剛剛已經(jīng)執(zhí)行的sql語句RUNor /實(shí)驗(yàn)4創(chuàng)立基表基表創(chuàng)立腳本圖書信息表 BOOK_INFO實(shí)體表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實(shí)體表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); 圖書作者關(guān)系表 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); 圖書出版社關(guān)系表 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); 出版社分類參數(shù)信息表 PUBLISHER_CLASS_INFO實(shí)體表 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); 出版社分類關(guān)系表 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); 圖書分類參數(shù)信息表 BOOK_CLASS_INFO實(shí)體表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);圖書分類關(guān)系表 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實(shí)體表，依賴角色表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);用戶借書關(guān)系表 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);預(yù)定關(guān)系表 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);實(shí)驗(yàn)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;運(yùn)行結(jié)果如下：按出版社查詢購書金額的最高及最低購書金額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