数据结构与数据库 习题课（3） 2016年12月16日.

Presentation on theme: "数据结构与数据库 习题课（3） 2016年12月16日."— Presentation transcript:

1 数据结构与数据库 习题课（3） 2016年12月16日

2 目 录 一、关系模式 二、关系代数 三、SQL 四、关系规范化

3 E-R 图

4 学校中有若干系，每个系有若干班级和教研室，每个教研室有若干教 员，其中教授和副教授每人各带若干研究生；每个班有若干学生，每 个学生选修若干课程，每门课可由若干学生选修。请用 E-R 图画出此 学校的概念模型。

5 学校中有若干系，每个系有若干班级和教研室，每个教研室有若干教 员，其中教授和副教授每人各带若干研究生；每个班有若干学生，每 个学生选修若干课程，每门课可由若干学生选修。请用 E-R 图画出此 学校的概念模型。

6 学校中有若干系，每个系有若干班级和教研室，每个教研室有若干教 员，其中教授和副教授每人各带若干研究生；每个班有若干学生，每 个学生选修若干课程，每门课可由若干学生选修。请用 E-R 图画出此 学校的概念模型。

7 某医院病房计算机管理中心需要如下信息： 科室：科名、科地址、科电话 病房：病房号、床位号 医生：姓名、职称、年龄、工作证号 病人：病历号、姓名、性别 其中，一个科室有多个病房、多个医生，一个病房只能属于一个科室，一个医生只属于一个科室，但可负责多个病人的诊治，一个病人的主管医生只有一个，一个病房可以入住多个病人。 设计该计算机管理系统的E-R图

8 某医院病房计算机管理中心需要如下信息： 科室：科名、科地址、科电话 病房：病房号、床位号 医生：姓名、职称、年龄、工作证号 病人：病历号、姓名、性别 其中，一个科室有多个病房、多个医生，一个病房只能属于一个科室，一个医生只属于一个科室，但可负责多个病人的诊治，一个病人的主管医生只有一个，一个病房可以入住多个病人。 设计该计算机管理系统的E-R图

9 图书管理中，图书信息包括：书号、书名、作者、出版社；借书证信息包括 ：借书证号、姓名、办证日期、身份证号。请画出反映以上两实体间借阅联 系和属性的E-R图，并分别转换为对应的关系模式结构。

10 图书管理中，图书信息包括：书号、书名、作者、出版社；借书证信息包括 ：借书证号、姓名、办证日期、身份证号。请画出反映以上两实体间借阅联 系和属性的E-R图，并分别转换为对应的关系模式结构。

11 图书管理中，图书信息包括：书号、书名、作者、出版社；借书证信息包括 ：借书证号、姓名、办证日期、身份证号。请画出反映以上两实体间借阅联 系和属性的E-R图，并分别转换为对应的关系模式结构。
关系模式：关系的描述称谓关系模式。关系模式通常可以简记R(A1,A2,…,An)， 其中R为关系名， A1,A2,…,An为属性名。 在此题中，图书实体和借书证实体可用下面的关系表示（下划线为主码） 图书（书号，书名，作者，出版社） 借书证（借书证号，姓名，办证日期，身份证号）

12 某企业集团有若干工厂，每个工厂聘用多名职工，且每名职工只能在 一个工厂工作，工厂聘用职工有聘期和工资。工厂的属性有工厂编号、 厂名、地址；职工的属性有职工号、姓名、技术等级。 （1）画出E-R图。 （2）写出关系模式。

13 一个工厂聘用多名职工，且每名职工只能在一个工厂工作，工厂聘用 职工有聘期和工资。工厂的属性有工厂编号、厂名、地址；职工的属 性有职工号、姓名、技术等级。 （1）画出E-R图。 （2）写出关系模式。

14 一个工厂聘用多名职工，且每名职工只能在一个工厂工作，工厂聘用 职工有聘期和工资。工厂的属性有工厂编号、厂名、地址；职工的属 性有职工号、姓名、技术等级。 （1）画出E-R图。 （2）写出关系模式。 P43例2: 学生，课程，学生与课程之间的多对多联系 学生（学号，姓名，性别，专业号，年龄） 课程（课程号，课程名，学分） 选修（学号，课程号，成绩） 学生 m 选修 n 课程

15 某企业集团有若干工厂，每个工厂聘用多名职工，且每名职工只能在 一个工厂工作，工厂聘用职工有聘期和工资。工厂的属性有工厂编号、 厂名、地址；职工的属性有职工号、姓名、技术等级。 （1）画出E-R图。 （2）写出关系模式。 职工（职工号，姓名，技术等级 工厂（工厂编号，厂名，地址） 聘用（职工号，工厂编号，聘期，工资）

16 关系代数 习题

17 传统的集合运算：并、差、交、广义笛卡尔积 专门的关系运算：选择、投影、连接、除
1、关系模型的三要素: 关系数据结构 关系操作集合 关系完整性约束 2、关系运算的三要素 运算对象 运算符 运算结果 3、关系代数运算的分类 传统的集合运算：并、差、交、广义笛卡尔积 专门的关系运算：选择、投影、连接、除 实体完整性、 参照完整性、 用户定义完整性

