数据库例题，零件供应商

  • 在一个数据库系统中，定义4个关系模式

供应商表S：

SNO供货商代码

SNAME供货商姓名

STATUS供货商状态

CITY供货商所在城市

CREATE TABLE S
(
  SNO char(3) primary key, 
  SNAME char(10), 
  STATUS char(2), 
  CITY char(10),
) ;

零件表P：

PNO零件代码

PNAME零件名

COLOR颜色

WEIGHT重量

CREATE TABLE P
(
  PNO char(3) primary key, 
  PNAME char(10), 
  COLOR char(4), 
  WEIGHT int, 
) ;

工程项目表J：

JNO工程项目代码

JNAME工程项目名

CITY工程项目所在城市

CREATE TABLE J
(
  JNO char(3) primary key, 
  JNAME char(10), 
  CITY char(10), 
) ;

供应情况表SPJ：

SNO供应商代码

PNO零件代码

JNO工程项目代码

QTY供应数量

CREATE TABLE SPJ
(
  SNO char(3), 
  PNO char(3), 
  JNO char(3), 
  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), 
) ;
  • 所有供应商的姓名和所在城市
SELECT SNAME CITY
FROM S ;
  • 所有零件的名称、颜色和重量
SELECT PNAME COLOR WEIGHT
FROM P ;
  • 使用供应商S1所供应零件的工程号码
SELECT DISTINCT JNO
FROM SPJ
WHERE SNO='S1' ;
  • 工程项目J2所使用的各种零件的名称及其数量
SELECT PNAME, SUM (QTY)
FROM P, SPJ
WHERE JNO='J2' AND P.PNO=SPJ.PNO
GROUP BY PNO ;
  • 上海厂商所供应的所有零件号码
SELECT DISTINCT PNO
FROM SPJ, S
WHERE S.CITY='上海' AND S.SNO=SPJ.SNO ;
  • 使用上海产的零件的工程名称
SELECT JNAME
FROM SPJ, J, S
WHERE S.CITY='上海' AND S.SNO=SPJ.SNO AND SPJ.JNO=J.JNO ;
  • 没有使用天津产的零件的工程号码
SELECT JNO
FROM J
WHERE NOT EXISTS
(
	SELECT *
	FROM SPJ, S
	WHERE J.JNO=SPJ.JNO AND SPJ.SNO=S.SNO AND S.CITY='天津'
) ;
  • 把全部红色零件的颜色改成蓝色
UPDATE P
SET COLOR='蓝'
WHERE COLOR='红' ;
  • 由S5供给J4的零件P6改为由S3供应
UPDATE SPJ
SET SNO='S3'
WHERE SNO='S5' AND PNO='P6' AND JNO='J4' ;
  • 从供应商关系和供应情况关系中删除S2的记录
DELETE
FROM S
WHERE SNO='S2' ;
DELETE
FROM SPJ
WHERE SNO='S2' ;
  • 将 (S2, J6, P4, 200) 插入供应情况关系
INSERT INTO SPJ
VALUES ('S2', 'J6', 'P4', 200) ;
  • 工程项目代码J1的零件的供应商代码SNO
\[    \pi_{SNO}(\sigma_{JNO='J1'}(SPJ))\\
    \{ t| \exists u(SPJ(u) \wedge u[3]='J1' \wedge t[1]=u[1]) \}\\
\]
SELECT DISTINCT SNO
FROM SPJ
WHERE JNO='J1' ;
  • 工程项目代码J1的零件P1的供应商代码SNO
\[    \pi_{SNO}(\sigma_{JNO='J1' \wedge PNO='P1'}(SPJ))\\
    \{ t| \exists u(SPJ(u) \wedge u[3]='J1' \wedge u[2]='P1' \wedge t[1]=u[1]) \}\\
\]
SELECT DISTINCT SNO
FROM SPJ
WHERE JNO='J1' AND PNO='P1' ;
  • 工程项目代码J1的红色零件的供应商代码SNO
