数据库大作业全_参考答案

1、数据库原理与应用综合设计任务书前言数据库原理与应用课程的重点知识模块包括：1）数据库设计、2）用SQL实现建库、建表、查询、更新、和创建视图、3）存储过程和触发器设计。针对这三个应用能力，用一个案例作为背景，布置三次大作业。在校大学生都能理解“图书管理系统”的应用场合和业务流程。因此，以图书管理系统作为案例来布置作业，可以降低业务分析难度，让学生将主要精力放在知识消化与技术应用上。本文档包括四个部分。第一部分描述系统的需求，第二部分提出E-R模型设计和关系模型设计的任务；第三部分提出在SQLServer中，用SQL语句来建库、建表、查询、更新数据、创建视图的任务；第四部分，根据应用需求、安全需

2、求和数据完整性要求，提出设计存储过程和触发器的任务。每个任务之前，都给出了完成任务所需要掌握的关键知识点，学生可以在对这些知识点进行复习的基础上完成任务，每个任务是一次大作业。第一部分案例的需求描述本部分描述“图书管理系统”的需求，学生通过阅读本部分内容，了解系统的功能要求、运行环境，对系统所需的数据有总体认识，作为三次作业的基础。1.1 需求故事洁是图书阅览室的管理员，她找到程序开发小组“枫”，请他们帮助开发一套管理程序。他们围坐在一起，开始以下对话：洁：你们进来都看到了，那几排大柜子，全都放满了书，很多教师来借书，管理起来越来越难，希望帮我开发一个管理软件。枫：你希望软件提供哪些功能。洁：

3、首先是能登记和查看图书信息，借书和还书的时候要方便操作。对于我来说，看图书的状态很重要，就是说要知道哪些图书已经借出去了，哪些书已经过了借阅期，此外还要做一些统计表大概就是这些吧。枫：你怎么登记借书呢？规定的借阅期有多长？过了借阅期怎么办？洁：每个老师都有一个工号，我就计工号和图书的ISBN号，一个人最多能借2本书。不同类型的书借阅期限是不一样的，比如文学作品的借阅期规定是15天，教学参考书的借阅期限是半年。有的人不自觉，过期好久都不还，我现在是翻记录本一个个地找，然后打电话到他的部门，并且发催还邮件。枫：同一ISBN只有一本书吗？洁：是的，现在把书分成这样几类：教学参考书、文学作品、学术期刊

4、、学术专著。枫：你想统计一些什么数据洁：主要是分类统计图书册数和价值、图书借阅次数。我们想知道哪些图书最受欢迎。枫：阅览室是你一个人在管么？洁：阅览室有两个人。我们俩都有一台计算机，最好两台计算机都可以用上这个软件。1.2 需求分析1）功能需求图1-1:功能需求示意图教师信息管理：用于教师基本资料的增删改查。图书信息管理：用于图书基本信息的增删改查，分类统计图书册数和价值。借书登记：记录借书时间、所借图书、借书人、办理人。还书登记：记录还书时间、所还图书、还书人、办理人。催还：查询借阅逾期的借书信息，给借书人发电子邮件，给借书人的部门打电话。2）运行环境要求图1-2:运行环境拓扑图系统采用C/

5、S模式，有两台PC和一台服务器，联成一个局域网。PC上安装图书管理软件的客户端，服务器上安装DBMS,服务器也可由两台PC中的一台来代替。第二部分作业1E-R模型与关系模型设计（满分8分）本部分的任务是：在需求分析的基础上，进行E-R图设计，然后将E-R模型转换为关系模型。任务：1） 根据需求描述，绘制E-R图。2） 将E-R图转换成关系模型，写出所有的关系模式，并写出每一个关系是按照什么规则转换而成的。3） 在已经形成的关系模型下，举例说明连接运算、投影运算、选择运算。参考答案：（属性写在了实体和联系图形内）任务：1）根据需求描述，分别为“图书管理系统”的不同功能模块绘制局部E-R图。教师信

6、息管理：图书基本信息管理:借还书登记:催还书登记:2）整体E-R图3）关系模式类型表（类型名,彳t阅期限），依据：实体转换为关系。图书表（ISBN,书名，作者，出版社，出版时间，单价，类型名，状态），依据：实体转换为关系，1:N的联系合并到N,在N方增加一个外键：类型名。注：状态属性为（库存，借出）管理员表（管理员名,性别，口令），依据：实体转换为关系。教师表（教师工号,姓名，性别，Email,部门名），依据：实体转换为关系，1:N的联系合并到N,在N方增加一个外键：部门名。部门表（部门名,电话），依据：实体转换为关系。借还表（借还业务编号，ISBN,教师工号，借办理人，借出日期，还办理人，归

