新建题库04040315

（含图）

（含图）

（含图）

【需求描述】

（1）患者到医院就诊，需提供本人医保卡或身份证，系统根据医保卡或身份证从外部医保信息库获取患者的其他详细信息，包括医保卡号、身份证号、姓名、性别、民族、出生日期等信息。

（2）医生信息包括医生编号、姓名、性别、出生日期、职称等信息。

（3）通过患者自述、化验结果分析等信息，医生对患者的病情进行诊断，开具处方，并填写病例。病例同时包含诊断和处方信息，包括编号、患者姓名、病情描述、诊断结论、主治医生、药品名称、药品数量、服用剂量等，处方中一般会有多种药品。

（4）病人凭医生开具的处方可在医院购买药品。医院记录药品的条码、名称、价格、生产厂商等信息。

【逻辑结构设计】

根据上述需求，设计出如下关系模式

医生（编号，姓名，性别，出生日期，职称）

患者（身份证号，医保卡号，姓名，性别，民族，出生日期）

药品（条码，名称，价格，生产厂商）

病例（编号，药品条码，患者身份证号，主治医生编号，病情描述，诊断结论，诊断日期，服用剂量，药品数量，购买日期）

【问题1】（5分）

对关系“患者”，请回答以下问题：

（1）给出函数依赖集。（2）给出所有候选码。（3）判定属于第几范式，并说明理由。

【问题2】对关系“病例”，存在如下的数据依赖：FD＝｛编号→（患者身份证号，主治医生编号，病情描述，诊断结论，诊断日期），（编号，药品条码）→（服用剂量，药品数量，购买日期）｝，请回答以下问题。

（1）该关系模式存在哪些问题？（2）该关系模式是否属于 4NF？请给给出理由。（3）如果“病例”不是 4NF，请分解，并指出分解后的关系模式所属范式。分解后的关系名依次为：病例 1，病例 2，...。

【问题3】

针对【问题 2】的规范化要求，如果设计者分解出的多个关系模式其中之一如下：

购药（病例编号，药品条码，服用剂量，药品数量，购买日期）

如果医院要求统计每名医生（编号和姓名）每天接诊的患者所购买的各个药品的条码和数量。基于上述设计，实现该统计共需哪几个关系？为提高该统计效率，允许对范式条件放宽要求，请修改“购药”关系模式以优化该统计。

驾驶员：EMP（Eno,Ename,Age,Sex,telephone），各属性分别表示驾驶员工号、姓名、年龄、性别和电话号码；

车辆：CAR（Cno,Brand,Capacity），各属性分别表示汽车车牌号、品牌名和排量；

调度：SCHEDULE（Sno,Eno,Cno,StartTime,Endtime），各属性分别表示调度号、驾驶员工号、汽车车牌号、发车时间和收车时间；

奖金：BONUS（Eno,Year,Month,Amount），各属性分别表示驾驶员工号、年、月和当月的奖金数量。

有关车辆调度的相关说明如下：

公车的行驶时间只能在工作时间内，因此规定调度表中每天安排发车的时间在上午07：00:00至下午18:00:00范围内。

【问题1】

请将下面创建调度关系的SQL语句的空缺部分补充完整，要求指定关系的主码、外码，以及调度表中每天安排发车的时间在上午07:00:00至下午18:00:00范围内的约束（由函数Time Get_time (DATETIME StartTime) 返回出车的时间）。

CREATE TABLE SCHEDULE(

Sno CHAR(10)，

Eno CHAR(10) ___(a)____，

Cno CHAR(8) ____(b)______，

StartTime DATETIME ____(c)______，

EndTime DATET IME，

PRIMARY KEY____(d)______)；

【问题2】

（1）创建所有'奥迪'品牌汽车的调度信息的视图AudiSCHEDULE，属性有Eno、Ename、Cno、Brand、StartTime和EndTime，请将下面SQL语句的空缺部分补充完整。

