2024年2月6日发(作者：)

可编辑修改

《数据库原理与应用》综合设计任务书

前言

《数据库原理与应用》课程的重点知识模块包括：1）数据库设计、2）用SQL实现建库、建表、查询、更新、和创建视图、3）存储过程和触发器设计。针对这三个应用能力，用一个案例作为背景，布置三次大作业。

在校大学生都能理解“图书管理系统”的应用场合和业务流程。因此，以图书管理系统作为案例来布置作业，可以降低业务分析难度，让学生将主要精力放在知识消化与技术应用上。

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

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

第一部分 案例的需求描述

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

1.1 需求故事

洁是图书阅览室的管理员，她找到程序开发小组 “枫”，请他们帮助开发一套管理程序。他们围坐在一起，开始以下对话：

洁：你们进来都看到了，那几排大柜子，全都放满了书，很多教师来借书，管理起来越来越难，希望帮我开发一个管理软件。

枫：你希望软件提供哪些功能。

洁：首先是能登记和查看图书信息，借书和还书的时候要方便操作。对于我来说，看图书的状态很重要，就是说要知道哪些图书已经借出去了，哪些书已经过了借阅期，此外还要做一些统计表……大概就是这些吧。

枫：你怎么登记借书呢？规定的借阅期有多长？过了借阅期怎么办？

洁：每个老师都有一个工号，我就计工号和图书的ISBN号，一个人最多能借2本书。不同类型的书借阅期限是不一样的，比如文学作品的借阅期规定是15天，教学参考书的借阅期限是半年。有的人不自觉，过期好久都不还，我现在是翻记录本一个个地找，然后打电话到他的部门，并且发催还邮件。

枫：同一ISBN只有一本书吗？

洁：是的，现在把书分成这样几类：教学参考书、文学作品、学术期刊、学术专著。

枫：你想统计一些什么数据

洁：主要是分类统计图书册数和价值、图书借阅次数。我们想知道哪些图书最受欢迎。

枫：阅览室是你一个人在管么？

洁：阅览室有两个人。我们俩都有一台计算机，最好两台计算机都可以用上这个软件。

1.2 需求分析

1）功能需求精选doc

可编辑修改

教师信息管理图书信息管理借书登记管理员还书登记催还

图1-1：功能需求示意图

教师信息管理：用于教师基本资料的增删改查。

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

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

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

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

2）运行环境要求

DBMSPCLANPC

图1-2：运行环境拓扑图

系统采用C/S模式，有两台PC和一台服务器，联成一个局域网。PC上安装图书管理软件的客户端，服务器上安装DBMS，服务器也可由两台PC中的一台来代替。

精选doc

可编辑修改

第二部分 作业1——E-R模型与关系模型设计

（满分8分）

本部分的任务是：在需求分析的基础上，进行E-R图设计，然后将E-R模型转换为关系模型。

任务：

1）

2）

根据需求描述，绘制E-R图。

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

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

参考答案：（属性写在了实体和联系图形内）

任务：

1） 根据需求描述，分别为“图书管理系统”的不同功能模块绘制局部E-R图。

教师信息管理：

教师（教师工号，姓名，性别，电话，Email）

N

属于

1

部门（部门名，电话）

图书基本信息管理：

图书（ISBN,书名，作者，出版社，出版时间，单价，状态）

N

归类

1

类型（类型名，借阅期限）

精选doc

可编辑修改

借还书登记：

图书 N

借/还（借日期，还日期））

M

教师

p

管理员（管理员名，口令）

催还书登记：

图书

N

催/还

（催还日期）

M

教师

p

管理员（用户名，口令）

2） 整体E-R图

精选doc

可编辑修改

类型（类型1

名，借阅期限）

1

N

归类

P

图书（ISBN,书名，作者，出版社，出版时间，单价，状态）

借/还（借日期，还日期）

N

管理员（管理员名，口令）

M

催/还（催还日期）

P

M

教师（教师工号，姓名，性别，电N

属于

1部门（部门名，电话）

精选doc

可编辑修改

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）

Tinyint

约束

Pk

Not null

图书表

列名

ISBN

类型

Char（20）

约束

Pk

精选doc

可编辑修改

书名

作者

出版社

出版时间

单价

类型名

状态

Nchar（10）

Nchar（4）

Nchar（10）

Datetime

Money

NChar（10）

NChar（2）

Not null

Not null

Not null

Not null

FK references类型表(类型名)

检查约束

管理员表

列名

管理员名

性别

口令

类型

Nchar（4）

Nchar（1）

Nchar（10）

约束

Pk

检查约束

Not null

部门表

列名

部门名

电话

类型

Nchar（10）

char（11）

约束

PK

Not null

教师表

列名

教师工号

姓名

性别

Email

部门名

类型

Char（10）

Nchar（4）

Nchar（1）

char（20）

Nchar（10）

约束

Pk

Not null

检查约束

Not null

FK references 部门表(部门名)

借还表

列名

借还业务编号

ISBN

教师工号

借办代理人

类型

Int

Char（20）

Char（10）

Nchar（4）

约束

PK

FK references 图书表(ISBN)

Fk references教师表(教师工号)

Not null

精选doc

可编辑修改

借出日期

还办代理人

归还日期

Datetime

Nchar（4）

Datetime

Not null

催还表

列名

催还业务编号

ISBN

约束

Pk

FK references图书表(ISBN)

