数据库概论必考例题课后重点答案市公开课一等奖百校联赛获奖课件.pptx

3.用用SQL语句建立第二章习题语句建立第二章习题5中四个表：中四个表：供给商关系：S(SNO，SNAME，STATUS，CITY)零件关系：P(PNO，PNAME，COLOR，WEIGHT)工程项目关系：J(JNO,JNAME，CITY)供给情况关系：SPJ(SNO,PNO,JNO,QTY)1第1页 定义关系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）；）；2第2页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)；3第3页4.针对上题中建立四个表试用SQL语言完成第二章习题5中查询1)求供给工程J1零件供给商号码SNO;2)求供给工程J1零件P1供给商号码SNO;3)求供给工程J1零件为红色供给商号SNO;4)求没有使用天津供给商生产红色零件工程号JNO;5)求最少用了供给商S1所供给全部零件工程号JNO4第4页n1)求供给工程J1零件供给商号码SNO;nSELECTDISTINCTSNOFROMSPJWHEREJNO=J1;nSELECT子句后面DISTINCT表示要在结果中去掉重复供给商编号SNO。一个供给商可认为一个工程J1提供多种零件。n2)求供给工程J1零件P1供给商号码SNO;nSELECTSNOFROMSPJnWHEREJNO=J1ANDPNO=P1;n3)求供给工程J1零件为红色供给商号SNO;nSELECTDISTINCTSNOFROMSPJnWHEREJNO=J1ANDPNOINn(SELECTPNOnFROMPnWHERECOLOR=红）;5第5页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 JWHERE 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=红)6第6页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=红)7第7页5)求最少用了供给商求最少用了供给商S1所供给全部零件工程号所供给全部零件工程号JNOSELECT DISTINCT JNOFROM SPJ SPJ1WHERE 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)8第8页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);查询结果：J4SPJZ.SNO=S1SPJZ.SNO=S19第9页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)插入供给情况关系10第10页1)找出全部供给商姓名和所在城市找出全部供给商姓名和所在城市SELECTSNAME,CITYFROMS;2)找出全部零件名称、颜色、重量找出全部零件名称、颜色、重量SELECTPNAME,COLOR,WEIGHTFROMP;3)找出使用供给商找出使用供给商S1所供给零件工程号码所供给零件工程号码SELECTDISTINCTJNOFROMSPJWHERESNO=S1;11第11页4)找出工程项目找出工程项目J2使用各种零件名称及其数量使用各种零件名称及其数量SELECTPNAME,QTYFROMP,SPJWHEREP.PNO=SPJ.PNOANDSPJ.JNO=J2;12第12页5)找出上海厂商供给全部零件号码找出上海厂商供给全部零件号码SELECTDISTINCTPNOFROMS,SPJWHERES.SNO=SPJ.SNOANDS.CITY=上海;SELECTDISTINCTPNOFROMSPJWHERESNOIN(SELECTSNOFROMSWHERES.CITY=上海);6)找出使用上海产零件工程名称SELECTJNAMEFROMS,SPJ,JWHERES.SNO=SPJ.SNOANDJ.JNO=SPJ.JNOANDS.CITY=上海;13第13页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=天津;14第14页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)15第15页 11.请为三建工程项目建立一个供给情况视图SANJIAN_SPJ，包含供给商代码(SNO)、零件代码(PNO)、供给数量(QTY)。针对该视图完成以下查询：1)找出三建工程项目使用各种零件代码及其数量。2)找出供给商S1供给情况。16第16页创建视图：CREATEVIEWSANJIAN_SPJASSELECTSNO,PNO,QTYFROMSPJ,JWHERESPJ.JNO=J.JNOANDJ.JNAME=三建;1)找出三建工程项目使用各种零件代码及其数量。SELECTPNO,SUM(QTY)SELECTPNO,QTYFROMSANJIAN_SPJFROMSANJIAN_SPJ;GROUPBYPNO;2)找出供给商S1供给情况。SELECT*FROMSANJIAN_SPJWHERESNO=S117第17页数据库设计方法数据库设计方法1）基本设计法基本设计法分五步进行：分五步进行：a.创建用户视图创建用户视图b.汇总用户视图，得出全局数据视图，即概念模型。汇总用户视图，得出全局数据视图，即概念模型。c.修改概念模型。修改概念模型。d.转换并定义概念模型，转换成转换并定义概念模型，转换成DBMS数据模型。数据模型。e.设计优化物理模型，即存放策略。设计优化物理模型，即存放策略。18第18页比如1n关系模式关系模式R(C,T,H,R,S,G),F=CT,CSG,HTR,HRC,HSR,则则=CT,CHR,HRT,CSG,HSR为一个为一个3NF既含既含有没有损联接性又含有函数依赖保持性分解。有没有损联接性又含有函数依赖保持性分解。R码是码是HS。19第19页比如比如2关系模式关系模式R(A,B,C,D,E),F=AD,ED,DB,BCD,DCA,则则=ED,BCD,ACD为一为一个个3NF含有函数依赖保持性分解。含有函数依赖保持性分解。因为因为R码是码是CE，则，则=ED,BCD,ACD,CE为一为一个个3NF既含有没有损联接性又含有函数依赖保既含有没有损联接性又含有函数依赖保持性分解。持性分解。20第20页比如比如3关系模式关系模式R(C,S,Z),F=CSZ,ZC,则则R属于属于3NF，能够分解为含有没有损联接性，能够分解为含有没有损联接性BCNF，而不可能分解成含有函数依赖保持性而不可能分解成含有函数依赖保持性BCNF。当分解为当分解为=SZ,CZ，则它为一个，则它为一个BCNF含有含有没有损联接性分解。没有损联接性分解。21第21页比如比如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。22第22页比如比如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，分解完成。，分解完成。23第23页习题：求候选码，转换3NF，BCNFn1、设相关系模式、设相关系模式R(O,I,S,Q,B,D)，其中，其中F=SD,IB,ISQ,BO。n2、设相关系模式、设相关系模式R(A,B,C,D)，其中，其中 F=AC,CA,BAC,DAC,BDA。n3、设相关系模式、设相关系模式R(A,B,C,D,E)，其中，其中F=AD,ED,DB,BCD,DCA。n4、设相关系模式、设相关系模式R(A,B,C,D,E,F)，其中，其中F=AB,CF,EA,CED。24第24页习题：习题：求候选码，转换成求候选码，转换成BCNF5、设相关系模式、设相关系模式R(学号学号,课程号课程号,学分学分,成绩成绩,奖学金奖学金)，其中，其中F=课程号课程号学分学分,成绩成绩奖学奖学金金,(学号学号,课程号课程号)成绩成绩。6、设相关系模式、设相关系模式R(学生学生,课程课程,教师教师)，其中，其中F=教师教师课程课程,(学生学生,课程课程)教师教师。25第25页习题答案n1、KEY=ISn2、KEY=BDn3、KEY=CEn4、KEY=CEn5、KEY=(学号,课程号)n6、KEY=(学生,课程)；R1(学生,教师)，R2(教师,课程)26第26页比如nR(A,B,C),F=AB,CB。当。当1=AB,AC时，它含有没时，它含有没有损联接性，但不含有依赖保持性。当有损联接性，但不含有依赖保持性。当 2=AB,BC时，它含有依赖保持性，但不含有没有损联时，它含有依赖保持性，但不含有没有损联接性。接性。n然而当然而当3=AB,AC,BC时，它既含有依赖保持性，又含有时，它既含有依赖保持性，又含有没有损联接性。没有损联接性。27第27页依赖保持n设关系模式设关系模式R一个分解为一个分解为=R1,R2,.,Rk，F是是R依赖依赖集。假如集。假如F等价于等价于R1(F)R2(F).Rk(F)，则，则称分解称分解含有依赖保持性。含有依赖保持性。n一个无损联接分解不一定含有依赖保持性；一个无损联接分解不一定含有依赖保持性；n 一样一个依赖保持分解不一定含有没有损联接。一样一个依赖保持分解不一定含有没有损联接。28第28页模式分解n若要求分解保持函数依赖，那么模式分解总能够到达若要求分解保持函数依赖，那么模式分解总能够到达3NF，但不一定能到达，但不一定能到达BCNF。n若要求分解既保持函数依赖，又含有没有损联接性，那么若要求分解既保持函数依赖，又含有没有损联接性，那么模式分解能够到达模式分解能够到达3NF，但不一定能到达，但不一定能到达BCNF。n若要求分解既含有没有损联接性，那么模式分解一定能够若要求分解既含有没有损联接性，那么模式分解一定能够到达到达4NF。29第29页求以下最高属于第几范式求以下最高属于第几范式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。30第30页答案答案1、Key=AB,R1NF2、Key=AB或或E,R2NF3、Key=AB或或AD,R3NF4、Key=A或或B,RBCNF5、Key=C,R3NF6、Key=AD,R1NF7、Key=AD,R1NF31第31页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。32第32页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。33第33页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。34第34页习题习题设设R(A,B,C),r为为R一个值，一个值，r=ab1c1,ab2c2,ab1c2,ab2c1。问问1.r满足条件满足条件AB吗？为何？吗？为何？2.假如在假如在r中任取一三个元组子集，这些子中任取一三个元组子集，这些子集满足条件集满足条件AB吗？为何？吗？为何？1.r 满足条件满足条件AB。2.不满足条件不满足条件AB。35第35页求关键字n1.设设R(A,B,C,D,E,P),F=AD,ED,DB,BCD,CDA。n2.设设R(O,I,S,Q,B,D),F=SD,DS,IB,BI,BO,OB。n3.设设R(X,Y,Z,W),F=WY,YW,XWY,ZWY,XZW。n4.设设R(O,I,S,Q,B,D),F=SD,IB,BO,OQ,QI。n5.设设R(O,I,S,Q,B,D),F=IB,BO,IQ,SD。36第36页答案n1、CEPn2、QSI，QSO，QSB，QDB，QDI，QDOn3、XZn4、SI，SQ，SB，SOn5、IS37第37页四大定理n定理定理1：设：设K为为R中属性或属性组合，若中属性或属性组合，若K是是L或或N类，则类，则K必为必为R任一候选关键字组员。即是主属性。任一候选关键字组员。即是主属性。n定理定理2：设：设X为为R中属性或属性组合，若中属性或属性组合，若X是是R类，类，则则X不在任何候选关键字中。即是非主属性。不在任何候选关键字中。即是非主属性。n定理定理3：若：若K是是L类，且类，且K+包含包含R全部属性，则全部属性，则K必为必为R唯一候选关键字。唯一候选关键字。n定理定理4：若：若K是是L和和N类属性组合，且类属性组合，且K+包含包含R全部属性，全部属性，则则K必为必为R唯一候选关键字。唯一候选关键字。38第38页快速求解关键字n给定关系模式给定关系模式R(A1,A2,.,An)和函数依赖集和函数依赖集F，可将其属，可将其属性分为四类：性分为四类：n1、仅仅出现在、仅仅出现在F函数依赖左部属性称函数依赖左部属性称L类；类；n2、仅仅出现在、仅仅出现在F函数依赖右部属性称函数依赖右部属性称R类；类；n3、在、在F函数依赖左右均未出现属性称函数依赖左右均未出现属性称N类；类；n4、在、在F函数依赖左右均出现属性称函数依赖左右均出现属性称LR类。类。39第39页nStudent(Sno,Sname,Sex,Bdate,Height)nSC(Sno,Cno,Grade)nCourse(Cno,Lhour,Credit,Semester)n在在SC中中Sno不是码，但却是不是码，但却是Student码，所以码，所以Sno是是SC外码。外码。n在在SC中中Cno不是码，但却是不是码，但却是Course码，所以码，所以Cno是是SC外码。外码。n学生学生(学号学号,姓名姓名,性别性别,专业号专业号,年纪年纪)n专业专业(专业号专业号,专业名专业名)n在在学生学生表中表中专业号专业号不是码，但却是不是码，但却是专业专业表码，所以表码，所以专业号专业号是是学生学生表外码。表外码。n学生学生2(学号学号,姓名姓名,性别性别,专业号专业号,年纪年纪,班长学号班长学号)n在在学生学生2表中表中班长班长不是码，但引用了本关系表不是码，但引用了本关系表学号学号属性，所以属性，所以班长班长是是学生学生2表外码。表外码。40第40页练习题n求求F=AC,CA,BAC,DAC,BDA最小函数依赖最小函数依赖集。集。n求求F=AD,ED,DB,BCD,DCA最小函数依赖集。最小函数依赖集。n求求F=AB,BA,BC,AC,CA最小函数依赖集。最小函数依赖集。n设相关系模式设相关系模式R(O,I,S,Q,B,D)，其中其中F=SD,IB,ISQ,BO，试计算试计算S+,I+,B+,(IS)+,(SB)+,(IB)+,(ISB)+。41第41页举例nF=ABC,CA,BCD,ACDB,DEG,BEC,CGBD,CEAG，计算，计算(BD)+。n解：解：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 42第42页举例举例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+。若。若YX+，则可去除，则可去除XY，不然不能去，不然不能去除。可得：除。可得：F=ABC,CA,BCD,CDB,DE,DG,BEC,CGD,CEG 43第43页