CPS 430/542 Lecture notes: Primitive Query Operators


Coverage: [FCDB] §§5.2 (pp. 191-214), 6.1 (pp. 239-254), and 10.1-10.2 (pp. 463-480)

Legend: RA = relational algebra, DL = Datalog

assume set semantics

Simple properties

  • ∪ is commutative, i.e., R ∪ S = S ∪ R
  • ∩ is commutative, i.e., R ∩ S = S ∩ R
  • π is commutative, i.e., πa (πb (R)) = πb (πa (R))
  • σ is commutative, i.e., σa (σb (R)) = σb (σa (R))

List all relations in database 

    SELECT * FROM TAB;

Relation schemas 

    Movies(title, year, length, studio)
Stars(name, height, studio)
Actors(name, age)
Actresses(name, age)
Students(id, name)
Grades(id, gpa)

Return all tuples from Movies

  • RA: Movies
  • SQL: 
    SELECT *
FROM Movies;
  • DL: Movies(t,y,l,s)

Union of Movies1 and Movies2

    (must have same schema, including types)
  • Movies1 ∪ Movies2
  • SQL: 
    ((SELECT *
  FROM Movies1) UNION
 (SELECT *
  FROM Movies2));
  • DL (no way to do it in one line): 
    MoviesU(t,y,l,s) ← Movies1(t,y,l,s)
MoviesU(t,y,l,s) ← Movies2(t,y,l,s)

Intersection of Movies1 and Movies2

    (must have same schema, including types)
  • RA: Movies1 ∩ Movies2
  • SQL: 
    ((SELECT *
  FROM Movies1) INTERSECT
 (SELECT *
  FROM Movies2));
  • DL: MoviesI(t,y,l,s) ← Movies1(t,y,l,s) AND Movies2(t,y,l,s)

Difference of Movies1 and Movies2

    (must have same schema, including types)
  • RA: Movies1 - Movies2
  • SQL: 
    ((SELECT *
  FROM Movies1) MINUS
 (SELECT *
  FROM Movies2));
    older versions of Oracle use keyword EXCEPT
  • DL: MoviesI(t,y,l,s) ← Movies1(t,y,l,s) AND NOT Movies2(t,y,l,s)
    • use NOT with caution
    • queries must be safe so that result is a finite relation, and so that rules with arithmetic subgoals or with negated subgoals make intuitive sense
    • safe query: every variable which appears anywhere (head, negated relation subgoal, or arithmetic subgoal) in the rule must also appear in some nonnegated, relation subgoal, e.g., LongMovies(t,y) ← Movie(t,y,_,_) AND l > 120

Projection

  • removes columns
  • sometimes introduces duplicate tuples, which are pruned out, remember we are using set semantics
    • (retrieve the titles for all movies)
  • RA: πtitle (Movies)
  • SQL: 
    SELECT title
FROM Movies;
  • DL: Titles(t) ← Movies(t,_,_,_)

Selection

  • removes rows (entire tuples)

    • retrieve long movies (>120)
  • RA: σlength > 120 (Movies)
  • SQL: 
    SELECT *
FROM Movies
WHERE length > 120;
  • DL: LongMovies(t,y,l,s) ← Movies(t,y,l,s) AND L > 120.

    • retrieve long movies (>120) made by Paramount
  • RA: (σstudio = 'Paramount' (Movies)) ∩ (σlength > 120 (Movies))
  • RA: σstudio = 'Paramount' AND length > 120 (Movies)
  • SQL: 
    ((SELECT *
FROM Movies
WHERE studio = 'Paramount') INTERSECT
 (SELECT *
  FROM Movies
  WHERE length > 120));
  • SQL: 
    SELECT *
FROM Movies
WHERE studio = 'Paramount' AND length > 120;
  • DL: LongParamountMovies(t,y,l,s) ← Movies(t,y,l,s) AND s = 'Paramount' AND l > 120

