圖書管理系統(tǒng)數(shù)據(jù)庫(kù)設(shè)計(jì)——SQL

1、數(shù)據(jù)庫(kù)課程設(shè)計(jì)課設(shè)名稱： 圖書管理系統(tǒng)數(shù)據(jù)庫(kù)設(shè)計(jì)與實(shí)現(xiàn) 年 級(jí)： 專 業(yè)： 網(wǎng)絡(luò)工程 班 級(jí)： 姓 名： 學(xué) 號(hào)： 成 績(jī)： 指導(dǎo)教師： 顏 穎 提交報(bào)告時(shí)間： 2015 年 1 月 14 日數(shù)據(jù)需求圖書館管理信息系統(tǒng)需要完成功能主要有：1. 讀者基本信息的輸入，包括借書證編號(hào)、讀者姓名、讀者性別登記日期。2讀者基本信息的查詢、修改，包括讀者借書證編號(hào)、讀者姓名、性別等。3書籍類別標(biāo)準(zhǔn)的制定、類別信息的輸入，包括類別編號(hào)、類別名稱。4書籍類別信息的查詢、修改，包括類別編號(hào)、類別名稱。5書籍庫(kù)存信息的輸入，包括書籍編號(hào)、書籍名稱、書籍類別編號(hào)、作者、出版社、出版日期、登記日期，價(jià)格，是否可借。6

2、書籍庫(kù)存信息的查詢，修改，包括書籍編號(hào)、書籍名稱、書籍類別編號(hào)、姓名、出版社、出版日期、登記日期、價(jià)格、是否可借等。7借書信息的輸入，包括讀者借書證號(hào)、書籍編號(hào)、借書日期，應(yīng)還時(shí)間。8借書信息的查詢、修改，包括借書證編號(hào)、讀者編號(hào)、讀者姓名、書籍編號(hào)、書籍名稱、借書日期等。9還書信息的輸入，包括借書證編號(hào)、書籍編號(hào)、還書日期。 10還書信息的查詢和修改，包括還書讀者借書證編號(hào)、讀者姓名、書籍編號(hào)、書籍名稱、借書日期、還書日期等。11超期還書罰款輸入，還書超出期限包括超出期限還書的讀者借閱證號(hào)，書籍編號(hào)，罰款金額。12.超期還書罰款查詢，刪除，包括讀者借書證編號(hào)、讀者姓名、書籍編號(hào)、書籍名稱，應(yīng)

3、還時(shí)間，罰款金額，借閱時(shí)間，超期時(shí)間等事物需求(1)在讀者信息管理部分,要求:a.可以查詢讀者信息。b.可以對(duì)讀者信息進(jìn)行添加及刪除的操作。(2 )在書籍信息管理部分,要求:a.可以瀏覽書籍信息,要求:b.可以對(duì)書籍信息進(jìn)行維護(hù),包括添加及刪除的操作。(3)在借閱信息管理部分,要求:。a.可以瀏覽借閱信息。b.可以對(duì)借閱信息進(jìn)行維護(hù)操作。(4)在歸還信息管理部分，要求:a.可以瀏覽歸還信息b.對(duì)歸還信息可修改維護(hù)操作(5)在管理者信息管理部分,要求:a.顯示當(dāng)前數(shù)據(jù)庫(kù)中管理者情況。b.對(duì)管理者信息維護(hù)操作。(6)在罰款信息管理部分,要求:a.可以瀏覽罰款信息b.對(duì)罰款信息可以更新(7) 在書籍

4、類別管理部分，要求：A. 可以瀏覽書籍類別信息B.對(duì)書籍類別信息可以更新(8) 在系部信息管理部分，要求：B. 可以系部信息B.對(duì)系部信息可以進(jìn)行增刪改操作關(guān)系模式(一) 書籍類別（書籍類別編號(hào)，類別名稱）(二) 借閱者信息實(shí)體（借閱證號(hào)，姓名，性別，登記時(shí)期，讀者類別）(三) 學(xué)生實(shí)體（讀者類別，學(xué)號(hào)，借閱數(shù)，專業(yè)，電話）(四) 教師實(shí)體（讀者類別，職位，工號(hào)，借閱數(shù)，電話）(五) 書籍（書籍編號(hào)，書籍名稱，書籍類別編號(hào)，作者，出版社，出版日期，價(jià)格，登記日期，是否可借）(六) 借閱（借閱證號(hào)，書籍編號(hào)，借閱時(shí)間時(shí)間，應(yīng)還時(shí)間）(七) 還書（借閱證號(hào)，書籍編號(hào)，還書時(shí)間）(八) 罰款（借閱證

