數(shù)據(jù)庫課程設(shè)計 書刊租借信息管理系統(tǒng)

1、數(shù)據(jù)庫系統(tǒng)原理課程設(shè)計數(shù)據(jù)庫系統(tǒng)原理課程設(shè)計課題名稱：課題名稱： 書刊租借信息管理系統(tǒng)書刊租借信息管理系統(tǒng) 姓姓 名：名： 班班 級：級： 計算機計算機 11021102 學(xué)學(xué) 號：號： 311109010225311109010225 指導(dǎo)老師：指導(dǎo)老師： 2013 年 9 月 10 日1目錄目錄一、系統(tǒng)需求分析一、系統(tǒng)需求分析 . 1 11.1 背景 . 11.2 系統(tǒng)主要功能 . 11.3 數(shù)據(jù)流程圖（DFD）. 2二、概念結(jié)構(gòu)設(shè)計二、概念結(jié)構(gòu)設(shè)計 . 3 32.1 書刊信息 E-R 圖 . 32.2 會員信息 E-R 圖 . 32.3 管理員信息 E-R 圖 . 32.4 書刊租借信息

2、管理系統(tǒng)總 E-R 圖 . 4三、邏輯結(jié)構(gòu)設(shè)計三、邏輯結(jié)構(gòu)設(shè)計 . 6 63.1 關(guān)系模式 . 6四、表結(jié)構(gòu)定義四、表結(jié)構(gòu)定義 . 6 6五、數(shù)據(jù)實施和維護五、數(shù)據(jù)實施和維護 . 8 85.1 創(chuàng)建數(shù)據(jù)庫 . 85.2 數(shù)據(jù)初始化 . 115.3 單表查詢 . 155.4 數(shù)據(jù)查詢，更新及觸發(fā)器，存儲過程的創(chuàng)建 . 17六、設(shè)計小結(jié)六、設(shè)計小結(jié) . 2626七、參考文獻七、參考文獻 . 27272一、一、 系統(tǒng)需求分析系統(tǒng)需求分析1.11.1 背景背景書刊租借工作繁瑣，借閱頻繁，包含大量的信息數(shù)據(jù)，面對著如此龐大的信息量，傳統(tǒng)的人工方式管理會導(dǎo)致書刊租借管理上的混亂，人力與物力過多浪費，因此，

3、就需要一個合理、有效，規(guī)范和實用的書刊租借信息管理系統(tǒng)來實現(xiàn)對這些數(shù)據(jù)的有效管理。提高書刊租借管理工作效率，作到信息的規(guī)范管理，科學(xué)統(tǒng)計和快速查詢，更方便于顧客的租借。1.21.2 系統(tǒng)主要功能系統(tǒng)主要功能1. 會員基本信息的輸入，包括會員證號、姓名、性別，年齡，電話，地址，已借書數(shù)等。2會員基本信息的查詢、修改，包括會員證號、姓名、性別，年齡，電話，地址，已借書數(shù)等。3書刊庫存信息的輸入，包括書刊編號，書刊名稱，作者，出版社，出版日期，類別，年份，庫存量，借出量。4書刊庫存信息的查詢，修改，包括書刊編號，書刊名稱，作者，出版社，出版日期，類別，年份，庫存量，借出量。5借書信息的輸入，包括會員

4、證號，書刊編號，借書日期，借書天數(shù)，租借費用。6借書信息的查詢、修改，包括會員證號，書刊編號，借書日期，借書天數(shù)，租借費用。7還書信息的輸入，包括會員證號，書刊編號，還書日期。 8還書信息的查詢和修改，包括還書會員證號、會員姓名、書刊編號、書刊名稱、借書日期、還書日期等。9超期還書罰款輸入，還書超出期限包括超出期限還書的會員證號，管理員編號，書刊編號，借書日期，超期罰款。10超期還書罰款查詢，刪除，包括會員證號、會員姓名、書刊編號、書刊名稱，超期罰款等。1.31.3 數(shù)據(jù)流程圖（數(shù)據(jù)流程圖（DFDDFD）3管理員登記會員信息管理書刊登記會員登記書刊信息管理管理員信息管理數(shù)據(jù)庫數(shù)據(jù)庫數(shù)據(jù)庫數(shù)據(jù)庫

