1、【习题12】 (P38) 学校中有若干系，每个系有若干班级和教研室，每个教研室有若干教员，其中有的教授和副教授每人各带若干研究生；每个班有若干学生，每个学生选修若干课程，每门课可由若干学生选修。请用 E 一 R 图画出此学校的概念模型。,P74习题5设有一个SPJ数据库，包括S，P，J，SPJ四个关系模式： 1）求供应工程J1零件的供应商号码SNO： Sno (Sno=J1 (SPJ) 2）求供应工程J1零件P1的供应商号码SNO： Sno(Sno=J1Pno=P1( SPJ) 3）求供应工程J1零件为红色的供应商号码SNO： Sno(Pno=P1 （COLOR=红 （P） SPJ）) 4）求
2、没有使用天津供应商生产的红色零件的工程号JNO： Jno(SPJ) -JNO（city=天津Color=红 （S SPJ P） 5）求至少用了供应商S1所供应的全部零件的工程号JNO： Jno，Pno(SPJ) Pno（Sno=S1 （SPJ）,除（Division）,给定关系R (X，Y) 和S (Y，Z)，其中X，Y，Z为属性组。 R中的Y与S中的Y可以有不同的属性名，但必须出自相同的域集。 R与S的除运算得到一个新的关系P(X)， P是R中满足下列条件的元组在 X 属性列上的投影： 元组在X上分量值x的象集Yx包含S在Y上投影的集合，记作：RS = tr X | tr RY (S) Yx
3、 Yx：x在R中的象集，x = trX,除(续),例6设关系R、S分别为下图的(a)和(b)，RS的结果为图(c),分析,在关系R中，A可以取四个值a1，a2，a3，a4a1的象集为 (b1，c2)，(b2，c3)，(b2，c1)a2的象集为 (b3，c7)，(b2，c3)a3的象集为 (b4，c6)a4的象集为 (b6，c6) S在(B，C)上的投影为(b1，c2)，(b2，c1)，(b2，c3) 只有a1的象集包含了S在(B，C)属性组上的投影所以 RS =a1,综合举例,以学生-课程数据库为例 (P56) 例7 查询至少选修1号课程和3号课程的学生号码 首先建立一个临时关系K： 然后求：
4、Sno,Cno(SC)K,综合举例(续),例 7续 Sno,Cno(SC)95001象集1，2，395002象集2，3K=1，3于是：Sno,Cno(SC)K=95001,P127习题3 用 sQL 语句建立第二章习题 5 (P74)中的 4 个表。 答： 对于 S 表： S ( SNO , SNAME , STATUS , CITY ) ; 建 S 表： CREATE TABLE S ( Sno C(2) UNIQUE，Sname C(6) ，Status C(2)，City C(4);对于 P 表： P ( PNO , PNAME , COLOR , WEIGHT ); 建 P 表 ： C
5、REATE TABLE P(Pno C(2) UNIQUE，Pname C(6)，COLOR C(2)， WEIGHT INT);对于 J 表： J ( JNO , JNAME , CITY） ; 建 J 表： CREATE TABLE J(Jno C(2) UNlQUE，JNAME C(8)， CITY C(4);对于 SPJ 表： SPJ ( sNo , PNo , JNo , QTY） ; 建 SPJ 表：SPJ(SNO,PNO,JNO,QTY) CREATE TABLE SPJ(Sno C(2)，Pno C(2)，JNO C(2)， QTY INT);,【P127】4.针对上题中建立的
6、 4 个表试用 SQL 语言完成第二章习题 5 中的查询。 ( l ）求供应工程 Jl 零件的供应商号码 SNO ;SELECT DISTINCT SNO FROM SPJ WHERE JNO=J1;( 2 ）求供应工程 Jl 零件 Pl 的供应商号码 SNO ; SELECT DISTINCT SNO FROM SPJ WHERE JNO=J1 AND PNO=P1; ( 3 ）求供应工程 Jl 零件为红色的供应商号码 SNO ; SELECT SNO FROM SPJ,P WHERE JNO=J1 AND SPJ.PNO=P.PNO AND COLOR=红;,( 4 ) 求没有使用天津供应
7、商生产的红色零件的工程号 JNO ; SELECT DISTINCT JNO FROM SPJ WHERE JNO NOT IN (SELECT JNO FROM SPJ,P,S WHERE S.CITY=天津 AND COLOR=红 AND S.SNO=SPJ.SNO AND P.PNO=SPJ.PNO);,( 5) 求至少用了供应商 S1 所供应的全部零件的工程号 JNO ;,p: 供应商S1供应了零件y q:工程x选用了零件y 找这样的x，使得 对于所有的零件y, p-q都成立等价转换为：不存在零件y，供应商S1供应了y，而工程x没有选用ySELECT DISTINCT JNO FROM
8、 SPJ SPJX WHERE NOT EXISTS(SELECT *FROM SPJ SPJYWHERE SPJY.SNO=S1 ANDNOT EXISTS(SELECT *FROM SPJ SPJZWHERE SPJZ.SNO = SPJX.SNO ANDSPJZ.PNO = SPJY.PNO);,5. (1)找出所有供应商的姓名和所在城市。SELECT SNAME,CITY FROM S; (2)找出所有零件的名称、颜色、重量。SELECT PNAME,COLOR,WEIGHT FROM P; (3)找出使用供应商S1所供应零件的工程号码。SELECT DIST JNO FROM SPJ
9、 WHERE SNO=S1; (4)找出工程项目J2使用的各种零件的名称及其数量。 SELECT PNAME,QTY FROM SPJ,P WHERE P.PNO=SPJ.PNO AND SPJ.JNO=J2;,(5)找出上海厂商供应的所有零件号码。 SELECT PNO FROM SPJ,S WHERE S.SNO=SPJ.SNO AND CITY=上海 ; (6)找出使用上海产的零件的工程名称。 SELECT JNAME FROM SPJ,S,J WHERE S.SNO=SPJ.SNO AND S.CITY=上海 AND J.JNO=SPJ.JNO ;,(7)找出没有使用天津产的零件的工程
10、号码。SELECT DISTINCT JNO FROM SPJ WHERE JNO NOT IN (SELECT DISTINCT JNO FROM SPJ,S WHERE S.SNO=SPJ.SNO AND S.CITY=天津) ;,(8)把全部红色零件的颜色改成蓝色。UPDATE P SET COLOR=蓝 WHERE COLOR=红;(9)由S5供给J4的零件P6改为由S3供应。UPDATE SPJ SET SNO=S3 WHERE SNO=S5 AND JNO=J4 AND PNO=P6;,(10)从供应商关系中删除供应商号是S2的记录，并从供应情况关系中删除相应的记录。A、 DELE
11、TE FROM SPJ WHERE SNO=S2; B、 DELETE FROM S WHERE SNO=S2;(11)请将(S2，J6，P4，200)插入供应情况关系。INSERT INTO SPJ VALUES（S2，J6，P4，200）;,习题解答,请用SQL的GRANT 和REVOKE语句(加上视图机制)完成以下授权定义或存取控制功能: ( d ) 用户刘星对职工表有SELECT 权力，对工资字段具有更新权力:GRANT SELECT,UPDATE(工资) ON 职工TO 刘星;,( e ) 用户张新具有修改这两个表的结构的权力。GRANT ALTER TABLE ON 职工,部门TO
12、 张新;,( f ) 用户周平具有对两个表所有权力（读，插，改，删数据），并具有给其他用户授权的权力GRANT ALL PRIVILIGES ON 职工,部门TO 周平WITH GRANT OPTION;,( g ) 用户杨兰具有从每个部门职工中SELECT 最高工资、最低工资、平均工资的权力，他不能查看每个人的工资。CREATE VIEW 部门工资 ASSELECT 部门.名称,MAX(工资),MIN(工资),AVG(工资)FROM 职工,部门WHERE 职工.部门号=部门.部门号GROUP BY 职工.部门号GRANT SELECT ON 部门工资TO 杨兰;,假设有下面两个关系模式：职工
13、（职工号，姓名，年龄，职务，工资，部门号），其中职工号为主码；部门（部门号，名称，经理名，电话），其中部门号为主码。用 SQL 语言定义这两个关系模式，要求在模式中完成以下完整性约束条件的定义：定义每个模式的主码；定义参照完整性；定义职工年龄不得超过 60 岁。 CREATE TABLE DEPT(Deptno NUMBER(2),Deptname VARCHAR(10),Manager VARCHAR(10),PhoneNumber Char(12)CONSTRAINT PK_SC PRIMARY KEY(Deptno);,CREATE TABLE EMP(Empno NUMBER(4),Ename VARCHAR(10),Age NUMBER(2),CONSTRAINT C1 CHECK ( Aage=60),Job VARCHAR(9),Sal NUMBER(7,2),Deptno NUMBER(2),CONSTRAINT FK_DEPTNOFOREIGN KEY(Deptno)REFFERENCES DEPT(Deptno);,
