数据库习题

习题一 数据库系统绪论

一、数据独立性包括数据的 物理独立性 和 逻辑独立性。
二、数据模型的组成三要素是数据结构 、 数据操作、 完整性约束条件。
三、两个实体型之间的联系分为三类，分别是 一对一关系、 一对多关系和 多对多关系 两个以上的实体型之间以及单个实体型也存在着同样的联系。
四、数据库的二级映像包括： 外模式/模式 映像 和 模式/内模式映像。外模式/模式映像保证了数据的逻辑 独立性，，模式/内模式保证了数据的 物理独立性
五、论述Data、Database（DB）、DBMS、DBS、DBA的概念以及关系

六、解释属于：模式、外模式、内模式、数据的逻辑独立性、数据的物理独立性（教材P29 - 30）

七、简述数据库管理员的职责（教材P32）

习题二 关系数据库

一、关系模型的数据结构非常简单，只包含单一的数据结构称为 关系
二、关系的描述可以有三种类型，分别是： 基本关系、 查询表、 视图表
三、关系的描述成为关系模式，它形式化地表示为R(U,D,DOM,F)，其中R表示 关系名，U表示组成该关系的 属性名集合，D表示属性U中属性所来自的域，DOM表示属性向域的映像集合，F表示属性间的数据的 依赖关系集合
四、关系模型由 关系数据结构、 关系操作集合、 关系的完整性约束
五、参照完整性规则是定义 外码与 主码之间的引用规则
六、职工（ 职工号，姓名，年龄，部门号） 和部门（ 部门号，部门名称）存在引用关系，其中 职工是参照关系， 职工中的部门号是外码。
七、在关系代数的八种基本运算中，并、差、笛卡尔积、选择和投影等物种运算为基本的运算，其他的三种运算，即 交、连接、和除，均可以用五种基本运算来表达。
八、解释术语：（1）实体完整性、参照完整性 （2）等值连接、自然连接
（1）
实体完整性：弱属性（只一个或一组属性）A是基本关系R的主属性，则A不能取空值，所谓空值就是“不知道”或“不存在”或“无意义”的值。
参照完整性：设F是基本关系R的一个或一组属性，但不是关系R的码，$K_S是基本关系S的竹马，如果F与$K_S$相对应，则称F是R的外码，并称基本关系R为参照关系，基本关系S为被参照关系或目标关系。
（2）
公式有些难打，还是看书吧P53页

九、习题P70-6（用关系代数完成前四个）
（1）求供应工程J1零件的供应商号码SNO

$${\Pi }_{SNO}({\sigma }_{JNO{=}^{\prime }J{1}^{\prime }}(SPJ))$$

（2）求供应工程J1零件P1的供应商号码SNO

$${\Pi }_{SNO}({\sigma }_{JNO{=}^{\prime }J{1}^{\prime }\Lambda PNO{=}^{\prime }P{1}^{\prime }}(SPJ))$$

（3）求·供应工程J1零件为红色的供应商号码SNO

$${\Pi }_{SNO}({\sigma }_{JNO{=}^{\prime }J{1}^{\prime }}(({\sigma }_{COLOR{=}^{\prime }红{色}^{\prime }}(P))\infty SPJ))$$

（4）求没有使用天津供应商生产的红色零件的工程号JNO

$${\Pi }_{SNO}(SPJ)-{\Pi }_{SNO}(({\sigma }_{CITY{=}^{\prime }天{津}^{\prime }\Lambda COLOR{=}^{\prime }红{色}^{\prime }}(S))\infty SPJ)$$

十、综合题：假设有一个数据库包含以下三个关系模式：
（1）Teacher（ Tno，Tname，Tage，Tsex）
（2）Department（ Dno，Dname，Tno）
（3）Work（ Tno，Dno，Year，Salary）
其中，教师表Teacher由教师代码（Tno）、教师姓名（Tname）、教师年龄（Tage）、教师性别（Tsex）组成，系表Department由系代码（Dno）、系名（Dname）、系主任代码（Tno）组成。工作表Work由教师代码（Tno）、系代码（Dno）、入职年份（Year）、工资（Salary）组成。
使用关系代数表示以下每个查询：
（1）列出工资超过5000的教师的不同年龄

$${\Pi }_{Tage}(({\sigma }_{salary>5000}(work))\infty Teacher)$$

（2）查找不在“计算机系”工作的教师代码

$${\Pi }_{Tno}({\sigma }_{Dname<{>}^{\prime }计算{机}^{\prime }}(Department\infty work))$$

（3）系主任T1管辖范围内的所有老师姓名。

