数据库作业答案

P74 5．设有一个SPJ数据库，包括S，P，J，SPJ四个关系模式：

1）求供应工程J1零件的供应商号码SNO：

πSno(σSno=‘J1’（SPJ）)

2）求供应工程J1零件P1的供应商号码SNO：

πSno(σSno=‘J1’∧Pno=‘P1‘(SPJ))

3）求供应工程J1零件为红色的供应商号码SNO：

πSno(σPno=‘P1‘ （σCOLOR=’红‘ （P）∞SPJ）)

4）求没有使用天津供应商生产的红色零件的工程号JNO：

πJno(SPJ)- πJNO（σcity=‘天津’∧Color=‘红‘ （S∞SPJ∞P）

5）求至少用了供应商S1所供应的全部零件的工程号JNO：

πJno，Pno(SPJ)÷ πPno（σSno=‘S1‘ （SPJ））

P127 3 ．用 sQL 语句建立第二章习题 5 中的 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 表 ：

CREATE 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))

4.针对上题中建立的 4 个表试用 sQL 语言完成第二章习题 5 中的查询。

( l ）求供应工程 Jl 零件的供应商号码 SNO ;

SELECT DIST SNO FROM SPJ WHERE JNO=’J1’

( 2 ）求供应工程 Jl 零件 Pl 的供应商号码 SNO ;

SELECT DIST 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 ）求没有使用天津供应商生产的红色零件的工程号 JNO ;

SELECT DIST JNO FROM SPJ WHERE JNO NOT IN (SELE JNO FROM SPJ,P,S WHERE S.CITY='天津' AND COLOR='红' AND S.SNO=SPJ.SNO AND P.PNO=SPJ.PNO)。

( 5 ）求至少用了供应商 Sl 所供应的全部零件的工程号 JNO ;

由于VFP不允许子查询嵌套太深，将查询分为两步

A、查询S1供应商供应的零件号

SELECT DIST PNO FROM SPJ WHERE SNO='S1'结果是（P1，P2）

B、查询哪一个工程既使用P1零件又使用P2零件。

SELECT JNO FROM SPJ WHERE PNO='P1'

AND JNO IN (SELECT JNO FROM SPJ WHERE PNO='P2')

5．针对习题3中的四个表试用SQL语言完成以下各项操作：

(1)找出所有供应商的姓名和所在城市。

SELECT SNAME,CITY FROM S

(2)找出所有零件的名称、颜色、重量。

SELECT PNAME,COLOR,WEIGHT FROM P

(3)找出使用供应商S1所供应零件的工程号码。

SELECT DIST JNO FROM SPJ 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)找出没有使用天津产的零件的工程号码。

注意: SELECT DISP JNO FROM SPJ WHERE JNO NOT IN (SELECT DIST JNO FROM SPJ,S WHERE S.SNO=SPJ.SNO AND S.CITY='天津') 适用于JNO是唯一或不唯一的情况.

注意: SELECT DIST JNO FROM SPJ,S WHERE S.SNO=SPJ.SNO AND S.CITY<>'天津'适用于JNO是唯一的情况

(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、DELETE FROM S WHERE SNO=’S2’

B、DELETE FROM SPJ WHERE SNO=‘S2’

(11)请将(S2，J6，P4，200)插入供应情况关系。

INSERT INTO SPJ VALUES（‘S2’，‘J6’，‘P4’，200）

11．请为三建工程项目建立一个供应情况的视图，包括供应商代码(SNO)、零件代码(PNO)、供应数量(QTY)。

CREATE VIEW VSP AS SELECT SNO,PNO,QTY FROM SPJ,J

WHERE SPJ.JNO=J.JNO AND J.JNAME='三建'

针对该视图VSP完成下列查询：

(1)找出三建工程项目使用的各种零件代码及其数量。

SELECT DIST PNO,QTY FROM VSP

(2)找出供应商S1的供应情况。

SELECT DIST * FROM VSP WHERE SNO='S1'

P148 8．请用SQL的GRANT 和REVOKE语句(加上视图机制)完成以下授权定义或存取控制功能:

( a ）用户王明对两个表有SELECT 权力。

GRANT SELECT ON 职工,部门

TO 王明

( b ）用户李勇对两个表有INSERT 和DELETE 权力。

GRANT INSERT,DELETE ON 职工,部门

TO 李勇

( c ) 每个职工只对自己的记录有SELECT 权力。

GRANT SELECT ON 职工

WHEN USER()=NAME

TO ALL;

( d ）用户刘星对职工表有SELECT 权力，对工资字段具有更新权力。

GRANT SELECT,UPDATE(工资) ON 职工

TO 刘星

( e ）用户张新具有修改这两个表的结构的权力。库

GRANT ALTER TABLE ON 职工,部门

TO 张新;

( f ）用户周平具有对两个表所有权力（读，插，改，删数据），并具有给其他用户授权的权力。

GRANT ALL PRIVILEGES ON 职工,部门

TO 周平

WITH GRANT OPTION;

( g ）用户杨兰具有从每个部门职工中SELECT 最高工资、最低工资、平均工资的权力，他不能查看每个人的工资。

CREATE VIEW 部门工资 AS

SELECT 部门.名称,MAX(工资),MIN(工资),AVG(工资)

FROM 职工,部门

WHERE 职工.部门号=部门.部门号

GROUP BY 职工.部门号

GRANT SELECT ON 部门工资

TO 杨兰;

9 ．把习题8 中（1）---（7）的每一种情况，撤销各用户所授予的权力

(1) REVOKE SELECT ON 职工，部门 FROM 王明；

(2) REVOKE INSERT , DELETE ON 职工，部门 FROM 李勇；

(3) REOVKE SELECT ON 职工

WHEN USER ( ) =NAME

FROM ALI ;

(4) REVOKE SELECT , UPDATE ON 职工

FROM 刘星；

(5) REVOKE ALTER TABLE ON 职工，部门

FROM 张新；

(6) REVOKE ALL PRIVILIGES ON 职工，部门

FROM 周平；

(7) REVOKE SELECT ON 部门工资

FROM 杨兰；

DROP VIEW 部门工资；

P 195 2．建立一个关于系、学生、班级、学会等诸信息的关系数据库。

学生：学号、姓名、出生年月、系名、班号、宿舍区。

班级：班号、专业名、系名、人数、入校年份。

系：系名、系号、系办公地点、人数。

学会：学会名、成立年份、办公地点、人数。

语义如下：一个系有若干专业，每个专业每年只招一个班，每个班有若干学生。一个系的学生住在同一宿舍区。每个学生可参加若干学会，每个学会有若干学生。学生参加某

学会有一个入会年份。

请给出关系模式，写出每个关系模式的极小函数依赖集，指出是否存在传递函数依赖，对于函数依赖左部是多属性的情况讨论函数依赖是完全函数依赖，还是部分函数依赖。指出各关系模式的候选码、外部码，有没有全码存在?

解：(1)关系模式如下：

学生：S(Sno，Sname，Sbirth，Dept，Class，Rno)

班级：C(Class，Pname，Dept，Cnum，Cyear)

系：D(Dept，Dno，Office，Dnum)

学会：M(Mname，Myear，Maddr，Mnum)

(2)每个关系模式的最小函数依赖集如下：

A、学生S (Sno，Sname，Sbirth，Dept，Class，Rno) 的最小函数依赖集如下:SnoSname，SnoSbirth，SnoClass，ClassDept，DEPTRno

传递依赖如下：

由于SnoDept，而DeptSno ，DeptRno（宿舍区）

所以Sno与Rno之间存在着传递函数依赖。

由于ClassDept，Dept  Class，DeptRno

所以Class与Rno之间存在着传递函数依赖。

由于SnoClass，ClassSno，ClassDept

所以Sno与Dept之间存在着传递函数依赖。

B、班级C(Class，Pname，Dept，Cnum，Cyear)的最小函数依赖集如下:

ClassPname，ClassCnum，ClassCyear，PnameDept.

由于ClassPname，PnameClass，PnameDept

所以C1ass与Dept之间存在着传递函数依赖。

C、系D(Dept，Dno，Office，Dnum)的最小函数依赖集如下：

DeptDno，DnoDept，DnoOffice，DnoDnum

根据上述函数依赖可知，Dept与Office，Dept与Dnum之间不存在传递依赖。

D、学会M(Mname，Myear，Maddr，Mnum)的最小函数依赖集如下：

MnameMyear，MnameMaddr，MnameMnum

该模式不存在传递依赖。

(3)各关系模式的候选码、外部码，全码如下：

A、学生S候选码：Sno；外部码：Dept、Class；无全码

B、班级C候选码：Class；外部码：Dept；无全码

C、系D候选码：Dept或Dno；无外部码；无全码

D、学会M候选码：Mname；无外部码；无全码

P234 18 ．现有一局部应用，包括两个实体：“出版社”和“作者”，这两个实体是多对多的联系，请读者自己设计适当的属性，画出 E 一 R 图，再将其转换为关系模型（包括关系名、属性名、码和完整性约束条件）。

答：

关系模型为：作者（作者号，姓名，年龄，性别，电话，地址）出版社（出版社号，名称，地址，联系电话）出版（作者号，出版社号，书的数量）出版关系的主码作者号，出版社号分别参照作者关系的主码作者号和出版社关系的主码出版社号。

19 ．请设计一个图书馆数据库，此数据库中对每个借阅者保存读者记录，包括：读者号，姓名，地址，性别，年龄，单位。对每本书存有：书号，书名，作者，出版社。对每本被借出的书存有读者号、借出日期和应还日期。要求：给出 E 一 R 图，再将其转换为关系模型。

答 ：E 一 R 图为：

关系模型为：读者（读者号，姓名，地址，性别） 书（书号，书名，作者，出版社）借书（读者号，书号，借出日期 ，应还日期）