数据库课程设计报告图书管理系统.doc

数据库系统实验报告课 程：数据库系统 院 系：计算机学院班 级： 08065801 成 绩： 100 学号： 姓名： 学号： 姓名： 指导教师： 李波 日期： 年 月 日目 录 1 数据库设计31.1 需求分析31.2 概念模式设计31.3 关系模式设计32 数据库编程32.1 表的创建32.2 数据的修改32.3 查询42.4 统计42.5 视图及索引42.6 约束及触发器43 应用程序设计（选做）44 小结41 数据库设计1.1 需求分析图书馆可以利用本系统实现对图书馆信息的管理，本系统的主要功能为管理有关读者，出版社，书记，借阅者以及管理者的信息。1.数据需求分析a)读者信息；包括借书证号，姓名，联系方式，借书的数目。b)借阅信息， 包括借阅日期，应归还日期，所借图书的编号，以及证件号码。c)管理者信息，管理者名称，管理者编号，以及管理者联系方式。根据以上分析，本系统要有读者信息表，出版社信息表，书籍信息表，借阅信息表以及管理者信息表。2.事务需求分析a)读者信息管理部分，应完成以下功能i)可以浏览读者信息，并且可以对读者信息进行维护。ii) 可以删除添加及更改读者信息。 b)书籍信息管理部分应完成以下功能 i) 可以浏览书籍信息 ii) 可以删除添加以及更改图书信息c) 图书管理系统部分应完成以下功能 i) 可以浏览当前管理者的信息 ii) 可以更改删除以及添加管理者的信息1.2 概念模式设计 本节中给出所设计的ER图 cardnum namePhonenum IDtypeReaderamountdeadlineownreadernamecardnummoneyBorrowcard iflostborrowdatereturndatebooknum name BooklostBookborrowcardnumborrowcardbooknumfinaldatereadroomnameBookplaceBelong tobooknumBOOK typebooknumbookshelfpublishmanage IDpublishtimegendernameiflendable authormanager1.3 关系模式设计Book表属性名数据类型是否主键是否允许为空缺省值说明booknumChar(20)yesNOT NULL书号nameChar(20)noNOT NULL书名typeChar（20）noNOT NULL书的类型publishChar(20)noNOT NULL出版社publishtimedatetimenoNOT NULL出版时间authorChar(10)noNOT NULL书的作者iflendablebooleannoNOT NULLtrue是否可借Manager表属性名数据类型是否主键是否允许为空缺省值说明IDChar(20)yesNOT NULL管理员编号nameChar(10)noNOT NULL管理员姓名genderChar(10)noNULL管理员性别Reader表属性名数据类型是否主键是否允许为空缺省值说明IDChar(20)yesNOT NULL读者证件号nameChar(10)noNOT NULL读者姓名cardnumChar(20)noNOT NULL借书证号typeChar(10)noNOT NULL读者类型PhonenumChar(15)noNULL联系号码deadlinedatetimenoNOT NULL有效期Bookplace表属性名数据类型是否主键是否允许为空缺省值说明readroomChar(10)noNOT NULL阅览室bookshelfChar(10)noNOT NULL书架号booknumChar(20)yesNOT NULL书号Borrowcard表属性名数据类型是否主键是否允许为空缺省值说明cardnumChar(20)yesNOT NULL借书证号readernameChar（10）noNOT NULL读者姓名amountintnoNOT NULL借书数目moneyfloatnoNOT NULL卡内余额 iflostbooleannoNOT NULLfalse是否丢失Bookborrow表属性名数据类型是否主键是否允许为空缺省值说明cardnumChar(20)yesNOT NULL借书证号booknumChar(20)yesNOT NULL所借书号nameChar(20)noNOT NULL书名borrowdatedatetimeyesNOT NULL借书日期finaldatedatetimenoNOT NULL截止日期returndatedatetimenoNULLNULL还书日期Booklost表属性名数据类型是否主键是否允许为空缺省值说明booknumChar(20)yesNOT NULL丢失书号borrowcardChar(20)noNOT NULL借书证号2 数据库编程2.1 表的创建CREATE TABLE Book( booknum char(20) PRIMAR KEY; name char(20) NOT NULL; type char(20) NOT NULL;publish char(20) NOT NULL;publishtime datatime NOT NULL;author char(10) NOT NULL;iflendable boolean NOT NULL;)CREATE TABLE Manager( ID char(20) PRIMAR KEY; name char(10) NOT NULL; gender char(10) NOT NULL;)CREATE TABLE Reader( ID char(20) PRIMAR KEY; name char(10) NOT NULL; cardnum char(20) NOT NULL; type char(10) NOT NULL; Phonenum char(15) NULL; deadline datatime NOT NULL;)CREATE TABLE Bookplace( readroom char(10) NOT NULL; bookshelf char(10) NOT NULL; booknum char(20) PRIMAR KEY;)CREATE TABLE Borrowcard( cardnum char(20) PRIMAR KEY; booknum char(20) PRIMAR KEY; name char(20) NOT NULL; borrowdate datatime PRIMAR KEY; finaldate datatime NOT NULL; returndate datatime NOT NULL; )CREATE TABLE Booklost ( booknum char(20) PRIMAR KEY; borrowcard char(20) NOT NULL;) 2.2 数据的修改 1） 读者信息的修改（添加、删除、修改）添加读者：INSERT INTO reader（ID,name,cardnum,type,phonenum,deadline）VALUES(3605021990,吴斐嘉,s08010078,本科生2011-07-01);删除读者：DELETE FROM reader where ID=3605021990;修改读者：UPDATE readerSET phonenumHERE ID=3605021990;2） 借书证的挂失、补办、充值挂失：UPDATE borrowcardSET iflost=trueWHERE cardnum=s08010078;补办：INSERT INTO borrowcard(cardnum,readername,amount,money,iflost)VALUES(s08060077,吴斐嘉,0,0.00,false);充值：UPDATE borrowcardSET money=5.77WHERE cardnum=s08010078; 3） 书籍信息的修改增加图书馆从未有的新书:INSERT INTO bookVALUES(978-7-5366-01,说谎,小说,重庆出版社，2006-10-17，李伯曼,true);增加已经有的书:INSERT INTO bookVALUES（978-7-5366-02,说谎,小说,重庆出版社，2006-10-17，李伯曼,true）;报废某一本书：DELETE FROM bookWHERE booknum=978-7-5366-02;馆藏位置的修改增加新的阅览室或书架:INSERT INTO bookplace(readroom,bookshelf)VALUES(第三借阅室,3-67);修改书籍的馆藏地址：UPDATE bookplaceSET readroom=第三借阅室，bookshelf=3-67WHERE booknum=978-7-5366-02;4） 图书的借阅及归还借阅：INSERT INTO bookborrow(cardnum,booknum,name,borrowdate,finaldate)VALUES(s08010078,978-7-5366-02,说谎,2010-06-07,2010-07-07);UPDATE bookSET iflendable=falseWHERE booknum=978-7-5366-02;UPDATE borrowcardSET amount=amount+1WHERE cardnum=s08010078;归还:UPDATE bookSET iflendable=trueWHERE booknum=978-7-5366-02;UPDATE bookborrowSET returntime=2010-07-04WHERE cardnum=s08010078 AND booknum=978-7-5366-02AND borrowdate=2010-06-07;UPDATE borrowcardSET amount=amount-1WHERE cardnum=s08010078;6) 图书损坏或丢失的处理图书损坏：UPDATE bookSET iflendable=falseWHERE booknum=978-7-5366-02;UPDATE borrowcardSET money=money-5;图书丢失：DELETE FROM bookWHERE WHERE booknum=978-7-5366-02;INSERT INTO booklost(booknum,cardnum)VALUES(978-7-5366-02,s08010078);UPDATE borrowcardSET money=money-15;2.3 查询本节中至少包含实现以下查询的相应的SQL语句和运行结果：1) 读者信息查询（可随意给定一个读者证件号，以下查询类似。具体内容包括证件号、姓名、读者条码号、读者类型、联系方式、有效期等） SELECT * FROM reader WHERE ID=3605021990;2) 读者当前借阅明细查询（包括当前借阅书籍的条码号、书名、借阅日期、应还日期、馆藏地） SELECT bookborrow.booknum,name,borrowdate,finaldate,readroom,bookshelf FROM bookborrow,bookplace WHERE bookborrow.booknum=bookplace.booknum AND cardnum=s08010078 AND finaldate=GETDATE();3) 读者历史借阅明细查询（包括历史上所有借阅书籍的条码号、书名、借阅日期、归还日期、馆藏地） SELECT bookborrow.booknum,name,borrowdate,returndate,readroom,bookshelf FROM bookborrow,bookplace WHERE bookborrow.booknum=bookplace.booknum AND cardnum=s08010078;4) 过期未还的图书及相关读者信息的查询 SELECT * FROM bookborrow,reader WHERE bookborrow.cardnum=reader.cardnum AND returndate=finaldate;5) 读者的图书遗失记录查询 SELECT * FROM book WHERE booknum IN (SELECT booknum FROM booklost WHERE cardnum=s08010078);6) 书籍的各类检索（包括书籍的类型、书名、作者、出版社、出版年份、馆藏数量、在馆数量，且得出结果根据某属性排序） SELECT * FROM book,bookplace WHERE book.booknum=bookplace.booknum AND name=说谎 ORDER BY book.booknum; 在馆数量： SELECT COUNT(*) FROM book WHERE name=说谎 AND iflendable=true GROUP BY name ORDER BY name;7) 所有毕业生应归还图书情况查询 SELECT booknum FROM reader,bookborrow WHERE reader.cardnum=bookborrow.cardnum AND deadline=finaldate; 2.4 统计本节中至少包含实现以下查询的相应的SQL语句和运行结果：1) 2010年5月每日图书借出册数 SELECT COUNT(*) FROM bookborrow WHERE borrowdate BETWEEN 2010-05-01 AND 2010-05-31GROUP BY borrowdate;2) 2010年5月热借图书排行榜 SELECT name,COUNT(*) FROM bookborrow WHERE borrowdate BETWEEN 2010-05-01 AND 2010-05-31 GROUP BY name ORDER BY COUNT(*) DESC;3) 各类读者平均持有（借而未还）图书数量 SELECT AVG(amount) FROM borrowcard,reader WHERE borrowcard.cardnum=reader.cardnum AND reader.cardnum IN (SELECT cardnum FROM bookborrow WHERE returndate=NULL ) GROUP BY type;