Midterm Review Topics:
Use the ToC from H.G-M for individual topic list. Also included are all
the handouts and class notes on additional algorithms/topics.
  1. ER Data Model (Ch2 from H.G-M)
  2. Relational Model (Ch3 from H.G-M, you may skip Section 3.7)
  3. Relational Algebra (Ch5 from H.G-M, you may skip Section 5.3)
  4. SQL (Ch6 from H.G-M)


Here is an example on 3NF decomposition that is different from the ones we discussed
in class (brought to my attention by Russ Fink)

R(ABCDE)
F = {A->BC, B->C, A ->B, AB->C, BD ->A}  Keys are BDE, ADE
Minimal cover Fc = {A ->B, B->C, BD ->A}
B ->C is a 3NF violation, therefore R needs to be decomposed. Using the minimal cover we get
decomposition {(BC), (ABD)} -- we don't need a separate relation for A->B since we have (ABD) and
the projected FD on it namely A ->B is not a violation because B is part of a key. We need to add a third relation that is the key -- add (BDE) or (ADE).  Therefore, working directly from the minimal cover, the 3NF decomposition set includes {(BC), (ABD), (BDE)}. Further adjustments can be made as follows.
(ABD) and (BDE) (or (ADE)) can be combined into one relation (ABDE) and here is why.
(ABDE) is in 3NF because the projected dependencies are {A->B (RHS is part of key), BD ->A (RHS is part of key),  ADE->B (LHS is key), BDE -> A(LHS is key)}.
SO the final 3NF decomposition is {(BC), (ABDE)}.
Note: here Steven Rook's suggestion of using the tree approach works -- (BC) and the rest (ABDE).
But working with the FDs in the minimal cover to guide you through the decomposition process  and
then making further adjustments/optimization is what I prefer.
---------------------------------------------------------
Minimal cover example discussed in class.

F = { A -> BC, ACD -> E, B -> D, C -> D, AB -> E, E -> BC}

1. ACD -> E can be replaced by AC -> E since C -> D is given.
2. AB -> E can be replaced by A -> E since A -> B is given.
3. AC -> E can be replaced by A -> E since A -> C is given. (in class I combined it as ABC -> E to be replaced
with A -> E because A -> BC was given.)

Modified F up to this point:
= {A -> BC, A -> E, B -> D, C -> D, E -> BC}

Note: do not combine 1st and the 5th FD and reduce it to A -> BC. Instead,
split them into FD's with a single attribute on the right.
So, set of FDs now is:
= { A -> B, A -> C, A -> E, B -> D, C -> D, E -> B, E -> C}
You can now eliminate A -> B because of A -> E, and E -> B;
eliminate A -> C because of A -> E and E -> C.
Note: you could have eliminated A -> BC because of A -> E and E -> BC.

Now the minimal cover is:

Fc = {A -> E, E -> B, E -> C, B -> D, C -> D}