资源描述
第一章 第二章 5 设有一个 SPJ 数据库，包括 S 、 P 、 J 、 SPJ 四个关系模式： S ( SNO , SNAME , STATUS , CITY ) ; P ( PNO , PNAME , COLOR , WEIGHT ) ; J ( JNO , JNAME , CITY ) ; SPJ ( SNO , PNO , JNO , QTY）; 供应商表 S 由供应商代码（ SNO ） 、供应商姓名（ SNAME ） 、供应商状态 ( STATUS ） 、供应商所在城市（ CITY ）组成； 零件表 P 由零件代码（ PNO ） 、零件名（ PNAME ） 、颜色（ COLOR ） 、重量 ( WEIGHT ）组成； 工程项目表 J 由工程项目代码（ JNO ） 、工程项目名（ JNAME ） 、工程项目所在城 市（ CITY ）组成； 供应情况表 SPJ 由供应商代码 （ SNO ） 、 零件代码 （ PNO ） 、 工程项目代码 ( JNO ） 、 供应数量（ QTY ）组成，表示某供应商供应某种零件给某工程项目的数量为 QTY 。今有 若干数据如下： SNO SNAME STATUS CITY S1 S2 S3 S4 S5 精 益 盛 锡 东方红 丰泰盛 为 民 20 10 30 20 50 天津 北京 北京 天津 上海 PNO PNAME COLOR WEIGHT Pl P2 P3 P4 P5 P6 螺 母 螺 栓 螺丝刀 螺丝刀 凸 轮 齿 轮 红 绿 蓝 红 蓝 红 12 17 14 14 40 30 JNO JNAME CITY J1 J2 J3 J4 J5 J6 J7 三 建 一 汽 弹簧厂 造船厂 机车厂 无线电厂 半导体厂 北京 长春 天津 天津 唐山 常州 南京 SNO PNO JNO QTY S1 S1 S1 S1 S2 S2 S2 S2 S2 S2 S3 S3 S4 S4 S4 S5 S5 S5 S5 P1 P1 P1 P2 四 P3 P3 四 P5 P5 P1 P3 P5 P6 P6 P： P3 P6 P6 J1 J3 J4 J2 n J2 J4 J5 n J2 J1 n J1 J3 J4 J4 J1 J2 J4 200 100 700 100 400 200 500 400 400 100 200 200 100 300 200 100 200 200 500 试分别用关系代数、AIJHA 语言、QBE 语言完成下列操作： (1)求供应工程 n 零件的供应商号 SNO； 答 (2)求供应工程 n 零件 n的供应商号 SNO； 答 关系代数 (3)求供应工程 n 红色零件的供应商号 SNO； 答 (4)求没有使用天津供应商生产的红色零件的工程号 JNO； 答 解析 减法运算中被减的部分是使用了天津供应商生产的红色零件的所有工程号， JNO 是全 部工程的工程号，两者相减就是没有使用天津供应商生产的红色零件的工程号，包括没有使 用任何零件的工程号。 (5)求至少用了 S1 供应商所供应的全部零件的工程号 JNO。 答 关系代数 解析 上面公式中除号前的部分是所有工程与该工程所用的零件，除号后的部分是 S1 所供应 的全部零件号。对于 SPJ 表中的某一个 JNO，如果该工程使用的所有零件的集合包含了 S1 所供应的全部零件号，则该 JNO 符合本题条件，在除法运算的结果集中。可以看到，使用 关系代数的除法运算概念清晰，语言表达也很简单。 第三章 3用 SQL 语句建立第二章习题 5中的 4 个表。 答： 对于 S表：S(SNO，SNAME，STATUS，CITY)； 建 S表 CREATE TABLE S ( S N O CH A R(3) , S N A ME C H A R( 10) , S T A TU S CH A R( 2) , CI TY CH A R ( 10) ) ; 对于 P表：P( PNO, PNAME, COLOR, WEIGHT); 建 P表 CREATE TABLE P ( P N O CH A R(3) , P N A ME CH A R( 10) , COLOR CHAR(4), WEI G H T I NT) ; 对于 J 表：J(JNO，JNAME，CITY)； 建 J 表 CREATE TABLE J ( JN O CH A R( 3) , J N A ME C H A R( 10) , CI T Y CH A R( 10) ) ; 对于 SPJ 表：SPJ(SNO，PNO，JNO，QTY)； 建 SPJ 表 CREATE TABLE SPJ (SNO CHAR(3), PNO CHAR(3), JNO CHAR(3), Q T Y I N T ) ; 4针对上题中建立的 4 个表试用 SQL 语言完成第二章习题 5 中的查询。 ( 1) 求供应工程 Jl 零件的供应商号码 SNO； SELECT SNO F RO M S P J W H E RE J N O = J l ; ( 2) 求供应工程 J1 零件 P1 的供应商号码 SNO； SELECT SNO F RO M S P J WHERE JNO = Jl AND PNO = P1; ( 3) 求供应工程 J1 零件为红色的供应商号码 SNO； SELECT SNO / *这是嵌套查询* / F RO M S P J WHERE JNO = Jl AND PNO IN / *找出红色零件的零件号码 PNO * / (SELECT PNO F RO M P / *从 P表中找*/ WH ERE CO LO R =红); 或 SELECT SNO F RO M S P J , P /*这是两表连接查询*/ WHERE JNO = J1 /*这是复合条件连接查询*/ AND SPJ. PNO= P. PNO AND COLOR =红； ( 4) 求没有使用天津供应商生产的红色零件的工程号 JNO； 这里的第一种解法是使用多重嵌套查询，第二种方法的子查询是一个多表连接。 注意：从 J 表入手，以包含那些尚未使用任何零件的工程号。 SELECT JNO F RO M J WHERE NOT EXISTS (SELECT * F RO M S P J WH ERE S P J. JN O = J. JN O AND SNO IN *天津供应商的 SNO* (SELECT SNO F RO M S WH ERE CI TY =天津) A ND P N O IN *红色零件的 PNO * (SELECT PNO F RO M P WH ERE CO LO R=红) ); 或 SELECT JNO F RO M J WHERE NOT EXISTS (SELECT * FROM SPJ, S, P W H E RE S P J . J N O = J . J N O AND SPJ. SNO = S. SNO AND SPJ. PNO = P. PNO AND S.CITY=天津 AND P. COLOR=红); (5)求至少用了供应商 S1 所供应的全部零件的工程号 JNO(类似于概论P111 例 47)。 SELECT DISTINCT JNO FROM SPJ SPJZ WHERE NOT EXISTS /*这是一个相关子查询*/ (SELECT * /*父查询和子查询均引用了 SPJ 表*/ F RO M S P J S P J X / *用别名将父查询与子查询中的 SPJ 表区分开*/ WHERE SNO=S1 AND NOT EXISTS (SELECT * FROM SPJ SPJY WHERE SPJY.PNO=SPJX.PNO AND SPJY.JNO=SPJZ.JNO)； 若设 P表示谓词“供应商 S1 供应了零件 y” ；q表示谓词“工程 x 选用了零件 y” 。上述 查询可以抽象为：要求这样的工程 x，使(Vy)pq 为真。即，对于所有的零件 y，满足逻辑 蕴涵 pq。即，只要“供应商 S1 供应了零件 y”为真，则“工程 x 选用了零件 y”为真。 逻辑蕴涵可以转换为等价形式： 不存在这样的零件 y，供应商 S1 供应了 y，而工程 x 没有选用 y。 5针对习题 3 中的 4 个表试用 SQI 语言完成以下各项操作： (1)找出所有供应商的姓名和所在城市。 SELECT SNAME,CITY F RO M S ； ( 2) 找出所有零件的名称、颜色、重量。 SELECT PNAME，COLOR，WEIGHT F RO M H ( 3) 找出使用供应商 S1 所供应零件的工程号码。 SELECT JNO F RO M S P J WHERZ SNO=S1； ( 4) 找出工程项目 J2使用的各种零件的名称及其数量。 SELECT P.PNAME，SPJ.QTY F RO M P ，SPJ WHERE P.PNO=SPJ.PNO AND SPJ.JNO=J2； ( 5) 找出上海厂商供应的所有零件号码。 SELECT DISTINCT PNO F RO M S P J WHERE SNO IN (SELECT SNO F RO M S WHERE CITY=上海)； ( 6) 找出使用上海产的零件的工程名称。 SELECT JNAME P RO M J ，SPJ，S WHERE J.JNO=SPJ.JNO AND SPJ.SNO=S.SNO AND S.CITY=上海； 或 SELECT JNAME F RO M J WHERE JNO IN (SELECT JNO F RO M S P J ，S WHERK SPJ.SNO=S.SNO AND S.CITY=上海)； ( 7) 找出没有使用天津产的零件的工程号码。 SELECT JNO F RO M J WHERE NOT EXISTS (SELECT * P RO M S P J WHERE SPJ.JNO=J.JNO AND SNO IN (SELECT SNO F RO M S WHERE CITY=天津)； 或 SELECT JNO F RO M J WHERE NOT EXIST (SELECT F RO M S P J ，S WHERE SPJ.JNO=J.JNO AND SPJ.SNO=S.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； ( 1 0) 从供应商关系中删除 S2 的记录，并从供应情况关系中删除相应的记录。 DELETE F RO M S P J WHERE SNO=S2； DELETE F RO M S WHERE SNO=S2； ( 1 1) 请将(S2,J6,P4,200)插入供应情况关系。 INSERT INTO SPJ(SNO,JNO,PNO,QTY) /*INTO子句中指明列名*/ VALUES(S2,J6,P4,200)； / * 插入的属性值与指明列要对应*/ 或 I N S E R T I N T O S P J /*I N T O子句中没有指明列名*/ VALUES(S2,P4,J6,200)； /*插入的记录在每个属性列上有值*/ / * 并且属性列要和表定义中的次序对应*/ 11请为三建工程项目建立一个供应情况的视图，包括供应商代码(SNO)、零件代码 (PNO)、供应数量(QTY)。针对该视图完成下列查询： ( 1) 找出三建工程项目使用的各种零件代码及其数量。 ( 2) 找出供应商 S1 的供应情况。 建视图： CREATE VIEW V_SPJ AS SELECT SNO,PNO,QTY F RO M S P J WHERE JNO= (SELECT JNO F RO M J WHERE JNAME=三建)； 对该视图查询： ( 1) 找出三建工程项目使用的各种零件代码及其数量。 SELECT PNO,QTY FROM V_SPJ； ( 2) 找出供应商 S1 的供应情况。 SELECT PNO,QTY /* S1 供应三建工程的零件号和对应的数量*/ FROM V_SPJ WHERE SNO=S1； 第四章 8今有两个关系模式： ， 职工(职工号，姓名，年龄，职务，工资，部门号) 部门(部门号，名称，经理名，地址，电话号) 请用 SQL 的 GRANT 和 REVOKE 语句(加上视图机制)完成以下授权定义或存取控 制功能： ( 1) 用户王明对两个表有 SELECT 权力。 GRANT SELECT ON 职工,部门 T O王明； ( 2) 用户李勇对两个表有 INSERT 和 DELETE 权力。 GRANT INSERT,DELETE ON 职工,部门 T O李勇； ( 3) 每个职工只对自己的记录有 SELECT 权力。 GRANT SELECT ON 职工 WHEN USER()=NAME T O A L L ； 这里假定系统的 CRANT 语句支持 WHEN子句和 USER()的使用。 用户将自己的名 字作为 ID。注意，不同的系统这些扩展语句可能是不同的。 ( 4) 用户刘星对职工表有 SELECT 权力，对工资字段具有更新权力。 GRANT SELECT,UPDATE(工资) ON 职工 T O刘星； ( 5) 用户张新具有修改这两个表的结构的权力。 GRANT ALTER TABLE ON 职工,部门 T O张新； ( 6) 用户周平具有对两个表所有权力(读，插，改，删数据)，并具有给其他用户授权 的权力。 GRANT ALL PRIVILIGES ON 职工,部门 T O周平 WITH GRANT OPTION； ( 7) 用户杨兰具有从每个部门职工中 SELECT 最高工资、最低工资、平均工资的权 力，他不能查看每个人的工资。 首先建立一个视图。然后对这个视图定义杨兰的存取权限。 CREATE VIEW部门工资 AS SELECT 部门,名称,MAX(工资),MIN(工资),A VG(工资) F RO M职工,部门 W H E RE职工.部门号=部门.部门号 GROUP BY 职工.部门号； GRANT SELECT ON 部门工资 T O杨兰； 9把习题 8 中(1)(7)的每一种情况，撤销各用户所授予的权力。 (1) REVOKE SELECT ON职工,部门 F RO M王明； (2) REVOKE INSERT,DELETE ON 职工,部门 F RO M李勇； (3) REOVKE SELECT ON职工 WHEN USER()=NAME F RO M A L L ； 这里假定用户将自己的名字作为 ID，且系统的 REOVKE 语句支持 WHEN 子句， 系统也支持 USER()的使用。 (4) REVOKE SELECT,UPDATE ON 职工 F RO M刘星； (5) REVOKE ALTER TABLE ON 职工,部门 F RO M张新； (6) REVOKE ALL PRIVILIGES ON职工,部门 F RO M周平； (7) REVOKE SELECT ON部门工资 F RO M杨兰； DROP VIEW 部门工资； 第五章 6假设有下面两个关系模式： 职工(职工号，姓名，年龄，职务，工资，部门号)，其中职工号为主码； 部门(部门号，名称，经理名，电话)，其中部门号为主码。 用 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 Cl CHECK(Aage=60)， J o b V A RCH A R( 9) ， Sal NUMBER(7，2)， Deptno NUMBER(2)， CONSTRAINT FK_DEPTNO FOREIGN KEY(Depmo) REFERENCES DEPT(Deptno)； 第六章 2建立一个关于系：学生、班级、学会等诸信息的关系数据库。 描述学生的属性有：学号、姓名、出生年月、系名、班号、宿舍区。 描述班级的属性有：班号、专业名、系名、人数、人校年份。 描述系的属性有：系名、系号、系办公室地点、人数。 描述学会的属性有：学会名、成立年份、地点；人数。 有关语义如下：一个系有若干专业，每个专业每年只招一个班，每个班有若干学生。一 个系的学生住在同一宿舍区。每个学生可参加若干学会，每个学会有若干学生。学生参加某 学会有一个入会年份。 请给出关系模式，写出每个关系模式的极小函数依赖集，指出是否存在传递函数依 赖，对于函数依赖左部是多属性的情况讨论函数依赖是完全函数依赖，还是部分函数依赖。 指出各关系的候选码、外部码，有没有全码存在? 答 关系模式：学生 S(S#，SN，SB，DN，C#，SA) 班级 C(C#，CS，DN，CNUM，CDATE) 系 D(D#，DN，DA，DNUM) 学会 P(PN，DATEl，PA，PNUM) 学生一学会 SP(S#，PN，DATE2) 其中，S#一学号，SN 一姓名，SB出生年月，SA 一宿舍区 C # 一班号，CS一专业名，CNUM 一班级人数，CDATE 一入校年份 D #一系号，DN 一系名，DA 一系办公室地点，DNUM 一系人数 P N一学会名，DATEl 一成立年月，PA 一地点，PNUM 一学会人数，DATE2 一入 会年份 每个关系模式的极小函数依赖集： S ：S#SN，S#SB，S#C#，C#DN，DNSA C ：C#CS，C#CNUM，C#CDATE，CSDN，(CS，CDATE) C# *因为每个专业每年只招一个班* D ：D#DN，DND#，D#-DA，D#DNUM *按照实际情况，系名和系号是一一对应的* P ：PNDATEl，PNPA，PNPNUM S P ：(S#，PN) DATE2 S中存在传递函数依赖：S#DN，S#SA，C#SA *因为 S#C#，C#DN，DNSA* C中存在传递函数依赖：C#DN *因为 C#CS，CSDN * ( S #，PN) DATE2 和(CS，CDATE) C#均为 SP 中的函数依赖，是完全函数依 赖。 关系 候选码 外部码 全码 S S # C#，DN 无 C C# ，(CS，CD A T E ) D N 无 D D#和D N 无 无 P P N 无 无 S P ( S #，P N ) S #，PN 无 解析 应该根据题目中给出的有关语义写出关系模式中的数据依赖， 有些依赖可以按照实 际情况写出，也许题目中并没有明显指出。例如，按照实际情况，系名和系号是一一对应的， 因此有 D#DN，DND#。 第七章 18现有一局部应用，包括两个实体： “出版社”和“作者” ，这两个实体是多对多的 联系，请读者自己设计适当的属性，画出 E-R 图，再将其转换为关系模型(包括关系名、属 性名、码和完整性约束条件)。 答 E R 图为： 关系模型为： 作者(作者号，姓名，年龄，性别，电话，地址) 出版社(出版社号，名称，地址，联系电话) 出版(作者号，出版社号，书的数量) 出版关系的主码作者号， 出版社号分别参照作者关系的主码作者号和出版社关系的 主码出版社号。 19请设计一个图书馆数据库，此数据库中对每个借阅者保存读者记录，包括：读 者号，姓名，地址，性别，年龄，单位。对每本书存有：书号，书名，作者，出版社。对每 本被借出的书存有读者号、借出日期和应还日期。要求：给出 E-R 图，再将其转换为关系 模型。 答 E R 图为： 关系模型为： 读者(读者号，姓名，地址，性别，年龄，单位) 书(书号，书名，作者，出版社) 借书(读者号，书号，借出日期，应还日期) 第九章 2对学生课程数据库有如下的查询： SELECT Cname F RO M S tud en t，Course，SC WHERE StudentSno=SCSno AND SCCno= CourseCno AND StudentSdept=IS ； 此查询要求信息系学生选修了的所有课程名称；试画出用关系代数表示的语法树， 并用关系代数表达式优化算法对原始的语法树进行优化处理，画出优化后的标准语法树。 答 第十一章 9设 T1，T2，T3 是如下的 3 个事务，设 A的初始值为 0： T 1 ：A： ：A+2； T 2 ：A： ：A* 2； T 3 ：A：A* 2；(AA 2 ) ( 1) 若这 3 个事务允许并行执行，则有多少可能的正确结果，请一一列举出来。 答 A的最终结果可能有 2、4、8、16。 因为串行执行次序有 T1 T2 T3、 Tl T3 T2、 T2 Tl T3、 T2 T3 T1、 T3 T1 T2、 T3 T2 T1。 对应的执行结果是 16、8、4、2、4、2。 ( 2) 请给出一个可串行化的调度，并给出执行结果 答 最后结果 A为 16，是可串行化的调度。 ( 3) 请给出一个非串行化的调度，并给出执行结果。 答 最后结果 A为 0，为非串行化的调度。 ( 4) 若这 3 个事务都遵守两段锁协议，请给出一个不产生死锁的可串行化调度。 答 (5)若这 3 个事务都遵守两段锁协议，请给出一个产生死锁的调度。 答
展开阅读全文