银符考试题库-在线练习-中级数据库系统工程师下午试题模拟25

中级数据库系统工程师下午试题模拟25

试题一
阅读下列说明，回答问题1至问题5。
[说明]
某公司要对其投放的自动售货机建立商品管理系统，其数据库的部分关系模式如下：
售货机：VEM(VEMno, Location)，各属性分别表示售货机编号、部署地点；
商品：GOODS(Gno, Brand，Price)，各属性分别表示商品编号、品牌名和价格；
销售单：SALES(Sno, VEMno, Gno, sDate, sTime)，各属性分别表示销售号、售货机编号、
商品编号、日期和时间。 ………
缺货单：OOS(VEMno, Gno, sDate, sTime)，各属性分别表示售货机编号、商品编号、日期和
时间。
相关关系模式的属性及说明如下：
(1)售货机摆放固定种类的商品，售货机内每种商品最多可以储存10件。管理员在每天结束的时候将售货机中所有售出商品补全。
(2)每售出一件商品，就自动向销售单中添加一条销售记录。如果一天内某个售货机上某种商品的销售记录达到10条，则表明该售货机上该商品已售完，需要通知系统立即补货，通过自动向缺货单中添加一条缺货记录来实现。
根据以上描述，回答下列问题，将SQL语句的空缺部分补充完整。

1. [问题1]
请将下面创建销售单表的SQL语句补充完整，要求指定关系的主码和外码约束。
CREATE TABLE SALES (
Sno CHAR（8）______,
VEMno CHAR（5）______,
Gno CHAR（8）______,
SDate DATE,
STime TIME) ;

PRIMARY KEY
REFERENCES VEM(VEMno)
REFERENCES GOODS(Gno)

2. [问题2]
创建销售记录详单视图SALES_Detail，要求按日期统计每个售货机上各种商品的销售数量，属性有VEMno、Location、Gno、Brand、Price、amount和SDate。为方便实现，首先建立一个视图SALES_Total，然后利用SALES_Total完成视图SALES Detail的定义。
CREATE VIEW SALES_Total(VEMno, Gno, SDate, amount) AS
SELECT VEMno, Gno, SDate, Count(*)
FROM SALES
GROUP BY ______;
CREATE VIEW ______ AS
SELECT VEM.VEMno, Location, GOODS.Gno, Brand, Price, amount, SDate
FROM VEM, GOODS, SALES_Total
WHERE ______ AND ______;

VEMno，Gno，SDate
SALES_Detail(VEMno，Location，Gno，Brand，Price，amount，SDate)
VEM.VEMno=SALES_Total.VEMno
GOODS.Gno=SALES_Total.Gno
注：后两空可互换

3. [问题3]
每售出一件商品，就自动向销售单中添加一条销售记录。如果一天内某个售货机上某种商品的销售记录达到10条，则自动向缺货单中添加一条缺货记录。需要用触发器来实现缺货单的自动维护。程序中的GetTime()获取当前时间。
CREATE ______ OOS_TRG AFTER ______ ON SALES
REFERENCING new row AS nrow
FOR EACH ROW
BEGIN
INSERT INTO OOS
SELECT SALES.VEMno, ______, GetTime()
FROM SALES
WHERE SALES.VEMno = nrow.VEMno AND SALES.Gno = nrow. Gno
AND SALES.SDate = nrow. SDate
GROUP BY SALES.VEMno, SALES.Gno, SALES.SDate
HAVING count(*) ＞ 0 AND mod(count(*), 10) = 0;
END

TRIGGER
INSERT
SALES.Gno，SALES.SDate

4. [问题4]
查询当天销售最多的商品编号、品牌和数量。程序中的GetDate()获取当天日期。
SELECT GOODS.Gno, Brand, ______
FROM GOODS, SALES
WHERE GOODS.Gno = SALES.Gno AND SDate = GetDate()
GROUP BY ______
HAVING ______ ( SELECT count(*)
FROM SALES
WHERE SDate = GetDate()
GROUP BY Gno );

count(*)
GOODS.Gno，Brand
count(*)＞=ALL

5. [问题5]
查询一件都没有售出的所有商品编号和品牌。
SELECT Gno, Brand
FROM GOODS
WHERE Gno ______ (
SELECT DISTINCT Gno
FROM ______);

