Complex RA Expressions

1
Complex RA Expressions

• Scenario
• Purchase(pid, seller-ssn, buyer-ssn, etc.)
• Person(ssn, name, etc.)
• Product(pid, name, etc.)
• Q Who (give names) bought gizmos from Dick?
• Where to start?
• Purchase uses pid, ssn, so must get them

2
Complex RA Expressions
P name
P pid
P ssn
sname'Dick'
sname'Gizmo'

• Person Purchase Person
  Product

3
Translation to SQL
(the names of the people who bought gadgets from
Dick)

• Were converting the tree on the last slide into
  SQL
• The result of the query should be the names
  indicated above
• One step at a time, well make the query more
  complete, until weve translated the
  English-language description to an actual SQL
  query
• Well also simplify the query when possible

4
Translation to SQL
SELECT DISTINCT name buyer FROM (the info, along
with buyer names, for purchases of gadgets sold
by Dick)

• Blue type actual SQL
• Black italics description of subquery
• Note the subquery above consists of purchase
  records, except with the info describing the
  buyers attached
• In the results, the column header for name will
  be 'buyer'

5
Translation to SQL
SELECT DISTINCT name buyer FROM (SELECT FROM
Person, (the purchases of gadgets from Dick)
P2 WHERE Person.ssn P2.buyer-ssn)

• Note the subquery in this version is being given
  the name P2
• Were pairing our rows from Person with rows from
  P2

6
Translation to SQL
SELECT DISTINCT name buyer FROM Person, (the
purchases of gadgets from Dick) P2 WHERE
Person.ssn P2.buyer-ssn

• We simplified by combining the two SELECTs

7
Translation to SQL
SELECT DISTINCT name buyer FROM Person, (SELECT
FROM Purchases
WHERE seller-ssn (Dicks
ssn)
AND
pid (the id of gadget)) P2 WHERE
Person.ssn P2.buyer-ssn

• P2 is still the name of the subquery
• Its just been filled in with a query that
  contains two subqueries
• Outer parentheses are bolded for clarity

8
Translation to SQL
SELECT DISTINCT name buyer FROM Person, (SELECT
FROM Purchases
WHERE seller-ssn (SELECT
ssn
FROM Person
WHERE
nameDick)
AND
pid (the id of gadget)) P2 WHERE
Person.ssn P2.buyer-ssn

• Now the subquery to find Dicks ssn is filled in

9
Translation to SQL
SELECT DISTINCT name buyer FROM Person, (SELECT
FROM Purchases
WHERE seller-ssn (SELECT
ssn
FROM Person
WHERE
nameDick)
AND
pid (SELECT pid
FROM Product

WHERE name'Gadget')) P2 WHERE Person.ssn
P2.buyer-ssn)

• And now the subquery to find Gadgets product id
  is filled in, too
• Note the SQL simplified by using subqueries
• Not used in relational algebra