Some sample exam 2 questions:

1. Briefly define the following terms:

   Bulk Loading of a B+ Tree

   3NF (Third Normal Form)

   BCNF (Boyce-Codd Normal Form)

   Functional dependency

   Lossless join decomposition

   Dependency preserving decomposition

2. For each of the following index methods describe
     (1) How the index is represented
     (2) Where data is stored in the index
     (3) How the following operations are executed
         searching the index for a key value
         insert a key value in the index
         deleting a key value from the index
   Also discuss the advantages and disadvantages of each method.

   The methods:

     ISAM
     B+ Trees
     Static Hashing
     Extendible Hashing
     Linear Hashing

3. Give an algorithm for a two-way external merge sort.  How many passes would
   your algorithm need for a file consisting of 87 pages?

4. Give an algorithm for a n-way external merge sort.  How many passes would
   your algorithm need for a file consisting of 87 pages when you have 15
   buffers?

5. 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 
   condition?

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

6. 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?

7. 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

   Hash-Join

8. 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.

9. 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.

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

11. 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?

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

13. What is the most important thing to consider when determining whether
    to include an index in a database schema?  Discuss some issues that should
    be considered for choosing to include a particular index?

14. Under what situation would you not decompose a relation that is in 3NF
    into a set of relations in BCNF?  Discuss the factors that influence these
    types of decisions.

15. What is discretionary access control?  Explain how discretionary access
    control is implemented using GRANT and REVOKE commands.

16. What is mandatory access control?  How does it differ from discretionary
    access control?

17. Define the term transaction.  What are some of the important characteristics
    of transactions?