数据库作业(答案)

《数据库原理与应用》综合设计任务书前言《数据库原理与应用》课程的重点知识模块包括：1）数据库设计、2）用SQL实现建库、建表、查询、更新、和创建视图、3）存储过程和触发器设计。

针对这三个应用能力，用一个案例作为背景，布置三次大作业。

在校大学生都能理解“图书管理系统”的应用场合和业务流程。

因此，以图书管理系统作为案例来布置作业，可以降低业务分析难度，让学生将主要精力放在知识消化与技术应用上。

本文档包括四个部分。

第一部分描述系统的需求，第二部分提出E-R模型设计和关系模型设计的任务；第三部分提出在SQL Server中，用SQL语句来建库、建表、查询、更新数据、创建视图的任务；第四部分，根据应用需求、安全需求和数据完整性要求，提出设计存储过程和触发器的任务。

每个任务之前，都给出了完成任务所需要掌握的关键知识点，学生可以在对这些知识点进行复习的基础上完成任务，每个任务是一次大作业。

第一部分案例的需求描述本部分描述“图书管理系统”的需求，学生通过阅读本部分内容，了解系统的功能要求、运行环境，对系统所需的数据有总体认识，作为三次作业的基础。

1.2 需求分析1）功能需求图1-1：功能需求示意图教师信息管理：用于教师基本资料的增删改查。

图书信息管理：用于图书基本信息的增删改查，分类统计图书册数和价值。

借书登记：记录借书时间、所借图书、借书人、办理人。

还书登记：记录还书时间、所还图书、还书人、办理人。

催还：查询借阅逾期的借书信息，给借书人发电子邮件，给借书人的部门打电话。

2）运行环境要求图1-2：运行环境拓扑图系统采用C/S模式，有两台PC和一台服务器，联成一个局域网。

PC上安装图书管理软件的客户端，服务器上安装DBMS，服务器也可由两台PC中的一台来代替。

第二部分作业1——E-R模型与关系模型设计（满分8分）本部分的任务是：在需求分析的基础上，进行E-R图设计，然后将E-R模型转换为关系模型。

任务：1）根据需求描述，绘制E-R图。

2）将E-R图转换成关系模型，写出所有的关系模式，并写出每一个关系是按照什么规则转换而成的。

3）在已经形成的关系模型下，举例说明连接运算、投影运算、选择运算。

参考答案：（属性写在了实体和联系图形内）任务：1）根据需求描述，分别为“图书管理系统”的不同功能模块绘制局部E-R图。

教师信息管理：图书基本信息管理：借还书登记：催还书登记：2）整体E-R图3）关系模式➢类型表(类型名，借阅期限)，依据：实体转换为关系。

➢图书表（ISBN，书名，作者，出版社，出版时间，单价，类型名，状态），依据：实体转换为关系，1：N的联系合并到N，在N方增加一个外键:类型名。

注：状态属性为（库存，借出）➢管理员表(管理员名，性别，口令) ，依据：实体转换为关系。

➢教师表(教师工号，姓名,性别，Email，部门名)，依据：实体转换为关系，1：N的联系合并到N，在N方增加一个外键:部门名。

➢部门表(部门名，电话)，依据：实体转换为关系。

➢借还表(借还业务编号，ISBN，教师工号，借办理人，借出日期，还办理人，归还日期)，依据，M:N联系转换为一个关系，取联系的实体的键加联系属性作为其属性，可以增加一个列作为主键。

➢催还表(催还业务编号，ISBN，教师工号，办理人，催还日期)，依据，M:N联系转换为一个关系，取联系的实体的键加联系属性作为其属性，可以增加一个列作为主键。

4）在已经形成的关系模型下，举例说明连接运算、投影运算、选择运算。

连接运算：查看类型的图书已经超过了有效借出期限。

（需要图书，类型，借还关系）投影运算：查看图书的书名、作者。

选择运算：查看单价高于50元的图书。

附：自底向上设计概念结构的方法通常分为两步：第一步：首先要根据需求分析的结果（数据流图、数据字典等）对现实世界的数据进行抽象，设计各个局部视图即分E-R图。

第二步：集成局部视图。

设计分E-R图的步骤是：选择局部应用在需求分析阶段，通过对应用环境和要求进行详尽的调查分析，逐一设计分E-R图，实体的属性、标识实体的码实际上实体与属性是相对而言的，很难有截然划分的界限。