NOT IN或＜＞ANY(注：两者填其一个即可)
SALES

[解析]
本题考查SQL应用，是比较传统的题目，要求考生细心分析题目中所描述的内容。
[问题1]
本问题考查SOL数据定义语言DDL和完整性约束。
完整性约束包括三类，实体完整性、参照完整性和用户定义的完整性。实体完整性约束规定关系的主属性不能取空值，关系模型中以主码作为唯一性标识；参照完整性约束规定：若属性(或属性组)A是关系R上的主码，B是关系S上的外码，A与B相对应(来自相同的域)，则B取值为空或者来自于R上的某个A的值；用户定义的完整性约束是针对具体的数据库应用而定义的，它反映该应用所涉及的数据必须满足用户定义的语义要求。
第一空考查实体完整性约束，Sno是SALES的主码，用关键字PRIMARY KEY约束。第二空和第三空考查参照完整性约束，VEMno属性参照VEM关系模式中的VEMno属性，Gno属性参照GOODS关系模式中的Gno属性，空白处分别填入“REFERENCESVEM(VEMno)”和“REFERENCESGOODS(Gno)”。
[问题2]
本问题考查SQL创建视图的操作及应用。
需创建的第一个视图SALES Total(VEMno，Gno，SDate，amount)来自SALES表，属性分别对应SALES表中的VEMno、Gno、SDate、Count(*)，其中，Count(*)要对SALES表中的VEMno、Gno、SDate进行分组计数，因此空第四空应填入“VEMno，Gno，SDate”。
需要创建的第二个视图SALES_Detail基于第一个已经创建的视图SALES_TOTAL和基本表VEM、GOODS。因此只需要把题意中需要统计的VEMno、Location、Gno、Brand、Price、amount、SDate从上述三个表中提取出来即可。第五空应填入“SALES_Detail(VEMno，Location，Gno，Brand，Price，amount，SDate)”。
WHERE条件后需要将三个表按照公共属性列连接起来，因此第五空和第七空分别填入“VEM.VEMno=SALES_Total.VEMno”和“GOODS.Gno=SALES_Total.Gno”即可(顺序可以互换)。
[问题3]
本问题考查触发器的设计与应用。
触发器是一个能由系统自动执行对数据库修改的语句。一个触发器由三部分组成：①事件，即对数据库的插入、删除和修改等操作。触发器在这些事件发生时，将开始工作：②条件，触发器将测试条件是否成立，若成立就执行相应的动作，否则就什么也不做；③动态，若触发器测试满足预定的条件，那么就由数据库管理系统执行这些动作。本题判断销售记录达到10条，则自动向缺货单中添加一条缺货记录。因此第八空处应填入“TRIGGER”，第九空处应填入“INSERT”，而第十空处应填入SALES表中的两个必要属性“SALES.Gno”和“SALES.SDate”。
[问题4]
本问题考查SQL的查询操作。
题意要求查询当天销售最多的商品编号、品牌和数量。SELECT语句后缺少数量，可以用count(*)来对分组后的商品销售数量进行统计。GROUP BY分组条件是商品号和品牌的组合，也就是GOODS.Gno和Brand。需要统计销售最多的商品，只需要在嵌套子查询前面使用count(*)＞=ALL即可达到目的。
[问题5]
本问题考查SQL的查询操作。
题意要求查询一件都没有售出的所有商品编号和品牌，因此外层查询的Gno不在销售表中即可，第十一空处应填“NOTIN”或“＜＞ANY”。内层子查询统计的Gno来自于销售表SALES，因此第十二空处应填“SALES”。

