CS 4611 Take Home Final

Due May 3rd by noon -- NO LATE EXAMS ALLOWED

To take this final you must signup in class or see me (Rich) by April 26

You must submit a signed copy of the following page with your exam.


  1. Draw an ER diagram to capture the following information: [25 points]
  2. Define in SQL a set of relations to capture the information from the previous question. Make sure to indicate keys and include foreign keys. Can you capture all of the constraints listed above in your definition? If not, why not? Could you capture these constraints in some other manner (besides defining relations)? (If so, how?) [20 points]
  3. For the following relations:
         Supplier (id: integer, name: string(20), address: string(50))
           - id is unique
         Part (id: integer, color: string(10), name: string(20), cost: real)
           - id is unique
         Contractor (id: integer, name: string(20), address: string(50))
           - id is unique
         Project (id: integer, cid: integer, budget: real, duedate: date)
           - id is unique
           - cid is a foreign key referring to Contractor
         Order (projid: integer, partid: integer, suppid: integer, quantity: integer)
           - the combination of projid, partid, suppid is unique
           - projid is a foreign key referring to Project
           - partid is a foreign key referring to Part
           - suppid is a foreign key referring to Supplier

    Construct the following queries:

  4. For the indexing methods ISAM, B+ trees, Extendible Hashing and Linear Hashing attempt to characterize the amount of space needed for each method in the best, worst, and averages cases based on the number of keys in the index and how many keys are stored on a page. You may introduce terms as needed (for example, you will likely need to include a term capturing overflow pages for ISAM). Also characterize the best, worst and average case lookup time (in page I/Os) for each method. Make sure to justify your answers and list any assumptions. [20 points]
  5. For the linear hashing indexing method: [20 points]
  6. Define the terms 3NF and BCNF. Define a relation that is not in 3NF. Show how you could decompose that relation into 3NF. Then show how you could further decompose the resulting relations to result in BCNF. Discuss the advantages and disadvantages of your decompositions. [20 points]
  7. Answer Exercise 14.8 from page 409 of the text. [30 points]
  8. Answer Exercise 19.2 from page 566 of the text for the question of whether these schedules are conflict-serializable. If they are not, show a dependency graph indicating why not. [20 points]
  9. Define Strict Two Phase Locking. Give an example of a schedule where a potential concurrency problem would be avoided using Strict 2PL. Make sure to clearly indicate how the locking affects the schedule. [15 points]
  10. Define write-ahead logging. Explain why it is so important for recovery. Define the Aries method for crash recovery. Include an example with transactions that would be identified in all three phases and how those transactions would be dealt with. [20 points]