1、圖書(shū)管理系統(tǒng)1需求說(shuō)明(用戶啟動(dòng)的功能分解)圖書(shū)管理系統(tǒng)包括圖書(shū)管理、學(xué)生借閱圖書(shū)管理兩個(gè)茄子主要功能，具體的工作功能如下：l管理員執(zhí)行新書(shū)入庫(kù)l管理員修改了圖書(shū)基本信息。l管理員記錄學(xué)生貸款信息和返回信息。l管理員查詢圖書(shū)基本信息。l經(jīng)理查詢圖書(shū)貸款償還情況。l管理員查詢學(xué)生圖書(shū)返回信息。l管理員查詢學(xué)生基本信息2數(shù)據(jù)庫(kù)設(shè)計(jì)2.2分析實(shí)體屬性從圖1所示的E-R模型轉(zhuǎn)換而來(lái)的關(guān)系模式如下：l讀者(借書(shū)證編號(hào)、名字、性別、年齡、地址)和借書(shū)證編號(hào)被設(shè)置為主鍵。l圖書(shū)(圖書(shū)編號(hào)、圖書(shū)名稱、圖書(shū)作者、圖書(shū)價(jià)錢(qián)、圖書(shū)類(lèi)別、出版日期、出版社)、圖書(shū)編號(hào)設(shè)定為主鍵。2.1數(shù)據(jù)庫(kù)概念模型設(shè)計(jì)(E-R模型)通

2、過(guò)分析，一名學(xué)生可以借多本書(shū)，一本書(shū)也可以借多本系統(tǒng)中包含的實(shí)體是學(xué)生和圖書(shū)，兩個(gè)實(shí)體之間通過(guò)貸款聯(lián)系在一起，連接類(lèi)型是多對(duì)多。相應(yīng)的E-R型號(hào)如圖1所示。mn借書(shū)的時(shí)間圖書(shū)借用讀者還書(shū)的時(shí)間圖1系統(tǒng)E-R型號(hào)2.3數(shù)據(jù)庫(kù)邏輯模型設(shè)計(jì)數(shù)據(jù)庫(kù)邏輯結(jié)構(gòu)設(shè)計(jì)結(jié)果表明，在SQL SER 2005數(shù)據(jù)庫(kù)管理系統(tǒng)(SQL SER 2005)中創(chuàng)建庫(kù)數(shù)據(jù)庫(kù)，并在相應(yīng)的數(shù)據(jù)庫(kù)(學(xué)生表student、書(shū)籍表book、貸款表borrow和表1-3)中創(chuàng)建三個(gè)數(shù)據(jù)表表1讀者表reader字段名數(shù)據(jù)格式長(zhǎng)度約束注釋ReaderId查爾12主鍵借書(shū)證號(hào)碼SnameVarchar10非空名字Ssex查爾2默認(rèn)值為“男人”

3、性別SbirthdayDatetime出生日期SaddressVarchar50地址表2圖書(shū)目錄字段名數(shù)據(jù)格式長(zhǎng)度約束注釋BnoVarchar20主鍵圖書(shū)編號(hào)BnameVarchar20非空?qǐng)D書(shū)名稱BauthorVarchar20圖書(shū)作家Bprice蒂寧特圖書(shū)價(jià)錢(qián)BcategoryVarchar20圖書(shū)類(lèi)別BpressdayDatetime出版日期BpressVarchar20出版社表3貸款西餐borrow字段名數(shù)據(jù)格式長(zhǎng)度和格式約束注釋BnoVarchar20主鍵，外鍵圖書(shū)編號(hào)ReaderId查爾12主鍵，外鍵學(xué)號(hào)BorrowdayDatetime主鍵借書(shū)的時(shí)間ReturndayDateti

4、me還書(shū)的時(shí)間轉(zhuǎn)換生成的所有三個(gè)茄子關(guān)系模式都檢查第三個(gè)范式是否符合。3 SQL語(yǔ)句練習(xí)1.學(xué)生表student，編寫(xiě)生成貸款表borrow的SQL語(yǔ)句。2.查詢每個(gè)學(xué)生基本信息。按學(xué)號(hào)升序排序。3.查詢各學(xué)生(學(xué)號(hào)、姓名、書(shū)號(hào)、貸款日期、返還日期)的貸款情況。這包括沒(méi)有借書(shū)的學(xué)生貸款。4.查詢各書(shū)借出的情況，包括未借出的圖書(shū)。5.查詢同名的書(shū)。6.查詢高等教育出版社出版的圖書(shū)借閱的學(xué)生信息。7.查詢沒(méi)有借來(lái)的書(shū)的學(xué)生信息。8.查詢圖書(shū)的總書(shū)目、最高價(jià)、最低價(jià)、總價(jià)值、和平平均價(jià)。9.查詢定價(jià)在30韓元到40韓元之間的圖書(shū)信息。10.查詢書(shū)名以“數(shù)據(jù)庫(kù)”開(kāi)頭的圖書(shū)信息。11.統(tǒng)計(jì)各出版社的圖書(shū)

