圖書管理系統(tǒng)綜合習題

1、精品資料圖書管理系統(tǒng)1需求說明（分用戶分解各項功能）圖書管理系統(tǒng)包括圖書管理，學生借閱圖書管理兩大功能，具體的業(yè)務功能 為：管理員進行新書入庫管理員對圖書基本信息進行修改管理員記錄學生的借書信息和還書信息管理員對圖書基本信息進行查詢管理員對圖書的借出還入情況進行查詢管理員對學生的借書還書信息進行查詢管理員對學生的基本信息進行查詢2數(shù)據(jù)庫設計2.2實體屬性分析由圖1所示的E-R模型轉換成的關系模式如下：讀者（借書證號，姓名，性別，年齡，住址），借書證號設為主鍵；圖書（圖書編號,圖書名，圖書作者，圖書價格，圖書類別，出版日期， 出版社），圖書編號設為主鍵；2.1數(shù)據(jù)庫概念模型設計（E-R模型）經(jīng)過

2、分析，一個學生可以借閱多本圖書，一本圖書也可以被多個系統(tǒng)包含的實體有學生和圖書，兩個實體之間通過借閱發(fā)生聯(lián)系，聯(lián)系的類型為 多對多。其對應的E-R模型如圖1所示。2.3數(shù)據(jù)庫邏輯模型設計根據(jù)數(shù)據(jù)庫邏輯結構設計結果，在 SQL Server2005數(shù)據(jù)庫管理系統(tǒng)中， 創(chuàng)建Library數(shù)據(jù)庫，并在該數(shù)據(jù)庫中創(chuàng)建3張數(shù)據(jù)表，分別為學生表student、 圖書表book、借閱表borrow ,表結構如表1至表3所示。表1 讀者表reader字段名數(shù)據(jù)類型長度約束備注ReaderIdchar12主鍵借書證號SnameVarchar10非空姓名SsexChar2默認值為“男”性別SbirthdayDat

3、etime出生日期SaddressVarchar50住址表2 圖書表book字段名數(shù)據(jù)類型長度約束備注BnoVarchar20主鍵圖書編號BnameVarchar20非空圖書名BauthorVarchar20圖書作者Bpricetinyint圖書價格BcategoryVarchar20圖書類別BpressdayDatetime出版日期BpressVarchar20出版社表3 借閱表borrow字段名數(shù)據(jù)類型長度與格式約束備注BnoVarchar20主鍵，外鍵圖書編號ReaderIdchar12主鍵，外鍵學號BorrowdayDatetime主鍵借書時間ReturndayDatetime還書時間

4、確認轉換生成的3個關系模式都符合第三范式3 SQL語句練習1 .寫出創(chuàng)建學生表student、借閱表borrow 的SQL語句。2 .查詢每個學生的基本信息，按學號升序排序。3 .查詢每個學生的借閱情況（學號、姓名、書號、借書日期、還書日期），包 括沒有借書的學生的借閱情況。4 .查詢每本圖書被借閱的情況，包括沒有被借閱的圖書情況。5 .查詢同名的圖書。6 .查詢借閱過“高等教育出版社”出版的圖書的學生信息。7 .查詢沒有借過書的學生信息。8 .查詢圖書的總冊數(shù)、最高價、最低價、總價值和平均價。9 .查詢定價在30到40元之間的圖書信息。10 .查詢書名以“數(shù)據(jù)庫”起始的圖書信息。11 .統(tǒng)計

5、各出版社圖書的數(shù)量。12 .統(tǒng)計每本書的借閱次數(shù)。13 .查詢每本書的基本信息，按價格降序排序。14 .統(tǒng)計每個學生的借書數(shù)量。（包括已經(jīng)還的書和正在借的書）15 .將“人民郵電出版社”出版的圖書的價格減少 3元。16 .刪除沒有人借閱的圖書信息。17 .刪除圖書“數(shù)據(jù)庫基礎及應用”的借閱信息。18 .創(chuàng)建一個視圖，查詢每個男生的借閱情況（學號，書號，借書日期，還書日 期）。然后使用該視圖，統(tǒng)計每個男生的借書數(shù)量。（包括已經(jīng)還的書和正在借的書）1. 寫出創(chuàng)建讀者表reader、圖書表book、借閱表borrow 的SQL語句。 CREATE TABLE student （ReaderId ch

6、ar (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,Bnamevarchar(20)NOT NULL,Bauthorvarchar(20),Bprice tinyint,Bcategory varchar(10),Bpressday datetime, Bpress varchar(10)CREATE TABLE borrow (

7、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. 查詢每個學生的基本信息，按學號升序排序。select * from studentorder by ReaderId3. 查詢每個學生的借閱情況(學號、姓名、書號、借書日期、還書日期)，包括沒有

8、借書的學生信息。select s.ReaderId,sname,bno,borrowday,returndayfrom student s left join borrow on s.ReaderId=borrow.ReaderId4. 查詢每本圖書被借閱的情況，包括沒有被借閱的圖書情況。select * from book b left join borrow on b.bno=borrow.bno5. 查詢同名的圖書。onselect distinct b1.bno,b1.bname from book b1 join book b2 =6. 查詢借閱過“高等教

9、育出版社”出版的圖書的學生信息。select * from student s join on borrow on s.ReaderId=borrow.ReaderId join on book b on b.bno=borrow.bnowhere bpress= 高等教育出版社 7. 查詢沒有借過書的學生信息。select * from studentwhere ReaderId not in(select distinct ReaderId from borrow)8. 查詢圖書的總冊數(shù)、最高價、最低價、總價值和平均價。select count(bno),max(bprice),min(b

10、price),sum(bprice),avg(bprice)from book9. 查詢定價在 30 到 40 元之間的圖書信息。select * from bookwhere bprice between 30 and 4010. 查詢書名以“數(shù)據(jù)庫”起始的圖書信息。select * from bookwhere bname like 數(shù)據(jù)庫 %11. 統(tǒng)計各出版社圖書的數(shù)量。select count(bno)from bookgroup by bpress12. 統(tǒng)計每本書的借閱次數(shù)。select count(bno)from borrowgroup by bno13. 查詢每本書的基本信

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