数据库实验题.doc

(word完整版)数据库实验题
《数据库实验题》

实验一:交互式SQL的使用

实验要求：
1,创建Student数据库，包括Students，Courses,SC表，表结构如下：
Students（SNO,SNAME,SEX,BDATE,HEIGHT，DEPARTMENT)
Courses（CNO，CNAME,LHOUR,CREDIT，SEMESTER)
SC(SNO，CNO,GRADE)
（注：下划线表示主键，斜体表示外键)，并插入一定数据。
答:
create table Students 
( 
SNO varchar(100） primary key , 
SNAME varchar（100) null, 
SEX varchar（100） null, 
BDATE datetime null， 
HEIGHT decimal null, 
DEPARTMENT varchar(100) null 
） 
go

create table Courses 
( 
CNO varchar（100) primary key ， 
CNAME varchar（100） null， LHOUR int null， 
CREDIT int null， 
SEMESTER varchar（100） null 
) 
go

CREATE TABLE [dbo].[SC］（  
[SNO］ varchar(100） NOT NULL,  
［CNO］ varchar(100) NOT NULL,  
［GRADE］ ［int］ NULL， 
 CONSTRAINT [PK_SC］ PRIMARY KEY CLUSTERED  
(  
[SNO] ASC,  
［CNO] ASC 
)WITH （PAD_INDEX  = OFF， STATISTICS_NORECOMPUTE  = OFF， IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON， ALLOW_PAGE_LOCKS  = ON) ON ［PRIMARY］ 
) ON ［PRIMARY]  

