数据库原理2研讨.ppt

12121670 郭超 数据库第八周研讨 1. 写有关school数据库的完整性约束 （1）确保每个学生在同一学期同一门课只能选一个老师开设的课程。 (1) create assertion asse1 check (not exists( select * from e a,e b where a.xh = b.xh and a.xq = b.xq and a.kh = b.kh and a.gh b.gh ) ) （2）确保每门课程每年只开设一次。 (2) create assertion asse2-1 check //不允许同一门课在同一年里由不同老师开 (1 all( select count(distinct a.kh) from o a,o b where a.kh=b.kh and left(a.xq,9)=left(b.xq,9) and a.ghb.gh ) ) create assertion asse2-2 check //不允许同一个老师在同一年开同一门课 (not exists (select * from o a,o b where a.gh=b.gh and left(a.xq,9)=left(b.xq,9) and a.kh=b.kh ) ) 2. 设有三个关系模式：STUDENT（SNO,SNAME,AGE,SEX,SADDR,DNO） COURSE（CNO,CNAME,CHARA,CREDIT,DNO,TERM,TNAME） GRADE（SNO,CNO,TERM,PGRADE,EGRADE,GGRADE） 中文含义：学生（学号，姓名，年龄，性别，住址，所在系）， 课程（课程号,课程名,课程性质,学分数,开课系,开课学期,教师）， 成绩（学号, 课程号, 开课学期,平时成绩,考试成绩,总评成绩）。 请用指定的方法定义下列完整性约束： 1、每个学期每个学生至多可选8门课程（用断言）。 (1) create assertion asse1 check (8= all(select count (distinct (cno)) from grade group by sno , term )); 2、如果总评成绩修改后低于60分，那么，如果平时成绩大于80分,在修改后的 总评成绩上再增加考试成绩的8%，但不得超过60分（用SQL3触发器）。 (2) create trigger trig1 after update of ggrade on grade referencing old as oldtuple new as newtuple when (newtuple.ggrage60 and oldtuple.pgrade80) update grade set ggrade=ggrade + oldtuple.egrade * 0.08 where sno = newtuple.sno and cno = newtuple.cno and term = newtuple.term update grade set ggrade=60 where sno = newtuple.sno and cno = newtuple.cno and term = newtuple.term and ggrade60 for each row; 3. 某图书借阅管理数据库有如下关系模式：书籍表（书号, 书名, 数量, 分类, 状态, 出版社名）读者表（借书证号, 姓名, 年龄, 电话, 地址）借阅情况表（借书证号, 书号, 借书日期, 还书日期） 书籍表B（书号SH, 书名SM, 数量SL, 分类FL, 状态ZT, 出版社名CBSM） 读者表R（借书证号JSZH, 姓名XM, 年龄NL, 电话DH, 地址DZ） 借阅情况表S（借书证号JSZH, 书号SH, 借书日期JSRQ, 还书日期HSRQ） 1）把对书籍表的查询、修改权和转授权授予用户admin。 2）使每个人都能查询未出借图书的借阅情况，但不能直接查看借阅情况表。 Grant select, update on B to admin with Grant Option Create view A As select B.SH,SM,SL,FL,ZT,CBSM, JSZH,JSRQ,HSRQ From B,S Where B.SH=S.SH and ZT=’未出借’ Grant select on A to Public