圖書管理系統(tǒng)數(shù)據(jù)庫設(shè)計(jì)——SQL_第1頁
圖書管理系統(tǒng)數(shù)據(jù)庫設(shè)計(jì)——SQL_第2頁
圖書管理系統(tǒng)數(shù)據(jù)庫設(shè)計(jì)——SQL_第3頁
圖書管理系統(tǒng)數(shù)據(jù)庫設(shè)計(jì)——SQL_第4頁
圖書管理系統(tǒng)數(shù)據(jù)庫設(shè)計(jì)——SQL_第5頁
已閱讀5頁,還剩17頁未讀 繼續(xù)免費(fèi)閱讀

下載本文檔

版權(quán)說明:本文檔由用戶提供并上傳,收益歸屬內(nèi)容提供方,若內(nèi)容存在侵權(quán),請進(jìn)行舉報(bào)或認(rèn)領(lǐng)

文檔簡介

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

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

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

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

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

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

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

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

9、Not Null借閱時間shouldreturndatetime null應(yīng)還時間exceeddatedatetime null超期時間表2-7 system_student 學(xué)生實(shí)體信息表表中列名數(shù)據(jù)類型可否為空說明studentnoVarchar(20)not null(主鍵)學(xué)號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(主鍵)工號ProfessionVarchar(20)null職位borrownumIntnot null借閱數(shù)readertypeVarchar(20)Not null讀者類別phoneVarchar(20)null電話表2-9 System_department 系部信息表表中列名數(shù)據(jù)類型可否為空說明departmentnoVarchar(20)not null(主鍵)系部編號deparmentnameVarchar(20)not null系部名稱表2-10system_resdertype讀者類別表表中列名數(shù)據(jù)類型可否為空說明readertypenoVa

11、rchar(20)Not null(主鍵)讀者類別編號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','社會科學(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ù)庫原理','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é)與社會','30','1234567890&#

39、39;)insert into system_student (readerid ,studentno ,readertype ,major ,borrownum ,phone )values('X05620204','112011100','1','國際經(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ù)庫教師','40','1234567890')insert into system_teacher(readerid , teacherno,readertype ,profession ,borrownum ,phone )values('00841

41、5','12022302','2','C語言教師','40','1234567890')7. (DISTINCT,多表查詢)查詢所有書所對應(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 讀者借書證編號 ,readername 讀者姓名,system_books.bookid 書籍編號,bookname 書名,0.3*(Datediff(day,convert(smalldatetime,borrowdate),getdate()-30) 超過時間天數(shù),borrowdate 借書時間, shouldreturn 應(yīng)還時間 , returndate 還書時間 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é)號,姓名,讀者類型,可借數(shù),專業(yè)以及所借書籍的編號)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é)號和剩余可借數(shù))select distinct studentno , borrownumfrom system_student group by studentno ,borrownum having borrownum <3015. 使用ORDER BY子句(查詢書籍,以價格從低到高排序)select *from system_books order by price 16.嵌套查詢,引入IN的用法:select * from system_books where bookpub in ('北京大學(xué)出版社','清華大學(xué)出版社')查詢出版社為“清華大學(xué)出版社”的書

溫馨提示

  • 1. 本站所有資源如無特殊說明,都需要本地電腦安裝OFFICE2007和PDF閱讀器。圖紙軟件為CAD,CAXA,PROE,UG,SolidWorks等.壓縮文件請下載最新的WinRAR軟件解壓。
  • 2. 本站的文檔不包含任何第三方提供的附件圖紙等,如果需要附件,請聯(lián)系上傳者。文件的所有權(quán)益歸上傳用戶所有。
  • 3. 本站RAR壓縮包中若帶圖紙,網(wǎng)頁內(nèi)容里面會有圖紙預(yù)覽,若沒有圖紙預(yù)覽就沒有圖紙。
  • 4. 未經(jīng)權(quán)益所有人同意不得將文件中的內(nèi)容挪作商業(yè)或盈利用途。
  • 5. 人人文庫網(wǎng)僅提供信息存儲空間,僅對用戶上傳內(nèi)容的表現(xiàn)方式做保護(hù)處理,對用戶上傳分享的文檔內(nèi)容本身不做任何修改或編輯,并不能對任何下載內(nèi)容負(fù)責(zé)。
  • 6. 下載文件中如有侵權(quán)或不適當(dāng)內(nèi)容,請與我們聯(lián)系,我們立即糾正。
  • 7. 本站不保證下載資源的準(zhǔn)確性、安全性和完整性, 同時也不承擔(dān)用戶因使用這些下載資源對自己和他人造成任何形式的傷害或損失。

評論

0/150

提交評論