《数据库系统原理》实验指导书定.doc

数据库系统原理实验指导书计算机科学与技术与学院计算机科学与技术系二一二年目 录实验一 认识DBMS3实验二 交互式SQL（4小时）4实验三 数据库完整性（2小时）13实验四 数据库安全性（2小时）17实验一 认识DBMS一、实验目的1通过对某个商用数据库管理系统的安装使用，初步了解DBMS的工作环境和系统构架。在此推荐SQL SERVER2005。2熟悉DBMS的安装、配置及使用。3搭建今后实验的平台。二、实验平台操作系统：Windows XP。数据库管理系统：SQL SERVER2005。选择安装各个数据库管理系统之前，请仔细看清硬件的配置要求。三、实验内容和要求1根据安装文件的说明安装数据库管理系统。在安装过程中记录安装的选择，并且对所作的选择进行思考，为何要进行这样的配置，对今后运行数据库管理系统会有什么影响。2学会启动和停止数据库服务。 在正确安装SQL SERVER2005后，SQL SERVER数据库引擎服务会在系统启动时自动启动。如果要手动地启动和停止数据库引擎服务，可以通过SQL SERVER配置管理器（SQLSERVER CONFIGURATION MANAGER）来进行管理。SQL SERVER配置管理器综合了SQLSERVER 2000中的服务管理器、服务器网络适用工具和客户端网络实用工具的功能。打开 SQL SERVER配置管理器工具，单击“SQL SERVER2005服务”节点，其中的“SQL SERVER”服务就是我们所说的数据库引擎。与SQL SERVER 2000一样，可以通过这个配置管理器来启动、停止所安装的服务，如“SQLSERVER（MSSQLSERVER）”。3了解RDBMS系统的体系结构。 SQL SERVER2005是一款具有“客户机/服务器”架构的关系型数据库管理系统，它使用T-SQL语言在客户机和服务器之间传递客户机的请求和服务器的响应。 数据库体系结构：又划分为数据库逻辑结构和数据库物理结构。数据库逻辑结构主要应用于面向用户的数据组织和管理，如表、视图、存储过程和触发器、约束等。数据库物理结构主要应用于面向计算机的数据组织和管理，如数据以表文件的形式存放在硬盘上。4了解RDBMS的管理和使用。例如SQL SERVER Management Studio是SQL SERVER 2005种最重要的管理工具，它融合了SQL SERVER2000的查询分析器和企业管理器、OLAP分析器等多种工具的功能，为管理人员提供了一个简单的实用工具，使用这个工具既可以用图形化的方法，也可以通过编写SQL语句来实现数据库的操作。5初步了解RDBMS的安全性，这里主要是服务器用户的登录和服务器预定义角色。可以尝试建立一个新的登录名，赋予其数据库管理员的角色，今后的实验可以用该登录名来创建数据库用户。实验二 交互式SQL（4小时）一、实验目的熟悉通过SQL对数据库进行操作。二、实验工具利用实验一中安装的RDBMS及其交互查询工具来操作SQL语言。三、实验内容和要求1在RDBMS中建立一个学生-课程数据库，进行实验所要求的各种操作，所有的SQL操作均在此建立的新库里进行。2根据以下要求认真进行实验，记录所有的实验用例及执行结果。数据定义：基本表的创建、修改及删除；索引的创建和删除。数据操作：完成各类查询操作(单表查询，连接查询，嵌套查询，集合查询)；完成各类更新操作(插入数据，修改数据，删除数据)。视图的操作：视图的定义(创建和删除)，查询，更新(注意更新的条件)。特别说明：实验中注意特定数据库系统（如SQL SERVER）的SQL语句格式与SQL-3标准的区别。参考实验用例：(一)数据定义：一基本表的操作1建立基本表1)创建学生表Student,由以下属性组成：学号Sno(char型，长度为9，主码)，姓名Sname(char型，长度为20，唯一)，性别Ssex(char型，长度为2)，年龄(smallint),所在系(char型，长度为20)。 create table Student (Sno char(9) primary key,Sname char(20) unique,Ssex char(2),Sage smallint,Sdept char(20);2）创建课程表Course,由以下属性组成：课程号Cno(char型，主码，长度为4)，课程名Cname(char型，长度为40)，先行课Cpno(char型，长度为4,外码)，学分Ccredit(smallint)。 create table Course(Cno char(4) primary key,Cname char(40),Cpno char(4),Ccredit smallint);3)创建学生选课表SC，由以下属性组成：学号Sno(char型，长度为9)，课程号Cno(char型，长度为4)，成绩Grade(smallint),其中Sno和Cno构成主码。create table sc(Sno char(9),Cno char(4),Grade smallint,primary key(Sno,Cno),foreign key (Sno) references student(Sno),foreign key (Cno) references course(Cno)；2修改基本表：1)向Student表增加“入学时间列”，其数据类型为日期型。 alter table Student add S_entrance date;2)将年龄的数据类型由字符型(假设原来的数据类型是字符型)改为整数。 alter table Student alter column Sage int;3)增加课程名称必须取唯一值的约束条件。 alter table Course add unique(Cname);3删除基本表：1)在所有的操作结束后删除Student表。 drop table Student;2)在所有的操作结束后删除Course表。 drop table Course;3)在所有的操作结束后删除SC表。 drop table SC;二索引操作1建立索引1)为学生课程数据库中的Student，Course，SC 3个表建立索引。其中Student表按学号升序建唯一索引，Course表按课程号升序建唯一索引，SC表按学号升序和课程号降序建唯一索引。create unique index Stusno on Student(Sno);create unique index Coucno on Course(Cno);create unique index SCno on SC(Sno ASC,Cno DESC);2删除索引1)删除Student表的Stusname索引。 drop index Stusname; (二)数据操作一更新操作1,插入数据1)在Student表中插入下列数据： ，李勇，男，20，CS ，刘晨，女，19，CS ，王敏。女，18，MA，张立，男，19，ISinsert into student(Sno,Sname,Ssex,Sage,Sdept)values('','李勇','男',20,'CS');insert into student(Sno,Sname,Ssex,Sage,Sdept)values('','刘晨','女',19,'CS');insert into student(Sno,Sname,Ssex,Sage,Sdept)values('','王敏','女',18,'MA');insert into student(Sno,Sname,Ssex,Sage,Sdept)values('','张立','男',19,'IS')2)在Course表中插入以下数据：1,数据库,5,42,数学,null，26,数据处理,null，24,操作系统,6,37,PASCAL语言,6,45,数据结构,7,41,数据库,5,43,信息系统,1,4insert into course(Cno,Cname,Cpno,Ccredit)values('1','数据库','5',4);insert into course(Cno,Cname,Ccredit)values('2','数学',2);insert into course(Cno,Cname,Ccredit)values('6','数据处理',2);insert into course(Cno,Cname,Cpno,Ccredit)values('4','操作系统','6',3);insert into course(Cno,Cname,Cpno,Ccredit)values('7','PASCAL语言','6',4);insert into course(Cno,Cname,Cpno,Ccredit)values('5','数据结构','7',4);insert into course(Cno,Cname,Cpno,Ccredit)values('1','数据库','5',4);insert into course(Cno,Cname,Cpno,Ccredit)values('3','信息系统','1',4);3) 在SC表中插入以下数据：,1,92,2,85,3,88,2,90,3,80insert into sc (Sno,Cno,Grade) values ('','1',92);insert into sc (Sno,Cno,Grade) values ('','2',85;insert into sc (Sno,Cno,Grade) values ('','3',88);insert into sc (Sno,Cno,Grade) values ('','2',90);insert into sc (Sno,Cno,Grade) values ('','3',80);4)将一个新学生元祖(学号：；姓名：陈冬；性别：男；所在系：IS；年龄：18岁)插入到Student表中。 insert into student (Sno,Sname,Ssex,Sdept,Sage) values ('','陈冬','男','IS',18);5)将学生张成民的信息插入到Student表中。 insert into student values('','张成民','男',18,'CS');6)插入一条选课记录：(，1)。 insert into sc(Sno,Cno) values('','1');7)对每一个系，求学生的平均年龄，并把结果存入数据库。 create table Dept_age (Sdept char(15), Avg_age smallint); insert into Dept_age (Sdept,Avg_age) select Sdept,avg(Sage)from student group by Sdept;2修改数据1)将学生的年龄改为22岁。update student set Sage=22 where Sno=''2)将所有学生的年龄增加一岁。 update student set Sage=Sage+1;3)将计算机科学系全体学生的成绩置零。update sc set Grade=0 where 'CS'=(select Sdept from student where student.Sno=sc.Sno);3删除数据1)删除学号为的学生记录。 delete from student where Sno=''2)删除所有学生的选课记录。 delete from sc;3)删除计算机科学系所有学生的选课记录。 delete from sc where 'CS'=(select Sdept from student where student.Sno=SC.Sno );二查询操作1单表查询1)查询全体学生的学号与姓名。 select Sno,Sname from student;2) 查询全体学生的姓名、学号、所在系。 select Sname,Sno,Sdept from student;3) 查询全体学生的详细记录。 select * from student;4) 查询全体学生的姓名及其出生年份。 select Sname,2011-Sage from student;5) 查询全体学生的姓名、出生年份和所在院系，要求用小写字母表示所有系名。 select Sname,'Year of Birth:',2011-Sage ,lower(Sdept) from student; select Sname NAME,'Year of Birth:' BIRTH,2011-Sage BIRTHDAY,lower(Sdept) DEPARTMENT from student;6)查询选修了课程的学生学号。 select distinct Sno from sc; 7)查询计算机科学系全体学生的名单。 select Sname from student where Sdept='CS' 8)查询所有年龄在20岁以下的学生姓名及其年龄。 select Sname,Sage from student where Sage<20; 9)查询考试成绩有不及格的学生的学号。 select distinct Sno from sc where Grade<60; 10)查询年龄在20-23岁(包括20岁和23岁)之间的学生的姓名、系别和年龄。 select Sname,Sdept,Sage from student where Sage between 20 and 23; 11) 查询年龄不在20-23岁(包括20岁和23岁)之间的学生的姓名、系别和年龄。 select Sname,Sdept,Sage from student where Sage not between 20 and 23; 12)查询计算机科学系(CS)、数学系(MA)、和信息系(IS)学生的姓名和性别。 select Sname,Sdept,Sage from student where Sdept in ('CS','MA','IS'); 13)查询既不是计算机科学系、数学系，也不是信息系的学生的姓名和性别。 select Sname,Sdept,Sage from student where Sdept not in ('CS','MA','IS'); 14)查询学号为的学生的详细情况。 select * from student where Sno like '' 15)查询所有姓刘的学生的姓名、学号和性别。 select Sname,Sno,Ssex from student where Sname like '刘%' 16)查询姓“欧阳”且全名为3个汉字的学生的姓名。 select Sname from student where Sname like '欧阳_' 17)查询名字中第2个字为“阳”字的学生的姓名和学号。 select Sname,Sno from student where Sname like '_阳%' 18) 查询所有不姓刘的学生姓名。 select Sname,Sno,Ssex from student where Sname not like '刘%' 19)查询DB_Design课程的课程号和学分。 select Cno,Ccredit from course where Cname like 'DB_Design' escape '' 20)查询以“DB_”开头，且倒数第3个字符为i的课程的详细情况。 select * from course where Cname like 'DB_%i_' escape '' 21)某些学生选修课程后没有参加考试，所以有选课记录，但没有考试成绩。查询缺少成绩的学生的学号和相应的课程号。 select Sno,Cno from sc where Grade is null; 22)查询所有有成绩的学生学号和课程号。 select Sno,Cno from sc where Grade is not null; 23)查询计算机科学系年龄在20岁以下的学生姓名。 select Sname from student where Sdept='CS' and Sage<20; select Sname,Ssex from student where Sdept='CS' or Sdept='MA' or Sdept='IS' 24)查询选修了3号课程的学生的学号及其成绩，查询结果按分数的降序排列。 select Sno,Grade from sc where Cno='3' order by Grade desc; 25)查询全体学生情况，查询结果按所在系的系号升序排列，同一系中的学生按年龄降序排列。 select * from student order by Sdept,Sage desc; 26)查询学生总人数。 select count(*) from student; 27）查询选修了课程的学生人数。 select count(distinct Sno) from sc; 28)计算1号课程的学生平均成绩。 select avg(Grade) from sc where Cno='1' 29)查询选修1号课程的学生最高分数。 select max(Grade) from sc where Cno='1' 30)查询学生选修课程的总学分数。 select sum(Ccredit) from sc,course where sc.Cno=course.Cno and Sno='' 31)求各个课程号及相应的选课人数。 select Cno,count(Sno) from sc group by Cno; 32)查询选修了3门以上课程的学生学号。 select Sno from sc group by Sno having count(*)>3; 2连接查询1)查询每个学生及其选修课程的情况。 select student.*,sc.* from student,sc where student.Sno=sc.Sno; 2)对上个题用自然连接完成。 select student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade from student,sc where student.Sno=sc.Sno; 3)查询每一门课的间接先修课(即先修课的先修课)。 select first.Cno,second.Cpno from course first,course second where first.Cpno=second.Cno; 4) 查询每个学生及其选修课程的情况，用外连接来完成。 select student.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade from student left outer join sc on(student.Sno=sc.Sno); 5)查询选修2号课程且成绩在90分以上的所有学生。 select student.Sno,Sname from student,sc where student.Sno=sc.Sno and sc.Cno='2' and sc.Grade>90; 6)查询每个学生的学号、姓名、选修的课程名及成绩。 select student.Sno,Sname,Cname,Grade from student,sc,course where student.Sno=sc.Sno and sc.Cno=course.Cno; v3嵌套查询1)查询与“刘晨”在同一个系学习的学生。 select Sno,Sname,Sdept from student where Sdept in(select Sdept from student where Sname='刘晨'); select s1.Sno,s1.Sname,s1.Sdept from student s1,student s2 where s1.Sdept=s2.Sdept and s2.Sname='刘晨' 2)查询选修了课程名为“信息系统”的学生学号和姓名。 select Sno,Sname from student where Sno in(select Sno from sc where Cno in(select Cno from course where Cname='信息系统'); 3)找出每个学生超过他选修课程平均成绩的课程号。 select Sno,Cno from sc x where Grade>=(select avg(Grade) from sc y where y.Sno=x.Sno); 4)查询其他系中比计算机科学系某一学生年龄小的学生姓名和年龄。 select Sname,Sage from student where Sage<any(select Sage from student where Sdept='CS') and Sdept<>'CS' 5) 查询其他系中比计算机科学系所有学生年龄小的学生姓名和年龄。 select Sname,Sage from student where Sage<all(select Sage from student where Sdept='CS') and Sdept<>'CS' 6)查询选修了1号课程的学生姓名。 select Sname from student where exists(select * from sc where Sno=student.Sno and Cno='1'); 7) 查询没有选修1号课程的学生姓名。 select Sname from student where not exists(select * from sc where Sno=student.Sno and Cno='1'); 8)查询选修了全部课程的学生姓名。 select Sname from student where not exists (select * from Course where not exists (select * from sc where Sno=student.Sno and Cno=course.Cno); 9)查询至少选修了学生选修的全部课程的学生号码。 select distinct Sno from sc scx where not exists (select * from sc scy where scy.Sno='' and not exists (select * from sc scz where scz.Sno=scx.Sno and scz.Cno=scx.Cno); 4集合查询1)查询计算机科学系的学生及年龄不大于19岁的学生。 select * from student where Sdept='CS' union select * from student where Sage<=19; 2)查询选修了课程1或课程2的学生。 select Sno from sc where Cno='1' union select Sno from sc where Cno='2' 3) 查询计算机科学系的学生与年龄不大于19岁的学生的交集。 select * from student where Sdept='CS' intersect select * from student where Sage<=19;4) 查询既选修了课程1又选修了课程2的学生。就是查询选修课程1的学生集合与选修课程2的学生集合的交集。 select Sno from sc where Cno='1' intersect select Sno from sc where Cno='2'5) 查询计算机科学系的学生与年龄不大于19岁的学生的差集。 select * from student where Sdept='CS' except select * from student where Sage<=19三、视图操作1建立视图1)建立信息系学生的视图。 create view IS_Student as select Sno,Sname,Sage from student where Sdept='IS'2) 建立信息系学生的视图,并要求进行修改和插入操作时仍需保证该视图只有信息系的学生。create view IS_Student as select Sno,Sname,Sage from student where Sdept='IS' with check option;3)建立信息系选修了1号课程的学生的视图。 create view IS_S1(Sno,Sname,Grade) as select student.Sno,Sname,Grade from student,sc where Sdept='IS' and student.Sno=sc.Sno and sc.Cno='1'4) 建立信息系选修了1号课程且成绩在90分以上的学生的视图。create view IS_S2(Sno,Sname,Grade) as select Sno,Sname,Grade from IS_S1 where Grade>=90;5)定义一个反应学生出生年份的视图。 create view BT_S(Sno,Sname,Sbirth) as select Sno,Sname,2004-Sage from student;6)将学生的学号及他的平均成绩定义为一个视图。 create view S_G(Sno,Gavg) as select Sno,avg(Grade) from sc group by Sno;7)将Student表中所有女生记录定义为一个视图。 create view F_Student(F_sno,name,sex,age,dept) as select * from student where Ssex='女'2删除视图：1)删除视图BT_S: drop view BT_S;2) 删除视图BT_S: drop view BT_S;3查询视图：1)在信息系学生的视图中找出年龄小于20岁的学生。 select Sno,Sage from IS_Student where Sage<20;2)查询选修了1号课程的信息系学生。 select IS_Student.Sno,Sname from IS_Student,sc where IS_Student.Sno=sc.Sno and sc.Cno='1'3)在S_G视图中查询平均成绩在90分以上的学生学号和平均成绩。 select * from S_G where Gavg>=90;4更新视图：1)将信息系学生视图IS_Student中学号为的学生姓名改为“刘辰”。 update IS_Student set Sname='刘辰' where Sno=''2)向信息系学生视图IS_Student中插入一个新的学生记录，其中学号为，姓名为赵新，年龄为20岁。 insert into IS_Student values('','赵新',20);3)删除信息系学生视图IS_Student中学号为的记录。 delete from IS_Student where Sno=''实验三 数据库完整性（2小时）1、实验目的（1）了解SQL Serer数据库系统中数据完整性控制的基本方法（2）熟练掌握常用CREATE 或ALTER 在创建或修改表时设置约束（3）了解触发器的机制和使用（4）验证数据库系统数据完整性控制2、实验平台使用SQL Server数据库管理系统提供的SSMS和查询编辑器。3 实验内容及要求结合ST数据库中的各个表，设置相关的约束，要求包括主键约束、外键约束、唯一约束、检查约束、非空约束等，掌握各约束的定义方法。设置一个触发器，实现学生选课总学分的完整性控制，了解触发器的工作机制。设计一些示例数据，验证完整性检查机制。要求包括如下方面的内容：3.1 使用SQL语句设置约束使用CREATE或ALTER语句完成如下的操作，包括：1 设置各表的主键约束2 设置相关表的外键3. 设置相关属性的非空约束、默认值约束、唯一约束4. 设置相关属性的CHECK约束3.2 使用触发器创建一个触发器，实现如下的完整性约束：.王玉民程明王林总学分姓名学号709080成绩310642063106学分课程号学号SCStudentl当向SC表中插入一行数据时，自动将学分累加到总学分中。l记录修改学分的操作。3.4 检查约束和触发器 分别向相关表插入若干条记录，检查你设置的完整性约束是否有效：1 插入若干条包含正确数据的记录，检查插入情况2 分别针对设置的各个约束，插入违反约束的数据，检查操作能否进行3 向SC表插入若干行数据，检查触发器能否实现其数据一致性功能。 实验报告要求写出实验的基本过程。解释操作过程中出现的现象。 参考示例：建立一个学生选课数据库，熟悉约束及触发器的使用方法。一、声明完整性约束创建学生选课数据库TEST，包括三个基本表，其中Student表保存学生基本信息，Course表保存课程信息，SC表保存学生选课信息，其结构如下表：表1. Student表结构列名称用途类型长度约束备注Sno学号字符8主键Sname姓名字符8非空Ssex性别字符2男，女Sage年龄整型Sdept所在系字符20Sclass班级字符4非空Stotal总学分整型默认值0表2. Course表结构列名称用途类型长度约束备注Cno课程号字符4主键Cname课程名字符40非空，唯一Cpno先修课程号 字符4外键引用本表的CnoCcredit学分整型表3. SC表结构列名称用途类型长度约束备注Sno学号字符8主码属性，外键引用Student的SnoCno课程号字符4主码属性，外键引用Course的CnoGrade成绩整型0<=成绩<=1001创建基本表及约束（1）创建Student表CREATE TABLE Student ( Sno CHAR(8) PRIMARY KEY, Sname CHAR(8) NOT NULL, Ssex CHAR(2) CHECK( Ssex in ('男','女'), Sage SMALLINT, Sdept CHAR(20), Sclass CHAR(4) NOT NULL, Stotal smallint DEFAULT 0 );l掌握主键约束、非空约束、CHECK约束、默认值的定义格式。（2）创建Course表CREATE TABLE Course( Cno CHAR(4) CONSTRAINT FK_Course PRIMARY KEY, Cname CHAR(40), Cpno CHAR(4) , Ccredit SMALLINT);（3）创建SC表CREATE TABLE SC ( Sno CHAR(8) FOREIGN KEY (Sno) REFERENCES Student(Sno), Cno CHAR(4), Grade SMALLINT CONSTRAINT SC_CHECK CHECK(Grade >0 AND Grade<100), PRIMARY KEY (Sno,Cno), CONSTRAINT FK_SC FOREIGN KEY (Cno) REFERENCES Course(Cno);l掌握多个属性的主键约束、外键约束、CHECK约束的定义格式。l掌握约束的命名方式。（4）检查表是否创建成功。2 插入数据将如下表格中的数据分别插入到数据库相应的表中：表4.学生基本信息表学号姓名性别年龄所在系班级总学分李勇男20CS10010刘晨女19CS10010王敏女18MA100