\[    \pi_{SNO}(\pi_{SNO,PNO}(\sigma_{JNO='J1'}(SPJ))\bowtie\pi_{PNO}(\sigma_{COLOR='红'}(P)))\\
    \{ t| \exists u \exists v(SPJ(u) \wedge P(v) \wedge u[3]='J1' \wedge u[2]=v[1] \wedge v[3]='红' \wedge t[1]=u[1]) \}\\
\]
SELCT DISTINCT SNO
FROM SPJ, P
WHERE SPJ.PNO=P.PNO AND P.COLOR='红' AND SPJ.JNO='J1' ;
  • 没有使用天津供应商生产的红色零件的工程项目代码JNO
\[    \begin{gather}
    \pi_{JNO}(J)-\pi_{JNO}(\pi_{PNO}(\sigma_{COLOR='红'}(P))\bowtie\pi_{SNO,PNO,JNO}(SPJ)\bowtie\pi_{SNO}(\sigma_{CITY='天津'}(S)))\\
    \{ t| \exists u (J(u) \wedge \lnot \exists v \exists w \exists x (SPJ(v) \wedge S(w) \wedge P(x) \wedge v[3]=u[1] \wedge v[1]=w[1] \wedge v[2]=x[1] \wedge w[4]='天津' \wedge x[3]='红') \wedge t[1]=u[1]) \}\\
    \end{gather}
\]
SELECT JNO
FROM J
WHERE NOT EXISTS
(
	SELECT *
	FROM SPJ, S, P, J
	WHERE J.JNO=SPJ.JNO AND SPJ.SNO=S.SNO AND SPJ.PNO=P.PNO 
	AND S.CITY='天津' AND P.COLOR='红'
) ;
  • 至少用了供应商S1所供应的全部零件的工程项目代码JNO
\[    \pi_{JNO,PNO}(SPJ)\div\pi_{PNO}(\sigma_{SNO='S1'}(SPJ))\\
    \{ t| \exists u (SPJ(u) \wedge \forall v(SPJ(v) \wedge v[1]='S1' \rightarrow \exists w(SPJ(w) \wedge w[1]=v[1] \wedge w[2]=v[2] \wedge w[3]=u[3])) \wedge t[1]=u[3]) \}\\
\]

用p表示谓词“供应商S1供应了零件y”
用q表示谓词“工程项目代码x使用了零件y”
则“至少用了供应商S1所供应的全部零件的工程项目代码JNO”
等价于“对每一个零件y，只要它是供应商S1所供应的，那么工程项目代码x就使用了y”
等价于\(\forall y (p \rightarrow q)\)
上式可化简为\(\lnot (\exists y (p \wedge \lnot q))\)
等价于“不存在零件y，它是供应商S1所供应的，而工程项目代码x没有使用y”

SELECT JNO
FROM SPJ SPJX
WHERE NOT EXISTS
(
	SELECT *
	FROM SPJ SPJY
	WHERE SPJY.SNO='S1' AND NOT EXISTS
	(
		SELECT *
		FROM SPJ SPJZ
		WHERE SPJX.JNO=SPJZ.JNO AND SPJY.PNO=SPJZ.PNO
	)
) ;
  • 授权用户WangMing对S表和P表的SELECT权限
GRANT SELECT
ON TALBE S, P
TO WangMing ;
  • 授权用户LiYong对S表和P表的INSERT和DELETE权限
GRANT SELECT INSERT
ON TABLE S, P
TO LiYong ;
  • 授权用户LiuXing对P表的SELECT权限，和对P表的PNAME字段的UPDATE权限
GRANT SELECT, UPDATE (PNAME)
ON TABLE P
TO LiuXing ;
  • 授权用户ZhouPing对P表和S表的全部权限，且授权他将这些权限授权给别人
GRANT ALL PRIVILEGES
ON TABLE P, S
TO ZhouPing
WITH GRANT OPTION ;
  • 授权用户YangLan对每种零件在各个项目中的最高供应数量，最低供应数量和平均供应数量的SELECT的权限，且他不能查看供应数量
CREATE VIEW PNAME_QTYs
AS
SELECT PNAME, MAX (QTY), MIN (QTY), AVG (QTY)
FROM P, SPJ
WHERE P.PNO=SPJ.PNO
GROUP BY P.PNO ;

GRANT SELECT
ON PNAME_QTYs
TO YangLan ;
  • 给P表添加一个名为"C1"的完整性约束，该约束规定了QTY的值大于等于10
ALTER TABLE P
ADD CONSTRAINT C1
CHECK (QTY=10) ;