数据库原理实验指导书-本科

数据库原理及应用实验指导材料（本科）温州大学城市学院

一、实验内容：实验共7次，每次2学时，期末一次实验考试

1、数据库的可视化操作

2、单表查询

3、连接查询和嵌套查询

4、综合查询

5、用SQL实现数据库的建立与维护

6、存储过程和触发器

7、数据库保护

实验考试题目由学生抽签决定

二、实验成绩

实验成绩由实验操作成绩、实验报告成绩及最后的实验考试组成

三、实验要求

实验准备：每次实验的内容提前公布（但不提供完整的答案及具体的实验操作步骤），因此要求学生在做实验之前提前准备实验内容。

实验：实验时所有的任务必须当场完成，不能将事先完成的数据库拷贝过来作为实验成果，也不能将其他同学的数据库拷贝过来作为实验成果，更不能请其他同学代为完成。

实验测评：学生觉得实验各项内容均已完成，而且自信也比较熟练，可以请老师测评。老师主要测评以下几方面：各项任务是否当场独立完成；熟练程度如何，对实验中观察到的现象能否合理解释。老师测评后将当场给出本次实验的成绩。

实验报告：在实验做完的下一周交主讲老师，实验报告成绩由主讲老师或实验老师根据评分标准给出。

实验一数据库的可视化操作

一、实验目的：

1、了解SQL Server2000的主要功能，并熟悉SQL Server的启动方法

2、掌握SQL Server2000的企业管理器的基本使用方法

3、掌握用可视化的方法实现数据库建立、建表、定义约束，修改表结构等操作

4、掌握用可视化的方法实现数据库表中数据的增加、删除、修改等操作

5、进一步理解数据库的实体完整性、参照完整性、自定义完整性约束条件的作用

二、实验内容：

1.启动SQL Server 2000，进入企业管理器

2.建立图书读者数据库，并存于D盘自建的目录

3.建立图书表、读者表、借阅表，表结构如下：

图书（书号，类别，出版社，书名）

读者（读者编号，姓名，单位，性别，电话）

借阅（书号，读者编号，借阅日期，归还日期）

4.上述各表属性类型及宽度自定（要求为属性选择合适的数据类型，长度，是否为空和缺省值等内

容），定义每个表的主码（上述表中划线的属性）。

5.给“图书”表增加“价格，作者，出版日期”3个属性，其中价格类型为数值型，小数位数是2

位，作者为字符型，出版日期为日期型。

6.定义约束：实现读者性别只能是”男”或”女”的检查约束，实现图书的价格不能为负数的检查约束，

借阅表与图书表、借阅表与读者表的外码约束，即实现借阅表中的参照完整性约束。

7.分别在三个表中输入不少于5条记录的数据，内容自定，输入数据时观察实体完整性、参照完整

性、自定义完整性约束的效果。

8.分别对三个表中的数据进行删除，修改操作，修改数据时观察约束的效果。

三、思考题：

1、图书表中的书号和读者表中的读者编号能否为数值型、能否为空值，为什么？

2、对图书表中的数据分别进行增加、删除、修改操作时要注意什么？

3、对借阅表中的数据分别进行增加、删除、修改操作时要注意什么？

4、定义外码的作用是什么？

要求以上问题回答在实验报告中

实验二单表查询

一、实验目的：

1、掌握用SQL对数据库中的数据进行简单查询操作

2、掌握SQL Server Query Analyzer（查询分析器）的使用方法

二、实验内容：

1、将教师机上的两个文件（工程零件_Data.MDF和工程零件_Log.LDF）拷贝至自己机器的D盘自建的目录中

该数据库的表结构如下：

供应商（供应商代号，姓名，所在城市，联系电话）

零件（零件代号，零件名，规格，产地，颜色）

工程（工程代号，工程名，负责人，预算）

供应零件（供应商代号，工程代号，零件代号，数量，供货日期）2、将两个物理文件联接到数据库管理系统中，方法如下：