CREATE ______( e ) _____

AS

SELECT EMP，Eno，Ename，CAR.Cno，Brand，StartTime，EndTime

FROM EMP，CAR，SCHEDULE

WHERE ____( f ) _____；

（2）驾驶员的奖金在收车时间写入时，由出车时间段自动计算，并用触发器来实现奖金的自动维护，函数float Bonus_value(DATETIME StartTime, DATETIME EndTime)依据发车时间和收车时间来计算本次出车的奖金。系统在每月初自动增加一条该员工的当月奖金记录，初始金额为零。请将下面SQL语句的空缺部分补充完整。

CREATE____(g)_____Bonus_TRG AFTER____(h)_____ON SCHEDULE

REFERENCING new row AS nrow

FOR EACH ROW

BEGIN

UPDATE BONUS

SET_____(i)______

WHERE_____(j)_____AND Year=Get_Year(nrow.StartTime)

AND Month=Get_Month(nrow.StartTime)；

END

【问题3】

请将下面SQL语句的空缺部分补充完整。

（1）查询调度次数最多的汽车车牌号及其品牌。

SELECT CAR.Cno，Brand

FROM CAR，SCHEDULE

WHERE CAR.Cno=SCHEDULE.Cno

GROUP BY___(k)_______

HAVING____(1)______(SELECT COUNT(*) FROM SCHEDULE GROUP BY Cno)；

（2）查询所有在调度表中没有安排过“大金龙”品牌车辆的驾驶员工员和姓名。

SELECT Eno，Ename

FROM EMP

WHERE Eno _____(m)_____( SELECT Eno FROM _____(n)_____

WHERE ____(o)______ AND Brand='大金龙')；

日志记录内容中：请根据上述描述，回答以下问题。

（含图）

【问题1】假设各数据项的初始值为： I=22, J=45, K=9, 系统出错恢复后，I, J, K的数值会恢复为多少？

【问题2】请给出系统恢复时需要redo的事务列表和需要undo的事务列表。

请根据上述描述，回答以下问题。

【问题1】如果当前主数据库系统遭遇到了不可修复的硬盘故障，整个数据库系统如何工作？

【问题2】如果日志文件如表5-1所示，第一列表示日志记录编号，第二列表示日志记录内容。系统发生事务故障时，故障恢复有撤销事务（undo）和重做事务（redo）两个操作。请给出系统恢复时需要redo的事务列表和需要undo的事务列表？

| 日志记录编号 (LSN) | 日志记录内容 | 含义说明 |

| :--- | :--- | :--- |

| LSN1 | <T1, START> | 事务 T1 开始 |

| LSN2 | <T1, A, 0, 10> | T1 修改数据 A：旧值 0，新值 10 |

| LSN3 | <T2, START> | 事务 T2 开始 |

| LSN4 | <T2, B, 0, 9> | T2 修改数据 B：旧值 0，新值 9 |

| LSN5 | <T1, C, 0, 11> | T1 修改数据 C：旧值 0，新值 11 |

| LSN6 | <T1, COMMIT> | 事务 T1 提交（成功完成） |

| LSN7 | <T2, C, 11, 13> | T2 修改数据 C：旧值 11，新值 13 |

| LSN8 | <T3, START> | 事务 T3 开始 |

| LSN9 | <T3, A, 10, 8> | T3 修改数据 A：旧值 10，新值 8 |

| LSN10 | <T2, ROLLBACK> | 事务 T2 回滚（撤销） |

| LSN11 | <T3, B, 9, 7> | T3 修改数据 B：旧值 9，新值 7 |

| LSN12 | <T4, START> | 事务 T4 开始 |

| LSN13 | <T3, COMMIT> | 事务 T3 提交（成功完成） |

| LSN14 | CRASH | 系统发生故障 |

【问题3】在【问题2】中，假设系统开始执行前A=0，B=0，C=0，请问系统出现操作系统故障后，恢复后X，Y，Z各自的数值是多少？

