数据库上机指导.docx

数据库上机指导.docx

《数据库上机指导.docx》由会员分享，可在线阅读，更多相关《数据库上机指导.docx（28页珍藏版）》请在冰豆网上搜索。

数据库上机指导

数据库系统原理及应用

实验指导书

合肥工业大学管理学院

2011.9

《数据库系统原理及应用》

实验指导书

一．“教学管理系统”实验数据库模式描述

1．数据项描述定义

数据项名

缩写名称

类型

长度，位数

学号

Sno

Char

9

姓名

sname

Char

10

出生年月

sbirthday

DateTime

8

性别

ssex

Char

2

专业班级

sclass

Char

20

评语

sremark

Char

100

家庭住址

address

Char

40

邮编

zipcode

Char

6

电话

phone

Char

15

电子邮箱

email

Char

40

课程号

cno

Char

6

课程名称

cname

Char

20

先行课

cpno

Char

6

学时数

ctime

Numeric

2

学分

credit

Numeric

2

成绩

score

Numeric

3

教工号

Tno

Char

7

教师名称

tname

Char

10

性别

tsex

Char

2

教师生日

tbirthday

DateTime

8

职称

position

Char

12

系别

department

Char

16

工作量

tamount

Numeric

7，2

简历

experience

Char

200

授课时间

tdate

DataTime

8

教室

classroom

Char

10

2．关系模式描述定义

Student（sno,sname,sbirthday,ssex,sclass,sremark,address,zipcode,phone,email）

Primarykey（sno）

Course（cno,cname,cpno,ctime,credit）

Primarykey（cno）

Foreignkey（cpno）

Score（sno,cno,score）

Primarykey（sno,cno）

Foreignkey（sno）

Foreignkey（cno）

Teacher（tno,tname,tsex,tbirthday,position,department,tamount,experience）

Primarykey（tno）

Teaching（tno,cno,tdate,classroom,sclass）

Primarykey（tno,cno）

Foreignkey（tno）

Foreignkey（cno）

二．上机实验

（一）实验目标

通过“数据库系统概论”书本知识学习和“数据库原理与应用”课程教学所授知识对“教学管理系统”实验内容进行分析，建立相应的数据库、数据表、数据视图以及相应的界面程序，利用SQL结构化查询语言提供的数据操纵功能，编程设计实现对数据的定义、修改、删除、查询、更新以及控制等操作，并按照实验指导书的各项要求完成实验。

（二）实验说明

1.在工作站计算机的硬盘D盘符下，请同学们建立以自己名字命名的子目录（可以是汉字名，也可以是汉语拼音字母）。

再在此子目录下，建立用于存放上机实验文件的相关子目录，它们是\DATA；\PROGS等，Data目录用来存放“教学管理系统”数据库文件。

Progs用来存放实验中的SQL语句和执行结果。

如用“萨师煊”的汉语拼音的第一个字母组成的“SSX”在D盘上建立目录后的结果是：

D:

\SSX\DATA；D:

\SSX\PROGS等。

2.本课程实验要利用数据库管理系统SQLSEVER2000进行实验，每台计算机上都已经安装好了微软公司的SQLServer2000数据库管理系统，请熟悉SQLSEVER2000的同学，根据SQLSEVER提供的客户端编程界面按照实验要求完成实验。

3.SQLServer2000的安装过程见附录，建议同学们在自己的电脑上进行安装，熟悉安装过程及安全性设置。

在数据库身份验证模式上，建议使用混合模式。

（三）实验内容

实验一：

设计数据库、数据表和数据视图并编程实现

1.目的要求

（1）这是一个验证性实验。

（2）利用所学E-R数据模型图概念，描述“学生管理”系统中数据表之间以及各数据项之间的联系。

并按照课程教学大纲要求，转化相关E-R图为关系模型中对应的二维表，指出每个数据表中的关键字、索引项、外码以及表中需要满足完整性约束条件；