1）进入MS-SQL SERVER 2000企业管理器

2）在数据库栏右击鼠标，选择所有任务，选择附加数据库

3）在本机自建目录下选择工程零件_Data.MDF文件

4）确定（开始附加数据库），即可

3、查询供应商王平的基本信息

4、找出天津供应商的姓名和电话

5、查询所有姓’王’的供应商的姓名，电话，所在城市

6、查找预算在50000-100000元之间的工程的信息，并要求查询结果按预算的降序排列

7、找出使用了供应商代号为s1的供应商所供应的零件代号为p3的工程的工程代号

8、查询供应商代号为s1的供应商在2001年以后的供货情况，包括零件代号、数量、供货日期

9、查询供应商总人数

10、查询供应商代号为s1供应商的供货次数

11、找出工程代号为j2使用的各种零件的零件代号及数量

三、实验要求

要求记录运行的SQL语句及运行结果，并能分析和解释实验现象及结果

四、思考题：

1、第5题如果改为：查询所有姓’王’的且为单名的供应商的姓名，电话，所在城市，则如何操作？

2、第10题如改为：查询供货的供应商总人数，则该如何操作？

要求以上问题回答在实验报告中

实验三连接查询和嵌套查询

一、实验目的：

1、掌握用SQL对数据库多表数据查询

2、进一步理解连接查询方法和嵌套查询方法的实际意义

2、进一步熟悉SQL Server2000的查询分析器和企业管理器的使用方法

二、实验内容：

1、启动SQL Server 2000的企业管理器，按实验二的实验步骤1-2将”工程零件”附加到SQL Server中

该数据库的表结构如下：

供应商（供应商代号，姓名，所在城市，联系电话）

零件（零件代号，零件名，规格，产地，颜色）

工程（工程代号，工程名，负责人，预算）

供应零件（供应商代号，工程代号，零件代号，数量，供货日期）

2、查询使用了零件代号为p3的零件的工程代号、工程名

3、查询使用了上海供应商供应的零件的工程代号

4、查询使用了蓝色零件的工程的工程代号（要求分别用连接查询和嵌套查询两种方法）

5、查询姓”王”的供应商的供货信息，包括零件代号、零件名、数量、供货日期

6、查询没有使用天津产的零件的工程代号

7、找出供应商代号为s1的供应商所供应的零件的工程的负责人

8、查询工程代号为j3所使用的零件代号、零件名

9、查询使用了全部零件的工程的工程代号、工程名

10、查询使用零件数量最多的工程的工程代号、工程名

三、实验要求

1、以上各题除有说明，查询方法可以任选

2、要求记录运行的SQL语句及运行结果，并能分析和解释实验现象及结果

四、思考题：（要求以下问题回答在实验报告中）

1、第3题如果用连接查询是否需要加distinct ,为什么？

2、第6题能否用如下语句实现？为什么？

Select distinct 工程代号from 供应零件,零件where 零件.零件代号=供应零件.零件代号and 产地<>’天津’

2、第6题能否用如下语句实现？为什么？

Select distinct工程代号from 供应零件where 零件代号not in (Select 零件代号from 零件where 产地=’天津’)

实验四综合查询

一、实验目的：

1、掌握用SQL对数据库中的数据进行各种不同要求的查询

2、进一步理解SELECT语句各子句的含义及用法

3、提高用SQL的查询语句解决实际问题的能力

二、实验内容：

1、启动SQL Server 2000的企业管理器，按实验二的实验步骤1-2将”工程零件”附加到SQL Server中

该数据库的表结构如下：

供应商（供应商代号，姓名，所在城市，联系电话）

零件（零件代号，零件名，规格，产地，颜色）

工程（工程代号，工程名，负责人，预算）

供应零件（供应商代号，工程代号，零件代号，数量，供货日期）

2、用SQL语句进行操作，解决以下问题：

（1）列出每个供应商代号以及分别给几个工程提供了零件，结果用视图的形式给出。

