数据库大作业(全·参考答案)

《数据库原理与应用》综合设计任务书前言《数据库原理与应用》课程的重点知识模块包括：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元的图书。

3．作业2——用SQL语句建库建表、增删改查数据、创建视图任务：1）根据关系规范化的要求，在第一部分得到的图书管理系统关系模式需要进行规范化吗？如果需要，请写出写出符合3NF的关系模式。

2）在得到3NF的图书管理系统关系模式的基础上，将每个关系转换为SQL Server支持的表结构，用表格形式表示出表结构。

类型表列名类型约束类型名NChar（10）Pk借阅期限Tinyint Not null图书表列名类型约束ISBN Char（20）Pk书名Nchar（10）Not null作者Nchar（4）Not null出版社Nchar（10）Not null出版时间Datetime单价Money Not null类型名NChar（10）FK references类型表(类型名)状态NChar（2）检查约束管理员表列名类型约束管理员名Nchar（4）Pk性别Nchar（1）检查约束口令Nchar（10）Not null部门表列名类型约束部门名Nchar（10）PK电话char（11）Not null教师表列名类型约束教师工号Char（10）Pk姓名Nchar（4）Not null性别Nchar（1）检查约束Email char（20）Not null部门名Nchar（10）FK references 部门表(部门名)借还表列名类型约束借还业务编号Int PKISBN Char（20）FK references 图书表(ISBN)教师工号Char（10）Fk references教师表(教师工号)借办代理人Nchar（4）Not null借出日期Datetime Not null还办代理人Nchar（4）归还日期Datetime催还表列名类型约束催还业务编号Int PkISBN Char（20）FK references图书表(ISBN)教师工号Char（10）FK references教师表(教师工号)办代理人Nchar（4）null催还日期Datetime null使用SQL语句，创建数据库，数据库名为BookStore，在数据库中创建表，设定表的主键约束、外键约束、检查约束、默认约束、非空约束、唯一约束。

写出SQL。

create database BookStorecreate table类型表(类型名 n char(10)not null primary key,借阅期限tinyint not null)create table图书表(ISBN char(20)primary key,书名nchar(10)not null,作者nchar(4)not null,出版社nchar(10)not null,出版时间datetime,单价money not null,类型名 n char(10)not null foreign key references类型表(类型名)on delete no action on update cascade,状态 n char(2)not null check(状态='库存'or状态='借出'))create table管理员表(管理员名nchar(4)primary key,性别nchar(1)check(性别='男'or性别='女'),口令nchar(10)not null)create table部门表(部门名nchar(10)primary key,电话char(11) not null)create table教师表(教师工号char(10)primary key,姓名nchar(4)not null,性别nchar(1)check(性别='男'or性别='女'),Email char(20)not null,部门名nchar(10)not null foreign key references部门表(部门名)on delete no action on update cascade )create table借还表(借还业务编号int primary key,ISBN char(20)foreign key references图书表(ISBN)on delete no action on update cascade,教师工号char(10)foreign key references教师表(教师工号)on delete no action on update cascade,借办代理人nchar(4)not null,借出日期datetime not null,还办代理人nchar(4)null,归还日期datetime null)create table催还表(催还业务编号int primary key identity(1,1),ISBN char(20)foreign key references图书表(ISBN)on delete no action on update cascade,教师工号char(10)foreign key references教师表(教师工号)on delete no action on update cascade,办代理人nchar(4)null,催还日期datetime null)写以下增删改查操作的SQL语句1、输入图书类型数据insert into类型表(类型名,借阅期限)values('文学',15),('教学参考书',180),('音乐',45)2、增加三个教师信息insert into部门表(部门名,电话)values('基础部','139********'),('电子系','136********'),('音乐艺术学院','186********')insert into教师表(教师工号,姓名,性别,Email,部门名)values('0001','张三','男','333@','基础部'),('0002','李四','男','4444@','电子系'),('0003','王五','女','55555@','音乐艺术学院')3、增加五本书的信息insert into图书表(ISBN,书名,作者,出版社,出版时间,单价,类型名,状态)values('9787533954109','夜航船', '张岱','浙江文艺出版社', '2018.8.8',99.8,文学 ','库存')略4、某教师今天借了某书，涉及两个操作，一是将书的状态改为“借出”，二是增加一个借书记录update图书表set状态='借出'where书名='夜航船'insert into借还表(借还业务编号,ISBN,教师工号,借办代理人,借出日期)values('3123','9787533954109','0001','张三','2019.5.23')5、将某书的作者改为自己的姓名update图书表set作者='***'where书名='夜航船'6、查询某教师的借还书记录select*from借还表where教师工号='0001'7、查询某本书的流转记录select*from借还表where ISBN='9787533954109'创建视图，写出SQL语句8、借出图书视图create view借出图书asselect*from图书表where状态='借出'9、借阅逾期视图create view借阅逾期asselect借还表.借办代理人,借还表.教师工号，图书表.书名from图书表inner join借还表on图书表.ISBN=借还表.ISBN inner join类型表on图书表.类型名=类型表.类型名where图书表.状态='借出'and datediff(day,借还表.借出日期,getdate())>类型表.借阅期限10、图书分类统计册数视图create view分类统计asselect count(*)as书籍册数,类型名from图书表group by类型名4．作业3——存储过程与触发器设计任务任务：1）为BookStore数据库增加一个用户oper1，允许oper1对图书表和借还表进行修改操作。