（3）对照关系模型中（1:

1）、（1:

n）、（m:

n）实体联系，指出各数据表实体属于哪一类联系。

（4）熟悉微软公司SQLServer的企业管理器（后台实验平台）和查询分析器（前台实验平台）内嵌的SQL编程环境，正确登录、退出和使用实验平台环境；

（5）熟练掌握SQL环境下的程序编辑和运行命令；

（6）建立一个教学管理数据库，即建立五个相关数据表。

练习并掌握定义、删除和修改表的数据结构操作。

建立数据库和数据表、数据视图并完成对各个数据表输入一定查询要求的模拟数据（模拟数据记录尽量多输一些且数据要能满足查询要求）的内容。

2.实验建议

（1）数据库服务器SQLServer2000的安装及启动见附录。

（2）数据库名称统一命名为“学生档案”。

建立远程数据视图或本地数据数据视图，一律要使用SQL语句编写成语句序列存成程序文件存放在“d:

\★★★\progs”子目录下，该子目录专门存放你编的程序，其中，★★★表示你自己的姓名。

建立数据表及相关索引

createtablestudent

（snoChar（9）,

snameChar（10）constraintc1notnull,

sbirthdayDatetime,

ssexChar

（2）constraintc2check（ssexin（‘男’,‘女’））,

sclassChar（20）,

sremarkChar（100）,

addressChar（40）,

zipcodeChar（6）,

phoneChar（15）,

emailChar（40）,

constraintpk_studentprimarykey（sno）

）;

createtablecourse

（cnoChar（6）,

cnameChar（20）,

cpnoChar（6）,

ctimeNumeric

（2）,

creditNumeric

（2）,

constraintpk_courseprimarykey（cno）

……

）;

…………

（4）在系统中，如果要将日期格式转变成日常习惯的方式“年/月/日”，请在Windows的控制面版中，找到“日期、时间显示格式”选项，再将“-”修改为“.”即可。

3.实验内容

（1）根据实验建议所示，建立“学生管理”数据库，这里是以SQLServer2000为例来进行建立，其他数据库管理系统的建立方法，同学们自己查询相关书籍完成。

两种方式建立数据库：

a.使用SQLServer2000的企业管理器

b.通过查询分析器，使用SQL语言。

（2）建立数据表的方法，建立五个相关的数据表。

完成数据库的建立并定义各表的完整性约束条件。

分别使用企业管理器和SQL语言来实现。

（3）熟练掌握并应用SQL中的数据表定义、删除和修改等操作命令；

（4）熟练掌握为各表常用字段建立索引，以提高数据检索效率。

*（5）了解域关系演算语言（QBE），这里作为选做内容要求。

另外，无论是SQLServer2000还是Access均没有提供基于QBE的更新操作。

只提供了检索操作。

我们也以检索为例加深印象。

a.SQLServer2000中的QBE实现。

在建立视图时使用，它的属性是纵向排列的。

b.Access中使用QBE的实现。

也是在建立查询（视图）时使用，它的属性是同书上一样行向排列的。

实验二：

设计数据插入、修改、删除、更新、查询和视图查询等操作并编程实现

1.目的要求

（1）熟练掌握数据更新语句，灵活地操作插入数据、修改数据和删除数据；

（2）熟练掌握关系数据库中的完整性概念的应用；

（3）了解数据录入的方法和过程。

（4）掌握单表查询的基本方法；

（5）掌握连接查询的基本方法；

（6）熟练掌握以下练习，并进行以下各类查询：

①选择表中的若干列、查询全部列、查询经过计算的值；

②选择表中的若干元组，即消除取值重复的行与查询满足指定条件的元组（包括：

比较大小、确定范围、确定集合、字符匹配、涉及空值和多种条件查询）；

③对查询结果排序；

④使用集函数；

⑤对查询结果分组

（7）熟练掌握以下练习，并进行下列各类连接查询：

