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

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

试题一

阅读以下说明和图，根据要求回答问题。

[说明]

某电子商务系统采用以数据库为中心的集成方式改进购物车的功能，详细需求如下：

1加入购物车。顾客浏览商品，单击加入购物车，根据商品标识从商品表中读取商品信息，并更新购物车表。

2 3提交订单。顾客单击提交订单请求，后台计算购物车表中商品的总价(将购物车表中的商品状态改为待付款，显示订单详情。若商家改变价格，格。

4商品价格。

5(验证码、价格等) 提交给支付系统(如信用卡系统) 的状态。

6() ；然后可根据顾客或

7生成报表。调用第三方服务Crystal Reports

8维护信息。管理员维护()

1所示的顶层数据流图和如图2所示的0层数据流图。

图1 顶层数据流图

图

11中的实体E1～E4的名称。

22中的数据存储D1～D4的名称。

3、图2中缺失了数据流，请用说明或图2中的词语，给出其起点和终点。

4、根据说明，给出数据流“转账请求”、“顾客订单物流查询请求”和“商家订单物流查询请求”的各组成数据项。

试题二

阅读下列说明，根据要求回答问题。

[说明]

天津市某银行信息系统的数据库部分关系模式如下所示。

客户(客户号，姓名，性别，地址，邮编，电话)

账户(账户号，客户号，开户支行号，余额)

支行(支行号，支行名称，城市，资产总额)

交易(交易号，账户号，业务金额，交易日期)

其中，业务金额为正值表示客户向账户存款，为负值表示取款。

5

67 UPDATE 账户;

(2)对于每笔金额超过10万元的交易，其对应账户标记属性值加1，给出触发器实现的方案。 CREATE TRIGGER交易_触发器交易

REFERENCING NEW ROW AS 新交易

FOR EACH ROW

BEGIN ATOMIC

UPDATE 账户 SET 账户标记=账户标记+1

WHERE (o) ;

COMMIT WORK;

END;

试题三

阅读以下说明，根据要求回答问题。

[说明]

某快递公司为了方便管理公司物品运送的各项业务活动，需要构建一个物品运送信息管理系统。

[需求分析结果]

8快递公司有多个分公司，分公司信息包括分公司编号、名称、经理、办公电话和地址。每个分公司可以有多名员工处理分公司的日常业务，每名员工只能在一个分公司工作。每个分公司由一名经理负责管理分公司的业务和员工，系统需要记录每个经理的任职时间。

9员工信息包括员工号、姓名、岗位、薪资、手机号和家庭地址。其中，员工号唯一标识员工信息的每一个元组。岗位包括经理、调度员、业务员等。业务员根据客户提交的快件申请单进行快件受理事宜，一个业务员可以受理多个客户的快件申请，一个快件申请只能由一个业务员受理。调度员根据已受理的申请单安排快件的承运事宜，例如：执行承运的业务员、运达时间等。一个业务员可以执行调度员安排的多个快件的承运业务。

10客户信息包括客户号、单位名称、通信地址、所属省份、联系人、联系电话、银行账号。其中，客户号唯一标识客户信息的每一个元组。当客户要寄快件时，先要提交快件申请单，申请号由系统自动生成。快件申请信息包括申请号、客户号、发件人、发件人电话、快件名称、运费、发出地、收件人、收件人电话、收件地址。其中，一个申请号对应唯一的一个快件申请，一个客户可以提交多个快件申请，但一个快件申请由唯一的一个客户提交。

[概念模型设计]

根据需求阶段收集的信息，设计的实体联系图如图1所示。

图1 某物品运送信息管理系统实体练习图(不完整)

[关系模式设计]

分公司(分公司编号，名称，经理，办公电话，地址)

员工(员工号，姓名，，岗位，薪资，手机号，家庭地址)

客户(客户号，单位名称，通信地址，所属省份，联系人，联系电话，银行账号)

申请单(，发件人，发件人电话，发件人地址，快件名称，运费，收件人，收件人电话，收件地址，受理标志，业务员)

安排承运(，实际完成时间，调度员)

8、根据问题描述，补充5个联系，完善图5。7的实体联系图。联系名可用联系1、联系2、联系3、联系4和联系5代替，联系的类型分为“1:1”、“1:n”和“m:n”(或“1:1”、“1:*”和“*:*”) 。

9、(1)根据实体联系图，将关系模式中的空(a)～(c)补充完整。

(2)给出员工、申请单和安排承运关系模式的主键和外键。

10、(1)客户关系的通信地址可以进一步分为邮编、省、市、街道，那么该属性是否属于简单属性，为什么? 请用100字以内的文字说明。

(2)假设分公司需要增设一个经理的职位，那么分公司与经理之间的联系类型应修改为，

11、对于关系“线路信息”，请回答以下问题。

(1)列举出所有不属于任何候选键的属性(非键属性) 。

(2)关系“线路信息”是否为BCNF 范式? 请用60字以内的文字简要叙述理由。

12、对于关系“订单信息”，请回答以下问题。

(1)“订单信息”是否为第2范式? 请用100字以内的文字简要说明会产生什么问题。

(2)把“订单信息”分解为第3范式，分解后的关系名依次为：订单信息1，订单信息2……

(3)列出分解后的各关系模式的主键。

13、对于关系“员工信息”，请回答以下问题。

(1)关系“员工信息”是否为第4范式? 请用100字以内的文字叙述理由。 (2)若“员工信息”不是第4范式，将其分解为第4工信息2……

试题五

阅读下列说明，根据要求回答问题。

[说明]

某银行的转账业务分为如下两类操作。

14读取账户A 余额到变量x ，记为x=RA.。

15将变量x 值写入账户A 中的余额，记为W(A。

从账户A 向账户B 转账金额x 元的伪代码操作序列为：a=a-x，W(A，a) ，b=RB.，b=b+x，W(B，b) 。

针对上述业务及规则，完成下列问题。

14?

0? 应如何处理? 请用100

15、引入共享锁指令SLock(b)和独占锁指令xLockA. 对数据A 对数据A 进行解锁。

2PL 协议。

16、若用SQL

UPDATE Accounts

CONMIT;

其中：Accounts 为账户表，CurrentBalance 为当前余额，Amount 为新存入的金额。

该事务程序能否保证数据的一致性? 如不能，请说明原因并改正。请用100字以内的文字进行阐述。

答案:

试题一

1、由题干中的关键信息“(3)提交订单……、“(4)改变价格。商家查看订购自家商品的订单信息，根据特殊优惠条件修改价格，更新订单塞中的商品价格”，结合如图2所示的0层数据流图中与外部实体E1相关的输入数据流“商家订单信息”、输出数据流“

、2识查) 、“息”D4。

3物车表中商品的总价(包括运费) 加入订单表……”等关键信息，结合图2中外部实体“顾客”、处理“提交订单”和数据存储“D1(或订单表) ”之间的逻辑关系，可知图2中缺失了一条起点为“D4(或购物车表) ”、终点为“提交订单”的数据流“商品价格”。

根据题干中“(5)付款。顾客单击付款后，系统先根据顾客表中关联的支付账户……”等关键信息，结合图2中外部实体“顾客”、处理“付款”和数据存储“顾客表”之间的逻辑关系，可知图2中缺失了一条起点为“顾客表”、终点为“付款”的数据流“支付账户”。

由题干中的关键信息“(5)付款。顾客单击付款后……然后根据转账结果返回支付状态并更改购物

车表中商品的状态”等，结合图2中外部实体“顾客”、处理“付款”和数据存储“D4(或购物车表) ”之间的逻辑关系，可知图2中缺失了一条起点为“付款”、终点为“D4(或购物车表) ”的数据流“商品状态”。

根据题干中的关键信息“(7)生成报表。根据管理员和商家设置的报表选项，从订单表、商品表以及商品分类表中读取数据……”等，结合图2中外部实体“管理员”和“E1(或商家) ”、处理“生成报表”、数据存储“D1(或订单表) ”、“D2(或商品表) ”和“D3(或商品分类表) ”之间的逻辑关系，可知图2中缺失了一条起点为“D1(或订单表) ”、终点为“生成报表”的数据流“订单信息”。 4、基于上题的分析结果，根据题干中“(5)付款。顾客单击付款后，系统先根据顾客表中关联的支付账户，将转账请求(验证码、价格等) 提交给支付系统……”等关键信息，可知数据流“至少包含的数据项有：账号信息、验证码、价格。

由题干中关键信息“(6)号) 询物流信息”等，可知数据流“顾客订单物流查询请求”数据流“商家订单物流查询请求”{}。 试题二

