Databases : SQL MultiRelations

1
Databases SQL Multi-Relations

• 2007, Fall
• Pusan National University
• Ki-Joune Li

These slides are made from the materials that
Prof. Jeffrey D. Ullman distributes via his
course web page (http//infolab.stanford.edu/ullm
an/dscb/gslides.html)
2
Multi-Relation Queries

• More than one relation.
• Several relations in the FROM clause.
• Distinguish attributes of the same name
• ltrelationgt.ltattributegt
• Example
• Using Likes(drinker, beer) and Frequents(drinker,
  bar), find the beers liked by at least one
  person who frequents Joes Bar.
• SELECT beer
• FROM Likes, Frequents
• WHERE bar Joes Bar ANDFrequents.drinker
  Likes.drinker

3
Formal Semantics

• Almost the same as for single-relation queries
• Start with the product of all the relations in
  the FROM clause.
• Apply the selection condition from the WHERE
  clause.
• Project onto the list of attributes and
  expressions
• in the SELECT clause.

4
Operational Semantics

• Imagine one tuple-variable for each relation in
  the FROM clause.
• These tuple-variables visit each combination of
  tuples, one from each relation.
• If the tuple-variables are pointing to tuples
  that satisfy the WHERE clause, send these tuples
  to the SELECT clause.
• Nested Algorithm
• For each tuple r1 in R1
• For each tuple r2 in R2
• if the condition(r1, r2) in WHERE clause is
  true then print the attributes in SELECT
  clause
• Problem of this nested Algorithm
• SELECT R.AFROM R, S, SWHERE R.A S.A OR R.A
  T.Awhen T is empty set

5
Example
drinker bar drinker
beer b1 b2 Sally Bud Sally
Joes Likes Frequents
6
Explicit Tuple-Variables

• Sometimes, a query needs to use two copies of the
  same relation.
• Distinguish copies by tuple-variables in FROM
  clause.
• Example From Beers(name, manf),
• find all pairs of beers by the same manf.
• Do not produce pairs like (Bud, Bud).
• Produce pairs in alphabetic order, e.g. (Bud,
  Miller), not (Miller, Bud).
• SELECT b1.name, b2.name
• FROM Beers b1, Beers b2
• WHERE b1.manf b2.manf AND b1.name lt b2.name

7
Union, Intersection, Difference

• Example
• MovieStar(name, address, gender,
  birthdate)MovieExec(name, address, cert,
  netWorth)
• Find names and addresses of all female movie
  stars who are also movie executives with a net
  worth over 10M (SELECT name, address FROM
  MovieStar WHERE genderF)
  INTERSECT (SELECT name, address FROM
  MovieExec WHERE netWorth gt 10000000)

8
Subqueries

• Parenthesized SFW statement (subquery) can be
  used
• as a value returns ONE tuple
• as tuples returns a set of tuples
• related with relations returns Boolean value
• in FROM clause

9
Subqueries That Return One Tuple

• If a subquery is guaranteed to produce one tuple,
  then the subquery can be used as a value.
• Usually, the tuple has one component.
• Also typically, a single tuple is guaranteed by
  keyness of attributes.
• A run-time error occurs if there is no tuple or
  more than one tuple.

10
Example

• From Sells(bar, beer, price), find the bars that
  serve Miller for the same price Joe charges for
  Miller.
• Two queries would surely work
• Find the price Joe charges for Miller.
• Find the bars that serve Miller at that price.

11
Query Subquery Solution

• SELECT bar
• FROM Sells
• WHERE beer Miller AND
• price ( SELECT price
• FROM Sells
• WHERE bar Joes Bar
• AND beer Miller)

The price at which Joe sells Bud
SELECT s1.bar FROM Sells s1,Sells s2 WHERE
s1.beer Miller AND s1.prices2.price AND
s2.barJoes Bar AND s2.beer Miller
12
Subqueries That Return More than one Tuple

• If a subquery may produce more than one tuple,
• then the subquery can be used as a set of values
• Set operator are used IN and NOT IN operators
• IN operator
• lttuplegt IN ltrelationgt is true if and only if the
  tuple is a member of the relation.
• lttuplegt NOT IN ltrelationgt means the opposite.
• IN-expressions can appear in WHERE clauses.
• The ltrelationgt is often a subquery.

13
Example

• From Beers(name, manf) and Likes(drinker, beer),
  find the name and manufacturer of each beer that
  Fred likes.

SELECT FROM Beers WHERE name IN (SELECT
beer FROM Likes WHERE drinker Fred)
The set of beers Fred likes
SELECT beer. FROM Beers beer, Likes like WHERE
beer.nameLikes.beer AND Likes.drinkerFred
14
The Exists Operator

• EXISTS( ltrelationgt ) is true if and only if the
  ltrelationgt is not empty.
• Being a boolean-valued operator, EXISTS can
  appear in WHERE clauses.
• Example
• From Beers(name, manf),
• find those beers that are the unique beer by
  their manufacturer.

15
Example Query with EXISTS
Notice scope rule manf refers to closest nested
FROM with a relation having that attribute.

• SELECT name
• FROM Beers b1
• WHERE NOT EXISTS(
• SELECT
• FROM Beers
• WHERE manf b1.manf AND
• name ltgt b1.name)

Set of beers with the same manf as b1, but not
the same beer
16
The Operator ANY

• x ANY( ltrelationgt ) is a boolean condition
  meaning that x equals at least one tuple in the
  relation.
• Similarly, can be replaced by any of the
  comparison operators.
• Example x gt ANY( ltrelationgt ) means x is not
  smaller than all tuples in the relation.
• Note tuples must have one component only.

17
The Operator ALL

• Similarly, x ltgt ALL( ltrelationgt ) is true if and
  only if for every tuple t in the relation, x is
  not equal to t.
• That is, x is not a member of the relation.
• The ltgt can be replaced by any comparison
  operator.
• Example x gt ALL( ltrelationgt ) means there is no
  tuple larger than x in the relation.

18
Example

• From Sells(bar, beer, price),
• find the beer(s) sold for the highest price.

price from the outer Sells must not be less than
any price.
SELECT beer FROM Sells WHERE price gt ALL(
SELECT price FROM Sells)
19
Example

• From Movie(title, year, length, inColor,
  studioName, producerC),
• find movie titles used for more than one film

year of movie production with the same title.
SELECT title FROM Movie Old WHERE year lt ANY(
SELECT year FROM Movie titleOld.title)
20
Subqueries in FROM Clause

• Subqueries can be used in FROM Clause
• StarsIn(movieTitle, movieYear, starName)MovieExec
  (name, address, cert, netWorth)Movie(title,
  year, length, inColor, studioName, producerC)
• Find producer the names of Harrison Fords movies

Foreign Key
SELECT name FROM MovieExec, (SELECT producerC
FROM Movie, StarsIN WHERE titlemovieTitle
AND yearmovieYear AND starNameHarrison
Ford ) ProdWHERE certProd.producerC
Set of producerC of Harrison Fords movies
21
JOIN

• Example
• MovieStar(name, address, gender,
  birthdate)MovieExec(name, address, cert,
  netWorth)
• CROSS JOIN Cartisan Product
• Movie CROSS JOIN StarsIN
• JOIN Theta Join
• Movie JOIN StarsIN on titlemovieTitle AND year
  movieYear
• NATURAL JOIN Natural Join
• MovieStar NATURAL JOIN MovieExec

Condition of Theta Join
Join on (name, address)