数据库原理与应用试题及答案

205 210 …

CAM COG

30 155

12 20 12 25 30

AAA BBB AAA CCC DDD

01 02 01 11 12

10 15 30 25 15

（1）写出基本函数依赖集，并找出候选码。（4分） （2）判断最高达到第几范式，并说明理由。（2分） （3）如果有必要，将其分解成3NF模式集。（6分）

3． 设有图书关系BOOK（BID，BNAME，QTY），属性依次是书号、书名、库存数

量。规定当一批图书入库时，首先判断是否存在该书，如果存在则更改其库存数量，并通知客户最新的库存数量；否则，插入该书信息（书名暂时为空），并发出“新书入库”的提示信息。设计一个存储过程来实现此功能，该存储过程有书号（@bid）和入库数量（@s）2个参数。同时，要求设计一个SQL程序验证存储过程的作用。（10分） 4． 设有4个关系模式：

供应商关系：S（SNO，SNAME，CITY），属性依次是供应商号、供应商名称和所在城市；

零件关系：P（PNO，PNAME，COLOR），属性依次是零件号、零件名和颜色； 工程关系：J（JNO，JNAME，CITY），属性依次是工程号、工程名和所在城市； 供应关系：SPJ（SNO，PNO，JNO，QTY），属性依次是供应商号、零件号、工程号和数量。

用SQL语句实现下列操作：

（1） 查询P1号零件的颜色。（2分）

（2） 查询S1号供应商为J1号工程提供零件的编号和供应数量，查询结果按

零件号降序排列。（2分）

（3） 查询由S1号供应商提供红色零件的工程号。（2分）

（4） 查询与其提供零件的供应商所在城市为同一城市的工程号。（2分） （5） 统计所在地为杭州的工程数量。（2分）

（6） 统计每个供应商提供的零件总数。（2分）

（7） 查询比J1号工程使用的零件数量多的工程号。（2分） （8） 删除为由S1号供应商提供零件的工程信息。（2分）

标准答案及评分标准

一、单选题（20分）

1 B 11 B 2 D 2 C 13 A 3 A 14 C 4 A 15 C 5 D 16 D 6 D 17 D 7 B 18 C 8 C 19 A 9 C 10 C 10 C 2二、填空题（16分）

1 客户机/服务器 2 授权 3 事务日志 4PRIMARY KEY （公司号） （1） 4FOREIGN KEY （公司号） REFERENCES 公司（公司号） （2） 4CHECK（性别=‘男’OR 性别=‘女’） （3） 4CHECK（性别=‘女’AND 年龄>=18 AND 年龄<=50） （4） 5 π姓名，年龄（σ公司名=‘金山’^性别=‘男’（职工∞工作∞公司）） 三、简答题（14分）

1．答：DBMS的主要功能包括：数据库的定义功能、数据库的操纵功能、数据库的

运行控制功能、数据库的运行和维护功能、数据字典。（4分）。 2．答：一个事务若申请锁未被批准，则需等待其它事务释放锁。当事务之间出现循环等待时，如果不加干预，则会一起等待下去，从而产生死锁。（2分）常见的预防死锁的三种方法如下：

（4） 要求每一个事务必须同时封锁所要使用的全部数据。（1分）

（5） 对所有数据对象规定一个封锁的次序，要求所有事务必须按照这个次序封锁

数据对象。（1分）

（6） 当事务申请锁未被批准时，不等待加锁而是让一些事务回滚重新执行。（1分） 3. 答：事务是DBMS的执行单位，它由有限的数据库操作序列组成。（1分）事务具有以下四个特性：

（5） 执行的原子性。即不允许事务部分地完成。（1分）

（6） 保持数据库的一致性。即事务对数据库的作用应使数据库从一个一致状态转

变到另一个一致状态。（1分）

（7） 作用的持久性。即一个事务成功执行后，以数据库的影响应是永久的，即使

数据库故障被破坏，DBMS也应该能够恢复。（1分）

（8） 彼此的隔离性。即多个事务并发执行时，应象各个事务单独执行一样，互不

干扰。（1分）

四、设计题（50分）

1. （1）E－R图如下：（6分） 病房号 病历号 姓名 性别 工作证号 姓名 职称 病房 1 入住 M 病人 M 诊治 1 M 组成 1 科室 1 从属 M 医生 年龄 床位号 科名 科地址 科电话 （2）对应关系模型结构如下： 科室（科名，科地址，科电话）（1分） 病房（病房号，床位号，科名）（1分）

医生（工作证号，姓名，职称，科名，年龄）（2分）

病人（病历号，姓名，性别，诊断，主管医生，病房号）（2分）

2．（1）FD={部件号→部件名，部件号→现有数量，项目代号→项目内容，项目代号→项目负责人，（项目代号，部件号）→已提供数量}，候选码为（项目代号，部件号）。（4分）

（2）存在部件名属性对候选码的部分函数依赖，，因此最高达到1NF。（2分） （3）部件（部件号，部件名，现有数量）（2分） 项目（项目代号，项目内容，项目负责人）（2分） 提供（项目代号，部件号，已提供数量）（2分） 3． 存储过程：

CREATE PROCEDURE BOOK_QTY @bid INT, @s INT AS

DECLARE @bs INT

IF EXISTS（SELECT * FROM BOOK WHERE BID=@bid） BEGIN

UPDATE BOOK SET QTY=QTY+@s WHERE BID=@bid SELECT @bs=QTY FROM BOOK WHERE BID=@bid

PRINT ‘库存数量’+STR(@s)

--（2分） --（2分）

--（2分）

--（2分）

END ELSE

INSERT INTO BOOK（BID，QTY） VALUES（@bid，@s） --（2分） PRINT ‘新书入库’

GO 4．

（1）SELECT COLOR FROM P WHERE PNO=‘P1’（2分）

（2）SELECT PNO，QTY FROM SPJ WHERE JNO=‘J1’ AND SNO=‘S1’ORDER BY PNO DESC（2分）

（3）SELECT JNO FROM P JOIN SPJ ON（P.PNO=SPJ.PNO）WHERE COLOR=‘红’

AND SNO=‘S1’ （2分）

（4）SELECT J.PNO FROM J JOIN SPJ ON（J.PNO=SPJ.PNO）JOIN S ON（S.SNO=SPJ.SNO）WHERE J.CITY=S.CITY（2分）

（5）SELECT COUNT（JNO）FROM J WHERE CITY=‘杭州’ （2分） （6）SELECT SNO，SUM（QTY）FROM SPJ GROUP BY SNO（2分）

（7）SELECT JNO FROM SPJ GROUP BY JNO HAVING SUM（QTY）>（SELECT SUM（QTY） FROM SPJ WHERE JNO=‘J1’） （2分）

（8）DELETE FROM J WHERE JNO IN（SELECT JNO FROM SPJ WHERE SNO=‘S1’） （2分）