$${\Pi }_{Tage}(({\sigma }_{Tno{=}^{\prime }T{1}^{\prime }}(Department))\infty work\infty Teacher)$$

习题三 关系数据库标准语言 SQL

一、根据以下表结构，用SQL语句（Create Table）创建每一个表。
（1）供应商表S(SNO,SNAME,STATUS,CITY)

字段名称	数据类型·	是否为空值	约束	说明
SNO	char(3)	x	主键	供应商代码
SNAME	xhar(10)	x	唯一值	供应商姓名
STATUS	int	x		供应商状态
CITY	varchar(10)	x		供应商所在城市

（2）零件表P（PNO，PNAME，COLOR，WEIGHT）

字段名称	数据类型·	是否为空值	约束	说明
PNO	char(3)	x	主键	零件代码
PNAME	xhar(10)	x	唯一值	零件名
COLOR	char(4)	x		颜色
WEIGHT	int	√		重量

（3）工程项目表J（JNO，JNAME，CITY）

字段名称	数据类型·	是否为空值	约束	说明
JNO	char(3)	x	主键	工程项目代码
JNAME	char(10)	x		工程项目名
CITY	char(10)	x		工程项目所在城市

（4）供应情况表SPJ（SNO，PNO，JNO，QTY）

字段名称	数据类型·	是否为空值	约束	说明
SNO	char(3)	x	主键外键	供应商代码
PNAME	char(3)	x	主键外键	零件代码
JNO	char(3)	x	主键外键	工程项目代码
QTY	int	x	大于0	供应数量

-- 供应商表
CREATE TABLE S(
	SNO VARCHAR(3) PRIMARY KEY,
	SNAME CHAR(10) NOT NULL UNIQUE,
	STATUS INT NOT NULL,
	CITY VARCHAR(10) NOT NULL
);
-- 零件表P
CREATE TABLE P (
	PNO CHAR(3) PRIMARY KEY,
	PNAEM CHAR(10) NOT NULL UNIQUE,
	COLOR CHAR(4) NOT NULL,
	WEIGHT INT 
);
-- 项目工程表 J
CREATE TABLE J(
	JNO CHAR(3) PRIMARY KEY,
	JNAME CHAR(10) NOT NULL,
	CITY CHAR(10) NOT NULL
);
--供应情况表
CREATE TABLE SPJ(
	SNO CHAR(3),
	PNO CHAR(3),
	JNO CHAR(3),
	QTY INT NOT NULL CHECK ( QTY > 0), -- check约束不起作用
	PRIMARY KEY(SNO,PNO,JNO),
	FOREIGN KEY (SNO) REFERENCES S(SNO),
	FOREIGN KEY (PNO) REFERENCES P(PNO),
	FOREIGN KEY (JNO) REFERENCES J(JNO)
);

二、针对上题中的四个表使用SQL语句完成以下各项操作（表内容详见P71页）
（1）查询供应商名称中含有’盛‘子的供应商名称以及所在的城市。

SELECT sname,city 
FROM s 
WHERE sname LIKE '%盛%';

（2）找出工程项目J2使用的各种零件的名称及其数量

SELECT pname,qty 
FROM spj INNER JOIN j ON spj.jno = j.jno
		 INNER JOIN p ON p.pno = spj.pno
WHERE jno = 'J2';

（3）找出使用’上海‘供应商的工程名称。（用连接查询和嵌套查询两种方法实现）

-- 嵌套查询
SELECT jname 
FROM j
WHERE jno IN (SELECT jno 
			  FROM spj 
			  WHERE sno IN (SELECT sno 
			  				FROM s 
			  				WHERE city = '上海'));	  				
-- 连接查询
SELECT jname 
FROM spj INNER JOIN j ON j.jno = spj.jno
	 INNER JOIN s ON s.sno = spj.sno
WHERE sname = '上海';

（4）统计各个供应商提供的各种不同零件的供应总数量，并显示供应商代码、零件代码。

SELECT sno,pno,SUM(qty) 
FROM spj
GROUP BY sno,pno;

（5）找出供应商供给不同零件供应量超过本种零件的平均供应量的供应商代码（用派生表实现p113）

SELECT sno 
FROM spj,(SELECT pno,AVG(qty) FROM spj GROUP BY pno) AS avg_spj(avg_pno,avg_qty)
WHERE spj.pno = avg_spj.sno AND spj.qty > avg_spj.qty;

（6）为’三建‘工程建立一个供应情况的视图V_SPJ，包括供应商代码、零件代码、供应数量，并要求进行修改和插入操作时仍需保证该视图只有’三建’工程的供应情况

CREATE VIEW V_SPJ AS
SELECT sno,pno,qty
FROM spj,j
WHERE spj.jno = j.jno AND j.jname = '三建'
WITH CHECK OPTION; 