5、數(shù)據(jù)庫借閱信息管理基本信息錄入基本信息錄入書刊信息返回借閱信息錄入超期罰款會員信息返回基本信息錄入歸還信息錄入數(shù)據(jù)庫二、二、 概念結(jié)構(gòu)設(shè)計概念結(jié)構(gòu)設(shè)計2.12.1 書刊信息書刊信息 E-RE-R 圖：圖：書刊信息書刊編號書刊名稱出版日期庫存量借出量出版社類別作者42.22.2 會員信息會員信息 E-RE-R 圖：圖：會員信息會員證號已借書數(shù)年齡最大借書數(shù)賬戶余額性別地址姓名電話2.32.3 管理員信息管理員信息 E-RE-R 圖：圖：管理員管理員編號姓名電話地址會員信息租借書刊信息MN書刊編號會員證號租借費用借書天數(shù)借書日期shijian歸還時間shijian52.42.4 書刊租借信息管理系

6、統(tǒng)總書刊租借信息管理系統(tǒng)總 E-RE-R 圖：圖：會員信息會員證號已借書數(shù)年齡最大借書數(shù)賬戶余額性別地址姓名電話書刊信息書刊編號書刊名稱出版日期庫存量借出量出版社類別作者租借租借費用借書天數(shù)借書日期shijian書刊編號會員證號管理員管理員編號姓名電話地址管理罰款MN1NMN歸還日期6三、邏輯結(jié)構(gòu)設(shè)計三、邏輯結(jié)構(gòu)設(shè)計3.13.1 關(guān)系模式：關(guān)系模式：通過 ER 模型到關(guān)系模型的轉(zhuǎn)化，可以得到如下關(guān)系模式：會員信息（會員證號，姓名，性別，年齡，電話，地址，最大借書數(shù)，已借書數(shù)，賬戶余額）管理員（管理員編號，姓名，地址，電話）書刊信息（書刊編號，書刊名稱，作者，出版社，出版日期，類別，庫存量，借出

7、量）書刊借閱（會員證號，書刊編號，借書日期，借書天數(shù)，租借費用， ）書刊歸還（會員證號，書刊編號，還書日期）罰款（會員證號，管理員編號，借書日期，超期罰款）四、四、 表結(jié)構(gòu)定義表結(jié)構(gòu)定義userinf 管理員信息表列名數(shù)據(jù)類型可否為空說明useridchar(7)NOT NULL（主鍵）管理員編號usernamechar(20)NOT NULL姓名useradresschar(30)NULL地址usertelchar(12)NOT NULL電話borrow_record 書刊借閱表列名數(shù)據(jù)類型可否為空說明memberidchar(7)NOT NULL會員證號bookidchar(7)NOT N

8、ULL書刊編號borrowdateDateTimeNOT NULL借書日期borrownumintNOT NULL借書天數(shù)bpricefloatNOT NULL租借費用7system_books 書刊信息表列名數(shù)據(jù)類型可否為空說明bookidchar(7)NOT NULL書刊編號booknamevarchar(40)NOT NULL書刊名稱bookstylechar(10)NOT NULL類別bookpubdateDatetimeNULL出版日期bookpubchar(50)NULL出版社bookauthorchar(20)NOT NULL作者stockcountintNOT NULL庫存量b

9、orrowcchar(2)NOT NULL借出量memberinf 會員信息表列名數(shù)據(jù)類型可否為空說明membernamechar(20)NOT NULL會員姓名memberidchar(7)NOT NULL會員證號membertelchar(12)NOT NULL聯(lián)系電話memberadressvarchar(30)NOT NULL地址membersexchar(7)NULL性別memberageintNULL年齡maxnumintNOT NULL最大借書數(shù)bbcountintNOT NULL已借書數(shù)balancefloatNOT NULL賬戶余額return_record 書刊歸還表列名數(shù)

10、據(jù)類型可否為空說明memberidchar(7)NOT NULL會員證號bookidchar(7)NOT NULL書刊編號returndateDateTimeNOT NULL還書日期8member_fee 罰款記錄表列名數(shù)據(jù)類型可否為空說明memberidchar(7)NOT NULL會員證號bookidchar(7)NOT NULL書刊編號borrowdateDateTimeNOT NULL借書日期useridchar(7)NULL管理員編號bookfeefloatNOT NULL罰款金額五、五、 數(shù)據(jù)實施和維護數(shù)據(jù)實施和維護5.15.1 數(shù)據(jù)庫建立：數(shù)據(jù)庫建立：5.1.15.1.1 創(chuàng)建數(shù)

11、據(jù)庫創(chuàng)建數(shù)據(jù)庫CREATE DATABASE bookstoresystemON ( NAME = bookstoresystem, FILENAME = d:bookstoresystem.mdf, SIZE = 10, MAXSIZE = 50, FILEGROWTH = 5 )LOG ON( NAME = bookstore, FILENAME = d:bookstoresystem.ldf, SIZE = 5MB, MAXSIZE = 25MB, FILEGROWTH = 5MB )GO5.1.25.1.2 創(chuàng)建基本表創(chuàng)建基本表1.管理員信息表 create table userinf