5

由题干中的关键信息“客户号为客户关系的唯一标识”“”可以作为“客户”关系模式的主键。同理并结合常识可得，属性“支行号”是““交易号”是“交易”关系模式的主键，属性“账户号”是其外键；“”可得，属性“账户号”可以作为“账户”关系模式的主键，属性““开户支行号”是其外键。

由以上分析可知，在创建“账户”“账户号”为“账户”关系模式的主键，即不能为空且唯一标识一条账户信息，因此PRIMARY KEY对属性“账户号”进行主键约束。其中，关键字：NOT NULL UNIQUE 、NOTNULL PRIMARY KEY 等。

由于“客户号”是“客户”“”关系模式的外键，因此(b)空缺处需要用FOREIGN KEY 对属性“客户号”“FOREIGN KEY(客户号)REFERENCES 客户(客户号) ”。

元”可知，需要限制“账户”关键模式中“余额”属性值的范围，可以通过即空缺处应填入“CHECK(余额＞1.00) ”。 6、(DML)的综合应用题，本题的解答思路是如下。

(1)“(d)空缺处应填入“支行. 城市='天津市' ”；②在2009年9月存在，因此需要通过模糊匹配来实现，用LIKE 关键词和“交易. 交易日期LIKE'2009-09-%'”。

表之间的连接必须出现在其他WHERE 条件之后，那些可以过滤掉最多条记录子句中其他条件的前面。据此，可以重新组织WHERE 条件的顺序或者通过

