1、中级数据库系统工程师下午试题-6 及答案解析(总分：75.01，做题时间：90 分钟)一、B试题一/B(总题数：1，分数：15.00)阅读以下说明和关系表，回答问题 1-6。说明设有三个关系学生 S(学号，姓名，性别，年龄，籍贯)课程 C(课程号，课程名，任课老师，所用教材)学生成绩 SC(学号，课程号，得分)其中规定各属性定义如下：学号：SNUM 姓名：SNAME 性别：SEX 年龄：AGE 籍贯：NATIVE课程号：CNUM 课程名：CNAME 任课老师：TEACHER 所用教材：BOOK得分：GRADE试用关系代数表达式表示下列查询语句。（分数：15.00）(1).问题 1检索张三老师所
2、授课程的名称。（分数：2.50）_(2).问题 2检索选修全部课程的学生的学号和姓名。（分数：2.50）_(3).问题 3检索全部学生都选修的课程号。（分数：2.50）_(4).问题 4检索至少选修了课程号为 1和课程号为 5的课程的学生的学号。（分数：2.50）_(5).问题 5检索选修课程包含李四老师所授课程之一的学生姓名。（分数：2.50）_(6).问题 6检索选修课程包含学号为 2且年龄小于 20的学生所修全部课程的课程号。（分数：2.50）_二、B试题二/B(总题数：1，分数：20.00)阅读以下说明，回答问题 1-3。在图书馆数据库有三个基本表：书目表 Cata(书号 Cno、书名
3、 Cname、作者 Cauthor、出版年 Cdate、价格Cprice)、学生表 Student(学号 Sno、姓名 Sname、性别 Sgender、专业 Sdept)和借书历史表 Borrow(所借书号 Cno、借书者学号 Sno、借书日期 Bbdate、还书日期 Brdate)。（分数：20.01）(1).问题 1请用 SQL语句来建立这三个基本表(请注意数据类型的选择)。（分数：6.67）_(2).问题 2请用 SQL实现如下操作：(1)图书馆里已有的鲁迅著作的名称(2)按借书日期列出学生李明的所借书名(3)往学生表 Student中插入一个学生记录。(学号：132，姓名：张华，性别
4、：男，系别：计算机)(4)把野火集的价格修改为 15元（分数：6.67）_(3).问题 3请用 SQL语句来实现如下改变：为借书历史 Borrow增加一个属性罚金 Bfine，同时删去书目表 Cata中的属性价格 Cprice。（分数：6.67）_三、B试题三/B(总题数：1，分数：20.00)阅读以下说明，回答问题 1-4。现有这样一个关系，用来表示某工厂工人家庭的信息。记录每个工人的工作证号、姓名和家庭住址，还包含他/她的每个孩子的身份证号、姓名和年龄，以及他她所拥有的每部电话的电话号码和型号。已知：有一些人可能有很多部电话，这些电话可能是同一型号，也可能不是同一型号；有一些人没有电话；如
5、果某人有电话，他的每部电话都有一个电话号码。有一些人可能有很多个孩子，有一些人没有孩子。初步设计的关系模式如下：R(身份证号，姓名，家庭住址，H 身份证号，H 姓名，H 年龄，电话号码，型号)，其中“H 身份证号，H 姓名，H 年龄”分别是孩子的身份证号、姓名和年龄。（分数：20.00）(1).问题 1请将该关系模式分解成第二范式的关系模式，确定主关系键。（分数：5.00）_(2).问题 2请将该关系模式分解成第三范式的关系模式，确定主关系键。（分数：5.00）_(3).问题 3请将该关系模式分解成 BCNF的关系模式，确定主关系键。（分数：5.00）_(4).问题 4请简要说明各范式之间的关
6、系。(文字和图标形式皆可)（分数：5.00）_四、B试题四/B(总题数：1，分数：20.00)阅读以下说明，回答问题 1-4。说明设学校教学数据库的模式如下：S(SNUM，SNAME，AGE)SC(SNUM，CNUM，CREDIT，SCORE)C(CNUM，CNAME，TEACHER)T(TNUM，TEHCHER，POS，SAL)SNUM表示学生的学号，SNAME 表示学生的姓名，AGE 表示学生的年龄，CNUM 表示选修课程的编号，CREDIT表示该课程的学分，SCORE 表示课程的成绩，CNAME 表示课程名，TEACHER 表示任课的教师,TNUM 表示老师的工作证号，POS 表示教师的
7、职称，SM，表示教师的收入。试定义下列完整性约束和规则：（分数：20.00）(1).问题 1在关系 S中插入的学生年龄值应在 1821 岁之间。（分数：5.00）_(2).问题 2在关系 SC中插入的元组时，其 SNUM和 CNUM的值必须分别在 S和 C中出现过。（分数：5.00）_(3).问题 3在关系 C中把某个 CNUM的值改为新的值的时候，必须同时要把关系 SC中那些同样的 CNUM的值也修改为新值。（分数：5.00）_(4).问题 4为 T定义完整性规则，“教授的工资不得低于 2000元，如果低于 2000元，自动改为 2000元”。（分数：5.00）_中级数据库系统工程师下午试题
8、-6 答案解析(总分：75.01，做题时间：90 分钟)一、B试题一/B(总题数：1，分数：15.00)阅读以下说明和关系表，回答问题 1-6。说明设有三个关系学生 S(学号，姓名，性别，年龄，籍贯)课程 C(课程号，课程名，任课老师，所用教材)学生成绩 SC(学号，课程号，得分)其中规定各属性定义如下：学号：SNUM 姓名：SNAME 性别：SEX 年龄：AGE 籍贯：NATIVE课程号：CNUM 课程名：CNAME 任课老师：TEACHER 所用教材：BOOK得分：GRADE试用关系代数表达式表示下列查询语句。（分数：15.00）(1).问题 1检索张三老师所授课程的名称。（分数：2.50
9、）_正确答案：()解析： CNAME( TEACHER=张三 (C)(2).问题 2检索选修全部课程的学生的学号和姓名。（分数：2.50）_正确答案：()解析： SNUM,CNUM(SC) CNUM(C) SNUMM,SNAME(S)(3).问题 3检索全部学生都选修的课程号。（分数：2.50）_正确答案：()解析： SNUM,CNUM(SC) SNUM(S)(4).问题 4检索至少选修了课程号为 1和课程号为 5的课程的学生的学号。（分数：2.50）_正确答案：()解析：可建立一个临时关系 R(5).问题 5检索选修课程包含李四老师所授课程之一的学生姓名。（分数：2.50）_正确答案：()解
10、析： SNUM( CNUM( TEACHER=李四 (C)SC) SNUM,SHAME(S)(6).问题 6检索选修课程包含学号为 2且年龄小于 20的学生所修全部课程的课程号。（分数：2.50）_正确答案：()解析： CNUM( SNUM( SNUM=2(S) AGE20(S) SNUM,CNUM(SC)考点分析 关系代数运算。二、B试题二/B(总题数：1，分数：20.00)阅读以下说明，回答问题 1-3。在图书馆数据库有三个基本表：书目表 Cata(书号 Cno、书名 Cname、作者 Cauthor、出版年 Cdate、价格Cprice)、学生表 Student(学号 Sno、姓名 Sn
11、ame、性别 Sgender、专业 Sdept)和借书历史表 Borrow(所借书号 Cno、借书者学号 Sno、借书日期 Bbdate、还书日期 Brdate)。（分数：20.01）(1).问题 1请用 SQL语句来建立这三个基本表(请注意数据类型的选择)。（分数：6.67）_正确答案：()解析：CREATE TABLE Cata( Cno INT, Cname CHAR(40)， Cauthor CHAR(10)， Cdate INT， Cpriee float， )； CREATE TABLE Student( Sno INT， Shame CHAR(20)， Sgendcr CHAR(
12、2)， Sdept CHAR(20)，)； CREATE TABLE BORROW( Cno INT， Sno INT， Bbdate INT： Brdate INT。 )；(2).问题 2请用 SQL实现如下操作：(1)图书馆里已有的鲁迅著作的名称(2)按借书日期列出学生李明的所借书名(3)往学生表 Student中插入一个学生记录。(学号：132，姓名：张华，性别：男，系别：计算机)(4)把野火集的价格修改为 15元（分数：6.67）_正确答案：()解析：SELECTCname FROM Cata WHEREauthor=鲁迅； (2)SELECT Cname from Cata，Stud
13、ent，Borrow Where Student. Sno=Bom)w. Sno and Cata. Cno=BorrowCno and Snamc=李明 Group by Bbdate (3)INSERT INTO Student(Sno，Sname，Sgcn- der，Sdept) VALUES(132，张华，男，计算机)； (4)UPDATE Cata SET Cprice=25 WHERE Cnarse=野火集；(3).问题 3请用 SQL语句来实现如下改变：为借书历史 Borrow增加一个属性罚金 Bfine，同时删去书目表 Cata中的属性价格 Cprice。（分数：6.67）_正
14、确答案：()解析：ALTER TABLE Borrow ADD Brine float； ALTER TABLE Cata drop Cprice Float； 考点分析 关系数据库标准语言 SQL相关操作。三、B试题三/B(总题数：1，分数：20.00)阅读以下说明，回答问题 1-4。现有这样一个关系，用来表示某工厂工人家庭的信息。记录每个工人的工作证号、姓名和家庭住址，还包含他/她的每个孩子的身份证号、姓名和年龄，以及他她所拥有的每部电话的电话号码和型号。已知：有一些人可能有很多部电话，这些电话可能是同一型号，也可能不是同一型号；有一些人没有电话；如果某人有电话，他的每部电话都有一个电话号
15、码。有一些人可能有很多个孩子，有一些人没有孩子。初步设计的关系模式如下：R(身份证号，姓名，家庭住址，H 身份证号，H 姓名，H 年龄，电话号码，型号)，其中“H 身份证号，H 姓名，H 年龄”分别是孩子的身份证号、姓名和年龄。（分数：20.00）(1).问题 1请将该关系模式分解成第二范式的关系模式，确定主关系键。（分数：5.00）_正确答案：()解析：首先关系模式 R的所有属性都是不可分的数据项，得到第一范式的关系模式： R1(U身份证号/U，姓名，家庭住址，UH 身份证号/U，H 姓名，H 籍贯，U电适号码/U，型号) 其中身份证号、H 身份证号、电话号码是主关系键。但是由于存在着非主属
16、性对主属性的部分函数依赖，采用投影分解法，R1 被分解为 R2、R3、R4、R5 得到第二范式的关系模型： R2(U身份证号/U，UH身份证号/U，U电适号码/U) R3(U身份证号/U，姓名，家庭住址) R4(UH 身份证号/U，H 姓名，H 籍贯) B5(U电话号码/U，型号)(2).问题 2请将该关系模式分解成第三范式的关系模式，确定主关系键。（分数：5.00）_正确答案：()解析：R3 中 H籍贯传递函数依赖于 H身份证号。再采用投影分解法，R4 被分解为 R6、R7 得到第三范式的关系模型： R2(U身份证号/U，UH 身份证号/U，U电话号码/U) R3(U身份证号/U，姓名，家庭
17、住址) R5(U电话号码/U，型号) R6(UH 身份证号/U，H 姓名) R7(UH姓名/U，H 籍贯)(3).问题 3请将该关系模式分解成 BCNF的关系模式，确定主关系键。（分数：5.00）_正确答案：()解析：由于存在主属性部分依赖于码的问题，再采用投影分解法，R2 被分解为 R8、R9，将得到 BCNF范式的关系模型： R8(U身份证号/U，UH 身份证号/U，U电话号码/U) R9(U身份证号/U，U电话号码/U) R3(U身份证号/U，姓名，家庭住址) R5(U电话号码/U，型号) R6(UH 身俭正号/U，H 姓名) R7(UH 姓名/U，H 籍贯)(4).问题 4请简要说明各
18、范式之间的关系。(文字和图标形式皆可)（分数：5.00）_正确答案：()解析：首先当一个关系模型 R的所有属性都是不可分的基本数据项的时候，得到第一范式。 通过消除非主属性对候选码的部分函数依赖关系，得到第二范式。接着通过消除非主属性对候选码的传递依赖关系，得到第三范式。最后，通过消除对任何属性对码的部分函数依赖和传递函数依赖得到 BCNF范式。 考点分析 关系数据库规范化；关系模式的分解。四、B试题四/B(总题数：1，分数：20.00)阅读以下说明，回答问题 1-4。说明设学校教学数据库的模式如下：S(SNUM，SNAME，AGE)SC(SNUM，CNUM，CREDIT，SCORE)C(CN
19、UM，CNAME，TEACHER)T(TNUM，TEHCHER，POS，SAL)SNUM表示学生的学号，SNAME 表示学生的姓名，AGE 表示学生的年龄，CNUM 表示选修课程的编号，CREDIT表示该课程的学分，SCORE 表示课程的成绩，CNAME 表示课程名，TEACHER 表示任课的教师,TNUM 表示老师的工作证号，POS 表示教师的职称，SM，表示教师的收入。试定义下列完整性约束和规则：（分数：20.00）(1).问题 1在关系 S中插入的学生年龄值应在 1821 岁之间。（分数：5.00）_正确答案：()解析：定义 s时采用检查子句：CREATTABLE S( SNUM CHA
20、R(5)，SNAME char(10) NOT NULL，AGE SMALLINT，PRIMARY KEY(SNUM)CHECK ；18 and AGE=21)(2).问题 2在关系 SC中插入的元组时，其 SNUM和 CNUM的值必须分别在 S和 C中出现过。（分数：5.00）_正确答案：()解析：采用外键子句约束： CREAT TABLE SC( SNUM CHAR(4)， CNUM CHAR(5)， CREDIT SMALLINT， SCORE SMALLINT， FOREIGN KEY (SNUM) REFERENCE S(SNUM) FOREIGN KEY (CNUM) REFERE
21、NCE C(CNUM) )(3).问题 3在关系 C中把某个 CNUM的值改为新的值的时候，必须同时要把关系 SC中那些同样的 CNUM的值也修改为新值。（分数：5.00）_正确答案：()解析：采用外键约束： CREATE TABLE SC( SNUM CHAR(4)， CNUM CHAR(5)， CREDIT SMALLINT， SCORE SMALLINT， FOREIGN KEY (CNUM) REFERENCE C(CNUM) ON UPDATE CASCADE， FOREIGN KEY(SNUM) REFERENCE S(SNUM)(4).问题 4为 T定义完整性规则，“教授的工资不得低于 2000元，如果低于 2000元，自动改为 2000元”。（分数：5.00）_正确答案：()解析：用数据库的触发器来实现： CREATE TRIGGER UPDATE SAL BEFORE INSERT OR UPDATE OF SAL，POS ON T FOR EACH ROW WHEN(:new.POS=教授) BEGIN IP:new.SAL
