中级数据库系统工程师上半下午试题答案及详细解析Word下载.doc
《中级数据库系统工程师上半下午试题答案及详细解析Word下载.doc》由会员分享,可在线阅读,更多相关《中级数据库系统工程师上半下午试题答案及详细解析Word下载.doc(14页珍藏版)》请在冰点文库上搜索。
![中级数据库系统工程师上半下午试题答案及详细解析Word下载.doc](https://file1.bingdoc.com/fileroot1/2023-4/30/a7b25e93-dab1-455e-a65b-8c470beeae81/a7b25e93-dab1-455e-a65b-8c470beeae811.gif)
采购处到D2需要一条数据流,因为采购处理需要更改库存信息;
供货请求处理到D4需要一条数据流,因为供货请求处理需要记录供货请求信息。
参考答案
E1:
财务部门E2:
采购部门
E3:
连锁超市E4.配送部门
D1:
采购清单记录文件D2:
商品库存记录文件
D3.商品配送记录文件D4:
供货请求记录文件
[问题2]
试题二
阅读下列说明,回答问题1至问题3,将解答填入答题纸的对应栏内。
某网上书店后台数据库的部分关系模式如下:
会员(会员编号,用户名,密码,姓名;
地址,邮编,电话,消费额,积分)
图书(图书编号,类型名称,图书名称,作者,出版社,出版日期,ISBN,价格)
订单(订单编号,用户名,销售额,订购日期,出货日期)
订单明细(订单明细编号,订单编号,图书编号,数量)
下面是创建订单关系的SQL语句,订单编号唯一识别一个订单,用户名为订购图书的会员用户名,且不能为空。
要求订购日期不能大于出货日期。
请将空缺部分补充完整。
CREATETABLE订单(
订单编号CHAR(6)(a)
用户名VARCHAR(40)NOTNULL(b),
销售额FLOAT,
订购日期DATENOTNULL,
出货日期DATE(c));
请完成下列查询的SQL语句。
(1)查询名称中包含“数据库”的图书的图书名称,作者,出版社和出版日期。
SELECT(d)
FROM图书
WHERE图书名称(e);
(2)查询提供销售(图书表中有)但没有销售过(没在订单明细表中出现)的图书名称和出版社。
SELECT图书名称,出版社
WHERENOTEXISTS(
SELECT(f)
FROM订单明细
WHERE(g));
(3)查询订购图书数量最多的会员名及其订购的数量。
SELECT用户名,(h)
FROM订单,订单明细
WHERE(i)
GROUPBY用户名
HAVING(j)
(SELECTSUM(数量)
FROM订单,订单明细
WHERE订单.订单编号=订单明细.订单编号
GROUPBY用户名);
(4)为了统计会员的购买行为信息,实施有意义的客户关怀策略,查询会员的平均订购间隔时间,考虑多次购买图书和一次购买图书的情况(其中,DATEDIFF函数表示两个日期之间的天数)。
SELECT用户名,CASEWHEN(k)
THENDATEDIFF(MAX(订购日期),MIN(订购日期))/
(1)
ELSEDATEDIFF(CURRENT_TIMESTAMP,MIN(订购日期))
ENDASAVGGAP
FROM订单
(m);
[问题3]
会员订购图书后,将本次订购的销售额累加到该会员的消费额中,并按照本次订单的销售额计算积分累加到该会员的积分中(每20元增加1个积分,不足20元不计入积分)。
下面用触发器实现该需求,请填充空缺部分。
CREATETRIGGER会员积分—TRIGGERAFTER(n)
REFERENCINGNEWROWASNROW
BEGIN
UPDATE会员
SET消费额=消费额+NROW.销售额,(o)
WHERE用户名=NROW.用户名
END
试题二分析
本题考核SQL语言的应用。
本题考查关系模型的完整性约束。
完整性约束包括三类:
实体完整性、参照完整性和用户定义的完整性。
实体完整性约束规定关系的主属性不能取空值,关系模型中以主码作为唯一性标示。
(a)考核的是实体完整性,订单编号是订单关系的主键,用PRIMARYKEY关键字,也可用NOTNULLUNIQUE或UNIQUENOTNULL来保证一个订单标号唯一识别一个订单:
参照完整性约束规定若属性(或属性组)A是关系R上的主码,B是关系S上的外码,A与B相对应(来自相同的域),则B取值为空或者来自于R上的某个A的值。
(b)考查参照完整性,用户名属性对应于会员关系的用户名属性,定义语法为REFERENCES会员(用户名),由于两个关系中的属性名一致,因此也可以用REFERENCES会员定义;
用户定义的完整性约束是针对具体的数据库应用而定义的,它反映该应用所涉及的数据必须满足用户定义的语义要求。
(c)考查用户定义的完整性约束,要求出货日期必须大于等于订购日期,因此答案为CHECK(订购日期<=出货日期)或C0ONSTRAINTchkdateCHECK(订购日期<=出货日期),此处,chkdate可以为其他的约束名。
本题考查查询语句SELECT的语法,查询是SQL的重要内容。
(1)考查基本的SQL查询语法,SELECT后是要查询的属性,查询条件是包含“数据库”的图书,因此用LIKE关键字。
(2)考查NOTEXISTS语法,(f)处填。
。
该查询为相关查询,因此(g)处的条件为图书.图书编号=订单明细.图书编号。
(3)考查较复杂的嵌套查询、分组查询和ALL关键字。
子查询得到所有会员的订购数,题目要求查询订购数最多的会员名和订购数,因此(j)的答案为SUM(数量)>=ALL。
(4)考查分组查询、聚集函数和CASE语法。
平均订购间隔时间对一次购买和多次购买的会员有不同的计算方式,对一次购买的会员,平均订购时间为当前时间—订购时间;
而对于多次购买的会员,平均订购时间为(最晚的订购时间—最早的订购时间)/间隔数,间隔数为订购数-1。
本题考查SQL的触发器机制。
触发器是通过事件触发而执行的,事件指的是对某个关系的插入、删除或更新。
当触发器被事件激活时,并不是立即执行,而是检测触发器的条件,若条件满足则执行触发器定义的动作。
因此,创建一个触发器时,必须指定:
触发器名称、在其上定义触发器的关系(表)、触发器将何时激发、触发器执行时应作的动作。
本题中触发器定义的事件为会员订购图书,即插入一个订单记录,因此(n)的答案为INSERTON订单。
执行的动作是累加会员的消费额,并按照规则把本次订单的销售
额计算为积分,更新会员的积分,因此(o)的答案为积分=积分+INT(NROW.销售额/20)。
(a)PRIMARYKEY或NOTNULLUNIQUE或UNIQUENOTNULL
(b)REFERENCES会员(用户名)或REFERENCES会员
(c)CHECK(订购日期<=出货日期)或
CONSTRAINTchkdateCHECK(订购日期<=出货日期)
(注:
chkdate可为其他名称)
(1)(d)图书名称,作者,出版社,出版日期
(e)LIKE'
%数据库%'
(2)(f)*
(g)图书.图书编号=订单明细.图书编号
(3)(h)SUM(数量)
(i)订单.订单编号=订单明细.订单编号
(j)SUM(数量)>=ALL
(4)(k)COUNT(*)>1
(l)(COUNT(*)-1或COUNT(*)也给分
(m)GROUPBY用户名
(n)INSERTON订单
(o)积分=积分+INT(NROW.销售额/20)
试题三
某集团公司拥有多个大型连锁商场,公司需要构建一个数据库系统便于管理其业务运作活动。
[需求分析结果]
1.商场需要记录的信息包括商场编号(商场编号不重复)、商场名称、地址和联系电话。
某商场信息如下表1所示。
2.每个商场包含不同的部门,部门需要记录的信息包括部门编号(不同商场的部门编号不同)、部门名称、位置分布和联系电话。
某商场的部门信息如表2所示。
表1部门信息表
商场编号
部门名称
位置分布
联系电话
DT002
财务部
商场大楼六层
82504342
DT007
后勤部
商场地下副一层
82504347
DT021
安保部
82504358
DT005
人事部
82504446
管理部
商场裙楼三层
82504668
3.每个部门雇用了多名员工处理日常事务,每名员工只能属于一个部门(新进员工在培训期不隶属于任何部门)。
员工需要记录的信息包括员工编号、姓名、岗位、电话号码和工资。
员工信息如下表3所示。
表3员工信息表
员工编号
姓名
岗位
电话号码
工资
XA3310
周超
理货员
13609257638
1500.00
SH1075
刘飞
防损员
13477293487
XA0048
江雪花
广播员
15234567893
1428.00
BJ3123
张正华
经理
13345698432
1876.00
4.每个部门的员工中有一个是经理,每个经理只能管理一个部门。
系统要记录每个经理的任职时间。
[概念模型设计]
根据需求阶段收集的信息,设计的实体联系图和关系模式(不完整)如下:
[关系模式设计]
商场(商场编号,商场名称,地址,联系电话)
部门(部门编号,部门名称,位置分布,联系电话,(a))
员工(员工编号,姓名,岗位,电话号码,工资,(b))
经理((c),任职时间)
根据问题描述,补充四个联系,完善图3-1的实体联系图。
根据实体联系图,将关系模式中的空(a)~(c)补充完整,并分别给出部门、员工和经理关系模式的主键和外键。
为了使商场有紧急事务时能联系到轮休的员工,要求每位员工必须且只能登记一位紧急联系人的姓名和联系电话(假设不同员工可以登记相同的紧急联系人)。
则在图3-1中还需添加的实体是(d),该实体与图3-1中的员工关系存在(e)联系。
给出该实体的关系模式。
试题三分析
本题考查数据库概念结构设计及向逻辑结构转换的掌握。
此类题目要求考生认真阅读题目对现实问题的描述,经过分类、聚集和概括等方法从中确定实体及其联系。
题目已经给出了4个实体,需要根据需求描述给出实体间的联系。
由“每个商场包含有不同的部门”可知商场与部门间为1:
m联系;
由“每个部门雇用了多名员工处理日常事务”可知部门与员工间为1:
n联系;
由“每个部门的员工中有一个经理……,每个经理只能管理一个部门”可知部门与经理间为1:
1联系,并且员工是经理的超类型,经理是员工的子类型。
完善的实体联系图如下图所示。
商场的属性信息中,商场编号由集团公司分配,不会重复,可作为商场的主键属性:
部门的属性信息中,部门编号由集团公司分配,不会重复,可作为部门的主键属性,商场与部门的联系需要通过将商场的主键(商场编号)加入到部门中来表达;
员工的属性信息中,员工编号由集团公司分配,不会重复,可作为员工的主键属性,部门与员工的联系需要通过将部门的主键(部门编号)加入到员工中来表达;
经理除了包含员工的属性信息外,还需要任职时间属性。
完整的关系模式如下:
商场(商场编号,商场名称,地址,联系电话)
部门(部门编号,部门名称,位置分布,联系电话,商场编号)
员工(员工编号,姓名,岗位,电话号码,工资,部门编号)
经理(员工编号,任职时间)
员工的紧急联系人信息通过添加紧急联系人关系来实现,由“每位员工必须且只能登记一位紧急联系人的姓名和联系电话”,但可能存在多位员工登记同一位家属,可知员工与家属间为n:
1联系;
由“不同员工可以登记相同的紧急联系人”可知,员工编号可作为家属的主键属性。
所以需要添加的关系模式如下:
紧急联系人(员工编号,姓名,联系电话)
(a)商场编号(b)部门编号(c)员工编号
部门关系模式的主键:
部门编号
外键:
员工关系模式的主键:
经理关系模式的主键:
(d)紧急联系人(e)1:
n
关系模式:
紧急联系人(员工编号,姓名,联系电话)
试题四
M公司为某宾馆设计宾馆机票预订系统,初步的需求分析结果如下:
(1)客户可以在提前预订或直接入住时向宾馆提供相关信息,宾馆登记的客户信息.包括:
客户编号,姓名,性别,类型,身份证号,联系方式,预订日期,入住时间和离开时间等信息。
其中类型字段说明客户是普通客户或VIP客户,不同的客户类型享受订票的折扣额度不同。
直接入住的客户其预订日期取空值。
(2)需要预订机票的客户应填写“机票预订”表,提供飞行日期、航班号、出发时间、目的地等信息。
宾馆根据客户订票信息购票后,生成“客户订单”表,并根据客户类型确定相应的折扣额度。
“机票预订”和“客户订单”表如下表1、表2所示。
表2“客户订单”示例
客户编号
飞行日期
航班名
机票订单号
折扣额度
A10001
2009.5.1
AZ100
90001
0.8
2009.5.3
AC400
2009.5.5
KC560
2009.8.6
A10002
90002
0.9
B10001
BC600
90003
B10002
90004
0.85
…
2009.8.9
AZ320
91206
2009.9.5
91207
[逻辑结构设计]
根据需求阶段收集的信息,设计的关系模式如下图所示。
关系模式的主要属性、含义及约束如下表3所示。
对关系“客户”,请回答以下的问题:
(1)若选定(客户编号,预订日期)作为主码,未预订而直接入住的客户信息能否录入客户表?
如不能,请说明原因。
(2)对“客户”关系增加一个流水号属性作为主码,“客户”关系属于第几范式?
还存在哪些问题?
(3)将增加入住标识属性后的“客户”关系分解为第三范式,分解后的关系名依次取客户1、客户2、…。
对关系“航班”,请回答以下问题:
(1)列举出“航班”关系中所有不属于任何候选码的属性(非码属性)。
(2)该关系模式可达到第几范式?
用不超过60个字的内容叙述理由。
对于没有预订客房或入住宾馆的客户,需要在(a)关系中修改其(b)属性的值域,以满足这类客户在宾馆预订机票的需求。
试题四分析
(1)若选定(客户编号,预订日期)作为主码,未预订而直接入住的客户信息是不能记入客户表的。
因为预订日期是主属性,直接入住客户的预订日期应该取空值,这违反实体完整性约束,所以对于直接入住的客户信息记录是无法插入到客户表中。
(2)对“客户”关系增加一个流水号属性作为主码,“客户”关系属于第二范式,存在数据冗余等问题。
如表4所示。
从表4所示的例子可以看出,A10001客户有3次预订信息,则其姓名、性别、身份证号、联系方式和类型信息将重复3次。
(3)将增加入住标识属性后的“客户”关系分解为第三范式如下所示:
从表5和表6可以看出,将“客户”关系分解为第三范式后能消除信息冗余及插入删除的不一致性。
表5客户关系举例
性别
身份证号
联系方式
类型
李军
男
400111801201211
38001221
VIP
张晓丽
女
610151830306112
56732222
普通
A10003
王向东
320211780911321
71628354
表6客户2关系举例
流水号
预订日期
入住时间
离开时间
10001
2009.5.1.08.30
2009.5.6.12.00
10002
2009.5.13
2009.5.13.14.00
2009.5.18.09.00
10003
2009.7.5
10004
10005
2009.5.11
2009.11.09.20
2009.5.21.11.30
10006
2009.8.3
(1)包含在任何一个候选码中的属性叫做主属性,否则叫做非主属性或称为非码属性。
对于“航班”关系模式的候选码为(航班名,飞行日期),故非码属性为:
航空公司名称,出发地点,出发时间,目的地,到达时间。
(2)若关系模式R∈1NF,且每一个非主属性完全依赖于码,则关系模式R∈2NF。
换句话说,当1NF消除了非主属性对码的部分函数依赖,则称为2NF。
但是“航班”不属于2NF。
因为该关系模式存在{航班名一航空公司名称,出发地点,目的地}函数依赖,非主属性航空公司名称,出发地点,目的地不完全函数依赖于候选码(航班名,飞行日期),故“航班”是属于1NF的。
根据题意类型字段说明客户是普通客户或VIP客户,不同的客户类型享受订票的折扣额度不同,这样对于没有预订客房或入住宾馆的客户,需要在“客户”关系中修改其“类型”属性的值域,即可以通过在“类型”属性中增加“非入住”标识属性以满足这类客户在宾馆预订机票的需求。
(1)不能,因为预订日期是主属性,直接入住客户的预订日期应该取空值,这违反实体完整性约束,记录无法插入到客户表。
(2)“客户”关系属于2NF,存在数据冗余等问题。
若某一客户有多次预订及入住信息,则其姓名等信息将重复多次。
(3)“客户”分解为第三范式如下所示:
(1)“航班”关系模式的候选码为(航