Retrieve titles of long movies made by Paramount

    cascade both: selection followed by a projection
  • RA: πtitle (σstudio = 'Paramount' AND length > 120 (Movies))
  • SQL: 
    SELECT title
FROM Movies
WHERE studio = 'Paramount' AND length > 120;
  • DL: TitlesLongParamountMovies(t) ← Movies(t,y,l,s) AND s = 'Paramount' AND l > 120

Find all movies made by Fox or in 1997

  • RA: σstudio = 'Fox' OR year = 1997 (Movies)
  • SQL: 
    SELECT *
FROM Movies
WHERE studio = 'Fox' OR year = 1997;
  • DL: 
    Movies2(t,y,l,s) ← Movies(t,y,l,s) AND s = 'Fox'
Movies2(t,y,l,s) ← Movies(t,y,l,s) AND y = 1997

Find all movies which are either long or made by Fox

  • RA: σlength > 120 OR studio = 'Fox' (Movies)
  • SQL: 
    SELECT *
FROM Movies
WHERE length > 120 OR studio = 'Fox';
  • DL: 
    LongFox(t,y,l,s) ← Movies(t,y,l,s) AND l > 120
LongFox(t,y,l,s) ← Movies(t,y,l,s) AND s = 'Fox'

Find all movies which are neither long nor made by Fox

use DeMorgan's laws: ¬(long ∨ Fox) ⇔ (¬long ∧ ¬Fox)
  • RA: (σlength ≤ 120 (Movies)) ∩ (σstudio ≠ 'Fox' (Movies))
  • SQL: 
    ((SELECT *
  FROM Movies
  WHERE length <= 120) INTERSECT
 (SELECT *
  FROM Movies
  WHERE studio <> 'Fox'));
  • DL: ShortFox(t,y,l,s) ← Movies(t,y,l,s) AND l <= 120 AND s ≠ 'Fox'

Find all movies which are not both long and made by Fox

use DeMorgan's laws: ¬(long ∧ Fox) ⇔ (¬long ∨ ¬Fox)
  • RA: (σlength <= 120 (Movies)) ∪ (σstudio <> 'Fox' (Movies)
  • SQL: 
    ((SELECT *
  FROM Movies
  WHERE length <= 120) UNION

 (SELECT *
  FROM Movies
  WHERE studio <> 'Fox'));
  • DL: 
    Movies2(t,y,l,s) ← Movies(t,y,l,s) AND l <= 120
Movies2(t,y,l,s) ← Movies(t,y,l,s) AND s ≠ 'Fox'




Find names of all
stars who are neither tall nor contracted by Fox.


use DeMorgan's laws:
¬(tall ∨ Fox) ⇔ (¬tall ∧ ¬Fox)

    

  • RA: πname ((σheight < 72
(Stars)) ∩
(σstudio <> 'Fox'
(Stars)))
    • 

  • SQL:


    SELECT name
FROM Stars
WHERE height < 72 AND studio <> 'Fox';

    

    • 


  • DL: Answer(n) ← Stars(n,h,s) AND h < 72
AND s ≠ 'Fox'
    • 




Find names of all
stars who are not both tall and contracted by Fox.


use DeMorgan's laws:
¬(tall ∧ Fox) ⇔ (¬tall ∨ ¬Fox)

    

  • RA: (πname (σheight < 72
(Stars))) ∪
(πname (σstudio ≠ 'Fox'
(Stars)))
    • 


  • SQL:

    SELECT name
FROM Stars
WHERE height < 72 OR studio <> 'Fox';

    


  • DL:


    Answer(n) ← Stars(n,h,s) AND h < 72.
Answer(n) ← Stars(n,h,s) AND s ≠ 'Fox'.

    

    • 




References



    

    

    
[FCDB]
J.D. Ullman and J. Widom. A First Course in Database Systems.
Prentice Hall, Upper Saddle River, NJ, Second edition, 2002.


    

    





Return Home