《数据库原理与应用》大作业
前言
《数据库原理与应用》课程的实践能力包括三个主要模块：一是根据需求设计数据库，二是用SQL脚本建库建表、数据查询更新和创建视图，三是编写存储过程和触发器。针对这三个能力模块，用一个案例作为背景，设计一个大作业，分三部分来完成。每一部分在对应课程内容结束之后布置给学生。
本文档包括四节，第一节描述需求，作为三次作业的基础；第二节是E-R模型设计和关系模型设计任务；第三节是编写SQL脚本建库建表、增删改查数据、创建视图的任务；第四节是编写存储过程和触发器的任务。
一、1. 11．12需求描述
1.1 需求故事
洁是图书阅览室的管理员，她到程序开发小组 “枫”，请他们帮助开发一套管理程序。他
们围坐在一起，开始以下对话：
洁：你们进来都看到了，那几排大柜子，全都放满了书，很多教师来借书，管理起来越来越难，希望帮我开发一个管理软件。
枫：你希望软件提供哪些功能。
洁：首先是能登记和查看图书信息，借书和还书的时候要方便操作。对于我来说，看图书的状态很重要，就是说要知道哪些图书已经借出去了，哪些书已经过了借阅期，此外还要做一些统计表……大概就是这些吧。
枫：你怎么登记借书呢？规定的借阅期有多长？过了借阅期怎么办？
洁：每个老师都有一个工号，我就计工号和图书的ISBN号。不同类型的书借阅期限是不一样的，比如文学作品的借阅期规定是15天，教学参考书的借阅期限是半年。有的人不自觉，过期好久都不还，我现在是翻记录本一个个地，然后打电话到他的部门，并且发催还邮件。
枫：同一ISBN只有一本书吗？
洁：是的，现在把书分成这样几类：教学参考书、文学作品、学术期刊、学术专著。
枫：你想统计一些什么数据
洁：主要是分类统计图书册数和价值、图书借阅次数。我们想知道哪些图书最受欢迎。
枫：阅览室是你一个人在管么？
洁：阅览室有两个人。我们俩都有一台计算机，最好两台计算机都可以用上这个软件。
1.2 需求分析
1）功能需求
图1-1：功能需求示意图
功能说明：
●教师信息管理：用于教师基本资料的增删改查。
●图书信息管理：用于图书基本信息的增删改查，分类统计图书册数和价值。
●借书登记：记录借书时间、所借图书、借书人、办理人。
●还书登记：记录还书时间、所还图书、还书人、办理人。
●催还：查询借阅逾期的借书信息，给借书人发，给借书人的部门打电话。
2）运行环境要求
图1-2：运行环境拓扑图
系统采用C/S模式，有两台PC和一台服务器，联成一个局域网。PC上安装图书管理软件的客户端，服务器上安装DBMS，服务器也可由两台PC中的一台来代替。
2． 作业1——E-R模型与关系模型设计
任务：
1）根据需求描述，分别为“图书管理系统”的不同功能模块绘制局部E-R图。
2）合并E-R图，绘制全局E-R图
3）将E-R图转换成关系模型，写出所有的关系模式。
4）在已经形成的关系模型下，举例说明连接运算、投影运算、选择运算。
3． 作业2——用SQL语句建库建表、增删改查数据、创建视图
任务：
1）在第二部分得到的图书管理系统关系模式的基础上，将每个关系转换为SQL Server支持的表结构，用表格形式表示出表结构。每一个表的结构描述如下例：
例：学生表的结构
例：学生表的结构
列名 | 数据类型 | 约束 | 说明 |
学号 | Char(10) | PK | |
姓名 | Nchar(4) | Not Null | |
性别 | Nchar(1) | Not Null 等于’男’或’女’ | |
生日 | Datetime | Null | |
班级编号 | Char(6) | FK，引用自班级表中的班级编号 | |
入校日期 | DateTime | 默认值为数据行的输入日期 | |
2）使用SQL语句，创建数据库，数据库名为BookStore，在数据库中创建表，设定表的主键约束、外键约束、检查约束、默认约束、非空约束、唯一约束。写出SQL。
3）写以下增删改查操作的SQL语句
a)输入图书类型数据
b)增加三个教师信息
c)增加五本书的信息
d)某教师今天借了某书，涉及两个操作，一是将书的状态改为“借出”，二是增加一个借书记录
e)将某书的单价改为31.23
f)查询某教师的借还书记录
g)查询某本书的流转记录
4）创建视图，写出SQL语句
a)借出图书视图
b)借阅逾期视图
c)图书分类统计册数视图
4． 作业3——存储过程与触发器设计任务
任务：
1）为BookStore数据库增加一个用户oper1，允许oper1对图书表和借还表进行修改操作。
use Bookstore
go
exec sp_addlogin oper1,'cx7105','bookstore'
go
exec sp_addremotelogin 'db_datareader','oper1'
GRANT UPDATE ON 图书馆 TO oper1
go
2）设计存储过程
a)数据初始化过程sp_Init。包含的操作有：所有的用于测试图书信息、教师信息、历史记录清空，输入图书分类、输入管理员信息、输入借书规定。
create proc sp_Init
@is char(20),
@bookname nchar(20),
@adds nchar(20),
@money money,
@status nchar(2)
AS
where ISBN=@is,@bookname =书名,@adds=出版社,@money=单价,@status=状态
insert 图书表(ISBN,书名,作者,出版社,出版时间,单价,类型名,状态 )
values (@is,@bookname,USER_NAME,@adds,getdate(),@money,TYPE_NAME,@status)
GO
b)某教师在某时间借了某本书，由某管理员办理。
create proc proc_lendbook
@ID char(4),
@is char(20)
AS
update 图书表
set 状态= '借出'
where ISBN=@is
insert 借还书表(ISBN,工号,代办理人,借出日期,还办理人,归还日期)
values(@is,@ID,USER_NAME(),getdate(),null,null)
go
c)某教师在某时间还了某本书，由某管理员办理
create proc proc_lendbook2
@ID char(4),
@is char(20)
AS
update 图书表
set 状态= '借出'
where ISBN=@is
insert 借还书表(ISBN,工号,代办理人,借出日期,还办理人,归还日期)
values(@is,@ID,null,null,USER_NAME(),getdate())
go
d)查看某人的全部借还书记录
基本的增删改查语句 create proc proc_lendbook3
@jobNumber nchar(4)
as
select a.姓名, c.书名, 借出日期, 归还日期
from 教师表 a join 借还书表 b on a.工号=b.工号
join 图书表 c on c.ISBN=b.ISBN
where a.工号= @jobNumber
go
EXEC proc_lendbook3 '杰克'
go
e)查看某本书的历史流转记录
create proc proc_hisbook
@is char(20)
AS
select a.姓名, c.书名, 借出日期, 归还日期
from 教师表 a join 借还书表 b on a.工号=b.工号
join 图书表 c on c.ISBN=b.ISBN
where c.ISBN=@is
go
EXEC proc_hisbook '网络工程师5天修炼'
go
f)某管理员在某时间给全部借阅过期者发催还邮件。
CREATE proc proc_writeEmailLog
@geter char(10)
AS
select a.姓名, c.书名, 借出日期, 归还日期
from 教师表 a join 借还书表 b on a.工号=b.工号
join 图书表 c on c.ISBN=b.ISBN
where b.催还者=@geter
go
EXEC proc_time @geter
发表评论