数据库习题课

1. 数据库习题课

2. Ch1. 概述 – 总结 • 数据库系统(DBS) 一个具有管理、控制和使用数据库功能的计算机系统。 • 硬件系统 • 数据库 • 数据库管理系统（及其开发工具） • 数据库应用程序 • 数据库管理员和终端用户

3. Ch1. 概述 – 总结 • 数据库(DB)长期存储在计算机内、有组织的、可共享的、相互关联的数据集合。 • 数据库管理系统(DBMS) 一个用来定义、创建、操作、使用和维护数据库的大型软件系统 。 • DBMS位于应用程序和操作系统之间。 • 例如sqlserver、mysql等。

4. Ch1. 概述 – 总结 • 局部应用逻辑结构描述，是一个用户的视图 • 描述每个特定用户使用的那一部分数据，隐藏其他部分数据 • 一个应用程序只能使用一个外模式 • 一个数据库有多个外模式 • 数据库系统特点（4个） • 数据库系统的模式结构 • 三级模式、两级印象 • 三级模式 • 外模式（用户模式、子模式） • 模式（逻辑模式、概念模式） • 内模式 （物理模式、存储模式） • 定义数据库的“型”，不涉及具体值。 • 各分类类型，长度，值域 • 数据之间联系 • 安全性、完整性 • 一个数据库只有一个模式 对数据的存储结构/物理结构的描述 一个数据库只有一个内模式

5. Ch1. 概述 – 总结 • 数据库系统特点（4个） • 数据库系统的模式结构 • 三级模式、两级印象 • 三级模式 • 两级映像 • 映象数据在各层之间转换请求和结果的处理过程称 • 外模式中的用户请求模式中的请求内模式中的请求 取数据

6. Ch1. 概述 – 总结 • 数据独立性 • 数据库系统在某一层次模式上的改变，不会影响它的上一层次模式也跟着发生变化的能力。 • 逻辑独立性 & 物理独立性 • 逻辑独立性： • 模式发生变化时，无需改变外模式或应用程序的能力。 • 物理独立性： • 内模式改变时，不会导致概念模式和外模式的发生变化的能力。

7. Ch1. 概述 – 总结 • 模型  概念模型 & 数据模型 • 概念模型：面向用户，按用户的观点对信息进行格式化处理(建模)，是现实世界到信息世界的抽象，与计算机无关（例如ER模型）。 • 数据模型：面向计算机，将格式化的信息转换为计算机能够识别和处理的数据，是信息世界到机器世界的抽象（例如网状模型、层次模型、关系模型）。

8. Ch1. 概述 – 总结 • 概念模型 • 术语&表示方法：实体、属性、码、域、实体型、实体集、联系。 • 联系：一对一、一对多、多对多。 • 实体-联系模型（ER模型） • 实体型、属性、联系（&联系的属性）、

9. Ch1. 概述 – 总结 • 概念模型 • 1.9学校有若干个系，每个系有若干班级和教研室，每个教研室有若干教员，其中有的教授和副教授没人各带若干研究生。每个班有若干学生，每个学生选修若干课程，每门课可由若干学生选修，用E-R图画出该学校的概念模型

10. Ch1. 概述 – 总结 • 数据模型 • …关系模型（二维表）：sqlserver、mysql，etc. • 逻辑结构：一组关系的集合。

11. Ch2. 关系数据库 • 关系数据库 – 支持关系模型的数据库。 • 关系模型三要素： • 数据结构：二维表 • 关系运算（操作）：操作对象和结果都是集合 • 完整性约束：实体、参照、用户定义的完整性 • 关系数据结构 • 关系定义 • 域、域的基数 • 笛卡尔积 • 关系笛卡尔积的任一个子集

12. Ch2. 关系数据库 • 关系数据结构 • 关系定义

13. Ch2. 关系数据库 • 关系数据结构 • 关系定义： • 候选码、主码、主属性、非码属性 • 关系性质：同列同域、异列可不同域、行列顺序无关、元组不同、分量不可再。 • 关系完整性 • 实体完整性：主码非空 • 参照完整性：属性组F为关系S中外码，与关系R中主码对应，则F为空或为R中某元组主码值

