万常选版数据库系统原理与设计第9章课后习题答案

USE ScoreDB GO

--9.6 在学生成绩管理数据库ScoreDB中完成。 --(1) 在班级表Class中，分别使用列级和元组级约束保证班级人数classNum属性的取值在(0,50)之间。

--为避免误删Class表的数据，另建两表演示，演示后删除 --列级约束

CREATE TABLE Class1(

classNo char(6) PRIMARY KEY, /*班级编号*/ className varchar(30) NOT NULL, /*班级名称*/ institute varchar(30) NOT NULL, /*所属学院*/ grade smallint NOT NULL /*年级*/ DEFAULT 0,

classNum tinyint NULL /*班级人数*/ CHECK(classNum >=0 AND classNum <= 50) )

--元组级约束

CREATE TABLE Class2(

classNo char(6) PRIMARY KEY, /*班级编号*/ className varchar(30) NOT NULL, /*班级名称*/ institute varchar(30) NOT NULL, /*所属学院*/ grade smallint NOT NULL /*年级*/ DEFAULT 0,

classNum tinyint NULL, /*班级人数*/

CONSTRAINT CK_Class_classNum CHECK(classNum >=0 AND classNum <= 50) )

--删除演示表

DROP TABLE Class1 DROP TABLE Class2 GO

--(2) 在学生表Student中，限制籍贯为上海或北京的学生的年龄必须在17岁以上。 --需要使用触发器

CREATE TRIGGER Student_birthday ON Student

FOR INSERT,UPDATE AS

IF EXISTS(SELECT * FROM inserted WHERE YEAR(GETDATE())-YEAR(birthday)<17) ROLLBACK GO

--(3) 对于某门课程，保证如果没有选修其先修课程，则不能选修该课程。

CREATE TRIGGER Score_courseNo ON Score FOR INSERT AS

IF NOT EXISTS(SELECT * FROM Score WHERE courseNo=(

SELECT courseNo FROM Course WHERE priorCourse=(SELECT courseNo FROM inserted))) ROLLBACK GO

--(4) 如果在学生表中修改了学号，则自动修改成绩表中的学号。 CREATE TRIGGER Student_studentNo ON Student FOR UPDATE AS IF NOT EXISTS(SELECT * FROM inserted WHERE studentNo=(SELECT studentNo FROM deleted)) UPDATE Score

SET studentNo=(SELECT studentNo FROM inserted) WHERE studentNo=(SELECT studentNo FROM deleted) GO

--(5) 使用游标编程统计每个班的学生人数，并将统计结果存入班级表的班级人数属性中。 --不使用游标的语句很简单

--UPDATE Class SET classNum=(SELECT COUNT(*) FROM Student WHERE classNo=Class.classNo) --定义变量并赋值

DECLARE @classNo char(6)='' DECLARE @classNum int=0 --定义游标

DECLARE CURSOR_Class CURSOR FOR

SELECT classNo FROM Class --使用游标

OPEN CURSOR_Class --打开游标 FETCH CURSOR_Class INTO @classNo WHILE(@@FETCH_STATUS=0) BEGIN

SELECT @classNum=( SELECT count(*) FROM Student

WHERE classNo=@classNo )

UPDATE Class SET classNum=@classNum WHERE CURRENT OF CURSOR_Class FETCH CURSOR_Class INTO @classNo --获取下一个游标值 END

CLOSE CURSOR_Class --关闭游标

DEALLOCATE CURSOR_Class --释放游标

--9.8 在学生成绩管理数据库ScoreDB中，完成如下操作： --(1) 创建5个用户user01,user02,user03,user04,user05。 --可以采用以下A,B,C,D四种方式创建数据库用户

--A. 创建数据库用户

--下面的示例首先创建名为 user01 且具有密码的服务器登录名，然后在 ScoreDB 中创建对应的数据库用户 user01。

CREATE LOGIN user01 WITH PASSWORD = '123'; USE ScoreDB;

CREATE USER user01 FOR LOGIN user01; GO

--B. 创建具有默认架构的数据库用户

--下面的示例首先创建名为 user02 且具有密码的服务器登录名，然后创建具有默认架构 db_datareader 的对应数据库用户 user02。

CREATE LOGIN user02 WITH PASSWORD = '123'; USE ScoreDB;

CREATE USER user02 FOR LOGIN user02 WITH DEFAULT_SCHEMA = db_datareader; GO

--C. 从证书创建数据库用户

--下面的示例从证书 Teacher50 创建数据库用户 user03。 USE ScoreDB;

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '123'; GO

CREATE CERTIFICATE Teacher50

