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

13.用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））；3CREATETABLEP(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))；44.针对上题中建立的四个表试用SQL语言完成第二章习题5中的查询1)求供应工程J1零件的供应商号码SNO;2)求供应工程J1零件P1的供应商号码SNO;3)求供应工程J1零件为红色的供应商号SNO;4)求没有使用天津供应商生产的红色零件的工程号JNO;5)求至少用了供应商S1所供应的全部零件的工程号JNO51)求供应工程J1零件的供应商号码SNO;SELECTDISTINCTSNOFROMSPJWHEREJNO=‘J1’;SELECT子句后面的DISTINCT表示要在结果中去掉重复的供应商编号SNO。一个供应商可以为一个工程J1提供多种零件。2)求供应工程J1零件P1的供应商号码SNO;SELECTSNOFROMSPJWHEREJNO=‘J1’ANDPNO=‘P1’;3)求供应工程J1零件为红色的供应商号SNO;SELECTDISTINCTSNOFROMSPJWHEREJNO=‘J1’ANDPNOIN(SELECTPNOFROMPWHERECOLOR=‘红’）;64)求没有使用天津供应商生产的红色零件的工程号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='红')74)求没有使用天津供应商生产的红色零件的工程号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='红'))85)求至少用了供应商S1所供应的全部零件的工程号JNOSELECTDISTINCTJNOFROMSPJSPJ1WHERENOTEXISTS(SELECT*FROMSPJSPJ2WHERESNO=‘S1’ANDNOTEXISTSPNO=ALL(SELECT*FROMSPJSPJ3WHEREPNO=SPJ2.PNOANDJNO=SPJ1.JNO))95)求至少用了供应商S1所供应的全部零件的工程号JNO第一种理解：SELECTDISTINCTJNOFROMSPJSPJXWHERENOTEXISTS(SELECT*FROMSPJSPJYWHERESPJY.SNO='S1'ANDNOTEXISTS(SELECT*FROMSPJSPJZWHERESPJZ.JNO=SPJX.JNOANDSPJZ.PNO=SPJY.PNOANDSPJZ.SNO=SPJY.SNO));查询结果：{}第二种理解：SELECTDISTINCTJNOFROMSPJSPJXWHERENOTEXISTS(SELECT*FROMSPJSPJYWHERESPJY.SNO='S1'ANDNOTEXISTS(SELECT*FROMSPJSPJZWHERESPJZ.JNO=SPJX.JNOANDSPJZ.PNO=SPJY.PNO));查询结果：{J4}SPJZ.SNO=‘S1’105.针对习题3中的四个表试用SQL语言完成以下各项操作1)找出所有供应商的姓名和所在城市2)找出所有零件的名称、颜色、重量3)找出使用供应商S1所供应零件的工程号码4)找出工程项目J2使用的各种零件的名称及其数量5)找出上海厂商供应的所有零件号码6)找出使用上海产的零件的工程名称7)找出没有使用天津产的零件的工程号码8)把全部红色零件的颜色改成蓝色9)有S5供给J4的零件P6改为由S3供应，请作必要的修改10)从供应商关系中删除S2的记录，并从供应情况关系中删除相应的记录11)请将(S2,J6,P4,200)插入供应情况关系111)找出所有供应商的姓名和所在城市SELECTSNAME,CITYFROMS;2)找出所有零件的名称、颜色、重量SELECTPNAME,COLOR,WEIGHTFROMP;3)找出使用供应商S1所供应零件的工程号码SELECTDISTINCTJNOFROMSPJWHERESNO=‘S1’;124)找出工程项目J2使用的各种零件的名称及其数量SELECTPNAME,QTYFROMP,SPJWHEREP.PNO=SPJ.PNOANDSPJ.JNO=‘J2’;135)找出上海厂商供应的所有零件号码SELECTDISTINCTPNOFROMS,SPJWHERES.SNO=SPJ.SNOANDS.CITY=‘上海’;SELECTDISTINCTPNOFROMSPJWHERESNOIN(SELECTSNOFROMSWHERES.CITY=‘上海’);6)找出使用上海产的零件的工程名称SELECTJNAMEFROMS,SPJ,JWHERES.SNO=SPJ.SNOANDJ.JNO=SPJ.JNOANDS.CITY=‘上海’;147)找出没有使用天津产的零件的工程号码SELECTJNOFROMJWHEREJNONOTIN(SELECTJNOFROMSPJ,SWHERES.SNO=SPJ.SNOANDS.CITY=‘天津’);SELECTJNOFROMJWHERENOTEXISTS(SELECT*FROMSPJWHEREJNO=J.JNOANDSNOIN(SELECTSNOFROMSWHERES.CITY=‘天津’));SELECTJNOFROMJWHERENOTEXISTS(SELECTSPJ.*,S.*FROMSPJ,SWHEREJNO=J.JNOANDSNO=S.SNOANDS.CITY=‘天津’;158)把全部红色零件的颜色改成蓝色UPDATEPSETCOLOR=‘蓝’WHERECOLOR=‘红';9)由S5供给J4的零件P6改为由S3供应，请作必要的修改UPDATESPJSETSNO=‘S3’WHERESNO=‘S5’ANDJNO=‘J4’ANDPNO=‘P6’10)从供应商关系中删除S2的记录，并从供应情况关系中删除相应的记录DELETEFROMSWHERESNO=‘S2’;DELETEFROMSPJWHERESNO=‘S2’11)请将(S2,J6,P4,200)插入供应情况关系INSERTINTOSPJVALUES(‘S2’,’P4’,’J6’,200)常见错误：INSERTINTOSPJVALUES(‘S2’,’J6’,’P4’,200)1611.请为三建工程项目建立一个供应情况的视图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={C→T,CS→G,HT→R,HR→C,HS→R},则ρ={CT,CHR,HRT,CSG,HSR}为一个3NF的既具有无损联接性又具有函数依赖保持性的分解。R的码是HS。20例如2关系模式R(A,B,C,D,E),F={A→D,E→D,D→B,BC→D,DC→A},则ρ={ED,BCD,ACD}为一个3NF的具有函数依赖保持性的分解。由于R的码是CE，则ρ={ED,BCD,ACD,CE}为一个3NF的既具有无损联接性又具有函数依赖保持性的分解。21例如3关系模式R(C,S,Z),F={CS→Z,Z→C},则R属于3NF，可以分解为具有无损联接性的BCNF，而不可能分解成具有函数依赖保持性的BCNF。当分解为ρ={SZ,CZ}，则它为一个BCNF的具有无损联接性的分解。22例如4关系模式R(T,Q,P,C,S,Z),F={T→Q,T→P,T→C,T→S,PCS→Z,Z→P,Z→C},试分解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={SC→G,C→T,T→D},试分解成BCNF。从题目可知码是SC。首先从关系R中分出TD，即R1(S,C,G,T),R2(T,D)。再从R1中分出CT，即R3(C,T),R4(S,C