12、(useridchar(7)NOT NULL primary key,username char(20) NOT NULL,useradress char(30)NULL,usertelchar(12) NOT NULL)92. 創(chuàng)建書刊信息表create table system_books(bookid char(7) NOT NULL primary key,bookname varchar(40) NOT NULL,bookstyle char(10) NOT NULL,bookpubdateDatetime NULL,bookpubchar(50) NULL,bookauthor c

13、har(20) NOT NULL,stockcount int NOT NULL,borrowcchar(2) NOT NULL)3. 創(chuàng)建會員信息表create table memberinf(memberid char(7) NOT NULL primary key,membername char(20)NOT NULL,membertel char(12) NOT NULL,memberadress varchar(30) NOT NULL,membersex char(7) NULL,memberage int NULL,maxnum int NOT NULL,bbcount intN

14、OT NULL,10balancefloat NOT NULL)4. 創(chuàng)建書刊借閱表create table borrow_record( bookid char(7) NOT NULL primary key, memberid char(7) NOT NULL, borrowdate datetime NOT NULL, borrownumintNOT NULL, bprice float NOT NULL, foreign key (bookid) references system_books(bookid), foreign key (memberid) references mem

15、berinf(memberid),)5. 創(chuàng)建書刊歸還表create table return_record( bookid char(7) primary key, memberid char(7), returndate datetime, foreign key (bookid) references system_books(bookid), foreign key (memberid) references memberinf(memberid),)6.創(chuàng)建罰款單表create table member_fee(11memberid char(7) NOT NULL,bookid c

16、har(7) NOT NULL primary key,borrowdate DateTimeNOT NULL,userid char(7) NULL,bookfeefloat NOT NULL)5.25.2數(shù)據(jù)初始化：數(shù)據(jù)初始化：1. 將管理員信息加入userinfinsert into userinfvalues(2013003,李華,商丘夏邑駱集鄉(xiāng),6223736);insert into userinfvalues(2013006,言麗麗,商丘夏邑康復(fù)路,6222766);2.將已有的書刊加入system_booksinsert into system_books(bookid,boo

17、kname,bookstyle,bookpubdate,bookpub,bookauthor,stockcount,borrowc)values(2013013,計算機組成原理,工程技術(shù)類,2001-01-03,清華大學(xué)出版社,王愛英,8,3)insert into system_books(bookid,bookname,bookstyle,bookpubdate,bookpub,bookauthor,stockcount,borrowc)values(2013086,數(shù)據(jù)庫原理,工程技術(shù)類,2007-07-02,高等教育出版社,薩師煊,10,4)insert into system_boo

18、ks12(bookid,bookname,bookstyle,bookpubdate,bookpub,bookauthor,stockcount,borrowc)values(2013016,計算機組成原理,工程技術(shù)類,2001-01-03,清華大學(xué)出版社,王愛英,8,3)insert into system_books(bookid,bookname,bookstyle,bookpubdate,bookpub,bookauthor,stockcount,borrowc)values(2013081,C程序設(shè)計,工程技術(shù)類,2002-04-02,清華大學(xué)出版社,譚浩強,12,6)insert