①等值与非等值连接查询；

②自身连接；

③外连接；

④复合条件连接；

（8）掌握嵌套查询的基本方法；

（9）掌握集合查询的基本方法；

（10）熟练掌握查询视图操作

（11）熟练掌握更新视图操作

（12）熟练关系的完整性概念，领会视图的用途

（13）练习以下各类查询：

①带有IN谓词的子查询；

②带有比较运算符的子查询；

③带有ANY或ALL谓词的子查询；

④带有EXISTS谓词的子查询；

⑤视图查询与更新操作；

2.实验建议

（1）为了便于程序正确性测试，需要对自己建立起来的数据表录入一些模拟数据（模拟数据是指符合数据格式要求的假数据），然后按照教材中结构化查询语言SQL章节例题和习题自己组织SQL语句：

（2）设计的SQL程序应该涵盖数据更新操作的全部内容不，包括插入、修改、删除；

（3）综合运用SQL语句实现数据插入、修改、删除等操作的综合运用能力。

（4）按照SQL语言编程要求，实现各类查询和检索操作；

（5）利用后台的SQL平台环境，编程验证数据库的控制保护功能。

（6）将查询视图命令等价改写为对数据表的查询操作命令，利用SQL编程设计完成并进行效率分析。

3.实验内容

（1）根据以下给定的数据表信息分别对student,course,score,teacher,teching表进行数据插入和修改，以完成给定的数据录入，同时要求输入班级同学名录模拟客观真实情况，给student、score表添加30条以上记录，以便以后查询应用方便。

Student表：

Sno

sname

Sbirthday

Sclass

011110101

章海潮

1982.02.07

信管系0101

011111103

王天力

1982.03.04

电商系0101

011111204

董丞悟

1982.06.06

电商系0102

011112110

马丽鹃

1982.04.03

工商系0101

011113104

杨乾坤

1982.03.23

会计系0101

011113221

刘抗日

1983.01.16

会计系0102

011114111

丘海棠

1983.02.08

旅游系0101

011115208

齐振国

1982.07.22

旅游系0102

001011101

王晓悦

1982.01.12

材料系0001

001011212

程伊莲

1982.03.21

材料系0002

course表：

Cno

Cname

Cpno

Credit

Ctime

C001

数据库原理

C005

4

64

C002

高等数学

4

72

C003

管理信息系统

C001

4

54

C004

操作系统

C006

3

32

C005

数据结构

C007

4

32

C006

计算机文化基础

2

32

C007

C语言程序设计

C006

3

32

score表：

Sno

Cno

Score

011110101

C001

90

011111103

C001

85

011111204

C001

73

011112110

C001

98

011113104

C001

96

011113221

C001

97

011114111

C001

83

011115208

C001

85

011110101

C007

92

011111103

C007

97

teacher表：

Tno

Tname

Tsex

Department

T001

江承基

男

信息管理系

T002

梁其征

男

信息管理系

T003

徐茉莉

女

信息管理系

T004

吴雅云

女

信息管理系

T005

杨运铎

男

电子商务系

T006

张昌盛

男

电子商务系

T007

程力衡

男

电子商务系

Teaching表

Tno

cno

tdate

classroom

sclass

T001

C005

1988-01-09

西二405

信管系0101

T002

C007

1990-02-01

西二406

电商系0101

T003

C001

1985-09-10

西二504

电商系0102

T004

C006

1984-07-15

主401

工商系0101

T005

C004

1989-11-12

主402

会计系0101

T006

C002

1991-05-21

西二506

会计系0102

（2）将两个同学（数据自己临时设置，用后即删除）的两门课程的成绩以运行SQL语句插入的形式加入score表中。

该题用以验证、理解和掌握关系模型的完整性规则；

（3）求每门课的平均成绩，并把结果存入average表；

（4）将学生“马丽鹃”的出生日期改为“1982.8.20”；

（5）将所有学生的zipcode属性列值填补上；