（2）查询所在地在“上海”的供应商姓名及电话，结果用视图的形式给出。

（3）哪些工程使用了蓝色的零件？列出工程名，结果用视图的形式给出。

（4）供应商S3发现已供应的所有零件存在质量问题，想找各工程负责人商议补救办法。请列出相关的工程负责人名单。

（5）哪些供应商没有给工程J2供应零件？请列出供应商名。

（6）每一个工程总共用了多少个零件？请列出工程代号，零件总数量。

（7）“胡胜利”（供应商）最近一次供货的时间是什么时候？是供应给哪个工程号？

（8）哪个供应商给全部工程提供了零件，请列出供应商代号

（9）哪一个供应商供应的零件数最多？请列出供应商名

（10）哪一个供应商一次供应的零件数最多？请列出供应商名

（11）哪一个供应商供应的零件种类最多？请列出供应商名

三、实验要求：

1、要求记录运行的SQL语句及运行结果，并能分析和解释实验现象

四、思考题：（要求以下问题回答在实验报告中）

1、第6题如果改为：每一个工程总共用了多少种零件？该如何操作？

2、请描述where和having的不同点

实验五用SQL实现数据库的建立与维护

一、实验目的：

1、掌握SQL的数据定义功能

2、进一步掌握SQL Server Query Analyzer（查询分析器）的使用方法

二、实验内容：

1、启动SQL Server 2000，进入企业管理器

2、建立一个数据库，数据库名为自己的姓名，并存于自建的目录中（此操作可用可视化操作）

3、进入查询分析器，用SQL建立学生表、课程表、选课表，表结构如下：

学生（学号，姓名，性别，出生日期）

课程（课程号，课程名称，学分）

选课（学号，课程号，成绩）

上述各表的属性类型及宽度自定（要求为属性选择合适的数据类型，长度，并且姓名不能为空，性别的缺省值为”男”，成绩为0--100），定义每个表的主码，主码为划线的属性，并将选课表中的学号与学生表中的学号建立关联，选课表中的课程号与课程表中的课程号建立关联。

3、用SQL语句在“学生”表中增加“电话”属性，类型、长度自定。

4、将课程表中的课程名称属性改为字符型，长度为20（原来长度不是20）。

5、用语句分别在三个表中各输入不少于3条记录的数据，内容为自已及同学的信息。

6、用语句实现每门课的学分加1分。

7、按学生的出生日期的降序对学生表建立索引，索引文件名为学生_出生日期。

8、建立平均成绩表（学号，平均成绩），内容为每个学生的平均成绩。

9、删除自己的所有信息（包括选课信息）。

三、实验要求

1、要求记录运行的SQL语句及运行结果，并能分析和解释实验现象

四、思考题：（要求以下问题回答在实验报告中）

1、第9题能否先删除学生表中的信息，再删除选课表中的信息？如果不能，请问为什么不能？

2、修改表中属性的类型和长度时要注意什么问题？

实验六存储过程和触发器

一、实验目的：

1、进一步理解存储过程和触发器的概念、作用，并且理解两者的异同点

2、掌握存储过程和触发器的建立和使用方法

3、掌握通过应用存储过程和触发器解决实际问题的基本方法

二、实验内容：

1、启动SQL Server 2000的企业管理器，按实验二的实验步骤1-2将”工程零件”附加到SQL Server中

该数据库的表结构如下：

供应商（供应商代号，姓名，所在城市，联系电话）

零件（零件代号，零件名，规格，产地，颜色）

工程（工程代号，工程名，负责人，预算）

供应零件（供应商代号，工程代号，零件代号，数量，供货日期）

2、建立如下存储过程并执行所创建的存储过程：

(1)按姓名查询供应商信息

参考语句

create procedure proc_供应商信息_姓名@name char(8)

as

begin

print @name + '的供应商信息'

if not exists( select * from 供应商where 姓名=@name)

print ‘没有符合条件的信息’

else

select * from 供应商where 姓名=@name

end