同一事物，在一种应用环境中作为"属性"，在另一种应用环境中就必须作为"实体"。

一般说来，在给定的应用环境中：属性不能再具有需要描述的性质。

即属性必须是不可分的数据项。

合并分E-R图，生成初步E-R图。

各分E-R图之间的冲突主要有三类：属性冲突、命名冲突和结构冲突。

解决方法是根据应用的语义对实体联系的类型进行综合或调整。

修改与重构，生成基本E-R图分E-R图经过合并生成的是初步E-R图。

之所以称其为初步E-R图，是因为其中可能存在冗余的数据和冗余的实体间联系，即存在可由基本数据导出的数据和可由其他联系导出的联系。

冗余数据和冗余联系容易破坏数据库的完整性，给数据库维护增加困难，因此得到初步E-R图后，还应当进一步检查E-R图中是否存在冗余，如果存在，应设法予以消除。

第三部分作业2——用SQL语句建库、查询、更新数据、创建视图（满分9分）本部分的任务是：在关系模型的基础上，结合SQL Server 2005的技术特点，设计出数据库的逻辑结构，然后在SQL Server 2005中用SQL语句创建数据库、数据表、并输入一些原始数据，写SQL 语句，进行数据增删改，创建视图。

任务：1）在第一次大作业的参考答案基础上，将每个关系转换为SQL Server支持的表结构，写出表结构。

2）使用SQL语句，创建数据库，数据库名为BookStore，3）使用SQL语句，在数据库中创建表，设定表的主键约束、外键约束、检查约束、非空约束。

4）写以下增删改查操作的SQL语句1、增加三条类型表数据2、增加三条教师表数据3、增加五条图书表数据4、某教师今天借了某书，涉及两个操作，一是图书表的状态改为“借出”，二是增加一个借还表记录5、将某书的单价改为32元。

5）写出创建以下视图的SQL语句1.查询某教师的借还书记录2.查询某本书的流转记录3.借出但未归还的图书视图4.借阅逾期视图5.图书分类统计册数和总价值的视图参照完成：1）表结构部门表催还记录表1）使用SQL语句，创建数据库，数据库名为BookStore，语句：Create Databse Bookstore2）使用SQL语句，在数据库中创建表，设定表的主键约束、外键约束、检查约束、非空约束。

