数据结构与数据库.ppt

数据结构与数据库,习题课（3）2018年12月18日,目录,一、关系模式二、关系代数三、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、20B、20C、10D、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、10B、30C、1000D、不确定（与计算结果有关）答案：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)不是等价的答案：C10、对表进行水平方向的分割用的运算是（）。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个表CREATETABLES(SNOC(2)UNIQUE,SNAMEC(6),CITYC(4);CREATETABLEP(PNOC(2)UNIQUE,PNAMEC(6),COLORC(2),WEIGHTINT);CREATETABLEJ(JNOC(2)UNIQUE,JNAMEC(8),CITYC(4);CREATETABLESPJ(SNOC(2),PNOC(2),JNOC(2),QTYINT).3.31)求供应工程J1零件的供应商号码SNO;SELECTDISTSNOFROMSPJWHEREJNO=J12)求供应工程J1零件P1的供应商号码SNO;SELECTDISTSNOFROMSPJWHEREJNO=J1ANDPNO=P1,作业题解析,3)求供应工程J1零件为红色的供应商号码SNOSELECTSNOFROMSPJ,PWHEREJNO=J1ANDSPJ.PNO=P.PNOANDCOLOR=红4)求没有使用天津供应商生产的红色零件的工程号JNOSELECTDISTJNOFROMSPJWHEREJNONOTIN(SELECTJNOFROMSPJ,P,SWHERES.CITY=天津ANDCOLOR=红ANDS.SNO=SPJ.SNOANDP.PNO=SPJ.PNO)5)求至少用了供应商Sl所供应的全部零件的工程号JNO不存在这样的零件Y，供应商S1生产了它，而工程X没有用(不存在工程X使用)。SelectDISTJNOfromSPJSPJXwherenotexist(select*fromSPJSPJYwhereSPJY.SNO=S1ANDnotexist(select*fromSPJSPJZwhereSPJZ.JNO=SPJX.JNOANDSPJZ.PNO=SPJY.PNO);,3.41)统计每种零件的供应总量:SelectPNO,sum(Qty)fromSPJgroupbyPNO2)零件供应总量在1000以上的供应商名SelectSNAMEfromSwhereSNOin(SelectSNOfromSPJgroupbySNOhavingSum(Qty)=1000)3)在S表中插入一条供应商信息(S6,华天,深圳)INSERTINTOS(SNo,SName,City)VALUES(S6,华天,深圳)4)把全部红色零件改成粉红色UPDATEPSETColor=粉红色whereColor=红色5)将s1供应给J1的零件p1改成p2UPDATESPJSETPNO=P2whereSNO=S1andJNO=J1andPNO=P16)删除全部蓝色零件及其相应的SPJ记录先从表再主表deletefromSPJwherePNOin(selectPNOfromPwhereColor=蓝色)deletefromPwhereColor=蓝色,设有关系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为CNAMEchar(40)。3、用SQL语句使用嵌套查询，查询出没有选修1号课程的学生学号和姓名。,练习题,1.2.ALTERTABLECMODIEYCNAMECHAR(40)3.,W3SchoolSQL教程:,资源,数据结构与数据库习题课（三）关系规范化,今要建立关于系、学生、班级、学会诸信息的一个关系数据库。一个系有若干专业，每个专业每年只招一个班，每个班有若干学生。一个系的学生住在同一宿舍区。每个学生可参加若干学会，每个学会有若干学生。描述学生的属性有：学号、姓名、出生年月、系名、班号、宿舍区。描述班级的属性有：班号、专业名、系名、人数、入校年份。描述系的属性有：系名、系号、系办公室地点、人数。描述学会的属性有：学会名、成立年份、地点、人数。学生参加某学会有一个入会年份。请给出关系模式，写出每个关系模式的极小函数依赖集，支出是否存在传递函数依赖，对于函数依赖左部是多属性的情况，讨论函数依赖是完全函数依赖，还是部分函数依赖。指出各关系的候选码和外部码。,关系模式：（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)根据现实生活及题中的规则，可以看出有如下的依赖关系：SnoSname，SnoDept，SnoSbirth，SnoClass，SnoRno，ClassDept，DeptRno（一个系的同学住同一个宿舍区）（1）把SnoSname去掉后，寻找Sno的闭包为Sno,Sbirth,Dept,Class,Rno，不包括Sname，因此SnoSname不能去除；（2）把SnoDept去掉后，寻找Sno的闭包为Sno,Sname,Sbirth,Dept,Class,Rno，由SnoClassDept从而包含Dept，因此SnoDept应该去除；（3）同理依次操作。SnoRno也可以去除。最小函数依赖集为：SnoSname,SnoSbirth,SnoClass,ClassDept,DeptRno,最小函数依赖集：同理，可以依次得到各个关系模式的最小函数依赖集：C：ClassPname,ClassCnum,ClassCyear,PnameDept(Pname,Cyear)Class（完全函数依赖）D：DeptDno,DnoDept,DnoOffice,DnoDnumM：MnameMyear,MnameMaddr,MnameMnumSM：(Sno,Mname)Smyear（完全函数依赖）,传递函数依赖：（1）对于关系模式S：SnoClass,而Clas