数据库原理实验教案.doc 15页

《数据库原理》课程实验实验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计算机原理教案下载，表结构及内容见教材P74。实验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、查询所有姓“刘”的学生记录。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语句的嵌套查询完成以下操作。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”选修课程的教师学号。实验4 数据库更新、视图定义及使用一、实验目的掌握Insert、Update、Delete语句的使用；对于Insert语句，要求理解默认值约束、空值约束在插入记录时所起的作用。理解视图的概念，掌握Create View、Drop View语句的使用；掌握基于视图的查询语句的使用。二、实验环境Windows XP操作系统，SQL Server 2005软件。三、实验课时2课时。四、实验内容（一）、在未完善的数据库和数据表的基础上，向数据库中添加一个临时表ST，其结构与Student表结构相似。用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、使用对象资源管理器创建两个服务器登录账号。以平台管理员身份注册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 数据库的完整性一、实验目的通过试验掌握数据库完整性概念，掌握运用SQL Server 2005实现数据库完整性的基本原则跟方法。二、实验环境Windows XP操作系统，SQL Server 2005软件。三、实验课时2课时。四、实验内容1、建立关系体系。完成教材P164第6题，用SQL语言定义“职工”和“部门”两个关系体系:职工（职工号,姓名,年龄，职务,工资,部门号）,职工号为主码部门（部门号,名称,经理名,电话）, 部门号为主码规定在机制中完成下列完整性约束条件的定义：（1）定义每个路径的主码；（2）定义参照完整性；（3）定义职工年龄不得超过60岁；（4）定义部门名称应该取值唯一。2、验证完整性约束。使用Insert Values语句向两个表中各插入5～10条记录，对以下内容进行验证。（1）验证主键约束：“职工”和“部门”两个表中能否能接受主码值同样的记录，通过例子验证。（2）验证唯一约束：“部门”表中能否能接受名称相似的两个不同记录？为什么？（3）验证检查约束：“职工”表中能够接受年龄不满足条件的记录？为什么？（4）验证参照完整性：若“部门”表中不存在部门号为“008”的记录，是否可在“职工”表中插入部门号为“008”的职工记录？若删除“001”部门的所有信息，正确的操作方法是哪个？实验7 数据库设计实验一、实验目的通过试验掌握数据库设计的基本原则跟方法。

二、实验环境Windows XP操作系统，SQL Server 2005软件。三、实验课时2课时。四、实验内容1、选定实验题目，为某个单位或部门设计数据库应用平台，比如：学生成绩管理、机房上机管理、职工档案管理、商品库存管理、图书管理、工资管理等。2、根据指定的题目进行意愿分析，重点剖析数据需求跟功能需求。3、概念结构设计：画出E-R图。4、逻辑结构设计：设计数据库和数据表的详细结构，指出各表的属性名称、数据类别；说明各表的主码及表之间的关联情况；说明本设计能否已超过3NF要求。5、简要评价平台设计的特点跟不足。五、实验要求1、提交设计报告，涵盖实验内容的全部信息，不超过1000字。2、提交设立应用平台数据库的代码，所有数据表中的记录人数不超过50条。（请参照实验2的附表建立数据库）实验8 存储过程实验一、实验目的通过试验熟悉使用存储过程进行数据库应用程序设计的方式二、实验环境Windows XP操作系统，SQL Server 2005软件。三、实验课时2课时。四、实验内容对sample_st数据库，编写存储过程，完成以下用途1、统计任意一门课程的平均成绩2、统计所有课程的平均成绩3、统计任意一门课程的成绩分布状况，即根据分数段统计数量（即