7、还日期）,依据：M:N联系转换为一个关系，取联系的实体的键加联系属性作为其属性，可以增加一个列作为主键。催还表（催还业务编号，ISBN,教师工号，办理人，催还日期），依据：M:N联系转换为一个关系，取联系的实体的键加联系属性作为其属性，可以增加一个列作为主键。4）在已经形成的关系模型下，举例说明连接运算、投影运算、选择运算。连接运算：查看类型的图书已经超过了有效借出期限。（需要图书，类型，借还关系）投影运算：查看图书的书名、作者。选择运算：查看单价高于50元的图书。3. 作业2用SQL语句建库建表、增删改查数据、创建视图任务：1) 根据关系规范化的要求，在第一部分得到的图书管理系统关系模式需要

8、进行规范化吗？如果需要，请写出写出符合3NF的关系模式。2) 在得到3NF的图书管理系统关系模式的基础上，将每个关系转换为SQLServer支持的表结构，用表格形式表示出表结构。类型表列名约束类型名NChar(10)Pk借阅期限TinyintNotnull图书表列名约束ISBNChar(20)Pk书名Nchar(10)Notnull作者NcharNotnull出版社Nchar(10)Notnull出版时间Datetime单价MoneyNotnull类型名NChar(10)FKreferences尖型表(类型名)状态NChar(2)检查约束管理员表列名约束管理员名NcharPk性别Nchar(1

9、)检查约束口令Nchar(10)Notnull部门表列名约束部门名Nchar(10)PK电话char(11)Notnull教师表列名约束教师，号Char(10)Pk姓名Nchar(4)Notnull性别Nchar(1)检查约束Emailchar(20)Notnull部门名Nchar(10)FKreferences部门表（部门名）借还表列名约束借还业务编号IntPKISBNChar(20)FKreferences图书表(ISBN)教师，号Char(10)Fkreferences教师表(教师，号)借办代理人Nchar(4)Notnull借出日期DatetimeNotnull还办代理人Nchar(4

10、)归还日期Datetime催还表列名约束催还业务编号IntPkISBNChar(20)FKreferences图书表(ISBN)教师，号Char(10)FKreferences教师表(教师，号)办代理人Nchar(4)null催还日期Datetimenull使用SQL语句，创建数据库，数据库名为Bookstore,在数据库中创建表，设定表的主键约束、外键约束、检查约束、默认约束、非空约束、唯一约束。写出SQL。createdatabaseBookstorecreatetable类型表(类型名nchar(10)notnullprimarykey,借阅期限tinyintnotnull)create