图书馆的主要业务包括以下几项：

(1)对所有图书进行编目，每一书目包括ISBN号、书名、出版社、作者、排名，其中一部书可以有多名作者，每名作者有唯一的一个排名；

(2)对每本图书进行编号，包括书号、ISBN号、书名、出版社、破损情况、存放位置和定价，其中每一本书有唯一的编号，相同ISBN号的书集中存放，有相同的存储位置，相同ISBN号的书或因不同印刷批次而定价不同；

(3)读者向图书馆申请借阅资格，办理借书证，以后凭借书证从图书馆借阅图书。办理借书证时需登记身份证号、姓名、性别、出生年月日，并交纳指定金额的押金。如果所借图书定价较高时，读者还须补交押金，还书后可退还所补交的押金；

(4)读者借阅图书前，可以通过ISBN号、书名或作者等单一条件或多条件组合进行查询。根据查询结果，当有图书在库时，读者可直接借阅；当所查书目的所有图书己被他人借走时，读者可进行预约，待他人还书后，由馆员进行电话通知；

(5)读者借书时，由系统生成本次借书的唯一流水号，并登记借书证号、书号、借书日期，其中同时借多本书使用同一流水号，每种书目都有一个允许一次借阅的借书时长，一般为90天，不同书目有不同的借书时长，并且可以进行调整，但调整前所借出的书，仍按原借书时长进行处理；

(6)读者还书时，要登记还书日期，如果超出借书时长，要缴纳相应的罚款；如果所还图书由借书者在持有期间造成破损，也要进行登记并进行相应的罚款处罚。

初步设计的该图书馆管理系统，其关系模式如图4-1所示。

书目（ISBN号，书名，出版社，作者，排名，借书时长）

图书（书号，ISBN号，书名，出版社，破损情况，存放位置，定价）

读者（借书证号，身份证号，姓名，性别，出生年月日，联系电话，押金）

预约（预约流水号，ISBN号，借书证号，预约日期）

借还（流水号，借书证号，书号，借书日期，还书日期，罚款金额，罚款原因）

【问题1】

对关系“借还”，请回答以下问题：

(1)列举出所有候选键；

(2)根据需求描述，借还关系能否实现对超出借书时长的情况进行正确判定？用60字以内文字简要叙述理由。如果不能,请给出修改后的关系模式（只修改相关关系模式属注时，仍使用原关系名，如需分解关系模式，请在原关系名后加1，2，…等进行区别)。

【问题2】

对关系“图书”，请回答以下问题：

(1)写出该关系的函数依赖集；

(2)判定该关系是否属于BCNF，用60字以内文字简要叙述理由。如果不是，请进行修改，使其满足BCNF,如果需要修改其它关系模式，请一并修改，给出修改后的关系模式（只修改相关关系模式属性时，仍使用原关系名，如需分解关系模式，请在原关系名后加1，2，...进行区别）。

【问题3】

对关系“书目”，请回答以下问题：

(1)它是否属于第四范式，用60字以内文字叙述理由。

(2)如果不是，将其分解为第四范式，分解后的关系名依次为：书目1，书目2，…。 如果在解决【问题1】、【问题2】时，对该关系的属性进行了修改，请沿用修改后的属性。

员工表：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 )

)；

【问题2】

请将下面SQL语句的空缺部分补充完整。

（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

【问题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)车辆驶入高速路入口站点时，将驶入信息（ETC卡号，入口编号，驶入时间） 写入登记表；

(2)车辆驶出高速路出口站点（收费口）时，将驶出信息（ETC卡号，出口编号， 驶出时间）写入登记表；根据入口编号、出口编号及相关收费标准，清算应缴费用，并从绑定的信用卡中扣除费用。

一张ETC卡号只能绑定一张信用卡号，针对企业用户，一张信用卡号可以绑定多 个ETC卡号。使用表绑定（ETC卡号，信用卡号）来描述绑定关系，从信用卡（信用卡号，余额）表中扣除费用。