19、into system_books(bookid,bookname,bookstyle,bookpubdate,bookpub,bookauthor,stockcount,borrowc)values(2013034,數(shù)據(jù)結(jié)構(gòu)（C語言版）,工程技術(shù)類,2002-06-28,清華大學(xué)出版社,吳偉民，嚴蔚敏,6,2)insert into system_books(bookid,bookname,bookstyle,bookpubdate,bookpub,bookauthor,stockcount,borrowc)values(5455515,中華歷史年,人文藝術(shù)類,2005-04-03,北京大學(xué)

20、出版社,吳強,6,1);insert into system_books(bookid,bookname,bookstyle,bookpubdate,bookpub,bookauthor,stockcount,borrowc)values(1546568,微觀經(jīng)濟學(xué),政治經(jīng)濟類,2000-10-03,北京大學(xué)出版社,李小剛,12,2)insert into system_books(bookid,bookname,bookstyle,bookpubdate,bookpub,bookauthor,stockcount,borrowc)values(0151153,古代埃及,社會科學(xué)類,2001-

21、02-02,北京大學(xué)出版社,趙文華,4,1)insert into system_books(bookid,bookname,bookstyle,bookpubdate,bookpub,bookauthor,stockcount,borrowc)values(1514514,日本文化,人文藝術(shù)類,2002-04-02,北京大學(xué)出版社,吳小鵬,7,0)insert into system_books(bookid,bookname,bookstyle,bookpubdate,bookpub,bookauthor,stockcount,borrowc)values(5658,影視文學(xué),圖片藝術(shù)類,

22、1999-02-28,北京大學(xué)出版社,蘇慶東,5,2)insert into system_books(bookid,bookname,bookstyle,bookpubdate,bookpub,bookauthor,stockcount,borrowc)values(5658020,探索宇宙奧秘,自然科學(xué)類,1999-02-28,北京大學(xué)出版社,蘇慶東,10,3)133.將已有的會員加入memberinfinsert into memberinf(membername,memberid,membertel,memberadress,membersex,memberage,maxnum,bbc

23、ount,balance)values(陳遠鵬,07,6213048,商丘夏邑,男,21,5,2,32)insert into memberinf(membername,memberid,membertel,memberadress,membersex,memberage,maxnum,bbcount,balance)values(李德,17,6234048,鄭州,男,31,7,3,62)insert into memberinf(membername,memberid,membertel,memberadress,membersex,memberage,maxnum,bbcount,bala

24、nce)values(趙銘靜,02,6223078,商丘,女,18,4,4,30)insert into memberinf(membername,memberid,membertel,memberadress,membersex,memberage,maxnum,bbcount,balance)values(李麗,37,6213565,開封,女,26,8,3,80)insert into memberinf(membername,memberid,membertel,memberadress,membersex,memberage,maxnum,bbcount,balance)values(

25、蔣偉,13,6233348,周口,男,24,6,2,18)insert into memberinf(membername,memberid,membertel,memberadress,membersex,memberage,maxnum,bbcount,balance)values(趙鑫,16,6218888,焦作,女,25,6,2,60)144. 添加借閱信息到borrow_record表insert into borrow_record(memberid,bookid,borrowdate,borrownum,bprice)values(17,2013013,2013-07-20,30

26、,5)insert into borrow_record(memberid,bookid,borrowdate,borrownum,bprice)values(02,2013081,2013-08-03,30,3)insert into borrow_record(memberid,bookid,borrowdate,borrownum,bprice)values(13,1546568,2013-09-02,30,4)insert into borrow_record(memberid,bookid,borrowdate,borrownum,bprice)values(16,2013034,2

27、013-08-16,30,7)insert into borrow_record(memberid,bookid,borrowdate,borrownum,bprice)values(17,0151153,2013-07-28,30,2)insert into borrow_record(memberid,bookid,borrowdate,borrownum,bprice)values(37,2013086,2013-09-02,30,5)5添加歸還信息到return_record表155.35.3單表查詢：單表查詢：5.3.15.3.1簡單查詢簡單查詢1.表userinf中查詢select

28、 *from userinf2.表ststem_books中查詢select *from system_books3.表memberinf中查詢select *from memberinf164表borrow_record和return_record中查詢select *from borrow_recordselect *from return_record5.查詢已借數(shù)量大于等于3會員的所有信息select *from memberinfwhere bbcount=3176.查詢借書會員的已借書天數(shù)select borrow_record.memberid 會員證號,membername 會

