数据库设计综合应用

实验数据库设计综合应用实验内容：任务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昆明10022002-11-2010020123李丹娜（021）86129618上海12082000-08-0610245082张岚（0871）66787968昆明10021998-06-0110204568王立群（021）89727982上海13032004-01-0810204567赵庆（0871）28008986昆明12082003-02-08(2)储户动态信息数据账号密码信誉存款额[元]状态10020089ASDZXC0286000110020123WERERT11678120110245082DFGASD13000000110204568SDFCVB035000110204567CVBGHJ112000001密码一般都是加密存储的。(3)储蓄所数据编号名称电话地址1001中国建行昆明分行秋涛支行（0871）88049082秋涛北街283号1002中国建行昆明分行武林支行（0871）68066080武林南路366号1208中国建行昆明分行下沙支行（0871）28800088下沙22号大街788号1303中国建行昆明分行滨江支行（0871）26880266滨江18号大街632号(4)存取款数据账号储蓄所编号标志存取金额存取日期1002012310021500002003-09-281024508212080800002003-10-161002012310010100002003-12-101002008913031120002003-12-101002012310020200002003-12-161024508210020100002004-01-081020456712081200002004-02-081024508212081300002004-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方式向“存取款”表中插入两条记录：账号储蓄所编号标志存取金额存取日期1002012312080200002004.06.281024508210021300002004.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语句并在系统中创建并在系统保存。可以使用截图