（6）将average表中的所有课程的平均成绩置零；

（7）删除average表中的课程号为‘c007’的平均成绩记录；

（8）删除所有average表中平均成绩记录；

（9）建立一个临时学生信息表（tstudent），删除该表中的学号前六位为‘001011’的所有学生记录。

（10）查询全体学生的学号与姓名；

（11）查询全体学生的学号、姓名、所属班级；

（12）查询全体学生的详细记录；

（13）查询全体学生的姓名及其年龄；

（14）查询全体学生的姓名、出生年份；

（15）查询所有修过课的学生的学号；

（16）查询“信管系0101”班全体学生名单；

（17）查询查询所有年龄在27岁以下的学生姓名及其年龄；

（18）查询考试成绩有不及格的学生的学号；

（19）查询年龄在15至28岁之间的学生姓名、班级和年龄；

（20）查询年龄不在15至28岁之间的学生姓名、班级和年龄；

（21）查询“信管系0101”和“电商系0102”班的学生的姓名和班级信息；

（22）查询既不是“信管系0101”也不是“电商系0102”班的学生的姓名和班级信息；

（23）查询学号为“011113104”的学生的详细情况；

（24）查询学号以“0111”打头的学生信息；

（25）查询所有姓“张”学生的学号、姓名、性别、年龄；

（26）查询名字中第二个字有“海”字的学生的学号、姓名、性别、年龄；

（27）查询所有不姓“刘”学生的姓名；

（28）查询课程号以“C”开头的最后两个字母为“05”的课程号和课程名；

（29）某些学生选修某门课程后没有参加考试，所以有选修课记录，但没有考试成绩，试查找缺少考试成绩的学生和相应的课程号；

（30）查找全部有成绩记录的学生学号、课程号；

（31）查找“电商系0101”班年龄在27岁以下的学生学号、姓名；

（32）查找选修了“C001”号课程的学生学号及其成绩，查询结果按分数降序排序；

（33）查询全体学生情况，查询结果按所在班级升序排列，对同一班级中的学生按年龄降序排列；

（34）查询学生总人数；

（35）查询选修了课程的学生人数；

（36）在所有课程中查询最高分的学生学号和成绩；

（37）查询学习“C001”课程的学生最高分数；

（38）计算各个课程号与相应的选课人数；

（39）查询“电商系0102”班选修了两门课程以上的学生学号、姓名；

（40）自然连接student和score表；

（41）使用自身连接查询每一门课程的间接先行课（即先行课的先行课）

（42）使用复合条件连接查询选修“c001”号课程且成绩在90分以上的所有同学；

（43）使用复合条件连接查询每个学生选修的课程名及其成绩；

（44）查询选修了全部课程的学生；

（45）查询至少选修全部学分数为4个学分的课程的学生的学号、姓名；

（46）查询所有选修了C001号课程的学生学号、姓名；

（47）查询选修了课程C001或c007的学生学号、姓名；

（48）查询“会计系0102”班的学生及年龄不大于27岁（现有年龄）的学生；

（49）查询既选修了课程C001又选修了课程c007的所有学生学号、姓名；

（50）查询选修了课程名为“数据库原理”的学生的学号、姓名、性别、年龄；

（51）查询其他班中比“信管系0101”班所有学生年龄都小的学生名单；

（52）查询与“齐振国”在同一个班学习的学生学号、姓名、性别、年龄；

（53）建立“信管系0101”班学生的视图，定义视图名为“info_student1”；

（54）建立“信管系0101”班学生的视图，定义视图名为“info_student2”，并要求进行修改与插入时，仍须保证该视图只有“信管系0101”班学生；

（55）建立“信管系0101”班选修了“C001”课程的学生的视图，定义视图名为“info_C001_student1”；

（56）建立“信管系0101”班选修了“C001”课程且成绩在90分以上的学生的视图，定义视图名为“info_c001_student2”；

