In the previous post I showed the method I use to determine the candidate keys and highest normal form of a relation. Now let’s break these large relations into smaller ones to make sure we achieve Boyce-Codd Normal Form (BCNF)!
Normalizing a relation, step by step:
- Begin with your candidate key. If you have many CK:s, choose one them. This will be your first relation. Underline all attributes in the CK to indicate the key.
- If there are stand-alone boxes, your first relation must contain only the CK, and no other attributes (example 2). Otherwise, your first relation can include additional attributes (example 1).
- Now follow the arrows leading away from the current box. For each arrow, add this destination box (attribute) to the current relation. The the source box will be the key of the relation, and the destination box(es) non-key attributes.
- Normally you only step one box ahead at a time and then start a new relation. But if an arrow between two boxes is double ( <-> ) you include the next box too in the same relation (B<->C in example 1).
- When dealing with double arrows, both attributes may be set as keys. In example 1, the grayed out “or” relations symbolize this, because it allows you to split your relations in different ways. Both of these ways are valid.
- Continue to the next box(es) and repeat the above steps. When you have covered all boxes, you should have a fully BCNF normalized set of relations!
Let’s do some examples:
|
1 2 3 4 |
R(A,B,C,D) A → B B → C C → B,D |
Example 1
As you can see, I like to cover the boxes I have drawn with my hand to make it easier to see what to focus on. In the pictures, the black boxes show what to focus on; the gray boxes can be ignored.
So, with your hand cover the arrow(s) pointing away from the last box you are working on. What you see is the boxes to include in the current relation. Now look at the arrow pointing towards the last box(es). Double arrow indicate if you are to move your hand one step further, still in the same relation. Single arrow indicate that you should close the relation and start a new one.
One final example:
|
1 2 3 4 5 6 |
R(A,B,C,D,E,F,G,H) {A,B} → C C → D D → C D → {E,F} F → G |
Example 2
Normalizing Example 2
Good luck!
Pingback: Method for determining candidate keys and highest normal form of a relation based on functional dependencies | RLV Blog
Please help me to understand all the terminologies that are required to study Schema Refinement . I want to give confident answers in exam , but can’t understand the bookish language . please help !!!!!
If you want to give a confident answer, then you probably need to learn the terminology in the books… :-)
The terms I’ve written in my two posts are what I learned in the books. If you go with these you are probably ok. Not sure how else I can help you.
R={A,B,C,D,E,G,H,I,J,K}
i had reduced the FD’s to minimal FD
Fm= {A→H, G→A, E→D, D→G, E→H, E→I, AB→C, AB→E, AB→K, CD→K}
is it possible to apply your method to a more complicate situation and convert to bcnf
I tried to do it but its confusing.
can you show me how its done.
thanks in advance
What if the dependencies are like this A –> BC,CD –>E, B–>D, E–>A ? How do you draw something like that? The BC and CD is the difficult part