2020年数据库系统工程师考试下午真题(专业解析+参考答案)

第 1 题

阅读下列说明,回答问题1至问题3,将解答填入答题纸的对应栏内。
【说明】
某小区快递驿站代为收发各家快递公司的包裹,为规范包裹收发流程,提升效率,需要开发一个信息系统。请根据下述需求描述完成该系统的数据库设计。
【需求描述】
(1)记录快递公司和快递员的信息。快递公司信息包括公司名称、地址和一个电话;快递员信息包括姓名、手机号码和所属公司名称。一个快递公司可以有若干快递员,一个快递员只能属于一家快递公司。
(2)记录客户信息,客户信息包括姓名、手机号码和客户等级。驿站对客户进行等级评定,等级高的客户在驿站投递包裹有相应的优惠。
(3)记录包裹信息,便于快速查找和管理。包裹信息包括包裹编号、包裹到达驿站时间、客户手机号码和快递员手机号码。快递驿站每个月根据收发的包裹数量,与各快递公司结算代收发的费用。
【概念模型设计】
根据需求阶段收集的信息,设计的实体联系图(不完整)如图1-1所示。

图1-1实体联系图

【逻辑结构设计】
根据概念模型设计阶段完成的实体联系图,得出如下关系模式(不完整 ):  
快递公司(公司名称,地址,电话)
快递员(姓名,快递员手机号码,  (a)  )
客户(姓名,客户手机号码,客户等级)
包裹(编号,到达时间,  (b)  , 快递员手机号码)

                                                                              



【问题1】(6分)
根据问题描述,补充图1-1的实体联系图。
【问题2】(4分)
补充逻辑结构设计结果中的(a)、(b) 两处空缺及完整性约束关系。
【问题3】(5分)
若快递驿站还兼有代缴水电费业务,请增加新的“水电费缴费记录"实体,并给出客户和水电费缴费记录之间的“缴纳”联系,对图1-1进行补充。“水电费缴费记录”实体包括编号、客户手机号码、缴费类型、金额和时间,请给出“水电费缴费记录”的关系模式,并说明其完整性约束。

答案与解析

  • 试题难度:一般
  • 知识点:数据库设计>逻辑结构设计
  • 试题答案:

    【问题1】

    【问题2】
    (a)公司名称
    (b)客户手机号码
    【问题3】补充内容如下图中虚线所示。

    关系模式:水电费缴费记录(编号客户手机号码,缴费类型,金额,时间)。

  • 试题解析:

    本题考查对数据库概念结构设计及逻辑结构转换的掌握。此类题目要求考生认真阅读题目,根据题目的需求描述,补充关系模式和实体联系图。
    【问题1】
    根据题意可知客户和快递员两个实体参与包裹联系,两方之间为*:*联系。由“包裹信息包括包裹编号、包裹到达驿站时间、客户手机号码和快递员手机号码”可知包裹关系模式的属性除了包含参与联系双方的主码,即客户手机号码和快递员手机号码外,还应该包含编号和到达驿站时间这两个属性,因此在包裹联系上还应该补充编号和到达驿站时间两个属性。
    【问题2】
    根据需求描述(1)可知快递员信息包括姓名、手机号码和所属公司名称。所以在快递员关系模式里应该包括“ 公司名称”,且以外键标识。根据需求描述(3) 可知包裹信息包括包裹编号、包裹到达驿站时间、客户手机号码和快递员手机号码。所以需要在包裹关系模式中包含“客户手机号码”,且以外键标识。
    【问题3】
    根据题意需要增加新的“水电费缴费记录”实体,并给出客户和水电费缴费记录之间的“缴纳”联系。因为每个客户可以有多条水电费缴纳记录,而每条缴纳记录只能对应一个客户,所以客户与水电费缴纳记录之间是1:* 联系。

第 2 题

