코드 정리

“Get supplier numbers for suppliers who supply at least all those parts supplied by
supplier S2”

• 관계형 대수
  S {S#} DIVIDEBY ( SP WHERE S# = S#('S2') ) {P#} PER SP{S#, P#}

• 튜플 해석
  SX.S# WHERE FORALL SPX ( SPX.S# ≠ S#('S2') OR EXISTS SPY ( SPY.S# = SX.S# AND
  SPY.P# = SPX.P# ) )

• SQL
  SELECT S.S# FROM S WHERE NOT EXISTS(SELECT FROM SP SPX WHERE SPX.S#='S2'
  AND NOT EXISTS (SELECT FROM SP SPY WHERE SPY.S#=S.S# AND SPY.P#=SPX.P#))

“For each part supplied, get the part number and the total shipment quantity”

• 관계형 대수
  P { P# } ADD SUM (SP WHERE SP.P# = P.P# , QTY ) AS TOTQTY

• 튜플 해석
  ( PX.P#, SUM( SPX WHERE SPX.P# = PX.P#, QTY) AS TOTQTY )

• SQL
  SELECT SP.P#, SUM( SP.QTY ) AS TOTQTY
  FROM SP
  GROUP BY SP.P# ;

• QBE

“Get all pairs of supplier numbers such that the suppliers concerned are collocated”

• 관계형 대수
  (SX.S# AS SA, SY.S# AS SB) WHERE SX.CITY=SY.CITY AND SX.S#<SY.S#

• 튜플 해석

  ( SX AS SA, SY AS SB ) WHERE EXISTS CITYZ
  (S ( S#:SX, CITY:CITYZ) AND S (S#:SY, CITY:CITYZ) AND SX < SY )

• SQL
  SELECT A.S# AS SA, B.S# AS SB
  FROM S AS A, S AS B
  WHERE A.CITY = B.CITY
  AND A.S# < B.S# ;

• QBE
  

SUMMARIZE SP PER S{S#} ADD COUNT AS NP

SELECT S.S#, NP FROM S LEFT OUTER JOIN (SELECT S#, COUNT(*) NP FROM SP
GROUP BY S#) USING (S#);