数据库综合实验.docx
《数据库综合实验.docx》由会员分享，可在线阅读，更多相关《数据库综合实验.docx（35页珍藏版）》请在冰豆网上搜索。
数据库综合实验
数据库原理及应用
--SQLServer2000
陈学进于2010年三月
实验指导书
精选文库
实验一用E-R图设计数据库
1.实验目的
1）熟悉E-R模型的基本概念和图形的表示方法。
2）掌握将现实世界的事物转化成E-R图的基本技巧。
3）熟悉关系数据模型的基本概念。
4）掌握将E-R图转化成关系表的基本技巧。
2.实验内容
1）根据需求确定实体，属性和联系。
2）将实体，属性和联系转化为E-R图。
3）将E-R图转化为表。
3.实验步骤
1）设计能够表示出班级与学生关系的数据库。
①确定班级实体和学生实体的属性。
②确定班级和学生之间的联系，给联系命名并指出联系的类型。
③确定联系本身的属性。
④画出班级与学生关系的E-R图。
⑤将E-R图转化为关系模式，写出各关系模式并标明各自的主码或外码。
2）设计能够表示出顾客与商品关系的数据库。
①确定顾客实体和商品实体的属性。
②确定顾客和商品之间的联系，给联系命名并指出联系的类型。
③确定联系本身的属性。
④画出顾客与商品关系的E-R图。
⑤将E-R图转化为关系模式，写出表的关系模式并标明各自的主码或外码。
3）设计能够表示学校与校长关系的数据库。
①确定学校实体和校长实体的属性。
②确定学校和校长之间的联系，给联系命名并指出联系的类型。
③确定联系本身的属性。
④画出学校与校长关系的E-R图。
⑤将E-R图转化为关系模式，写出表的关系模式并标明各自的主码或外码。
4）设计能够表示出房地产交易中客户，业务员和合同三者之间关系的数据库。
①确定客户实体，业务员实体和合同实体的属性。
②确定客户，业务员和合同三者之间的联系，给联系命名并指出联系的类型。
③确定联系本身的属性。
④画出客户，业务员和合同三者关系E-R图。
⑤将E-R图转化为关系模式，写出表的关系模式并标明各自的主码或外码。
5）学生之间相互交流各自设计的数据库。
--2
精选文库
实验二确定表中的关键字
1.实验目的
1）正确理解候选关键字，主关键字，组合关键字，外关键字的基本概念。
2）能够正确判断给定的表中各种类型的关键字。
3）在设计数据库时能正确指定各种类型的关键字，知道如何实施数据完整性。
2.实验内容
1）复习候选关键字，主关键字，外关键字，组合关键字以及数据完整性的基本概念。
2）在给出的部门表和员工表中能正确标识出各种类型的关键字。
3.实验步骤
1）写出候选关键字，主关键字，组合关键字，外关键字，实体完整性，域完整性，参照完整性的定义。
已知部门表和员工表分别见表2-1和表2-2
表2-1部门表
部门代码
部门名
负责人
地点
0001
生产部
李华江
浙江杭州
0002
销售部
张丽
浙江宁波
0003
市场部
王欣
浙江温州
表2-2员工表
员工代码
姓名
家庭住址
联系电话
邮政编码
部门代码
200001
王华
杭州
86960986
310006
0001
200002
李想
富阳
85438769
310010
0003
200003
张丽
杭州
67893542
310017
0002
200004
李华江
萧山
82849873
310101
0001
2）确定部门表和员工表中的候选关键字，并陈述理由。
3）选择部门表和员工表的关键字。
4）在部门表和员工表的结构中标注主关键字。
5）在员工表中确定可能的组合关键字，并陈述理由。
6）确定在部门表和员工表中共有的属性。
7）指出哪个表中的属性是外关键字。
8）确定哪个表是主表，哪个表是从表。
9）部门表和员工表是如何通过关键字实施数据完整性的。
--3
精选文库
实验三关系规范化
1.实验目的
1）了解函数依赖的基本概念。
2）能正确判断某一关系是属于第几范式。
3）掌握规范化范式的方法。
2.实验内容
1）复习函数依赖，数据规范化，范式的基本概念以及各级范式的判别标准。
2）判断给定的表满足哪级范式的条件。
3）将给定的表转换成满足特定等级范式条件的表。
3.实验步骤
1）写出函数依赖，数据规范化，范式的定义以及各级范式的判别标准。
2）有表3-1所示的项目表1，判断其是否满足第一范式的条件，并说明理由。
表3-1项目表1
项目代码
职员代码
部门
累计工时间
P27
90
P51
E101
系统集成部
101
P20
60
P27
E305
销售部
109
P22
98
P51
E508
行政办公室
NULL
P27
72
3）有表3-2所示的项目表2，判断其是否满足第二范式的条件，并说明理由。
表3-2项目表2
项目代号
职员代码
部门
累计工作时间
P27
E101
系统集成部
90
P27
E305
财务部
10
P51
E508
行政办公室
NULL
P51
E101
系统集成部
101
P20
E101
系统集成部
60
P27
E508
行政办公室
72
4）有表3-3所示的职员表，判断其是否满足第三范式的条件，并说明理由。
表3-3职员表
职员代码
部门
部门负责人代码
E101
系统集成部
E901
E305
财务部
E909
E402
销售部
E909
E508
行政办公室
E908
E607
财务部
E909
E608
财务部
E909
--4
精选文库
5）有表3-4所示的项目表3，判断其是否满足第三范式的条件，并说明理由。
表3-4项目表3
项目代码
职员代码
职员姓名
累计工作时间
P2
E1
李华玉
48
P5
E2
陈家伟
100
P6
E3
张勤
15
P3
E4
谢成权
2505
P5
E4
谢成权
75
P5
E1
李华玉
40
6）将项目表1转换成满足第一范式条件的表。
7）将项目表2转化成满足第二范式条件的表。
8）将职员表转换成满足第三范式条件的表。
9）将项目表3转换成满足BCNF条件的表。
10）非规范化数据带来的不利影响是什么?
--5
精选文库
实验四创建SQLServer2000数据库和表
1.实验的目的
1）熟悉企业管理器环境。
2）掌握创建数据库和表的操作。
2.实验内容
1）熟悉SQLServer2000企业管理器环境。
2）创建XSCJ数据库。
3）在XSCJ数据库中创建学生情况表XSQK，课程表KC，学生成绩表XS_KC。
4）在XSQK、KC、XS_KC表中输入数据。
3.实验步骤
1）启动SQLServer企业管理器，打开“SQLServerEnterpriseMananger”窗口，并在左边的目录树结构中选择“数据库”文件夹。
2）选择“操作”菜单中的“新建数据库”命令，打开“数据库属性”对话框，并在“名称”框内输入数据库名称XSCJ。
3）单击“确定”按钮，完成XSCJ数据库的创建。
4）打开刚才创建的“XSCJ”文件夹，并在“SQLServerEnterpriseMananger”窗口的右边窗口中选择“表”对象。
5）选择“操作”菜单中的“新建表”命令，打开SQLServer的表编辑器窗口。
--6
精选文库
6）根据表4-1所示的表结构增加新列。
表4-1
学生情况表XSQK的结构
列名
数据类型
长度
是否允许为空值
默认值
说明
学号
Char
6
N
主键
姓名
Char
8
N
性别
Bit
1
N
男1，女0
出生日期
smalldatetime
4
N
专业名
Char
10
N
所在系
Char
10
N
联系电话
char
11
Y
7）点击快捷工具栏上的快捷按钮，在弹出的“选择名称”对话框中输入表名XSQK，然后单击“确定”按钮，关闭表编辑器窗口，完成新表的创建。
8）打开“表”对象，在“SQLServerEnterpriseManager”窗口的右边窗口中选择刚才创建的“XSQK”表。
9）选择“操作”菜单中的“打开表”子菜单下的“返回所有行”命令，打开表的数据记录窗口。
10）输入的学生情况数据记录见表4-2。
表4-2学生情况记录
学号
姓名
性别
出生日期
专业
所在系
联系电话
020101
杨颖
0
1980-7-20计算机应用
计算机
88297147
020102
方露露
0
1981-1-15
计算机应用
计算机
88297147
020103
俞奇军
1
1980-2-20
信息管理
计算机
88297151
020104
胡国强
1
1980-11-7信息管理
计算机
88297151
020105
薛冰
1
1980-7-29
水利工程
水利系
88297152
020201
秦盈飞
0
1981-3-10
电子商务
经济系
88297161
020202
董含静
0
1980-9-25
电子商务
经济系
88297062
020203
陈伟
1
1980-8-7
电子商务
经济系
88297171
020204
陈新江
1
1980-7-20
房建
水利系
88297171
11）同理建课程表KC,表的结构见表4-3所示，表的内容见表4-4所示。
表4-3
课程表KC的结构
列名
数据类型
长度
是否允许为空值
默认值
说明
课程号
Char
3
N
主键
课程名
Char
20
N
教师
Char
10
开课学期
Tinyint
1
只能1-6
学时
Tinyint
1
60
学分
Tinyint
1
N
--7
精选文库
表4-4
课程表记录
课程号
课程名
教师
开课学期
学时
学分
101
计算机原理
陈红
2
45
3
102
计算方法
王颐
3
45
3
103
操作系统
徐格
2
60
4
104
数据库原理及应用
应对刚
3
75
5
105
网络基础
吴江江
4
45
3
106
高等数学
孙中文
1
90
6
107
英语
陈刚
1
90
6
108
VB程序设计
赵红韦
3
70
5
12）同理建成绩表XS_KC,表的结构见表4-5所示，表的内容见表4-6所示。
表4-5成绩表XS_KC的结构
列名
数据类型
长度
是否允许为空值
默认值
说明
学号
Char
6
N
外键
课程号
Char
3
N
外键
成绩
Tinyint
1
0-100之间
表4-6
成绩表XS_KC的记录
学号
课程号
成绩
020101
101
85
020101
102
87
020101
107
88
020102
101
58
020102
102
63
020104
107
76
020202
103
55
020202
108
80
020203
103
57
020204
103
71
--8
精选文库
实验五查询数据库
1．实验目的
1）熟悉SQLServer2000查询分析器环境。
2）掌握基本的SELECT查询及其相关子句的使用。
3）掌握复杂的SELECT查询，如多表查询、子查询、连接和联合查询。
2．实验内容
1）启动SQLServer2000查询分析器环境。
2）涉及多表的简单查询。
3）涉及多表的复杂查询。
3.实验步骤
1）启动SQLServer查询分析器，打开“SQL查询分析器”窗口。
2）在“SQL查询分析器”窗口中选择要操作的数据库，如“XSCJ”数据库。
3）在KC表中查询学分低于3的课程信息，并按课程号升序排列。
在查询命令窗口中输入以下SQL查询命令并执行：
SELECT*FROMKC
WHEREKC学.分<3
ORDERBY课程号
--9
精选文库
4）在XS_KC表中按学号分组汇总学生的平均分，并按平均分的降序排列。
SELECT学号,平均分=AVG（成绩）FROMXS_KC
GROUPBY学号
ORDERBY平均分DESC
5）在XS_KC表中查询选修了3门以上课程的学生学号。
SELECT学号FROMXS_KC
GROUPBY学号
HAVINGCOUNT（*）>3
--10
精选文库
6）按学号对不及格的成绩记录进行明细汇总。
SELECT学号,课程号,成绩FROMXS_KC
WHERE成绩<60
ORDERBY学号
COMPUTECOUNT（成绩）
BY学号
7）分别用子查询和连接查询，求107号课程不及格的学生信息。
用子查询：
SELECT学号,姓名,联系电话FROMXSQK
WHERE学号IN
（SELECT学号
FROMXS_KC
--11
精选文库
WHERE课程号=’107’AND成绩<60）
用连接查询：
SELECT学号,姓名,联系电话FROMXSQK
JOINXS_KCONXSQK学.号=XS_KC学.号
WHERE课程号=’107’AND成绩<60
8）用连接查询在XSQK表中查询住在同一寝室的学生，即其联系电话相同SELECTA学.号,A.姓名,A.联系电话FROMXSQKAJOINXSQKBONA.联系电话=B.联系电话WHEREA.学号!
=B.学号
请自已完成以下的查询:
9）查询XSQK表中所有的系名。
10）查询有多少同学选修了课程。
11）查询有多少同学没有选课。
12）查询与杨颖同一个系的同学姓名。
13）查询选修了课程的学生的姓名、课程名与成绩。
14）统计每门课程的选课人数和最高分。
15）统计每个学生的选课门数和考试总成绩，并按选课门数的降序排列。
--12
精选文库
实验六创建和使用视图
1．实验目的
1）掌握视图的创建、修改和删除。
2）掌握使用视图来访问数据。
2．实验内容
1）创建一个简单的视图，查询101号课程不及格的学生信息。
2）修改简单视图，查询107号课程成绩介于70-90的学生信息。
3）使用视图访问数据。
4）删除所创建的视图。
3．实验步骤
1）启动SQLServer企业管理器，打开“SQLServerEnterpriseManager”窗口。
2）选择要创建视图的数据库文件夹，如“XSCJ”文件夹，并在右边的对象窗口中选择其中
的“视图”对象。
3）选择“操作”菜单中的“新建视图”命令，打开SQLServer的视图设计窗口。
4）在“数据源关系图窗口”中单击鼠标右键，打开“添加表”窗口，添加XSQK表和XS_KC
表。
5）选择XSQK表的学号和姓名列，选择XS_KC表的课程号和成绩列，作为视图的显示列。
6）设置学号列的排序类型为升序。
7）设置查询条件：
先在课程号行的“准则”列设置条件为“=‘101’”，然后在成绩行的“准则”列设置条件为“<60”。
--13
精选文库
8）点击快捷工具栏上的快捷按钮，在弹出的“另存为”对话框中输入视图名，如“v_101
不及格”，然后单击“确定”按钮，关闭视图设计窗口，完成视图的创建。
9）在“v_101不及格”视图上单击鼠标右键，在弹出的快捷菜单中选择“设计视图”命令，
修改视图定义。
10）添加数据源KC表，以显示107号课程的课程名称。
打开“添加表”窗口，选择“KC”
表，系统自动为KC表和XS_KC表建立基于课程号的内连接。
11）选择KC表中的课程名列。
12）添加查询条件：
修改课程号行中“准则”列的条件为“=‘107’”；修改成绩行中“准则”列的条件为“>=70”，并复制该行，去掉“输出”列中的复选，并修改“准则”列的条件为“<=90”。
13）点击快捷工具栏上的快捷按钮，关闭视图设计窗口，保存对视图的修改。
请自已创建以下视图:
14）创建一个简单视图，查询“计算机系”学生的信息。
15）创建一个简单视图，统计每门课程的选课人数和最高分。
16）创建一个复杂视图，查询与“俞奇军”住在同一寝室的学生信息，即其联系电话相同。
17）创建一个复杂视图，查询选修了课程的同学的姓名，课程名及成绩。
--14
精选文库
实验七创建和使用索引
1．实验目的
1）熟悉SQLServer2000索引管理器。
2）掌握索引的创建和使用。
3）掌握对索引的优化操作。
2．实验要求
1）使用索引管理器为XS-KC表创建索引IX-XS-KC。
2）使用强制索引查询数据。
3）用索引优化向导优化索引。
3．实验内容
1）用索引管理器创建XS_KC表的索引IX_XS_KC。
①选择要创建索引的数据库文件夹，如“XSCJ”文件夹，并在右边的对象窗口中选择并打开其中的“表”对象。
②选择所要创建索引的表，如“XS_KC”表，并从“操作”菜单中选择“所有任务”子菜单下的“管理索引”命令，打开SQLServer的索引管理器窗口。
③单击其中的“新建”按钮，创建新的索引，并为其设置相应的属性。
为XS_KC表创建一个基于“课程号”列和“成绩”列的索引IX_XS_KC,其中课程号列按升序排列，成绩列按降序排列。
首先，单击“新建”按钮，此时系统打开“新建索引”口，在其中的“列名”列表
框中选择“课程号”项。
再选择“成绩”项，并选中其后的“排列次序（DESC）”项，使成绩列按降序排列。
接着，选择“填充因子”项，其值保留系统默认的80，并选中“填充索引”选项，使索引中间页具有与叶级页相同的填充程度。
最后，将索引名设置为IX_XS_KC。
--15
精选文库
④单击“确定”按钮，完成新索引的创建。
回到索引管理器窗口。
2）强制使用刚才创建的索引查询数据。
①启动SQLServer查询分析器，打开“SQL查询分析器”窗口，并在其右上脚的下拉框中选择要操作的“XSCJ”数据库。
②强制使用“IX_XS_KC索引查询所有课程的及格成绩记录。
在查询命令窗口中输入以下SQL查询命令并执行：
SELECT学号，课程表，成绩
FROMXS_KC
WITH（INDEX（IX_XS_KC））
WHERE成绩>=60
观察一下显示出来的数据是否有序
3）使用索引优化向导优化索引。
索引优化向导可以根据给定的工作负荷，通过使用查询优化器分析该工作负荷的查
询。
此时为数据库推荐最佳索引组合，而为了记录工作负荷，必须使用SQL事件探查创建一个跟踪记录工作负荷。
①启动SQLServer事件探查器，打开“SQL事件探查”窗口。
②选择“文件“菜单中”新建“子菜单下的”跟踪“命令”打开“跟踪属性”对话框，新建一个跟踪。
③设置跟踪名为“INDEX_TRACE”，把跟踪保存为负荷文件“INDEX_TRACE”。
然后单击运行按纽开始跟踪。
④切换到SQL查询分析器窗口，在其中的查询命令中输入以下SELECT查询语句：
SELECTA学.号,A.姓名,D.课程名,C.成绩,A.联系电话
FROMXSQKAJOINXSQKBONA联.系电话=B.联系电话
JOINXS_KCCONA.学号=C.学号JOINKCDOND.课程号=C.课程号WHEREA姓.名='俞奇军'andB.姓名!
='俞奇军'
⑤切换回SQL事件探查起窗口，此时在窗口中列出了刚刚执行的查询语句的跟踪信息。
单击快捷工具栏的快捷键按钮暂停跟踪。
--16
精选文库
其中，注意列的含义如下：
1CPU列：
表示查询所占用的CPU时间，单位为毫秒。
2Duration列：
表示查询执行的时间，单位为毫秒。
3Reads列：
表示查询所引起服务器执行的物理磁盘读取次数。
4Writes列：
表示查询所引起服务器执行的物理磁盘写入次数。
⑥选择“工具”菜单中的“索引优化向导”命令，打开“索引优化向导”对话框。
开始优化索引。
⑦切换到SQL事件探查窗口，然后点击快捷工具栏上的快捷按钮继续跟踪。
⑧切换到SQL查询分析器窗口，再次执行刚才的SELECT查询语句。
⑨切换回SQL事件探查起窗口，此时在窗口中又列出了优化索引执行同样的查询语句的跟踪信息。
我们可以看到，优化索引后，读盘次数减少了，执行时间变短了。
比较图中两次查询的执行情况，很显然，经过索引优化后，查询性能有了很大的提高。
如果全换成大型表，优化效果将更加明显。
⑩单击快捷工具栏上的快捷按钮停止跟踪，并关闭“SQL事件探查窗口”。
--17
精选文库
实验八创建并使用约束和默认值对象
1.实验目的
1）掌握约束的定义和删除操作。
2）掌握默认值对象的定义和删除。
2．实验内容
1）使用命令删除XSQK表中性别的约束，然后再创建。
2）使用界面方式创建默认值对象，并绑定到列，然后再删除。
3．实验步骤
1）删除并重建XSQK表中性别的约束。
①打开“SQL查询分析器”窗口，选择要操作的数据库，如“XSCJ”数据库。
②在命令窗口中输入以下SQL命令并执行，删除XSQK表中性别列的约束。
ALTERTABLEXSQK
DROPCONSTRAINTCK_XSQK性别_
③在命令窗口中输入以下SQL命令并执行，查询创建刚才
升级会员 