"Normalizing a Relation to BCNF Based on Functional Dependencies"

This article uses functional dependencies to transform a set of relations to 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!


Source: http://www.rlvision.com/blog/method-for-normalizing-a-relation-to-bcnf-based-on-functional-dependencies/
Creative Commons License This work is licensed under a Creative Commons Attribution 4.0 License.

Last modified: Monday, July 22, 2019, 2:02 PM