圖書(shū)管理系統(tǒng)綜合習(xí)題

1、圖書(shū)管理系統(tǒng)1 需求說(shuō)明（分用戶(hù)分解各項(xiàng)功能）圖書(shū)管理系統(tǒng)包括圖書(shū)管理，學(xué)生借閱圖書(shū)管理兩大功能，具體的業(yè)務(wù)功能為：l 管理員進(jìn)行新書(shū)入庫(kù)l 管理員對(duì)圖書(shū)基本信息進(jìn)行修改l 管理員記錄學(xué)生的借書(shū)信息和還書(shū)信息l 管理員對(duì)圖書(shū)基本信息進(jìn)行查詢(xún)l 管理員對(duì)圖書(shū)的借出還入情況進(jìn)行查詢(xún)l 管理員對(duì)學(xué)生的借書(shū)還書(shū)信息進(jìn)行查詢(xún)l 管理員對(duì)學(xué)生的基本信息進(jìn)行查詢(xún)2 數(shù)據(jù)庫(kù)設(shè)計(jì)2.2 實(shí)體屬性分析由圖1所示的E-R模型轉(zhuǎn)換成的關(guān)系模式如下：l 讀者（借書(shū)證號(hào)，姓名，性別，年齡，住址），借書(shū)證號(hào)設(shè)為主鍵；l 圖書(shū)（圖書(shū)編號(hào)，圖書(shū)名，圖書(shū)作者，圖書(shū)價(jià)格，圖書(shū)類(lèi)別，出版日期，出版社)，圖書(shū)編號(hào)設(shè)為主鍵； 2.1 數(shù)

2、據(jù)庫(kù)概念模型設(shè)計(jì)（E-R模型）經(jīng)過(guò)分析，一個(gè)學(xué)生可以借閱多本圖書(shū)，一本圖書(shū)也可以被多個(gè)系統(tǒng)包含的實(shí)體有學(xué)生和圖書(shū)，兩個(gè)實(shí)體之間通過(guò)借閱發(fā)生聯(lián)系，聯(lián)系的類(lèi)型為多對(duì)多。其對(duì)應(yīng)的E-R模型如圖1所示。mn借書(shū)時(shí)間圖書(shū)借閱讀者還書(shū)時(shí)間圖1 系統(tǒng)E-R模型2.3 數(shù)據(jù)庫(kù)邏輯模型設(shè)計(jì)根據(jù)數(shù)據(jù)庫(kù)邏輯結(jié)構(gòu)設(shè)計(jì)結(jié)果，在SQL Server2005數(shù)據(jù)庫(kù)管理系統(tǒng)中，創(chuàng)建Library數(shù)據(jù)庫(kù)，并在該數(shù)據(jù)庫(kù)中創(chuàng)建3張數(shù)據(jù)表，分別為學(xué)生表student、圖書(shū)表book、借閱表borrow，表結(jié)構(gòu)如表1至表3所示。表1 讀者表reader字段名數(shù)據(jù)類(lèi)型長(zhǎng)度約束備注 ReaderIdchar12主鍵借書(shū)證號(hào) SnameVa

3、rchar10非空姓名 SsexChar2默認(rèn)值為“男”性別SbirthdayDatetime出生日期 SaddressVarchar50住址表2 圖書(shū)表book字段名數(shù)據(jù)類(lèi)型長(zhǎng)度約束備注BnoVarchar20主鍵圖書(shū)編號(hào)BnameVarchar20非空?qǐng)D書(shū)名BauthorVarchar20圖書(shū)作者Bpricetinyint圖書(shū)價(jià)格BcategoryVarchar20圖書(shū)類(lèi)別BpressdayDatetime出版日期BpressVarchar20出版社表3 借閱表borrow字段名數(shù)據(jù)類(lèi)型長(zhǎng)度與格式約束備注BnoVarchar20主鍵，外鍵圖書(shū)編號(hào)ReaderIdchar12主鍵，外鍵學(xué)號(hào)B

4、orrowdayDatetime主鍵借書(shū)時(shí)間ReturndayDatetime還書(shū)時(shí)間確認(rèn)轉(zhuǎn)換生成的3個(gè)關(guān)系模式都符合第三范式。3 SQL語(yǔ)句練習(xí)1. 寫(xiě)出創(chuàng)建學(xué)生表student、借閱表borrow的SQL語(yǔ)句。2. 查詢(xún)每個(gè)學(xué)生的基本信息，按學(xué)號(hào)升序排序。3. 查詢(xún)每個(gè)學(xué)生的借閱情況（學(xué)號(hào)、姓名、書(shū)號(hào)、借書(shū)日期、還書(shū)日期），包括沒(méi)有借書(shū)的學(xué)生的借閱情況。4. 查詢(xún)每本圖書(shū)被借閱的情況，包括沒(méi)有被借閱的圖書(shū)情況。5. 查詢(xún)同名的圖書(shū)。6. 查詢(xún)借閱過(guò)“高等教育出版社”出版的圖書(shū)的學(xué)生信息。7. 查詢(xún)沒(méi)有借過(guò)書(shū)的學(xué)生信息。8. 查詢(xún)圖書(shū)的總冊(cè)數(shù)、最高價(jià)、最低價(jià)、總價(jià)值和平均價(jià)。9. 查詢(xún)定價(jià)在

