数据库期末练习参考答案

数据库期末试卷

—、关系数据库分析题(本题共10分)

某运输公司数据库中有一个车队的关系模式:
R(司机编号,汽车牌照,行驶公里,车队名称,车队主管)

此处每个汽车牌照对应一辆汽车。“行驶公里”为某司机驾驶某辆汽车行驶的总公里数。如果规定每个司机属于一个车队，每个车队只有一个主管。

（1）写出关系模式R的基本函数依赖和关键码。（4分)

（2）说明R不是 2NF 模式的理由,并把R分解成2NF模式集。(3分)

（3）进而把R分解成3NF模式集。(3分)

（1）基本FD={司机编号→车队名称,车队名称→车队主管,(司机编号,汽车牌照）→行驶公里}

∴R的关键码为(司机编号,汽车牌照)(4分)

（2）
∵车队名称和车队主管部分依赖于主码(司机编号,汽车牌照)

∴R不是2NF模式。
R应分解为 R1(司机编号,车队名称,车队主管)∈2NF
R2(司机编号,汽车牌照行驶公里)∈2NF(3分)
（3）
R2已是 3NF模式,但R1不是3NF模式。

∵司机编号→车队名称，车队名称→车队主管

∴司机编号→车队主管(传递)

R1应分解R11(司机编号,车队名称)
R12(车队名称,车队主管)

这样ρ=(R11,R12,R2),其中每个模式都是3NF模式。(3分)

第二、三、四题所用关系数据库模式如下:

设有如下4个关系模式:

书店（书店号，书店名，地址）

图书（书号，书名，定价)

图书馆（馆号，馆名，城市，电话）

图书发行（馆号，书号，书店号，数量）

二、试用关系代数表达式表示下列查询语句:(本大题共3小题,每小题5分,共15分)

1.查询书店号为“1005”的书店名和地址。
$$\Pi 书店名，地址(\sigma 书店号{=}^{\prime }1005^{\prime }(书店))$$
2.查询向“江苏海洋大学”图书馆发行图书的书店名。
$$\Pi 书店名(\sigma 馆名{=}^{\prime }江苏海洋大{学}^{\prime }(图书馆)\infty 图书发行\infty 书店)$$
3.查询向所有图书馆发行了图书的书号和书名。
$$\Pi 书号,书名((图书发行÷图书馆)\infty 图书)$$

三、试用SQL语句完成下列查询:(本大题共3小题,每小题5分,共15分)

（1）用SQL语句检索己发行的图书中最贵和最便宜的书名和定价。

SELECT 图书.书名,图书.定价
			FROM 图书
			WHERE定价=(SELECT MAX(定价)
					  FROM 图书,图书发行
                      WHERE 图书.书号=图书发行.书号)
			 UNION
			 SELECT 图书.书名,图书.定价
			 FROM 图书
			 WHERE 定价=(SELECT MIN(定价)
						FROM 图书,图书发行
                        WHERE 图书.书号=图书发行.书号)

（2）查询拥有已发行的"数据库系统基础"一书的图书馆馆名。

SELECT 馆名 FROM 图书馆 WHERE 馆号 
			IN (SELECT 馆号 FROM 图书发行
                WHERE书号 IN
					(SELECT 书号 FROM 图书
                     WHERE 书名='数据库系统基础'))

（3）查询“江苏海洋大学”图书馆的图书名、数量。

SELECT图书.书名,图书发行.数量 FROM 图书馆，图书发行,图书
WHERE 图书馆.馆号=图书发行.馆号 and 图书.书号=图书发行.书号
and 图书馆.馆名='江苏海洋大学'

四、应用题(用 SOL语言表示以下各题)(本大题共5小题、每小题6分,共30分)

1.创建视图V_B,查询由“新华书店”发行的图书的信息。

CREATE VIEW V_B AS
SELECT * from 图书 where 书号 in 
(SELECT 书号 from 图书发行 where 书店号 in (Select 书店号 from 书店 where 书店名 = '新华书店'))

2.在书店表的书店名称字段上建立非聚集索IDX_SNAME。

CREATE INDEX IDX_SNAME ON 书店(书店名)

3.用 SQL语句插入一本图书信息:(“B1001”，“数据结构” , 23.5)。

INSERT INTO 图书 VALUES("B1001","数据结构",23.5)

4.给"新华书店”向连去港市发行的书名为“十九届六中全会公报”的发行数量增加500册。

Update 图书发行 set 数量=数量+500 where 馆号 in
	(select 馆号 from 图书馆 where 城市='连云港')
	And 书号 in (select 书号 from 图书 where 书名='十九届六中全会公报')
	And 书店号 in(select 书店号 from 书店 where 书店名='新华书店')

5.建立一个存储过程 PROC_ B,要求输出指定书号的书名和定价。

CREATE PROCEDURE PROC_B (@bno char(10))
	AS
	SELECT 书名,定价 FROM 图书
	WHERE 书号=@bno

五、数据库设计（本题共30分)

某医院病房计算机管理中需要如下信息:

科室：科名，科地址，科电话

病房：病房号，床位号，所属科室名

医生：姓名，职称，所属科名，年龄，工号

病人：病历号，姓名，性别，主管医生，病房号

其中，一个科室有多个病房、多个医生,一个病房只能属于一个科室，一个医生只属于一个科室,但可负责多个病人的诊治,一个病人的主管医生只有一个。诊治有日期和医嘱。

（1）试画出E-R图,并在图上注明属性、联系的类型。(10分）E_R

（2）将（1)中E-R图转换为等价的关系模式结构,并说明主键和外键。(8分）

​ 科室（科名，地址，电话）

​ 病房（病房号，床位号，科名）

​ 医生（医生工号，姓名，职称，年龄，科名)

​ 病人（病历号，姓名，性别，病房号，医生工号）

​ 诊治（病历号，医生工号，日期，医嘱）

（3）使用 SQL语句创建一个名称为 Hospital的数据库,并设定数据文件为"E :\DBF\Hospital.MDF”，初始大小为10MB，最大为80MB，每次增长10MB。事务日志文件为“E:\DBF\Hospital_logLDF"，初始大小为10MB，最大为50MB，每次增长为5%（4分）

CREATE DATEBASE Hospital
	ON(
    	NAME=Hospital,FILENAME='E:\DBF\Hospital.MDF',
    	SIZE=10MB,MAXSIZE=80MB,FILEGROWTH=10MB
    )
    LOG ON(
    NAME=Hospital_log,FILENAME='E:\DBF\Hospital_log.LDF',
    SIZE=10MB,MAXSIZE=50MB,FILEGROWTH=5%
    )

（4）使用SQL命令创建（2）中的科室和诊治关系对应的数据库表，要求:（8分）

1）表名、字段名自拟。

2）字段类型、字段长度根据常识进行设置。

3）需要设置主键和外建。

科室表：
CREATE TABLE 科室
(
    科名 char(10) PRIMARY KEY,
 	地址 char(20),
 	电话 char(10)
	)             									(4分)
诊治关系表：
CREATE TABLE 诊治
(
    病历号 char(10) FOREIGN KEY REFERENCES 病人(病历号),
    医生工号 char(5) FOREIGN KEY REFERENCES 医生(工号),
    日期 datetime,
    医嘱 char(50),
    CONSTRAINT 约束名 PRIMARY KEY(病历号,日期)
	)                                               (4分)