GO
ALTER TABLE ［dbo］.[SC］  WITH CHECK ADD  CONSTRAINT [FK_SC_Courses］ 
FOREIGN KEY（［CNO］） 
REFERENCES [dbo］。[Courses］ (［CNO]) 
GO 
ALTER TABLE [dbo］.[SC］ CHECK CONSTRAINT [FK_SC_Courses］ 
GO 
ALTER TABLE ［dbo].[SC］  WITH CHECK ADD  CONSTRAINT [FK_SC_Students］ FOREIGN KEY([SNO]） 
REFERENCES ［dbo］.[Students] （［SNO]） 
GO 
ALTER TABLE [dbo］.[SC] CHECK CONSTRAINT ［FK_SC_Students]

2．完成如下的查询要求及更新的要求。
（1）查询身高大于1。80m的男生的学号和姓名；
答：select SNO,SNAME from Students where HEIGHT>1。8

（2） 查询计算机系秋季所开课程的课程号和学分数；
答：select CNO，CREDIT from Courses where SEMESTER=’秋季’

（3） 查询选修计算机系秋季所开课程的男生的姓名、课程号、学分数、成绩；
答：select s。SNAME，SC。CNO,c。CREDIT,SC.GRADE from students s 
inner join SC on sc.SNO=s。SNO
inner join Courses c on sc.CNO=c。CNO

（4） 查询至少选修一门电机系课程的女生的姓名(假设电机系课程的课程号以EE开头）；
答：select distinct s.sname from Students s，sc where s。sno=sc。sno and  s.sex=’女' and sc。cno like ’EE％’

（5） 查询每位学生已选修课程的门数和总平均成绩；
答:select count（c.CNO) as 课程门数,avg（SC。GRADE） as 总平均成绩from students s 
inner join SC on sc。SNO=s.SNO 
inner join Courses c on sc.CNO=c.CNO group by s。SNO

（6） 查询每门课程选课的学生人数，最高成绩，最低成绩和平均成绩;
答：SELECT CNAME，COUNT<CNO>，MAX〈GRADE>,MIN〈GRADE〉，AVG〈GRADE>
FROM STUDENTS NATURAL JOIN SC NATURAL JOIN COURSES
GROUP BY CNAME；

（7） 查询所有课程的成绩都在80分以上的学生的姓名、学号、且按学号升序排列；
答：SELECT SNAME，SNO
FROM STUDENTS NATURAL JOIN
<SELECT SNO。MIN〈GRADE> AS MINI
FROM SC
GROUP BY SNO〉
WHERE MINI>80
ORDER BY〈SNO〉;

（8） 查询缺成绩的学生的姓名，缺成绩的课程号及其学分数;
答:SELECT SNAME COURSES。CNO,CREDIT
FROM STUDENTS，COURSES,SC
WHERE STUDENTS.SNO=SC.SNO AND COURSES。CNO AND GRADE IS NULL；

（9） 查询有一门以上（含一门)三个学分以上课程的成绩低于70分的学生的姓名；
答：SELECT SNAME
FROM STUDENTS，COURSES,SC
WHERE STUDENTS。SNO=SC.SNO AND CREDIT〉=3 AND GREAD<70

（10）查询1984年~1986年出生的学生的姓名，总平均成绩及已修学分数。
答：select sname，avg（grade）,sum(credit）
from students natural join sc natural join courses
where bdate between ‘1984—00-00’ and ‘1987-00—00’
groud by sname

（11） 在STUDENT和SC关系中，删去SNO以’01’开关的所有记录。
答：delete sc where SNO like '%01%'
delete Students where SNO like '%01%'

（12）在ＳＴＵＤＥＮＴ关系中增加以下记录：
<0409101 何平　　女　　1987-03—02　　1。62>
〈0408130 向阳　　男　　1986—12-11　　1。75〉
答：insert into students values <'0409101’，’何平’,'女’,’1987—03-03’,'1.62’’〉
insert into students values 〈'0408130’,’向阳’，’女’，’1986-12—11’，’1.75’’〉

（13） 将课程CS—221的学分数增为３，讲课时数增为６０
答：update courses
set credit=3
where cno='CS-211'

update courses
Set 1hour=60
where cno=’CS—211’

3．补充题:
（1) 统计各系的男生和女生的人数。
答：SELECT
DEPARTMENT,
SUM<CASE WHEN SEX='男’ THEN 1 ELSE 0 END>，
SUM<CASE WHEN SEX=’女’ THEN 1 ELSE 0 END〉,
COUNT<SNO〉 总人数
FROM Students
GROUP BY DEPARTMENT
ORDER BY DEPARTMENT

(2) 列出学习过‘编译原理’，‘数据库'或‘体系结构’课程，且这些课程的成绩之一在90分以上的学生的名字。
答:select sname
From students natural join sc natural join courses
Where cname='编辑原理’ or cname=’体系结构' and grade〉90

(3) 列出未修选‘电子技术’课程，但选修了‘数字电路’或‘数字逻辑’课程的学生数。
答:SELECT COUNT<DISTINCT SC。SNO〉
FROM Courses,SC
WHERE SC。SNO NOT IN
〈SELECT SC.SNO
FROM Courses,SC
WHERE Courses。CNO=SC。CNO
AND CNAME=’电子技术'
AND Courses.CNO=SC.CNO
AND SC。CON IN
<SELECT CNO
FROM COURSES
WHERE CNAME=’数字逻辑’ OR CNAME=’数字电路’〉

(4) 按课程排序列出所有学生的成绩，尚无学生选修的课程，也需要列出，相关的学生成绩用NULL表示。
答：SELECT DISTINCT COURSES.CNO,COURSES.CNAME，SNO，GRADE
FROM COURSES LEFT JOIN SC ON 〈COURSES，CNO=SC.CNO〉
GROUP BY COURSES。CNO，COURSES。CNAME，SNO,GRADE
ORDER BY COURSES.CNO,COURSES.CNAME，SNO，GRADE

(5) 列出平均成绩最高的学生名字和成绩。（SELECT句中不得使用TOP n子句)
答:SELECT SNAME,r
FROM 〈
SELECT SNAME，AVG〈GRADE〉 AS
FROM STUDENTS,SC
WHERE STUDENTS。SNO=SC.SNO
GROUP BY SNAME,STUDENTS.SNC
ORDER BY r DESC>
WHERE ROWNUM=1;

4．选做题：对每门课增加“先修课程”的属性，用来表示某一门课程的先修课程，每门课程应可记录多于一门的先修课程。要求：
1） 修改表结构的定义，应尽量避免数据冗余，建立必要的主键,外键.
2) 设计并插入必要的测试数据，完成以下查询：
列出有资格选修数据库课程的所有学生.（该学生已经选修过数据库课程的所有先修课，并达到合格成绩。）
注意:须设计每个查询的测试数据，并在查询之前用INSERT语句插入表中。
答：（1）create table Credits
(SNO varchar（100）， 
SumCredit int，
NoPass int)

（2）create view Student_Grade 
as
Select s。SNAME,c。CNAME,SC.GRADE from students s 
Inner join SC onsc.SNO=s.SNO 
Inner join Courses c on sc.CNO=c。CNO

实验二:数据库的安全和完整性约束

实验要求：
1．采用实验一的建库脚本和数据插入脚本创建Student数据库，并完成以下操作:
1）新增表Credits(SNO,SumCredit，NoPass)，表示每学生已通过选修课程的合计学分数，以及不及格的课程数。
2）创建视图Student_Grade(Sname，Cname，Grade)，表示学生选修课程及成绩的详细信息.
2. 在数据库中创建以下触发器：
1） Upd_Credit
要求：当在SC表中插入一条选课成绩，自动触发Upd_Credit，完成在Credits表中修改该学生的合计学分数和不及格的课程数。
2） Upd_StuView　（Instead of触发器)
要求：当对视图Student_Grade作插入数据项操作时，自动触发Upd_StuView，完成对SC表的插入操作。
如:当执行Insert into Student_Grade values（‘王刚','数据库’，54)
则触发器完成另一插入操作:Insert into SC values(‘980201’，’CS—110’,54)
另外，需要检查当前插入的学生和课程是否已在Students,和Courses表中存在,如不存在，不执行任何操作，并提示用户错误信息.
3） PK_SC,FK_SC_SNO,FK_SC_CNO）　　（选做）
要求：首先删除SC中所有主键和外键定义，用触发器实现表SC上的主键(SNO，CNO）和外键SNO,CNO的约束定义。
答：
(1）create trigger Upd_Credit on SC for insert 
as
Declare @SNO varchar（100)，@CNO varchar(100),@GRADE  int,＠NoPass int ，＠CREDIT int 
select ＠SNO=SNO，＠CNO=CNO，@GRADE=GRADE,@NoPass=（case when GRADE〈60 then 1 else 0 end) from inserted 
select @CREDIT=CREDIT from Courses where CNO=@CNO 
update Credits   
set SumCredit=SumCredit+@CREDIT， 
NoPass=NoPass+＠NoPass 
where SNO =@SNO

(2）create trigger Upd_StuView on Student_Grade for insert
as
declare ＠SNAME varchar（100)，@CNAME varchar(100），＠GRADE int
select @SNAME=SNAME，@CNAME=CNAME，@GRADE=GRADE from inserted
IF(EXISTS(SELECT * FROM Students WHERE SNAME=@SNAME） AND
EXISTS （SELECT ＊ FROM Courses WHERE CNAME=@CNAME)）
BEGIN
Insert into SC
select （SELECT TOP 1 SNO FROM Students WHERE SNAME=@SNAME)，
(SELECT TOP 1 CNO FROM Courses WHERE CNAME=@CNAME),
@GRADE
END

3．为Student数据库设计安全机制。
要求：在该数据库系统中，有三类用户：
1) 学生，权限包括:查询所有的课程信息，根据学号和课程号来查询成绩。但不允许修改任何数据.（必做)
只能查询自己的成绩，不能查询别人的成绩。（选做）
2）老师：权限包括：查询有关学生及成绩的所有信息，有关课程的所有信息，但不允许修改任何数据。
3）教务员：权限包括：查询和修改任何有关学生和课程的信息，但不允许查询和修改数据库中其它任何表，视图等数据库对象.

要求：安全控制必须仅由数据库一端来实现，不考虑由应用程序来控制。
为此，需要创建三个用户，登录时密码验证；分别授予各类权限，并测试权限的控制是否有效。
答:1）
2)
CREATE TRIGGER secure_student
BEFORE UPDATE OR DELETE ON database
BEGIN
IF(（select user from dual）=’老师’)
THEN
RAISE_APPLICATION_ERROR（—20506,
'您没有权限对学生表进行修改.')