5、30到40元之間的圖書(shū)信息。10. 查詢(xún)書(shū)名以“數(shù)據(jù)庫(kù)”起始的圖書(shū)信息。11. 統(tǒng)計(jì)各出版社圖書(shū)的數(shù)量。12. 統(tǒng)計(jì)每本書(shū)的借閱次數(shù)。13. 查詢(xún)每本書(shū)的基本信息，按價(jià)格降序排序。14. 統(tǒng)計(jì)每個(gè)學(xué)生的借書(shū)數(shù)量。（包括已經(jīng)還的書(shū)和正在借的書(shū)）15. 將“人民郵電出版社”出版的圖書(shū)的價(jià)格減少3元。16. 刪除沒(méi)有人借閱的圖書(shū)信息。17. 刪除圖書(shū)“數(shù)據(jù)庫(kù)基礎(chǔ)及應(yīng)用”的借閱信息。18. 創(chuàng)建一個(gè)視圖，查詢(xún)每個(gè)男生的借閱情況（學(xué)號(hào)，書(shū)號(hào)，借書(shū)日期，還書(shū)日期）。然后使用該視圖，統(tǒng)計(jì)每個(gè)男生的借書(shū)數(shù)量。（包括已經(jīng)還的書(shū)和正在借的書(shū)）1. 寫(xiě)出創(chuàng)建讀者表reader、圖書(shū)表book、借閱表borrow的S

6、QL語(yǔ)句。CREATE TABLE student ( ReaderId char (12) PRIMARY KEY, Sname varchar (10) NOT NULL, Ssex char (2) default 男, Sbirthday datetime, Saddress varchar (50)CREATE TABLE book ( Bno varchar(20) PRIMARY KEY, Bname varchar(20) NOT NULL, Bauthor varchar(20), Bprice tinyint, Bcategory varchar(10), Bpressda

7、y datetime, Bpress varchar(10) CREATE TABLE borrow ( Bno varchar(20), ReaderId char(12), Borrowday datetime, Returnday datetime, PRIMARY KEY (Bno,ReaderId,Borrowday), FOREIGN KEY (Bno ) REFERENCES book(Bno ), FOREIGN KEY (ReaderId) REFERENCES student(ReaderId)2. 查詢(xún)每個(gè)學(xué)生的基本信息，按學(xué)號(hào)升序排序。select * from stu

8、dentorder by ReaderId3. 查詢(xún)每個(gè)學(xué)生的借閱情況（學(xué)號(hào)、姓名、書(shū)號(hào)、借書(shū)日期、還書(shū)日期），包括沒(méi)有借書(shū)的學(xué)生信息。select s.ReaderId,sname,bno,borrowday,returndayfrom student s left join borrow on s.ReaderId=borrow.ReaderId4. 查詢(xún)每本圖書(shū)被借閱的情況，包括沒(méi)有被借閱的圖書(shū)情況。select * from book b left join borrow on b.bno=borrow.bno5. 查詢(xún)同名的圖書(shū)。select distinct b1.bno,b1.b

9、name from book b1 join book b2 on =6. 查詢(xún)借閱過(guò)“高等教育出版社”出版的圖書(shū)的學(xué)生信息。select * from student s join on borrow on s.ReaderId=borrow.ReaderId join on book b on b.bno=borrow.bnowhere bpress=高等教育出版社7. 查詢(xún)沒(méi)有借過(guò)書(shū)的學(xué)生信息。select * from studentwhere ReaderId not in(select distinct ReaderId from borrow)8. 查

10、詢(xún)圖書(shū)的總冊(cè)數(shù)、最高價(jià)、最低價(jià)、總價(jià)值和平均價(jià)。select count(bno),max(bprice),min(bprice),sum(bprice),avg(bprice)from book9. 查詢(xún)定價(jià)在30到40元之間的圖書(shū)信息。select * from bookwhere bprice between 30 and 4010. 查詢(xún)書(shū)名以“數(shù)據(jù)庫(kù)”起始的圖書(shū)信息。select * from bookwhere bname like 數(shù)據(jù)庫(kù)%11. 統(tǒng)計(jì)各出版社圖書(shū)的數(shù)量。select count(bno)from bookgroup by bpress12. 統(tǒng)計(jì)每本書(shū)的借閱次數(shù)

11、。select count(bno)from borrowgroup by bno13. 查詢(xún)每本書(shū)的基本信息，按價(jià)格降序排序。select * from bookorder by bprice desc14. 統(tǒng)計(jì)每個(gè)學(xué)生的借書(shū)數(shù)量。（包括已經(jīng)還的書(shū)和正在借的書(shū)）select count(ReaderId)from borrowgroup by ReaderId15. 將“人民郵電出版社”出版的圖書(shū)的價(jià)格減少3元。update bookset bprice=bprice-3where bpress=人民郵電出版社16. 刪除沒(méi)有人借閱的圖書(shū)信息。delete from book where bno not in(select distinct bno from borrow)17. 刪除書(shū)名為“數(shù)據(jù)庫(kù)基礎(chǔ)及應(yīng)用”的圖書(shū)信息。delete from bookwhere bname=數(shù)據(jù)庫(kù)基礎(chǔ)及應(yīng)用18. 創(chuàng)建一個(gè)視圖，查詢(xún)每個(gè)男生的借閱情況（學(xué)號(hào)，書(shū)號(hào)，借書(shū)日期，還書(shū)日期）。然后使用該視圖，統(tǒng)計(jì)每個(gè)男生的借書(shū)數(shù)量。（包括已經(jīng)還的書(shū)和正