【计算机类职业资格】中级数据库系统工程师下午试题-12及答案解析.doc

1、中级数据库系统工程师下午试题-12 及答案解析(总分：26.01，做题时间：90 分钟)一、试题一(总题数：1，分数：15.00)说明设有三个关系：BOOK (BNO，BNAME，PUBLISH_YEAR，PRICE，PRESS)READ(RNO，RNAME，DUTY)BR(BNO，RNO，RETURN_DAY)试用关系代数表达式表示下列查询语句：（分数：15.00）(1).问题 1检索读者号为 01 的读者所借的所有的书名。（分数：3.00）_(2).问题 2检索读者 DAVID 没有借的书号。（分数：3.00）_(3).问题 3检索在 1988 年以前出版的书名。（分数：3.00）_(4)

2、.问题 4检索被 DUTY 为老师的读者借出的所有的书名。（分数：3.00）_(5).问题 5检索借了书号为 BOOKl 这本书的读者号。（分数：3.00）_二、试题二(总题数：1，分数：4.00)1.下面是创建项目关系的 SQL 语句。请实现相关的完整性约束。CREATE TABLE 项目(项目编号 CHAR(6)，项目名称 VARCHAR(20)，状态 CHAR(1) CHECK (c) ，城市 VARCHAR(20)，负责人编号 CHAR(6) (d) ，FOREIGNKEY (e) REFERENCES (f) )；（分数：4.00）_三、试题三(总题数：1，分数：-1.00)2.在图

3、书馆数据库有三个基本表：书目表 Cata(书号 Cno、书名 Cname、作者 Cauthor、出版年 Cdate、价格 Cprice)、学生表 Student(学号 Sno、姓名 Sname、性别 Sgender、专业 Sdept)和借书历史表Borrow(所借书号 Cno、借书者学号 Sno、借书日期 Bbdate、还书日期 Brdate)。(1)请用 SQL 语句来建立这三个基本表(请注意数据类型的选择)。(2)请用 SQL 实现如下操作：图书馆里已有的鲁迅著作的名称按借书日期列出学生李明的所借书名往学生表 Student 中插入一个学生记录。(学号：132，姓名：张华，性别：男，系别：

4、计算机)把野火集的价格修改为 15 元(3)请用 SQL 语句来实现如下改变：为借书历史 Borrow 增加一个属性罚金 Bfine，同时删去书目表 Cata中的属性价格 Cprice。（分数：-1.00）_四、试题四(总题数：1，分数：-1.00)说明某鲜花订购网站有鲜花、顾客、员工三个实体集，其之间的联系是订货。并要求能提供下述服务：(1)可查询现存鲜花的名称、代号、数量和每支花的单价。其中，所有鲜花的代号是唯一标识的。(2)可查询顾客订货情况，包括订单号、顾客名、所订鲜花代号、订购数量、包装方式、联系方式、送货地。所有订单号唯一。(3)可查询员工的送货情况，包括员工号、送货日期、接受订单

5、号。一个订单只由一名员工送货。根据以上要求，完成如下任务：（分数：-0.99）(1).问题 1根据以上语义设计出 E-R 模型。（分数：-0.33）_(2).问题 2将该 E-R 模型转换为一组等价的关系模式。（分数：-0.33）_(3).问题 3对问题 2 的关系模型注明主键和外键。（分数：-0.33）_五、试题五(总题数：1，分数：9.00)阅读下列说明，回答下列问题说明某网上商品销售系统的业务流程如下：(1)将客户的订单记录(订单号，客户 ID，商品 ID，购买数量)写入订单表；(2)将库存表(商品 ID，库存量)中订购商品的库存量减去该商品的购买数量。针对上述业务流程，完成下列问题：（

6、分数：9.00）(1).假设库存量有大于等于 0 的约束，可能出现如下情况：当订单记录写入订单表后，修改库存表时因违法约束而无法执行，应如何处理?(100 字以内)（分数：4.50）_(2).引入如下伪指令：将商品 A 的订单记录插入订单表记为 I(A)；读取商品 A 的库存量到变量 x，记为x=R(A)；变量 x 值写入商品 A 中的库存量，记为 W(A，x)。则客户 i 的销售业务伪指令序列为：I i(A)，xi=Ri(A)，x i=xi-ai，W i(A，x i)。其中 ai为商品的购买数量。假设当前库存量足够，不考虑发生修改后库存量小于 0 的情况。若客户 1、客户 2 同时购买同一种

7、商品时，可能山现的执行序列为：I 1(A)，I 2(A)，x 1=R1(A)，x 2=R2(A)，x 1=x1-a1，W 1(A，x 1)，x 2=x2-a2，W 2(A，x 2)。(1)此时会出现什么问题?(100 字以内)(2)为了解决上述问题，引入共享锁指令 SLock(A)和独占锁指令 XLock(A)对数据 A 进行加锁，解锁指令Unlock(A)对数据 A 进行解锁，客户 i 的加锁指令用 SLocki(A)表示，其他类同。插入订单表的操作不需要引入锁指令。请补充上述执行序列，使其满足 2PL 协议，并使持有锁的时间最短。（分数：4.50）_中级数据库系统工程师下午试题-12 答案