教师工号 Char（10） FK references教师表(教师工号)

办代理人 Nchar（4） null

催还日期 Datetime null

使用SQL语句，创建数据库，数据库名为BookStore，在数据库中创建表，设定表的主键约束、外键约束、检查约束、默认约束、非空约束、唯一约束。写出SQL。

create database BookStore

create table 类型表

( 类型名 nchar(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,

类型名 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) primary key,类型

Int

Char（20）

精选doc

可编辑修改

电话 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********'),

insert into 教师表(教师工号,姓名,性别,Email,部门名)

values('0001','张三','男','333@','基础部'),('电子系','136********'),

('音乐艺术学院','186********')

精选doc

可编辑修改

('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 借出图书

as

select *

from 图书表

where 状态='借出'

9、借阅逾期视图

create view 借阅逾期

as

select 借还表.借办代理人,借还表.教师工号，图书表.书名

from 图书表 inner join 借还表 on 图书表.ISBN=借还表.ISBN inner join 类型表 on 图书表.类型名=类型表.类型名

where 图书表.状态='借出' and datediff(day,借还表.借出日期,getdate())>类型表.借阅期限精选doc

可编辑修改

10、图书分类统计册数视图

create view 分类统计

as

select count(*) as 书籍册数,类型名

from 图书表

group by 类型名

4． 作业3——存储过程与触发器设计任务

任务：

1） 为BookStore数据库增加一个用户oper1，允许oper1对图书表和借还表进行修改操作。

use BookStore

go

exec sp_addlogin operl,'（此处为密码）','BookStore'

go

exec sp_addrolemember 'db_datareader','operl'

grant update on 图书表 to operl

grant update on 借还表 to operl

2） 设计存储过程

a) 数据初始化过程sp_Init。包含的操作有：所有的用于测试图书信息、教师信息、历史记录清空，输入图书分类、输入管理员信息、输入借书规定。

略，界面进行输入或者代码输入作业用数据

b) 某教师在某时间借了某本书，由某管理员办理。

create procedure proc_lendbook

@aid char(10),

@atime datetime,

@ISBN char(20),

@aname nchar(4)

as

begin

update 图书表

set 状态='借出'

where ISBN=@ISBN

insert into 借还表(ISBN,教师工号,借办代理人,借出日期)

values(@ISBN,@aid,@aname,@atime)

end

c) 某教师在某时间还了某本书，由某管理员办理

create procedure proc_returnbook

@bid char(10),

@btime datetime,精选doc

可编辑修改

@ISBN char(20),

@bname nchar(4)

as

begin

update 图书表

set 状态='库存'

where ISBN=@ISBN

update 借还表

set 还办代理人=@bname,归还日期=@btime

where 教师工号=@bid and ISBN=@ISBN

end

d) 查看某人的全部借还书记录

create procedure proc_getReaderlog

@cname nchar(4),

@cid char(10)

as

begin

select b.姓名,c.书名,a.借出日期,归还日期

from 借还表 a join 教师表 b on a.教师工号=b.教师工号 join 图书表 c on =

where b.姓名=@cname or b.教师工号=@cid

end

e) 查看某本书的历史流转记录

create procedure proc_getBooklog

@bookname nchar(10),

@ISBN char(20)

as

begin

select 教师表.姓名,图书表.书名,借还表.借出日期,归还日期

from 借还表 a join 教师表 b on a.教师工号=b.教师工号 join 图书表 c on =

where =@ISBN or c.书名=@bookname

end

f) 某管理员在某时间给全部借阅过期者发催还邮件。

部分有错误的需要将修改催还业务编号为自增长（identity（1,1））,还有整个表都要允许空值：

例：alter table 催还表

alter column 办代理人 char(4) null

精选doc

可编辑修改

create procedure proc_writeEmailLog

@dname char(4),

@dtime datetime

as

begin

insert into 催还表(教师工号)

select 教师工号

from 借还表 a join 图书表 b on = join 类型表 c on b.类型名=c.类型名

where datediff(day, a.借出日期,getdate())>c.借阅期限 and a.归还日期 is null

update 催还表

set 办代理人=@dname,催还日期=@dtime

where 催还日期 is null

end

g)对书名进行模糊搜索（搜索条件可以是多个模糊列值的组合，例如：搜索书名包含“网络”两个字，并且是近3年出版的图书）

create procedure proc_search

@bookname nchar(10),

@max tinyint

as

begin

select *

from 图书表

where datediff(year,出版时间,getdate())<@max and 书名 like '%'+@bookname+'%'

end

3） 设计触发器

a) 当oper1用户对图书状态进行修改时，自动在借还表追加相应的数据。

create trigger tri_a

on 图书表 after update

as

declare @status char(4)

declare @ISBN char(20)

select @status=状态 from inserted

select @ISBN=ISBN from inserted

if user_name()='operl'

begin

if @status='借出'

insert into 借还表(ISBN,借办代理人,借出日期)精选doc

可编辑修改

values(@ISBN,user_name(),getdate())

else if @status='库存'

update 借还表

set 还办代理人=user_name(),归还日期=getdate()

where ISBN=@ISBN

end

b) 不允许oper1用户对催还表信息进行删除操作，如果删除进行回滚。

create trigger tri_b

on 催还表 after delete

as

if user_name()='operl'

begin

if (select count(*) from deleted)>0

rollback

end

.

精选doc