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

1,3.用SQL语句建立第二章习题5中的四个表：,供应商关系：S(SNO，SNAME，STATUS，CITY)零件关系：P(PNO，PNAME，COLOR，WEIGHT)工程项目关系：J(JNO,JNAME，CITY)供应情况关系：SPJ(SNO,PNO,JNO,QTY),2,定义的关系S有四个属性，分别是供应商号(SNO)、供应商名(SNAME)、状态(STATUS)和所在城市(CITY)，属性的类型都是字符型，长度分别是4、20、10和20个字符。主键是供应商编号SNO。在SQL中允许属性值为空值，当规定某一属性值不能为空值时，就要在定义该属性时写上保留字“NOTNULL”。本例中，规定供应商号和供应商名不能取空值。由于已规定供应商号为主码，所以对属性SNO的定义中的“NOTNULL”可以省略不写。CREATETABLES(SNOCHAR(4)NOTNULL，SNAMECHAR(20)NOTNULL，STATUSCHAR(10)，CITYCHAR(20)，PRIMARYKEY（SNO）；,3,CREATETABLEP(PNOCHAR（4）NOTNULL，PNAMECHAR(20)NOTNULL，COLORCHAR(8)，WEIGHTSMALLINT，PRIMARYKEY(PNO)；CREATETABLEJ(JNOCHAR(4)NOTNULL，JNAMECHAR(20)，CITYCHAR(20)，PRIMARYKEY(JNO)；CREATETABLESPJ(SNOCHAR(4)NOTNULL，PNOCHAR(4)NOTNULL，JNOCHAR(4)NOTNULL，QTYSMALLINT，PRIMARYKEY(SNO，PNO，JNO)，FOREIGNKEY(SNO)REFERENCESS(SNO),FOREIGNKEY(PNO)REFERENCESP(PNO),FOREIGNKEY(JNO)REFERENCESJ(JNO)；,4,4.针对上题中建立的四个表试用SQL语言完成第二章习题5中的查询1)求供应工程J1零件的供应商号码SNO;2)求供应工程J1零件P1的供应商号码SNO;3)求供应工程J1零件为红色的供应商号SNO;4)求没有使用天津供应商生产的红色零件的工程号JNO;5)求至少用了供应商S1所供应的全部零件的工程号JNO,5,1)求供应工程J1零件的供应商号码SNO;SELECTDISTINCTSNOFROMSPJWHEREJNO=J1;SELECT子句后面的DISTINCT表示要在结果中去掉重复的供应商编号SNO。一个供应商可以为一个工程J1提供多种零件。2)求供应工程J1零件P1的供应商号码SNO;SELECTSNOFROMSPJWHEREJNO=J1ANDPNO=P1;3)求供应工程J1零件为红色的供应商号SNO;SELECTDISTINCTSNOFROMSPJWHEREJNO=J1ANDPNOIN(SELECTPNOFROMPWHERECOLOR=红）;,6,4)求没有使用天津供应商生产的红色零件的工程号JNO;常见错误：SELECTJNOFROMJWHERENOTEXISTS(SELECT*FROMS,SPJ,PWHERESPJ.JNO=J.JNOANDSPJ.SNO=S.SNOANDSPJ.PNO=P.PNOANDS.CITY=天津ANDP.COLOR=红);,当从单个表中查询时,目标列表达式用*,若为多表必须用表名.*,正确写法SELECTJNOFROMJWHERENOTEXISTS(SELECTS.*,SPJ.*,P.*FROMS,SPJ,PWHERESPJ.JNO=J.JNOANDSPJ.SNO=S.SNOANDSPJ.PNO=P.PNOANDS.CITY=天津ANDP.COLOR=红),7,4)求没有使用天津供应商生产的红色零件的工程号JNO;SELECTJNOFROMJWHEREJNONOTIN(SELECTJNOFROMS,SPJ,PWHERES.SNO=SPJ.SNOANDSPJ.PNO=P.PNOANDS.CITY=天津ANDP.COLOR=红);SELECTJNOFROMJWHERENOTEXISTS(SELECT*FROMSPJWHERESPJ.JNO=J.JNOANDSPJ.SNOIN(SELECTSNOFROMSWHERES.CITY=天津)ANDSPJ.PNOIN(SELECTPNOFROMPWHEREP.COLOR=红),8,5)求至少用了供应商S1所供应的全部零件的工程号JNOSELECTDISTINCTJNOFROMSPJSPJ1WHERENOTEXISTS(SELECT*FROMSPJSPJ2WHERESNO=S1ANDNOTEXISTSPNO=ALL(SELECT*FROMSPJSPJ3WHEREPNO=SPJ2.PNOANDJNO=SPJ1.JNO),9,5)求至少用了供应商S1所供应的全部零件的工程号JNO,第一种理解：SELECTDISTINCTJNOFROMSPJSPJXWHERENOTEXISTS(SELECT*FROMSPJSPJYWHERESPJY.SNO=S1ANDNOTEXISTS(SELECT*FROMSPJSPJZWHERESPJZ.JNO=SPJX.JNOANDSPJZ.PNO=SPJY.PNOANDSPJZ.SNO=SPJY.SNO);查询结果：,第二种理解：SELECTDISTINCTJNOFROMSPJSPJXWHERENOTEXISTS(SELECT*FROMSPJSPJYWHERESPJY.SNO=S1ANDNOTEXISTS(SELECT*FROMSPJSPJZWHERESPJZ.JNO=SPJX.JNOANDSPJZ.PNO=SPJY.PNO);查询结果：J4,SPJZ.SNO=S1,10,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)插入供应情况关系,11,1)找出所有供应商的姓名和所在城市SELECTSNAME,CITYFROMS;2)找出所有零件的名称、颜色、重量SELECTPNAME,COLOR,WEIGHTFROMP;3)找出使用供应商S1所供应零件的工程号码SELECTDISTINCTJNOFROMSPJWHERESNO=S1;,12,4)找出工程项目J2使用的各种零件的名称及其数量SELECTPNAME,QTYFROMP,SPJWHEREP.PNO=SPJ.PNOANDSPJ.JNO=J2;,13,5)找出上海厂商供应的所有零件号码SELECTDISTINCTPNOFROMS,SPJWHERES.SNO=SPJ.SNOANDS.CITY=上海;SELECTDISTINCTPNOFROMSPJWHERESNOIN(SELECTSNOFROMSWHERES.CITY=上海);6)找出使用上海产的零件的工程名称SELECTJNAMEFROMS,SPJ,JWHERES.SNO=SPJ.SNOANDJ.JNO=SPJ.JNOANDS.CITY=上海;,14,7)找出没有使用天津产的零件的工程号码SELECTJNOFROMJWHEREJNONOTIN(SELECTJNOFROMSPJ,SWHERES.SNO=SPJ.SNOANDS.CITY=天津);SELECTJNOFROMJWHERENOTEXISTS(SELECT*FROMSPJWHEREJNO=J.JNOANDSNOIN(SELECTSNOFROMSWHERES.CITY=天津);,SELECTJNOFROMJWHERENOTEXISTS(SELECTSPJ.*,S.*FROMSPJ,SWHEREJNO=J.JNOANDSNO=S.SNOANDS.CITY=天津;,15,8)把全部红色零件的颜色改成蓝色UPDATEPSETCOLOR=蓝WHERECOLOR=红;9)由S5供给J4的零件P6改为由S3供应，请作必要的修改UPDATESPJSETSNO=S3WHERESNO=S5ANDJNO=J4ANDPNO=P610)从供应商关系中删除S2的记录，并从供应情况关系中删除相应的记录DELETEFROMSWHERESNO=S2;DELETEFROMSPJWHERESNO=S211)请将(S2,J6,P4,200)插入供应情况关系INSERTINTOSPJVALUES(S2,P4,J6,200)常见错误：INSERTINTOSPJVALUES(S2,J6,P4,200),16,11.请为三建工程项目建立一个供应情况的视图SANJIAN_SPJ，包括供应商代码(SNO)、零件代码(PNO)、供应数量(QTY)。针对该视图完成下列查询：1)找出三建工程项目使用的各种零件代码及其数量。2)找出供应商S1的供应情况。,17,创建视图：CREATEVIEWSANJIAN_SPJASSELECTSNO,PNO,QTYFROMSPJ,JWHERESPJ.JNO=J.JNOANDJ.JNAME=三建;1)找出三建工程项目使用的各种零件代码及其数量。SELECTPNO,SUM(QTY)SELECTPNO,QTYFROMSANJIAN_SPJFROMSANJIAN_SPJ;GROUPBYPNO;2)找出供应商S1的供应情况。SELECT*FROMSANJIAN_SPJWHERESNO=S1,18,数据库设计方法,1）基本设计法分五步进行：a.创建用户视图b.汇总用户视图，得出全局数据视图，即概念模型。c.修改概念模型。d.转换并定义概念模型，转换成DBMS的数据模型。e.设计优化物理模型，即存储策略。,19,例如1,关系模式R(C,T,H,R,S,G),F=CT,CSG,HTR,HRC,HSR,则=CT,CHR,HRT,CSG,HSR为一个3NF的既具有无损联接性又具有函数依赖保持性的分解。R的码是HS。,20,例如2,关系模式R(A,B,C,D,E),F=AD,ED,DB,BCD,DCA,则=ED,BCD,ACD为一个3NF的具有函数依赖保持性的分解。由于R的码是CE，则=ED,BCD,ACD,CE为一个3NF的既具有无损联接性又具有函数依赖保持性的分解。,21,例如3,关系模式R(C,S,Z),F=CSZ,ZC,则R属于3NF，可以分解为具有无损联接性的BCNF，而不可能分解成具有函数依赖保持性的BCNF。当分解为=SZ,CZ，则它为一个BCNF的具有无损联接性的分解。,22,例如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。,23,例如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，分解完成。,24,习题：求候选码，转换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。,25,习题：求候选码，转换成BCNF,5、设有关系模式R(学号,课程号,学分,成绩,奖学金)，其中F=课程号学分,成绩奖学金,(学号,课程号)成绩。6、设有关系模式R(学生,课程,教师)，其中F=教师课程,(学生,课程)教师。,26,习题答案,1、KEY=IS2、KEY=BD3、KEY=CE4、KEY=CE5、KEY=(学号,课程号)6、KEY=(学生,课程)；R1(学生,教师)，R2(教师,课程),27,例如,R(A,B,C),F=AB,CB。当1=AB,AC时，它具有无损联接性，但不具有依赖保持性。当2=AB,BC时，它具有依赖保持性，但不具有无损联接性。然而当3=AB,AC,BC时，它既具有依赖保持性，又具有无损联接性。,28,依赖保持,设关系模式R的一个分解为=R1,R2,.,Rk，F是R的依赖集。如果F等价于R1(F)R2(F).Rk(F)，则称分解具有依赖保持性。一个无损联接分解不一定具有依赖保持性；同样一个依赖保持分解不一定具有无损联接。,29,模式分解,若要求分解保持函数依赖，那么模式分解总可以达到3NF，但不一定能达到BCNF。若要求分解既保持函数依赖，又具有无损联接性，那么模式分解可以达到3NF，但不一定能达到BCNF。若要求分解既具有无损联接性，那么模式分解一定可以达到4NF。,30,求下列最高属于第几范式,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。,31,答案,1、Key=AB,R1NF2、Key=AB或E,R2NF3、Key=AB或AD,R3NF4、Key=A或B,RBCNF5、Key=C,R3NF6、Key=AD,R1NF7、Key=AD,R1NF,32,BCNF定义,若R1NF，若XY且YX时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。,33,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。,34,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。,35,习题,设R(A,B,C),r为R的一个值，r=ab1c1,ab2c2,ab1c2,ab2c1。问1.r满足条件AB吗？为什么？2.如果在r中任取一三个元组的子集，这些子集满足条件AB吗？为什么？1.r满足条件AB。2.不满足条件AB。,36,求关键字,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。,37,答案,1、CEP2、QSI，QSO，QSB，QDB，QDI，QDO3、XZ4、SI，SQ，SB，SO5、IS,38,四大定理,定理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的唯一候选关键字。,39,快速求解关键字,给定关系模式R(A1,A2,.,An)和函数依赖集F，可将其属性分为四类：1、仅仅出现在F的函数依赖左部的属性称L类；2、仅仅出现在F的函数依赖右部的属性称R类；3、在F的函数依赖左右均未出现的属性称N类；4、在F的函数依赖左右均出现的