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

1、USE ScoreDBGO 在学生成绩管理数据库ScoreDB中完成。-(1) 在班级表Class中，分别使用列级和元组级约束保证班级人数classNum属性的取值在(0,50)之间。-为避免误删Class表的数据，另建两表演示，演示后删除-列级约束CREATE TABLE Class1(classNochar(6)PRIMARY KEY,/*班级编号*/classNamevarchar(30)NOT NULL,/*班级名称*/institutevarchar(30)NOT NULL,/*所属学院*/gradesmallintNOT NULL/*年级*/DEFAULT 0,classNumti

2、nyintNULL/*班级人数*/CHECK(classNum >=0 AND classNum <= 50)-元组级约束CREATE TABLE Class2(classNochar(6)PRIMARY KEY,/*班级编号*/classNamevarchar(30)NOT NULL,/*班级名称*/institutevarchar(30)NOT NULL,/*所属学院*/gradesmallintNOT NULL/*年级*/DEFAULT 0,classNumtinyintNULL,/*班级人数*/CONSTRAINT CK_Class_classNum CHECK(class

3、Num >=0 AND classNum <= 50)-删除演示表DROP TABLE Class1DROP TABLE Class2GO-(2) 在学生表Student中，限制籍贯为上海或北京的学生的年龄必须在17岁以上。-需要使用触发器CREATE TRIGGER Student_birthdayON StudentFOR INSERT,UPDATEASIF EXISTS(SELECT * FROM inserted WHERE YEAR(GETDATE()-YEAR(birthday)<17)ROLLBACKGO-(3) 对于某门课程，保证如果没有选修其先修课程，则不能

4、选修该课程。CREATE TRIGGER Score_courseNoON ScoreFOR INSERTASIF NOT EXISTS(SELECT * FROM Score WHERE courseNo=(SELECT courseNo FROM Course WHERE priorCourse=(SELECT courseNo FROM inserted)ROLLBACKGO-(4) 如果在学生表中修改了学号，则自动修改成绩表中的学号。CREATE TRIGGER Student_studentNoON StudentFOR UPDATEASIF NOT EXISTS(SELECT *

5、FROM inserted WHERE studentNo=(SELECT studentNo FROM deleted)UPDATE ScoreSET studentNo=(SELECT studentNo FROM inserted)WHERE studentNo=(SELECT studentNo FROM deleted)GO-(5) 使用游标编程统计每个班的学生人数，并将统计结果存入班级表的班级人数属性中。-不使用游标的语句很简单-UPDATE Class SET classNum=(SELECT COUNT(*) FROM Student WHERE classNo=-定义变量并赋

6、值DECLARE classNo char(6)=''DECLARE classNum int=0-定义游标DECLARE CURSOR_Class CURSORFORSELECT classNoFROM Class-使用游标OPEN CURSOR_Class-打开游标FETCH CURSOR_Class INTO classNoWHILE(FETCH_STATUS=0)BEGINSELECT classNum=(SELECT count(*)FROM StudentWHERE classNo=classNo)UPDATE Class SET classNum=classNum

7、 WHERE CURRENT OF CURSOR_ClassFETCH CURSOR_Class INTO classNo-获取下一个游标值ENDCLOSE CURSOR_Class-关闭游标DEALLOCATE CURSOR_Class-释放游标 在学生成绩管理数据库ScoreDB中，完成如下操作：-(1) 创建5个用户user01,user02,user03,user04,user05。-可以采用以下A,B,C,D四种方式创建数据库用户-A. 创建数据库用户-下面的示例首先创建名为 user01 且具有密码的服务器登录名，然后在 ScoreDB 中创建对应的数据库用户 user01。CRE

8、ATE 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_SC

9、HEMA = db_datareader;GO-C. 从证书创建数据库用户-下面的示例从证书 Teacher50 创建数据库用户 user03。USE ScoreDB;CREATE MASTER KEY ENCRYPTION BY PASSWORD = '123'GOCREATE CERTIFICATE Teacher50WITH SUBJECT = 'Teachers',EXPIRY_DATE = '11/11/2015'GOCREATE USER user03 FOR CERTIFICATE Teacher50;GO-D. 创建和使用不含登录

10、名的用户-以下示例创建一个数据库用户 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-创建和使用不含登录名的用

11、户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) 用户

12、 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)

13、 用户 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 在学生成绩管

14、理数据库ScoreDB中，编写如下的存储过程：-(1) 根据输入的班级编号，逐行输出该班每个同学的姓名、课程名和相应成绩，并按姓名排序输出。CREATE PROCEDURE PROC1(cNo char(6)ASSELECT studentName,courseName,ScoreFROM Student INNER JOIN Score ON =INNER JOIN Course ON =WHERE classNo=cNoORDER BY studentNameGO-EXEC PROC1 'IS0801'-(2) 根据输入的课程编号，统计该课程的选课人数和平均分，并将统计结果

15、返回给调用者。CREATE PROCEDURE PROC2(cNo char(3), count tinyint OUTPUT, avg numeric(5,1) OUTPUT)ASSELECT count=COUNT(*), avg=AVG(score)FROM ScoreWHERE courseNo=cNoGO-DECLARE count tinyint, avg numeric(5,1)-EXEC PROC2 '006', count OUTPUT, avg OUTPUT-SELECT count, avg-(3) 不允许使用聚集函数，统计每个学院的选课学生人数和课程平均

16、分，并将学院的名称、选课人数-和平均分按学院的名称顺序，以集合的方式返回给调用者。-1) 书上使用临时表的方法：CREATE PROCEDURE PROC3ASBEGIN-定义一个临时表,存放每个学院的名称、课程名称、课程选课人数和课程平均分CREATE TABLE #myTemp(institutevarchar(30),courseNamevarchar(30),counttinyint,avgnumeric(5,1),PRIMARY KEY(institute,courseName)DECLARE institute varchar(30)-定义游标curInstitute，查找学院名称

17、DECLARE curInstitute CURSORFORSELECT DISTINCT instituteFROM ClassORDER BY instituteOPEN curInstituteFETCH curInstitute INTO instituteWHILE (FETCH_STATUS=0)BEGINDECLARE courseName varchar(30),count tinyint=0,avg numeric(5,1)=0-定义游标curCourse，查询学院名称为institute的课程名称、课程选课人数和平均分DECLARE curCourse CURSORFORS

18、ELECT courseName,count(*),avg(score)FROM Score,CourseWHERE studentNo IN(SELECT studentNoFROM StudentWHERE classNo IN(SELECT classNoFROM ClassWHERE institute=institute) AND =GROUP BY courseNameOPEN curCourseFETCH curCourse INTO courseName,count,avgWHILE(FETCH_STATUS=0)BEGIN-将学院名称、课程名称、课程选课人数和课程平均分插入到

19、临时表#myTemp中INSERT INTO #myTemp VALUES(institute,courseName,count,avg)-获取下一个游标值，取该学院下一门课程的课程名、选课人数和平均分FETCH curCourse INTO courseName,count,avgENDCLOSE curCourseDEALLOCATE curCourse-获取游标curInstitute的下一个值，即取下一个学院FETCH curInstitute INTO instituteENDCLOSE curInstituteDEALLOCATE curInstitute-显示临时表的内容，同时将

20、临时表的内容返回给调用者SELECT * FROM #myTemp Order BY instituteENDGO-EXEC PROC3-DROP PROCEDURE PROC3-2) 使用游标作为OUTPUT参数的方法：-将游标使用成OUTPUT参数。游标可以使用OUTPUT（输出）参数，但不能使用成输入参数。-也就是说，游标可以作为结果返回，但却不能传输到过程中去。当游标被用作参数时，-需要限定其为OUTPUT和VARYING。VARYING关键字指出该结果集要用来支持输出参数。-这样就提供了将结果集返回到调用过程的能力。-例如：CREATE PROCEDURE PROC3(institu

21、te_cursor CURSOR VARYING OUTPUT)ASSET institute_cursor = CURSORFORSELECT institute, courseName, COUNT(*), AVG(score)FROM Class,Student,Score,CourseWHERE =AND =AND =GROUP BY institute,courseNameORDER BY instituteOPEN institute_cursorGO-DECLARE MyCursor CURSOR;-EXEC PROC3 institute_cursor = MyCursor O

22、UTPUT;-WHILE (FETCH_STATUS = 0)-BEGIN- FETCH NEXT FROM MyCursor;-END;-CLOSE MyCursor;-DEALLOCATE MyCursor;-GO-(4) 不允许使用聚集函数，统计每门课程的选课人数和平均分，按如下格式输出。-课程名 1：xxxxxx-学号姓名成绩-xxxxxxxxxxxxxxxx-.-选课人数：xxx-平均分：-课程名 2：xxxxxx-学号姓名成绩-xxxxxxxxxxxxxxxx-.-选课人数：xxx-平均分：-CREATE PROCEDURE PROC4ASBEGINSET NOCOUNT ONDECLARE courseNo char(3),courseName varchar(30)DECLARE n tinyint=0DECLARE curCourse CURSORFORSELECT courseNo,courseNameFROM CourseOPEN curCourseFETCH curCourse INTO courseNo,courseNameWHILE(FETCH_STATUS=0)BEGINSET n=n+1PRINT '课程名' + STR(n,2) + '：' + courseNamePRINT '