18 4. 设k元关系R，则σ2>’4’表示（ ）。
A、从R中挑选第4个分量的值小于2的元组所构成的关系 B、从R中挑选第2个分量值大于4的元组所构成的关系 C、从R中挑选第2个分量值大于第4个分量值的元组所构成的关系 D、σ 2>’4’与R相比,基数不变,元数减少 答案：B 5、设关系R和S的结构相同,且各有10个元组,那么这两个关系的并操作结果的元组个数为（ ）。 A、 B、≤20 C、 D、≤10 答案：B

19 分析： 并、交、差要求关系R 、S具有相同的目，（即两个关系都有n个 属性），相应的属性取自同一个域。 答案：A
6. 分析：C 在有n个顶点的无向完全图中，每一个顶点都有一条边与其它 某一顶点相连，所以每一个顶点有n-1条边与其他n-1个顶点相连， 总计n个顶点有n(n-1)条边。但在无向图中，顶点i到顶点j与顶点j到 顶点i是同一条边，所以总共有n(n-1)/2条边。 分析： 并、交、差要求关系R 、S具有相同的目，（即两个关系都有n个 属性），相应的属性取自同一个域。 答案：A

20 7、设关系R、S、W各有10个元组，那么这3关系的自然联接的元组个数为（ ）。
A、 B、30 C、 D、不确定（与计算结果有关） 答案：D 8、下列式子中,不正确的是（ ）。 A、R-S=R-(R∩S) B、R=(R-S)∪(R∩S) C、R∩S=S-(S-R) D、R∩S=S-(R-S) 答案：D

21 A、πD,B(R)为取属性值为D，B的两列组成新关系 B、π4,2(R)为取属性值为4，2的两列组成新关系
9、设4元关系R(A,B,C,D), 则（ ）。 A、πD,B(R)为取属性值为D，B的两列组成新关系 B、π4,2(R)为取属性值为4，2的两列组成新关系 C、πD,B(R)和π4,2(R)是等价的 D、πD,B(R)与π4,2(R)不是等价的 答案：C 10、对表进行水平方向的分割用的运算是 （） 。 A、交 B、投影 C、选择 D、连接 分析：投影操作主要是从列的角度进行运算 答案：C

22 11、若有关系选课（学号，课号，成绩），对于每一门课，教室是固定的。已知有50个学生选修了课x，则当课x换教室时，需要修改的元组有（）。
A、1个 B、3个 C、50个 D、100个 答案：A

23 11、若有关系选课（学号，课号，成绩），对于每一门课，教室是固定的。已知有50个学生选修了课x，则当课x换教室时，需要修改的元组有（）。
A、1个 B、3个 C、50个 D、100个 答案：A

24 11、

25 答案： 基本关系的性质

26 SQL

27 作业题解析 3.2 建立4个表 3.3 1) 求供应工程 J1 零件的供应商号码 SNO ;
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’ 2) 求供应工程 J1 零件P1的供应商号码 SNO ; SELECT DIST SNO FROM SPJ WHERE JNO=’J1’ AND PNO=’P1’

28 3) 求供应工程 J1 零件为红色的供应商号码 SNO
SELECT SNO FROM SPJ,P WHERE JNO='J1' AND SPJ.PNO=P.PNO AND COLOR='红' 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 JNO from 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 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) 3) 在S表中插入一条供应商信息(S6,华天,深圳) INSERT INTO S(SNo, SName, City) VALUES ('S6','华天','深圳') 4) 把全部红色零件改成粉红色 UPDATE P SET Color = '粉红色' where Color = '红色‘ 5) 将s1供应给J1的零件p1改成p2 UPDATE 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] = '蓝色'

30 练习题 设有关系S（S＃，SNAME，SAGE，SEX），C（C＃，CNAME），SC（S＃， C＃，GRADE）。其中S＃是学生号，SNAME是学生姓名，SAGE是学生年 龄，SEX是学生的性别，C＃是课程号，CNAME是课程名称，GRADE是成 绩。 1、用SQL语句创建S表，声明“S＃”为主码，“SNAME”不能为空， “SEX” 的取值为男或女。 2、用SQL语句修改C表中的列CNAME为CNAME char(40)。 3、用SQL语句使用嵌套查询，查询出没有选修1号课程的学生学号和 姓名。

31 1. 2. ALTER TABLE C MODIEY CNAME CHAR(40) 3.

32 资源 W3School SQL 教程: 搜索“著名的 SQL 50题”

33 数据结构与数据库习题课（三） ——关系规范化
数据结构与数据库习题课（三） ——关系规范化

34 今要建立关于系、学生、班级、学会诸信息的一个关系数据库。一个系有若干专业，每个专业每年只招一个班，每个班有若干学生。一个系的学生住在同一宿舍区。每个学生可参加若干学会，每个学会有若干学生。
描述学生的属性有：学号、姓名、出生年月、系名、班号、宿舍区。 描述班级的属性有：班号、专业名、系名、人数、入校年份。 描述系的属性有：系名、系号、系办公室地点、人数。 描述学会的属性有：学会名、成立年份、地点、人数。学生参加某学会有一个入会年份。 请给出关系模式，写出每个关系模式的极小函数依赖集，支出是否存在传递函数依赖，对于函数依赖左部是多属性的情况，讨论函数依赖是完全函数依赖，还是部分函数依赖。指出各关系的候选码和外部码。

