数据库系统原理--第2章作业2--习题答案

题目：

设有一个SPJ 数据库,包括S、P、J 及SPJ4 个关系模式
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。
今有若干数据如下：

试用关系代数完成如下查询：SPJ
(1)求供应工程J1 零件的供应商号码SNO；
(2)求供应工程J1 零件P1 的供应商号码SNO；
(3)求供应工程J1 零件为红色的供应商号码SNO；
(4)求没有使用天津供应商生产的红色零件的工程号JNO；
(5)求至少用了供应商S1 所供应的全部零件的工程号JNO。

答案

(1)
ΠSNO(σJNO=‘J1’（SPJ）)
(2)
ΠSNO(σJNO='J1’∧PNO=‘P1’(SPJ))
(3)
ΠSNO(ΠJNO,PNO(σJNO=‘J1’(SPJ))∞ΠPNO(σCOLOR=‘红’ ( P) ))
(4)
ΠJNO(J)-ΠJNO(ΠSNO(σCITY=‘天津’(S))∞ΠPNO(σCOLOR=‘红’( P))∞ΠSNO,JNO,PNO(SPJ))
(5)
ΠJNO,PNO(SPJ)÷ΠPNO,SNO(σSNO=‘S1’(SPJ))

说明：把这段代码复制到软件上一步步运行即可出现题目要求的表，以及题目的答案；每道题SQL查询语言和关系代数查询语言在后面。想自己操作的话可以复制到软件上运行下。

--创建数据库
CREATE DATABASE SPJ
--使用数据库
USE SPJ
--创建S表
CREATE TABLE S(
SNO CHAR(10) PRIMARY KEY,
SNAME CHAR(50) UNIQUE,
STATUS INT,
CITY CHAR(20))
--添加数据到S表
INSERT INTO S(SNO,SNAME,STATUS,CITY)
VALUES('S1','精益',20,'天津'),
('S2','盛锡',10,'北京'),
('S3','东方红',30,'北京'),
('S4','丰泰盛',20,'天津'),
('S5','为民',30,'上海')
--检验
SELECT * FROM S
--创建P表
CREATE TABLE P(
PNO CHAR(10) PRIMARY KEY,
PNAME CHAR(50) ,
COLOR CHAR(20),
WEIGHT INT)
--添加数据到P表
INSERT INTO P(PNO,PNAME,COLOR,WEIGHT)
VALUES('P1','螺母','红',12),
('P2','螺栓','绿',17),
('P3','螺丝刀','蓝',14),
('P4','螺丝刀','红',14),
('P5','凸轮','蓝',40),
('P6','齿轮','红',30)
--检验
SELECT * FROM P
--创建J表
CREATE TABLE J(
JNO CHAR(10) PRIMARY KEY,
JNAME CHAR(50) UNIQUE ,
CITY CHAR(20))
--添加数据到J表
INSERT INTO J(JNO,JNAME,CITY)
VALUES('J1','三建','北京'),
('J2','一汽','长春'),
('J3','弹簧厂','天津'),
('J4','造船厂','天津'),
('J5','机车厂','唐山'),
('J6','无线电厂','常州'),
('J7','半导体厂','南京')
--检验
SELECT * FROM J
---创建SPJ表
CREATE TABLE SPJ(
SNO CHAR(10),
PNO CHAR(10),
JNO CHAR(10),
QTY INT,
PRIMARY KEY(SNO,PNO,JNO),
FOREIGN KEY(SNO) REFERENCES S(SNO),  --外键约束
FOREIGN KEY(PNO) REFERENCES P(PNO),
FOREIGN KEY(JNO) REFERENCES J(JNO),
)
--添加数据到SPJ表
INSERT INTO SPJ(SNO,PNO,JNO,QTY)
VALUES ('S1','P1','J1',200),
('S1','P1','J3',100),
('S1','P1','J4',700),
('S1','P2','J2',100),
('S2','P3','J1',400),
('S2','P3','J2',200),
('S2','P3','J4',500),
('S2','P3','J5',400),
('S2','P5','J1',400),
('S2','P5','J2',100),
('S3','P1','J1',200),
('S3','P3','J1',200),
('S4','P5','J1',100),
('S4','P6','J3',300),
('S4','P6','J4',200),
('S5','P2','J4',100),
('S5','P3','J1',200),
('S5','P6','J2',200),
('S5','P6','J4',500)
--检验
SELECT * FROM SPJ

--题目：试用关系代数完成如下查询：SPJ
--（1）求供应工程J1 零件的供应商号码SNO；
SELECT * FROM SPJ
WHERE JNO='J1'
--关系代数：ΠSNO(σJNO=‘J1’（SPJ）)
--（2）求供应工程J1零件P1的供应商号码SNO：
SELECT * FROM SPJ
WHERE JNO='J1'AND PNO ='P1'
--关系代数：ΠSNO(σJNO='J1'∧PNO='P1'(SPJ))
--（3）求供应工程J1零件为红色的供应商号码SNO：
SELECT * FROM SPJ
WHERE JNO = 'J1' AND PNO IN (
SELECT PNO FROM P
WHERE COLOR='红')
--关系代数：ΠSNO(ΠJNO,PNO(σJNO='J1'(SPJ))∞ΠPNO(σCOLOR='红'(P)))
--（4）求没有使用天津供应商生产的红色零件的工程号JNO:
SELECT JNO FROM J
WHERE JNO NOT IN(
SELECT JNO FROM SPJ,P,S
WHERE S.CITY='天津'
AND P.COLOR='红'
AND SPJ.PNO=P.PNO
AND SPJ.SNO=S.SNO
)
--关系代数：ΠJNO(J)-ΠJNO(ΠSNO(σCITY='天津'(S))∞ΠPNO(σCOLOR='红'(P))∞ΠSNO,JNO,PNO(SPJ))
--解析：减法运算中被减的部分是使用了天津供应商的红色零件的所有工程号，
--ΠJNO(J)是全部工程的工程号，两者相减就是题意了
--（5）求至少用了供应商S1所供应的全部零件的工程号JNO：
--由于VFP不允许子查询嵌套太深，将查询分为两步
--A、查询S1供应商供应的零件号
SELECT PNO FROM SPJ WHERE SNO='S1'--结果是（P1，P2）
--B、查询哪一个工程既使用P1零件又使用P2零件。
SELECT * FROM SPJ 
WHERE PNO='P1'
AND JNO IN (SELECT JNO FROM SPJ WHERE PNO='P2')--结果是J4
--关系代数：ΠJNO,PNO(SPJ)÷ΠPNO,SNO(σSNO='S1'(SPJ))