数据库系统原理-实验指导书.doc

数据库原理与应用合性实验指导书 实验名称：数据库设计实验性质：综合性实验实验类别：基础 专业基础 专业 所涉及课程及知识点：数据库原理与应用计划学时：12学时 北京城市学院软件与信息管理学部目 录一、实验目的1二、实验仪器设备和材料清单1三、实验内容1四、实验要求3五、实验步骤示例3六、考核形式12七、实验报告要求13八、思考题13九、参考教材13一、实验目的1掌握数据库和数据模型的基本概念、数据模型的三要素、概念模型的表示方法、数据库系统的模式结构与体系结构、DBMS的功能与组成。2通过设计E-R模型并分析，掌握从实际问题出发建立概念模型的方法。3掌握将E-R模型转换为关系模型的方法。4掌握使用sqlserver创建数据库、数据表、表间关系等的方法。5掌握数据完整性的基础知识；学会使用约束来实施数据完整性的保护；掌握使用默认值实现数据完整性的方法；熟练使用规则实施数据完整性的保护。6了解视图的概念和作用；掌握视图相关的命令。7了解索引的作用与分类；掌握索引的创建方法。8. 掌握数据的查询、插入删除以及更新等各种sql语句的编写。9了解存储过程的使用方法；掌握存储过程的调用方法。10了解触发器的使用方法，掌握触发器的创建方法。11理解备份的基本概念，了解备份设备的概念。掌握各种备份数据库的方法，了解如何制定备份计划，如何从备份中恢复设备。12.了解数据库设计的主要步骤。13.理解范式的作用和意义。二、实验仪器设备和材料清单PC机，内装windows操作系统和sqlserver数据库管理系统三、实验内容学生1-3人一组，从以下题目中任选1个题目，完成实验要求中的各项内容(一)题目一：信用卡管理系统CCMS现准备为某银行开发一个信用卡管理系统CCMS，该系统的基本功能为： 1. 信用卡申请。非信用卡客户填写信用卡申请表，说明所要申请的信用卡类型及申请者的基本信息，提交CCMS。如果信用卡申请被银行接受，CCMS将记录该客户的基本信息，并发送确认函给该客户，告知客户信用卡的有效期及信贷限额；否则该客户将会收到一封拒绝函。非信用卡客户收到确认函后成为信用卡客户。 2. 信用卡激活。信用卡客户向CCMS提交激活请求，用信用卡号和密码激活该信用卡。激活操作结束后，CCMS将激活通知发送给客户，告知客户其信用卡是否被成功激活。3. 信用卡客户信息管理。信用卡客户的个人信息可以在CCMS中进行在线管理。每位信用卡客户可以在线查询和修改个人信息。4. 交易信息查询。信用卡客户使用信用卡进行的每一笔交易都会记录在CCMS中。信用卡客户可以通过CCMS查询并核实其交易信息（包括信用卡交易记录及交易额）。（二）题目二：订餐系统COS某企业为了方便员工用餐，为餐厅开发了一个订餐系统（COS：Cafeteria Ordering System），企业员工可通过企业内联网使用该系统。企业的任何员工都可以查看菜单和今日特价。系统的顾客是注册到系统的员工，可以订餐（如果未登录，需先登录）、注册工资支付、预约规律的订餐，在特殊情况下可以覆盖预订。餐厅员工是特殊顾客，可以进行备餐、生成付费请求和请求送餐，其中对于注册工资支付的顾客生成付费请求并发送给工资系统。菜单管理员是餐厅特定员工，可以管理菜单。送餐员可以打印送餐说明，记录送餐信息（如送餐时间）以及记录收费（对于没有注册工资支付的顾客，由送餐员收取现金后记录）。顾客订餐过程如下：1. 顾客请求查看菜单；2. 系统显示菜单和今日特价；3. 顾客选菜；4. 系统显示订单和价格；5. 顾客确认订单；6. 系统显示可送餐时间；7. 顾客指定送餐时间、地点和支付方式；8. 系统确认接受订单，然后发送Email给顾客以确认订餐，同时发送相关订餐信息通知给餐厅员工。(三)题目三：商品配送中心信息管理系统假设某大型商业企业由配送中心和连锁超市组成，其中商品配送中心包括采购、财务、配送等部门。为了实现高效管理，设计了商品配送中心信息管理系统，其主要功能描述如下：1.系统接收由连锁超市提出的供货请求，并将其记录到供货请求记录文件。2.在接到供货请求后，从商品库存记录文件中进行商品库存信息查询。如果库存满足供货请求，则配送处理，发送配送通知，否则，向采购部门发出缺货通知。3配送处理接到配送通知后，查询供货请求记录文件，更新商品库存记录文件，并向配送部门发送配送单，在配送货品的同时记录配送信息至商品配送记录文件。4采购部门接到缺货通知后，与供货商洽谈，进行商品采购处理，合格商品入库，并记录采购清单至采购清单记录文件，向配送处理发出配送通知，同时通知财务给供货商支付货款。（四）题目四：音像管理信息系统某音像制品出租商店欲开发一个音像管理信息系统，管理音像制品的租借业务。需求如下：1系统中的客户信息文件保存了该商店的所有客户的用户名、密码等信息。对于首次来租借的客户，系统会为其生成用户名和密码。2系统中音像制品信息文件记录了商店中所有音像制品的详细信息及其库存数量 。3根据客户所租借的音像制品的品种，会按天收取 相应的费用。音像制品的最长租借周期为一周，每位客户每次最多只能租借6件音像制品。4客户租借某种音像制品的具体流程为：（1）根据客户提供的用户名和密码，验证客户身份。（2）若该客户是合法客户，查询音像制品信息文件，查看商店中是否还有这种音像制品。（3）若还有该音像制品，且客户所要租借的音像制品数小于等于6个，就可以将该音像制品租借给客户。这时，系统给出相应的租借信息，生成一条新的租借记录并将其保存在租借记录文件中。（4）系统计算租借费用，将费用信息保存在租借记录文件中并告知客户。（5）客户付清租借费用之后，系统接收客户付款信息，将音像制品租借给该客户。（五）题目五 ：比赛信息管理系统某地区举行篮球比赛，需要开发一个比赛信息管理系统来记录比赛的相关信息。需要实现以下功能：1.登记参赛球队的信息。记录球队的名称、代表地区、成立时间等信息。系统记录球队每个队员的姓名、年龄、身高、体重等信息。每个球队有一个教练负责管理球队，一个教练仅负责一个球队。系统记录教练的姓名、年龄等信息。2.安排球队的训练信息。比赛组织者为球队提供了若干个场地，供球队进行适应性训练。系统记录现有的场地信息，包括：场地名称、场地规模、位置等信息。系统可为每个球队安排不同的训练场地。系统记录场地安排的信息。3.安排比赛。该赛事聘请专职裁判，每场比赛只安排一个裁判。系统记录裁判的姓名、年龄、级别等信息。系统按照一定的规则，首先分组，然后根据球队、场地和裁判情况，安排比赛（每场比赛的对阵双方分别成为甲队和乙队）。记录参赛球队名称、比赛时间、比分、比赛场地等信息。4.所有球员、教练和裁判可能出现重名情况。（六）题目六 ：网上图书电子商城建立一个网上图书电子商城的数据库。该电子商城可以实现用户购买图书，购物车，下订单等功能；同时网上书城的管理员可以对图书进行管理，包括：新书上架、图书分类、订单管理等。另外该网上商城还可以提供如最畅销书籍、销售前十的书籍、最新到货书籍、图书打折等信息的显示。对于网站的会员，当其达到一定的购买量后，可以适当予以优惠。四、实验要求按照实验步骤的示例，从实验内容中任选一题，完成以下要求，各要求所占比例如下所示。1对系统的功能进行分析，并形成文档，文档中体现系统主要功能模块的具体内容。2完成数据库的概念设计，画出E-R图。（10%）3将E-R图转换为关系模型，写出数据库中的各个关系模式。（5%）4根据关系模型完成数据库的物理设计，包括表的设计、创建数据库和表。合理设计数据库中各个表的属性，表中各属性的数据类型考虑得当。（15%）5进行数据库完整性设计，包括主键、外键、check、默认值等约束主键设置合理，表间关系、外键考虑得当，各类约束设计合理。（10%）6给出数据库的关系图。（5%）7. 根据实际功能要求创建适合的视图。（10%）根据数据库的功能，至少设计两个以上的视图。8. 安全性设计，针对小组各个成员的职责，说明各成员的角色及权限的分配。（5%）按照下表的形式进行说明：成员姓名登录名角色和权限9.所设计的数据库达到的范式级别（5%）10.出实现主要功能的sql语句，包括数据的查询、插入、删除以及修改。至少15条sql语句，按照需求分析阶段分析的功能写出（25%）11根据功能要求创建触发器和存储过程。（10%）（至少两个触发器，两个存储过程）12.成数据库的备份和恢复。五、实验步骤示例下面以电费管理系统为例说明具体的实验步骤，学生可以参考本实例进行实验。（一）系统功能分析1录入功能 录入用电用户的基本信息、用电度数、自动计算电费用等；2用户的种类管理：比如，用户类型：企业或是家庭，增加和删除用户的类型，每一种类型的用户的电费的标准；3村庄（小区）的管理：比如村庄的各种属性，增加和删除村庄的多少等；4用户的管理：根据用户的不同类型比如企业或是家庭，实现用户的各种信息的修改；5用电情况的管理：根据不同的类型统计他们的用电情况；6可以实现不同月份的用电情况的统计7查询的功能：（1），根据用户的类型查询，根据用户的姓名查询，模糊查询，单位查询；8实现数据库用户的不同权限的管理功能，不同权限有有对数据的操作的限制；9数据库的备份。（二）概念设计用户用户姓名地址邮编电话用户ID电费ID所属单位单位名称单位ID单位地址单位电话单位邮编单位类型电费电费ID每月用电量电费类型ID电费类型电费价格电费类型ID电费类型名称交纳属于属于单位ID注册时间注册时间（三）逻辑结构设计客户信息（客户ID，客户姓名，电费ID，住址，电话，工作单位，注册时间，邮编）单位信息（单位ID，单位名称，单位地址，邮编，单位类型，电话）电费信息（电费ID，电费类型ID，各个月份的用电量）电费类型信息（电费类型ID，电费价格，电费类型名称）（四）物理设计1.表结构设计表的设计包括以下内容：l 表名(中英文)l 字段名l 字段数据类型l 字段是否为空l 字段的默认值l 备注，对字段的解释性说明：主键、外键、是否自动增一、是否为索引、是否唯一、是否进行数据检查（check约束）等。例如：客户表字段名数据类型是否允许为空默认值备注用户IDInt否无主键单位IDInt否无外键参照单位表（单位ID）用户姓名nvarchar(50)否无电费IDInt否无外键参照电费表（电费ID）地址nvarchar(50)否无邮编Postcode（自定义）是无电话Tel（自定义）否无注册时间Datetime是无2.数据库的创建运行图： 3.数据表的创建运行图单位表：客户表：电费类型表：电费表： （五）数据完整性设计1主键约束、非空值约束(部分) 2创建用户自定义数据类型（1）邮政编码Exec sp_addtype postcode ,'char(6)' ,null（2）电话号码sp_addtype postcode ,'char(11)' ,null3 填写性别的时候只能是男或者是女alter table 系统用户表 add constraint default_sex default '男' for sysuser_sex 4 check约束 alter table 电费类型表 add constraint check_price check(ele_fare_price>0 and ele_fare_price<5)5 唯一约束为电费类型表 添加唯一约束 即是unique约束 保证电费类型的名称要唯一alter table 电费类型表 add constraint unique_ele_fare_price unique nonclustered( ele_fare_name)6 创建外键约束(1）alter table 客户表 add constraint Fk_cus_ele_fare_ID foreign key (cus_ele_fare_ID) references 电费表(ele_ID)（2）alter table 电费表 add constraint Fk_ele_fare_ID foreign key (ele_fare_ID) references 电费类型表(ele_fare_ID)(3) 保证用户的单位要在单位表中存在alter table 客户表 add constraint Fk_dep_ID foreign key (cus_ID) references 单位表(dep_ID)（六）创建数据库关系图（七）创建视图1功能说明：为安全起见，要求用户只能查看表中的一部分数据；代码：create view 用户信息表 as select cus_ID,cus_name ,cus_dep_ID from 客户表 ,单位表 where 客户表.cus_dep_ID=单位表.dep_ID效果图2功能说明为方便每一次查询电费的时候，不必要每一次都要进行表的连接，创建客户的电费视图代码：create view 客户用电信息As select cus_name,ele_fare_ID, cus_reg_timer,ele_fareofyiyue,ele_fareoferyue,ele_fareofsanyue,ele_fareofsiyueele_fareofwuyue,ele_fareofliuyue,ele_fareofqiyue,ele_fareofbayue,ele_fareofjiuyue,ele_fareofshiyue,ele_fareofshiyiyue,ele_fareofshieryuefrom 客户表,电费表where 客户表.cus_ele_fare_ID=电费表.cus_ele_fare_ID（八）数据库设计分析该数据库中的各个关系模式由于存在以下函数依赖：（要求一一列出）所以该数据库中的关系模式能达到3NF。（九）数据查询和更新1功能说明：查询所有客户的基本信息代码：select * from 客户表效果图：2功能说明：修改客户张飞的地址代码：Update 客户表Set cus_adress=北京市朝阳区亚运村10-10-901Where cus_name=张飞（十）创建触发器1功能说明: 创建一个触发器 功能是每当一个客户的信息被插入的时候 自动填充客户的注册时间！代码：create trigger insert_ele_ID on 客户表for insert asdeclare ele_ID int select ele_ID=客户表.cus_ele_fare_ID from 客户表,inserted iwhere 客户表.cus_ele_fare_ID=i.cus_ele_fare_IDbegininsert 电费表(cus_ele_fare_ID) VALUES(ele_ID) end效果图:2功能说明: 创建触发器 使每一个系统用户注册的时候就自动的添加时间字段代码：create trigger inser_systime on 系统用户表 for insert as begin update 系统用户表 set sysuser_time=getdate()end效果图:3功能说明: 功能是 每当删除一个客户记录的时候 就自动的删除其在电费表里面的电费ID ;代码：create trigger delete_ele_ID on 客户表for delete asdeclare ele_ID int select ele_ID=客户表.cus_ele_fare_ID from 客户表,inserted iwhere 客户表.cus_ele_fare_ID=i.cus_ele_fare_IDbegindelete from 电费表 where cus_ele_fare_ID=ele_ID end（十一）存储过程的创建1功能说明：创建用户信息的查询的存储过程的语句代码：create proc view_custable as select * from 客户表效果图：2功能说明：创建用户修改信息的存储过程代码：create proc alter_custablealter_cus_ID int ,alter_text nvarchar(50)as update 客户表 set alter_ziduan=alter_text where cus_ID=alter_cus_ID3功能说明：创建用户模糊查询的存储过程代码：create proc select_userinformationname nvarchar(50)as select * from 客户表 where cus_name like name+'%'效果图：4功能说明：创建查询每一个用户i在每一个月份的用电量的存储过程代码代码：create proc caulate_ele_fare ele_ID nvarchar(4), totle_money float outputasselect totle_money=ele_fareofyiyue+ele_fareoferyue+ele_fareofsanyue+ele_fareofsiyue+ele_fareofsanyue+ele_fareofwuyue+ele_fareofliuyue+ele_fareofqiyue+ele_fareofbayue+ele_fareofjiuyue+ele_fareofshiyue+ele_fareofshiyiyue+ele_fareofshieryue from 电费表 where ele_ID=ele_ID效果图：5功能说明：创建用户添加所在单位类型的存储过程的语句代码：create proc add_dep_ID dep_name nvarchar(50) ,dep_ID int ,dep_adress nvarchar(50) ,dep_tel tel ,dep_postcode postcode ,dep_type nvarchar(50)asinsert into 单位表 (dep_name ,dep_ID , dep_adress ,dep_tel ,dep_postcode, dep_type) values (dep_name,dep_ID,dep_adress,dep_tel,dep_postcode,dep_type)6功能说明：创建删除单位的存储过程代码：create proc delete_depdep_ID int asdelete from 单位表 where dep_ID=dep_ID7功能说明：创建查看单位类型的存储过程代码：create proc view_depas select * from 单位表 效果图：8功能说明：创建修改单位表的存储过程代码：create proc alter_depalter_text nvarchar(50) ,alter_dep_ID int Asupdate 单位表 set alter_ziduan=alter_text where dep_ID=alter_dep_ID9功能说明：就是创建一个存储过程 每当输入一个姓名的时候 就自动的计算表中的数据，并且把结果作为返回值代码：create proc view_farename nvarchar(50)asselect ele_fareofyiyue+ele_fareoferyue+ele_fareofsanyue+ele_fareofsiyue+ele_fareofwuyue+ele_fareofliuyue+ele_fareofqiyue+ele_fareofbayue+ele_fareofjiuyue+ele_fareofshiyue+ele_fareofshiyiyue+ele_fareofshieryue as '电费总额' from 电费表 where ele_ID in (select cus_ele_fare_ID from 客户表 where cus_name=name )效果图：（十二）安全性设计该数据库共有3名用户。其中成员姓名登录名角色和权限成员1SaDBA成员2U1Datareaderdatareader（十三）数据库备份和恢复备份方案：完全+事务日志创建备份磁盘语句：exec sp_addumpdevice 'disk','小型电费管理系统备份磁盘','c:电费管理系统.bak'结果：开始进行完全备份语句：backup database 小型电费管理系统 to 小型电费管理系统备份磁盘结果：开始事务日志备份语句：小型电费管理系统备份磁盘结果：六、考核形式（1）过程考核（40%）100-90：严格遵守上机规则，独立完成作业；程序模块完整，功能齐全，界面新颖独特；上机报告认真、规范；关键代码不少于500行。89-80：遵守上机规则，独立完成作业；程序模块完整，功能齐全；上机报告认真、规范；关键代码不少于300行。79-70：遵守上机规则，独立完成作业；程序模块完整，运行结果正确；上机报告认真、规范；关键代码不少于200行。69-60：遵守上机规则，独立完成作业；程序结果正确；上机报告认真规范。60分以下：上机不认真；程序未调试完成或结果不正确；上机报告不认真。 （2）答辩（30）100-90：回答问题熟练；思路清晰，简单明了 89-80 ：回答问题熟练；79-70： 问题回答正确；69-60： 回答问题基本正确60分以下：回答问题不正确 （3）报告（30）100-90：内容全面；图表规范；结构清晰；叙述准确；独立完成实验报告；具有个人见解 89-80 ：内容全面；图表规范；结构清晰；叙述准确；独立完成实验报告；79-70 ：内容全面；图表规范；结构清晰；叙述准确；69-60 ：内容全面；图表不规范；结构清晰；60分以下：内容不全；图表不规范；结构混乱；叙述不准确实验结束后以小组为单位提交数据库文件、sql语句的脚本文件和实验报告。如无答辩时间，则按照过程+提交作品占60%，实验报告占40%的标准计算成绩。七、实验报告要求实验名称： 数据库设计 课程名称： 数据库原理与应用 实验日期：2010年 月 日实验报告提交日期：2010年 月 日学号： 实验人姓名： 一、实验目的1掌握数据库和数据模型的基本概念、数据模型的三要素、概念模型的表示方法、数据库系统的模式结构与体系结构、DBMS的功能与组成。2通过设计E-R模型并分析，掌握从实际问题出发建立概念模型的方法。3掌握将E-R模型转换为关系模型的方法。4掌握使用sqlserver创建数据库、数据表、表间关系等的方法。5掌握数据完整性的基础知识；学会使用约束来实施数据完整性的保护；掌握使用默认值实现数据完整性的方法；熟练使用规则实施数据完整性的保护。6了解视图的概念和作用；掌握视图相关的命令。7了解索引的作用与分类；掌握索引的创建方法。8. 掌握数据的查询、插入删除以及更新等各种sql语句的编写。9了解存储过程的使用方法；掌握存储过程的调用方法。10了解触发器的使用方法，掌握触发器的创建方法。11理解备份的基本概念，了解备份设备的概念。掌握各种备份数据库的方法，了解如何制定备份计划，如何从备份中恢复设备。12.了解数据库设计的主要步骤。二、实验环境三、实验内容 按照实验步骤示例写，要包含示例中各项内容，否则酌情予以扣分。四、思考题回答实验指导书中的思考题八、思考题1该数据库还存在哪些没有解决的问题？对于这些问题有何想法？ 九、参考教材1数据库系统概论学习指导与习题解答，王珊、朱青编著，高等教育出版社，2003年。2sqlserver2005 宝典,美pual Nielesen著，赵子鹏，袁国忠等译，人民邮电出版社，2008年。3Sqlserver2005基础教程与实验指导郝安林，许勇，康会光，郭洪武等编著，清华大学出版社，2008年。4SQL server2005实例教程，中国水力出版社，2008年。5数据库原理及应用，陈志泊等，人民邮电出版社，2002年。6数据库原理与设计，陶宏才，清华大学出版社，2007年。7数据库原理与应用，张俊玲，清华大学出版社，2007年。8数据库原理与应用习题指导，张俊玲，清华大学出版社，2007年。9数据库设计与开发（影音版），（美）弗罗斯特，清华大学出版社，2007年。