依题意所给出的程序语句，需要添加两个子查询以缩小参与连接的记录的数目，即筛选出天津市的所有支行，(f)空缺处应填入(SELECT * FROM支行WHERE 城市='天津市') ，而且找到2009年9月发生的交易记录，(g)空缺处应填入(SELECT * FROM 交易 WHERE 交易日期

LIKE'2009-09-%')，然后再进行连接查询。

(2)依题意要求，可通过子查询实现“当前所有账户余额之和超过百万的客户信息”的查询，即(h)空缺处应填入“客户号 IN”；对SUM 函数计算的结果应通过HAVING 条件语句进行约束，即(i)空缺处应填入“HAVING SUM(余额) ＞1000000.00”；降序排列可以通过关键字DESC 来实现，即(i)

空缺处应填入“客户号DESC ”。 7、这是一道要求读者掌握SQL 语句及触发器机制应用编程的综合分析题，本题的解答思路如下。

(1)关系模式的修改通过ALTER 语句来实现，使用关键字ADD 添加相应的属性，即(k)空缺处应填入“ADD 账户标记INT ”；使用关键字SET 修改属性值，即(l)空缺处应填入“SET 账户标记=0”。

(2)依题意，在“交易”关系模式中插入一条记录时，触发器应能够自动执行，因此需要创建基于INSERT 类型的触发器，即(m)空缺处应填入“AFTER INSERT ”；其触发条件是新插入交易记录的金额属性值大于100000，即(n)空缺处应填入“新交易. 金额＞100000.00”；最后(o)空缺处应添加表连接条件——“账户. 账户号=新交易. 账户号”。

试题三

8 仔细阅读题干给出的说明信息，由其中关键信息“(1)……的日常业务，每名员工只能在一个分公司工作”等可知，图1中实体“分公司”联系“隶属”，其联系的类型为一对多(1:*)。

基于题干给出的关键信息“(1)……”和常识可知，图1中实体“分公司”与子类实体“经理”之间存在联系“(1:1)。 根据题干给出的“(3)……交”等关键信息可知，图1中实体“客户”与实体“申请单”“，其关联的类型为一对多(1:*)。

结合题干给出的关键信息“(2)……一个快件申请只能由一个业务员受理”等可知，图1中子类实体“业务员””之间存在关联“受理”，其关联的类型为一对多(1:*)。

基于题干给出的“(2)……”等关键信息，结合常识可知，图1中子类实体“调度员””“”三者之间存在联系“安排承运”，其关联的类型为多对多对多(*:*:*)。 2所示。

图2 某物品运送信息管理系统实体联系图 9、这是一道要求读者掌握数据库的逻辑结构设计的综合理解题，本题的解答思路如下。