29、員姓名,bookname 書刊名稱,borrowdate 借書日期,datediff(day,convert(smalldatetime,borrowdate),getdate() 已借天數(shù)from borrow_record,memberinf,system_bookswhere borrow_record.bookid=system_books.bookid and borrow_record.memberid=memberinf.memberid7.查詢未被借出的書本select *from system_bookswhere bookid not in (select bookid f

30、rom borrow_record)5.45.4數(shù)據(jù)查詢，更新及觸發(fā)器，存儲過程的創(chuàng)建：數(shù)據(jù)查詢，更新及觸發(fā)器，存儲過程的創(chuàng)建：185.4.15.4.1創(chuàng)建觸發(fā)器創(chuàng)建觸發(fā)器1.在return_record表中建立一個插入觸發(fā)器(returnbook),功能：當還書操作時，要在還書記錄中添加一條記錄，同時刪除相應(yīng)書本的借閱記錄。SQL 語言：create trigger returnbookon return_recordafter insertasdeletefrom borrow_recordwhere borrow_record.bookid=(select borrow_record.b

31、ookid from borrow_record,inserted where borrow_record.bookid=inserted.bookid)2. 在return_record表中再建立一個插入觸發(fā)器(returnbook2)，功能：當還書操作時，同時對相應(yīng)表system_books， memberinf做相應(yīng)的改動，system_books的屬性：庫存量加1，借出量減1，memberinf的屬性：已借書數(shù)減1。SQL 語言：create trigger returnbook2on return_recordafter insertasupdate system_booksset

32、stockcount=stockcount+1,borrowc=borrowc-1where system_books.bookid in (select system_books.bookid from system_books,inserted where system_books.bookid=inserted.bookid) and system_books.borrowc0update memberinfset bbcount=bbcount-1where memberinf.memberid in (select memberinf.memberid from memberinf,

33、inserted where memberinf.memberid=inserted.memberid) and memberinf.bbcount0假設(shè)歸還書刊編號為2013086代碼insert into return_recordvalues(2013086,37,2013-9-6)19執(zhí)行前system_books表：執(zhí)行后system_books表：執(zhí)行前memberinf表：20執(zhí)行后memberinf表：3.在borrow_record表中建立一個插入觸發(fā)器(borrowbook),當借書操作時，要改變memberinf表的部分屬性，已借書數(shù)加 1，賬戶余額減去相應(yīng)的租借費用。S

34、QL 語言：create trigger borrowbookon borrow_recordafter insertasupdate memberinfset bbcount=bbcount-1,balance=balance-(select borrow_record.bprice from borrow_record,inserted where inserted.bookid=borrow_record.bookid)where memberinf.memberid in (select memberinf.memberid from memberinf,inserted where

35、memberinf.memberid=inserted .memberid)假設(shè)借書刊編號為5658代碼insert into borrow_recordvalues(5658,02,2013-09-01,30,5)21執(zhí)行代碼前memberinf表：執(zhí)行代碼后memberinf表：4.在borrow_record表中建立一個插入觸發(fā)器(borrowbook2),當借書操作時，要改變system_books表的部分屬性，庫存量減 1，借出量加 1。SQL 語言：create trigger borrowbook2on borrow_recordafter insertasupdate syst

36、em_booksset stockcount=stockcount-1,borrowc=borrowc+122where system_books.bookid in (select system_books.bookid from system_books,inserted where system_books.bookid=inserted .bookid)成功創(chuàng)建截圖5.4.25.4.2創(chuàng)建存儲過程：創(chuàng)建存儲過程：1.創(chuàng)建一個按照書刊類型查找該類型書刊的所有信息的存儲過程SQL 語言：create procedure bstyle類別char(10)asselect *from syst

37、em_bookswhere bookstyle=類別執(zhí)行存儲過程查詢工程技術(shù)類書刊信息232.創(chuàng)建一個當會員屬性已借書數(shù)等于最大借書數(shù)時，提示不能進行借書的存儲過程SQL 語言create procedure memer會員證號char(7),會員姓名char(20)asif(select bbcount from memberinf where memberid=會員證號)=(select maxnum from memberinf where memberid=會員證號)print 會員姓名+已借書數(shù)等于最大借書數(shù)，不能再借書elseprint 會員姓名+可以繼續(xù)借書執(zhí)行存儲過程分別查詢趙

38、銘靜，與陳遠鵬是否可以借書memberinf表對比圖：3創(chuàng)建一個存儲過程根據(jù)會員證號，查詢出會員借書信息24SQL 語句create procedure selectinf會員證號char(7)asselect memberinf.memberid 會員證號,membername 會員姓名, bbcount 已借書數(shù), balance 賬戶余額,system_books.bookid 書刊編號,bookname 書刊名稱,borrowdate 借書日期,borrownum 借書天數(shù),bprice 租借費用from borrow_record,system_books,memberinfwher

39、e borrow_record.bookid=system_books.bookid and borrow_record.memberid=memberinf.memberid and memberinf.memberid=會員證號查找會員證號為02,37,13借書信息exec selectinf 02exec selectinf 37exec selectinf 13參與對照表borrow_record表：25所創(chuàng)建的觸發(fā)器，存儲過程列表圖： 5.4.35.4.3超期處理：超期處理：1.對已借書的會員進行查詢借書是否超期（為了方便借書天數(shù)全為30）說明：當前的日期為2013年9月13日計算S

40、QL 語言：select memberinf.memberid 會員證號,memberinf.membername 會員姓名, system_books.bookid 書刊編號,bookname 書刊名稱,borrowdate 借書日期,datediff(day,convert(smalldatetime,borrowdate),getdate()-30 超出天數(shù)from borrow_record,memberinf,system_bookswhere borrow_record.bookid=system_books.bookid and borrow_record.memberid=memberinf.memberidand datediff(day,convert(smalldatetime,borrowdate),getdate()=30運行結(jié)果圖：26對照表borrow_record表的截圖：2.對超過天數(shù)的讀者進行罰款，將信息插入到member_fee表超出一天按0.2元扣除計算：inser