5、數(shù)量。12.統(tǒng)計(jì)每本書(shū)的貸款次數(shù)。13.按價(jià)錢(qián)降序查詢每本書(shū)的基本信息。14.計(jì)算每個(gè)學(xué)生的圖書(shū)借閱數(shù)。(包括已經(jīng)歸還的書(shū)和借來(lái)的書(shū))15.人民郵政出版社出版的圖書(shū)價(jià)錢(qián)減少了3元。16.刪除沒(méi)有借閱者的圖書(shū)信息。17.刪除圖書(shū)“數(shù)據(jù)庫(kù)基礎(chǔ)和應(yīng)用”中的貸款信息。18.生成可以查詢每個(gè)男生的貸款情況(學(xué)號(hào)、書(shū)號(hào)、貸款日期、返還日期)的視圖。然后使用此視圖計(jì)算每個(gè)男孩的書(shū)數(shù)。(包括已經(jīng)歸還的書(shū)和借來(lái)的書(shū))1.編寫(xiě)創(chuàng)建讀者表reader、圖書(shū)表book和貸款表borrow的SQL語(yǔ)句。CREATE TABLE student(ReaderId char (12) PRIMARY KEY、Sname v

6、archar (10)非空，Ssex char (2) default男人，Sbirthday datetime、Saddress varchar (50)，以獲取詳細(xì)信息CREATE TABLE book(Bno varchar(20) PRIMARY KEY、Bname varchar(20)非空，Bauthor varchar(20)、Bprice tinyint、Bcategory varchar(10)、Bpressday datetime、Bpress varchar(10)，以獲取詳細(xì)信息CREATE TABLE borrow(Bno varchar(20)、ReaderId c

7、har(12)、Borrowday datetime、Returnday datetime、主密鑰(bno，reader id，borrow day)、FOREIGN KEY (Bno)參考手冊(cè)(Bno)、FOREIGN KEY (ReaderId)REFERENCES student(ReaderId)，以獲取詳細(xì)信息2.查詢每個(gè)學(xué)生基本信息。按學(xué)號(hào)升序排序。Select * from studentOrder by ReaderId3.查詢各學(xué)生貸款情況(學(xué)號(hào)、姓名、序號(hào)、貸款日期、返還日期)。這包括沒(méi)有借書(shū)的學(xué)生信息。Select S. readerid、sname、bno、borrow

8、 day、return dayfrom student s left join borrow on s . reader id=borrow . reader id4.查詢各書(shū)借出的情況，包括未借出的圖書(shū)。select * from book b left join borrow on b . bno=borrow . bno5.查詢同名的書(shū)。Select distinct b1.bno，B1 . bname from B1 join book B2 on B1 . name=B2 . name6.查詢高等教育出版社出版的圖書(shū)借閱的學(xué)生信息。select * from student s jo

9、in on borrow on s . reader id=borrow . reader idJoin on book b on b.bno=borrow.bnoWhere bpress=高等教育出版社7.查詢沒(méi)有借來(lái)的書(shū)的學(xué)生信息。Select * from studentwhere reader id not in(select distinct reader id from borrow)8.查詢圖書(shū)的總書(shū)目、最高價(jià)、最低價(jià)、總價(jià)值、和平平均價(jià)。Select count (bno)、max (bprice)、min (bprice)、sum (bprice)和avg (bprice)從

10、書(shū)(From book)9.查詢定價(jià)在30韓元到40韓元之間的圖書(shū)信息。Select * from bookWhere bprice between 30 and 4010.查詢書(shū)名以“數(shù)據(jù)庫(kù)”開(kāi)頭的圖書(shū)信息。Select * from bookWhere bname like數(shù)據(jù)庫(kù)%11.統(tǒng)計(jì)各出版社的圖書(shū)數(shù)量。選擇計(jì)數(shù)(bno)從書(shū)(From book)Group by bpress12.統(tǒng)計(jì)每本書(shū)的貸款次數(shù)。選擇計(jì)數(shù)(bno)來(lái)自borrow群組鄭智薰bno13.按價(jià)錢(qián)降序查詢每本書(shū)的基本信息。Select * from book按bprice desc訂購(gòu)14.計(jì)算每個(gè)學(xué)生的圖書(shū)借閱數(shù)

11、。(包括已經(jīng)歸還的書(shū)和借來(lái)的書(shū))選擇計(jì)數(shù)(ReaderId)來(lái)自borrow組by reader id15.人民郵政出版社出版的圖書(shū)價(jià)錢(qián)減少了3元。Update bookSet bprice=bprice-3Where bpress=人民郵政出版社16.刪除沒(méi)有借閱者的圖書(shū)信息。Delete from bookwhere bno not in(select distinct bno from borrow)17.刪除標(biāo)題為“數(shù)據(jù)庫(kù)基礎(chǔ)和應(yīng)用”的圖書(shū)信息。Delete from bookWhere bname=數(shù)據(jù)庫(kù)基礎(chǔ)知識(shí)和應(yīng)用程序18.生成可以查詢每個(gè)男生的貸款情況(學(xué)號(hào)、書(shū)號(hào)、貸款日期、返還日期)的視圖。然后使用此視圖計(jì)算每個(gè)男孩的書(shū)數(shù)。(包括已經(jīng)歸還的書(shū)和借來(lái)