试题二
[说明]
现有某个应用，涉及到两个实体集，相关的属性为：
实体集R(A#，A1，A2，A3)，其中A#为主键
实体集S(B#，B1，B2)，其中B#为主键
从实体集R到S存在多对一的联系，联系属性是D1。

1. [问题1]
设计相应的关系数据模型；

R1(A#，A1，A2，A3，B#，D1)
R2(B#，B1，B2)

2. [问题2]
如果将上述应用的数据库设计为一个关系模式，如下：
RS(A#，A1，A2，A3，B#，B1，B2，D1)
指出该关系模式的主键。

主键是A#B#

3. [问题3]
假设上述关系模式RS上的全部函数依赖为：A1→A3，指出上述模式RS最高满足第几范式?(在1NF～BCNF之内)为什么?

RS满足2NF，不满足3NF。
因为存在非主属性A3对码A#B#的传递依赖，没有部分函数依赖。

4. [问题4]
如果将上述应用的数据库设计为三个关系模式，如下：
R1(A#，A1，A2，A3)
R2(B#，B1，B2)；
R3(A#，B#，D1)
关系模式R2是否一定满足第3范式?为什么?

不一定。
因为R3中有两个非主属性B1和B2，有可能存在函数依敕B1→B2，则出现传递依赖B#→B1、B1→B2。

试题三
阅读下列说明，回答下列问题。
[说明]
某大型集团公司的数据库的部分关系模式如下：
员工表：EMP(Eno, Ename, Age, Sex, Title)，各属性分别表示员工工号、姓名、年龄、性别和职称级别，其中性别取值为“男”“女”；
公司表：COMPANY(Cno, Cname, City)，各属性分别表示公司编号、名称和所在城市；
工作表：WORKS(Eno, Cno, Salary)，各属性分别表示职工工号、工作的公司编号和工资。
有关关系模式的属性及相关说明如下：
(1)允许一个员工在多家公司工作，使用身份证号作为工号值。
(2)工资不能低于1500元。
根据以上描述，回答下列问题：

1. 请将下面创建工作关系的SQL语句的空缺部分补充完整，要求指定关系的主码、外码，以及工资不能低于1500元的约束。
CREATE TABLE WORKS (
Eno CHAR(10) (a) ,
Cno CHAR(4) (b) ,
Salary int (c) ,
PRIMARY KEY (d) ,
);

(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, COMPANY.Cno, 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

(1)(e)VIEW FemaleEMP(Eno, Ename, Cno, Cname, Salary)
(f)EMP.Eno=WORKS.Eno AND COMPANY.Cno=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, Ename, 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类型的触发器，其触发条件是更新职工职称级别；最后添加表连接条件。完整的触发器实现的方案如下：
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.Cno
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='中国银行北京分行'
);

(1)(k)COMPANY.Cno, Cname
(1)COUNT(*)＞=ALL
(2)(m)NOT IN 或＜＞ANY (注：两者填一个即可)
(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(*) ＞=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='中国银行北京分行'
);

试题四
阅读下列说明，回答问题1至问题3。
[说明]
某房屋租赁公司拟开发一个管理系统用于管理其持有的房屋、租客及员工信息。请根据下述需求描述完成系统的数据库设计。
[需求描述]
1．公司拥有多幢公寓楼，每幢公寓楼有唯一的楼编号和地址。每幢公寓楼中有多套公寓，每套公寓在楼内有唯一的编号(不同公寓楼内的公寓号可相同)。系统需记录每套公寓的卧室数和卫生间数。
2．员工和租客在系统中有唯一的编号(员工编号和租客编号)。
3．对于每个租客，系统需记录姓名、多个联系电话、一个银行账号(方便自动扣房租)、一个紧急联系人的姓名及联系电话。
4．系统需记录每个员工的姓名、一个联系电话和月工资。员工类别可以是经理或维修工，也可兼任。每个经理可以管理多幢公寓楼。每幢公寓楼必须由一个经理管理。系统需记录每个维修工的业务技能，如水暖维修、电工、木工等。
5．租客租赁公寓必须和公司签定租赁合同。一份租赁合同通常由一个或多个租客(合租)与该公寓楼的经理签定，一个租客也可租赁多套公寓。合同内容应包含签定日期、开始时间、租期、押金和月租金。
[概念模型设计]
根据需求阶段收集的信息，设计的实体联系图(不完整)如图1所示。

图1 实体联系图

[逻辑结构设计]
根据概念模型设计阶段完成的实体联系图，得出如下关系模式(不完整)：
联系电话(电话号码，租客编号)
租客(租客编号，姓名，银行账号，联系人姓名，联系人电话)
员工(员工编号，姓名，联系电话，类别，月工资，______)
公寓楼(______，地址，经理编号)
公寓(楼编号，公寓号，卧室数，卫生间数)
合同(合同编号，租客编号，楼编号，公寓号，经理编号，签订日期，起始日期，租期，______，押金)

1. [问题1]
补充图1中的“签约”联系所关联的实体及联系类型。

补充内容如图2中虚线所示。

图2

2. [问题2]
补充逻辑结构设计中的三处空缺。

(a)业务技能
(b)楼编号
(c)月租金

3. [问题3]
在租期内，公寓内设施如出现问题，租客可在系统中进行故障登记，填写故障描述，每项故障由系统自动生成唯一的故障编号，由公司派维修工进行故障维修，系统需记录每次维修的维修日期和维修内容。请根据此需求，对图1进行补充，并将所补充的ER图内容转换为一个关系模式，请给出该关系模式。

ER图的补充方式不唯一，补充内容如图3或图4中的虚线所示。
关系模式：维修记录(故障编号，租客编号，楼编号，公寓号，故障描述，员工编号，维修日期，维修内容)
备注：此联系名称能够合理表达需求即可。

图3 ER图一

图4 ER图二

[解析] 本题考查数据库概念设计及逻辑设计中E．R图与关系模式的转换方法。
此类题目要求考生认真阅读题目中对需求问题的描述，用分类、聚集、概括等方法，来确定实体及其联系。题目已经给出了6个实体以及部分实体之间的联系，需要根据需求描述，将实体之间的联系补充完整。
[问题1]
题目中已经给出了租客与公寓间的租赁关系，由“一份租赁合同通常由一个或多个租客(合租)与该公寓楼的经理签订”可知，需要建立经理和“租客与公寓问的租赁关系”之间的联系，即将联系作为实体，参与下一次联系，使用聚合的方法。因此，解答如参考答案所示图的虚线部分所示。
[问题2]
从需求描述4中的“系统需记录每个维修工的业务技能”，可知员工的属性信息需要业务技能属性。由需求1中“每幢公寓楼有唯一的楼编号和地址”，可知楼编号是唯一的，不会重复，可作为公寓楼的主键属性。需求5说明了合同的属性信息中包含签订日期、开始时间、租期、押金、月租金，因此模式中还缺少月租金属性。完整的关系模式如下：
联系电话(电话号码，租客编号)
租客(租客编号，姓名，银行账号，联系人姓名，联系人电话)
员工(员工编号，姓名，联系电话，类别，月工资，业务技能)
公寓楼(楼编号，地址，经理编号)
公寓(楼编号，公寓号，卧室数，卫生问数)
合同(合同编号，租客编号，楼编号，公寓号，经理编号，签订日期，起始日期，租期，月租金，押金)
[问题3]
此题E-R图不唯一，这里给出两种备选的解答。
答案一：由“公寓内设施如出现问题，租客可在系统中进行故障登记”，但公寓出现问题的次数不止一次，可知租客和公寓之间存在着m:n联系。系统故障生成之后会派维修工进行维修，因此可建立维修工和特定故障记录之间的联系。
答案二：也可直接建立租客、公寓和维修工之间的三元联系。

试题五
假设某商业集团数据库中有一关系模式R如下：
R(商店编号，商品编号，数量，部门编号，负责人)
如果规定：
①每个商店的每种商品只在一个部门销售。
②每个商店的每个部门只有一个负责人。
③每个商店的每种商品只有一个库存数量。
试回答下列问题：

1. 根据上述规定，写出关系模式R的基本函数依赖。

有三个函数依赖：
(商店编号，商品编号)→部门编号
(商店编号，部门编号)→负责人
(商店编号，商品编号)→数量

2. 找出关系模式R的候选键。

R的候选键是：
(商店编号，商品编号)

3. 关系模式R最高已经达到第几范式?为什么?

因为R中存在着非主属性“负责人”对候选键(商店编号，商品编号)的传递函数依赖，所以R属于2NF，而不属于3NF。

4. 如果R不属于3NF，请将R分解成3NF模式集。

将R分解成：
R1(商店编号，商品编号，数量，部门编号)
R2(商店编号，部门编号，负责人)