辽东学院自编教材
《数据库原理》实验指导书
鲁琴李荣霞编
（信息管理与信息系统专业用）
信息技术学院
2011年1月
目录
实验一SQL SERVER环境、数据定义与数据更新 (1)
实验二数据查询 (6)
实验三数据安全性 (10)
课程设计 (12)
实验一SQL Server环境、数据定义与数据更新
实验类型：验证实验课时： 2 指导教师：
时间：20 年月日课次：第节教学周次：第周
实验分室：实验台号：实验员：
一、实验目的
1.熟悉SQL Server 2000开发环境，了解各常用工具的使用。
2.掌握数据库服务器的本机注册，熟悉网络中其他数据库服务器的注册。
3.掌握数据库的创建、删除。
4.熟练掌握命令方式下各种难度表的创建。
5.掌握命令方式下不同类型索引的创建与删除。
6.熟练掌握命令方式下对表中数据的增、删、改。
二、实验内容及要求
要将所有实验内容写在“实验结果”，并在本实验完成时完成实验报告并上交。
1.创建数据库
要求：
（1）创建的数据库名为你的姓名。
（2）在默认路径下建库，能指出这个默认的路径。
（3）会在某一盘符下以你的班级姓名学号命名的文件夹中创建数据库。
2.定义基本表
要求：
（1）在建好的数据库中用命令方式创建三个关系，分别为学生关系student、课程关系course、选课关系sc。
（2）学生关系的关系模式为：student(sid,sname,ssex,sage, sdept)，各字段分别代表学号、姓名、性别、年龄、所在系。sid设为主键。
（3）课程关系的关系模式为：course(cid,cname,credit)，各字段分别代表课程号、课程名、学分。cid设为主键。
（4）选课关系的关系模式为：sc(sid,cid,score)，各字段分别代表学号、课程号、成绩。cid 和sid为复和主键，sid和cid为外键。
（5）各字段数据类型要设置恰当，并对ssex、score设置合理的约束。
（6）将建表命令存成脚本文件，可存为一个文件也可存为独立的多个脚本文件。
3.修改基本表
要求：
（1）向student表增加“入学时间”列，其数据类型为日期型。
（2）将course表中的cname列加宽到60个字符。
（3）*在student 表中增加完整性约束定义，使sage取值在15－40之间。
（4）*增加课程名称必须取唯一值的约束条件。
（5）删除student表中“入学时间”列。
4.删除基本表
要求：
*删除sc表。
5.索引的建立、删除
要求：
（1）为表sc按学号升序和课程号降序建唯一索引sci。
（2）*在实例数据库pubs中，为表authors在au_id上建立聚集索引。
（3）删除表sc的索引sci。
6.插入数据
要求：
在建好的3个表student,course,sc中各插入2条记录，要求内容如下。用命令方式写出相关命令(注意顺序)：
Sc
7.修改数据
要求：
（1）将“郑秀丽”的系改为“国际贸易”系。
（2）将所有学生的成绩提高到原来的1.1倍。
（3）将所有学生的年龄加1岁。
（4）将“国际贸易”系所有学生的成绩置零。
8.删除数据
要求：
（1）删除学号为“0009002”的学生记录。
（2）删除所有学生选课记录。
（3）删除计算机科学系所有学生的选课记录。
三、实验结果
--创建基本表
--建立学生表
create table Student(
sid char(7)not null primary key,
sname char(5),
ssex char(1),
sage int,
sdept char(20));
--建立课程表
create table Course(
cid char(4)not null primary key,
cname char(20),
credit float,);
--成绩表
create table Sc(
sid char(7),
cid char(4),
score int,
primary key(sid,cid),
foreign key(sid)references Student(sid),
foreign key(cid)references Course(cid));
--修改Course的cid长度，失败
alter table Course alter column cid char(4);
--删除course表
drop table Course;
--3.修改基本表
--向student表增加“入学时间”列，其数据类型为日期型
alter table Student add入学时间date;
--将course表中的cname列加宽到个字符
alter table Course alter column cname varchar(60); --*在student 表中增加完整性约束定义，使sage取值在－之间alter table Student
--*增加课程名称必须取唯一值的约束条件
--删除student表中“入学时间”列
alter table Student drop column入学时间;
--5.索引的建立,删除
--为表sc按学号升序和课程号降序建唯一索引sci
create unique index sci on Sc(sid asc,cid desc);
--*在实例数据库pubs中，为表authors在au_id上建立聚集索引
--删除表sc的索引sci
drop index Sc.sci;
--6.插入数据
/*在建好的个表student,course,sc中各插入条记录
Course
cid cname credit
C801 高等数学3
C802 C++语言4
Sc
sid cid score
0009001 C801 54
0009002 C802 70
student
sid sname ssex sage sdept
0009001 葛文卿女22 国际贸易
0009002 郑秀丽男21
*/
insert into Course(cid,cname,credit)values('C801','高等数学',3);
insert into Course values('c802','c++语言',4);
insert into Student(sid,sname,ssex,sage,sdept)values('0009001','葛文卿','女',22,'国际贸易');
insert into Student(sid,sname,ssex,sage,sdept)values('0009002','郑秀丽','男',21);
--7.修改数据
--（）将“郑秀丽”的系改为“国际贸易”系es
update Student set sdept='国际贸易'where sname='郑秀丽';
--（）将所有学生的成绩提高到原来的.1倍
update Sc set score=score*1.1;
--（）将所有学生的年龄加岁
update Student set sage=sage+1;
--（）将“国际贸易”系所有学生的成绩置零
update Sc set score=0 where sid in(select sid from Student where sdept='国际贸易');
--8.删除数据
--（1）删除学号为“0009002”的学生记录
DELECT FROM Student WHERE sid=’0009002’;
--（2）删除所有学生选课记录
Delect from Sc;
--（3）删除计算机科学系所有学生的选课记录
Delect from Sc where (select cid from Course where cname=’计算机科学’);
四、实验中遇到的问题及解决方法
实验二数据查询
实验类型：设计实验课时： 2 指导教师：
时间：20 年月日课次：第节教学周次：第周实验分室：实验台号：实验员：
一、实验目的
1.掌握查询的基本语法格式。
2.熟练掌握单表查询。
3.掌握连接查询。
4.熟悉嵌套查询。
二、实验内容及要求
要求将所有实验内容写在“实验结果”，并在本实验完成时完成实验报告并上交。
对实验一所创建的数据库中的三个表：student、course和sc，用SQL完成下面的操作。
1.简单查询
（1）查询全体学生的学号与姓名。
（2）*查询全体学生的姓名及其出生年份。
（3）*查询选修了课程的学生学号。
（4）查询平均成绩大于60分的课程号和平均成绩。
（5）查询考试成绩有不及格的学生的学号。
（6）查询年龄不在20－25岁之间的学生学号、姓名和年龄。
（7）*查询计算机系、国际贸易系、数学系学生的姓名和性别。
（8）*查询有多少学生专业不确定。
（9）*查询所有姓刘的学生的姓名、学号和性别。
（10）查询名字中第2个字为“阳”字的学生的学号、姓名。
（11）查询所有不姓张的学生姓名。
（12）*查询选修课程号为“c801”或“c802”且分数不低于90分的学生的学号、课程号和成绩。
（13）*查询没有考试成绩的学生的学号和课程号。
（14）*查询选修课程号为“c801”的学生的学号及成绩，按成绩降序排列。
（15）查询选修了课程的学生人数。
（16）查询各个课程号及相应的选课人数。
（17）查询选修了3门以上课程的学生学号。
2.复杂检索
（1）*查询单科成绩不及格的学生的学号、姓名、课程名、系别及成绩。
（2）*查询选修了2门以上课程的学生学号。
（3）*查询既不是外语系、数学系，也不是财务会计系的学生的姓名和系别。
（4）查询每个学生及其选修课程的情况，要求结果当中包含学号、姓名、性别、所在系、课名、成绩。
（5）*查询选修了2号课程且成绩在90分以上的所有学生的学号、姓名。
（6）*查询与“张三”在同一个系学习的学生的学号、姓名、系别。
（7）查询选修了课程名为“信息系统”的学生学号和姓名。
（8）*查询每个学生超过他选修课程平均成绩的课程号。
（9）*查询其它系中比计算机科学系所有学生年龄都小的学生姓名及年龄。
（10）*查询所有选修了1号课程的学生姓名。
三、实验结果
--实验二数据查询
--1.简单查询
--（）查询全体学生的学号与姓名。
select sid,sname from Student;
--（）*查询全体学生的姓名及其出生年份。
select sname,DATENAME(YEAR,GETDATE())-sage from Student;
select YEAR(GETDATE()from student;--获取当前年份
--（）*查询选修了课程的学生学号。
select sid from Sc where cid is not null;
--（）查询平均成绩大于分的课程号和平均成绩。
select AVG(score),cid from Sc where AVG(score)>60;
--（）查询考试成绩有不及格的学生的学号。
select sid from Sc where score<60;
--（）查询年龄不在－岁之间的学生学号、姓名和年龄。
select sid,sname,sage from Student where sage between 20 and 25;
--（）*查询计算机系、国际贸易系、数学系学生的姓名和性别。
select sname,ssex from Student where sdept in('计算机','国际贸易','数学');
--（）*查询有多少学生专业不确定。
select COUNT(*)from Student where sdept is null;
--（）*查询所有姓刘的学生的姓名、学号和性别。
select sname,sid,ssex from Student where sname like'刘%';
--（）查询名字中第个字为“阳”字的学生的学号、姓名。
select sid,sname from Student where sname like'_阳%';
--（）查询所有不姓张的学生姓名。
select sname from Student where sname not like'张%';
--（）*查询选修课程号为“c801”或“c802”且分数不低于分的学生的学号、课程号和成绩。
select student.sid,cid,score from Student,Sc where Student.sid=sc.sid and cid='c801,c802'and score>=90;
select sid,cid,score from Sc where cid='c801,c802'and score>=90;
--（）*查询没有考试成绩的学生的学号和课程号。
select sid,cid from Sc where score is null;
--（）*查询选修课程号为“c801”的学生的学号及成绩，按成绩降序排列。
select sid,score from Sc where cid='c801'order by score desc;
--（）查询选修了课程的学生人数。
select COUNT(*)from Sc where cid is not null;
--（）查询各个课程号及相应的选课人数。
select cid,COUNT(sid)from Sc group by cid;
--（）查询选修了门以上课程的学生学号。
select sid from Sc group by sid having COUNT(cid)>3;
--2.复杂检索
--（）*查询单科成绩不及格的学生的学号、姓名、课程名、系别及成绩。
select sc.sid,sname,cname,sdept,score from Student,Sc,Course where Student.sid=sc.sid and
Course.cid=sc.cid and score<60;
--（）*查询选修了门以上课程的学生学号。
select sid from Sc group by sid having COUNT(*)>=2;
--（）*查询既不是外语系、数学系，也不是财务会计系的学生的姓名和系别。
select sname,sdept from Student where sdept not in('外语','数学','财务会计');
--（）查询每个学生及其选修课程的情况，要求结果当中包含学号、姓名、性别、所在系、课名、成绩。
select student.sid,sname,ssex,sdept,cname,score from Student,Sc,Course where Student.sid=sc.sid and sc.cid=Course.cid;
--（）*查询选修了c802号课程且成绩在分以上的所有学生的学号、姓名。
select student.sid,sname from Sc,Student where student.sid=sc.sid and score>=90;
--（）*查询与“张三”在同一个系学习的学生的学号、姓名、系别。
select sid,sname,sdept from Student where sdept in(select sdept from Student where sname='张三');
--（）查询选修了课程名为“信息系统”的学生学号和姓名。
select student.sid,sname from Student,Course,Sc where student.sid=sc.sid and
sc.cid=Course.cid and cname='信息系统';
--（）*查询每个学生超过他选修课程平均成绩的课程号。
select cid from Sc group by cid,score having score>AVG(score);
--（）*查询其它系中比计算机科学系所有学生年龄都小的学生姓名及年龄。
select sname,sage from Student group by sname,sage,sdept having sdept!='计算机';
--（）*查询所有选修了（号）课程名为的学生姓名。
select sname from Course,Student,Sc where student.sid=sc.sid and sc.cid=Course.cid and cname='1';
四、实验中遇到的问题及解决方法
实验三数据安全性
实验类型：验证实验课时： 2 指导教师：
时间：20 年月日课次：第节教学周次：第周
实验分室：实验台号：实验员：
一、实验目的
1.掌握视图的定义以及与基表的区别。
2.掌握视图的建立、删除、查询、更新。
3.熟悉用户的创建。
4.掌握用户权限的授予。
5.掌握用户权限的收回。
6.熟悉数据库角色。
二、实验内容及要求
要求将所有实验内容写在“实验结果”，并在本实验完成时完成实验报告并上交。
1.建立信息系学生情况视图Info_student，通过该视图可查询学生的学号、姓名、年龄。
2.建立信息系选修了1号课程的学生视图Info_student1，通过该视图可查询学生的学号、姓名、成绩。
3.在Info_student1的基础上，建立信息系选修了1号课程且成绩在90分以上的学生视图。
4.建立视图，将其定义为学生的学号及他的平均成绩。
5.建一视图myview，通过该视图可查询某个学生某门课的成绩，要求内容有学号、姓名、课程名、成绩。
6.删除视图Info_student1。
7.查询信息系学生视图Info_student中年龄小于20岁的学生。
8.将信息系学生视图Info_student中学号为1的学生姓名改为“马丽”。
9.向信息系学生视图Info_student中插入一个新的学生记录“20081000，刘一，19”。
10.删除信息系学生视图Info_student中学号为“20081000”的记录。
11.利用可视化方式在企业管理器下完成如下操作题：
（1）新建登录账号aa和bb。
（2）将aa转换成pubs库下的用户，并授予对authors表的select、 insert、 update、delete权限。
（3）新建角色my_role，为其授予对pubs库下authors表的select、 insert、 update、delete权限，通过将用户添加到角色的方法，使bb用户自动具有my_role拥有的权限。
12.用命令方式完成权限的授予与收回：
（1）授予用户aa查询student表的权限。
（2）把查询student表和修改学生学号的权限授给用户bb。
（3）收回用户aa和bb查询student表的权限。
（4）收回用户bb修改学生学号的权限。
三、实验结果
--实验三数据安全性
--1. 建立信息系学生情况视图Info_student，通过该视图可查询学生的学号、姓名、年龄。
create view Info_student(sid,sname,sage)as select sid,sname,sage from Student;
--2. 建立信息系选修了号课程的学生视图Info_student1，通过该视图可查询学生的学号、姓名、成绩。
create view Info_student1(sid,sname,scoure)as select
student.sid,sname,score from Student,Sc
where student.sid=sc.sid and student.sdept='信息'and cid='c801';
--3. 在Info_student1的基础上，建立信息系选修了号课程且成绩在分以上的学生视图。
create view Info_student2as select*from Info_student1where scoure>90; --4. 建立视图，将其定义为学生的学号及他的平均成绩。
create view Info_student3(sid,avgscore)as select sid,AVG(score)from Sc group by sid;
--5. 建一视图myview，通过该视图可查询某个学生某门课的成绩，要求内容有学号、姓名、课程名、成绩。
create view myview(sid,sname,cname,score)as select
student.sid,sname,cname,score from Student,Sc,Course
where student.sid=sc.sid and sc.cid=Course.cid;
--6. 删除视图Info_student1。
drop view Info_student1;
--7. 查询信息系学生视图Info_student中年龄小于岁的学生。
select*from Info_student where sage<20;
--8. 将信息系学生视图Info_student中学号为的学生姓名改为“马丽”。
update Info_student set sname='马丽'where sid='1';
--9. 向信息系学生视图Info_student中插入一个新的学生记录“，刘一，”。
insert Info_student values('20081000','刘一',19);
--10. 删除信息系学生视图Info_student中学号为“”的记录。
delete from Info_student where sid='20081000';
--/*11. 利用可视化方式在企业管理器下完成如下操作题：
--（）新建登录账号aa和bb。
--（）将aa转换成pubs库下的用户，并授予对authors表的select、insert、update、delete 权限。
--（）新建角色my_role，为其授予对pubs库下authors表的select、insert、update、delete 权限，
--通过将用户添加到角色的方法，使bb用户自动具有my_role拥有的权限。*/
--12. 用命令方式完成权限的授予与收回：
--（）授予用户aa查询student表的权限。
grant select to aa;
--（）把查询student表和修改学生学号的权限授给用户bb。
GRANT update(sid),SELECT ON Student to bb;
--（）收回用户aa和bb查询student表的权限。
revoke select from aa,bb;
--（）收回用户bb修改学生学号的权限。
revoke update(sid)on student from bb;
四、实验中遇到的问题及解决方法
课程设计
实验类型：综合设计实验课时：指导教师：
时间：20 年月日课次：第节教学周次：第周实验分室：实验台号：实验员：
一、实验目的
1. 学习对应用系统的需求分析
2. 学习数据库设计
3. 数据库设计在SQL Server中的实现
二、实验内容及要求
1．学生可查阅相关资料，结合数据库原理课程的内容，自拟应用系统数据库设计题目。
可供参考的题目：
《XX学生管理信息系统》数据库设计
《XX教学排课管理系统》数据库设计
《XX工厂生产管理系统》数据库设计
《XX网络教学管理系统》数据库设计
《XX图书管理系统》数据库设计
《XX超市仓库管理系统》数据库设计
《XX教学科研管理系统》数据库设计
《XX在线考试系统》数据库设计
《XX人事管理系统》数据库设计
要求：题目要结合具体的应用（如《数据库原理课程在线考试系统》），以便进行下一步具体的数据库分析、设计与实现。
2．系统需求分析
3．数据库设计
要求：
（1）画出系统E—R图。
（2）将E—R图转化为关系模式，写出各关系模式并标明各自的主键和外键。
4. 数据库实现
要求：选择一种关系数据库如SQL Server，在SQL Server中实现应用系统数据库。
包括建立数据库，数据表的定义与修改，索引的建立与删除，数据的更新（包括向数据表中插入数据、修改数据、删除数据），数据的查询（包括单表查询、连接查询、嵌套查询），定义视图，查询视图，更新视图等。
5．系统实现（选做）
要求：选择一种软件开发工具实现系统。
三、实验结果
四、实验中遇到的问题及解决方法