阅读下列说明,回答问题1至问题3,将解答填入答题纸的对应栏内。
【说明】
某市居委会在新冠病毒疫情期间需分配社区人员到辖区内各个小区,协助小区物业人员进行业主出入登记及体温检测工作。居委会为高效完成工作并记录必要的工作信息,设计了相应的数据库。其中有一个表用来记录工作人员在各个小区的分布情况及每天工作时长。表的结构如下:
人员分配(人员编号,人员姓名,小区编号,物业经理姓名,人员职责)。
其中人员编号和小区编号唯一,人员职责用于记录该人员需配合小区完成的工作,假设每个人员在同一个小区只负责一项工作,但在不同小区可以负责不同的工作。请回答以下问题。

【问题1】(6分)
给出人员分配表中成立且左侧只有一个属性的所有函数依赖关系。题中设计的人员分配表是否满足2NF,请用100字以内的文字说明原因。
【问题2】(3分)
如果要将人员分配表规范化为满足3NF,请用100字以内的文字简要说明解决方案。
【问题3】(6 分)
请给出问题2设计结果中各个表的主键和外键。

答案与解析

  • 试题难度:一般
  • 知识点:关系数据库>1NF至4NF
  • 试题答案:

    【问题1】
    包含的函数依赖有:
    人员编号→人员姓名
    小区编号→物业经理姓名
    不是2NF
    因为人员分配表的主键是(人员编号,小区编号),存在非主属性对主键的部分依赖。
    【问题2】
    拆分为三个表:
    人员表(人员编号,人员姓名)
    小区表(小区编号,物业经理姓名)
    责任表(人员编号,小区编号,人员职责)
    【问题3】
    人员表的主键为人员编号,无外键。
    小区表的主键为小区编号,无外键。
    责任表的主键为(人员编号,小区编号),外键是人员编号和小区编号。

  • 试题解析:

    本题考查数据库中第二范式和第三范式的概念和在实际问题中的应用。
    此类题目要求考生认真阅读题目对现实问题的描述,判断数据库中表的主键以及其他属性对主键的依赖关系,从而确定符合2NF的要求。在此基础上,考生需要实现数据库的规范化,满足3NF的要求。同时考生也需要掌握外键的概念。
    【问题1】
    第二范式(2NF)是指每个表必须有主键(Primary key),其他属性与主键一一对应。 通常称这种关系为函数依赖(Functional dependence)关系,即表中其他属性都依赖于主键,或称该数据元素唯一地被主键所标识。
    在本题的人员分配表中,人员编号和小区编号为主键,由于每个人员在不同小区可以负责不同的工作,人员职业对主键完全依赖。但是由人员编号可以确定人员姓名,由小区编号可以确定物业经理,这两个属性对主键部分依赖,因此不符合2NF。
    【问题2】
    第三范式(3NF)就是指表中的所有数据元素不但要能唯一地被主关键字所标识,而且它们之间还必须相互独立,不存在其他的函数关系。
    首先我们需要满足第二范式,应分为三个表。
    人员表(人员编号,人员姓名),人员编号为主键。
    小区表(小区编号,物业经理姓名),小区编号为主键。
    责任表(人员编号,小区编号,人员职责),人员编号和小区编号为主键。
    这三个表中已经不存在其他函数关系,符合第三范式。
    【问题3】
    如果公共关键字在一个关系中是主关键字,那么这个公共关键字被称为另一个关系的外键。
    各表主键已在问题2中确定。
    其中,人员表和小区表无外键。
    责任表中的人员编号和小区编号是人员表和小区表中的主关键字,因此人员编号和小区编号是外键。

第 3 题

阅读下列说明,回答问题1至问题4,将解答填入答题纸的对应栏内。
【说明】
某订单管理系统的部分数据库关系模式如下:
客户:CUSTOMERS(Cno, Cname, Cage, Csex),各属性分别表示客户编号、客户姓名、年龄和性别;
商品:GOODS(Gno, Gname, Gprice, Gorigin),各属性分别表示商品编号、商品名称、单价和产地;
订单:ORDERS(Ono, Cno, Gno, Oprice, Onumber),各属性分别表示订单编号、客户编号、商品编号、顾客购买商品的单价和数量。
有关关系模式的说明如下:
(1)下画线标出的属性是表的主键。
(2)商品表中的Gprice是商品的当前价格,可能会发生变动;订单表中的Oprice是订单成交时的商品单价。
(3)一个订单只包含一位顾客购买的一种商品;其商品数量至少1件,最多99件。
根据以上描述,回答下列问题,将SQL语句的空缺部分补充完整。

