Some sample exam 2 questions:

1. Define the following terms:


   Entity, Entity set (in an ER model)

   Relationship, Relationship set (in an ER model)

   Key constraint (in an ER model)

   Participation constraint (ER)

   Weak entity (ER)

   ISA hierarchy (ER)

   3NF (Third Normal Form)

   BCNF (Boyce-Codd Normal Form)

   Functional dependency

   Lossless join decomposition

   Dependency preserving decomposition

2. A SQL query may have five different clauses, a SELECT, FROM, WHERE,
   GROUP BY and HAVING clause.  Explain how each of these clauses works
   and how they are evaluated in an SQL query.  Give an example using
   all five clauses.

3. For the following relations (keys are shown with _s over the field name):

  Student                  Class_Grade      Class
         ___               ___ ___          ___
  sname  sid  age  gpa     sid cid grade    cid cdept cnum
  --------------------     -------------    --------------
  Ann      1   21  3.5       1   1   A-       1  CS   1621 
  Bob      2   19  3.4       1   2   A        2  BIOL 1011
  Cal      3   20  2.6       2   1   B        3  ECE  1315
  Dee      4   22  4.0       2   3   C
                             3   1   A
                             3   2   C
                             3   3   F
                             4   2   A

  How would you design the following queries in SQL:

  - The id#s of students who are 19 or 20.
  - The student names (sname) of those students who have taken CS 1621.
  - Students (names) who have taken a biology course or ECE course.
  - Students (names) who have taken every course in Class.
  - Students (names) who have received an A.

  For added practice, produce queries for exercises 8.3, 9.1, and 9.3.

4. Answer the question above using QBE and skeletons as shown in class.

5. How is a view defined in SQL?  How are queries on views resolved?  What
   are some of the difficulties introduced with views?

6. If you have a selection operator

   SELECT equality condition ( relation )

   how would you perform the operation under the following conditions, and
   what would be the estimated cost of your approach in disk I/Os.

   - There is no index, data is unsorted
   - There is a clustered tree index
   - There is a hash index

   How would your answer change if the condition were not an equality 

   If there is more than one index available, how do you choose which
   mechanism(s) to use?

7. How is the projection operator implemented with sorting?  How about hashing?
   What are the expected costs for these operations in terms of disk I/Os?

8. Give pseudo-code for the following join operations (you may assume the join
   is performed on a single attribute with an equality condition).  Also give
   the expected cost for each operation.

   Simple Nested Loops Join

   Index Nested Loops Join

   Block Nested Loops Join

   Sort-Merge Join


9. What is meant by the term reduction factor?  How does it apply in query
   evaluation?  Give at least two examples of reduction factors and how they
   might affect a particular query.

10. SQL queries can always be mapped to a simple query plan.  Explain how this
    simple query plan can be generated from any SQL query and why we would not
    want to use such query plans in general.

11. System R only considers left deep plans.  Why is this?  Give an example of
    a plan that is left deep and one that is not.

12. Give an example of an optimization that might be performed on a query
    plan.  What is the advantage of your optimization?

13. What are Armstrong's Axioms for reasoning with a set of functional
    dependencies?  What is the closure of a set of FDs?  What is the
    attribute closure of a set of attributes with respect to a set of FDs?

14. What do functional dependencies relate to the concept of redundancy in a
    database? What is our main mechanism for combatting redundancy?  Give
    an example.