有关系数据库:
职工关系 EMPLOYEE(职工号Eno,职工名Ename)
工作关系 WORKS(职工号Eno,公司号Cno,工资Wage)
公司关系 COMPANY(公司号Cno,公司名Cname)
假设职工可以在多个公司兼职,请用关系代数表达式写出至少在公司号为’C2’和’C5’公司兼职的职工名。
1.基于除法:
关系代数:
∏Ename((∏Cno, Eno (WORKS) ⌯ ∏Cno (σCno=’C2’ ∨ Cno=’C5’( COMPANY)))∞ EMPLOYEE)
sql语句:
SELECT Ename FROM EMPLOYEE WHERE NOT EXIST(
SELECT * FROM COMPANY WHERE Cno IN (‘C2’, ‘C5’) AND NOT EXIST(
SELECT * FROM WORKS WHERE WORKS.Cno = COMPANY.Cno AND
WORKS.Eno = EMPLOYEE.Eno))
2. 基于自身连接
关系代数:
∏Ename((∏1(σ1=4 ∧ 2=’c2’ ∧ 5=’c5’ (WORKS×WORKS))) ∞ EMPLOYEE)
sql语句:
SELECT Ename FROM EMPLOYEE, WORKS W1, WORKS W2 WHERE
W1.Eno =W2.Eno AND W1.Eno=EMPLOYEE.Eno AND W1.Cno=’C2’ AND W2.Cno=’C5’
3. 基于集合运算
关系代数:
ΠEname ((σCno =' C2' (WORKS) ∩ σCno =‘C5’(WORKS)) ∞ EMPLOYEE)
sql语句:
SELECT Ename FROM EMPLOYEE, (
SELECT Eno FROM WORKS WHERE Cno = ‘C2’ INTERSECT
SELECT Eno FROM WORKS WHERE Cno = ‘C5’) AS DJ
WHERE EMPLOYEE.Eno = DJ.Eno