3）
CREATE TRIGGER secure_student   
BEFORE UPDATE OR DELETE ON database EXCEPT Students 
BEGIN   
IF(（select user from dual)=’教务员’)
THEN   
RAISE_APPLICATION_ERROR(-20506,
    ’您没有权限进行修改.’）

实验三：SQL编程
实验要求：
1．采用实验一的建库脚本和数据插入脚本创建Student数据库。
2．在数据库中创建以下存储过程：
1) Add_Student （SNO，SNAME,SEX,BIRTHDAY，HEIGHT，DEPT)
要求：根据输入参数，插入一条学生记录。
2) Upd_Grade （SNO, CNO, GRADE)
要求：根据输入参数,修改某学生选课的成绩。
3) Disp_Student (SNO，SUM_CREDIT output，AVG_GRADE output）
要求：根据SNO参数显示该学生的有关信息，包括：
a）学号,姓名，性别，年龄，身高,系别，所有选修的课程及成绩；
b）显示输出参数SUM_CREDIT(表示选修课程的总学分)及AVG_GRADE （表示3学分以上的课程的平均成绩).
4) CAL_GPA (SNO，GPA output)
要求：根据SNO参数, 输出并显示该学生的GPA值。计算方法如下：
GRADE(G) GRADEPOINT（GP）
G>=85 4
85>G>=75 3
75〉G>=60 2
60〉G 1

