《数据库原理》课程实验
实验1 创建数据库和数据表
一、实验目的
熟悉SQL Server Management Studio界面；掌握通过图形化向导和执行Transact-SQL语句创建数据库的方法。
二、实验环境
Windows XP操作系统，SQL Server 2005软件。
三、实验课时
2课时。
四、实验内容
SQL Server中的一个数据库必须至少包含一个数据文件和一个事务日志文件，所以创建数据库就是建立主数据文件和日志文件。
在SQL Server 2005中创建数据库的方法主要有两种：一是在SQL Server Management Studio窗口中使用可视化界面，通过方便的图形化向导创建，二是通过执行Transact-SQL语句创建。
（一）向导方式创建
1、从“开始”菜单选择“所有程序”→“Microsoft SQL Server 2005”，打开“SQL Server Management Studio”窗口，使用Windows或SQL Server身份验证建立连接。
2、在“对象资源管理器”窗格中展开服务器，选择“数据库”节点右击，从弹出的快捷菜单中选择“新建数据库”命令，打开“新建数据库”窗口。
3、该窗口中有3个页，分别是“常规”、“选项”和“文件组”，完成这3个页的内容即可完成数据库的创建。这里，我们仅设置“常规”的相应内容，其他2个页的内容按照默认设置即可。
（1）“数据库名称”文本框中输入数据库的名称，如“sample_st”，再输入该数据库的所有者，这里使用默认值即可。（也可以通过单击文本框右边的“浏览”按钮选择所有者。
（2）“数据库文件”列表中包括两行，一个是数据文件，一个是日志文件。通过单击下面相应的按钮可以添加或删除相应的数据文件。
逻辑名称：指定该文件的文件名。
文件类型：用于区别当前文件是数据文件还是日志文件。 文件组：显示当前数据库文件所属的文件组。
初始大小：指定该文件的初始容量。在SQL Server 2005中数据文件的默认值为3MB，日志文件的默认值为1MB。 自动增长：用于设置在文件容量不够用时，文件根据何种增长方式自动增长。
路径：指定存放在文件的目录。默认情况下，SQL Server 2005将存放路径设置为其安装目录下的data子目录，单击该列中的按钮可以在打开的“定位文件夹”对话框中更改数据库的存储路径。
完成上述操作后，单击“确定”按钮关闭“新建数据库”窗口，即完成了数据库的创建，可以在“对象资源管理器”窗格中看到新建的数据库。
（二）语言方式创建
SQL Server 2005使用的Transact-SQL语言是标准SQL的增强版本，使用它提供的Create Database语句同样可以完成对数据库的建立（参考内容见附录）。
1、单击“新建查询”按钮，创建一个查询输入窗口将附录中的语句复制到该窗口。选择创建数据库的命令，单击工具栏中的“分析”按钮，可以检查语法错误；单击“执行”按钮即可执行语句，在查询窗口内的“查询”窗格中可以看到“命令已成功完成”的提示消息。在“对象资源管理器”窗格中刷新，即可看到新建的数据库。
2、继续执行Create Database之后的语句，在“sample_st”数据库中用Create Table命令依次建立Student、Course和SC这三个数据表，并建立各自的约束及它们之间的联系（外键），利用Insert命令为各数据表添加数据。
（三）知识巩固
用SQL-DDL语言创建“产品订购”数据库，包含四个数据表S、P、J和SPJ，表结构及内容见教材P122。
实验2 数据库的简单查询
一、实验目的
掌握单表查询中各个子句（Select、From、Where、Group、Having、Order）的用法，特别要掌握比较运算符和逻辑运算符的使用。
二、实验环境
Windows XP操作系统，SQL Server 2005软件。
三、实验课时
2课时。
四、实验内容
在已建立的数据库和数据表的基础上，用Select语句完成下列查询操作。
1、查询全体学生的学号和姓名。
2、查询全体学生的所有基本信息（仅针对Student表）。
3、对SC表查询全体学生的选课记录，在显示结果中为“Grade”列增加5分。
4、查询全体学生的学号和姓名，将原来的英文列名设置中文别名。
5、显示所有选课学生的学号，去掉重复结果。
6、查询成绩在80分以上的学生选课记录。
7、查询经济系所有学生的学号和姓名。
8、查询成绩在80～90分的学生选课记录。
9、查询年龄不在22～25的学生记录。
10、查询所有姓“刘”的学生记录。
4
11、查询无考试成绩（成绩为空值）的学号、课程号。
12、查询考试成绩非空值的学号、课程号。
13、查询数学系年龄小于20岁的学生记录。
14、查询选修了“101”或“102”课程的选课记录。
15、查询学生总人数。
16、查询选修了课程的学生人数。
17、查询选修了“101”课程的学生的平均成绩。
18、查询学号为“01003”的学生的考试总成绩。
19、查询“101”课程的最高分和最低分。 20、查询每门课程的选课人数。
21、查询每个学生的学号、选课数、平均成绩和总成绩。
22、查询选课数超过2的学生学号及其选课数。
23、查询所有学生信息，查询结果按年龄降序排列。（针对Student表操作）
24、查询所有学生信息，查询结果按系名升序，同系学生按年龄降序排列。
25、查询选课数超过2的学生学号及其选课数，查询结果按选课数降序排列。
实验3 数据库的多表查询
一、实验目的
继续熟练SQL-SELECT语句的使用；理解多表查询的概念；掌握多表连接查询中各个子句的用法，特别要比较内连接和外连接的结果。掌握非相关子查询、相关子查询的用法，理解它们的执行过程；学会在SQL Server中用Exists实现交运算，用Not Exists实现差运算。
二、实验环境
Windows XP操作系统，SQL Server 2005软件。
三、实验课时
2课时。
四、实验内容
（一）、在已建立的数据库和数据表的基础上，用Select语句的连接查询完成下列操作。
1、查询每个学生的基本信息及其选课情况。
2、查询选修“101”课程的学生学号、姓名和成绩。
3、查询与“李平”在同一个系学习的学生记录。
4、查询与“李平”年龄相同的学生记录。
5、查询选修课程名为“数据库原理”的选课记录。
6、查询选修课程名为“数据库原理”的学生记录。
7、查询选修“101”课程且成绩≥90的学号、姓名和成绩。
8、查询“李平”的所有选课记录。
9、查询数学系学生选修的课程号，要求结果中去掉重复记录。
10、查询计算机系选修课程数≥2的学号、姓名及平均成绩，查询结果按平均成绩降序。
11、查询每个学生的学号、选修课程号、课程名及成绩。
12、查询所有学生的选修情况（包括选课和未选课的学生），要求显示学号、姓名、课程号和成绩。
（二）、在已建立的数据库和数据表的基础上，用Select语句的嵌套查询完成下列操作（含EXISTS量词）。
1、查询选修“101”课程且成绩≥90的学号、姓名。
2、查询“李平”的所有选课记录。
3、查询与“李平”在同一个系学习的学生记录。
4、查询与“李平”年龄相同的学生记录。
5、查询选修课程名为“数据库原理”的选课记录，输出结果包括学号和成绩。
6、查询选修课程名为“数据库原理”的学生记录，输出结果包括学号、姓名和所在系。
7、查询学号为“01003”学生的选修课程号和课程名。
8、查询没有选修“101”课程的学生学号和姓名。
9、查询选修“101”课程或“102”课程的学生姓名。
10、查询选修“101”课程和“102”课程的学生学号。
11、查询选修“101”课程但没选修“102”课程的学生学号。
12、查询没有选修任何课程的学生记录，输出结果包括学号、姓名和所在系。
13、查询数学系学生选修的课程号，要求结果中去掉重复记录。
14、查询选修课程至少包含“01003”选修课程的学生学号。
7 实验4 数据库更新、视图定义及使用
一、实验目的
掌握Insert、Update、Delete语句的使用；对于Insert语句，要求理解默认值约束、空值约束在插入记录时所起的作用。理解视图的概念，掌握Create View、Drop View等语句的使用；掌握基于视图的查询语句的使用。
二、实验环境
Windows XP操作系统，SQL Server 2005软件。
三、实验课时
2课时。
四、实验内容
（一）、在已建立的数据库和数据表的基础上，向数据库中添加一个临时表ST（sno,sname,dept,sage,cno,cname,score）。用Insert语句向临时表输入数据，输入有误时用Update语句进行修改。再用三个更新语句完成下列操作。
1、向Student表添加若干新记录，内容自定。（观察已定义的表的约束情况）
2、向SC表添加新记录，内容自定，不低于10条（注意不能违反参照完整性）。
3、为临时表ST添加记录。
4、把Student表的所有行及其对应的选课一次性地加到临时表ST中。
5、在ST表中把所有学生的成绩加2分。
6、在ST表中把所有学生的年龄增加1。
7、在ST表中把“李平”的所在系改为“计算机”。
8、在ST表中将选修课程“数据库原理”的学生成绩加2分。
9、在SC表中删除所有成绩为空值的选修记录。
10、删除计算机系选修成绩不及格的选课记录。
（二）、在已建立的数据库和数据表的基础上，完成下列操作。
1、建立数学系学生的视图MAST。
2、建立计算机系选修课程名为“数据库原理”的学生视图，视图名：CSTVIEW，该视图中应包括属性列：学号、姓名、成绩。
3、创建一个名为STSUMVIEW的视图，包括所有学生的学号和总成绩。
4、建立学生选课视图SCVIEW，包括所有学生的学号、姓名、课程号、课程名和成绩。
5、通过MAST视图查询学生基本信息。
6、通过SCVIEW查询成绩大于90分的学生的学号和成绩。
7、查询计算机系选修课程名为“数据库原理”并且成绩大于85分的学生的学号和成绩。
8、通过MAST视图将学号为“01008”学生的年龄修改为21岁。
9、通过MAST视图将学号为“01009”学生所在系改为“经济”，是否能成功执行？若不能成功请说明理由；若修改成功，请再次通过MAST视图查询学生基本信息，是否能查询到该生？若不能查询请说明理由。
10、通过SCVIEW视图将学号为“01004”学生的总成绩修改为380分， 是否能成功执行？若不能成功请说明理由。
实验5 数据库的安全与保护
一、实验目的
理解SQL Server的用户与权限管理机制，掌握用对象资源管理器创建服务器登录帐号，并同时建立用户。掌握如何为给定的用户分配权限。
二、实验环境
Windows XP操作系统，SQL Server 2005软件。
三、实验课时
2课时。
四、实验内容
用户与权限管理
（1）打开“SQL Server Management Studio”窗口，使用对象资源管理器创建两个服务器登录帐号Test1和Test2，并在“sample_st”数据库中创建两个对应的同名用户。设置Test1用户和Test2用户的默认架构为guest。
（2）使用Test1身份登录，并为Test2分配创建数据表的权限；用Test2身份登录来验证权限分配成功。
（3）使用Test1身份登录，收回刚刚分配给Test2的创建数据表的权限；用Test2身份登录来验证权限回收成功。
五、实验步骤
1、使用对象资源管理器创建两个服务器登录帐号。
10 以系统管理员身份登录SQL Server。打开“SQL Server Management Studio”窗口，在对象资源管理器列表中，打开展开“安全性”文件夹，选择“登录名”图标，单击鼠标右键，在弹出的菜单中选择“新建登录名”。在打开的对话框中依次建立Test1和Test2，选择“SQL Server身份验证”，同时还要输入密码，默认数据库为“sample_st”。
（2）在“用户映射”中列出了当前登录帐号可以选择访问的数据库如“sample_st”，在其左侧的复选框中打勾，表示当前登录帐号可以访问对应的数据库，默认用户名与登录帐号相同。
（3）单击“确定”按钮完成创建。
2、创建新的数据库用户。
在对象资源管理器中选中要访问操作的数据库，展开“安全性”文件夹，在“用户”文件夹中查找是否已建立与登录名Test1和Test2同名的数据库用户，若已建立，则在属性中修改其默认架构为guest；否则右击鼠标在菜单中选择“新建用户”选项，出现“数据库用户-新建”对话框，建立相应的用户。
3、用Grant、Revoke命令实现对用户的授权和收权。
4、用Create命令创建数据表验证授权和收权是否成功。
5、以系统管理员身份登录SQL Server。使用：“GRANT SELECT ON SC TO Test1 WITH GRANT OPTION”命令，再分别以Test1和Test2身份登录，进行授权及查询数据表，验证“WITH GRANT OPTION”能否成功执行。
6、以系统管理员身份使用“REVOKE”命令回收上述授权。
实验6 数据库的事务及并发控制
一、实验目的
通过实验加深学生对事务的基本概念理解语掌握；加深学生对并发控制的基本概念理解，认识不正确的并发控制所带来的危害；加深学生对锁的基本概念的掌握与理解，认识锁带来的问题；
二、实验环境
Windows XP操作系统，SQL Server 2005软件。
三、实验课时
2课时。
四、实验内容
1、事务的控制
数据准备：执行如下命令Select * into ##temp from student，将student的数据倒入到##temp表中。 启动事务，执行删除后，回滚事务
选择##temp的数据，察看记录总数 显式启动事务 删除##temp表的数据
选择##temp数据，察看记录总数 回滚事务
选择##temp数据，察看记录总数
启动事务，执行删除后，提交事务
选择##temp数据，察看记录总数
12 显式启动事务 删除##temp表的数据
选择##temp的数据，察看记录总数 回滚事务
选择##temp数据，察看记录总数
比较这两次执行效果的差异，为什么会有这些差异？
2、事务的隔离级别试验
数据准备：执行如下命令Select * into ##temp from student，将student的数据倒入到##temp表中。 脏读
启动两个分析器，分别叫（A，B）
在A中，选择##temp表中数据，察看记录总数 在A中，显式启动事务 在A中，删除##temp表的数据
在B中，将事务隔离级别设为UNCOMMITTED 在该查询分析器中选择##temp表数据，察看记录总数 回到先前窗口，回滚事务 选择##temp数据，察看记录总数 再次切查询分析器窗口
选择##temp数据，察看记录总数 观察结果，为什么会有这些现象？ 不可重复读
启动两个分析器，分别叫（A，B）
在A中，显式启动事务，察看dept = \'math\'的记录（注意地址中的数据）
在B中，显式启动事务，察看dept = \'math\'的记录。在B中，
13 将dept = \'math’地址更新为’AAAAAA’
在B中，再次察看dept = \'math\'的记录。在B中，提交事务 在A中，再次察看dept = \'math\'的记录。 在A中，提交事务
观察结果，为什么会有这些现象？ 丢失修改
启动两个查询分析器，分别叫（A，B）
在A中，显式启动事务，察看dept = \'math\'的记录（注意地址中的数据）
在B中，显式启动事务，察看dept = \'math\'的记录。 在B中，将dept = \'math’地址更新为’AAAAAA’ 在B中，再次察看dept = \'math\'的记录。 在B中，提交事务
在A中，再次察看dept = \'math\'的记录。 在A中，将dept = \'math’地址更新为’BBBBBB’ 在A中，提交事务
在A，B窗口分别察看dept = \'math’的记录，结果如何? 锁的模拟
启动两个分析器，分别叫（A，B）
在A中，显式启动事务，察看dept = \'math\'的记录 在B中，显式启动事务，察看dept = \'math\'的记录 在B中，将dept = \'math’地址更新为’AAAAAA’ 在A中，将dept = \'math’地址更新为’BBBBBB’ 观察A窗口的结果，为什么？
事务隔离级别设置：set transaction isolation level {read committed, read uncommitted, repeatable read, serializable}
实验7 数据库设计实验
一、实验目的
通过实验掌握数据库设计的基本方法和步骤。
二、实验环境
Windows XP操作系统，SQL Server 2005软件。
三、实验课时
2课时。
四、实验内容
1、选定实验题目，为某个单位或部门设计数据库应用系统，比如：学生成绩管理、机房上机管理、职工档案管理、商品库存管理、图书管理、工资管理等。
2、根据选定的题目进行需求分析，重点分析数据需求和功能需求。
3、概念结构设计：画出E-R图。步骤如下：
（1）设计局部E-R图。设计依据是需求分析阶段的DFD/DD。主要内容是确定实体集合、联系、属性及主关键字
（2）集成局部E-R图。集成时要解决冲突和冗余等问题。 （3）合并局部E-R图 。合并局部E-R图中相同部分，尽可能的保留特殊部分，删除冗余部分，用累加的方式集成若干个局部E-R图。
（4）优化全局E-R图，得到最佳的全局E-R图方案。
4、逻辑结构设计：设计数据库和数据表的具体结构，指出各表的属性名称、数据类型；说明各表的主码及表之间的关联情况；说明本设计是否已达到3NF要求。
5、简要评价系统设计的优点和不足。
五、实验要求
1、提交设计报告，涵盖实验内容的全部信息，不低于1000字。
2、提交建立应用系统数据库的代码，所有数据表中的记录总数不低于50条。
实验8 存储过程实验
一、实验目的
通过实验熟悉使用存储过程进行数据库应用程序设计的方法
二、实验环境
Windows XP操作系统，SQL Server 2005软件。
三、实验课时
2课时。
四、实验内容
对sample_st数据库，编写存储过程，完成下面功能
1、统计任意一门课程的平均成绩
2、统计所有课程的平均成绩
3、统计任意一门课程的成绩分布情况，即按照分数段统计人数（即 9、70-7
9、80-8
9、90以上）