8、解析(总分：26.01，做题时间：90 分钟)一、试题一(总题数：1，分数：15.00)说明设有三个关系：BOOK (BNO，BNAME，PUBLISH_YEAR，PRICE，PRESS)READ(RNO，RNAME，DUTY)BR(BNO，RNO，RETURN_DAY)试用关系代数表达式表示下列查询语句：（分数：15.00）(1).问题 1检索读者号为 01 的读者所借的所有的书名。（分数：3.00）_正确答案：(BNAME( RN0=01(BOOKD )解析：(2).问题 2检索读者 DAVID 没有借的书号。（分数：3.00）_正确答案：( BNO(BOOK)- BNO( RNAME=D

9、AVID(READ )解析：(3).问题 3检索在 1988 年以前出版的书名。（分数：3.00）_正确答案：( RNAME( PUBLIJSH_YEAR1998 (BOOK)解析：(4).问题 4检索被 DUTY 为老师的读者借出的所有的书名。（分数：3.00）_正确答案：( BNAME( BNO(BOOK) - BNO( DUTY=老师(READD BR)( )解析：(5).问题 5检索借了书号为 BOOKl 这本书的读者号。（分数：3.00）_正确答案：( RNO(BR) BNO( BSO=BOOKI(BOOK)解析：二、试题二(总题数：1，分数：4.00)1.下面是创建项目关系的 SQ

10、L 语句。请实现相关的完整性约束。CREATE TABLE 项目(项目编号 CHAR(6)，项目名称 VARCHAR(20)，状态 CHAR(1) CHECK (c) ，城市 VARCHAR(20)，负责人编号 CHAR(6) (d) ，FOREIGNKEY (e) REFERENCES (f) )；（分数：4.00）_正确答案：(c)(状态 IN(0，1)(d)NOT NULL(不区分人小写)(e)负责人编号(f)职工(职工编号)解析：解析 本题考核的还是完整性约束。用户定义项目状态有两个，既 0 和 1 分别表示未完成和已完成状态，用 CHECK 关键字；负责人编号是外码，用关键字 FOR

11、EIGN KEY REFERENCES，并且题中要求每个项目必须有负责人，负责人来自职工关系。三、试题三(总题数：1，分数：-1.00)2.在图书馆数据库有三个基本表：书目表 Cata(书号 Cno、书名 Cname、作者 Cauthor、出版年 Cdate、价格 Cprice)、学生表 Student(学号 Sno、姓名 Sname、性别 Sgender、专业 Sdept)和借书历史表Borrow(所借书号 Cno、借书者学号 Sno、借书日期 Bbdate、还书日期 Brdate)。(1)请用 SQL 语句来建立这三个基本表(请注意数据类型的选择)。(2)请用 SQL 实现如下操作：图书馆

12、里已有的鲁迅著作的名称按借书日期列出学生李明的所借书名往学生表 Student 中插入一个学生记录。(学号：132，姓名：张华，性别：男，系别：计算机)把野火集的价格修改为 15 元(3)请用 SQL 语句来实现如下改变：为借书历史 Borrow 增加一个属性罚金 Bfine，同时删去书目表 Cata中的属性价格 Cprice。（分数：-1.00）_正确答案：(1)CREATE TABLE Cata(Cno INT,Cname CHAR(40)，Cauthor CHAR(10)，Cdate INT，Cpriee float，)；CREATE TABLE Student(Sno INT，Sham

13、e CHAR(20)，Sgendcr CHAR(2)，Sdept CHAR(20)，)；CREATE TABLE BORROW(Cno INT，Sno INT，Bbdate INT：Brdate INT。)；(2)SELECTCnameFROM CataWHEREauthor=鲁迅；SELECT Cname from Cata，Student，BorrowWhere Student. Sno=Bom)w. Sno andCata. Cno=BorrowCno and Snamc=李明Group by BbdateINSERT INTO Student(Sno，Sname，Sgcn- der，S

14、dept)VALUES(132，张华，男，计算机)；UPDATE Cata SET Cprice=25WHERE Cnarse=野火集；(3)ALTER TABLE BorrowADD Brine float；ALTER TABLE Catadrop Cprice Float；)解析：四、试题四(总题数：1，分数：-1.00)说明某鲜花订购网站有鲜花、顾客、员工三个实体集，其之间的联系是订货。并要求能提供下述服务：(1)可查询现存鲜花的名称、代号、数量和每支花的单价。其中，所有鲜花的代号是唯一标识的。(2)可查询顾客订货情况，包括订单号、顾客名、所订鲜花代号、订购数量、包装方式、联系方式、送货

15、地。所有订单号唯一。(3)可查询员工的送货情况，包括员工号、送货日期、接受订单号。一个订单只由一名员工送货。根据以上要求，完成如下任务：（分数：-0.99）(1).问题 1根据以上语义设计出 E-R 模型。（分数：-0.33）_正确答案：( )解析：解析 概念数据模型 E-R 图及其与关系模式之间的关系；主键，外键。(2).问题 2将该 E-R 模型转换为一组等价的关系模式。（分数：-0.33）_正确答案：(其等价的关系模式为：鲜花(名称，代号，数量，单价)顾客(订单号，顾客名，所订鲜花代号，订购数量，包装方式，联系方式，送货地)员工(员工号，送货日期，接受订单号)解析：(3).问题 3对问题

16、 2 的关系模型注明主键和外键。（分数：-0.33）_正确答案：(“鲜花”中的“名称”“代号”分别可作为主键；“顾客”中的“订单号”可作为主键；“员工”中的“接受订单号”可作为主键，也是外键：)解析：五、试题五(总题数：1，分数：9.00)阅读下列说明，回答下列问题说明某网上商品销售系统的业务流程如下：(1)将客户的订单记录(订单号，客户 ID，商品 ID，购买数量)写入订单表；(2)将库存表(商品 ID，库存量)中订购商品的库存量减去该商品的购买数量。针对上述业务流程，完成下列问题：（分数：9.00）(1).假设库存量有大于等于 0 的约束，可能出现如下情况：当订单记录写入订单表后，修改库存

17、表时因违法约束而无法执行，应如何处理?(100 字以内)（分数：4.50）_正确答案：(将写订单记录和修改库存表作为一个完整的事务来处理，当修改库存表无法执行时，回滚事务，则会撤销写入的订单记录，数据库保持一致。)解析：本题考查事务及并发控制的概念和应用，属于比较传统的题目，考查点也与往年类似。本问题考查事务的基本概念。对于现实中的一项业务，相对应的数据库更新操作应作为一个完整的事务，要么全做要么全不做。销售业务对应的写入订单记录和更新库存表应作为一个事务，当出现故障(违反约束)而无法完成时，应回滚事务。(2).引入如下伪指令：将商品 A 的订单记录插入订单表记为 I(A)；读取商品 A 的库

18、存量到变量 x，记为x=R(A)；变量 x 值写入商品 A 中的库存量，记为 W(A，x)。则客户 i 的销售业务伪指令序列为：I i(A)，xi=Ri(A)，x i=xi-ai，W i(A，x i)。其中 ai为商品的购买数量。假设当前库存量足够，不考虑发生修改后库存量小于 0 的情况。若客户 1、客户 2 同时购买同一种商品时，可能山现的执行序列为：I 1(A)，I 2(A)，x 1=R1(A)，x 2=R2(A)，x 1=x1-a1，W 1(A，x 1)，x 2=x2-a2，W 2(A，x 2)。(1)此时会出现什么问题?(100 字以内)(2)为了解决上述问题，引入共享锁指令 SLoc

19、k(A)和独占锁指令 XLock(A)对数据 A 进行加锁，解锁指令Unlock(A)对数据 A 进行解锁，客户 i 的加锁指令用 SLocki(A)表示，其他类同。插入订单表的操作不需要引入锁指令。请补充上述执行序列，使其满足 2PL 协议，并使持有锁的时间最短。（分数：4.50）_正确答案：(出现问题：客户 1 购买后写入的库存量值被覆盖，库存量不能体现客户 1 已购买，属于丢失修改造成的数据库不一致性。(2)重写后的序列：I1(A)，I 2(A)，XLock 1(A)，x 1=R1(A)，x 1=x1-a1，W 1(A，x 1)，Unlock 1(A)，XLock 2(A)，x 2=R2

20、(A)，x 2=x2-a2，W 2(A，x 2)，Unlock 2(A)。)解析：本问题考查对事务并发控制的相关知识的理解掌握。若对并发事务的指令交叉执行不加以干涉，就会相互干扰，破坏事务的隔离性，造成数据库的不一致。并发事务产生的三种不一致性为丢失修改、不可重复读和读脏数据。本例中客户 1 对库存量的修改被客户 2 的修改覆盖，出现丢失修改不一致性。为保证可串行化调度，在事务执行过程中引入相应指令进行控制，即两段锁协议(2PL)，对数据读之前先加读锁，写前加写锁，事务只有获得相应的锁才能操作数据，加解锁过程分为两个阶段，前一阶段只能加锁，后一阶段只能解锁，不允许有交叉。两段锁协议是保证并发事务可串行化调度的充分条件。针对给出的伪指令操作序列，需要在修改库存量之前加 Xlock()指令，并保证客户 1 释放 A 上的锁后客户2 才能加锁。题目设定插入订单表的操作不需要引入锁指令，每个事务只需对库存量加解锁，满足 2PL 协议。