How to identify the corrects step to complete 3NF?

Question

This is an example from a textbook: Consider the relation R (A ,B ,C ,D ,E ) with FD’s AB -> C, C -> B, and A -> D.

We get that the key is ABE and ACE. With decompositions: ABE+=ACE+=ABCDE.

How do you check minimality? I know that AB+=ABD and the textbook says that because AB+ does not include C. Then it is minimal. C+=AB and A+=AD are also minimal. But I do not know why. How do you check minimality?

Also, do we have to find all the FD's besides the ones given to check whether to perform 3-NF or not?

We then check if AB -> C can be split into A -> C and B -> C, we notice that these do not stand on their own so AB -> C is not splittable.

We are left with the final relations: S1(ABC), S2(BC), S3(AD) and the key (since not present) S4(ABE) (or S4(ABC)). We then remove S2 because it's a subset of S1.

If it is in 3NF and there are no violations, then why do they split the original relation into: S1(A, B, C), S2(A, D), and S4(A, B, E).

Book name and page: Ullman's Database Systems page 103

Answer 1

How do you check minimality?

The authors don't use the word minimality here. To check for the minimal basis, follow the procedure in the first two paragraphs of example 3.27. It boils down to

• ". . . verify that we cannot eliminate any of the given dependencies."
• ". . . verify that we cannot eliminate any attributes from a left side."

Also, do we have to find all the FD's besides the ones given to check whether to perform 3-NF or not?

That question doesn't really make sense. 3NF isn't something you perform. The example in the textbook has to do with the synthesis algorithm for 3NF schemas. The synthesis algorithm decomposes a relation R into relations that are all in at least 3NF.

The synthesis algorithm operates on the FDs you've been given. In an academic setting, as you might find in a textbook, the assumption is that you've been given enough information to solve the problem. In real-world applications, you might be given a set of FDs from a business analyst. Don't assume the analyst has given you enough information; look for more FDs.

We then check if AB -> C can be split into A -> C and B -> C, we notice that these do not stand on their own so AB -> C is not splittable.

No. You verify (not notice) that you can't eliminate any attributes from a left side. Eliminating A leaves B->C; eliminating B leaves A->C. Neither of these are implied by the three original FDs. So you can't eliminate any attributes from a left side.

If [the original relation] is in 3NF and there are no violations . . .

The original relation is not in 3NF. It's not even in 2NF. (A->D)