数据库作业题目

2. 列出所有供应商的信息，包括供应商、所供应的零件名（没有供应零件的 供应商也要列出，最后结果中不要出现重复元组） select distinct SNAME,T1.PNAME from S right outer join ( select SPJ.SNO,P.PNAME from SPJ,P where SPJ.PNO=P.PNO ) as T1 on S.SNO=T1.SNO
1. 求向的工程供应了红色零件的供应商。
∏ ( S.SNAME
S.SNO=SPJ.SNO ∧ P.COLOR=’ 红 色 ’ ∧ J.CITY=’’
( S×P×J×SPJ))
2. 求只向的工程供应零件的供应商。
∏
( S.SNAME
S.SNO=SPJ.SNO
∧
J.CITY=’
’
( S×J×SPJ))
3. 求至少供应了两种不同零件的供应商。
∏ SNO, PNO (SPJ) ÷ ∏ ( PNO ( SNO=’s1’ SPJ))
8. 求和 s1 号供应商所供应的零件完全相同的供应商的供应商号。
∏SNO (SPJ) － ∏SNO (∏ SNO (SPJ)
× ∏ ( PNO
SNO=’s1’
(SPJ))
－ ∏SNO,
PNO (SPJ) )
－ ∏ ∏ (SPJ) SNO( SNO, PNO
∏ S.SNAME(S⋈ (X.SNO=Y.SNO ∧ X.PNO ≠
Y.PNO (ρ X (SPJ) × ρ Y (SPJ) ) )
4. 求没有供应任何零件的供应商。
∏ (S⋈ S.SNAME ( ∏ S.SNO(S)- ∏
S.SNO(SPJ) )
....
..
..
5. 求供应了所有零件的供应商。
....
..
..
(select S.CITY,S.SNO,sum(SPJ.QTY) as SUMQTY from SPJ,S where SPJ.SNO=S.SNO group by S.CITY, S.SNO) T4 group by T4.CITY) T2, S T3 where T1.SNO=T3.SNO and T2.MAXSUMQTY=T1.SUMQTY （或者使用 with 子句， Oracle9i 版本后支持， sqlserver 不支持） with T1(CITY,SNO,SUMQTY) as
– 给出其候选码
5. 求供应了所有零件的供应商。 select S.SNAME
....
..
..
from S where not exists (select P.PNO from P where not exists ( select * from SPJ where SPJ.SNO=S.SNO and SPJ.PNO=P.PNO
....
..
..
（工程可以向不同的供应商购买同一零件，总金额＝单价*供货数量）。 select J.JNO , sum(SPJ.QTY)*J.PRICE
from SPJ , J where SPJ.JNO=J.JNO and J.COLOR= ’红色’ group by J.JNO order by sum(SPJ.QTY) 9. 求供应零件数量最多的供应商。 select T1.SNAME from S T1, ( select SPJ.SNO from SPJ group by SPJ.SNO having sum(SPJ.QTY)>=all (select sum(T1.QTY) from SPJ T1 group by T1.SNO) ) T2 where T1.SNO=T2.SNO 10.求每个城市中供应零件数量最多的供应商。
..
..
作业一：ER 设计
题目一：用 ER 图可以表达下列哪些数据完整性约束，不能表达哪些约束？能表
达的给出 ER 图。Βιβλιοθήκη Baidu
1. 每门课选课人数不能低于 10 个，不能高于 100 个
答：不能表达约束
2. 课程名是唯一的
课程名
课程
3. 不能供应不存在的零件
供应商名
零件号
零件号
零件名
供应商
4. 性别只能为男或女
3. 求只向与自己位于不同城市的工程供应零件的供应商。 select distinct T1.SNO,T1.SNAME from S T1, SPJ T2,J T3 where T1.SNO=T2.SNO and T2.JNO=T3.JNO and T1.CITY=T3.CITY except select distinct T4.SNO,T4.SNAME from S T4, SPJ T5,J T6 where T4.SNO=T5.SNO and T5.JNO=T6.JNO and T4.CITY=T6.CITY
的 E-R 图，使得可以从该 E-R 图推导出上述关系模式。
E-R 图如下：
....
..
..
题目三：考虑设计一个关系数据库，它要存储以下信息： 教师有教工号、教工名、职称；项目有项目号、项目名称、项目类型、 起始年份、截至时间、资助额；学生有学号、学生名、年龄、学位。 学生分为本科生和研究生，老师按职称可以分为讲师、副教授、教授， 副教授以上职称的可以作为研究生的导师。 一个教工可以负责多个项目；每个项目只能有一个负责人；一个老师可 以参与多个项目；一个本科生只能参与一个项目，一个研究生学生可以 参与多个项目；一个项目可以有多个学生和老师参与；学生参与项目时 必须（如果改为可以呢？）有一个老师作为他的指导老师。 E-R 图如下：
∏ (S⋈ (∏ ( SPJ) S.SNAME
SPJ.SNO,SPJ.PNO
÷
∏P.PNO (P) ))
6. 求供应了所有红色零件的供应商。
∏ (S⋈ (∏ ( SPJ) S.SNAME
SPJ.SNO,SPJ.PNO
÷
∏ ( (P)) )) P.PNO
P.COLOR=’红色’
7. 求供应了 s1 号供应商所供应的所有零件的供应商的供应商号。
select T3.SNAME,T2.MAXSUMQTY from (select S.SNO,sum(SPJ.QTY) as SUMQTY from SPJ,S where SPJ.SNO=S.SNO group by S.SNO) T1, (select max(T4.SUMQTY) AS MAXSUMQTY from
答：不能表达约束
5. 每个学生都必须得选课
学号
姓名
供应
零件
课程号
课程号
课程名
学生
选课
零件
6. 学生可以参加多个社团，但所参加的社团的活动时间必须不同 答：不能表达约束
7. 学生可以参加多个项目，参加不同的项目其指导老师也不同
....
..
..
学生
参加
项目
指导
老师
题目二：解答以下问题 1. 列举聚集、弱实体、细化/泛化的实用例子，并用 ER 图表示出来。 聚集：客户签订合同与采购产品之间是聚集关系
....
..
..
题目四：下面是一采购订单的票据，根据上面列出的信息，给出其实体联系模型。
名称
业务员
供应商
提交
采购
货品 货品名称
作业二：关系代数
题目一： S(SNO, SNAME, STATUS, CITY)
....
编号
日期
订单
状态
..
..
P(PNO, PNAME, COLOR, WEIGHT, CITY) J(JNO, JNAME,CITY) SPJ(SNO, PNO, JNO, QTY) S 表示供应商，各属性依次为供应商号，供应商名，供应商状态值，供应商所在 城市； P 表示零件，各属性依次为零件号，零件名，零件颜色，零件重量，零件存放的 城市； J 表示工程，各属性依次为工程号，工程名，工程所在城市； SPJ 表示供货关系，各属性依次为供应商号，零件号，工程号，供货数量。 基于以上 SPJ 关系模式用关系代数表达查询：
) ) 6. 求供应了所有红色零件的供应商。 select S.SNAME from S where not exists (select P.PNO from P where not exists ( select * from SPJ where SPJ.SNO=S.SNO and SPJ.PNO=P.PNO and P.COLOR=’红’ ) ) 7. 列出每个城市的工程所使用的零件总的数量。 select sum(J.QTY) from SPJ,J where SPJ.JNO=J.JNO group by J.CITY 8. 按零件数量总和的降序列出每项工程所使用的每种红色零件的总的金额
弱实体：下图中教科书属于弱实体
....
..
..
细化 /泛化：家俱与（桌子、椅子）属于细化/泛化关系
名称
厂家
家俱
ISA
桌子
椅子
编号
编号
2. 已知有如下关系模式：E1(a1, a2, a3)，E2(a3, a4)，E3(a5, a6)，E4(a3,
a5, a7)，其中带下划线的属性标识为所在关系模式的主码。试画出相应
select S.CITY,S.SNO,sum(SPJ.QTY) from SPJ,S where SPJ.SNO=S.SNO group by S.CITY, S.SNO with T2(MAXSUMQTY) as select max(T1.SUMQTY) from T1 group by T1.CITY select T3.SNAME from T1,T2,S T3 where T1.SNO=T3.SNO and T2.MAXSUMQTY=T1.SUMQTY
1. 求没有供应 p1 和 p2 两种零件的供应商。 select SNAME from S where not exists ( select SPJ.SNO from SPJ where S.SNO = SPJ.SNO and ( PNO=’p1’ or PNO=’ p 2’)
....
..
..
4. 求只向与自己位于相同城市的工程供应零件的供应商。 select distinct T4.SNO,T4.SNAME from S T4, SPJ T5,J T6 where T4.SNO=T5.SNO and T5.JNO=T6.JNO and T4.CITY=T6.CITY except select distinct T1.SNO,T1.SNAME from S T1, SPJ T2,J T3 where T1.SNO=T2.SNO and T2.JNO=T3.JNO and T1.CITY!=T3.CITY
X
(R)) Y
结果如果恒定为 1 ，说明 A 的取值是唯一的。
作业三：基本 SQL
S(SNO, SNAME, STATUS, CITY) P(PNO, PNAME, COLOR, WEIGHT, CITY) J(JNO, JNAME,CITY) SPJ(SNO, PNO, JNO, QTY) S 表示供应商，各属性依次为供应商号，供应商名，供应商状态值，供应商所在 城市； P 表示零件，各属性依次为零件号，零件名，零件颜色，零件重量，零件存放的 城市； J 表示工程，各属性依次为工程号，工程名，工程所在城市； SPJ 表示供货关系，各属性依次为供应商号，零件号，工程号，供货数量。 基于以上 SPJ 关系模式用 SQL 完成下面的操作：
－
∏ SNO(SPJ) ×
∏ ( PNO
SNO= ’ s1 ’
(SPJ)) )
题目二： 对于关系 R(A, B, C)，用关系代数来检验 A 是否取值唯一。
....
..
..
关系 R ≠ ϕ 并且 R 中元组{ a, b, c }是唯一的，则：
(ρ (R)×ρ X.A=Y.A ∧ (X.B ≠ Y.B∧ X.C≠ Y.C)
作业四：关系规化
题目一：分别写出关系代数和 SQL 语句，验证关系 R(ABC)上 AB 和 AB 是否成立。
....
..
..
解：验证 AB 关系代数：
( ρ (R) × ρ 如 果 X.A=Y.A ∧ X.B ≠ Y.B
X
(R)) Y
结果如果恒定为 0 ，则 A B 成立；否则不成立。
SQL 验证： create assertion AtoB check (not exists (select A from R group by A having count(distinct B) > 1 ) ) 题目二：R(ABCDE), F = {ABC, BD, CE, CEB, ACB}。