11、table图书表(ISBNchar(20)primarykey,书名nchar(10)notnull,作者nchar(4)notnull,出版社nchar(10)notnull,出版时间datetime,单价moneynotnull,类型名nchar(10)notnullforeignkeyreferences类型表(类型名)ondeletenoactiononupdatecascade,状态nchar(2)notnullcheck(状态='库存'or状态='借出)createtable管理员表(管理员名nchar(4)primarykey,性别nchar(1)chec

12、k(性别='男'or性别='女'),口令nchar(10)notnull)createtable部门表(部门名nchar(10)primarykey,电话char(11)notnull)createtable教师表(教师工号char(10)primarykey,姓名nchar(4)notnull,性别nchar(1)check(性别='男'or性别='女'),Emailchar(20)notnull,部门名nchar(10)notnullforeignkeyreferences部门表(部门名)ondeletenoactiononu

13、pdatecascade)createtable借还表(借还业务编号intprimarykey,ISBNchar(20)foreignkeyreferences图书表(ISBN)ondeletenoactiononupdatecascade,教师工号char(10)foreignkeyreferences教师表(教师工号)ondeletenoactiononupdatecascade,借办代理人nchar(4)notnull,借出日期datetimenotnull,还办代理人nchar(4)null,归还日期datetimenull)createtable催还表(催还业务编号intprimar

14、ykeyidentity(1,1),ISBNchar(20)foreignkeyreferences图书表(ISBN)ondeletenoactiononupdatecascade,教师工号char(10)foreignkeyreferences教师表(教师工号)ondeletenoactiononupdatecascade,办代理人nchar(4)null,催还日期datetimenull)写以下增删改查操作的SQL语句1、输入图书类型数据insertinto类型表(类型名，借阅期限)values('文学',15),（'教学参考书',180）,（音乐，45）2

15、、增加三个教师信息insertinto部门表（部门名，电话）values（"基础部，,139*'）,('电子系','136*1),(,音乐艺术学院,'186*,insertinto教师表（教师工号，姓名，性别，Email,部门名）values（'0001','张三'，'男'，'333',基石Hi部,），（'0002',李四，男，'4444','电子系）,（'0003','王五，女，'55555',

16、9;音乐艺术学院,）3、增加五本书的信息insertinto图书表（ISBN书名，作者，出版社，出版时间，单价，类型名，状态）values（'9787533954109',夜航船，张岱，,浙江文艺出版社,'2018.8.8,99.8，文学，库存）略4、某教师今天借了某书，涉及两个操作，一是将书的状态改为“借出”，二是增加一个借书记录update图书表set状态='借出where书名='夜航船insertinto借还表（借还业务编号，ISBN教师工号，借办代理人，借出日期）values（'3123','9787533954109&#

17、39;,'0001','张三'，'2019.5.23'）5、将某书的作者改为自己的姓名update图书表set作者='*'where书名='夜航船6、查询某教师的借还书记录select*from借还表where教师工号='0001'7、查询某本书的流转记录select*from借还表whereISBN='9787533954109,创建视图，写出SQL语句8、借出图书视图createview借由图书asselect*from图书表where状态="借出9、借阅逾期视图createview借

18、阅逾期asselect借还表.借办代理人，借还表.教师工号，图书表.书名from图书表innerjoin借还表on图书表.ISBN=借还表.ISBNinnerjoin类型表on图书表.类型名=类型表.类型名where图书表.状态="借出'anddatediff(day,借还表.借出日期，getdate()类型表.借阅期限10、图书分类统计册数视图createview分类统计asselectcount(*)as书籍册数，类型名from图书表groupby类型名4. 作业3存储过程与触发器设计任务任务：1)为BookStore数据库增加一个用户oper1,允许oper1对图书表和

19、借还表进行修改操作。useBookStoregoexecsp_addloginoperl,"(此处为密码)'，'BookStore'goexecsp_addrolemember'db_datareader','operl'grantupdateon图书表tooperlgrantupdateon借还表tooperl2)设计存储过程a)数据初始化过程sp_Inito包含的操作有：所有的用于测试图书信息、教师信息、历史记录清空，输入图书分类、输入管理员信息、输入借书规定。略，界面进行输入或者代码输入作业用数据b)某教师在某时间借了某本

20、书，由某管理员办理createprocedureproc_lendbookaidchar(10),atimedatetime,ISBNchar(20),anamenchar(4)asbeginupdate图书表set状态='借出whereISBN=ISBNinsertinto借还表(ISBN教师工号，借办代理人，借出日期)values(ISBNaid,anameatimeendc)某教师在某时间还了某本书，由某管理员办理createprocedureproc_returnbookbidchar(10),btimedatetime,ISBNchar(20),bnamenchar(4)as

21、beginupdate图书表set状态='库存whereISBN=ISBNupdate借还表set还办代理人=bnamem还日期=btimewhere教师工号=bidandISBN=ISBNendd)查看某人的全部借还书记录createprocedureproc_getReaderlogcnamechar(4),cidchar(10)asbeginselectb.姓名，c.书咨a.借出日期，归还日期from借还表ajoin教师表bona.教师工号=b.教师工号join图书表cona.ISBN=c.ISBNwhereb.姓名=cnameorb.教师工号=cidende)查看某本书的历史流

22、转记录createprocedureproc_getBooklogbooknamenchar(10),ISBNchar(20)asbeginselect教师表.姓名，图书表.书名，借还表.借出日期，归还日期from借还表ajoin教师表bona.教师工号=b.教师工号join图书表cona.ISBN=c.ISBNwherea.ISBN=ISBNorc.书名=booknameendf)某管理员在某时间给全部借阅过期者发催还邮件。部分有错误的需要将修改催还业务编号为自增长(identity(1,1),还有整个表都要允许空化例：altertable催还表altercolumn办代理人char(4)nullcreateprocedureproc_writeEmailLogdnamechar(4),dtimedatetimeasbegininsertinto催还表(教师工号)select教师工号from借还表ajoin图书表bona.ISBN=b.ISBNjoin类型表conb.类型名=c.类型名wheredatediff(day,a.借出日期，getdate()>c.借阅期限anda.归还日期isnullupdate催还表set办代理人=dnameW还