（7）插入操作：将一条记录（零件号为P7的黑色螺钉）插入到零件表P中

INSERT 
INTO p(pno,pname,color) VALUES ('P7','螺钉','黑色'); 

（8）修改操作：把供应商号为S4的供应商所在城市改为’哈尔滨’,供应商状态改为10。

UPDATE 
s 
SET city = '哈尔滨',status = 10
 WHERE sno = 'S4';

（9）删除操作：从供应情况表SPJ中删除零件号为’P1’的记录

DELETE 
FROM spj 
WHERE pno = 'p1';

习题四 数据安全安全       习题五 数据库完整性

一、名词解释：数据库的安全性、自主存取方法、强制存取控制方法（P133，P140）

二、简述强制存取控制（MAC）规则

三、名词解释：数据库的完整性

习题六 关系数据理论

一、简述1NF、2NF、3NF、BCNF各级范式分别清除了那些属性（码）之间的什么样的数据依赖

过程	影响
1NF————>2NF	消除非主属性对码的部分函数依赖
2NF————>3NF	消除非主属性对码的传递函数依赖
3NF ————>BCNF	消除主属性对码的部分和传递函数依赖
BCNF————>4NF	消除非平凡且非函数依赖的多值依赖
4NF————>5NF	消除连接依赖

理解范式：详细讲解：https://blog.csdn.net/SevenGirl2017/article/details/77678233

二、关系模式R（员工编号，日期，零件数，部门名称，部门经理），表示某个工厂中生产零件数以及员工所在部门和经理信息。已知语义：每个员工每天只有一个日生产零件数，每个员工只在一个部门工作，每个部门只有一个经理。
要求：（1）写出关系模式R的基本函数依赖和码（2）判断R是几范式，并说明原因（3）将R分解成高一级范式
答案：

解：设关系模式R中的员工编号为A，日期为B，零件数为C，部门名称为D，部门经理为E
（1）写出关系模式R的基本函数依赖和码
根据上述语义确定基本函数依赖集F={（A，B）——> C，A——>D，D——>E}
码为（A，B）
（2）判断R是几范式，并说明原因？
R是一范式
原因：因为码为（A,B）存在非主属性（部门名称）对码的部分函数依赖（员工编号——>部门名称）
（3）将R分解成高一级范式
只要消除非主属性对码的部分函数依赖就行
分解
R1(A,B,C)，R2(A,D,E)
即R1（员工编号，日期，零件数），R2（员工编号，部门名称，部门经理）

三、关系模式R（商店编号，商品编号，商品库存数量，部门编号，负责人），表示某商业集团数据库中的想关信息。已知道语义：每个商店的每种商品只在该商店的一个部门中销售。每个商店的每个部门只有一位负责人，每个商店的每种商品只有一个库存数量。
要求：（1）写出关系模式R的基本函数依赖和码（2）判断R是几范式，并说明原因（3）将R分解成高一级范式
答案：

