数据库大作业全参考答案

1、数据库大作业（全参考答案）标准化文件发布号：（9456EUATWKMWUBWUNNINNUL-DDQTY-KII 洁：是的，现在把书分成这样几类：教学参考书、文学作品、学术期刊、学术专著。 枫：你想统计一些什么数据洁：主妥是分类统计图书册数和价值、图书借阅次数。我们想知道哪些图书最受欢迎。枫：阅览室是你一个人在管么？洁：阅览室有两个人。我们俩都有一台计算机，最好两台计算机都可以用上这个软件。1.2需求分析 1）功能需求教师信息管理：用于教师基本资料的増删改查。图书信息管理:用于图书基本信息的増删改查，分类统计图书册数和价值。借书登记：记录借书时间、所借图书、借书人、办理人。还书登记：记录还书时

2、间、所还图书、还书人、办理人。催还：查询借阅逾期的借书信息，给借书人发电子邮件，给借书人的部门打电话。2) 运行环境要求)BMSPC1cLAN0图1-2 :运行环境拓扑图系统采用C/S模式，有两台PC和一台服务器，联成一个局域网。PC上安装图书管 理软件的客户端，服务器上安装DBMS,服务器也可由两台PC中的一台来代替。第二部分作业1ER模型与关系模型设计(满分8分)本部分的任务是：在需求分析的基础上，进行E-R图设计，然后将E-R模型转换为 关系模型。任务：1) 根据需求描述，绘制E-R图。2) 将E-R图转换成关系模型，写出所有的关系模式，并写出每个关系是按照 什么规则转换而成的。3) 在

3、已经形成的关系模型下，举例说明连接运算、投影运算、选择运算。参考答案：（属性写在了实体和联系图形内）任务：1）根据需求描述，分别为“图书管理系统”的不同功能模块绘制局部E-R图。教师信息管理：图书基本信息管理：借还书登记:13催还书登记:2）整体E-R图3）关系模式类型表（类型名,借阅期限），依据：实体转换为关系。图书表书名，作者出版社，出版时间，单价，类型名,状态）,依据：实体转换为关系，1 ：N的联系合并到N,在N方增加一个外键:类型名。注：状态属性为（库存.借岀）管理员表（管理员名,性别，口令），依据：实体转换为关系。教师表（教师工号,姓名,性别，Email,部门名），依据：实体转换为关

4、系，1 : N的联系合并到N,在N方增加一个外键:部门名。部门表（部门名,电话），依据：实体转换为关系。借还表（借还业务编号,ISBN,教师工号，借办理人，借出日期，还办理人，归 还日期），依据：M:N联系转换为一个关系，取联系的实体的键加联系属性作为其属性， 可以増加一个列作为主键，催还表（催还业务编号，ISBN,教师工号，办理人，催还日期），依据：M:N联系转换为一个关系，取联系的实体的键加联系属性作为其属性， 可以増加一个列作为主键n4）在已经形成的关系模型下，举例说明连接运算、投影运算、选择运算。连接运算：查看类型的图书已经超过了有效借出期限。（需要图书.类型，借还关 系）投影运算：查

5、看图书的书名、作者。选择运算：查看单价高于50元的图书。3作业2用SQL语句建库建表、增删改查数据、创建视图任务：1）根据关系规范化的要求，在第一部分得到的图书管理系统关系模式需要进行规范化吗？如果需要请写出写出符合3NF的关系模式。2)3)在得到3NF的图书管理系统关系模式的基础上，将每个关系转换为SQLServer支持的表结构，用表格形式表示出表结构。类型表列名类型约束类型名NChar (10)Pk借阅期限TinyintNot null图书表列名类型约束ISBNChar (20)Pk书名Nchar (10)Not null作者Nchar (4)Not null出版社Nchar (10)No