14. Ch2. 关系数据库 • 关系代数 • 运算对象-关系；运算结果-关系 • 集合运算符，专门关系运算符，算数比较符，逻辑运算符 • 集合运算符：交、并、差、广义笛卡尔积 • m+n列 • 专门关系运算 • 选择、投影、连接（自然连接&）、除

15. Ch2. 关系数据库 • 专门关系运算 • 选择 • 投影 • 从关系R中选取满足条件的列 • 连接 • 从两关系广义笛卡尔积中选取属性间满足条件的元组 • 等值连接：θ为= • 自然连接：等值连接中去除重复的属性

16. Ch2. 关系数据库 • 专门关系运算 • 除 • 关系R(X,Y)中x的象集Yx={t[Z] | t∈R && t[X]=x} • 即从R中选取X=x的元组，再仅留下Y属性组中分量 • 除： 通过连接操作 得到学生姓名 找出选修了全部 课程的学生号码

17. Ch2. 关系数据库 • 2.7 设有四个关系模式： • 1)求供应工程J1零件的供应商号码SNO： πSNO(σSNO=‘J1’(SPJ)) • 2)求供应工程J1零件P1的供应商号码SNO： πSNO(σSNO=‘J1’∧PNO=‘P1’(SPJ)) • 3)求供应工程J1零件为红色的供应商号码SNO： πSNO(σJNO=‘J1’(σCOLOR=‘红’(P)∞SPJ)) OR π𝑆𝑁𝑂(𝜎𝐽𝑁𝑂=‘𝐽1’∧𝐶𝑂𝐿𝑂𝑅=‘红’(π𝑃𝑁𝑂,𝐶𝑂𝐿𝑂𝑅(𝑃) ∞ 𝑆𝑃𝐽)) πSNO(σJNO=‘J1’ ∧ COLOR=‘红’(P)∞SPJ)) 连接零件表和供应关系 表，选取颜色为红色的行 选择工程为J1的行 选取SNO列

18. Ch2. 关系数据库 • 2.7 设有四个关系模式： • 4)求没有使用天津供应商生产的红色零件的工程号JNO： π𝐽𝑁𝑂(J) − π𝐽𝑁𝑂(π𝑃𝑁𝑂(𝜎𝐶𝑂𝐿𝑂𝑅=‘红’(𝑃))⋈𝑆𝑃𝐽⋈π𝑆𝑁𝑂(𝜎𝐶𝐼𝑇𝑌=‘天津’(𝑆))) • 5）求至少用了供应商S1所供应的全部零件的工程号JNO： πSNO,JNO,PNO(SPJ) ÷πSNO,PNO(σSNO=‘S1’(SPJ)) 得到所有工程号 通过三表连接选取城市为天津，零件为红色且在供应表中出现的行 投影得到JNO属性 选取供应商为S1的行 投影得到供应商S1供应的全部零件号PNO

19. Ch3. SQL • 基本内容 • 结构化查询语言 • 功能：数据定义、数据 查询、数据操纵、数据控制。 • 数据类型

20. Ch3. SQL • 数据定义 • CREATE基本语句： • CREATE TABLE name(colname1type1restrict1, colname2type2restrict2, … colnameNtypeNrestrictN); • 约束：列完整性约束、表级完整性约束

21. Ch3. SQL • 数据定义 • 约束： • 列完整性约束 • NOT NULL，UNIQUE • DEFAULT，CHECK • 表级完整性约束 • UNIQUE • PRIMARY KEY

22. Ch3. SQL • 数据定义 • 修改表 • 索引… • 数据查询

23. Ch3. SQL • 数据查询 • 单表查询 • 经过计算的值 • 去重复行 • 带条件查询 取值在集合内 %：匹配零个或多个字符串 “100%” _：匹配1个字符串 “张__”

24. Ch3. SQL • 数据查询 • 单表查询 • 带函数查询 • 连接查询 取别名

25. Ch3. SQL • 数据查询 • 嵌套查询. • in : 父查询结果是否在子查询结果集中。 • 可以连接查询形式表达 • 带比较运算符：用户确切知道内层返回结果是单值时 • 其他：ANY, ALL

26. Ch3. SQL • 数据查询 • 嵌套查询. • exist：返回逻辑值true/ false. • Select colname from tablename where exist (select …) • 内存返回结果非空，返回true