针对上述业务描述，完成下列问题：

【问题1】

在不修改登记表的表结构和保留该表历史信息的前提下，当车辆驶入时，如何保 证当前ETC卡已经清算过，而在驶出时又如何保证该卡已驶入而未驶出？请用100 字以内文字简述处理方案。

【问题2】

当车辆驶出收费口时，从绑定信用卡余额中扣除费用的伪指令如下：读取信用卡余额到变量X，记为x = R(A)；扣除费用指令x = x - a;写信用卡余额指令记为W(A, x)。

(1)当两个绑定到同一信用卡号的车辆同时经过收费口时，可能的指令执行序列为：x1=R(A)，x1 =x1-a1, x2 = R(A), x2 = x2-a2, W(A，x1)，W(A，x2)。此时会出现什么问题？（100字以内）

(2)为了解决上述问题，引入独占锁指令XLock(A)对数据A进行加锁，解锁指令Unlock(A)对数据A进行解锁。请补充上述执行序列，使其满足2PL协议。

【问题3】

下面是用E-SQL实现的费用扣除业务程序的一部分，请补全空缺处的代码。

CREATE PROCEDURE 扣除 (IN ETC 卡号 VARCHAR(20), IN 费用 FLOAT)

BEGIN

UPDATE 信用卡 SET 余额=余额-费用

FROM 信用卡,绑定

WHERE 信用卡.信用卡号=绑定.信用卡号 AND ( a ) ;

if error then ROLLBACK;

else ( b ) ;

END

【需求分析】

（1）系统需要维护球队、球员、主教练、主裁判、比赛等信息。

球队信息主要包括：球队编号、名称、成立时间、人数、主场地址、球队主教练。

球员信息主要包括：姓名、身份证号、出生日期、身高、家庭住址。

主教练信息主要包括：姓名、身份证号、出生日期、资格证书号、级别。

主裁判信息主要包括：姓名、身份证号、出生日期、资格证书号、获取证书时间、级别。

（2）每支球队有一名主教练和若干名球员。一名主教练只能受聘于一支球队，一名球员只能效力于一支球队。每支球队都有自己的唯一主场场地，且场地不能共用。

（3）足球联赛采用主客场循环制，一周进行一轮比赛，一轮的所有比赛同时进行。

（4）一场比赛有两支球队参加，一支球队作为主队身份、另一支作为客队身份参与比赛。一场比赛只能有一名主裁判，每场比赛有唯一的比赛编码，每场比赛都记录比分和日期。

【概念模型设计】

根据需求阶段收集的信息，设计的实体联系图（不完整），如图2-1所示。

（含图）

【逻辑结构设计】

根据概念结构设计阶段完成的实体联系图，得出如下关系模式（不完整）：

球队（球队编号，名称，成立时间，人数，主场地址）

球员（姓名，身份证号，出生日期，身高，家庭住址，（ 1 ） )

主教练（姓名，身份证号，出生日期，资格证书号，级别，（ 2 ）)

主裁判（姓名，身份证号，出生日期，资格证书号，获取证书时间，级别）

比赛（比赛编码，主队编号，客队编号，主裁判身份证号，比分，日期）

【问题1】（ 6分）

补充图2-1中的联系和联系的类型。图2-1中的联系“比赛”应具有的属性是哪些？

【问题2】根据图2-1，将逻辑结构设计阶段生成的关系模式中的空(1)～(2)补充完整。
【问题3】现在系统要增加赞助商信息，赞助商信息主要包括赞助商名称和赞助商编号。

赞助商可以赞助某支球队，一支球队只能有一个赞助商，但赞助商可以赞助多支球队。赞助商也可以单独赞助某些球员，一名球员可以为多个赞助商代言。请根据该要求，对图3-1进行修改，画出修改后的实体间联系和联系的类型。