6、t null出版时间Datetime单价MoneyNot 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)检杳约束Emailchar (20)Not null部门名Nchar (10)FK references 部门表(部门

7、名）借还表列名类型约束借还业务编号IntPKISBNChar (20)FK references 图书表(ISBN)教师工号Char (10)Fk references 教师表(教师工号）借办代理人Nchar (4)Not null借出日期DatetimeNot null还办代理人Nchar (4)归还日期Datetime催还表列名类型约束催还业务编号IntPkISBNChar (20)FK references 图书表(ISBN)教师工号Char (10)FK references 教师表(教师工号）办代理人Nchar (4)null催还日期Datetimenull使用SQL语句，创建数据库

8、，数据库名为Bookstore,在数据库中创建表，设定表的主键约束、外键约束、检查约束、默认约束、非空约束、唯一约束。写出SQL。create database BookStorecreate table 类型表1 类型名 nchar 101 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

9、null,类型名 nchar ： 10 not null foreign key references 类型表丨类型名on delete no action on update cascade.状态nchar( 2) not null check 状态='库存'or状态='借出')create table管理员表(管理员名 nchar 4) primary key,性别nchar(1) check；性别二'男'or性别二'女口令 nchar 10 not null)create table 部门表(部门名 nchar (10 primar

10、y key,电话 char 'll) not null create table 教师表(教师工号 char 101 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 c

11、har 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

12、),ISBN char 120 foreign key references 图书表 11SBN 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 C 文学15）,（'教学参考书'，180

13、）,C音乐，45）2、增加三个教师信息insert into部门表（部门名、电话）values C 基础部'，1 139*1 ）,C 电子系',* 136*1）,C音乐艺术学院1 r 1 186*1）insert into教师表（教师工号，姓名，性别.Email,部门名）values 0001/ ,'张三',* 男'，'333qq com* ,'基础部'）,C 0002,. 李四'男'，'44440139. com,'电子系'），C 0003*，'王五'，'女'

14、;，'55555qq. com','音乐艺术学院'）3、增加五本书的信息insert into图书表（ISBN.书名，作者.出版社.出版时间.单价.类型名，状态）values （'9787533951109','夜航船'，'张岱'，'浙江文艺出版社','2018. 8. 8', 99. 8,文学'，'库存'）略4、某教师今天借了某书，涉及两个操作，一是将书的状态改为“借出"，二是增加一个借书记录update图书表set状态='借出'w

15、here书名='夜航船'insert into借还表（借还业务编号.ISBN.教师工号，借办代理人、借出日期）values（* 3123', 9787533954109, ' 000f ,'张三','2019. 5. 23'）5、将某书的作者改为自己的姓名update图书表set 作者=，*where书名='夜航船'6、查询某教师的借还书记录select *from借还表where教师工号=* OOOT7、查询某本书的流转记录select *from借还表where ISBN=，9787533954109*创建视图

16、，写出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书籍

17、册数,类型名from图书表group by类型名4.作业3存储过程与触发器设计任务任务：1) 为Bookstore数据库増加一个用户operl,允许operl对图书表和借还表进行修use BookStoregoexec sp_addlogin operl, ' (此处为密码BookStore'goexec sp_addrolemember dbdatareader' ,' operl'grant update on 图书衣 to operlgrant update on 借还表 to operl2) 设计存储过程a) 数据初始化过程sp_lnito包含的

18、操作有：所有的用于测试图书信息、教师信息、 历史记录清空，输入图书分类、输入管理员信息、输入借书规定。略，界面进行输入或者代码输入作业用数据b) 某教师在某时间借了某本书，由某管理员办理。create procedure proc_lendbookaid char(10),atime datetime.ISBX char(20J,aname nchar'41asbeginupdate图书表set状态='借出'where ISBN=ISBNinsert into借还表(ISBN教师工号借办代理人，借出日期：values ©ISBN, aid. ©ana

19、me. ®atimeendC)某教师在某时间还了某本书，由某管理员办理create procedure proc_returnbookbid char 10),btime datetime,ISBN char 20),bname nchar'4:asbeginupdate图书表set状态='库存'where ISBN=ISBNupdate借还表set还办代理人二©bname.归还日期二btime where 教师工号二bid and ISBN二ISB endd）查看某人的全部借还书记录create procedure proc_getReaderlo

20、gRename nchar '：4 >,cid char-10asbeginselect b.姓名,c.书名，a.借出日期,归还日期from借还表a join教师表b on a.教师匸号二b.教师工号join图书表c on a. ISBN=c. ISBN where b.姓名=6cname or b.教师工号=0cidende) 查看某本书的历史流转记录create procedure proc_getBooklogbookname nchar 10 ,ISBX char(20)asbeginselect教师表姓名，图书表书名.借还表借出日期.归还日期from借还表a join教

21、师表b on a.教师匸号二b.教师工号join图书表c on a. ISBN=c. ISBN where a. ISBNQISBX or c.书名Sbooknameendf) 某管理员在某时间给全部借阅过期者发催还邮件。部分有错误的需要将修改催还业务编号为自增长(identity (1,1)，还有整个表都要允许空值：例:alter table催还表alter column 办代理人 char nullcreate procedure proc_utiteEmai1Logdname char 14）,©dtime datetimeasbegininsert into催还表（教师工号）select教师工号from借还表a join图书表b on a. ISBN二b. ISBN join类型表c on b.类型名二c.类型名 where datediff （day, a借出日期,getdate 1） c借阅期限and a.归还日期is null update催还表set办代理人=dname,催还日期=dtimewhere催还日期is nullendg）对书名进行模糊搜索（搜索条件可以是多个模糊列值的组合，例如：搜索书名包含“网络”两个字，并且是近3年出版的图书