数据库综合实验.docx
《数据库综合实验.docx》由会员分享，可在线阅读，更多相关《数据库综合实验.docx（45页珍藏版）》请在冰豆网上搜索。
数据库综合实验
数据库原理及应用
--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）学生之间相互交流各自设计得数据库。
实验二确定表中得关键字
1、实验目得
1）正确理解候选关键字，主关键字，组合关键字，外关键字得基本概念。
2）能够正确判断给定得表中各种类型得关键字。
3）在设计数据库时能正确指定各种类型得关键字，知道如何实施数据完整性。
2、实验内容
1）复习候选关键字，主关键字，外关键字，组合关键字以及数据完整性得基本概念。
2）在给出得部门表与员工表中能正确标识出各种类型得关键字。
3、实验步骤
1）写出候选关键字，主关键字，组合关键字，外关键字，实体完整性，域完整性，参照完整性得定义。
已知部门表与员工表分别见表2-1与表2-2
表2-1部门表
部门代码
部门名
负责人
地点
0001
生产部
李华江
浙江杭州
0002
销售部
张丽
浙江宁波
0003
市场部
王欣
浙江温州
表2-2员工表
员工代码
姓名
家庭住址
联系电话
邮政编码
部门代码
200001
王华
杭州
310006
0001
200002
李想
富阳
310010
0003
200003
张丽
杭州
310017
0002
200004
李华江
萧山
310101
0001
2）确定部门表与员工表中得候选关键字，并陈述理由。
3）选择部门表与员工表得关键字。
4）在部门表与员工表得结构中标注主关键字。
5）在员工表中确定可能得组合关键字，并陈述理由。
6）确定在部门表与员工表中共有得属性。
7）指出哪个表中得属性就是外关键字。
8）确定哪个表就是主表，哪个表就是从表。
9）部门表与员工表就是如何通过关键字实施数据完整性得。
实验三关系规范化
1.实验目得
1）了解函数依赖得基本概念。
2）能正确判断某一关系就是属于第几范式。
3）掌握规范化范式得方法。
2、实验内容
1）复习函数依赖，数据规范化，范式得基本概念以及各级范式得判别标准。
2）判断给定得表满足哪级范式得条件。
3）将给定得表转换成满足特定等级范式条件得表。
3、实验步骤
1）写出函数依赖，数据规范化，范式得定义以及各级范式得判别标准。
2）有表3-1所示得项目表1，判断其就是否满足第一范式得条件，并说明理由。
表3-1项目表1
项目代码
职员代码
部门
累计工时间
P27
E101
系统集成部
90
P51
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
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）非规范化数据带来得不利影响就是什么?
实验四创建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）根据表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。
学号
姓名
性别
出生日期
专业
所在系
联系电话
020101
杨颖
0
1980-7-20
计算机应用
计算机
020102
方露露
0
1981-1-15
计算机应用
计算机
020103
俞奇军
1
1980-2-20
信息管理
计算机
020104
胡国强
1
1980-11-7
信息管理
计算机
020105
薛冰
1
1980-7-29
水利工程
水利系
020201
秦盈飞
0
1981-3-10
电子商务
经济系
020202
董含静
0
1980-9-25
电子商务
经济系
020203
陈伟
1
1980-8-7
电子商务
经济系
020204
陈新江
1
1980-7-20
房建
水利系
表4-2学生情况记录
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
表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
实验五查询数据库
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课程号
4）在XS_KC表中按学号分组汇总学生得平均分，并按平均分得降序排列。
SELECT学号,平均分=AVG（成绩）FROMXS_KC
GROUPBY学号
ORDERBY平均分DESC
5）在XS_KC表中查询选修了3门以上课程得学生学号。
SELECT学号FROMXS_KC
GROUPBY学号
HAVINGCOUNT（*）>3
6）按学号对不及格得成绩记录进行明细汇总。
SELECT学号,课程号,成绩FROMXS_KC
WHERE成绩<60
ORDERBY学号
COMPUTECOUNT（成绩）
BY学号
7）分别用子查询与连接查询，求107号课程不及格得学生信息。
用子查询：
SELECT学号,姓名,联系电话FROMXSQK
WHERE学号IN
（SELECT学号
FROMXS_KC
WHERE课程号=’107’AND成绩<60）
用连接查询：
SELECT学号,姓名,联系电话FROMXSQK
JOINXS_KCONXSQK、学号=XS_KC、学号
WHERE课程号=’107’AND成绩<60
8）用连接查询在XSQK表中查询住在同一寝室得学生，即其联系电话相同
SELECTA、学号,A、姓名,A、联系电话FROMXSQKAJOINXSQKB
ONA、联系电话=B、联系电话WHEREA、学号!
=B、学号
请自已完成以下得查询:
9）查询XSQK表中所有得系名。
10）查询有多少同学选修了课程。
11）查询有多少同学没有选课。
12）查询与杨颖同一个系得同学姓名。
13）查询选修了课程得学生得姓名、课程名与成绩。
14）统计每门课程得选课人数与最高分。
15）统计每个学生得选课门数与考试总成绩，并按选课门数得降序排列。
实验六创建与使用视图
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”。
8）点击快捷工具栏上得快捷按钮，在弹出得“另存为”对话框中输入视图名，如“v_101不及格”，然后单击“确定”按钮，关闭视图设计窗口，完成视图得创建。
9）在“v_101不及格”视图上单击鼠标右键，在弹出得快捷菜单中选择“设计视图”命令，修改视图定义。
10）添加数据源KC表，以显示107号课程得课程名称。
打开“添加表”窗口，选择“KC”表，系统自动为KC表与XS_KC表建立基于课程号得内连接。
11）选择KC表中得课程名列。
12）添加查询条件：
修改课程号行中“准则”列得条件为“=‘107’”；修改成绩行中“准则”列得条件为“>=70”，并复制该行，去掉“输出”列中得复选，并修改“准则”列得条件为“<=90”。
13）点击快捷工具栏上得快捷按钮，关闭视图设计窗口，保存对视图得修改。
请自已创建以下视图:
14）创建一个简单视图，查询“计算机系”学生得信息。
15）创建一个简单视图，统计每门课程得选课人数与最高分。
16）创建一个复杂视图，查询与“俞奇军”住在同一寝室得学生信息，即其联系电话相同。
17）创建一个复杂视图，查询选修了课程得同学得姓名，课程名及成绩。
实验七创建与使用索引
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。
④单击“确定”按钮，完成新索引得创建。
回到索引管理器窗口。
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事件探查起窗口，此时在窗口中列出了刚刚执行得查询语句得跟踪信息。
单击快捷工具栏得快捷键按钮暂停跟踪。
其中，注意列得含义如下：
1CPU列：
表示查询所占用得CPU时间，单位为毫秒。
2Duration列：
表示查询执行得时间，单位为毫秒。
3Reads列：
表示查询所引起服务器执行得物理磁盘读取次数。
4Writes列：
表示查询所引起服务器执行得物理磁盘写入次数。
⑥选择“工具”菜单中得“索引优化向导”命令，打开“索引优化向导”对话框。
开始优化索引。
⑦切换到SQL事件探查窗口，然后点击快捷工具栏上得快捷按钮继续跟踪。
⑧切换到SQL查询分析器窗口，再次执行刚才得SELECT查询语句。
⑨切换回SQL事件探查起窗口，此时在窗口中又列出了优化索引执行同样得查询语句得跟踪信息。
我们可以瞧到，优化索引后，读盘次数减少了，执行时间变短了。
比较图中两次查询得执行情况，很显然，经过索引优化后，查询性能有了很大得提高。
如果全换成大型表，优化效果将更加明显。
⑩单击快捷工具栏上得快捷按钮停止跟踪，并关闭“SQL事件探查窗口”。
实验八创建并使用约束与默认值对象
1、实验目得
1）掌握约束得定义与删除操作。
2）掌握默认值对象得定义与删除。
2．实验内容
1）使用命令删除XSQK表中性别得约束，然后再创建。
2）使用界面方式创建默认值对象，并绑定到列，然后再删除。
3．实验步骤
1）删除并重建XSQK表中性别得约束。
①打开“SQL查询分析器”窗口，选择要操作得数据库，如“XSCJ”数据库。
②在命令窗口中输入以下SQL命令并执行，删除XSQK表中性别列得约束。
ALTERTABLEXSQK
DROPCONSTRAINTCK_XSQK_性别
③在命令窗口中输入以下SQL命令并执行，查询创建刚才删除得性别列约束，使性别只能取值为1（表示男）或者0（表示女）。
ALTERTABLEXSQK
ADDCONSTRAINTCK_XSQK_性别
CHECK（性别=1OR性别=0）
2）定义新得默认值对象。
①启动SQLServer企业管理器，打开“SQLServerEnterpriseManager”窗口。
②选择要创建默认值对象得数据库文件夹，如“XSCJ”文件夹，并在右边得对象窗口中选择其中得“默认”对象。
③选择“操作”菜单中得“新建默认”命令，打开“默认属性”对话框，并输入名称“DFO_出生日期”与值1980-1-1
注意：
’1980-1-1’前后有单引号（’）括起来，表示其为日期类型。
④单击“确定”按钮，完成默认值对象得定义。
3）将默认值对象绑定到XSQK表得出生日期列。
①打开“默认”对象，并选中刚才定义得默认值对象DFO_出生日期。
②选择“操作”菜单中得“属性”命令，打开“默认属性”对话框。
③单击“绑定列”按钮，打开“将默认值绑定到列”得对话框。
④单击“确定”按钮，完成到列得绑定。
4）使用值对象为新插入行得出生日期列设置值。
①在对象窗口中选择并打开其中得“表”对象，选中XSQK表。
②选择“操作”菜单中“打开表”子菜单下得“返回所有行”命令，打开表得数据记录窗口。
③在表中插入一行新记录，其中出生日期列不填，其值由刚才绑定得默认值对象设定。
如果在设置默认值对象属性时，其值前后没有加单引号（’），那么在插入出生日期列得默认值时，该
升级会员 