Answer to Problem 3.3

To put this in 4NF, the first step is to put it in 3NF. Since manufacturer depends solely on car, we should put this information in a separate relation. So, now we have

car —> manufacturer
car, color, engine, stripe, roof

This is now in 3NF. None of the attributes in the second relation (just above) depend on any combination of the other attributes, etc.

The next step is to find some multi-valued determinancies. We know that car (F) multi-determines engine (M); that is F —» M.

  1. It doesn't appear that any other attributes in the relation depend on the combination of car and engine. Thus, there is no D and there is no K.
  2. Create another relation car, engine. Since there is no D, there is nothing on the right side of the arrow — so the arrow is not shown.
  3. Delete engine from the original relation. Now we have

car —> manufacturer
car —» engine
car, color, stripe, roof

We also know car (F) multi-determines color (M).

  1. The next step is to determine if any attributes in the relation are multi-determined by the combination of car and color. It appears that stripe and roof (K) are multi-determined by car, color.
  2. Move car (F), color (M), and stripe and roof (K) to another relation: car, color, stripe, roof.
  3. Delete stripe and roof (K) from the original relation.
  4. There are no attributes that are determined by car (F) and color (M).
  5. Move car and color to another relation; however, these are the only two attributes left in this relation, so this doesn't have any effect. The primary identifier is car, color. So, the result is that

car —> manufacturer
car —» engine
car —» color
car, color, stripe, roof

However, remember that we know that stripe and roof are both multi-determined by car and color. This means that the last relation (just above) should be

[car, color] —» stripe | roof

So, going through the whole procedure for [car, color] —» stripe | roof, we end up replacing this relation with

[car, color] —» stripe
[car, color] —» roof

So, the result is that we have these five tables.

Result of putting car info into 4NF
Car table
Car Manufacturer
Mustang Ford
Cirrus Chrysler
Engine info table
Car Engine
Mustang F3.2L
Mustang F4.5L
Cirrus C2.1L
Cirrus C2.1L
Car color table
Car Color
Mustang Red
Mustang White
Mustang Blue
Cirrus Red
Cirrus Green
Car stripe table
Car Color Stripe
Mustang Red white
Mustang Red blue
Mustang White red
Mustang White black
Mustang Blue white
Mustang Blue yellow
Cirrus Red white
Cirrus Red black
Cirrus Green white
Cirrus Green black
Car roof table
Car Color Roof
Mustang Red black
Mustang Red white
Mustang White white
Mustang White red
Mustang Blue black
Mustang Blue white
Cirrus Red black
Cirrus Red blue
Cirrus Green brown
Cirrus Green white

This answer exhibits the standard structure of a correction of a 4NF problem. After taking out the dependency of manufacturer on car, we are left with two pairs of 4NF multi-dependencies: engine & color on car and stripe & roof on [car, color]. Each of these pairs contributed two new relations to the answer so we ended up with five relations: one for the dependency, two for one pair of multi-dependencies, and two pair for the other.

Now, before you go crazy, obsessing over this problem, note the following: This was a difficult problem. You should understand what went on but you will probably not come across this kind of problem very often (if at all) in your daily routine at your job.

Return to 4th normal form.

Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-NonCommercial-ShareAlike 3.0 License