第八章习题

8.19 设教学数据库的关系如下：

S（SNO，SNAME，AGE，SEX）

SC（SNO，CNO，GRADE）

C（CNO，CNAME，TEACHER）

试用多种方法定义下列完整性约束：

⑴ 在关系 S 中插入的学生年龄应在 16~25 岁之间。

--用CHECK子句定义
CREATE TABLE S
(SNO CHAR(8) NOT NULL,
SNAME CHAR(20),
AGE SMALLINT,
SEX CHAR(4),
PRIMARY KEY (SNO),
CHECK(AGE BETWEEN 16 AND 25)
)

⑵ 在关系 SC 中插入元组时，其 SNO 值和 CNO 值必须分别在 S 和 C 中出现。

FOREIGN KEY(SNO) REFERENCES S(SNO);
FOREIGN KEY(CNO) REFERENCES C(CNO);

⑶ 在关系 C 中删除一个元组时，首先要把关系 SC 中具有同样 CNO 值的元组全部删去。

CREATE TABLE SC
(SNO CHAR(8) NOT NULL,
CNO CHAR(8) NOT NULL,
PRIMARY KEY(SNO,CNO),
FOREIGN KEY(SNO) REFERENCES S(SNO) ON DELETE CASCADE,
FOREIGN KEY(CNO) REFERENCES C(CNO) ON DELETE CASCADE,
CHECK(GRADE BETWEEN 0 AND 100)
);

⑷ 在关系 S 中把某个 SNO 值修改为新值时，必须同时把关系 SC 中那些同样的 SNO 值 也修改为新值。

在SC中加：
FOREIGN KEY(SNO) REFERENCES S(SNO) ON UPDATE CASCADE

8.20 在教学数据库中的关系 S、SC、C 中，试用 SQL 的断言机制定义下列两个完整性约束：

⑴ 每位教师开设的课程不能超过 10 门。

CREATE ASSERTION ASSE1 CHECK
(10 > ALL(SELECT COUNT(CNO) FROM C 
GROUP BY TNAME
));

(2)不允许男同学选修 WU 老师的课程。

CREATE ASSERTION ASSE2 CHECK
(NOT EXISTS(SELECT * FROM S,SC,C WHERE S.SNO = SC.SNO AND SC.CNO = C.CNO
AND SEX = '男' AND TNAME = 'WU'
));

(3)每门课程最多 50 名男同学选修

CREATE ASSERTION ASSE3 CHECK
(50 >= ALL(SELECT COUNT(SC.SNO) FROM SC,C WHERE SC.CNO = C.CNO
AND SEX = '男' GROUP BY CNO
));

(4)学生必须在选修 Maths 课后，才能选修其他课程。

--不存在学生的选课，没学过MATHS课。

CREATE ASSERTION ASSE4 CHECK
(NOT EXISTS(SELECT * FROM SC X WHERE NOT EXISTS(
SELECT * FROM SC Y,C WHERE Y.CNO = C.CNO AND Y.SNO = X.SNO
AND CNAME = 'MATH' 
)));

(5)每个男学生最多选修 20 门课程。

CREATE ASSERTION ASSE5 CHECK
(20 >= ALL(SELECT COUNT(CNO) FROM SC,C WHERE SC.CNO = C.CNO
AND SEX = '男' GROUP BY S.SNO
));