(2)查询预算在x元至y元之间的工程信息

(3)将零件代号为参数1的供应零件表中的供应商代号更新为参数2，并且在更新之前检查供应

商表中是否存在供应商代号为参数2的供应商，如果不存在则提示不能修改。

3、用触发器完成下列操作：

（1）将对供应零件表中的供应商代号进行更新，并且在更新之前检查供应商表中是否存在此供应商，若不存在则取消更新操作，若存在则执行更新。并观察触发器的作用。

参考语句

create trigger trig_供应零件_修改供应商on 供应零件for update

as

begin

if not exists(select * from 供应商where 供应商代号in (select 供应商代号from inserted))

begin

print ‘不能更新’

rollback

end

end

（2）在供应零件表中增加新数据时进行完整性检查，并观察触发器的作用。

三、实验要求：

1、要求记录运行的SQL语句及运行结果，并能分析和解释实验现象

四、思考题：（要求以下问题回答在实验报告中）

1、比较存储过程和触发器的异同点

实验七数据库保护

一、实验目的：

1、掌握SQL Server 2000中有关用户、权限的管理方法。

2、了解SQL Server2000的数据备份机制

3、加深对数据库安全性的理解

二、实验内容：

1、以sa登录，在MS SQL SERVER2000企业管理中，创建一个数据库，数据库名为DB1，在该数据库中创建一个学生表（学号，姓名，性别，年龄，所在系）。

2、以sa登录，在MS SQL SERVER的安全性中创建一个登录(Login)，登录名为Login1，密码为123456。以Login1登录，观察可访问的数据库情况，并记录。

3、在DB1数据库中创建数据库用户，即使登录名Login1成为DB1的数据库用户，再次以Login1登录，观察可访问的数据库情况，并记录。请在查询分析器中执行如下SQL，观察并记录结果。

1）select * from 学生表

2）用SQL在学生表中插入一条记录

3）create table 课程表(

课号char(10) primary key,

课程名称char(30) not null,

学分smallint not null)

4、新建一个查询分析器窗口，以sa登录，用授权语句赋于Login1用户创建表的权力，查询学生表的权力，向学生表中添加、修改、删除记录的权力。

5、重新执行3中的SQL，观察并记录结果。

6、新建一个查询分析器窗口，以sa登录，用收权语句收回Login1用户创建表的权力，查询学生表的权力，向学生表中添加、修改、删除记录的权力。

7、重新执行3中的SQL，观察并记录结果。

8、创建二个登录，登录名分别为user1、user2，密码分别为user1、user2，并使他们都能访问DB1数据库。以sa的身份使用查询分析器连接数据库，并选择数据库DB1，用SQL语句将学生表的所有权利赋予user1用户，将课程表的所有权利赋予user2用户。新建二个查询分析器窗口，分别以user1和user2连接DB1数据库，用user1和user2分别对学生表和课程表操作，观察并记录结果。

9、创建四个登录，登录名分别为user3、user4、user5、user6，密码分别为user3、user4、user5、user6，并使他们都能访问DB1数据库。以sa的身份使用查询分析器连接数据库，并选择数据库DB1，用SQL 语句将学生表的所有权利赋予user3用户，并能将权利再授权给其它用户，再用SQL语句将学生表的所有权利赋予user4用户，但不能授权给其它用户。新建二个查询分析器窗口，分别以user3和user4连接DB1数据库，在user3的窗口中，将学生的所有权利赋予user5；在user4的窗口中，将学生的所有权利赋予user6，观察并记录结果。

10、使用SQL语句和可视化操作二种方法备份、恢复数据库。

11、使用SQL语句和可视化操作二种方法分离、附加数据库。

三、实验要求：实验时要记录观察到的结果，并能分析结果产生的原因。

四、思考题：（要求以下问题回答在实验报告中）

1、分离数据库和删除数据库有何不同？

2、如果要将实验室中某台计算机中的某个数据库复制到你自己的计算机中，该如何操作？