数据库原理及应实验指导书.doc

数据库原理与应用数据库原理与应用 综合性实验指导书综合性实验指导书 数据库理论及应用教研室数据库理论及应用教研室 2009.82009.8 目 录 实验实验 1 DDL 语言的应用语言的应用 1 实验实验 2 DML 语言的应用语言的应用 .5 实验实验 3 数据库查询语言的应用（一）数据库查询语言的应用（一）.7 实验实验 4 数据库查询语言的应用（二）数据库查询语言的应用（二）.8 实验实验 5 DCL 语言的应用、备份和恢复语言的应用、备份和恢复 .10 实验实验 6 存储过程与触发器的应用存储过程与触发器的应用.11 实验实验 1 1 DDLDDL 语言的应用语言的应用 实验目的实验目的：本次实验，要求学生在 SQL Server 2000 中创建学生信息管理数据库； 创建学生基本信息表、课程基本信息表以及 学生_课程表。要求学生为学生信息管理数 据库的学生基本信息表、课程基本信息表以 及学生_课程表根据已经确定的主码，为这 三个表之间建立关联。根据需求，建立必要 的索引、视图等数据库对象。 实验内容实验内容 ： 1）创建学生信息管理数据库，创建 student 库，数据文件初始为 10MB，最大空 间为 20MB，文件自动增长为 10%，日志文件 初始大小为 2MB，最大空间 10MB，文件的增 长率为 10MB。 2）创建表、确定表的主码和约束条件, 表的数据字典如下诸表所示。 SUDENT 表的结构 列列 名名 说说 明明 数据数据 类型类型 约约 束束 Sn o 学号 字符 串,长度 7 主码 Sn ame 姓名 字符 串,长度 10 非空 Ss ex 性别 长度, 字符串 2 取”男”或” 女” Sa ge 年龄整数取值 1545 Sd ept 所在 系 字符 串 默认值”计算 机系” COURSE 表的结构 列列 名名 说说 明明 数据数据 类型类型 约约 束束 Cno课程 号 字符 串,长度 10 主码 Cna me 课程 名 字符 串,长度 20 非空 Ccr edit 学分整数取值0 Sem ster 开课 学期 整数取值0 Per iod 学时整数取值0 SC 表的结构 列列 名名 说说 明明 数据数据 类型类型 约约 束束 Sn o 学号字符 串,长度 7 主码, 引 用 Student 的 外码 Cn o 课程 号 字符 串,长度 10 主码,引 用 Course 的 外码 Gr ade 成绩整数取值 0100 3）熟悉 SQL Server Enterpriser Manager 工具的使用方法。 4)为基本表 STUDENT、 COURSE、 SC 建 立基于主码的聚簇索引。 5)为基本表 STUDENT、 COURSE、 SC 建立非聚簇索引，索引字段自由确定。 6)建立符合要求的数据库视图。 7)为基本表建立关系图。 （本部分实验 内容可以补写入实验报告） 实验步骤：实验步骤： 第一步第一步 启动 SQL Server 2000 Query Analyzer，输入如下代码： CREATE DATABASE STUDENT ON ( NAME = STUDENT_DATA, FILENAME = C:program filesMicrosoft SQL ServerMssqlData STUDENT.mdf , SIZE = 10, MAXSIZE = 20, FILEGROWTH = 10% ) LOG ON ( NAME = STUDENT_LOG, FILENAME = C:program filesMicrosoft SQL ServerMssqlData STUDENT.ldf , SIZE = 2, MAXSIZE = 10, FILEGROWTH = 1 ) 执行以上代码之后，完成了创建学生信 息管理数据库。 第二步第二步 启动 SQL Server 2000 Query Analyzer，输入如下代码： USE STUDENT GO CREATE TABLE Student ( Sno char ( 7 ) PRIMARY KEY, Sname char ( 10 ) NOT NULL, Ssex char (2) CHECK (Ssex = 男 OR Ssex = 女), Sage tinyint CHECK (Sage = 15 AND Sage 0), Semester tinyint CHECK (Semester 0), Period int CHECK (Period 0), PRIMARY KEY(Cno) ) 执行以上代码之后，完成了创建 COURSE 表。 第四步第四步 启动 SQL Server 2000 Query Analyzer，输入如下代码： USE STUDENT GO CREATE TABLE SC ( Sno char(7) NOT NULL, Cno char(10) NOT NULL, Grade tinyint, CHECK (Grade = 0 and Grade =500) 实验实验 3 3 数据库查询语言的应用（一）数据库查询语言的应用（一） 实验目的：实验目的：本次实验，要求学生在学生信息 管理数据库的基本表中，适当利用集函数检 索满足条件的数据，并根据要求输出检索结 果。 （单表数据检索，实验时要求分别使用 SQL 语言和 T-SQL 语言实现自定的题目） 实验内容：实验内容： 1)检索学生的姓名，年龄和性别 2)检索学生选修课程的课程号 3)检索学习课程号为 C2 的所有学生的学号 和成绩 4)检索课程号为 C2 且成绩高于 85 分以上的 学生学号和成绩 5) 检索选修 C1 或 C2 的学生学号 6) 检索成绩在 70 分至 80 分之间的学生学 号,课程号和成绩 7) 检索选修 C1 的学生学号和成绩,并按成 绩的降序和学号的升序排列 8)检索学号为 S1,S3,S4 的姓名,年龄(年龄 按升序排列) 实验步骤：实验步骤： 启动 SQL Server 2000 Query Analyzer， 输入如下代码：（查询结果在此不做描述， 操作的方式相似，不赘述） USE STUDENT GO SELECT SN,AGE,SEX FROM S; (SELECT * FROM S；) SELECT DISTINCT C# FROM SC SELECT S#，GRADE FROM SC WHERE C#=C2; S#,GRADE FROM SC WHERE C#=C2 AND GRADE85; SELECT S# FROM SC WHERE C#=C1 OR C#=C2; SELECT * FROM SC WHERE GRADE BETWEEN 70 AND 80; SELECT S#,GRADE FROM SC WHERE C#=C1 ORDER BY GRADE DESC,S#; SELECT SN,AGE FROM S WHERE S# IN (S1,S3,S4) ORDER BY AGE; 执行以上代码，运行的结果分别对应实验内 容中的 8 个题目。根据实验过程中的实际情 况，同学们可以自行命题实现数据的检索。 实验实验 4 4 数据库查询语言的应用（二）数据库查询语言的应用（二） 实验目的：实验目的：本次实验，要求学生在学生信息 管理数据库的多个基本表中，适当利用集函 数检索满足条件的数据，并根据要求输出检 索结果。 （多表数据检索，可以使用的多表 连接关键词包括：IN、 ALL、 ANY、 EXISTS、 INNER JOIN ON 以及关系运算符 等等，实验时要求分别使用 SQL 语言和 T- SQL 语言实现自定的题目） 实验内容：实验内容： 1）检索辛国年同学所学课程的课程号及成 绩 2）查询每一门课程的间接先修课的课号 和课名。(参照给定的数据表)3）检索所有 学生的姓名、选课名称和成绩。 4）检索和辛国年同学同岁的学生姓名。 5）检索选修 C2 课程号的学生姓名 6）检索 选修课程 C2 的学生中成绩最高的学生的学 号。 7）检索没有选修 C2 的学生姓名 8）检索选修 C2 课程号的学生姓名(用 EXISTS)9）检索没有选修 C2 的学生姓名 (用 NOT EXIST) 10）检索出选修课程至少等于三门的学生学 号和选课门数 11）检索缺少学习成绩的学生学号和课程号 实验步骤实验步骤：启动 SQL Server 2000 Query Analyzer，输入如下代码：（查询结果在此 不做描述，操作的方式相似，不赘述） USE STUDENT GO SELECT SN,C#,GRADE FROM S,SC WHERE S.S#=SC.S# AND SN=辛国年; SELECT FIRST.C#,SECOND.CP# FROM C FIRST,C SECOND WHERE FIRST.CP#=SECOND.C# SELECT SN,CN,GRADE FROM S,SC,C WHERE S.S#=SC.S# AND SC.C#=C.C#; SELECT SN FROM S WHERE AGE= (SELECT AGE FROM S WHERE SN=辛国年); SELECT SN FROM S WHERE S#=ANY (SELECT S# FROM SC WHERE C# =C2); (SELECT SN FROM S,SC WHERE S.S#=SC.S# AND C#=C2); WHERE C#=C2 AND GRADE=ALL (SELECT GRADE FROM SC WHERE C#=C2); WHERE C2 NOT IN (SELECT C# FROM SC WHERE S#=S.S#); SELECT SN FROM S WHERE EXISTS (SELECT * FROM SC WHERE S#=S.S# AND C#=C2); SELECT SN FROM S WHERE NOT EXISTS (SELECT * FROM SC WHERE S#=S.S# AND C#=C2); SELECT S#,COUNT(*) FROM SC GROUP BY S# HAVING COUNT(*)=3; SELECT S#,C# FROM SC WHERE GRADE IS NULL; 执行以上代码，运行的结果分别对应实验内 容中的 8 个题目。根据实验过程中的实际情 况，同学们可以自行命题实现数据的检索。 实验实验 5 5 DCLDCL 语言的应用、备份和恢复语言的应用、备份和恢复 实验目的实验目的：本次实验，要求学生对学生信息 管理数据库、学生基本信息表、课程基本信 息表以及学生_课程表根据要求进行插入、 删除、更新、检索等权限的授予和收回。并 根据所得到的权限进行相应的操作；要求学 生对数据库和基本表进行数据的备份和恢复。 实验内容实验内容： 1、权限的授予与收回 (1)授予(收回)SCOTT 用户创建基本表的权 限 (2)把 STUDENT 表上的插入和修改 Sno 列 的特权授予 LI 和 WANG 用户 (3)把 Course 表上的所有权力授予用户 WANG,而 WANG 还 可以把权限授予其它用户 (4)从 WANG 用户 手中收回修改 SCOTT 表的权限 2、数据库的 备份与恢复 本实验的实验方法参照相关 SQL Server 书籍中数据备份和恢复机制的内容 和步骤进行，本指导书将省略操作步骤。 1)用 Enterprise Manager 创建一个备份 设备，自命名。 2)为学生信息管理数据库设置一个备份计 划，要求每星期一进行备份。 3)在 Enterprise Manager 中恢复学生选 课数据库。 本部分实验过程可以不写入实验报告， 但要求将实验内容和结果写在实验报告中。 2 2、利用查询分析器、利用查询分析器 启动 SQL Server 2000 Query Analyzer， 输入如下代码：（查询结果在此不做描述， 操作的方式相似，不赘述） /*授予(收回)SCOTT 用户创建基本表的权限 */GRANT CREATE TABLE TO SCOTT; REVOKE CREATE TABLE FROM SCOTT; /*把 STUDENT 表上的插入和修改 Sno 列的特 权授予 LI 和 WANG 用户*/GRANT INSERT,UPDATE(Sno) ON Student TO LI,WANG; /*把 Course 表上的所有权力授予用户 WANG,而 WANG 还可以把权限授予其它用户*/GRANT ALL ON Course TO WANG WITH GRNAT OPTION /*从 WANG 用户手中收回修改 SCOTT 表的权 限*/REVOKE UPDATE ON Student FROM WANG 分别在查询分析器中执行上述语句，查看执 行结果。 实验实验 6 6 存储过程与触发器的应用存储过程与触发器的应用 实验目的实验目的：本次实验，要求学生在学生基本 信息表中创建插入数据触发器、在课程基本 信息表中创建删除数据和更新数据触发器。 在该数据库中创建检索满足条件的数据的存 储过程。学生可以根据实际情况，自行设计 实验题目。 实验内容实验内容： 1)基于 PUBS 数据库建立 AU_INFO 存储过 程，该存储过程利用表间连接的方式返回 指定的作者的姓名、该作者所写的书籍的 出版商。 2)基于 student 数据库创建触发器。在 Student 表中存储了学号和姓名信息。在 COURSE 表中存储了课程的编号、学号和 成绩等信息。在删除 STUDENT 表中某些学 生的信息时，应该同时删除 COURSE 表中 相关的数据行。 实验步骤：实验步骤： 第一步第一步 启动 SQL Server 2000 Query Analyzer， 输入如下代码： /*基于 PUBS 数据库建立 AU_INFO 存储过程， 该存储过程利用表间连接的方式返回指定的 作者的姓名、该作者所写的书籍的出版商。 */ use pubs if exists (select name from sysobjects where name=au_info and type=P) drop proc au_info go use pubs go create