CS 4250: Database Management Systems

Spring 2020 - Homework 3

Due Thursday 5/14/2020, at midnight

This is an individual assignment. All work must be entirely your own. You should not look at any other student's work (in whole or in part, on paper or on screen), nor allow anyone else to look at yours, nor discuss the assignment with other students, during the course of this assignment.

(Strongly prefer plain text. Will accept PDF or MSWord documents, if typed.) Email with subject line "cs4250,hw3" (by midnight).

  1. Consider a relation R with attributes ABCDEF. You are given the following dependencies A -> B, D -> E, AC -> E.

    1. Attribute closure of A?

    2. Attribute closure of C?

    3. Attribute closure of D?

    4. Attribute closure of AC?

    5. Attribute closure of ABCDEF?

    6. Identify which, if any, of the above are candidate keys.
  2. Consider a relation R with attributes ABCDE. You are given the following dependencies AC -> B, AC -> D, AC -> E, E -> C.

    1. List all (candidate) keys for R.

    2. Is R in 3NF? Why or why not?

    3. Is R in BCNF? Why or why not?

  3. Consider a relation R with attributes ABCDE. You are given the following dependencies A -> B, A -> C, A -> D, A -> E, C -> B.

    1. List all (candidate) keys for R.

    2. Is R in 3NF? Why or why not?

    3. Is R in BCNF? Why or why not?

  4. Consider a relation R with attributes ABCDE. You are given the following dependencies AB -> C, AB -> D, AB -> E.

    1. List all (candidate) keys for R.

    2. Is R in 3NF? Why or why not?

    3. Is R in BCNF? Why or why not?

  5. Suppose you are given a relation R(A, B, C, D). For each of the following sets of FDs, assume they are the only dependencies that hold for R. Do the following: a) identify the candidate key(s) for R. b) State whether or not the proposed decomposition of R into smaller relations is a good decomposition and briefly explain why or why not.

    1. A -> B, B -> C, B -> D; decompose into ABC and BCD.
      1. Candidate keys:
      2. Analysis:

    2. A -> C, B -> D; decompose into AC and BD.
      1. Candidate keys:
      2. Analysis:

    3. A -> C, D -> ABC; decompose into AC and BCD.
      1. Candidate keys:
      2. Analysis:

    4. B -> CD, D -> A; decompose into BCD and AD.
      1. Candidate keys:
      2. Analysis:

  6. Suppose you are given a relation R with attributes ABCDEF. You are given functional dependencies A -> E, AC -> B, and D -> C. Using the decomposition techniques described in class, decompose this relation into relations in "good" normal forms. For each of the new relations you create, identify the normal form it is in.

    If you choose not to decompose the relations into the best normal form we discussed, explain why you made that choice. If there are any interesting issues or potential problems with your decomposition, explain what they are.

  7. Consider the following schedule:

    T1: R(A), T1: R(B), T2: R(B), T3: R(A), T3: W(A), T2: W(B), T2: R(A), T2: W(A), T1: R(D), T1: W(D), T3: W(C), T3: R(B)

    Is the schedule serializable? If so, show an equivalent serial transaction order. If not, precisely describe why not.

    If relevant, fill in this table with the equivalent serial transaction order. Time proceeds from left to right, with only one action possible in each time slot.

    Serializable Schedule Time 1 Time 2 Time 3 Time 4 Time 5 Time 6 Time 7 Time 8 Time 9 Time 10 Time 11 Time 12 Time 13 Time 14 Time 15
    T1














    T2














    T3














  8. Consider the following schedule:

    T2: W(A), T1: R(A), T1: W(A), T3: R(A), T1: R(A), T3: R(A), T2: R(B), T2: W(B), T1: R(A), T3: R(B), T3: W(B), T1: R(C), T2: R(C)

    Is the schedule serializable? If so, show an equivalent serial transaction order. If not, precisely describe why not.

    If relevant, fill in this table with the equivalent serial transaction order. Time proceeds from left to right, with only one action possible in each time slot.

    Serializable Schedule Time 1 Time 2 Time 3 Time 4 Time 5 Time 6 Time 7 Time 8 Time 9 Time 10 Time 11 Time 12 Time 13 Time 14 Time 15
    T1














    T2














    T3














  9. For questions 3-5, consider the execution of the ARIES recovery algorithm given the following log:

    LSN Log Record
    00 begin_checkpoint
    05 end_checkpoint
    10 Update: T3 writes P1
    20 Update: T1 writes P1
    30 Update: T1 writes P2
    40 Update: T2 writes P3
    50 T1 abort
    60 T3 commit
    70 Update: T4 writes P2
    80 CLR: Undo T1 LSN 30
    90 T2 abort
    100 T3 end
    110 Update: T4 writes P3
    X - crash, restart

    For the questions below, when you are asked which log records are read, you are to supply the exact list of LSNs from log above. When data pages are asked for, you are to supply the exact list of page identifiers from the log above. And so on. Be specific and concrete in your answers, answering specifically for the provided log.

    Operations can be identified using the LSN for the log record recording that operation. (So, of course, can the log record itself.)

    Generic statements or quotations from the textbook will earn 0 points. Example: "all the pages" == 0 points. "P1, P2 and P3" == more than 0 points, assuming those are the pages read.

  10. During Analysis:

  11. During Redo:

  12. During Undo: