关系代数与关系演算（下）

模式
branch(branch_name,branch_city,assets)
customer(customer_name, customer_street,customer_city)
loan(loan_number, branch_name,amount)
borrower(customer_name, loan_number)
account(account_number, branch_name,balance)
depositor(customer_name, account_number)

例子
 1.找出在银行有存款及在银行有借贷的客户名
 {t|?s ∈borrower(t[customer_name]=s[customer_name]) ∧ ?u∈depositor(t[customer_name]=u[customer_name])}
∏customer_name(borrower) ∪ ∏customer_name(depositor)
 SELECET customer_name
 FROM  borrower
 UNION?
SELECT customer_name
FROMdepositor
2.找出在银行有存款但是没借款的客户名
{t|?u∈depositor(t[customer_name]=u[customer_name]) ∧ ﹁?s∈borrower(t[customer_name]=s[customer_name]) }
∏ customer_name(depositor) –
∏ customer_name(borrower)
SELECET customer_name
 FROM  depositor ?
 EXCEPT
?SELECT customer_name
 FROM  borrower
3.找出在perryridge有借贷的客户名
  ∏ customer_name(σborrower.loan_number=loan.loan_number
  (σbranch_name=“ perryridge”( borrower×loan)))
   SELECET customer_name
   FROM  borrower loan
   WHERE borrower.loan_number=loan.loan_number    AND   branch_name=“ perryridge”
  4.找出在brooklyn的所有银行都有存款的客户名
  思路：用逻辑蕴函表达：查询客户x，对所有的银行y，只要brooklyn 有银行y，则x在y就有存款。
形式化表示：
 用P表示谓词 “brooklyn 有银行y”
 用q表示谓词 “客户x在y有存款”
 则上述查询为: (?y) p ? q
等价变换：
         由p? q ≡ ? p∨q
              (?y)p1 ≡ ? ?y(? p1)
     (?y)p ? q≡  ? ?y(p∧?q)

SELECET distinct S.customer_name
FROM  depositor as S
WHERE not exists    
((SELECET branch_name
   FROM  branch
   WHERE branch_city=“ brooklyn”
    except
?  SELECT R.branch_name
   FROM depositor as T, account as R
   WHERE T.account_number=R. account_number
           AND S.customer_name=T. customer_name))
∏customer_name,branch_name(depositor    account) ÷ ∏branch_name(σbranch_city=“ brooklyn”( branch)

{t|?r ∈customer(r[customer_name]=t[customer_name]) ∧(?u∈branch(u[branch_city]=“brooklyn”→?s      ∈ depositor(t[customer_name]=s[customer_name]
     ∧?w∈account(w[account_number]=s[account_number] ∧?w[branch_name]=u[branch_name]))))}

{t|?r ∈customer(r[customer_name]=t[customer_name]) ∧
  (﹁? u∈branch(u[branch_city]=“brooklyn” ∧ ﹁ ?s      ∈ depositor(t[customer_name]=s[customer_name]
     ∧ ?w∈account(w[account_number]=s[account_number] ∧w[branch_name]=u[branch_name]))))}

5.查询至少选修了学生95002选修的全部课程的学生号码
学生关系Student (Sno, Sname, Sage, Sdept)
课程关系Course (Cno, Cname, Cpon, Score)
选修关系SC( Sno, Cno ,Score)
解题思路：查询学号为x的学生，对课程y，只要95002学生选修了课程y，则x也选修了y。

形式化表示：
 用P表示谓词 “学生95002选修了课程y”
 用q表示谓词 “学生x选修了课程y”
 则上述查询为: (?y) p ? q
等价变换：
     (?y)p ? q  ≡  ? (?y (?(p ? q ))
               ≡  ? (?y (?(? p∨ q)
               ≡  ? ?y(p∧?q)

变换后语义：不存在这样的课程y，学生95002选修了y，而学生x没有选。
SELECT DISTINCT Sno
         FROM SC SCX
         WHERE NOT EXISTS
               (SELECT *
                FROM SC SCY
                WHERE SCY.Sno = ' 95002 '
                     EXCEPT
                          (SELECT *
                           FROM SC SCZ
                           WHERE SCZ.Sno=SCX.Sno AND             
                                         SCZ.Cno=SCY.Cno))