资源描述
单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,单击此处编辑母版标题样式,*,3.用SQL语句建立第二章习题5中的四个表：,供应商关系：S(SNO，SNAME，STATUS，CITY),零件关系：P(PNO，PNAME，COLOR，WEIGHT),工程项目关系：J(JNO,JNAME，CITY),供应情况关系：SPJ(SNO,PNO,JNO,QTY),1,定义的关系,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,）；,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,4.针对上题中建立的四个表试用SQL语言完成第二章习题5中的查询,1)求供应工程J1零件的供应商号码SNO;,2)求供应工程J1零件P1的供应商号码SNO;,3)求供应工程J1零件为红色的供应商号SNO;,4)求没有使用天津供应商生产的红色零件的工程号JNO;,5)求至少用了供应商S1所供应的全部零件的工程号JNO,4,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=,红,）,;,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 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=红),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,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),8,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,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,1)找出所有供应商的姓名和所在城市,SELECT SNAME,CITY,FROM S;,2)找出所有零件的名称、颜色、重量,SELECT PNAME,COLOR,WEIGHT,FROM P;,3)找出使用供应商S1所供应零件的工程号码,SELECT DISTINCT JNO,FROM SPJ,WHERE SNO=S1;,11,4)找出工程项目J2使用的各种零件的名称及其数量,SELECT PNAME,QTY,FROM P,SPJ,WHERE P.PNO=SPJ.PNO AND SPJ.JNO=J2;,12,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=上海;,13,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=天津;,14,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),15,11.请为三建工程项目建立一个供应情况的视图SANJIAN_SPJ，包括供应商代码(SNO)、零件代码(PNO)、供应数量(QTY)。针对该视图完成下列查询：,1)找出三建工程项目使用的各种零件代码及其数量。,2)找出供应商S1的供应情况。,16,创建视图：,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,17,数据库设计方法,1,）,基本设计法,分五步进行：,a.,创建用户视图,b.,汇总用户视图，得出全局数据视图，即概念模型。,c.,修改概念模型。,d.,转换并定义概念模型，转换成,DBMS,的数据模型。,e.,设计优化物理模型，即存储策略。,18,例如1,关系模式R(C,T,H,R,S,G),F=CT,CSG,HTR,HRC,HSR,则=CT,CHR,HRT,CSG,HSR为一个3NF的既具有无损联接性又具有函数依赖保持性的分解。R的码是HS。,19,例如2,关系模式,R(A,B,C,D,E),F=AD,ED,DB,BCD,DCA,则,=ED,BCD,ACD,为一个,3NF,的具有函数依赖保持性的分解。,由于,R,的码是,CE,，则,=ED,BCD,
展开阅读全文