【问题1】(3分)
请将下面创建订单表的SQL语句补充完整,要求定义实体完整性约束、参照完整性约束,以及其他完整性约束。

【问题2】(5分)
查询所有订单的详细情况,要求输出订单号(Ono)、 客户姓名(Cname)、 商品名称(Gname)、单价(Oprice)、数量(Onumber)和金额(Oamount), 查询结果按照金额从大到小排列。此功能由下面的SQL语句实现,请补全。

【问题3】(5分)
创建已售商品信息视图,给出已售商品的编号(Gno)、名称(Gname)、订单个数(Onum)及平均每单的商品数量(GAnum)。此视图的创建语句如下,请补全。

【问题4】(2分)
查询未售出商品的编号和名称。此功能由下面的SQL语句实现,请补全。


答案与解析

  • 试题难度:一般
  • 知识点:SQL语言>表及相关约束
  • 试题答案:

    【问题1】
    (a) REFERENCES CUSTOMERS (Cno)
    (b) REFERENCES GOODS (Gno)
    (c) CHECK (Onumber>=1 AND Onumber<=99)
         或者CHECK (Onumber BETWEEN 1 AND 99)
    【问题2】
    (d) Oprice * Onumber
    (e) ORDERS.Cno=CUSTOMERS.Cno
    (f) ORDERS.Gno=GOODS.Gno
    注:(e)和(f)的答案可以互换
    (g) ORDER
    (h) Oamount DESC 或者 6 DESC
    【问题3】
    (i) VIEW
    (j) COUNT(*)或者COUNT(Ono)或者COUNT(1)
    (k) AVG(Onumber)
    (1) GROUP
    (m) ORDERS.Gno
    【问题4】
    (n) GOODS
    (o) EXCEPT

  • 试题解析:

    本题考查SQL编程应用。
    【问题1】
    本问题考查SQL的数据定义和完整性约束。
    根据题目描述,在订单表ORDERS中,订单编号Ono为主键;客户编号Cno为外键,参照客户表CUSTOMERS的Cno;商品编号Gno也是外键,参照商品表GOODS的Gno。另外,顾客购买商品的数量Onumber的取值范围为[1,99]。这些约束都是列级约束。因此创建订单表的完整SQL语句为:
    【问题2】
    本问题考查SQL的多表查询及排序。
    题目要求查询所有订单的详细情况,输出订单号、客户姓名、商品名称、单价、数量和金额,这需要连接CUSTOMERS、ORDERS和GOODS三个表进行查询,连接属性分别为Cno和Gno。每笔订单的金额是由该笔订单所购买商品的单价和数量相乘得到的。
    题目还要求查询结果按照金额从大到小排列,这需要用ORDER BY子句进行降序排序,排序属性为输出属性“金额”,用属性名Oamount或列序号6皆可。
    完整的查询语句如下:

    【问题3】
    本问题考查SQL创建视图及分组统计查询。
    题目要求创建已售商品信息的视图,其属性有已售商品的编号(Gno)、名称(Gname)、订单个数(Onum)及平均每单的商品数量(GAnum)。创建视图用CREATE VIEW语句。已售商品可以从订单表ORDERS中查询到。使用GROUP BY子句在表ORDERS中按Gno分组统计,可以得到每种商品相应的订单个数,以及此商品在这些订单中的平均数量。这里为了输出Gname,需要进行ORDERS和GOODS的连接;Gname不能直接输出,因为它不是分组属性,但由于在同一分组里Gname的值相同,因此可以对其作用聚集函数MIN或MAX后输出。因此完整创建视图GOODS_SOLD的SQL语句如下:

    【问题4】
    本问题考查SQL的集合操作。
    题目要求查询未售出商品的编号和名称。全部的商品来自于商品表GOODS,已售商品可以从视图GOODS_SOLD得到,二者的差集就是所需结果。完整的查询语句如下:

