数据库第二次作业

数据库第二次作业-标准化文件发布号：（9456-EUATWK-MWUB-WUNN-INNUL-DDQTY-KII

《数据库第二次作业——上机实验题》

实验一：交互式S Q L的使用

实验要求：

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_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）查询计算机系秋季所开课程的课程号和学分数；

（3）答：

（4）select CNO,CREDIT from Courses where SEMESTER=’秋季’

（5）查询选修计算机系秋季所开课程的男生的姓名、课程号、学分数、成绩；

（6）答：

（7）select s.SNAME,https://www.360docs.net/doc/ef803839.html,O,c.CREDIT,SC.GRADE from students s

（8）inner join SC on sc.SNO=s.SNO

（9）inner jpin Courses c on https://www.360docs.net/doc/ef803839.html,O=c,CNO

（10）where s.DEPARTMENT=’计算机系’and s.SEX=’男’and c.SEMESTER=’秋季’

（11）查询至少选修一门电机系课程的女生的姓名（假设电机系课程的课程号以EE开头）；（12）答：

（13）select distinct s.sname from Students s,sc where s.sno=sc.sno and s.sex=’女’and https://www.360docs.net/doc/ef803839.html,o like ’EE%’（14）查询每位学生已选修课程的门数和总平均成绩；

（15）答：

（16）select count(https://www.360docs.net/doc/ef803839.html,O) as 课程门数,avg(SC.GRADE) as 总平均成绩from students s

（17）inner join SC on sc.SNO=s.SNO

（18）inner join Courses c on https://www.360docs.net/doc/ef803839.html,O=https://www.360docs.net/doc/ef803839.html,O

（19）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 chane;

（7）查询所有课程的成绩都在80分以上的学生的姓名、学号、且按学号升序排列；

答：

select sname,sno from students natural join(select sno.min(grade)as mini from sc group by sno)where mini>80order by(sno);

（8）查询缺成绩的学生的姓名，缺成绩的课程号及其学分数；

答：

select sname,https://www.360docs.net/doc/ef803839.html,o,credit

from students,courses,sc

where students.sno=sc.sno and https://www.360docs.net/doc/ef803839.html,o=https://www.360docs.net/doc/ef803839.html,o and grade is null;

（9）查询有一门以上(含一门)三个学分以上课程的成绩低于70分的学生的姓名；

（10）答：

（11）select sname from students,courses,sc

（12）where students.sno=sc.sno and https://www.360docs.net/doc/ef803839.html,o=sc.cho and credit>=3 and grade<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的学分数增为３，讲课时数增为６０

（14）答：

（15）update courses set credit=3 where cno=’CS-211’

（16）update courses set 1hour=60 where cno=’CS-211’

3．补充题：

(1) 统计各系的男生和女生的人数。

答:

select department,

sum(case wgen sex=’男’then 1 else 0 end),

sum(case wgen sex=’女’then 1 else 0 end),

count(sno) from students group by department order by deparment;

(2) 列出学习过‘编译原理’，‘数据库’或‘体系结构’课程，且这些课程的成绩之一在90分以上的学生的名字。

答：

select snme

from students natural join sc natural join courses

where cname=’编辑原理’or cname=’体系结构’ and grade>90

(3) 列出未修选‘电子技术’课程，但选修了‘数字电路’或‘数字逻辑’课程的学生数。

答：

select count(distinci sc.sno) from courses,sc.sno not in (select sc.sno from https://www.360docs.net/doc/ef803839.html,o=https://www.360docs.net/doc/ef803839.html,o and cname=’电子技术’)a nd https://www.360docs.net/doc/ef803839.html,o in (select cno from courses where cname=’数学逻辑’or cname=’数字电路’)

(4) 按课程排序列出所有学生的成绩，尚无学生选修的课程，也需要列出，相关的学生成绩用NULL 表示。

答：

select distinct https://www.360docs.net/doc/ef803839.html,o,https://www.360docs.net/doc/ef803839.html,ame,sno,grade from courses left join sc on (https://www.360docs.net/doc/ef803839.html,o) group by https://www.360docs.net/doc/ef803839.html,o,https://www.360docs.net/doc/ef803839.html,ame,sno,grade

group by https://www.360docs.net/doc/ef803839.html,o,https://www.360docs.net/doc/ef803839.html,ame,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.sno order by r desc)

where rownum=1;

4．选做题：对每门课增加“先修课程”的属性，用来表示某一门课程的先修课程，每门课程应可记录多于一门的先修课程。要求：

1) 修改表结构的定义，应尽量避免数据冗余，建立必要的主键，外键。

2) 设计并插入必要的测试数据，完成以下查询：

列出有资格选修数据库课程的所有学生。(该学生已经选修过数据库课程的所有先修课，并达到合格成绩。)

注意：须设计每个查询的测试数据，并在查询之前用INSERT语句插入表中。

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

实验要求：

1．采用实验一的建库脚本和数据插入脚本创建Student数据库,并完成以下操作：1）新增表Credits(SNO,SumCredit,NoPass)，表示每学生已通过选修课程的合计学分数，以及不及格的课程数。

答：

create table Credits(SNO varchar(100),SumCredit int,NoPass int)

2）创建视图Student_Grade(Sname,Cname,Grade)，表示学生选修课程及成绩的详细信息。

答：

create view Student_Grade

as

select s.SNAME,https://www.360docs.net/doc/ef803839.html,AME,SC.GRADE from students s

inner join SC on sc.SNO=s.SNO

inner join Courses c on https://www.360docs.net/doc/ef803839.html,O=https://www.360docs.net/doc/ef803839.html,O

2.在数据库中创建以下触发器：

1）Upd_Credit

要求：当在SC表中插入一条选课成绩，自动触发Upd_Credit，完成在Credits表中修改该学生的合计学分数和不及格的课程数。

答:

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

update Credits

set SumCredit=SumCredit+@CREDIT,

NoPass=NoPass+@NoPass

where SNO=@SNO

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表中存在，如不存在，不执行任何操作，并提示用户错误信息。

答:

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=@SANME),

(SELECT TOP 1 CNO FROM Courses WHERE CNAME=@CNAME),

@GRADE

END

3）PK_SC,FK_SC_SNO,FK_SC_CNO)（选做）

要求：首先删除SC中所有主键和外键定义，用触发器实现表SC上的主键（SNO，CNO）和外键SNO，CNO的约束定义。

3．为Student数据库设计安全机制。

要求：在该数据库系统中，有三类用户：

1)学生，权限包括：查询所有的课程信息，根据学号和课程号来查询成绩。但不允许修改任何

数据。(必做)

只能查询自己的成绩，不能查询别人的成绩。（选做）

2）老师：权限包括：查询有关学生及成绩的所有信息，有关课程的所有信息，但不允许修改任何数据。

答:

CREATE TRIGGRT secure_student

BEFORE UPDATE OR DELETE ON database

BEGIN

IF((select user from dual)=’老师’)

THEN

RAISE_APPLICATION_ERROE(-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,

‘您没有权限进行修改。’)

要求：安全控制必须仅由数据库一端来实现，不考虑由应用程序来控制。

为此，需要创建三个用户，登录时密码验证；分别授予各类权限，并测试权限的控制是否有效。

实验三：S Q L编程