（1）写出关系模式R的基本函数依赖和码
设商店编号为A，商品编号为B，商品库存数量为C，部门编号为D，负责人为E
根据题干中的语义确定基本函数依赖集F = {（A，B）——> D，(（A，D）——>E，（A，B）——>C}
码为（A，B）即（商店编号，商品编号）
（2）判断R是几范式，并说明原因
R是二范式，原因：存在非主属性（E）对码（A，B）的传递函数依赖，所以R是二范式不是三范式
（3）分解
R1（A，B，C，D），R2（A，B，E）
即R1（商店编号，商品编号，商品库存数量，部门编号），R2（商店编号，商品编号，负责人）

四、关系模式R（教师编号，教师姓名，所在部门，借阅图书编号，书名，借书日期，还书日期，备注）
，表示某图书馆数据库中的相关信息，已知语义：教师编号决定教师姓名，且教师编号唯一，借阅图书编号决定书名，且借阅图书编号是唯一的，教师编号、借阅图书编号和借书日期决定还书日期。
要求：（1）写出关系模式R的基本函数依赖和码（2）判断R是几范式，并说明原因（3）将R分解三级范式
答案：

设教师编号为A，教师姓名为B，所在部门为C，借阅图书编号为D，书名为E，借书日期为F，还书日期为G，备注为H。
（1）写出关系模式R的基本函数依赖和码
根据题干中的语义确定基本函数依赖集F = {A ——>B，D ——>E，A——>C，A ——> H，（A，D，F）——>G}
码为（A，D，F）即（教师编号，借阅图书编号，借书日期）
（2）判断R是几范式，并说明原因
R是一范式，原因：存在非主属性（B，E）对码（A，D，F）的部分函数依赖
（3）
R1（A，B，C），R2（D，E），R3（A,D,F,H）
即R1（教师编号，教师姓名，所在部门），R2（借阅图书编号，书名），R3（教师编号，借阅图书编号，借书日期，还书日期，备注）

五、关系模式R（课程号，课程名，学分，授课教师号，教师名，授课时数），表示某学校管理数据库中的相关信息。已知语义：每个课程号决定课程名和学分，每名授课教师号决定教师名，每门课程可以由多名老师讲授，每名教师也可以讲授多门课程，每名教师对每门课程有确定的授课时数。
要求：（1）写出关系模式R的基本函数依赖和码（2）判断R是几范式，并说明原因（3）将R分解三级范式
答案：

设课程号为A，课程名为B，学分为C，授课教师号为D，教师名为E，授课时数为F
（1）写出关系模式R的基本函数依赖和码
根据题干中的语义确定基本的函数依赖集F={A——>B，A——>C，D——>E，（A，D）——>F}
码为（A，D） 即（课程名，授课教师号）
（2）判断R是几范式，并说明原因
一范式。原因：存在非主属性B，C，E对码（A，D）的部分函数依赖
（3）将R分解三级范式
R1（A，B，C），R2（D，E），R3（A，D，F）
即R1（课程号，课程名，学分），R2（授课教师号，教师名），R3（课程名，授课教师号，授课时数）

习题七 数据库设计

一、简述E-R图向关系模型转换原则

ER图转换为关系模型，实际上就是要将实体性实体的属性和实体性之间的联系转换为关系模式。一个实体性转换为一个关系模式，关系的属性就是实体的属性，关系的码就是实体的码。
①一个实体集转换为关系模型中的一个关系，实体的属性就是关系的属性，实体的码就是关系的码，关系的结构是关系模式。
②一个1:1联系可以转换为一个独立的关系，也可以与任意一端实体集所对应的关系合并。如果将1:1系转换为一个独立的关系，则与该联系相连的各实体的码以及联系本身的属性均转换为关系的属性，且每个实体的码均是该关系的候选码。如果将1:1联系与某一端实体所对应的关系合并，则需要在被合并关系中增加属性，其新增的属性为联系本身的属性和与联系相关的另一个实体的码。
③实体间的1:n联系可以有两种转换方法：一种方法是将联系转换为一个独立的关系，其关系的属性由与该联系相连的各实体集的码以及联系本身的属性组成，而该关系的码为n端实体的码；另一种方法是在n端实体集中增加新属性，新属性由联系对应的1端实体集的码和联系自身的属性构成，新增属性后原关系的码不变。
④一个m:n联系转换为一个关系：与该联系相连的各实体集的码以及联系本身的属性均转换为关系的属性，新关系的码为两个相连实体码的组合。

二、数据库设计，现有某高校图书管理系统的局部应用，包括”读者“，“书”两个实体，已知每个读者可借阅多本书，每本书可以被多个读者借阅，每本书在借阅时要记录借书日期和还书日期。
要求：（1）请设计适当的属性，画出E-R图

（2）请将E-R图转换为关系模式（之处关系名、属性名、主码）。

读者表（读者编号,读者姓名，读者性别，读者电话）
书表（书编号，书名，出版社，作者）
借阅表（读者编号，书编号，借书日期，还书日期）

（3）写出关系模式应该遵循的实体完整性、参照完整性约束条件。

实体完整性：读者表中的读者编号 为主码 不为空 ，书表中的书编号 为主码 不为空 ，借阅表中的读者编号以及书编号 为主码 不为空 。
参照完整性：借阅表中的读者编号参照读者表中的读者编号，借阅表中的书编号参照书表中的书编号

三、数据库设计，现在有某超市管理系统的局部应用，包括“超市”，“员工”两个实体。已知每个超市聘用多名员工，每名员工只能在一家超市工作，超市聘用的员工有聘期和每月底薪。要求：
（1）请设计适当的属性，画出E-R图

（2）请将E-R图转换为关系模式（之处关系名、属性名、主码）。

读者表（读者编号,读者姓名，读者性别，读者电话）
书表（书编号，书名，出版社，作者）
借阅表（读者编号，书编号，借书日期，还书日期）

（3）写出关系模式应该遵循的实体完整性、参照完整性约束条件。

实体完整性：读者表中的读者编号 为主码 不为空 ，书表中的书编号 为主码 不为空 ，借阅表中的读者编号以及书编号 为主码 不为空 。
参照完整性：借阅表中的读者编号参照读者表中的读者编号，借阅表中的书编号参照书表中的书编号