数据库概论必考经典例题课后重点答案.ppt

,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,单击此处编辑母版标题样式,*,3.,用,SQL,语句建立第二章习题,5,中的四个表：,供应商关系：,S(SNO,，,SNAME,，,STATUS,，,CITY),零件关系：,P(PNO,，,PNAME,，,COLOR,，,WEIGHT),工程项目关系：,J(JNO,JNAME,，,CITY),供应情况关系：,SPJ(SNO,PNO,JNO,QTY),定义的关系,S,有四个属性，分别是供应商号,(SNO),、供应商名,(SNAME),、状态,(STATUS),和所在城市,(CITY),，属性的类型都是字符型，长度分别是,4,、,20,、,10,和,20,个字符。主键是供应商编号,SNO,。在,SQL,中允许属性值为空值，当规定某一属性值不能为空值时，就要在定义该属性时写上保留字,“,NOT NULL”,。本例中，规定供应商号和供应商名不能取空值。由于已规定供应商号为主码，所以对属性,SNO,的定义中的,“,NOT NULL”,可以省略不写。,CREATE TABLE S(SNO CHAR(4)NOT NULL,，,SNAME CHAR(20)NOT NULL,，,STATUS CHAR(10),，,CITY CHAR(20),，,PRIMARY KEY,（,SNO,）；,CREATE TABLE P(PNO CHAR,（,4,）,NOT NULL,，,PNAME CHAR(20)NOT NULL,，,COLOR CHAR(8),，,WEIGHT SMALLINT,，,PRIMARY KEY(PNO),；,CREATE TABLE J(JNO CHAR(4)NOT NULL,，,JNAME CHAR(20),，,CITY CHAR(20),，,PRIMARY KEY(JNO),；,CREATE TABLE SPJ(SNO CHAR(4)NOT NULL,，,PNO CHAR(4)NOT NULL,，,JNO CHAR(4)NOT NULL,，,QTY SMALLINT,，,PRIMARY KEY(SNO,，,PNO,，,JNO),，,FOREIGN KEY(SNO)REFERENCES S(SNO),FOREIGN KEY(PNO)REFERENCES P(PNO),FOREIGN KEY(JNO)REFERENCES J(JNO),；,4.,针对上题中建立的四个表试用,SQL,语言完成第二章习题,5,中的查询,1),求供应工程,J1,零件的供应商号码,SNO;,2),求供应工程,J1,零件,P1,的供应商号码,SNO;,3),求供应工程,J1,零件为红色的供应商号,SNO;,4),求没有使用天津供应商生产的红色零件的工程号,JNO;,5),求至少用了供应商,S1,所供应的全部零件的工程号,JNO,1),求供应工程,J1,零件的供应商号码,SNO;,SELECT DISTINCT SNO FROM SPJ WHERE JNO=,J1,;,SELECT,子句后面的,DISTINCT,表示要在结果中去掉重复的供应商编号,SNO,。一个供应商可以为一个工程,J1,提供多种零件。,2),求供应工程,J1,零件,P1,的供应商号码,SNO;,SELECT SNO FROM SPJ,WHERE JNO=,J1,AND PNO=,P1,;,3),求供应工程,J1,零件为红色的供应商号,SNO;,SELECT DISTINCT SNO FROM SPJ,WHERE JNO=,J1,AND PNO IN,(SELECT PNO,FROM P,WHERE COLOR=,红,）,;,4),求没有使用天津供应商生产的红色零件的工程号,JNO;,常见错误：,SELECT JNO FROM J,WHERE NOT EXISTS,(SELECT*,FROM S,SPJ,P,WHERE SPJ.JNO=J.JNO AND,SPJ.SNO=S.SNO AND,SPJ.PNO=P.PNO AND,S.CITY=,天津,AND,P.COLOR=,红,);,当从单个表中查询时,目标,列表达式用*,若为多表必须用,表名,.*,正确写法,SELECT JNO,FROM J,WHERE NOT EXISTS,(SELECT S.*,SPJ.*,P.*,FROM S,SPJ,P,WHERE SPJ.JNO=J.JNO AND,SPJ.SNO=S.SNO AND,SPJ.PNO=P.PNO AND,S.CITY=,天津,AND,P.COLOR=,红,),4),求没有使用天津供应商生产的红色零件的工程号,JNO;,SELECT JNO FROM J,WHERE JNO NOT IN,(SELECT JNO,FROM S,SPJ,P,WHERE S.SNO=SPJ.SNO AND SPJ.PNO=P.PNO AND,S.CITY=,天津,AND P.COLOR=,红,);,SELECT JNO FROM J,WHERE NOT EXISTS,(SELECT*FROM SPJ,WHERE SPJ.JNO=J.JNO AND,SPJ.SNO IN,(SELECT SNO FROM S,WHERE S.CITY=,天津,)AND,SPJ.PNO IN,(SELECT PNO FROM P,WHERE P.COLOR=,红,),5),求至少用了供应商,S1,所供应的全部零件的工程号,JNO,SELECT DISTINCT JNO,FROM SPJ SPJ1,WHERE NOT EXISTS,(SELECT*,FROM SPJ SPJ2,WHERE SNO=S1 AND,NOT EXISTS PNO=ALL,(SELECT*,FROM SPJ SPJ3,WHERE PNO=SPJ2.PNO AND,JNO=SPJ1.JNO),5),求至少用了供应商,S1,所供应的全部零件的工程号,JNO,第一种理解：,SELECT DISTINCT JNO,FROM SPJ SPJX,WHERE NOT EXISTS,(SELECT*,FROM SPJ SPJY,WHERE SPJY.SNO=S1 AND,NOT EXISTS,(SELECT*,FROM SPJ SPJZ,WHERE SPJZ.JNO=SPJX.JNO AND,SPJZ.PNO=SPJY.PNO AND,SPJZ.SNO=SPJY.SNO);,查询结果：,第二种理解：,SELECT DISTINCT JNO,FROM SPJ SPJX,WHERE NOT EXISTS,(SELECT*,FROM SPJ SPJY,WHERE SPJY.SNO=S1 AND,NOT EXISTS,(SELECT*,FROM SPJ SPJZ,WHERE SPJZ.JNO=SPJX.JNO AND,SPJZ.PNO=SPJY.PNO);,查询结果：,J4,SPJZ.SNO=,S1,5.,针对习题,3,中的四个表试用,SQL,语言完成以下各项操作,1),找出所有供应商的姓名和所在城市,2),找出所有零件的名称、颜色、重量,3),找出使用供应商,S1,所供应零件的工程号码,4),找出工程项目,J2,使用的各种零件的名称及其数量,5),找出上海厂商供应的所有零件号码,6),找出使用上海产的零件的工程名称,7),找出没有使用天津产的零件的工程号码,8),把全部红色零件的颜色改成蓝色,9),有,S5,供给,J4,的零件,P6,改为由,S3,供应，请作必要的修改,10),从供应商关系中删除,S2,的记录，并从供应情况关系中删除相应的记录,11),请将,(S2,J6,P4,200),插入供应情况关系,1),找出所有供应商的姓名和所在城市,SELECT SNAME,CITY,FROM S;,2),找出所有零件的名称、颜色、重量,SELECT PNAME,COLOR,WEIGHT,FROM P;,3),找出使用供应商,S1,所供应零件的工程号码,SELECT DISTINCT JNO,FROM SPJ,WHERE SNO=S1;,4),找出工程项目,J2,使用的各种零件的名称及其数量,SELECT PNAME,QTY,FROM P,SPJ,WHERE P.PNO=SPJ.PNO AND SPJ.JNO=J2;,5),找出上海厂商供应的所有零件号码,SELECT DISTINCT PNO,FROM S,SPJ,WHERE S.SNO=SPJ.SNO AND S.CITY=,上海,;,SELECT DISTINCT PNO,FROM SPJ,WHERE SNO IN,(SELECT SNO,FROM S,WHERE S.CITY=,上海,);,6),找出使用上海产的零件的工程名称,SELECT JNAME,FROM S,SPJ,J,WHERE S.SNO=SPJ.SNO AND J.JNO=SPJ.JNO AND,S.CITY=,上海,;,7),找出没有使用天津产的零件的工程号码,SELECT JNO,FROM J,WHERE JNO NOT IN,(SELECT JNO,FROM SPJ,S,WHERE S.SNO=SPJ.SNO AND S.CITY=,天津,);,SELECT JNO,FROM J,WHERE NOT EXISTS,(SELECT*,FROM SPJ,WHERE JNO=J.JNO AND,SNO IN,(SELECT SNO,FROM S,WHERE S.CITY=,天津,);,SELECT JNO,FROM J,WHERE NOT EXISTS,(SELECT SPJ.*,S.*,FROM SPJ,S,WHERE JNO=J.JNO AND,SNO=S.SNO AND,S.CITY=,天津,;,8),把全部红色零件的颜色改成蓝色,UPDATE PSET COLOR=,蓝,WHERE COLOR=,红,;,9),由,S5,供给,J4,的零件,P6,改为由,S3,供应，请作必要的修改,UPDATE SPJ,SET SNO=S3,WHERE,SNO=S5,AND JNO=J4 AND PNO=P6,10),从供应商关系中删除,S2,的记录，并从供应情况关系中删除相应的记录,DELETE FROM S WHERE SNO=S2;,DELETE FROM SPJ WHERE SNO=S2,11),请将,(S2,J6,P4,200),插入供应情况关系,INSERT INTO SPJ VALUES(S2,P4,J6,200),常见错误：,INSERT INTO SPJ VALUES(S2,J6,P4,200),11.,请为三建工程项目建立一个供应情况的视图,SANJIAN_SPJ,，包括供应商代码,(SNO),、零件代码,(PNO),、供应数量,(QTY),。针对该视图完成下列查询：,1),找出三建工程项目使用的各种零件代码及其数量。,2),找出供应商,S1,的供应情况。,创建视图：,CREATE VIEW SANJIAN_SPJ,AS SELECT SNO,PNO,QTY,FROM SPJ,J,WHERE SPJ.JNO=J.JNO AND J.JNAME=,三建,;,1),找出三建工程项目使用的各种零件代码及其数量。,SELECT PNO,SUM(QTY)SELECT PNO,QTY,FROM SANJIAN_SPJ FROM SANJIAN_SPJ;,GROUP BY PNO;,2),找出供应商,S1,的供应情况。,SELECT*,FROM SANJIAN_SPJ,WHERE SNO=S1,数据库设计方法,1,）,基本设计法,分五步进行：,a.,创建用户视图,b.,汇总用户视图，得出全局数据视图，即概念模型。,c.,修改概念模型。,d.,转换并定义概念模型，转换成,DBMS,的数据模型。,e.,设计优化物理模型，即存储策略。,例如,1,关系模式,R(C,T,H,R,S,G),F=CT,CSG,HTR,HRC,HSR,则,=CT,CHR,HRT,CSG,HSR,为一个,3NF,的既具有无损联接性又具有函数依赖保持性的分解。,R,的码是,HS,。,例如,2,关系模式,R(A,B,C,D,E),F=AD,ED,DB,BCD,DCA,则,=ED,BCD,ACD,为一个,3NF,的具有函数依赖保持性的分解。,由于,R,的码是,CE,，则,=ED,BCD,ACD,CE,为一个,3NF,的既具有无损联接性又具有函数依赖保持性的分解。,例如,3,关系模式,R(C,S,Z),F=CSZ,ZC,则,R,属于,3NF,，可以分解为具有无损联接性的,BCNF,，而不可能分解成具有函数依赖保持性的,BCNF,。,当分解为,=SZ,CZ,，则它为一个,BCNF,的具有无损联接性的分解。,例如,4,关系模式,R(T,Q,P,C,S,Z),F=TQ,TP,TC,TS,PCSZ,ZP,ZC,试分解,R,属于,3NF,既具有无损联接性又具有函数依赖保持性。从题目可知码是,T,。,根据相同左部原则可分解为,=TQPCS,PCSZ,ZPC,，由于,ZPC,包含于,PCSZ,中，所以分解为,=TQPCS,PCSZ,。,而,R1=T,Q,P,C,S,属于,BCNF,。,但,R2=P,C,S,Z,不属于,BCNF,；再继续分解成,SZ,PCZ,后，则属于,BCNF,。,例如,5,关系模式,R(S,C,G,T,D),F=SCG,CT,TD,试分解成,BCNF,。从题目可知码是,SC,。,首先从关系,R,中分出,TD,，即,R1(S,C,G,T),R2(T,D),。,再从,R1,中分出,CT,，即,R3(C,T),R4(S,C,G),。,R2,R3,R4,都属于,BCNF,，分解完成。,习题：,求候选码，转换,3NF,，,BCNF,1,、设有关系模式,R(O,I,S,Q,B,D),，其中,F=SD,IB,ISQ,BO,。,2,、设有关系模式,R(A,B,C,D),，其中,F=AC,CA,BAC,DAC,BDA,。,3,、设有关系模式,R(A,B,C,D,E),，其中,F=AD,ED,DB,BCD,DCA,。,4,、设有关系模式,R(A,B,C,D,E,F),，其中,F=AB,CF,EA,CED,。,习题：,求候选码，转换成,BCNF,5,、设有关系模式,R(,学号,课程号,学分,成绩,奖学金,),，其中,F=,课程号学分,成绩奖学金,(,学号,课程号,),成绩,。,6,、设有关系模式,R(,学生,课程,教师,),，其中,F=,教师课程,(,学生,课程,),教师,。,习题答案,1,、,KEY=IS,2,、,KEY=BD,3,、,KEY=CE,4,、,KEY=CE,5,、,KEY=(,学号,课程号,),6,、,KEY=(,学生,课程,),；,R1(,学生,教师,),，,R2(,教师,课程,),例如,R(A,B,C),F=AB,CB,。当,1,=AB,AC,时，它具有无损联接性，但不具有依赖保持性。当,2,=AB,BC,时，它具有依赖保持性，但不具有无损联接性。,然而当,3,=AB,AC,BC,时，它既具有依赖保持性，又具有无损联接性。,依赖保持,设关系模式,R,的一个分解为,=R,1,R,2,.,R,k,，,F,是,R,的依赖集。如果,F,等价于,R,1,(F)R,2,(F).R,k,(F),，则称分解,具有依赖保持性。,一个无损联接分解不一定具有依赖保持性；,同样一个依赖保持分解不一定具有无损联接。,模式分解,若要求分解保持函数依赖，那么模式分解总可以达到,3NF,，但不一定能达到,BCNF,。,若要求分解既保持函数依赖，又具有无损联接性，那么模式分解可以达到,3NF,，但不一定能达到,BCNF,。,若要求分解既具有无损联接性，那么模式分解一定可以达到,4NF,。,求下列最高属于第几范式,1.,设,R(A,B,C,D),F=BD,ABC,。,2.,设,R(A,B,C,D,E),F=ABCE,EAB,CD,。,3.,设,R(A,B,C,D),F=BD,DB,ABC,。,4.,设,R(A,B,C),F=AB,BA,AC,。,5.,设,R(A,B,C),F=AB,BA,CA,。,6.,设,R(A,B,C,D),F=AC,DB,。,7.,设,R(A,B,C,D),F=AC,CDB,。,答案,1,、,Key=AB,R1NF,2,、,Key=AB,或,E,R2NF,3,、,Key=AB,或,AD,R3NF,4,、,Key=A,或,B,RBCNF,5,、,Key=C,R3NF,6,、,Key=AD,R1NF,7,、,Key=AD,R1NF,BCNF,定义,若,R1NF,，若,X,Y,且,Y X,时,X,必含有码。,例如：由于,(SNO,CNO)G,，满足,BCNF,的定义，所以,SC,属于,BCNF,。,当,S-L,分解成,SD(SNO,SDEPT),和,DL(SDEPT,SLOC),后的情形如下。,对于,SD,的函数依赖,SNOSDEPT,，所以它的码是,SNO,，所以,SD,属于,BCNF,。,对于,DL,的函数依赖,SDEPTSLOC,，所以它的码是,SDEPT,，所以,DL,属于,BCNF,。,3NF,定义,若,R1NF,，且每一个非主属性既不部分函数依赖于码也不传递函数依赖于码。,例如：当把,S-L-C,分解成,SC(SNO,CNO,G),和,S-L(SNO,SDEPT,SLOC),后。,由于,(SNO,CNO)G,，满足,3NF,的定义，所以,SC,属于,3NF,。,而,S-L,中候选码是,SNO,，但,SDEPTSLOC;SNOSDEPT,，即,非主属性,SLOC,传递依赖于码，所以,S-L,不属于,3NF,。,2NF,定义,若,R1NF,，且每一个非主属性完全函数依赖于码。,例如：,S-L-C(SNO,SDEPT,SLOC,CNO,G),，这里,SNO,表示学号，,SDEPT,表示系名，,SLOC,表示楼号，,CNO,表示课程号，,G,表示成绩。,函数依赖有,:(SNO,CNO)G;SDEPTSLOC;SNOSDEPT,。,所以候选码是,(SNO,CNO),。而,非主属性,SDEPT,和,SLOC,都是部分函数依赖于码，所以,S-L-C,不属于,2NF,，但属于,1NF,。,习题,设,R(A,B,C),r,为,R,的一个值，,r=ab1c1,ab2c2,ab1c2,ab2c1,。,问,1.r,满足条件,AB,吗？为什么？,2.,如果在,r,中任取一三个元组的子集，这些子集满足条件,AB,吗？为什么？,1.r,满足条件,AB,。,2.,不满足条件,AB,。,求关键字,1.,设,R(A,B,C,D,E,P),F=AD,ED,DB,BCD,CDA,。,2.,设,R(O,I,S,Q,B,D),F=SD,DS,IB,BI,BO,OB,。,3.,设,R(X,Y,Z,W),F=WY,YW,XWY,ZWY,XZW,。,4.,设,R(O,I,S,Q,B,D),F=SD,IB,BO,OQ,QI,。,5.,设,R(O,I,S,Q,B,D),F=IB,BO,IQ,SD,。,答案,1,、,CEP,2,、,QSI,，,QSO,，,QSB,，,QDB,，,QDI,，,QDO,3,、,XZ,4,、,SI,，,SQ,，,SB,，,SO,5,、,IS,四大定理,定理,1,：设,K,为,R,中的属性或属性组合，若,K,是,L,或,N,类，则,K,必为,R,的任一候选关键字成员。即是主属性。,定理,2,：设,X,为,R,中的属性或属性组合，若,X,是,R,类，则,X,不在任何候选关键字中。即是非主属性。,定理,3,：若,K,是,L,类，且,K,+,包含,R,的全部属性，则,K,必为,R,的唯一候选关键字。,定理,4,：若,K,是,L,和,N,类属性组合，且,K,+,包含,R,的全部属性，则,K,必为,R,的唯一候选关键字。,快速求解关键字,给定关系模式,R(A,1,A,2,.,A,n,),和函数依赖集,F,，可将其属性分为四类：,1,、仅仅出现在,F,的函数依赖左部的属性称,L,类；,2,、仅仅出现在,F,的函数依赖右部的属性称,R,类；,3,、在,F,的函数依赖左右均未出现的属性称,N,类；,4,、在,F,的函数依赖左右均出现的属性称,LR,类。,Student(,Sno,Sname,Sex,Bdate,Height),SC(,Sno,Cno,Grade),Course(,Cno,Lhour,Credit,Semester),在,SC,中,Sno,不是码，但却是,Student,的码，所以,Sno,是,SC,的外码。,在,SC,中,Cno,不是码，但却是,Course,的码，所以,Cno,是,SC,的外码。,学生,(,学号,姓名,性别,专业号,年龄,),专业,(,专业号,专业名,),在,学生,表中,专业号,不是码，但却是,专业,表的码，所以,专业号,是,学生,表的外码。,学生,2(,学号,姓名,性别,专业号,年龄,班长学号,),在,学生,2,表中,班长,不是码，但引用了本关系表,学号,属性，所以,班长,是,学生,2,表的外码。,练习题,求,F=AC,CA,BAC,DAC,BDA,的最小函数依赖集。,求,F=AD,ED,DB,BCD,DCA,的最小函数依赖集。,求,F=AB,BA,BC,AC,CA,的最小函数依赖集。,设有关系模式,R(O,I,S,Q,B,D),，,其中,F=SD,IB,ISQ,BO,，,试计算,S,+,I,+,B,+,(IS),+,(SB),+,(IB),+,(ISB),+,。,举例,F=ABC,CA,BCD,ACDB,DEG,BEC,CGBD,CEAG,，计算,(BD),+,。,解：,X,(0),=BD,X,(1),=X,(0),EG=BDEG DEG,X,(2),=X,(1),C=BCDEG BEC,X,(3),=X,(2),A=ABCDEG CA,由于,X,(3),=U,所以不需要继续计算,因此，,(BD),+,=ABCDEG,举例,F=ABC,CA,BCD,ACDB,DEG,BEC,CGBD,CEAG,，计算最小函数依赖集。,解：,1,、使依赖右部变成单属性：,F=ABC,CA,BCD,ACDB,DE,DG,BEC,CGB,CGD,CEA,CEG;,2,、去除多余函数依赖，即去除,XY,后，求,X,+,。若,Y X,+,，则可去除,XY,，否则不能去除。可得：,F=ABC,CA,BCD,CDB,DE,DG,BEC,CGD,CEG,