1、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中允许属性值为空值，当规定某一属性值不能为空值时，就要在定义该属性时写上保留字“NOT
2、 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）；）；3CREATE TABLE P(PNO CHAR（4）NOT NULL，PNAME CHAR(20)NOT NULL，COLOR CHAR(8)，WEIGHT SMALLINT，PRIMARY KEY(PNO)；CREATE TABLE J(J
3、NO 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 4.针对上题中建立的四个表试用S
4、QL语言完成第二章习题5中的查询 1)求供应工程J1零件的供应商号码SNO;2)求供应工程J1零件P1的供应商号码SNO;3)求供应工程J1零件为红色的供应商号SNO;4)求没有使用天津供应商生产的红色零件的工程号JNO;5)求至少用了供应商S1所供应的全部零件的工程号JNO51)求供应工程求供应工程J1零件的供应商号码零件的供应商号码SNO;SELECT DISTINCT SNO FROM SPJ WHERE JNO=SELECT DISTINCT SNO FROM SPJ WHERE JNO=J1J1;SELECT SELECT子句后面的子句后面的DISTINCTDISTINCT表示要在结
5、果中去掉重复的供应商编号表示要在结果中去掉重复的供应商编号SNOSNO。一个供应商可以为一个工程一个供应商可以为一个工程J1J1提供多种零件。提供多种零件。2)求供应工程求供应工程J1零件零件P1的供应商号码的供应商号码SNO;SELECT SNO FROM SPJ SELECT SNO FROM SPJ WHERE JNO=WHERE JNO=J1J1 AND PNO=AND PNO=P1P1;3)求供应工程求供应工程J1零件为红色的供应商号零件为红色的供应商号SNO;SELECT DISTINCT SNO FROM SPJ SELECT DISTINCT SNO FROM SPJ WHER
6、E JNO=WHERE JNO=J1J1 AND PNO IN AND PNO IN (SELECT PNO (SELECT PNO FROM P FROM P WHERE COLOR=WHERE COLOR=红红）;6 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.
7、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=红)74)求没有使用天津供应商生产的红色零件的工程号求没有使用天津供应商生产的红色零件的工程号JNO;SELECT JNO FROM J WHERE JNO NOT
8、 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=红)85)求至少用了供应商求至少用了供应商S1所供应的全部零件的工程
9、号所供应的全部零件的工程号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)95)求至少用了供应商求至少用了供应商S1所供应的全部零件的工程号所供应的全部零件的工程号JNO 第一种理解：SELECT DISTINCT JNO FROM SPJ SPJX WHERE NOT EXISTS (SELECT*FROM
10、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
11、SPJZ.PNO=SPJY.PNO);查询结果：J4SPJZ.SNO=S1SPJZ.SNO=S1105.针对习题针对习题3中的四个表试用中的四个表试用SQL语言完成以下各项操作语言完成以下各项操作1)找出所有供应商的姓名和所在城市2)找出所有零件的名称、颜色、重量3)找出使用供应商S1所供应零件的工程号码4)找出工程项目J2使用的各种零件的名称及其数量5)找出上海厂商供应的所有零件号码6)找出使用上海产的零件的工程名称7)找出没有使用天津产的零件的工程号码8)把全部红色零件的颜色改成蓝色9)有S5供给J4的零件P6改为由S3供应，请作必要的修改10)从供应商关系中删除S2的记录，并从供应情况关
12、系中删除相应的记录11)请将(S2,J6,P4,200)插入供应情况关系111)找出所有供应商的姓名和所在城市找出所有供应商的姓名和所在城市 SELECT SNAME,CITY FROM S;2)找出所有零件的名称、颜色、重量找出所有零件的名称、颜色、重量 SELECT PNAME,COLOR,WEIGHT FROM P;3)找出使用供应商找出使用供应商S1所供应零件的工程号码所供应零件的工程号码 SELECT DISTINCT JNO FROM SPJ WHERE SNO=S1;12 4)找出工程项目找出工程项目J2使用的各种零件的名称及其数量使用的各种零件的名称及其数量 SELECT PN
13、AME,QTY FROM P,SPJ WHERE P.PNO=SPJ.PNO AND SPJ.JNO=J2;135)找出上海厂商供应的所有零件号码找出上海厂商供应的所有零件号码 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
14、J.JNO=SPJ.JNO AND S.CITY=上海;147)找出没有使用天津产的零件的工程号码找出没有使用天津产的零件的工程号码SELECT JNOFROM JWHERE JNO NOT IN (SELECT JNO FROM SPJ,S WHERE S.SNO=SPJ.SNO AND S.CITY=天津);SELECT JNOFROM JWHERE NOT EXISTS (SELECT*FROM SPJ WHERE JNO=J.JNO AND SNO IN (SELECT SNO FROM S WHERE S.CITY=天津);SELECT JNOFROM JWHERE NOT EXIS
15、TS (SELECT SPJ.*,S.*FROM SPJ,S WHERE JNO=J.JNO AND SNO=S.SNO AND S.CITY=天津;158)把全部红色零件的颜色改成蓝色把全部红色零件的颜色改成蓝色 UPDATE PSET COLOR=蓝 WHERE COLOR=红;9)由由S5供给供给J4的零件的零件P6改为由改为由S3供应，请作必要的修改供应，请作必要的修改 UPDATE SPJ SET SNO=S3 WHERE SNO=S5 AND JNO=J4 AND PNO=P610)从供应商关系中删除从供应商关系中删除S2的记录，并从供应情况关系中删除的记录，并从供应情况关系中删除
16、相应的记录相应的记录 DELETE FROM S WHERE SNO=S2;DELETE FROM SPJ WHERE SNO=S211)请将请将(S2,J6,P4,200)插入供应情况关系插入供应情况关系 INSERT INTO SPJ VALUES(S2,P4,J6,200)常见错误：INSERT INTO SPJ VALUES(S2,J6,P4,200)16 11.请为三建工程项目建立一个供应情况的视图SANJIAN_SPJ，包括供应商代码(SNO)、零件代码(PNO)、供应数量(QTY)。针对该视图完成下列查询：1)找出三建工程项目使用的各种零件代码及其数量。2)找出供应商S1的供应情
17、况。17创建视图：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=S118数据库设计方法数据库设计方法 1）基本设计法基本设计法 分五步进行：分五步进行：a
18、.创建用户视图创建用户视图 b.汇总用户视图，得出全局数据视图，即概念模型。汇总用户视图，得出全局数据视图，即概念模型。c.修改概念模型。修改概念模型。d.转换并定义概念模型，转换成转换并定义概念模型，转换成DBMS的数据模型。的数据模型。e.设计优化物理模型，即存储策略。设计优化物理模型，即存储策略。19例如1n 关系模式关系模式R(C,T,H,R,S,G),F=CT,CSG,HTR,HRC,HSR,则则=CT,CHR,HRT,CSG,HSR为一个为一个3NF的既的既具有无损联接性又具有函数依赖保持性的分解。具有无损联接性又具有函数依赖保持性的分解。R的码是的码是HS。20例如例如2关系模式
19、关系模式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,C
20、Z，则它为一个，则它为一个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；再继续分解成
21、；再继续分解成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，BCNFn 1、设有关系模式、设有关系模式R(O,I,S,Q,B,D)，其中，其中F=SD,IB,ISQ,BO。n 2、设有关系模式、设有关系模式R(A
22、,B,C,D)，其中，其中 F=AC,CA,BAC,DAC,BDA。n 3、设有关系模式、设有关系模式R(A,B,C,D,E)，其中，其中F=AD,ED,DB,BCD,DCA。n 4、设有关系模式、设有关系模式R(A,B,C,D,E,F)，其中，其中F=AB,CF,EA,CED。25习题：习题：求候选码，转换成求候选码，转换成BCNF5、设有关系模式、设有关系模式R(学号学号,课程号课程号,学分学分,成绩成绩,奖学金奖学金)，其中，其中F=课程号课程号学分学分,成绩成绩奖学奖学金金,(学号学号,课程号课程号)成绩成绩。6、设有关系模式、设有关系模式R(学生学生,课程课程,教师教师)，其中，其中
23、 F=教师教师课程课程,(学生学生,课程课程)教师教师。26习题答案n 1、KEY=ISn 2、KEY=BDn 3、KEY=CEn 4、KEY=CEn 5、KEY=(学号,课程号)n 6、KEY=(学生,课程)；R1(学生,教师)，R2(教师,课程)27例如n R(A,B,C),F=AB,CB。当。当1=AB,AC时，它具有无时，它具有无损联接性，但不具有依赖保持性。当损联接性，但不具有依赖保持性。当 2=AB,BC时，它具有依赖保持性，但不具有无损联接时，它具有依赖保持性，但不具有无损联接性。性。n 然而当然而当3=AB,AC,BC时，它既具有依赖保持性，又具时，它既具有依赖保持性，又具有无
24、损联接性。有无损联接性。28依赖保持n 设关系模式设关系模式R的一个分解为的一个分解为=R1,R2,.,Rk，F是是R的依的依赖集。如果赖集。如果F等价于等价于R1(F)R2(F).Rk(F)，则，则称分解称分解具有依赖保持性。具有依赖保持性。n 一个无损联接分解不一定具有依赖保持性；一个无损联接分解不一定具有依赖保持性；n 同样一个依赖保持分解不一定具有无损联接。同样一个依赖保持分解不一定具有无损联接。29模式分解n 若要求分解保持函数依赖，那么模式分解总可以达到若要求分解保持函数依赖，那么模式分解总可以达到3NF，但不一定能达到但不一定能达到BCNF。n 若要求分解既保持函数依赖，又具有无
25、损联接性，那么模若要求分解既保持函数依赖，又具有无损联接性，那么模式分解可以达到式分解可以达到3NF，但不一定能达到，但不一定能达到BCNF。n 若要求分解既具有无损联接性，那么模式分解一定可以达若要求分解既具有无损联接性，那么模式分解一定可以达到到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=
26、AC,DB。7.设设R(A,B,C,D),F=AC,CDB。31答案答案1、Key=AB,R1NF2、Key=AB或或E,R2NF3、Key=AB或或AD,R3NF 4、Key=A或或B,RBCNF 5、Key=C,R3NF6、Key=AD,R1NF7、Key=AD,R1NF 32BCNF定义定义若若R1NF，若，若XY且且Y X时时X必含有码。必含有码。例如：由于例如：由于(SNO,CNO)G，满足，满足BCNF的定的定义，所以义，所以SC属于属于BCNF。当当S-L分解成分解成SD(SNO,SDEPT)和和DL(SDEPT,SLOC)后的情形如下。后的情形如下。对于对于SD的函数依赖的函数
27、依赖SNOSDEPT，所以它的，所以它的码是码是SNO，所以，所以SD属于属于BCNF。对于对于DL的函数依赖的函数依赖 SDEPTSLOC，所以它，所以它的码是的码是SDEPT，所以，所以DL属于属于BCNF。333NF定义定义若若R1NF，且每一个非主属性既不部分函数，且每一个非主属性既不部分函数依赖于码也不传递函数依赖于码。依赖于码也不传递函数依赖于码。例如：当把例如：当把S-L-C分解成分解成SC(SNO,CNO,G)和和 S-L(SNO,SDEPT,SLOC)后。后。由于由于(SNO,CNO)G，满足，满足3NF的定义，所以的定义，所以SC属于属于3NF。而而S-L中候选码是中候选码
28、是SNO，但，但 SDEPTSLOC;SNOSDEPT，即，即非主属性非主属性SLOC传递依赖于传递依赖于码，所以码，所以S-L不属于不属于3NF。342NF定义定义若若R1NF，且每一个非主属性完全函数依赖，且每一个非主属性完全函数依赖于码。于码。例如：例如：S-L-C(SNO,SDEPT,SLOC,CNO,G)，这，这里里SNO表示学号，表示学号，SDEPT表示系名，表示系名，SLOC表表示楼号，示楼号，CNO表示课程号，表示课程号，G表示成绩。表示成绩。函数依赖有函数依赖有:(SNO,CNO)G;SDEPTSLOC;SNOSDEPT。所以候选码是所以候选码是(SNO,CNO)。而。而非主
29、属性非主属性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求关键字n 1.设设R(A,B,C,D,E,P),F=AD,ED,DB,BCD,CDA。n 2.设设R
30、(O,I,S,Q,B,D),F=SD,DS,IB,BI,BO,OB。n 3.设设R(X,Y,Z,W),F=WY,YW,XWY,ZWY,XZW。n 4.设设R(O,I,S,Q,B,D),F=SD,IB,BO,OQ,QI。n 5.设设R(O,I,S,Q,B,D),F=IB,BO,IQ,SD。37答案n 1、CEPn 2、QSI，QSO，QSB，QDB，QDI，QDOn 3、XZn 4、SI，SQ，SB，SOn 5、IS38四大定理n 定理定理1：设：设K为为R中的属性或属性组合，若中的属性或属性组合，若K是是L或或N类，则类，则K必为必为R的任一候选关键字成员。即是主属的任一候选关键字成员。即是主
31、属性。性。n 定理定理2：设：设X为为R中的属性或属性组合，若中的属性或属性组合，若X是是R类，则类，则X不在任何候选关键字中。即是非主属性。不在任何候选关键字中。即是非主属性。n 定理定理3：若：若K是是L类，且类，且K+包含包含R的全部属性，则的全部属性，则K必为必为R的唯一候选关键字。的唯一候选关键字。n 定理定理4：若：若K是是L和和N类属性组合，且类属性组合，且K+包含包含R的全部属的全部属性，则性，则K必为必为R的唯一候选关键字。的唯一候选关键字。39快速求解关键字n 给定关系模式给定关系模式R(A1,A2,.,An)和函数依赖集和函数依赖集F，可将其属性，可将其属性分为四类：分为
32、四类：n 1、仅仅出现在、仅仅出现在F的函数依赖左部的属性称的函数依赖左部的属性称L类；类；n 2、仅仅出现在、仅仅出现在F的函数依赖右部的属性称的函数依赖右部的属性称R类；类；n 3、在、在F的函数依赖左右均未出现的属性称的函数依赖左右均未出现的属性称N类；类；n 4、在、在F的函数依赖左右均出现的属性称的函数依赖左右均出现的属性称LR类。类。40nStudent(Sno,Sname,Sex,Bdate,Height)nSC(Sno,Cno,Grade)nCourse(Cno,Lhour,Credit,Semester)n在在SC中中Sno不是码，但却是不是码，但却是Student的码，所以
33、的码，所以Sno是是SC的外码。的外码。n在在SC中中Cno不是码，但却是不是码，但却是Course的码，所以的码，所以Cno是是SC的外码。的外码。n学生学生(学号学号,姓名姓名,性别性别,专业号专业号,年龄年龄)n专业专业(专业号专业号,专业名专业名)n在在学生学生表中表中专业号专业号不是码，但却是不是码，但却是专业专业表的码，所以表的码，所以专业号专业号是是学生学生表的外码。表的外码。n学生学生2(学号学号,姓名姓名,性别性别,专业号专业号,年龄年龄,班长学号班长学号)n在在学生学生2表中表中班长班长不是码，但引用了本关系表不是码，但引用了本关系表学号学号属性，所以属性，所以班长班长是是
34、学生学生2表的外码。表的外码。41练习题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)+。42举例n F=ABC,CA,BCD,ACDB,DEG,BEC,CGBD,CEAG，计算，计算(BD)+。n 解：解：X(0)=BD X(1)=X
35、(0)EG=BDEG DEG X(2)=X(1)C=BCDEG BEC X(3)=X(2)A=ABCDEG CA 由于由于X(3)=U 所以不需要继续计算所以不需要继续计算因此，因此，(BD)+=ABCDEG 43举例举例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
