数据库作业图书馆管理系统.docx

南京理工大学数据库系统实验报告课 程：数据库系统 院 系：计算机学院班 级： 08062303 成 绩： 学号： 0806230331 姓名： 尹星晨 指导教师： 李波 日期：2010年 06 月 12日ELEGANTA system is best in which one can specify things withthe most simplicity and straightforwardness。目 录 1 数据库设计11.1 需求分析11.2 概念模式设计31.3 关系模式设计72 数据库编程92.1 表的创建92.2 数据的修改112.3 查询132.4 统计152.5 视图及索引152.6 约束及触发器163 应用程序设计（选做）174 小结211 数据库设计1.1 需求分析问题的提出书籍是人类不可缺少的精神食粮，对学校来说，显得尤为重要。所以图书馆管理系统应该能够为用户提供充足的信息和快捷的查询手段。但一直以来人们使用传统人工的方式管理图书、期刊、试卷合订本等，这种管理方式存在着许多缺点,如:效率低、保密性差,另外时间一长,将产生大量的文件和数据,给查找、更新和维护都带来了不少的困难。 随着科学技术的不断提高,计算机科学日渐成熟,其强大的功能已为人们深刻认识,它已进入人类社会的各个领域并发挥着越来越重要的作用。作为计算机应用的一部分,使用计算机对图书资源信息进行管理,具有着手工管理所无法比拟的优点。例如:检索迅速、查找方便、可靠性高、存储量大、保密性好、寿命长、成本低等。这些优点能够极大地提高图书馆管理的效率,也是企业、学校的科学化、正规化管理,与世界接轨的重要条件。图书馆管理系统是典型的信息管理系统(MIS),其开发主要包括后台数据库的建立和维护以及前端应用程序的开发两个方面。对于前者要求建立起数据一致性和完整性强、数据安全性好的数据库。而对于后者则要求应用程序功能完备,易使用等特点。系统的主要功能需求图书管理系统的主要任务是便于用户对图书的浏览、查询。为了使系统具有更广泛的适用性，现将系统功能分为系统设置、图书管理和用户管理。系统设置主要提供对系统默认信息的设置 系统设置：添加删除管理员、设置管理员的权限、说明图书存放书架信息添加删除书架信息。图书管理主要方便用户对图书的借阅及管理员对书籍的管理，具体功能如下： 图书借还：借阅者可以借阅图书馆中可供借阅的书籍或归还书籍。 图书查询：可根据不同的条件（条形码、书名、类别、作者、书架、出版社）查询读者需要的图书、读者可根据不同的条件（图书条形码、图书名称、读者编号、读者名字、到期时间）查询自己借阅图书信息。 图书管理：图书管理员可以对图书信息进行添加、删除、修改。 图书排名：可以让读者知道图书借阅的排行榜及读者借阅的排行榜。读者管理添加删除不同类型的读者及注明可借图书的数量、添加删除读者的档案。具体功能如下： 读者类型管理：利用可借数目和可借时间区分普通读者和高级读者。 读者档案管理：添加新读者，对读者的信息进行添加，删除或修改。根据功能描述，设计系统的功能结构图如下：图1：系统功能分类表1：显示图书管理系统具体的各个模块的功能系统模块功能块功能区说明图书管理系统系统设置管理员设置实现管理员的添加或删除书架设置提供图书在书架上的存放位置读者管理读者类型管理提供读者的类型及可借阅数量读者档案管理删除毕业或退学的档案信息，添加新入学同学的信息图书管理图书管理图书档案管理添加新图书或删除不使用的图书图书类型管理给图书分类，方便管理图书借还图书借阅方便管理员和读者查询借阅信息图书归还方便用户查询图书是否借出图书查询图书档案查询方便管理员管理图书档案图书借阅查询方便读者查询及寻找图书图书排名图书借阅排行榜是读者了解最受欢迎的书读者借阅排行榜提供哪位读者借阅的图书最多系统的信息需求通过对数据库的初步分析，可将数据库初步分为7个数据库：某书表、某本书表、图书类型表、读者表、读者类别表、借阅卡表、图书位置表、借还归还表、管理员表，丢失损坏表。某书（条码号，书名，作者，价格，图书类型，出版社，出版日期，供应商，ISBN号入库时间，馆藏数目，库存数目）；某本书（图书编号，条码号，书架号，状态）；图书类型（ 条码号，类别，阅览室，书架号）；读者（身份证号，姓名，密码，性别，班级，读者类型，地址，联系电话，有效期）；读者类型（读者类型，可借数目，可借时间）；借阅卡（借阅卡号，身份证号，可借数目，余额，状态）；借阅归还（图书编号，借阅卡号，借出日期，应还日期，归还日期，状态）；图书位置（书架号，阅览室）；管理员（用户名，密码，姓名，性别）；丢失损坏（图书编号，借阅卡号，是否丢失，是否损坏，惩罚金额）；1.2 概念模式设计 核心E-R图： 由于图书管理系统主要用来处理读者借阅图书这一个任务，该任务的主要实体集是读者和书，又根据他们之间的联系借阅归还，得到图书馆管理系统的核心E-R图如下：图2：核心ER图 各实体E-R图： 读者（身份证号，姓名，密码，性别，班级，读者类型，地址，联系电话，有效期）图3：读者ER图 读者类型（读者类型，可借数目，可借时间）图4：读者类型ER图 借阅卡（借阅卡号，身份证号，可借数目，余额，状态）图5：借阅卡ER图 某书（条码号，书名，作者，价格，图书类型，出版社，出版日期，供应商，ISBN号入库时间，馆藏数目，库存数目）图6：某书ER图 某本书（图书编号，条码号，书架号，状态）图7：某本书ER图 图书类型（ 条码号，类别，阅览室，书架号）图8：图书类型ER图 图书位置（书架号，阅览室）图9：图书位置ER图 管理员（用户名，密码，姓名，性别）图10：管理员ER图 借阅归还（图书编号，借阅卡号，借出日期，应还日期，归还日期，状态）图11：借阅归还ER图 丢失损坏（图书编号，借阅卡号，是否丢失，是否损坏，惩罚金额）图12：丢失损坏ER图 各实体之间的关系E-R图：（见下页）图13：实体关系ER图1.3 关系模式设计某书（条码号，书名，作者，价格，图书类型，出版社，出版日期，供应商，ISBN号入库时间，馆藏数目，库存数目）；某本书（图书编号，条码号，书架号，状态）；图书类型（ 条码号，类别，阅览室，书架号）；读者（身份证号，姓名，密码，性别，班级，读者类型，地址，联系电话，有效期）；读者类型（读者类型，可借数目，可借时间）；借阅卡（借阅卡号，身份证号，可借数目，余额，状态）；借阅归还（图书编号，借阅卡号，借出日期，应还日期，归还日期，状态）；图书位置（书架号，阅览室）；管理员（用户名，密码，姓名，性别）；丢失损坏（图书编号，借阅卡号，是否丢失，是否损坏，惩罚金额）；表2 某书表属性名数据类型是否主键是否允许为空缺省值说明条码号VARCHAR(10)YESNOT NULL编号书名VARCHAR(10)NONOT NULL书名作者VARCHAR(10)NONOT NULL作者译者VARCHAR(20)NONULL译者价格MONEY(8)NONOT NULL价格图书类型VARCHAR(10)NONOT NULL图书类别出版社VARCHAR(20)NONOT NULL出版社出版日期DATETIME(8)NONOT NULL出版日期供应商VARCHAR(20)NONOT NULL供应商ISBNVARCHAR(20)NONOT NULLISBN馆藏数目INTNONOT NULL馆藏数目可借数目INTNONOT NULL可借数目入库时间DATETIME(8)NONOT NULL入库时间表3某本书表属性名数据类型是否主键是否允许为空缺省值说明图书编号VARCHAR(10)YESNOT NULL图书编号条码号VARCHAR(10)NONOT NULL条码号书架号VARCHAR(10)NONOT NULL书架号状态MONEY(8)NONOT NULL状态表4图书类型表属性名数据类型是否主键是否允许为空缺省值说明条码号VARCHAR(10)YESNOT NULL书本编号图书类型VARCHAR(10)NONOT NULL图书类型表5 读者表属性名数据类型是否主键是否允许为空缺省值说明身份证号VARCHAR(10)YESNOT NULL身份证号姓名VARCHAR(10)NONOT NULL姓名密码VARCHAR(10)NONOT NULL密码性别VARCHAR(2)NONOT NULL默认M性别班级VARCHAR(10)NONULL班级读者类型VARCHAR(10)NONOT NULL读者类型住址VARCHAR(10)NONULL家庭住址联系电话VARCHAR(15)NONULL联系电话有效期VARCHAR(10)NONULL入学年限表6 读者类型表属性名数据类型是否主键是否允许为空缺省值说明读者类别VARCHAR(10)YESNOT NULL读者类别可借数目INTNONOT NULL可借数目可借时间DATETIMENONOT NULL可借时间表7 借阅卡表属性名数据类型是否主键是否允许为空缺省值说明身份证号VARCHAR(10)YESNOT NULL身份证号借阅卡号VARCHAR(100NONOT NULL借阅卡号可借数量INTNONOT NULL可借数目余额MONEYNONOT NULL0.00余额状态VRACHAR(10)NONOT NULL状态表8借阅归还表属性名数据类型是否主键是否允许为空缺省值说明借阅卡号VARCHAR(10)YESNOT NULL读者卡号图书编号VARCHAR(10)YESNOT NULL书本编号借出日期DATETIME(8)NONOT NULL借出日期应还日期DATETIME(8)NONOT NULL应还日期归还日期DATETIME(8)NONULL归还日期状态VARCHAR(10)NONOT NULL状态表9图书位置表属性名数据类型是否主键是否允许为空缺省值说明书架号VARCHAR(10)YESNOT NULL书架号阅览室VARCHAR(10)NONOT NULL阅览室表10 管理员表属性名数据类型是否主键是否允许为空缺省值说明用户名VARCHAR(10)YESNOT NULL用户名密码VARCHAR(10)NONOT NULL密码姓名VARCHAR(10)NONOT NULL姓名性别VARCHAR(10)NONOT NULLM(F,M)性别表11丢失损坏表属性名数据类型是否主键是否允许为空缺省值说明图书编号VARCHAR(10)YESNOT NULL图书编号读者卡号VARCHAR(10)YESNOT NULL读者卡号是否损坏BOOLNONOT NULLFALSE是否超期是否丢失BOOLNONOT NULLFALSE是否丢失惩罚金额VARCHAR(10)NONOT NULL0.00惩罚金额2 数据库编程2.1 表的创建 创建某书表：CREATE TABLE 某书( 条码号 VARCHAR(20) PRIMARY KEY,书名VARCHAR(20) NOT NULL,作者VARCHAR(20) NOT NULL,译者VARCHAR(10) NULL,价格MONEY NOT NULL DEFAULT 0.00,图书类型VARCHAR(10) NOT NULL REFERENCES图书类型（图书类型）,出版社VARCHAR(10) NOT NULL,出版日期DATETIME NOT NULL,供应商VARCHAR(10) NOT NULL,ISBN VARCHAR(10) NOT NULL,入库时间DATETIME NOT NULL,库存数量INT NOT NULL,馆藏数量INT NOT NULL,) 创建图书类型表：CREATE TABLE 图书类型(条码号VARCHAR(10) PRIMARY KEY REFERENCES某书（条码号）,图书类型VARCHAR(10) NOT NULL ,) 创建图书位置表：CREATE TABLE 图书位置 (书架号VARCHAR(10) PRIMARY KEY,阅览室VARCHAR(10) NOT NULL,) 创建某本书表：CREATE TABLE 图书位置 (图书编号VARCHAR(10) PRIMARY KEY,条码号VARCHAR(10) NOT NULL REFERENCES某书（条码号）,书架号VARCHAR(10) NOT NULL REFERENCES图书位置（书架号）,状态VARCHAR(10) NULL,) 创建读者表：CREATE TABLE 读者 (身份证号VARCHAR(10) PRIMARY KEY,姓名VARCHAR(10) NOT NULL,密码VARCHAR(10) NOT NULL DEFAULT 身份证号,性别VARCHAR(10) NOT NULL DEFAULT M CHECK(状态 IN ( M, F),班级VARCHAR(10) NULL,读者类型VARCHAR(10) NOT NULL,地址VARCHAR(10) NULL,联系电话VARCHAR(10) NULL,有效期DATETIME NOT NULL,) 创建读者类型表：CREATE TABLE 读者类型 (读者类型VARCHAR(10) PRIMARY KEY,可借数量INT NOT NULL, DEFAULT 0,可借时间DATETIME NOT NULL,) 创建借阅卡表：CREATE TABLE 借阅卡 (身份证号VARCHAR(10) NOT NULL REFERENCES读者（身份证号），借阅卡号VARCHAR(10) PRIMARY KEY,余额MONEY NOT NULL DEFAULT 0.00,可借数量INT NOT NULL DEFAULT 0,状态VARCHAR(10) NOT NULL DEFAULT 未挂失CHECK(状态 IN(挂失,未挂失),) 创建借阅归还表：CREATE TABLE 借阅归还 (图书编号VARCHAR(10) REFERENCES 某本书(图书编号),借阅卡号VARCHAR(10) REFERENCES 借阅卡(借阅卡号),借出日期DATETIME NOT NULL,应还日期DATETIME NOT NULL,归还日期DATETIME NULL,状态VARCHAR(10) NOT NULL DEFAULT 未归还 CHECK(状态 IN(归还,未归还),PRIMARY KEY(图书编号，借阅卡号),) 创建管理员表：CREATE TABLE 管理员 (用户名VARCHAR(10) PRIMARY KEY,密码VARCHAR(10) NOT NULL,姓名VARCHAR(10) NOT NULL,性别VARCHAR(10) NOT NULL DEFAULT M CHECK(状态 IN ( M, F),) 创建丢失损坏表：CREATE TABLE 丢失损坏 (图书编号VARCHAR(10) REFERENCES 某本书(图书编号),借阅卡号VARCHAR(10) REFERENCES 借阅卡(借阅卡号),是否超期BOOL NOT NULL DEFAULT FALSE,是否丢失BOOL NOT NULL DEFAULT FALSE,惩罚金额MONEY NOT NULL DEFAULT 0.00,PRIMARY KEY(图书编号，借阅卡号),)2.2 数据的修改 1） 读者信息的修改（添加、删除、修改） 读者信息的添加：INSERT INTO Reader(身份证号, 借阅卡号, 姓名, 密码, 性别, 班级, 读者类型, 住址, 联系电话, 有效期)VALUES 08062723, 尹星晨, 123456, M , 08062303 , 本科生 , 孝陵卫200号 ,2012/12/30) 读者信息的修改：UPDATE Reader SET 联系电话HERE 身份证号读者信息的删除DELETE FROM 读者 WHERE 身份证号=3213241993070734212） 借书证的挂失、补办、充值借书证的挂失：UPDATE 节约卡SET 状体=挂失WHERE 借阅卡号 =08062723;借书证的补办：INSERT 借阅卡(身份证号,借阅卡号)VALUES 08062724);借书证的充值：UPDATE 借阅卡SET 余额=(SELECT 余额 FROM 借阅卡 WHERE 借阅卡号 =08062723)+100)WHERE 借阅卡号 =08062724;3） 书籍信息的修改（增加新书、增加已有书、报废某一本书、修改书的馆藏位置）增加新书：INSERT 一类书(条码号 ,作者 , 书名 ,出版社, 出版时间)VALUES(978-7-111-24733-3,Jeffrey,A First Course in Database System ,机械工业出版社 ,2008)增加已有书：INSERT 一类书(amount)VALUES(978-7-111-24733-3, 978-7-111-24733-3-1,馆藏数量=(SELECT 馆藏数量 FROM 一类书WHERE 条码号=978-7-111-24733-3)+1);报废一本书：UPDATE 一类书SET 状态=不可借WHERE 条码号=123-65-23 AND 状态=可借;修改书的编号位置：UPDATE 一类书SET 条码号=178-56-540WHERE 条码号=178-56-560;4） 馆藏位置的修改（增加新的阅览室或书架）增加阅览室和书架号：INSERT 图书位置(书架号，阅览室)VALUES(208-1， 208);修改图书的阅览室和书架号：UPDATE 某书SET 书架号=209-00WHERE 书架号 =209-01;5） 图书的借阅及归还图书的借阅：INSERT 借阅归还(图书编号, 借阅卡号，借出日期，归还日期，状态)VALUES(1204377,08062723,2010-06-01, 2010-07-01，未归还)UPDATE 某本书SET 状态=不可借WHERE图书编号=1204377图书的归还：UPDATE 借阅归还SET 归还日期=GETDATE()WHERE 图书编号=1204377 AND 借阅卡号=08062723; INSERT借阅归还 (图书编号, 借阅卡号,借出日期,状态)VALUES(1204377,08062723,2010-06-01, 未归还)UPDATE 借阅归还SET 状态=已归还AND 归还日期=GETDATE()WHERE 图书编号=1204377 AND 借阅卡号=08062723 AND 借出日期 =2010-06-10UPDATE 某本书SET 状态=可借WHERE 图书编号=12043776） 图书超期、损坏或丢失的处理a) 图书超期处理UPDATE 借阅卡SET余额=余额-1WHERE 借阅咔号=(SELECT 借阅卡号 FROM 借阅归还 WHERE 应还日期GETDATE() AND 归还日期 =NULL);b) 图书丢失处理INSERT 丢失损坏(图书编号,借阅卡号,借出日期,状态)VALUES(1204377,08062723,2010-06-01,丢失);UPDATE 某本书 SET 状态=不可借WHERE 图书编号 =1204377;UPDATE 借阅归还SET 归还日期=GETDATE() WHERE 借阅卡号=08062723AND 图书编号=1204377 AND借出日期=2010-06-01;UPDATE 借阅卡SET 余额=余额-价格=(SELECT 价格FROM某书WHERE 图书编号= 1204377)*0.5)WHERE 借阅卡号 = 08062723;c) 图书损坏处理UPDATE 某本书 SET STATUS=不可借WHERE 图书编号= 112-7-890-2 UPDATE 借阅归还SET 归还日期=GETDATE() WHERE 借阅卡号=08062723AND 图书编号=112-7-890-2 AND 借出日期=2010-06-10UPDATE 借阅卡SET 余额=余额-10WHERE 借阅卡号= 080627232.3 查询1) 读者信息查询（可随意给定一个读者证件号，以下查询类似。具体内容包括证件号、姓名、读者条码号、读者类型、联系方式、有效期等）SQL：SELECT *FROM 读者WHERE 身份证号=2) 读者当前借阅明细查询（包括当前借阅书籍的条码号、书名、借阅日期、应还日期、馆藏地）SQL:SELECT某书.条码号，某书.书名，借阅归还.借阅日期，借阅归还.应还日期，某书.馆藏地FROM 某书，借阅归还，借阅卡WHERE 借阅卡.身份证号=ND借阅卡.借阅卡号=借阅归还.借阅卡号AND某书.条码号=借阅归还.条码号AND借阅归还.状态=未归还;3) 读者历史借阅明细查询（包括历史上所有借阅书籍的条码号、书名、借阅日期、归还日期、馆藏地）SQL:SELECT 某书.条码号，某书.书名，借阅归还.借阅日期，借阅归还.归还日期，某书.馆藏地FROM 借阅归还，借阅卡，某书WHERE借阅卡.身份证号ND借阅卡。借阅卡号=借阅归还.借阅卡号AND借阅归还.条码号=某书.条码号；4) 过期未还的图书及相关读者信息的查询SQL:SELECT *FROM 某本书WHERE 状态=未归还;SELECT *FROM 读者，借阅归还WHERE 借阅归还.状态=未归还AND 读者.借阅卡号=借阅归还.借阅卡号;5) 读者的图书遗失记录查询SQL:SELECT 图书编号,借阅卡号, 借出日期 FROM 丢失损坏WHERE 状态=丢失;6) 书籍的各类检索（包括书籍的类型、书名、作者、出版社、出版年份、馆藏数量、在馆数量，且得出结果根据某属性排序）SQL:SELECT *FROM 某书WHERE 书名=%数据库%ORDER BY 出版日期;7) 所有毕业生应归还图书情况查询SQL:SELECT读者.身份证号,读者.姓名,读者.班级,读者.地址,读者.联系电话,借阅卡.借阅卡号,借阅归还.图书编号FROM借阅卡,借阅归还,读者WHERE读者.有效期=GETYEAR() AND读者.身份证号=借阅卡.身份证号AND借阅卡.借阅卡号=借阅归还.借阅卡号AND借阅归还.状态=未归还;2.4 统计1) 2010年5月每日图书借出册数SELECT COUNT(*) FROM 借阅归还WHERE 2010/5/1=借出日期 AND 借出日期=2010/05/01 AND 借出日期=2010/05/31GROUP BY 图书编号,书名,作者,条码号,出版日期ORDER BY 借出次数 DESC3) 各类读者平均持有（借而未还）图书数量SELECT (借阅总数.借阅总数/读者总数.读者总数) AS各类读者平均持有,读者总数.读者类型FROM 读者总数,借阅总数WHERE读者总数.读者类型=借阅总数.读者类型2.5 视图及索引 借阅总数CERETE VIEW借阅总数 ASSELECT COUNT(*) AS 借阅总数,读者类型FROM 借阅信息GROUP BY 读者类型 读者总数CREATE VIEW 读者总数 ASSELECT COUNT(*) AS 读者总数,读者类型FROM 读者GROUP BY读者类型 过期信息CERATE VIEW 过期信息 ASSELECT 借阅卡号, 某本书.图书编号, 借出日期FROM 借阅归还JOIN 借阅卡 ON 借阅归还.借阅卡号 = 借阅卡. 借阅卡号 WHERE 借阅归还.状态=未归还AND 借阅归还.借阅卡号=借阅卡.借阅卡号 AND 应还日期(SELECT 可借数量 FROM 借阅卡 WHERE 借阅卡号 IN(SELECT 借阅卡号 FROM INSERTED)DELETE FROM 借阅归还 WHERE 图书编号=(SELECT 图书编号 FROM INSERTED)AND 借阅卡号 IN(SELECT 借阅卡号 FROM INSERTED) ELSE BEGIN UPDATE 图书编号SET某本书.状态=不可借WHERE某本书.状态=不可借 WHERE 某本书.图书编号IN(SELECT 图书编号FROM INSERTED)END;END; 如果可借CREATE TRIGGER如果可借ON借阅归还AFTER INSERT ASBEGINIF(SELECT COUNT(*) FROM借阅归还WHERE图书编号IN(SELECT 图书编号FROM INSERTED)AND 状态=未归还)=2)DELETE FROM借阅归还 WHERE图书编号 IN(SELECT图书编号 FROM INSERTED) AND借出日期 IN (SELECT借出日期 FROM INSERTED)END 书籍归还触发器CREATE TRIGGER 书籍归还ON 借阅归还AFTER UPDATE ASBEGINIF(SELECT 状态 FROM DEETED )=未归还AND (SELECT 状态FROM INSERTED) = 已归还 )UPDATE 借阅归还 SET 归还日期=GETDATE() WHERE 图书编号 IN(SELECT 图书编号 FROM DELETED )AND 借出日期 IN (SELECT 节约日期 FROM DELETED)UPDATE 图书编号 SET 状态=可借 WHERE 图书编号 IN(SELECT 图书编号 FROM INSERTED)END4 小结我在文章扉页引用的那句话是来自Frederick P. Brooks, Jr的经典名著The Mythical Man-Month的第四章Aristocracy，Democracy，and System Design。虽然我并不能够完全看懂书中所提及的那些关于系统设计的理论或者建议，但我确实可以理解Brooks的担忧，当然这些担忧也是现在许多软件行业的系统设计师们所担忧的。这本书给我在系统设计方面的影响远远多于我之前看过的关于系统设计或者系统分析的书籍。无独有偶，同时我在读的是来Object Mentor公司总裁Robert C. Martin的一本名为Clear Code的书，这本书强调的似乎与Brooks在书中声明的遥相呼应。他们都认为无论是系统还是代码都应该让阅读的人can specify things with the most simplicity and straightforwardness。如果说Brooks的书现在只能在我的想象中给我一定的引导，Martin的书则确实使我对我之前所写过的冗长的，效率不高的代码产生了鄙夷的情绪，虽然之前一直为之骄傲。Martin在Clear Code的第11章提到了如何设计一个系统，当我读到这个章节的时候，我的关于图书馆管理系统数据库的设计正处于寒冰时代。我的关于读者的信