The Databases Course

Exercise No. 5

More SQL and Design Theory - Theoretical

Due on January 26th by midnight

  1. Express the following queries in SQL:
  1. Which research project(s) uses the largest number of sites?
  2. For each whale and each site such that the whale was used in that site more than twice, what is the number of different projects that were involved?
  3. What is the average age of animals participating in projects? If an animal participates in two projects, or twice in the same project, count it twice for the average.
  4. Same as (c), but if an animal participates in the same project more than once, count it only once for the average.
  5. Which animals were used in all the projects that were somehow active in 1994? Hint: This is a division question. Remember how we defined division using the other algebra operators - the 'minus' keyword is probably is quickest solution here as well.
  1. Like most programming languages, SQL contains some redundancy to make programmers' life easier.
  1. Show that any query using the 'in' predicate can be rewritten to use 'exists' instead.
  2. Show that any query using '>=all', '>any', '<>all' and others from this family of operators can be rewritten using the 'exists' predicate as well.
  1. You are given a relation scheme S = { A1, ..., An } and a set of functional dependencies F. Write an efficient algorithm to compute a simple key of R = <S,F>. What is the running time of your algorithm?
  1. Prove the following lemmas for functional dependencies inference, in two ways: Once using Armstrong's axioms, and once directly using the definition of functional dependencies.
  1. X ® A1,A2,...,An if and only if for all i=1..n, X ® Ai.
  2. X1 ® X2, X2 ® X3, ..., Xn ® X1 if and only if for all i,j=1..n, Xi ® Xj.
  1. Let R be a relation with S = { A, B, C, D, E, F } and F = { A ® F, AC ® D, AF ® B, E ® AF, EC ® F, EFB ® D}.
  1. Find a non-redundant (= minimal) cover for F. Show, step by step, how the algorithm taught in class finds this minimal cover.
  2. Let X = { A , C }. Use the table algorithm to find X+F,AA.
  3. Find all the simple keys of R.