WITH SUBJECT = 'Teachers', EXPIRY_DATE = '11/11/2015'; GO

CREATE USER user03 FOR CERTIFICATE Teacher50; GO

--D. 创建和使用不含登录名的用户

--以下示例创建一个数据库用户 user04，该用户不映射到 SQL Server 登录名。该示例然后向用户 user01 授予相应权限以便模拟 user04 用户。 USE ScoreDB ;

CREATE USER user04 WITHOUT LOGIN ;

GRANT IMPERSONATE ON USER::user04 TO user01 ; GO

--为了使用 user04 凭据，用户 user01 执行以下语句。 EXECUTE AS USER = 'user04' ; GO

--为了恢复到 user01 凭据，该用户执行以下语句。 REVERT ; GO

--创建和使用不含登录名的用户

CREATE USER user05 WITHOUT LOGIN; GO

--(2) 将课程表Course的所有权限授予用户 user01 和 user05，并具有转授权限的权利。 GRANT ALL ON Course TO user01, user05 WITH GRANT OPTION

--(3) 将班级表Class的查询和修改权限授予用户 user01 和 user02，不具有转授的权利，仅能对班级名称、年级和所属学院这3个属性进行操作。

GRANT SELECT,UPDATE ON Class(className,grade,institute) TO user01, user02

--(4) 用户 user01 将课程表Course的查询和删除权限授予用户user03，不具有转授的权利。

GRANT SELECT,DELETE ON Course TO user03

--(5) 用户 user02 将课程表Course的所有权限表授予用户user04，并具有转授的权利。 GRANT ALL ON Course TO user04 WITH GRANT OPTION

--(6) 用户 user04 将课程表Course的查询、删除权限授予用户 user05，并具有转授权限的权利。

GRANT SELECT,DELETE ON Course TO user05 WITH GRANT OPTION --(7) 用户 user05 将课程表Course的查询权限授予用户 user02。 GRANT SELECT ON Course TO user02

--(8) 删除用户 user05 对课程表Course的查询和删除权限。 REVOKE SELECT,DELETE ON Course FROM user05 CASCADE --(9) 删除用户 user02 的所有权限。 REVOKE ALL FROM user02

--(10) 将创建表和存储过程的权限授予用户 user02 和 user03。 GRANT CREATE TABLE,CREATE PROCEDURE TO user02, user03

--9.10 在学生成绩管理数据库ScoreDB中，编写如下的存储过程： --(1) 根据输入的班级编

号，逐行输出该班每个同学的姓名、课程名和相应成绩，并按姓名排序输出。

CREATE PROCEDURE PROC1(@cNo char(6)) AS

SELECT studentName,courseName,Score

FROM Student INNER JOIN Score ON Student.studentNo=Score.studentNo INNER JOIN Course ON Score.courseNo=Course.courseNo WHERE classNo=@cNo ORDER BY studentName GO

--EXEC PROC1 'IS0801'

--(2) 根据输入的课程编号，统计该课程的选课人数和平均分，并将统计结果返回给调用者。 CREATE PROCEDURE PROC2(@cNo char(3), @count tinyint OUTPUT, @avg numeric(5,1) OUTPUT) AS

SELECT @count=COUNT(*), @avg=AVG(score) FROM Score

WHERE courseNo=@cNo GO

--DECLARE @count tinyint, @avg numeric(5,1)

--EXEC PROC2 '006', @count OUTPUT, @avg OUTPUT --SELECT @count, @avg

--(3) 不允许使用聚集函数，统计每个学院的选课学生人数和课程平均分，并将学院的名称、选课人数

--和平均分按学院的名称顺序，以集合的方式返回给调用者。 --1) 书上使用临时表的方法： CREATE PROCEDURE PROC3 AS BEGIN

--定义一个临时表,存放每个学院的名称、课程名称、课程选课人数和课程平均分 CREATE TABLE #myTemp( institute varchar(30), courseName varchar(30), count tinyint,

avg numeric(5,1),

PRIMARY KEY(institute,courseName) )

DECLARE @institute varchar(30)

--定义游标curInstitute，查找学院名称 DECLARE curInstitute CURSOR FOR

SELECT DISTINCT institute FROM Class

ORDER BY institute OPEN curInstitute

FETCH curInstitute INTO @institute WHILE (@@FETCH_STATUS=0) BEGIN

DECLARE @courseName varchar(30),@count tinyint=0,@avg numeric(5,1)=0

--定义游标curCourse，查询学院名称为@institute的课程名称、课程选课人数和平均分 DECLARE curCourse CURSOR FOR

SELECT courseName,count(*),avg(score)

本文来源：https://www.bwwdw.com/article/25bt.html