数据结构与数据库.ppt

数据结构与数据库,习题课（3） 2016年12月16日,目 录,一、关系模式 二、关系代数 三、SQL 四、关系规范化,E-R 图,学校中有若干系，每个系有若干班级和教研室，每个教研室有若干教员，其中教授和副教授每人各带若干研究生；每个班有若干学生，每个学生选修若干课程，每门课可由若干学生选修。请用 E-R 图画出此学校的概念模型。,学校中有若干系，每个系有若干班级和教研室，每个教研室有若干教员，其中教授和副教授每人各带若干研究生；每个班有若干学生，每个学生选修若干课程，每门课可由若干学生选修。请用 E-R 图画出此学校的概念模型。,学校中有若干系，每个系有若干班级和教研室，每个教研室有若干教员，其中教授和副教授每人各带若干研究生；每个班有若干学生，每个学生选修若干课程，每门课可由若干学生选修。请用 E-R 图画出此学校的概念模型。,某医院病房计算机管理中心需要如下信息： 科室：科名、科地址、科电话 病房：病房号、床位号 医生：姓名、职称、年龄、工作证号 病人：病历号、姓名、性别 其中，一个科室有多个病房、多个医生，一个病房只能属于一个科室，一个医生只属于一个科室，但可负责多个病人的诊治，一个病人的主管医生只有一个，一个病房可以入住多个病人。 设计该计算机管理系统的E-R图,某医院病房计算机管理中心需要如下信息： 科室：科名、科地址、科电话 病房：病房号、床位号 医生：姓名、职称、年龄、工作证号 病人：病历号、姓名、性别 其中，一个科室有多个病房、多个医生，一个病房只能属于一个科室，一个医生只属于一个科室，但可负责多个病人的诊治，一个病人的主管医生只有一个，一个病房可以入住多个病人。 设计该计算机管理系统的E-R图,图书管理中，图书信息包括：书号、书名、作者、出版社；借书证信息包括：借书证号、姓名、办证日期、身份证号。请画出反映以上两实体间借阅联系和属性的E-R图，并分别转换为对应的关系模式结构。,图书管理中，图书信息包括：书号、书名、作者、出版社；借书证信息包括：借书证号、姓名、办证日期、身份证号。请画出反映以上两实体间借阅联系和属性的E-R图，并分别转换为对应的关系模式结构。,图书管理中，图书信息包括：书号、书名、作者、出版社；借书证信息包括：借书证号、姓名、办证日期、身份证号。请画出反映以上两实体间借阅联系和属性的E-R图，并分别转换为对应的关系模式结构。 关系模式：关系的描述称谓关系模式。关系模式通常可以简记R(A1,A2,An)，其中R为关系名， A1,A2,An为属性名。 在此题中，图书实体和借书证实体可用下面的关系表示（下划线为主码）,图书（书号，书名，作者，出版社） 借书证（借书证号，姓名，办证日期，身份证号）,某企业集团有若干工厂，每个工厂聘用多名职工，且每名职工只能在一个工厂工作，工厂聘用职工有聘期和工资。工厂的属性有工厂编号、厂名、地址；职工的属性有职工号、姓名、技术等级。 （1）画出E-R图。 （2）写出关系模式。,一个工厂聘用多名职工，且每名职工只能在一个工厂工作，工厂聘用职工有聘期和工资。工厂的属性有工厂编号、厂名、地址；职工的属性有职工号、姓名、技术等级。 （1）画出E-R图。 （2）写出关系模式。,一个工厂聘用多名职工，且每名职工只能在一个工厂工作，工厂聘用职工有聘期和工资。工厂的属性有工厂编号、厂名、地址；职工的属性有职工号、姓名、技术等级。 （1）画出E-R图。 （2）写出关系模式。 P43例2: 学生，课程，学生与课程之间的多对多联系 学生（学号，姓名，性别，专业号，年龄） 课程（课程号，课程名，学分） 选修（学号，课程号，成绩）,选修,课程,学生,n,m,某企业集团有若干工厂，每个工厂聘用多名职工，且每名职工只能在一个工厂工作，工厂聘用职工有聘期和工资。工厂的属性有工厂编号、厂名、地址；职工的属性有职工号、姓名、技术等级。 （1）画出E-R图。 （2）写出关系模式。,职工（职工号，姓名，技术等级 工厂（工厂编号，厂名，地址） 聘用（职工号，工厂编号，聘期，工资）,关系代数 习题,1、关系模型的三要素: 关系数据结构 关系操作集合 关系完整性约束 2、关系运算的三要素 运算对象 运算符 运算结果 3、关系代数运算的分类 传统的集合运算：并、差、交、广义笛卡尔积 专门的关系运算：选择、投影、连接、除,实体完整性、 参照完整性、 用户定义完整性,4. 设k元关系R，则24表示（ ）。 A、从R中挑选第4个分量的值小于2的元组所构成的关系 B、从R中挑选第2个分量值大于4的元组所构成的关系 C、从R中挑选第2个分量值大于第4个分量值的元组所构成的关系 D、 24与R相比,基数不变,元数减少 答案：B,5、设关系R和S的结构相同,且各有10个元组,那么这两个关系的并操作结果的元组个数为（ ）。 A、20 B、20 C、10 D、10 答案：B,6. 分析：C 在有n个顶点的无向完全图中，每一个顶点都有一条边与其它 某一顶点相连，所以每一个顶点有n-1条边与其他n-1个顶点相连， 总计n个顶点有n(n-1)条边。但在无向图中，顶点i到顶点j与顶点j到 顶点i是同一条边，所以总共有n(n-1)/2条边。,分析： 并、交、差要求关系R 、S具有相同的目，（即两个关系都有n个属性），相应的属性取自同一个域。 答案：A,7、设关系R、S、W各有10个元组，那么这3关系的自然联接的元组个数为（ ）。 A、10 B、30 C、1000 D、不确定（与计算结果有关） 答案：D,8、下列式子中,不正确的是（ ）。 A、R-S=R-(RS) B、R=(R-S)(RS) C、RS=S-(S-R) D、RS=S-(R-S) 答案：D,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,11、若有关系选课（学号，课号，成绩），对于每一门课，教室是固定的。已知有50个学生选修了课x，则当课x换教室时，需要修改的元组有（）。 A、1个 B、3个 C、50个 D、100个 答案：A,11、若有关系选课（学号，课号，成绩），对于每一门课，教室是固定的。已知有50个学生选修了课x，则当课x换教室时，需要修改的元组有（）。 A、1个 B、3个 C、50个 D、100个 答案：A,11、,答案： 基本关系的性质,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 2) 求供应工程 J1 零件P1的供应商号码 SNO ; SELECT DIST SNO FROM SPJ WHERE JNO=J1 AND PNO=P1,作业题解析,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);,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 = 蓝色,设有关系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号课程的学生学号和姓名。,练习题,1. 2. ALTER TABLE C MODIEY CNAME CHAR(40) 3.,W3School SQL 教程: 搜索“著名的 SQL 50题”,资源,数据结构与数据库习题课（三） 关系规范化,今要建立关于系、学生、班级、学会诸信息的一个关系数据库。一个系有若干专业，每个专业每年只招一个班，每个班有若干学生。一个系的学生住在同一宿舍区。每个学生可参加若干学会，每个学会有若干学生。 描述学生的属性有：学号、姓名、出生年月、系名、班号、宿舍区。 描述班级的属性有：班号、专业名、系名、人数、入校年份。 描述系的属性有：系名、系号、系办公室地点、人数。 描述学会的属性有：学会名、成立年份、地点、人数。学生参加某学会有一个入会年份。 请给出关系模式，写出每个关系模式的极小函数依赖集，支出是否存在传递函数依赖，对于函数依赖左部是多属性的情况，讨论函数依赖是完全函数依赖，还是部分函数依赖。指出各关系的候选码和外部码。,关系模式： （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),最小函数依赖集： 定义：如果函数依赖集F满足下列条件，则称F为最小函数依赖集或最小覆盖： （1）F中的任何一个函数依赖的右部仅含有一个属性； （2）F中不存在这样一个函数依赖XA，使得F与F-XA等价；（即：不存在冗余依赖关系） （3）F中不存在这样一个函数依赖XA，X有真子集Z使得F-XAZA与F等价。（即：依赖关系左部不存在冗余属性）,最小函数依赖集： 求解方法： （1）用分解的法则，使F中的任何一个函数依赖的右部仅含有一个属性； （2）去掉多余的函数依赖：从第一个函数依赖XY开始将其从F中去掉，然后在剩下的函数依赖中求X的闭包X+，看X+是否包含Y，若是，则去掉XY；否则不能去掉，依次做下去。直到找不到冗余的函数依赖； 属性X的闭包就是该属性直接或间接推导出的所有属性集合 （3）去掉各依赖左部多余的属性。一个一个地检查函数依赖左部非单个属性的依赖。例如XYA，若要判Y为多余的，则以XA代替XYA是否等价？若是，则Y是多余属性，可以去掉,最小函数依赖集： 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,最小函数依赖集： 同理，可以依次得到各个关系模式的最小函数依赖集： 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（完全函数依赖）,传递函数依赖： （1）对于关系模式S： Sno