【学习笔记之数据库系统工程师】2019上半年下午试题四

某学生信息管理系统的部分数据库关系模式如下：
学生： Student ( stuno, stuname, stuage, stusex, schno),各属性分别表示学生的学号、姓名、年龄、性别，以及学生所属学院的编号；
学院： School ( schno ，〜schname, schstunum〜),各属性分别表示学院的编号、名称及学生人数；
俱乐部： Club (clubno,clubname, clubyear, clubloc〜),各属性分别表示俱乐部的编号、名称、成立年份和活动地点；
参加： JoinClub ( stuno, clubno. joinyear〜),各属性分别表示学号、俱乐部编号，以及学生加入俱乐部的年份。
有关关系模式的说明如下：
(1)学生的性别取值为‘F’和‘M’ (F 表示女性， M 表示男性)。
(2)删除一个学院的记录时，通过外键约束级联删除该学院的所有学生记录。
(3)学院表中的学生人数值与学生表中的实际人数要完全保持一致。也就是说，当学生表中增减记录时，就要自动修改相应学院的人数。

问题： 1

请将下面创建学生表的 SQL 语句补充完整，要求定义实体完整性约束、参照完整性约束，
以及其他完整性约束。
CREATE TABLE Student (
stuno CHAR(ll) ()，
stuname VARCHAR,
stuage SMALLINT,
stusex CHAR(l) ()，
schno CHAR(3) () ON DELETE ())；

答案：
（a）PRIMARY KEY
（b）CHECK VALUES IN(‘F’,‘M’)或 CHECK (stusex IN(‘F’,’M’))或其他等价形式
（c）REFERNCES School(schno)
（d）CASCADE

分析：

CREATE TABLE Student ( 
stuno CHAR(11) (a), //学号 所以可得它是主键
stuname VARCHAR, //姓名 
stuage SMALLINT,  //年龄 
stusex CHAR(1) (b), //性别 
schno CHAR(3) (c) ON DELETE (d) //学生所属学院的编号 
)

(b)学生的性别取值为‘F’和‘M’ (F 表示女性， M 表示男性),所以应该在创建表的时候使用check约束
(c、d )可得需要添加外键约束级联删除

问题： 2

创建倶乐部人数视图，能统计每个倶乐部已加入学生的人数，属性有 clubno、 clubname
和 clubstunum 。对于暂时没有学生参加的俱乐部，其人数为 0 。此视图的创建语句如下，
请补全。
CREATE VIEW CS_NUMBER ( clubno, clubname, clubstunum ) AS
SELECT JoinClub.clubno, () , ()
FROM JoinClub, Club
WHERE JoinClub.clubno = Club.clubno
() BY JoinClub.clubno ()
SELECT clubno, clubname, 0 FROM Club
WHERE clubno NOT 04
(SELECT DISTINCT clubno FROM ());

答案：
(e)Club.clubname
(f)COUNT(DISTINCT(JoinClub.stuno)) as clubstunum
(g)GROUP
(h)UNION
(i)JoinClub

分析：

CREATE VIEW CS_NUMBER ( clubno, clubname, clubstunum ) AS
SELECT JoinClub.clubno, (e) , (f)
FROM JoinClub, Club
WHERE JoinClub.clubno = Club.clubno
(g) BY JoinClub.clubno (h)
SELECT clubno, clubname, 0 FROM Club
WHERE clubno NOT 04
(SELECT DISTINCT clubno FROM (i));

（f）求人数clubstunum ，DISTINCT为消除重复项，COUNT则是求和
（h）union用来合并两个或多个SELECT结果。

问题： 3

每当系统中新加或删除一个学生，就需要自动修改相应学院的人数，以便保持系统中学生
人数的完整性与一致性。此功能由下面的触发器实现，请补全。
CREATE TRIGGER STU_NUM_TRG
AFTER INSERT OR DELETE ON ()
REFERENCING new row AS nrow, old row AS orow FOR EACH()
BEGIN
IF INSERTING THEN
UPDATE School ()
END IF;
IF DELETING THEN
UPDATE School ();
END IF;
END;

答案;
(j)Student
(k)ROW
(l)SET schstunum=schstunum+1 where School.schno= nrow.schno
(m)SET schstunum=schstunum-1 where School.schno=orow.schno

分析：

CREATE TRIGGER STU_NUM_TRG
AFTER INSERT OR DELETE ON (j)
REFERENCING new row AS nrow, old row AS orow FOR EACH(k)
BEGIN
IF INSERTING THEN
UPDATE School (l)
END IF;
IF DELETING THEN
UPDATE School (m);
END IF;
END;

（j，k）根据触发器的语法可知
（l、m）从这句话‘’每当系统中新加或删除一个学生‘’可得需要进行更改

问题：4

查询年龄小于 19 岁的学生的学号、姓名及所属学院名，要求输出结果把同一个学院的学生
排在一起。此功能由下面的 SQL 语句实现，请补全。
SELECT stuno, stuname, schname FROM Student, School WHERE Student.schno =
School.schno
AND stuage <19 () BY ();

答案：
(n)order
(o)Student.schno或School.schno

分析：

SELECT stuno, stuname, schname FROM Student, School WHERE Student.schno =
School.schno
AND stuage <19 (n) BY (o);