第 4 题

阅读下列说明,回答问题1至问题3,将解答填入答题纸的对应栏内。
【说明】
某网上销售系统的部分关系模式如下:
订单表:orders(o_no, o_date, o_time, p_no, m  no, p_price, nums, amt, status)。其中属性含义分别为:订单号、订单日期、订单时间、产品编码、供应商编码、产品价格、产品数量、订单金额、订单状态(0-未处理、1-已处理、 2-已取消)。
产品表:products(p_no, p_name, p_type, price, m_no, p_nums)。其中属性含义分别为:产品编码、产品名称、产品类型、产品价格、供应商编码、库存数量。

【问题1】(5分)
节假日时,由供应商提供商品打折后的新价格,数据存放在临时表中,该临时表的表名为tmp_prices(不同供应商有不同的临时表),其关系模式如下:

后台维护人员需要根据供应商填写在tmp  prices中的数据来更新产品表中某些产品的价格。下面是基于游标,用SQL实现的价格更新程序,请补全空缺处的代码。


【问题2】(6分)
假设用户1和用户2同时购买1份A商品,用户3查询和浏览A商品。三个用户对应事务的部分调度序列如表4-1所示(事务中未进行并发控制),其中TO时刻该A商品的库存数量p_nums为100。

表4-1 事务运行部分调度示意表

请说明T4、T7时刻,用户3事务读取到的p_nums 数值分别是多少。请说明T8时刻事务调度结果是否正确?若不正确请说明属于哪一种数据不一致性。
【问题3】(4分)
为保证并发事务的正确性,系统要求所有事务需遵循两段锁协议。
(1)请用100字以内的文字简要解释两段锁协议,并说明“两段”的含义。
(2)请说明两段锁协议是否可以避免死锁?如不能避免,应采取什么措施解决死锁问题。

答案与解析

  • 试题难度:一般
  • 知识点:事务管理>并发操作及问题
  • 试题答案:

    【问题1】
    (a)cursor
    (b)open
    (c)Pno, Pprice, Mno
    (d)commit
    【问题2】
    T4时刻,p_nums的值为100。
    T7时刻,p_nums的值为99。
    事务调度结果不正确。
    丢失修改。
    【问题3】
    (1)两段锁协议是指对任何数据进行读写之前必须对数据加锁;在释放一个封锁之后,事务不再申请和获得任何其他锁。
    “两段”的含义是:事务分为两个阶段,第一阶段是获得封锁,称为扩展阶段;第二阶段是释放封锁,称为收缩阶段。
    (2)两段锁协议不能避免死锁。
    解决措施是采用死锁检测机制,发现后按照一定算法解除死锁。

  • 试题解析:

    本题考查数据库设计中存储过程和事务并发的相关知识及应用。
    【问题1】
    本问题考查存储过程和游标的基本知识。题干描述中已经给出了关系模式和具体的需求描述,给出了使用游标的明确要求,并给出了主体程序。考生应根据游标的具体使用要求,填写空白处代码。
    存储过程中游标的使用分为四个步骤:定义游标、打开游标、取数据、关闭游标。空白(a)处于存储过程的Declare部分,此部分用于定义临时变量,所以此处应该是游标定义,应该填cursor关键字;空白(b)处应该填open,即打开游标;游标打开后,常见的数据处理方法是采用循环方式,利用fetch语句取出一条数据进行处理,所以空白(c)处应该填写存储数据的临时变量,即Pno,Pprice,Mno,分别来存储取出的产品编码、产品价格和供应商编码,此处需要注意的是临时变量的顺序,需要和游标定义中select后的属性顺序保持一致。空白(d)是该存储过程的最后一条语句,应该对修改后的数据进行确认提交,应该填写commit语句。
    【问题2】
    本问题考查事务并发可能会带来的并发问题。
    根据题干描述,三个事务并发执行,T0时刻p_nums为100。到T4时刻,事务1和事务2均对事务内部的变量p_nums进行了减一操作,事务1和事务2的p_nums值为99,但均未写入数据库,因此此时事务3从数据库中读取的p_nums值应该依然是100。到了T7时刻,事务1将99的数值写入数据库,事务2也将99的数值写入数据库,并且覆盖了事务1写入的数值(虽然都是99),所以T7时刻事务3从数据库读取的p_nums值为99。
    从上述描述中可以看到,到了T8时刻,事务1和事务2都完成了减一操作,但数据库中p_nums的值却是99(不是98),因此事务调度结果不正确,不属于串行化调度,属于丢失修改。导致不正确的原因是事务2的修改覆盖了事务1的修改,导致事务1的修改丢失了。
    【问题3】
    本问题考查两段锁协议的基本知识。
    两段锁协议是指对任何数据进行读写之前必须对数据加锁;在释放一个封锁之后,事务不再申请和获得任何其他锁。
    该协议明确所有事务必须分两个阶段对数据项加锁和解锁:第一阶段是获得封锁,事务可以对数据加读/写锁,但不能释放,也称为扩展阶段;第二阶段是释放封锁,事务可以释放前面对数据加的读/写锁,但不能再申请加锁,也称为收缩阶段。
    两段锁协议不能保证完全避免锁死的发生,当两个满足两段锁协议的事务并发运行时,如果两个事务对两个资源进行了交叉锁定,死锁就必然发生。
    因此DBMS必须要提供死锁检测机制,以实时检测死锁的发生,并根据某种策略解除死锁。

