数据库设计综合应用.docx
《数据库设计综合应用.docx》由会员分享，可在线阅读，更多相关《数据库设计综合应用.docx（8页珍藏版）》请在冰豆网上搜索。
数据库设计综合应用
实验数据库设计综合应用
实验内容：
任务1：
活期储蓄管理系统数据库设计
1．问题的提出
在活期储蓄管理过程中，“储户”通过“存取款”与“储蓄所”发生业务联系。
试设计活期储蓄管理系统数据库的概念模型、关系模型并确定约束。
2．系统需求分析
通过对数据库用户（储蓄所业务人员）的调查，对用户的需求进行分析得知：
（1）储户是指在某个储蓄所开户的人。
该储蓄所称为储户的开户行。
（2）一个储蓄所可以有多个储户，每个储户有惟一的账号。
（3）每个储户可以在多个允许发生业务的储蓄所进行存取款。
（4）储户按信誉分为“一般”和“良好”两种（分别用0和1表示）。
信誉“一般”的储户不允许透支。
信誉“良好”的储户可以透支，但不能超过5万元。
（5）储户按状态分为：
“正常”和“挂失”两种（分别用0、1表示）。
状态为“正常”的储户允许存取款，状态为“挂失”的储户不允许存取款。
（6）储户的信息有：
账号、姓名、密码、电话、地址、信誉、存款额、开户行编号、开户日期、状态等。
（7）储蓄所的信息有：
编号、名称、电话、地址、负责人
（8）储户进行存取款时应该提供账号、存取标志（1：
表示存款，0：
表示取款）、存取金额、存取日期等信息，储蓄所首先要对储户的身份进行验证，对合法的储户再根据“信誉”判断是否可以发生此次业务。
若发生业务则记录相应信息，修改储户的存款额。
3．概念模型设计
请你根据系统需求分析设计E-R图。
（请在此处用E-R图代替）
4．关系模型设计
请你根据E-R图转换成对应的关系模型，并标出主码
（请在此处用对应的关系模型代替）
5．约束关系
（1）实体完整性
例如实体“储户”的主码“账号”不能为空等。
（请你在此处描述出全部的实体完整性。
）
（2）参照完整性
例如参照关系“存取款”的属性“账号”和储蓄所“编码”分别是被参照关系“储户”和“储蓄所”的外码。
因此，参照关系“存取款”的属性“账号”的值必须是被参照关系“储户”中某一元组的“账号”的值。
（请你在此处描述出全部的参照完整性。
）
（3）用户定义的完整性
要求如下：
对信誉为“一般”的储户取款时，只有当：
“储户.存款额”－“存取款.金额”≥0
时才可以发生取款业务。
同理，对信誉为“良好”的储户取款时，只要：
“储户.存款额”－“存取款.金额”≥－50000（元）
时便可以发生取款业务。
（请自行思考在本系统中是否还有其他用户定义的完整性，并补充在此处）
6．关系结构描述
请你仿照表1在此处描述出活期储蓄管理系统的关系结构，实际上是给出了该系统数据库模式的型。
表1学生数据库关系结构描述
关系名称
属性名称
数据类型
宽度
说明
学生
学号
字符串
10
姓名
字符串
8
电话
字符串
16
出生年月
日期时间型
8
性别
字符串
15
日期时间型
8
课程
课程号
字符串
15
课程名
字符串
6
先修课程号
货币型
8
状态
整数
1
取值为0、1
学生选课
学号
字符串
10
课程号
字符串
40
成绩
字符串
16
任务2：
创建活期储蓄管理系统数据库
--数据库的物理设计与实现
这里给出的活期储蓄数据库的物理设计与实现，实际上就是数据库模式到内模式（数据库的存储模式）的映射。
1．用SQLServer2008创建任务1描述的数据库
数据库名称为：
活期储蓄。
数据主文件为：
活期储蓄_data.mdf，存储在“D:
\data”（或者是自行定义的文件夹），存储空间初始值为5MB，最大空间为100MB，文件增量10％。
数据次数据文件为：
活期储蓄_data2.mdf，存储在“D:
\data”（或者是自行定义的文件夹），存储空间初始值为5MB，最大空间为100MB，文件增量10％。
日志文件为：
活期储蓄_log.ldf，存储在“E:
\data”（或者是自行定义的文件夹，但是不要和数据文件在一起），存储空间初始值为5MB，最大空间为200MB，文件增量5MB。
实现上述要求的Transact-SQL语句为：
（请你在此处写出实现上述要求的Transact-SQL语句）
2．数据库的管理
1）收缩数据库：
分别使用SHRINKDATABASE和SHRINKFILE进行收缩活期储蓄数据库
2）数据库更名
3）查看数据库的属性
4）数据库附加和分离
要求：
用SSMS完成以上工作，尝试使用Transact-SQL完成部分工作，写出相应的Transact-SQL语句。
2．创建表
根据任务1描述的关系结构，建立表，并设置实体完整性、参照完整性的约束。
实现上述要求的Transact-SQL语句为：
（请你在此处写出实现上述要求的Transact-SQL语句）
3．添加数据
下面的示例数据，实际上是给出了活期储蓄管理系统数据库模式中各关系型的一个值，这些值构成数据库模式的一个值（或一个状态）。
（1） 储户基本信息数据
账号
姓名
电话
地址
开户行
开户日期
10020089
张晓军
（0871）22959618
昆明
1002
2002-11-20
10020123
李丹娜
（021）86129618
上海
1208
2000-08-06
10245082
张岚
（0871）66787968
昆明
1002
1998-06-01
10204568
王立群
（021）89727982
上海
1303
2004-01-08
10204567
赵庆
（0871）28008986
昆明
1208
2003-02-08
（2） 储户动态信息数据
账号
密码
信誉
存款额[元]
状态
10020089
ASDZXC
0
286000
1
10020123
WERERT
1
1678120
1
10245082
DFGASD
1
3000000
1
10204568
SDFCVB
0
35000
1
10204567
CVBGHJ
1
1200000
1
密码一般都是加密存储的。
（3） 储蓄所数据
编号
名称
电话
地址
1001
中国建行昆明分行秋涛支行
（0871）88049082
秋涛北街283号
1002
中国建行昆明分行武林支行
（0871）68066080
武林南路366号
1208
中国建行昆明分行下沙支行
（0871）28800088
下沙22号大街788号
1303
中国建行昆明分行滨江支行
（0871）26880266
滨江18号大街632号
（4）存取款数据
账号
储蓄所编号
标志
存取金额
存取日期
10020123
1002
1
50000
2003-09-28
10245082
1208
0
80000
2003-10-16
10020123
1001
0
10000
2003-12-10
10020089
1303
1
12000
2003-12-10
10020123
1002
0
20000
2003-12-16
10245082
1002
0
10000
2004-01-08
10204567
1208
1
20000
2004-02-08
10245082
1208
1
30000
2004-03-16
实现上述要求的Transact-SQL语句分别为：
（请你在此处写出实现上述要求的Transact-SQL语句，可以用截图）
任务3：
活期储蓄管理系统数据库上的一般操作
1．录入数据（略）
2．数据查询
请你在上述给出的活期储蓄管理系统数据库表数据中，写出完成如下诸查询SQL命令。
（1）查询储户的姓名和存款额
（2）查询账号为10245082的储户的存取款记录。
（3）查询编号为1002的储蓄所2003.10-2004.10的存取款业务详细记录。
（4）查询编号为1002的储蓄所的存取款业务汇总情况。
（1、请你在此处写出实现上述要求的Transact-SQL语句及相应的查询结果，可以截图；
2、自行分析该系统，还可以添加那些常用的查询，并用Transact-SQL语句实现）
任务4：
建立活期储蓄管理系统数据库的触发器和存储过程
1．触发器的编写
1）问题提出：
利用SSMS（图形界面）或者T-SQL方式向“存取款”表中插入两条记录：
账号
储蓄所编号
标志
存取金额
存取日期
10020123
1208
0
20000
2004.06.28
10245082
1002
1
30000
2004.7.16
检查“储户动态信息表”，会发现以上两个储户存取款后，储户的“存款额”值并未发生改变。
这显然是不符合实际情况的。
我们希望，每当“储户”在某一个“储蓄所”进行“存取款”时，应同时自动修改该储户的“存款额”。
这一功能可以通过在表“存取款”上建立“INSERT”触发器完成。
2）在“存取款”表上创建“INSERT”触发器
（1、请你在此处写出实现上述要求的Transact-SQL语句；
2、在SQLServer2008中创建该触发器，并保存在活期储蓄管理系统数据库中；
3、设计相应的数据操作验证该触发器是否正确。
如：
向“存取款“表中插入记录，“储户动态信息”表中相应数据是否发生变化。
）
2．编写验证储户“密码”的存储过程
1）问题要求：
将储户输入的“密码”与“用户动态信息”表中保存的“密码”值进行比较，回答正确返回“1”，不正确返回“0”。
如果不正确，将无法进行以后的业务活动。
2）存储过程设计
（1、请你在此处写出实现上述要求的Transact-SQL语句程序（一个存储过程）
；提示：
该存储过程可以带有入口参数（储户账号和储户输入的密码）
2、在SQLServer2008中创建该存储过程，并保存在活期储蓄管理系统数据库中；
3、设计相应的数据操作验证该存储过程是否正确。
）
任务5：
建立活期储蓄管理系统数据库的视图和索引
1、创建视图
（1）创建视图“存款”，包括信息：
储户基本信息.账号、储户基本信息.姓名、储户动态信息.存款额
以便于储户简单查询。
实现上述要求的Transact-SQL语句为：
请你在此处写出实现上述要求的Transact-SQL语句并在系统中创建并在系统保存。
可以使用截图
（2）创建视图“储户”，信息来自“储户基本信息”和“储户动态信息”。
前台应用程序，输入开户信息时，应该包括这两个表的内容，建立视图后，应用程序可以只调用这个视图，从而简化了前台应用程序对数据库的调用。
实现上述要求的Transact-SQL语句为：
请你在此处写出实现上述要求的Transact-SQL语句并在系统中创建并在系统保存。
可以使用截图
2、创建索引
在表“存取款”上按“存取日期”的建立索引，以利于按时间段的业务查询。
实现上述要求的Transact-SQL语句为：
请你在此处写出实现上述要求的Transact-SQL语句并在系统中创建并在系统保存。
可以使用截图
升级会员 