35 关系模式： （1）学生：S(Sno, Sname, Sbirth, Dept, Class, Rno)
（2）班级：C(Class, Pname, Dept, Cnum, Cyear) （3）系：D(Dept, Dno, Office, Dnum) （4）学会：M(Mname, Myear, Maddr, Mnum) （5）学生-学会：SM(Sno, Mname, Smyear)

36 最小函数依赖集： 定义：如果函数依赖集F满足下列条件，则称F为最小函数依赖集或最小覆盖： （1）F中的任何一个函数依赖的右部仅含有一个属性；
（2）F中不存在这样一个函数依赖X→A，使得F与F-{X→A}等价；（即：不存在冗余依赖关系） （3）F中不存在这样一个函数依赖X→A，X有真子集Z使得F-{X→A}∪{Z→A}与F等价。（即：依赖关系左部不存在冗余属性）

37 最小函数依赖集： 求解方法： （1）用分解的法则，使F中的任何一个函数依赖的右部仅含有一个属性；
（2）去掉多余的函数依赖：从第一个函数依赖X→Y开始将其从F中去掉，然后在剩下的函数依赖中求X的闭包X+，看X+是否包含Y，若是，则去掉X→Y；否则不能去掉，依次做下去。直到找不到冗余的函数依赖； 属性X的闭包就是该属性直接或间接推导出的所有属性集合 （3）去掉各依赖左部多余的属性。一个一个地检查函数依赖左部非单个属性的依赖。例如XY→A，若要判Y为多余的，则以X→A代替XY→A是否等价？若是，则Y是多余属性，可以去掉

38 S(Sno,Sname,Sbirth,Dept,Class,Rno)
最小函数依赖集： S(Sno,Sname,Sbirth,Dept,Class,Rno) 根据现实生活及题中的规则，可以看出有如下的依赖关系： Sno → Sname， Sno → Dept ，Sno → Sbirth ，Sno → Class， Sno → Rno，Class → Dept，Dept → Rno （一个系的同学住同一个宿舍区） （1）把Sno → Sname去掉后，寻找Sno的闭包为Sno, Sbirth, Dept, Class, Rno，不包括Sname，因此Sno → Sname不能去除； （2）把Sno → Dept 去掉后，寻找Sno的闭包为Sno, Sname, Sbirth, Dept, Class, Rno，由Sno → Class → Dept从而包含Dept，因此Sno → Dept 应该去除； （3）同理依次操作。。。 Sno → Rno也可以去除。 最小函数依赖集为： Sno → Sname, Sno → Sbirth, Sno → Class, Class → Dept, Dept → Rno

39 最小函数依赖集： 同理，可以依次得到各个关系模式的最小函数依赖集：
C：Class → Pname, Class → Cnum, Class → Cyear, Pname → Dept (Pname,Cyear) →Class（完全函数依赖） D：Dept → Dno, Dno → Dept, Dno → Office, Dno → Dnum M：Mname → Myear, Mname → Maddr, Mname → Mnum SM：(Sno, Mname) → Smyear（完全函数依赖）

40 传递函数依赖： （1）对于关系模式S： Sno → Class, 而Class → Dept, Dept → Rno，所以Sno和Dept之间、Class和Rno之间、Sno和Rno之间存在传递函数依赖 （2）对于关系模式C： 由于Class → Pname，Pname → Dept，所以Class和Dept之间存在 传递函数依赖。 （3）对于关系模式D： 不存在传递函数依赖。 （4）对于关系模式M： （5）对于关系模式SM：

41 候选码、外部码和全码： （1）对于关系模式S： 候选码：Sno 外部码：Dept、Class （2）对于关系模式C： 候选码：Class
候选码：Dept或Dno 无外部码 （4）对于关系模式M： 候选码：Mname （5）对于关系模式SM： 候选码：(Sno, Mname) 外部码：Sno, Mname

42 B 1.X→Y，当下列哪一条成立时，称为平凡的函数依赖（ ）。 X ∈Y Y∈X X∩Y＝Φ X∩Y≠Φ
2.从关系规范化理论的角度讲，一个只满足1NF的关系可能存在的四方面问题是：数据冗余度大、修改异常、插入异常和 。 删除异常

43 其中：AB组合构成关系模式的主码，R上存在的函数依赖有（AB）→E，B→C，C→D，分析关系模式R是否达到3NF，如果没有将其分解到3NF。
3. 现有如下关系模式： R(A，B，C，D，E) 其中：AB组合构成关系模式的主码，R上存在的函数依赖有（AB）→E，B→C，C→D，分析关系模式R是否达到3NF，如果没有将其分解到3NF。 R关系中存在非主属性C对主码（AB）的部分函数依赖，非主属性D对主码（AB）的传递函数依赖，所以关系模式R没有达到3NF。分解之后的关系模式为： R1（A,B,E） R2（B,C） R3（C,D）