5、號(hào)，姓名，書籍名稱，書籍編號(hào)，借閱時(shí)間，應(yīng)還時(shí)間，還書時(shí)間，罰款金額）(九) 系部（系部名稱，系部編號(hào)）(十) 讀者類別表（讀者類別編號(hào)，讀者類別名稱）E/R圖總的信息實(shí)體E-R圖數(shù)據(jù)字典表-1 book_sytle 書籍類別信息表表中列名數(shù)據(jù)類型可否為空說明bookstylenoVarchar(20)not null(主鍵)書籍類別編號(hào)bookstyleVarchar(30)not null種類名稱表-2system_readers借閱者信息表格表中列名數(shù)據(jù)類型可否為空說明readeridVarchar(9)not null(主鍵)借閱證號(hào)readernameVarchar(9)not nu

6、ll讀者姓名readersexVarchar(2)not null讀者性別readertypeVarchar(10)Not null讀者類別regdatedatetimenull登記日期表2-3system_books書籍信息表表中列名數(shù)據(jù)類型可否為空說明bookidVarchar(20)Not null(主鍵)書籍編號(hào)priceVarchar(6)Not null價(jià)格booknameVarchar(30)Not null書籍名稱BookstylenoVarchar(20)Not null書籍類別編號(hào)bookauthorVarchar(30)Not null作者isborrowedVarcha

7、r(1)Not null是否可借bookpubVarchar(30)Null出版社bookpubdateDatetimeNull出版日期bookindateDatetimeNull登記日期表2-4borrow_record 借閱記錄信息表表中列名數(shù)據(jù)類型可否為空說明readeridVarchar(9)Not null(外主鍵)借閱證號(hào)bookidVarchar(20)Not null(外主鍵)書籍編號(hào)borrowdatedatetimeNot null借閱時(shí)間shouldreturndatetime?應(yīng)還時(shí)間表2-5return_record 還書記錄信息表表中列名數(shù)據(jù)類型可否為空說明read

8、eridVarchar(9)Not null(外主鍵)借閱證號(hào) bookidVarchar(20)Not null(外主鍵)書籍編號(hào)returndatedatetimeNot null讀者還書時(shí)間表2-6reader_fee 罰款記錄信息表readeridVarchar（9）Not null（外主鍵）借閱證號(hào)readernameVarchar(9)Not null讀者姓名bookidVarchar(20)Not null(外主鍵)書籍編號(hào)booknameVarchar(30)Not null書籍名稱bookfeeVarchar(10)Not Null罰款金額borrowdatedatetime

9、Not Null借閱時(shí)間shouldreturndatetime null應(yīng)還時(shí)間exceeddatedatetime null超期時(shí)間表2-7 system_student 學(xué)生實(shí)體信息表表中列名數(shù)據(jù)類型可否為空說明studentnoVarchar(20)not null(主鍵)學(xué)號(hào)majorVarchar(30)not null專業(yè)borrownumIntnot null借閱數(shù)readertypeVarchar(10)Not null讀者類別phoneVarchar(20)null電話表2-8 system_teacher 教師實(shí)體信息表表中列名數(shù)據(jù)類型可否為空說明teachertnoVa

10、rchar（20）not null(主鍵)工號(hào)ProfessionVarchar（20）null職位borrownumIntnot null借閱數(shù)readertypeVarchar（20）Not null讀者類別phoneVarchar（20）null電話表2-9 System_department 系部信息表表中列名數(shù)據(jù)類型可否為空說明departmentnoVarchar(20)not null(主鍵)系部編號(hào)deparmentnameVarchar（20）not null系部名稱表2-10system_resdertype讀者類別表表中列名數(shù)據(jù)類型可否為空說明readertypenoVa