语句：CREATE TABLE管理员表(管理员名nchar(10)NOT NULL,性别Char(2)not null DEFAULT'男',口令nchar(20)NOT NULL,CONSTRAINT PK_管理员PRIMARY KEY CLUSTERED(管理员名ASC))ON [PRIMARY]goCREATE TABLE类型表(类型名nchar(10)NOT NULL,借阅期限tinyint NOT NULL,CONSTRAINT PK_类型PRIMARY KEY CLUSTERED(类型名ASC))ON [PRIMARY]goCREATE TABLE图书表(ISBN char(20)NOT NULL,书名nchar(20)NOT NULL,作者nchar(20)NOT NULL,出版社nchar(20)NOT NULL,出版时间datetime NOT NULL,单价money NOT NULL,类型名nchar(10)NOT NULL,状态nchar(2)NOT NULL CHECK(状态in('库存','借出')),CONSTRAINT PK_图书PRIMARY KEY CLUSTERED(ISBN ASC),CONSTRAINT FK_图书_类型FOREIGN KEY(类型名)REFERENCES类型表(类型名)ON UPDATE CASCADEON DELETE CASCADE)ON [PRIMARY]--==goCREATE TABLE部门表(部门名nchar(10)NOT NULL,电话char(11),CONSTRAINT PK_部门表PRIMARY KEY CLUSTERED(部门名ASC))ON [PRIMARY]goCREATE TABLE教师表(工号char(4)NOT NULL,姓名nchar(4)NOT NULL,性别Char(2)not null DEFAULT'男',Email char(30)NOT NULL,部门名nchar(10)NOT NULL,CONSTRAINT PK_教师表PRIMARY KEY CLUSTERED(工号ASC),CONSTRAINT FK_部门表_教师表FOREIGN KEY(部门名)REFERENCES部门表(部门名) ON UPDATE CASCADEON DELETE CASCADE,)ON [PRIMARY]goCREATE TABLE借还记录表(业务编号int identity(0,1)NOT NULL,ISBN char(20)NOT NULL,工号char(4)NOT NULL,代办理人char(10)NOT NULL,借出日期datetime NOT NULL,还办理人char(10)NULL,归还日期datetime NULL,CONSTRAINT PK_借还记录表PRIMARY KEY CLUSTERED(业务编号ASC),CONSTRAINT FK_借还记录表_教师表FOREIGN KEY(工号)REFERENCES教师表(工号)ON UPDATE CASCADEON DELETE CASCADE,CONSTRAINT FK_借还记录表_借还记录表FOREIGN KEY(ISBN)REFERENCES图书表(ISBN) ON UPDATE CASCADEON DELETE CASCADE)ON [PRIMARY]GO--CREATE TABLE催还记录表(业务编号int identity(0,1)NOT NULL,ISBN char(20)NOT NULL,工号char(4)NOT NULL,办理人char(10)NOT NULL,催还日期datetime,CONSTRAINT PK_催还记录表PRIMARY KEY CLUSTERED(业务编号ASC),CONSTRAINT FK_催还记录表_教师表FOREIGN KEY(工号)REFERENCES教师表(工号)ON UPDATE CASCADEON DELETE CASCADE,CONSTRAINT FK_催还记录表_图书FOREIGN KEY(ISBN)REFERENCES图书表(ISBN)ON UPDATE CASCADEON DELETE CASCADE)ON [PRIMARY]3）写以下增删改查操作的SQL语句1.增加三条类型表数据语句：goINSERT INTO类型表(类型名,借阅期限)VALUES('计算机',150)INSERT INTO类型表(类型名,借阅期限)VALUES('文学',180)INSERT INTO类型表(类型名,借阅期限)VALUES('经济',30)--delete 类型表--select * from 类型表2.增加三条部门表数据语句：goINSERT INTO部门表(部门名,电话)VALUES('网络系','020-*******')INSERT INTO部门表(部门名,电话)VALUES('外语系','020-*******')INSERT INTO部门表(部门名,电话)VALUES('管理系','020-*******')--select * from 部门表3.增加三条教师表数据语句：goINSERT INTO教师表(工号,姓名,性别,Email ,部门名)VALUES('J001','张三','女','zs@','网络系')INSERT INTO教师表(工号,姓名,性别,Email ,部门名)VALUES('J002','李四','男','LS@','网络系')INSERT INTO教师表(工号,姓名,性别,Email ,部门名)VALUES('J003','王五','男','ww@','外语系')--select * from 教师表4.增加五条图书表数据语句：goINSERT INTO图书表(ISBN,书名,作者,出版社,出版时间,单价,类型名,状态)VALUES('9789302218661','C#','江红','清华大学出版社','2012.2.10',45,'计算机','库存') INSERT INTO图书表(ISBN,书名,作者,出版社,出版时间,单价,类型名,状态)VALUES('9787802229761','人生博弈密码','吕叔春','中国华侨出版社','2009.2.1',36.8,'文学','库存')INSERT INTO图书表(ISBN,书名,作者,出版社,出版时间,单价,类型名,状态)VALUES('9787301150894','经济学原理','梁小民','北京大学出版社','2009.4.1',54,'经济','借出')--select * from 图书表5.增加三条管理员表数据语句：goINSERT INTO管理员表(管理员名,性别,口令)VALUES('吴管','男','wg001')INSERT INTO管理员表(管理员名,性别,口令)VALUES('李管','女','Lg002')INSERT INTO管理员表(管理员名,性别,口令)VALUES('徐管','男','Xg003')--select * from 管理员表go--select * from 教师表----select * from 部门表--select * from 类型表--select * from 图书表--select * from 管理员表6.某教师今天借了某书，涉及两个操作，一是图书表的状态改为“借出”，二是增加一个借还表记录语句：insert借还记录表(ISBN ,工号,代办理人,借出日期,还办理人,归还日期)values('9787802229761','J001','wg001','2012.3.20','Lg002','')insert借还记录表(ISBN ,工号,代办理人,借出日期,还办理人,归还日期)values('9787301150894','J001','wg001','2011.3.20','Lg002','')--select * from 借还记录表--delete 借还记录表goupdate图书表set状态='借出'where ISBN='9787802229761'--select * from 图书表7.将某书的单价改为32元。