第 5 题

阅读下列说明,回答问题1至问题3,将解答填入答题纸的对应栏内。
【说明】
如果一个数据库恢复系统采用检查点机制,且其日志文件如表5-1所示,第一列表示日志记录编号,第二列表示日志记录内容。<Ti, START>表示事务Ti开始执行,<Ti, COMMIT>表示事务Ti提交,<Ti, D,VI, V2>表示事务Ti将数据项D的值由V1修改为V2。请回答以下问题。

表5-1 日志记录列表

【问题1】(6分)
假设系统开始执行前X=100,Y=50,Z=10,系统出错恢复后,X、Y、Z各自的数值是多少?
【问题2】(4 分)
系统发生事务故障时,故障恢复有撤销事务(undo)和重做事务(redo)两个操作。请给出系统恢复时需要redo的事务列表和需要undo的事务列表。
【问题3】(5 分)
请用100字以内的文字,简要描述系统出错后,基于检查点的恢复过程。

答案与解析

  • 试题难度:一般
  • 知识点:数据库运行与管理>日志文件
  • 试题答案:

    【问题1】
    X=1,Y=50,Z=10。
    【问题2】
    需要redo的事务列表:T1。
    需要undo的事务列表:T2,T3。
    【问题3】
    步骤1:反向扫描日志文件,确定需要redo的事务和需要undo的事务。
    步骤2:对需要undo的事务撤销已经执行的操作。
    步骤3:对需要redo的事务重新执行已执行的操作。

  • 试题解析:

    本题考查考生对数据库中事务故障的掌握。
    此类题目要求考生认真阅读日志文件,判断故障发生时完成和未完成的事务对数据项的影响。在此基础上,考生需要了解撤销事务和重做事务在故障恢复中的应用和故障恢复的正确步骤。
    【问题1】
    系统出错前,只提交了事务T1,即将数据项X的值由100改为1。
    T2和T3事务由于未提交,其中的修改未生效。
    【问题2】
    系统恢复的方法为:
    ①清除尚未完成的事务对数据库的所有修改,undo(撤销)所有未完成的事务(从后往前)。
    ②将缓冲区中已完成事务提交的结果写入数据库,redo(重做)所有已提交的事务(从前往后)。
    T1已完成事务提交,需要redo。
    T2和T3事务尚未完成,需要undo。
    【问题3】
    本题需要重点关注撤销事务和重做事务的顺序。
    在重做事务之前,需要先进行撤销操作。

results matching ""

    No results matching ""