11、rchar(20)Not null(主鍵)讀者類別編號(hào)ReadertypeVarchar(20)Not null讀者類別名稱建表語句：1.書本類別表建立create table book_style( bookstyleno varchar(20) primary key, bookstyle varchar(30) not null)2.書籍表建立create table system_books( bookid varchar(20) primary key, bookname varchar(20) Not null, bookstyleno varchar(30) Not null,

12、price varchar(6) not null, bookauthor varchar(30) not null, isborrowed varchar(1) not null, bookpub varchar(30) , bookpubdate datetime, bookindate datetime , foreign key (bookstyleno) references book_style (bookstyleno),)3.借閱者表建立create table system_readers ( readerid varchar(9) primary key, readerna

13、me varchar(9) not null , readersex varchar(2) not null, readertype varchar(10) not null, regdate datetime)4. 借書記錄表建立create table borrow_record( bookid varchar(20) primary key, readerid varchar(9) not null, borrowdate datetime not null ,shouldreturn datetime, foreign key (bookid) references system_bo

14、oks(bookid), foreign key (readerid) references system_readers(readerid),)5.還書記錄表建立create table return_record( bookid varchar(20) primary key, readerid varchar(9)not null, returndate datetime not null, foreign key (bookid) references system_books(bookid), foreign key (readerid) references system_read

15、ers(readerid)6. 罰款單表建立*/create table reader_fee( readerid varchar(9)not null, readername varchar(9)not null , bookid varchar(20) primary key, bookname varchar(30) Not null, bookfee varchar(10) not null, borrowdate datetime not null, shouldreturn datetime, exceeddate varchar(5) , foreign key (bookid)

16、 references system_books(bookid), foreign key (readerid) references system_readers(readerid) )7.學(xué)生表建立create table system_student(studentno varchar(20) primary key,major varchar (30) not null,borrownum int not null,readertype varchar(20) ,phone varchar(20)8. 讀者類別表建立create table system_readertype(read

17、ertypeno varchar(20) primary key,readertype varchar (20) not null）9.系別表建立 create table system_department(departmentno varchar(20) primary key,departmentname varchar (20) not null)10教師表建立create table system_teacher(readerid varchar(9)not null,teacherno varchar(20) primary key,borrownum int not null,p

18、rofession varchar(20),readertype varchar(20) not null,phone varchar(20),foreign key (readerid) references system_readers (readerid)數(shù)據(jù)初始化及表更新、查詢1.向Book_style表中插入數(shù)據(jù)insert into book_style(bookstyleno,bookstyle)values('1','人文藝術(shù)類') insert into book_style(bookstyleno,bookstyle)values('

19、2','自然科學(xué)類')insert into book_style(bookstyleno,bookstyle)values('3','社會(huì)科學(xué)類')insert into book_style(bookstyleno,bookstyle)values('4','圖片藝術(shù)類')insert into book_style(bookstyleno,bookstyle)values('5','政治經(jīng)濟(jì)類')insert into book_style(bookstyleno,bo

20、okstyle)values('6','工程技術(shù)類')insert into book_style(bookstyleno,bookstyle)values('7','語言技能類')表單查詢：select * from book_style2.向system_books 表中插入數(shù)據(jù)：insert into system_books(bookid ,bookname, bookstyleno,bookauthor,bookpub,bookpubdate, bookindate, price,borrowednum,totalnum

21、 )values('00125415152','計(jì)算機(jī)組成原理','6','王愛英','清華大學(xué)出版社','2001-01-03','2003-11-15','35.5','3','10');insert into system_books(bookid ,bookname, bookstyleno,bookauthor,bookpub,bookpubdate, bookindate, price,borrowednum,totalnu

22、m )values('00456456','數(shù)據(jù)庫(kù)原理','6','薩師煊','高等教育出版社','2007-07-02','2007-09-15','40','4','10');insert into system_books(bookid ,bookname, bookstyleno,bookauthor,bookpub,bookpubdate, bookindate, price,borrowednum,totalnum )val

23、ues('12215121','C程序設(shè)計(jì)','6','譚浩強(qiáng)','清華大學(xué)出版社','2002-04-02','2004-03-14','60','5','8');insert into system_books(bookid ,bookname, bookstyleno,bookauthor,bookpub,bookpubdate, bookindate, price,borrowednum,totalnum )values(

24、9;9787308020558','計(jì)算機(jī)體系結(jié)構(gòu)','6','石教英','浙江大學(xué)出版社','2004-10-03','2006-11-15','60','5','8');insert into system_books(bookid ,bookname, bookstyleno,bookauthor,bookpub,bookpubdate, bookindate, price,borrowednum,totalnum )values(

25、9;45456141414','數(shù)據(jù)結(jié)構(gòu)（C語言版）','6','吳偉民，嚴(yán)蔚敏','清華大學(xué)出版社','2002-06-28','2004-01-21','40','5','10');insert into system_books(bookid ,bookname, bookstyleno,bookauthor,bookpub,bookpubdate, bookindate, price,borrowednum,totalnum )valu

26、es('545551523','中華歷史年','1','吳強(qiáng)','北京大學(xué)出版社','2005-04-03','2006-05-15','56','0','10');insert into system_books(bookid ,bookname, bookstyleno,bookauthor,bookpub,bookpubdate, bookindate, price,borrowednum,totalnum )values(

27、9;151451424','日本文化','1','吳小鵬','北京大學(xué)出版社','2002-04-02','2004-03-14','35','0','10');insert into system_books(bookid ,bookname, bookstyleno,bookauthor,bookpub,bookpubdate, bookindate, price,borrowednum,totalnum )values('1515

28、46564','微觀經(jīng)濟(jì)學(xué)','5','李小剛','北京大學(xué)出版社','2000-10-03','2001-11-15','35','0','10');insert into system_books(bookid ,bookname, bookstyleno,bookauthor,bookpub,bookpubdate, bookindate, price,borrowednum,totalnum )values('565833422

29、','影視文學(xué)','4','蘇慶東','北京大學(xué)出版社','1999-02-28','2000-01-21','35','0','10');insert into system_books(bookid ,bookname, bookstyleno,bookauthor,bookpub,bookpubdate, bookindate, price,borrowednum,totalnum )values('565800020',

30、'探索宇宙奧秘','2','蘇慶東','北京大學(xué)出版社','1999-02-28','2000-01-21','35','0','10');表單查詢：select * from system_books3.向讀者表中插入數(shù)據(jù)：insert into system_readers(readerid,readername,readersex,readertype,regdate)values('X05620207','陳飛'

31、,'男','1','2005-9-23 14:23:56')insert into system_readers(readerid,readername,readersex,readertype,regdate)values('X05620206','張三','男','1','2005-09-30 13:24:54.623')insert into system_readers(readerid,readername,readersex,readertype,regd

32、ate)values('X05620204','趙靜','女','1','2005-09-27 11:24:54.123')insert into system_readers(readerid,readername,readersex,readertype,regdate)values('X05620202','潘小虹','女','1','2005-09-30 13:24:54.473')insert into system_read

33、ers(readerid,readername,readersex,readertype,regdate)values('008415','蔣偉','男','2','2004-04-30 09:24:54.478')insert into system_readers(readerid,readername,readersex,readertype,regdate)values('001456','李風(fēng)','女','2','2004-04-30

34、 09:24:54.478')表單查詢：select * from system_readers4.（insert，update ,set）向借書記錄表中加入數(shù)據(jù)：insert into borrow_record(bookid,readerid,borrowdate,shouldreturn)values('545551523','X05620207','2007-09-27 11:24:54.123','2007-10-27 11:24:54.123')update system_booksset isborrowed

35、=0 where bookid='545551523' and isborrowed='1'insert into borrow_record(bookid,readerid,borrowdate,shouldreturn)values('151546564','X05620204','2014-09-03 10:24:54.123','2014-10-03 10:24:54.123')update system_booksset isborrowed=0where bookid='1515

36、46564' and isborrowed='1'insert into borrow_record(bookid,readerid,borrowdate,shouldreturn)values('151451424','001456','2014-09-03 10:24:54.123','2014-12-03 10:24:54.123')update system_booksset isborrowed=0.where bookid='151451424' and isborrowed=&

37、#39;1'5向?qū)W生表中加入數(shù)據(jù)insert into system_student (readerid , studentno,readertype ,major ,borrownum ,phone )values('X05620207','1125111001','1','計(jì)算機(jī)','30','1234567890')insert into system_student (readerid ,studentno ,readertype ,major ,borrownum ,phone )

38、values('X05620206','1125111002','1','計(jì)算機(jī)','30','1234567890')insert into system_student (readerid ,studentno ,readertype ,major ,borrownum ,phone )values('X05620202','1122111001','1','哲學(xué)與社會(huì)','30','1234567890&#

39、39;)insert into system_student (readerid ,studentno ,readertype ,major ,borrownum ,phone )values('X05620204','112011100','1','國(guó)際經(jīng)濟(jì)與貿(mào)易','30','1234567890')(DELETE)刪除學(xué)生操作：delete from system_student where readerid= 'X05620204' 系部信息表：6.向教師表中加入數(shù)據(jù)：ins

40、ert into system_teacher(readerid , teacherno,readertype , profession ,borrownum ,phone )values('001456','12022301','2','數(shù)據(jù)庫(kù)教師','40','1234567890')insert into system_teacher(readerid , teacherno,readertype ,profession ,borrownum ,phone )values('00841

41、5','12022302','2','C語言教師','40','1234567890')7. (DISTINCT,多表查詢)查詢所有書所對(duì)應(yīng)的類別：8.向罰款表中添加數(shù)據(jù)操作(超期1天罰款0.3元)：【INSERT,多表查詢，DATEDIFF(),GETDATE(),CONVERT(),ADD】insert into reader_fee(readerid,readername,bookid,bookname,bookfee,borrowdate,shouldreturn ,returndate )sele

42、ct system_readers.readerid 讀者借書證編號(hào) ,readername 讀者姓名,system_books.bookid 書籍編號(hào),bookname 書名,0.3*(Datediff(day,convert(smalldatetime,borrowdate),getdate()-30) 超過時(shí)間天數(shù),borrowdate 借書時(shí)間, shouldreturn 應(yīng)還時(shí)間 , returndate 還書時(shí)間 from borrow_record ,system_readers ,system_books ,return_record where system_readers.

43、readerid=borrow_record.readerid and system_books.bookid=borrow_record.bookidand Datediff(day,convert(smalldatetime,borrowdate),getdate()>=309.創(chuàng)建索引：【INDEX】create index keyindex on borrow_record (bookid,readerid)刪除索引：DROP INDEX keyindex on borrow_record 10應(yīng)用distinct查詢表select distinct readerid from

44、borrow_record 11.應(yīng)用COUNT統(tǒng)計(jì)表單數(shù)據(jù): select COUNT (readerid) from borrow_record group by readerid12.應(yīng)用count統(tǒng)計(jì)某學(xué)生節(jié)約的書籍總數(shù)select COUNT (readerid) from borrow_record where readerid ='X05620201'13.多表查詢（查詢有借書的學(xué)生的學(xué)號(hào)，姓名，讀者類型，可借數(shù)，專業(yè)以及所借書籍的編號(hào)）select system_readers.readerid,studentno,readername,system_reade

45、rs.readertype,borrownum,majorfrom system_readers ,system_student where system_readers.readerid=system_student.readerid 14. 使用GROUP BY , HAVING子句（查詢有借過書的同學(xué)的學(xué)號(hào)和剩余可借數(shù)）select distinct studentno , borrownumfrom system_student group by studentno ,borrownum having borrownum <3015. 使用ORDER BY子句（查詢書籍，以價(jià)格從低到高排序）select *from system_books order by price 16.嵌套查詢，引入IN的用法：select * from system_books where bookpub in ('北京大學(xué)出版社','清華大學(xué)出版社')查詢出版社為“清華大學(xué)出版社”的書