《万常选版数据库系统原理与设计第9章课后习题答案.doc》由会员分享，可在线阅读，更多相关《万常选版数据库系统原理与设计第9章课后习题答案.doc（8页珍藏版）》请在金锄头文库上搜索。
1、USE ScoreDBGO-9.6 在学生成绩管理数据库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,classN
2、umtinyintNULL/*班级人数*/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(c
3、lassNum >=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(SELEC
5、T * 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=Cla
6、ss.classNo)-定义变量并赋值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=
7、classNum WHERE CURRENT OF CURSOR_ClassFETCH CURSOR_Class INTO classNo-获取下一个游标值ENDCLOSE CURSOR_Class-关闭游标DEALLOCATE CURSOR_Class-释放游标-9.8 在学生成绩管理数据库ScoreDB中，完成如下操作：-(1) 创建5个用户user01,user02,user03,user04,user05。-可以采用以下A,B,C,D四种方式创建数据库用户-A. 创建数据库用户-下面的示例首先创建名为 user01 且具有密码的服务器登录名，然后在 ScoreDB 中创建对应的数据库用
8、户 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 =
9、 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. 创建和使用不含登录名的用户-以下示例创建一个数据库用户 user04，该用户不映射到
10、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) 将课程
11、表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，不具有转授的权
12、利。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
13、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) 根据输入的班级编号，逐
14、行输出该班每个同学的姓名、课程名和相应成绩，并按姓名排序输出。CREATE PROCEDURE PROC1(cNo char(6)ASSELECT studentName,courseName,ScoreFROM Student INNER JOIN Score ON Student.studentNo=Score.studentNoINNER JOIN Course ON Score.courseNo=Course.courseNoWHERE classNo=cNoORDER BY studentNameGO-EXEC PROC1 IS0801-(2) 根据输入的课程编号，统计该课程的选课人数和平均分，并将统计结果返回给调用者。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,