27. Ch3. SQL • 3.2建立4个表 • CREATE TABLE S (SNO C(2) UNIQUE, SNAME C(6), CITY C(4)); • CREATE TABLE P(PNO C(2) UNIQUE, PNAME C(6), COLOR C(2)， WEIGHT INT); • CREATE TABLE J(JNO C(2) UNIQUE, JNAME C(8), CITY C(4)); • CREATE TABLE SPJ(SNO C(2), PNO C(2), JNO C(2), QTY INT)). • 3.3 • 1) 求供应工程 J1 零件的供应商号码 SNO ; SELECT DIST SNO FROM SPJ WHERE JNO=’J1’ • 3) 求供应工程 J1 零件为红色的供应商号码 SNO ; SELECT SNO FROM SPJ,P WHERE JNO='J1' AND SPJ.PNO=P.PNO AND COLOR='红'

28. Ch3. SQL • 3.3 • 4)求没有使用天津供应商生产的红色零件的工程号 JNO ; SELECT DIST JNO FROM SPJ WHERE JNO NOT IN (SELECT JNO FROM SPJ,P,S WHERE S.CITY='天津' AND COLOR=‘ 红' AND S.SNO=SPJ.SNO AND P.PNO=SPJ.PNO) • 5) 求至少用了供应商Sl所供应的全部零件的工程号 JNO • 不存在这样的零件Y，供应商S1生产了它，而工程X没有用(不存在工程X使用)。 select DIST JNOfrom SPJ SPJX where not exist ( select * from SPJ SPJY where SPJY.SNO='S1' AND not exist (select * from SPJ SPJZ where SPJZ.JNO=SPJX.JNO AND SPJZ.PNO=SPJY.PNO));

29. Ch3. SQL • 3.3 • 5) 求至少用了供应商Sl所供应的全部零件的工程号 JNO A、查询S1供应商供应的零件号 SELECT DIST PNO FROM SPJ WHERE SNO='S1'结果是（P1，P2） B、查询哪一个工程既使用P1零件又使用P2零件。 SELECT JNO FROM SPJ WHERE PNO='P1' AND JNO IN (SELECT JNO FROM SPJ WHERE PNO='P2') • 3.4 • 1) 统计每种零件的供应总量:select PNO, sum(Qty) from SPJ group by PNO • 2) 零件供应总量在1000以上的供应商名select SNAME from S where SNO in (select SNO from SPJ group by SNO having Sum(Qty) >=1000)

30. Ch3. SQL • 3.4 • 3) 在S表中插入一条供应商信息(S6,华天,深圳)INSERT INTO S(SNo, SName, City) VALUES ('S6','华天','深圳') • 4) 把全部红色零件改成粉红色UPDATE P SET Color = '粉红色' where Color = '红色‘ • 5) 将s1供应给J1的零件p1改成p2UPDATE SPJ SET PNO = 'P2' where SNO = 'S1' and JNO = 'J1' and PNO = 'P1‘ • 6)删除全部蓝色零件及其相应的SPJ记录 先从表再主表delete from SPJ where PNO in (select PNO from P where Color = '蓝色')delete from P where [Color] = '蓝色'

31. Ch4. 关系数据库设计理论 • 数据依赖 • 属性之间相互制约，相互依存关系。 • R(U, D, DOM, F)，F属性间的依赖集合。 • 函数依赖： • R(U)，X, Y为U的两个子集，对R(U)任一可能关系r， 不存在两个元组，当x相同时y不同，则xy。 • 非平凡函数依赖：Y不为X的子集 • 完全函数依赖：X任意真子集有x’y，反之部分依赖 • 传递函数依赖：xy, yz且y x, yx

32. Ch4. 关系数据库设计理论 • 范式 按一定级别进行规范化的关系模式 • 1NF：分量是不可分的数据项 • 2NF：每个非主属性完全依赖于码 • 3NF：每个非主属性既不部分依赖也不传递依赖于码。 • BCNF：所有的决定因素都是候选码