基于问题1的分析结果，结合常识可知，在关系模式“分公司(分公司编号，名称，经理，办公电话，地址1”中，属性“分公司编号”唯一标识每一条“分公司”的数据记录，因此它是“分公司”关系模式的主键。属性“经理”是“分公司”关系模式的外键。

对于图2中子类实体“员工”与实体“分公司”之间存在的一对多(1:*)联系“隶属”，根据E-R 模

型到关系模式的转换规则，在多端实体“员工”关系模式中应加入1端实体“分公司”的主键“分公司编号”(即(a)空缺处所填写的内容) 。该属性“分公司编号”作为“员工”关系模式的外键。结合常识，属性“员工号”唯一标识每一条“员工”的数据记录，因此它是“员工”关系模式的主键。

依据题干给出的关键信息“客户号唯一标识客户信息的每一个元组”可知，属性“客户号”是“客户”关系模式的主键。

结合题干给出的关键信息“(3)……快件申请信息包括申请号、客户号、发件人、发件人电话、快件名称、运费、发出地、收件人、收件人电话、收件地址。其中，一个申请号对应唯一的一个快件申请……”可知，属性“申请号”是“申请单”关系模式的主键。“申请号”是(b)空缺处所填写的内容之一。 对于图2中实体“客户”与实体“申请单”之间存在的一对多(1:*)联系“提交E-R 模型到关系模式的转换规则，在多端实体“申请单”关系模式中应加入1端实体“客户”“”(即(b)空缺处所填写的内容之一) 。属性“客户号”、“业务员”作为“申请单” 根据E-R 模型到关系模式的转换规则，联系“安排承运”其属性内容包括3端实体类型的主键(作为外键) ——申请号、属性“运达时间”。关系模式“安排承运”的主键为申请号、

10

在E-R 模型中，实体通过一组属性来表示，根据属属性和派生属性等。依复合属性。部分(即划分为别的属性) 的属性。

每名经理只能在一家分公司工作。此时，实体“”“经理”之间的联系“负责”，其联系的类型变更为一对多(1:*)。“”“分公司编号，经理”。 试题四

11BCNF 范式的综合分析题，本题的解答思路如下。

(1)依题意，由表2“”可知，属性“线路编号”是“线路信息””和“天数”是不属于该关系模式任何候选键的属性(非键属性) 。

(2)根据“线路信息”关系模式存在“线路编号→((价格，天数) 完全函数依赖于码且不传递依BCNF 范式。 12、这是一2NF 和3NF 的综合应用题，本题的解答思路如下。

“旅行社为游客在行程中的每个城市安排一个负责导游……”可知，属性“订单号，负责导游工号””(非键属性) 为“线路编”。

“关系模式的函数依赖为：

(订单号, 负责导游工号) →负责城市

订单号→(线路编号, 联系人身份证号, 人数, 订单价格, 出发时间)

联系人身份证号→(联系人名称, 联系方式)

根据第2范式(2NF)的要求：不存在非主属性对码的部分依赖。而“订单信息”关系模式存在非主属性对码的部分依赖，因此该关系模式不属于2NF ，会造成插入异常、删除异常和修改复杂(或修改异常) 等问题。

根据第3范式(3NF)的要求：每一个非主属性既不部分依赖于码，也不传递依赖于码。因此，需

要根据3NF 的要求和“订单信息”关系模式的函数依赖，对“订单信息”关系模式进行如下分解以满足3NF 。的要求。

订单信息1(订单号, 线路编号, 联系人身份证号, 出发时间, 订单价格, 人数)

订单信息2(, 联系人名称, 联系方式)

订单信息3(订单号, 负责导游工号, 负责城市)

其中，带下划线的属性为分解后的各关系模式对应的主键。 13、这是一道要求读者掌握关系模式规范化理论中4NF 的综合分析题，本题解答思路如下。 (1)由“员工信息”关系模式和题干中的关键信息可知，属性“员工工号，计薪月”“员工信息”关系模式的主键，而该关系模式不属于任何候选键的属性(非键属性) 为“姓名，员工类别，手机号，被投诉次数，带团人数，月薪”。

“员工信息”关系模式的函数依赖为：

员工工号→姓名, 出生日期, 员工类别

员工工号→手机号

(员工工号, 计薪月) →被投诉次数, 带团人数, 月薪

根据第4范式的要求：由“”依赖可知，由于“员工信息”关系模式存在部分依赖，2范式(2NF)；由于“员工信息”关系模式也存在多值依赖，因此该关系模式不满足第4 (2)根据第4范式(4NF)的要求和“员工信息”对”关系模式进行如下分解以满足4NF 的要求。

员工信息1(员工工号, 姓名, 出生日期, 员工类别)

员工信息2(员工工号, 手机号)

员工信息3(被投诉次数,

试题五

14

处理，若将转账业务作为一个完整的事务，则能保障其 从账户A 向账户B x 。假设参与转账的账户余额有大于等于0的约束，当A 0而违反该约束，不能被执行。此 15、这是一道要求读者掌握2PL 协议实际应用的综

ACID 属性，加锁机制是保障事务正确执行的一种机制。

·

·

要保证转账程序的并发执行，引入共享锁指令SLock(B)和独占锁指令XLock(A)对数据A 进行加锁，解锁指令Unlock(A)对数据A 进行解锁。用伪代码描述，即在R()、W()操作之前分别加指令SLock()和XLock ，不再使用数据之后再解锁，且第一个解锁指令后不能再有加锁指令。对于题干中转账业务的伪代码序列，使其满足2PL 协议的伪代码程序如下。 XLock(A),a=R(A),a=a-x,W(A,a),XLock(B),b=R(B),b=b+x,W(B,b),Unlock(A),Unlock(B) 16、这是一道要求读者掌握2PL 协议理论与SQL 中的隔离级别，以及嵌入式SQL 的编程实践题，本题的解答思路如下。事务的开始通常是隐性的，上一事务结束后第一条SQL 语句为事务开始，结束必须以RollBack 或Commit 显式地标明。在嵌入式SQL 编程中，使用标准SQL 定义的隔

离级别来实现事物并发执行下的一致性控制，4个隔离级别分别为Read Uncommitted、Read committed 、Repeatable Read和Serializable 。

此题中的转账程序应为一个完整的现实业务，就满足原子性。而第7行COMMIT 将程序分为了两个事务，不满足现实业务需求，删除该行即可。