中级数据库系统工程师下午试题-4

中级数据库系统工程师下午试题-4
(总分：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)，各属性分别表示员工工号、姓名、年龄、性别和职称级别，其中性别取值为“男”“女”；
公司表：COMPANY( Cno , Cname, City)，各属性分别表示公司编号、名称和所在城市；
工作表：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 __(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和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) ,
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, o, Cname, Salary
FROM EMP, COMPANY, WORKS
WHERE __(f)__ ;
(2)员工的工资由职称级别的修改自动调整，需要用触发器来实现员工工资的自动维护，函数float Salary_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 o=o 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, Ename, Cno, Cname, Salary)
AS
SELECT EMP.Eno, Ename, o, Cname, Salary
FROM EMP, COMPANY, WORKS
WHERE EMP.Eno=WORKS.Eno AND o=o AND Sex=
"女";
(2)创建触发器可通过CREATE TRIGGER语句实现，要求考生掌握触发器的基本语法结构。

按照问题要求，在工资关系中更新职工职称级别时触发器应自动执行，故需要创建基于UPDATE类型的触发器，其触发条件是更新职工职称级别；最后添加表连接条件。

完整的触发器实现的方案如下：
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 o, Cname
FROM COMPANY, WORKS
WHERE o=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)o, Cname
(1)COUNT(*)＞=ALL
(2)(m)NOT IN 或＜＞ANY (注：两者填一个即可)
(n)PANY
(o)o=o [解析] SQL查询通过SELECT语句实现。

(1)根据问题要求，可通过子查询实现“查询员工最多的公司编号和公司名称”的查询；对COUNT函数计算的结果应通过HAVING条件语句进行约束；通过Cno和Cname的组合来进行分组查询。

完整的SQL语句如下：SELECT o, Cname
FROM COMPANY, WORKS
WHERE o=o
GROUP BY o, Cname
HAVING COUNT(*) ＞=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 o=o
AND Cname="中国银行北京分行"
);
三、试题三(总题数：1，分数：20.00)
假设某商业集团数据库中有一关系模式R如下：
R(商店编号，商品编号，数量，部门编号，负责人)
如果规定：
①每个商店的每种商品只在一个部门销售。

②每个商店的每个部门只有一个负责人。

③每个商店的每种商品只有一个库存数量。

试回答下列问题：（分数：20.00）
(1).根据上述规定，写出关系模式R的基本函数依赖。

（分数：5.00）
__________________________________________________________________________________________ 正确答案：()
解析：有三个函数依赖：
(商店编号，商品编号)→部门编号
(商店编号，部门编号)→负责人
(商店编号，商品编号)→数量
(2).找出关系模式R的候选键。

（分数：5.00）
__________________________________________________________________________________________ 正确答案：()
解析：R的候选键是：
(商店编号，商品编号)
(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)，其属性是仓库编号、仓库名称和地址。

存储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(w)
∧u[1]=v[2]∧[1]：w[1]∧w[2]
=莘庄"∧"t[1]=u[1]∧t[2]=u[2])}
关系逻辑规则如下：
W(x，y)(GOODS(x，y，a)∧SG(b，x，c)∧ STORE(b，莘庄，"d)
SELECT语句为：
SELECT A．G#，GNAME
FROM GOODS AS A．SG AS B，STORE AS C
WHERE A．C#：B．C# AND B．S#=C．S# AND
SNAME="莘庄"：
(2).[问题2]
在基本表中，检索存储全部种类商品的仓库的编号及名称。

试写出相应的关系代数表达式、元组表达式、关系逻辑规则和SELECT语句。

（分数：5.00）
__________________________________________________________________________________________ 正确答案：()
解析：关系代数表达式为：
πS#.SNAME(STORE (πS#,G#(SG)÷πG# (GOODS)))
元组表达式为：{t}((u)((v)((w)(STORE(u)∧GOODS(v)∧SG(w)∧w[1]
=u[1)∧w[2]=v[1]∧t[1]=u[1]∧t [2]=u[2])}
关系逻辑规则如下：
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# = 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）
__________________________________________________________________________________________ 正确答案：()
解析：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所示：
根据上述描述，在入口处的伪代码程序为：
x=Get()；
IF x=NULL THEN return 0；
Writ(x,1)；（分数：15.00）
(1).[问题1]
若两辆车在不同的入口处同时执行上述代码，会出现什么问题? (100字以内描述)（分数：5.00）
__________________________________________________________________________________________ 正确答案：()
解析：若两辆车在不同的入口处同时执行代码，可能造成两辆车争用同一停车位。

即两车同时申请到一个停车位，并置为占用状态。

[解析] 本题考查并发控制的概念和应用，属于比较传统的题目，考查点也与往年类似。

[问题1]考查对并发情况下，会产生数据的不一致性问题，要求结合理论知识与实际问题，找出问题所在。

两车同时读空车位时，可能会读到同一空车位，进而占用造成突，与并发控制中的丢失修改错误相类似。

(2).[问题1]
为保证入口处伪代码正确地并发执行，引入共享锁指令SLock(T)和独占锁指令XLock(T)对表T进行加锁；Upgrade(T)对表T所加的共享锁升级为独占锁；解锁指令 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)会产生死锁，调度如下：
注：调度次序不局限于本参考答案，但执行语句不能写在一行，必须是并发而不能是串行，且在Upgrade 后等待。

[解析] [问题2]考查对2PL协议的掌握和实际应用的能力，考生要清楚何时回锁，加什么类型的锁，以及锁的升级等基本概念和方法，同时对2PL协议与死锁的关系要求有明确的认知。

2PL规定：读数据前加S锁，事务结束时释放；写数据前加X锁，事务结束时释放；当要修改数据时，事务应对自己所加的S锁升级为X锁。

2PL解决的是事务并发时的正确调度，并不能解决死锁，死锁是由DBMS进行检测和解除的。

(3).[问题3]
若停车位表的关系模式为：park(parkno,isused)，其中parkno为停车位号，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 SQLCA．sqlcode=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)要补充的是关闭游标和函数返回值部分。