1、中级数据库系统工程师下午试题-4 及答案解析(总分：74.00，做题时间：90 分钟)一、试题一(总题数：1，分数：4.00)1.【问题 1】 使用【说明】中给出的词汇，将数据流图 1-1 中(1)(4)处的数据流补充完整。 （分数：4.00）_二、试题二(总题数：1，分数：15.00)阅读下列说明，回答下列问题。 说明 某大型集团公司的数据库的部分关系模式如下： 员工表：EMP( Eno , Ename, Age, Sex, Title)，各属性分别表示员工工号、姓名、年龄、性别和职称级别，其中性别取值为“男”“女”； 公司表：COMPANY( Cno , Cname, City)，各属性分
2、别表示公司编号、名称和所在城市； 工作表：WORKS( Eno, Cno , Salary)，各属性分别表示职工工号、工作的公司编号和工资。 有关关系模式的属性及相关说明如下： (1)允许一个员工在多家公司工作，使用身份证号作为工号值。 (2)工资不能低于 1500 元。 根据以上描述，回答下列问题：（分数：15.00）(1).请将下面创建工作关系的 SQL 语句的空缺部分补充完整，要求指定关系的主码、外码，以及工资不能低于 1500 元的约束。 CREATE TABLE WORKS ( Eno CHAR(10) _(a)_ , Cno CHAR(4) _(b)_ , Salary int _
3、(c)_ , PRIMARY KEY _(d)_ , );（分数：5.00）_(2).(1)创建女员工信息的视图 FemaleEMP，属性有 Eno、Ename、Cno、Cname 和 Salary，请将下面 SQL 语句的空缺部分补充完整。 CREATE _(e)_ AS SELECT EMP.Eno, Ename, COMPANY.Cno, Cname, Salary FROM EMP, COMPANY, WORKS WHERE _(f)_ ; (2)员工的工资由职称级别的修改自动调整，需要用触发器来实现员工工资的自动维护，函数 float Salary_value(char(10)Eno
4、)依据员工号计算员工新的工资。请将下面 SQL 语句的空缺部分补充完整。 CREATE _(g)_ Salary TRG AFTER _(h)_ ON EMP REFERENCING new row AS nrow FOR EACH ROW BEGIN UPDATE WORKS SET _(i)_ WHERE _(j)_ ; END（分数：5.00）_(3).请将下面 SQL 语句的空缺部分补充完整。 (1)查询员工最多的公司编号和公司名称。 SELECT COMPANY.Cno, Cname FROM COMPANY, WORKS WHERE COMPANY.Cno=WORKS.Cno GR
5、OUP BY _(k)_ HAVING _(l)_ (SELECT COUNT(*) FROM WORKS GROUP BY Cno ); (2)查询所有不在“中国银行北京分行”工作的员工工号和姓名。 SELECT Eno, Ename FROM EMP WHERE Eno _(m)_ ( SELECT Eno FROM _(n)_ WHERE _(o)_ AND Cname=“中国银行北京分行“ );（分数：5.00）_三、试题三(总题数：1，分数：20.00)假设某商业集团数据库中有一关系模式 R 如下： R(商店编号，商品编号，数量，部门编号，负责人) 如果规定： 每个商店的每种商品只在
6、一个部门销售。 每个商店的每个部门只有一个负责人。 每个商店的每种商品只有一个库存数量。 试回答下列问题：（分数：20.00）(1).根据上述规定，写出关系模式 R 的基本函数依赖。（分数：5.00）_(2).找出关系模式 R 的候选键。（分数：5.00）_(3).关系模式 R 最高已经达到第几范式?为什么?（分数：5.00）_(4).如果 R 不属于 3NF，请将 R 分解成 3NF 模式集。（分数：5.00）_四、试题四(总题数：1，分数：20.00)说明 设某商业集团为仓库存储商品设计了三个基本表： 仓库 STORE(S#，SNAME，SADDR)，其属性是仓库编号、仓库名称和地址。 存
7、储 SG(S#，C#，QUANTITY)，其属性是仓库编号、商品编号和数量。 商品 GOODS(C#，GNAME，PRICE)，其属性是商品编号、商品名称和单价。 现检索仓库名称为“莘庄”的仓库里存储的商品的编号和名称。（分数：20.00）(1).问题 1 试写出相应的关系代数表达式、元组表达式、关系逻辑规则和 SELECT 语句。（分数：5.00）_(2).问题 2 在基本表中，检索存储全部种类商品的仓库的编号及名称。试写出相应的关系代数表达式、元组表达式、关系逻辑规则和 SELECT 语句。（分数：5.00）_(3).问题 3 在基本表中，检索每个仓库存储商品的总价值。试写出相应的 SEL
8、ECT 语句。要求显示(S#，SUM_VALUE)，其属性为仓库编号及该库存储商品的总价值。（分数：5.00）_(4).问题 4 在基本表中，写一个断言，规定每个仓库存储商品的单价为 1 万元以上的商品种类最多为 20 种。（分数：5.00）_五、试题五(总题数：1，分数：15.00)说明 某停车场有多个入口和出口，车辆进入时从入口处由系统查询可用的停车位，从出口驶出时系统将其刚使用的车位标记为空车位。 假设实现停车场管理的伪指令如下表 1 所示： （分数：15.00）(1).问题 1 若两辆车在不同的入口处同时执行上述代码，会出现什么问题? (100 字以内描述)（分数：5.00）_(2).
9、问题 1 为保证入口处伪代码正确地并发执行，引入共享锁指令 SLock(T)和独占锁指令 XLock(T)对表 T 进行加锁；Upgrade(T)对表 T 所加的共享锁升级为独占锁；解锁指令 Unlock(T)对表 T 进行解锁。 (1)请修改上述入口处的伪代码程序，使其满足 2PL 协议。 (2)满足 2PL 协议的入口处的伪代码程序，在并发执行时是否会产生死锁?若是，给出一个产生死锁的调度。（分数：5.00）_(3).问题 3 若停车位表的关系模式为：park(parkno,isused)，其中 parkno 为停车位号，isused 为停车位标志，0 为空，1 为非空。 下面是用 E-S
10、QL 实现的查询空车位的函数 Get()，请补全空缺处的代码。 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE EXEC SQL DECLARE getblk CURSOR FOR _(a)_ ； EXEC SQL OPEN getblk； EXEC SQL FETCH getblk INTO：Hparkno；/Hparkno 为已声明的主变量 IF SQLCAsqlcode=100 THEN EXEC SQL CLOSE getblk； Return NULL； ELSE _(b)_ ； END IF（分数：5.00）中级数据库系统工程师下午试题-4
11、 答案解析(总分：74.00，做题时间：90 分钟)一、试题一(总题数：1，分数：4.00)1.【问题 1】 使用【说明】中给出的词汇，将数据流图 1-1 中(1)(4)处的数据流补充完整。 （分数：4.00）_正确答案：()解析：费用单 (2)待租赁房屋列表 (3)看房请求 (4)变更房屋状态请求二、试题二(总题数：1，分数：15.00)阅读下列说明，回答下列问题。 说明 某大型集团公司的数据库的部分关系模式如下： 员工表：EMP( Eno , Ename, Age, Sex, Title)，各属性分别表示员工工号、姓名、年龄、性别和职称级别，其中性别取值为“男”“女”； 公司表：COMPA
12、NY( Cno , Cname, City)，各属性分别表示公司编号、名称和所在城市； 工作表：WORKS( Eno, Cno , Salary)，各属性分别表示职工工号、工作的公司编号和工资。 有关关系模式的属性及相关说明如下： (1)允许一个员工在多家公司工作，使用身份证号作为工号值。 (2)工资不能低于 1500 元。 根据以上描述，回答下列问题：（分数：15.00）(1).请将下面创建工作关系的 SQL 语句的空缺部分补充完整，要求指定关系的主码、外码，以及工资不能低于 1500 元的约束。 CREATE TABLE WORKS ( Eno CHAR(10) _(a)_ , Cno C
13、HAR(4) _(b)_ , Salary int _(c)_ , PRIMARY KEY _(d)_ , );（分数：5.00）_正确答案：()解析：(a)REFERENCES EMP(Eno) (b)REFERENCES COMPANY(Cno) (c)CHECK(Salary=1500) (d)(Eno, Cno) 解析 本题考查 SQL 语句的应用。 此类题目要求考生掌握 SQL 语句的基本语法和结构，认真阅读题目给出的关系模式，针对题目的要求具体分析并解答。本试题已经给出了 3 个关系模式，需要分析每个实体的属性特征及实体之间的联系，补充完整 SQL 语句。 由题目说明可知，Eno
14、和 Cno 两个属性组合是 WORKS 关系表的主键，所以在 PRIMARY KEY 后填的应该是(Eno, Cno)组合；Eno 和 Cno 分别作为外键引用到 EMP 和 COMPANY 关系表的主键，因此需要用 REFERENCES对这两个属性进行外键约束：由“工资不能低于 1500 元”的要求，可知需要限制账户余额属性值的范围，通过 CHECK 约束来实现。从上述分析可知，完整的 SQL 语句如下： CREATE TABLE WORKS ( Eno CHAR(10) REFERENCES EMP(Eno) , Cno CHAR(4) REFERENCES COMPANY(Cno) ,
15、Salary int CHEKCK (Salary =1500) , PRIMARY KEY (Eno, Cno) , );(2).(1)创建女员工信息的视图 FemaleEMP，属性有 Eno、Ename、Cno、Cname 和 Salary，请将下面 SQL 语句的空缺部分补充完整。 CREATE _(e)_ AS SELECT EMP.Eno, Ename, COMPANY.Cno, Cname, Salary FROM EMP, COMPANY, WORKS WHERE _(f)_ ; (2)员工的工资由职称级别的修改自动调整，需要用触发器来实现员工工资的自动维护，函数 float S
16、alary_value(char(10)Eno)依据员工号计算员工新的工资。请将下面 SQL 语句的空缺部分补充完整。 CREATE _(g)_ Salary TRG AFTER _(h)_ ON EMP REFERENCING new row AS nrow FOR EACH ROW BEGIN UPDATE WORKS SET _(i)_ WHERE _(j)_ ; END（分数：5.00）_正确答案：()解析：(1)(e)VIEW FemaleEMP(Eno, Ename, Cno, Cname, Salary) (f)EMP.Eno=WORKS.Eno AND COMPANY.Cno=
17、WORKS.Cno AND Sex=“女“ (2)(g)TRIGGER (h)UPDATE (i)Salary=Salary_value(nrow.Eno) (i)WORKS.Eno=nrow.Eno 解析 (1)创建视图需要通过 CREATE VIEW 语句来实现，由题目可知视图的属性有(Eno, Ename, Cno, Cname, Salary)；通过公共属性列 Eno 和 Cno 对使用的三个基本表进行连接；由于只创建女员工的试图，所以还要在 WHERE 后加入“Sex=“女“”的条件。从上分析可见，完整的 SQL 语句如下： CREATE VIEW FemaleEMP (Eno, E
18、name, Cno, Cname, Salary) AS SELECT EMP.Eno, Ename, COMPANY.Cno, Cname, Salary FROM EMP, COMPANY, WORKS WHERE EMP.Eno=WORKS.Eno AND COMPANY.Cno=WORKS.Cno AND Sex=“女“; (2)创建触发器可通过 CREATE TRIGGER 语句实现，要求考生掌握触发器的基本语法结构。按照问题要求，在工资关系中更新职工职称级别时触发器应自动执行，故需要创建基于 UPDATE 类型的触发器，其触发条件是更新职工职称级别；最后添加表连接条件。完整的触发器
19、实现的方案如下： CREATE TRIGGER Salary_TRG AFTER UPDATE ON EMP REFERENCING new row AS nrow FOR EACH ROW BEGIN UPDATE WORKS SET Salary=Salary value (nrow.Eno) WHERE WORK. Eno=nrow. Eno ; END(3).请将下面 SQL 语句的空缺部分补充完整。 (1)查询员工最多的公司编号和公司名称。 SELECT COMPANY.Cno, Cname FROM COMPANY, WORKS WHERE COMPANY.Cno=WORKS.Cn
20、o GROUP BY _(k)_ HAVING _(l)_ (SELECT COUNT(*) FROM WORKS GROUP BY Cno ); (2)查询所有不在“中国银行北京分行”工作的员工工号和姓名。 SELECT Eno, Ename FROM EMP WHERE Eno _(m)_ ( SELECT Eno FROM _(n)_ WHERE _(o)_ AND Cname=“中国银行北京分行“ );（分数：5.00）_正确答案：()解析：(1)(k)COMPANY.Cno, Cname (1)COUNT(*)=ALL (2)(m)NOT IN 或 ANY (注：两者填一个即可) (
21、n)WORKS.COMPANY (o)WORKS.Cno=COMPANY.Cno 解析 SQL 查询通过 SELECT 语句实现。 (1)根据问题要求，可通过子查询实现“查询员工最多的公司编号和公司名称”的查询；对 COUNT 函数计算的结果应通过 HAVING 条件语句进行约束；通过 Cno 和 Cname 的组合来进行分组查询。完整的 SQL 语句如下： SELECT COMPANY.Cno, Cname FROM COMPANY, WORKS WHERE COMPANY.Cno=WORKS.Cno GROUP BY COMPANY.Cno, Cname HAVING COUNT(*) =
22、ALL (SELECT COUNT(*) FROM WORKS GROUP BY Cno ); (2)根据问题要求，需要使用嵌套查询。先将 WORKS 和 COMPANY 表进行连接，查找出所有在“中国银行北京分行”工作的员工；然后在雇员表中使用“NOTIN”或者“ANY”查询不在前述结果里面的员工即可。完整的 SQL 语句如下： SELECT Eno, Ename FROM EMP WHERE Eno NOT IN 或 ANY ( SELECT Eno FROM WORKS, COMPANY WHERE WORKS.Cno=COMPANY.Cno AND Cname=“中国银行北京分行“ )
23、;三、试题三(总题数：1，分数：20.00)假设某商业集团数据库中有一关系模式 R 如下： R(商店编号，商品编号，数量，部门编号，负责人) 如果规定： 每个商店的每种商品只在一个部门销售。 每个商店的每个部门只有一个负责人。 每个商店的每种商品只有一个库存数量。 试回答下列问题：（分数：20.00）(1).根据上述规定，写出关系模式 R 的基本函数依赖。（分数：5.00）_正确答案：()解析：有三个函数依赖： (商店编号，商品编号)部门编号 (商店编号，部门编号)负责人 (商店编号，商品编号)数量(2).找出关系模式 R 的候选键。（分数：5.00）_正确答案：()解析：R 的候选键是： (
24、商店编号，商品编号)(3).关系模式 R 最高已经达到第几范式?为什么?（分数：5.00）_正确答案：()解析：因为 R 中存在着非主属性“负责人”对候选键(商店编号，商品编号)的传递函数依赖，所以 R 属于2NF，而不属于 3NF。(4).如果 R 不属于 3NF，请将 R 分解成 3NF 模式集。（分数：5.00）_正确答案：()解析：将 R 分解成： R1(商店编号，商品编号，数量，部门编号) R2(商店编号，部门编号，负责人)四、试题四(总题数：1，分数：20.00)说明 设某商业集团为仓库存储商品设计了三个基本表： 仓库 STORE(S#，SNAME，SADDR)，其属性是仓库编号、
25、仓库名称和地址。 存储 SG(S#，C#，QUANTITY)，其属性是仓库编号、商品编号和数量。 商品 GOODS(C#，GNAME，PRICE)，其属性是商品编号、商品名称和单价。 现检索仓库名称为“莘庄”的仓库里存储的商品的编号和名称。（分数：20.00）(1).问题 1 试写出相应的关系代数表达式、元组表达式、关系逻辑规则和 SELECT 语句。（分数：5.00）_正确答案：()解析：关系代数表达式为： 解析 关系代数表达式为： G#,GNAME ( SNAME=“莘庄“ (STORE SG GOODS) 元组表达式为： t |(u)(v)(w)(COODS(u)SC(v) STOBE(
26、w) u1=v21：w1w2 =莘庄“t1=u1t2=u2) 关系逻辑规则如下： W(x，y)(GOODS(x，y，a)SG(b，x，c) STORE(b，莘庄，“d) SELECT 语句为： SELECT AG#，GNAME FROM GOODS AS ASG AS B，STORE AS C WHERE AC#：BC# AND BS#=CS# AND SNAME=“莘庄“：(2).问题 2 在基本表中，检索存储全部种类商品的仓库的编号及名称。试写出相应的关系代数表达式、元组表达式、关系逻辑规则和 SELECT 语句。（分数：5.00）_正确答案：()解析：关系代数表达式为： S#.SNAME
27、(STORE ( S#,G# (SG) G# (GOODS) 元组表达式为： t(u)(v)(w)(STORE(u)GOODS(v)SG(w)w1 =u1)w2=v1t1=u1t 2=u2) 关系逻辑规则如下： W(x,y) STORE(x,y,a) GOODS(b,c, d) SG(x,b,e) SELECT 语句为: SELECT S#, SNAME FROM STORE WHERE NOT EXISTS ( SELECT * FROM GOODS WHERE NOT EXISTS ( SELECT * FROM SG WHERE SG. S# = STORE. S# AND SG. G#
28、 = GOODS. G#) );(3).问题 3 在基本表中，检索每个仓库存储商品的总价值。试写出相应的 SELECT 语句。要求显示(S#，SUM_VALUE)，其属性为仓库编号及该库存储商品的总价值。（分数：5.00）_正确答案：()解析：SELECT 语句为: SELECT S#, SUM ( QUANTITY * PRICE) AS SUM_VALUE FROM SG,GOODS WHERE SG. G# = GOODS. G# GROUP BY S#;(4).问题 4 在基本表中，写一个断言，规定每个仓库存储商品的单价为 1 万元以上的商品种类最多为 20 种。（分数：5.00）_正
29、确答案：()解析：CREATE ASSERTION ASSE6 CHECK (20 = ALL ( SELECT COUNT( SG. G#) FROM SG, GOODS WHERE SG. G# = GOODS. G# AND PRICE 10000 GROUP BY S#)；五、试题五(总题数：1，分数：15.00)说明 某停车场有多个入口和出口，车辆进入时从入口处由系统查询可用的停车位，从出口驶出时系统将其刚使用的车位标记为空车位。 假设实现停车场管理的伪指令如下表 1 所示： （分数：15.00）(1).问题 1 若两辆车在不同的入口处同时执行上述代码，会出现什么问题? (100 字
30、以内描述)（分数：5.00）_正确答案：()解析：若两辆车在不同的入口处同时执行代码，可能造成两辆车争用同一停车位。即两车同时申请到一个停车位，并置为占用状态。 解析 本题考查并发控制的概念和应用，属于比较传统的题目，考查点也与往年类似。 问题 1考查对并发情况下，会产生数据的不一致性问题，要求结合理论知识与实际问题，找出问题所在。两车同时读空车位时，可能会读到同一空车位，进而占用造成突，与并发控制中的丢失修改错误相类似。(2).问题 1 为保证入口处伪代码正确地并发执行，引入共享锁指令 SLock(T)和独占锁指令 XLock(T)对表 T 进行加锁；Upgrade(T)对表 T 所加的共享
31、锁升级为独占锁；解锁指令 Unlock(T)对表 T 进行解锁。 (1)请修改上述入口处的伪代码程序，使其满足 2PL 协议。 (2)满足 2PL 协议的入口处的伪代码程序，在并发执行时是否会产生死锁?若是，给出一个产生死锁的调度。（分数：5.00）_正确答案：()解析：(1)满足 2PL 协议的伪代码： SLock(T)； x=Get()； IF x=NULL THEN return 0； Upgrade(T) Writ(x，1)； Unlock(T)； (2)会产生死锁，调度如下： (3).问题 3 若停车位表的关系模式为：park(parkno,isused)，其中 parkno 为停车
32、位号，isused 为停车位标志，0 为空，1 为非空。 下面是用 E-SQL 实现的查询空车位的函数 Get()，请补全空缺处的代码。 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE EXEC SQL DECLARE getblk CURSOR FOR _(a)_ ； EXEC SQL OPEN getblk； EXEC SQL FETCH getblk INTO：Hparkno；/Hparkno 为已声明的主变量 IF SQLCAsqlcode=100 THEN EXEC SQL CLOSE getblk； Return NULL； ELSE _(b)_ ； END IF（分数：5.00）解析：(a)SELECT parkno FROM park WHERE isused=0； (b)EXEC SQL CLOSE getblk； Return：Hparkno； 解析 问题 3考查对 2PL 协议理论与 SQL 中的隔离级别，以及嵌入式 SQL 的编程实践。 空缺(a)要补充的是游标定义中的查询部分；空缺(b)要补充的是关闭游标和函数返回值部分。
