JZTXT

sql语句（三）

发布时间 2023-03-29 14:09:21作者: 太好了还有脑子可以用

7.3.4 设计题

设教学数据库中有4个基本表:
系 DEPT(D#,DNAME, MGR#)
其属性分别表示系编号、系名、系主任的教师工号。
教师 T(T#,TNAME,AGE, SEX,SALARY, D#)
其属性分别表示教师工号、姓名、年龄、性别、工资、所在系的编号。
任课 TC(T#,C#, TEXTBOOK)
其属性分别表示教师工号、课程号和所用的教材名。
课程 C(C#,CNAME,D#)
其属性分别表示课程号、课程名和开课系的编号。
下面的题目都是针对这4个基本表进行操作。
总结：
系 DEPT(D#,DNAME, MGR#)
教师 T(T#,TNAME,AGE, SEX,SALARY, D#)
任课 TC(T#,C#, TEXTBOOK)
课程 C(C#,CNAME,D#)

1．用sQL的子句或语句定义下列完整性约束。

①编号小于D8的系的教师年龄为20～40岁，其他系的教师年龄为20~60岁。
check(
(D#<'D8' and age between 20 and 40) or 
(D#>='D8' and age between 20 and 80))
②在教师表T中出现的D#值必须在 DEPT表中出现（用3种形式定义)。

第一种形式，在T表中，加-个外键子句，同时在D#定义时注明非空:

D# char(4) not null,
foreign key D# references DEPT(D#)

第二种形式,在T表中用CHECK子句定义:

check(D# in(select D# from DEPT))

第三种形式，用断言定义:

create assertion asse2 CHECK
(not exists
(select *from T
where D# not in
(select D#
from DEPT)));
③每个女教师至少要任教一门课。

用断言定义:

create assertion asse3 check
(not exists
(select*from T
where sex="f"
and T# not in
(select t# from TC)));
④每个男教师至少要任教两门课。

用断言定义:

create assertion asse4 check
(2<=all(select count(C#) from T,TC
where sex='m' and TT#=TC.T#
group by T.T#));
⑤每个系的教师的平均工资不能低于2000元。

用断言定义:

create assertion asse5 check
(2000<=all(select avg(salary)
from T
group by D#));
⑥不允许男教师任教“艺术体操”课程。

用断言定义:

create assertion asse6 check
(not exists
  (select*from TC
   where T# in
     (select T# from T
     where sex='m')
     and C# in
     (select C# from C
     where cname="艺术体操")));

2．试对下列操作写出授权语句。

①把对DEPT表的插入、修改、删除、查询的权限授给用户LIU。
grant insert, update,delete,select on DEPT to LIU;
②把对TC、C表的查询权限转授给全体用户。
grant select on TC,C to public;
③允许用户ZHANG引用C表的主键作为新表的外键，并有转让权限。
grant references(C#) on C to ZHANG with grant option;
④从用户ZHANG回收对C表主键引用的转授权。
revoke grant option for references(C#) on C from ZHANG;