（57）定义一个反映学生年龄的视图，定义视图名为“vbirthday_student”；

（58）将学生表中所有女生记录定义为一个视图，视图名为“vfemale_student”；

（59）将学生的学号及其平均成绩定义为一个视图，视图名为“vaverage_student”；

（60）删除视图“info_student1”，删除后即重建；

（61）在“信管系0101”班学生视图中找出年龄小于27岁（现在的年龄）的学生；

（62）利用视图查询“信管系0101”班选修了“C001”课程的学生；

（63）通过“信管系0101”班info_student2视图中学号“011111103”的学生姓名改为“潘长江”；

（64）向“信管系0101”班info_student1视图中插入一个新学生记录，其中：

学号：

011111136，姓名：

张艺谋，性别：

男，出生日期：

1987.11.9；

（65）通过视图info_student1删除信管系0101班学号为“011111135”、姓名为“黄健中”的学生记录；

实验三：

数据库存储过程、触发器的建立及编程操作的实现

1.目的要求

（1）理解掌握数据库中存储过程、触发器的原理、使用场合及方法；

（2）掌握触发器在关系数据库的完整性实现中的作用；

（3）掌握数据库中存储过程的建立方法和调用过程；

（4）掌握数据库中触发器的建立方法；

*（5）理解事务的作用及定义；

*（6）熟练游标的作用、定义及作用：

2.实验建议

（1）为了便于程序正确性测试，需要结合教材中的知识点，对数据库管理系统中的存储过程、触发器、事务、游标、并发处理等内容进行强化：

（2）对存储过程和触发器的建立可以参照数据库的建立方法，采用两种方式实现；

（3）建立过程中，注意命令和函数的书写方法，不要简单复制代码。

*（4）有兴趣同学，可以使用触发器来实现数据表的参照完整性功能；

3.实验内容

（1）创建两个临时表Table1,Table2。

两表的结构均为：

字段名

类型

F1

Char（20）

F2

Int

（2）创建触发器：

a）在Table1上建立Insert触发器T1，触发器内容为：

begin

declare@F1char（20）,@F2int,@T_F1Char（20）

Declare@Slenint

Select@F1=F1,@F2=F2,@T_F1=F1Frominserted

If@F2=1

begin

Select@F2=0

whileLen（RTrim（@F1））>0

begin

Select@F2=@F2+Convert（int,left（RTrim（@F1）,1））

Select@F1=Right（RTrim（@F1）,Len（Rtrim（@F1））-1）

end

end

Else

begin

Select@F2=Ceiling（Len（RTrim（@F1））/2.0）

end

InsertintoTable2（F1,F2）values（@T_F1,@F2）

end

b）在Table2上建立删除触发器T2,触发器内容为：

begin

declare@F1char（20）,@F2int,@T_F2int

Select@F1=a.F1,@F2=a.F2,@T_F2=b.F2Fromtable1a,deletedbwherea.F1=b.F1

If@F2=1

begin

UpdateTable1

setF1=LTrim（RTrim（F1））+LTrim（Str（@T_F2））+Right（RTrim（Str（@T_F2））,1）

whereF1=@F1

end

If@F2=2

begin

UpdateTable1

SetF1=Ltrim（Rtrim（F1））+Ltrim（Str（@T_F2））+Ltrim（Str（Unicode（SubString（Ltrim（F1）,@T_F2,1））））

whereF1=@F1

end

If@F2=3

begin

UpdateTable1

SetF1=LTrim（RTrim（F1））+LTrim（Str（@T_F2））+RTrim（LTrim（Char（64+@T_F2）））

whereF1=@F1

end

If@F2=4

begin

UpdateTable1

SetF1=LTrim（RTrim（F1））+LTrim（Str（@T_F2））+RTrim（LTrim（Str（@@Connections）））

whereF1=@F1

end

end

（3）创建存储过程Exam_Proc

存储过程的两个入口参数分别