Lecture 3

Question 1

What are the symptoms of poor design?

Answer 1

Redundacy and anomalies

Question 2

When is something considered poor design when splitting a relation?

Answer 2

If the initial set R cannot be recovered from S and T

Question 3

If you want to put a relationship in BCNF, when do you have to change something?

Answer 3

If attributes in R determined by something that isn’t a (super)key, then B is determined that is not necessarily unique
thus:
- avoidable reduncacy
- decomposition of R advised

Question 4

When is R in BCNF?

Answer 4

For each non-trivial FD a –> b of R:

left-hand side A is a (super)key of R

Question 5

What is a lossless join?

Answer 5

All relations are in BCNF, all data in original relation R represented faithfully by decomposition

Question 6

What is the BCNF Decomposition algorithm?

Answer 6

1. Check whether R is in BCNF, if so return
2. If they exist let on be X -> Y use Algorithm 3.7 to compute X+. Choose R1 = X+ as one relation schema and let R2 have attributes X and those attributes of R that are not in X+.
3. Use Algorithm to compute the sets of FD’s for R1 and R2, let these be S1 and S2 respectively.
4. Recursively decompose R1 and R2 using this algorithm. Return the union of the results of these decompositions.