GPA= (∑GP*CREDIT）/ ∑CREDIT）

答：1)create procedure Add_Student 
＠SNO varchar(100）， 
@SNAME varchar（100), 
＠SEX varchar（10）, 
＠BIRTHDAY datetime, 
＠HEIGHT decimal， 
@DEPT varchar（100) 
as 
Insert into Students
values( 
＠SNO, SNO, 
@SNAME， 
＠SEX, 
@BIRTHDAY, 
＠HEIGHT， 
＠DEPT 

2）create procedure Upd_Grade 
＠SNO varchar（100）， 
＠CNO varchar(100）， 
＠GRADE INT 
as 
UPDATE SC set SNO=＠SNO， 
CNO=＠CNO, 
GRADE=＠GRADE 
where SNO=@SNO and CNO=＠CNO

3）create procedure Upd_Grade 
＠SNO varchar（100）, 
＠SUM_CREDIT INT output, 
@AVG_GRADE int output 
as 
select ＊ from Students s 
inner join SC on sc。SNO=s.SNO 
inner join Courses c on sc.CNO=c。CNO 
where s.SNO=@SNO 

select @AVG_GRADE=avg（case when SC.GRADE>=85 then 4 when 85>SC。GRADE and
SC。GRADE>=75 then 3 when 75>SC。GRADE and SC。
GRADE〉=60 then 2 when
60〉SC.GRADE then 1 end） from Students s 
inner join SC on sc。SNO=s。SNO 
where s.SNO=@SNO and SC.GRADE〉3 
group by s.SNO 

3．选做题：
使用其它程序设计语言编程，连接数据库并提交SQL语句,显示查询结果。
要求：实现上题中的第3)小题要求，设计一个图形界面来输入查询的参数SNO,及显示查询的结果。(如采用VC++,VB等）

实验四：事务的管理（选作）
实验要求：
1． 采用实验一的建库脚本和数据插入脚本创建Student数据库.
2． 测试事务隔离级别,要求:
分别设置不同的隔离级别，包括：
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET TRANSACTION ISOLATION LEVEL READ ONLY；
两个并发事务交错执行的程序,能分别显示每种隔离级别下，是否出现丢失更新，脏读，读值不可复现以及幻象四种情况.
3．备份与恢复
Ø 备份数据库
Ø 删除sc表
Ø 恢复到删除之前