33. Ch4. 关系数据库设计理论 • 已知学生关系模式 S(Sno，Sname，SD，Sdname，Course，Grade) 其中：Sno学号、Sname姓名、SD系名、Sdname系主任名、Course课程、Grade成绩。 • (1)写出关系模式S的基本函数依赖和主码。 • (2)原关系模式S为几范式？为什么？分解成高一级范式，并说明为什么? • (3)将关系模式分解成3NF，并说明为什么?

34. Ch4. 关系数据库设计理论 • (1)写出关系模式S的基本函数依赖和主码。 • 答: 关系模式S的基本函数依赖如下： Sno→Sname，SD→Sdname，Sno→SD，(Sno，Course) →Grade • 关系模式S的码为：（Sno，Course）。 • (2)原关系模式S为几范式？为什么？分解成高一级范式，并说明为什么? • 答: 原关系模式S是属于1NF的，码为(Sno，Course)，非主属性中的成绩完全依赖于码，而其它非主属性对码的函数依赖为部分函数依赖，所以不属于2NF。 • 消除非主属性对码的函数依赖为部分函数依赖，将关系模式分解成2NF如下： S1(Sno，Sname，SD，Sdname) S2(Sno，Course，Grade)

35. Ch4. 关系数据库设计理论 • 将关系模式分解成3NF，并说明为什么? • 答：将上述关系模式分解成3NF如下： 关系模式S1中存在Sno→SD，SD→Sdname，即非主属性Sdname传递依赖于Sno，所以S1不是3NF。进一步分解如下： S11(Sno，Sname,SD) S12(SD，Sdname) 分解后的关系模式S11、S12满足3NF。 • 对关系模式S2不存在非主属性对码的传递依赖，故属于3NF。所以，原模式S(Sno，Sname，SD，Sdname，Course，Grade)按如下分解满足3NF。 S11(Sno，Sname，SD) S12(SD，Sdname) S2(Sno，Course，Grade)

36. Ch4. 关系数据库设计理论 • 4.3 建立一个关于系、学生、班级、学会等诸信息的关系数据库。 学生：学号、姓名、出生年月、系名、班号、宿舍区。 班级：班号、专业名、系名、人数、入校年份。 系：系名、系号、系办公地点、人数。 学会：学会名、成立年份、办公地点、人数。 语义如下：一个系有若干专业，每个专业每年只招一个班，每个班有若干学生。一个系的学生住在同一宿舍区。每个学生可参加若干学会，每个学会有若干学生。学生参加某学会有一个入会年份。 • 关系模式如下： 学生：S(Sno，Sname，Sbirth，Dept，Class，Rno) 班级：C(Class，Pname，Dept，Cnum，Cyear) 系：D(Dept，Dno，Office，Dnum) 学会：M(Mname，Myear，Maddr，Mnum)

37. Ch4. 关系数据库设计理论 • 4.3 • 最小依赖、传递 • 学生S (Sno，Sname，Sbirth，Dept，Class，Rno) 的最小函数依赖集如下: SnoSname，SnoSbirth，SnoClass，ClassDept，DeptRno 传递依赖如下： 由于Sno  Dept，而Dept ! Sno ，Dept  Rno(宿舍区) 由于Class  Dept，Dept ! Class，Dept  Rno 由于Sno  Class，Class ! Sno，Class  Dept 所以Class与Rno, Sno与Rno, Sno与Dept之间存在着传递函数依赖。 •  班级C(Class，Pname，Dept，Cnum，Cyear)的最小函数依赖集如下: Class  Pname，Class  Cnum，Class  Cyear，Pname  Dept. 由于Class  Pname，Pname ! Class，Pname  Dept 所以C1ass与Dept之间存在着传递函数依赖

38. Ch4. 关系数据库设计理论 • 4.3 • 最小依赖、传递 • 系D(Dept，Dno，Office，Dnum)的最小函数依赖集如下： Dept  Dno，Dno  Dept，Dno  Office，Dno  Dnum • 学会M(Mname，Myear，Maddr，Mnum)的最小函数依赖集如下： Mname  Myear，Mname  Maddr，Mname  Mnum • 码 • 学生S候选码：Sno；外部码：Dept、Class；无全码 •   班级C候选码：Class；外部码：Dept；无全码 •   系D候选码：Dept或Dno；无外部码；无全码 •   学会M候选码：Mname；无外部码；无全码 • 备注： • 学生学会表S2M(Sno，Mname，Myear)