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

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

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

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

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

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

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

8、ULL書刊編號borrowdateDateTimeNOT NULL借書日期borrownumintNOT NULL借書天數bpricefloatNOT NULL租借費用7system_books 書刊信息表列名數據類型可否為空說明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 會員信息表列名數據類型可否為空說明membernamechar(20)NOT NULL會員姓名memberidchar(7)NOT NULL會員證號membertelchar(12)NOT NULL聯系電話memberadressvarchar(30)NOT NULL地址membersexchar(7)NULL性別memberageintNULL年齡maxnumintNOT NULL最大借書數bbcountintNOT NULL已借書數balancefloatNOT NULL賬戶余額return_record 書刊歸還表列名數

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

11、據庫創建數據庫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 創建基本表創建基本表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. 創建書刊信息表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. 創建會員信息表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. 創建書刊借閱表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. 創建書刊歸還表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.創建罰款單表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數據初始化：數據初始化：1. 將管理員信息加入userinfinsert into userinfvalues(2013003,李華,商丘夏邑駱集鄉,6223736);insert into userinfvalues(2013006,言麗麗,商丘夏邑康復路,6222766);2.將已有的書刊加入system_booksinsert into system_books(bookid,boo

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

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

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

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

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

22、1999-02-28,北京大學出版社,蘇慶東,5,2)insert into system_books(bookid,bookname,bookstyle,bookpubdate,bookpub,bookauthor,stockcount,borrowc)values(5658020,探索宇宙奧秘,自然科學類,1999-02-28,北京大學出版社,蘇慶東,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.查詢已借數量大于等于3會員的所有信息select *from memberinfwhere bbcount=3176.查詢借書會員的已借書天數select borrow_record.memberid 會員證號,membername 會

29、員姓名,bookname 書刊名稱,borrowdate 借書日期,datediff(day,convert(smalldatetime,borrowdate),getdate() 已借天數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數據查詢，更新及觸發器，存儲過程的創建：數據查詢，更新及觸發器，存儲過程的創建：185.4.15.4.1創建觸發器創建觸發器1.在return_record表中建立一個插入觸發器(returnbook),功能：當還書操作時，要在還書記錄中添加一條記錄，同時刪除相應書本的借閱記錄。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表中再建立一個插入觸發器(returnbook2)，功能：當還書操作時，同時對相應表system_books， memberinf做相應的改動，system_books的屬性：庫存量加1，借出量減1，memberinf的屬性：已借書數減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假設歸還書刊編號為2013086代碼insert into return_recordvalues(2013086,37,2013-9-6)19執行前system_books表：執行后system_books表：執行前memberinf表：20執行后memberinf表：3.在borrow_record表中建立一個插入觸發器(borrowbook),當借書操作時，要改變memberinf表的部分屬性，已借書數加 1，賬戶余額減去相應的租借費用。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)假設借書刊編號為5658代碼insert into borrow_recordvalues(5658,02,2013-09-01,30,5)21執行代碼前memberinf表：執行代碼后memberinf表：4.在borrow_record表中建立一個插入觸發器(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)成功創建截圖5.4.25.4.2創建存儲過程：創建存儲過程：1.創建一個按照書刊類型查找該類型書刊的所有信息的存儲過程SQL 語言：create procedure bstyle類別char(10)asselect *from syst

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

38、銘靜，與陳遠鵬是否可以借書memberinf表對比圖：3創建一個存儲過程根據會員證號，查詢出會員借書信息24SQL 語句create procedure selectinf會員證號char(7)asselect memberinf.memberid 會員證號,membername 會員姓名, bbcount 已借書數, balance 賬戶余額,system_books.bookid 書刊編號,bookname 書刊名稱,borrowdate 借書日期,borrownum 借書天數,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所創建的觸發器，存儲過程列表圖： 5.4.35.4.3超期處理：超期處理：1.對已借書的會員進行查詢借書是否超期（為了方便借書天數全為30）說明：當前的日期為2013年9月13日計算S

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