数据库系统原理--第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)组成。foreign key references用法
零件表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))