CODEWITHSUNDEEP
databasecomputer-sciencenormalization
devoured elysium
devoured elysium

Reputation: 105097

Question about relation normalization

Let's consider, for instance, the following relation:

R (A,B,C,D,E,F)

where the bold denotes that it is a primary key attribute

with

F = {AB->DE, D->E}

Now, this looks to be in the first normal form. It can't be on the third normal form as I have a transitive dependency and it cannot be in the second form as not all non-key attributes depend on the whole primary key.

So my questions are:

  1. I don't know what to make of F and C. I don't have any functional dependency info on them! F doesn't depend on anything? If that is the case, I can't think of any solution to get R into the 2nd normal form without taking it out!

  2. What about C? C also suffers from the problem of not being referred on the functional dependencies list. What to do about it?

My attempt to get R into the 2nd normal form would be something like:

R(A,B,D)

R' (D,E)

but as stated earlier, I don't have a clue of what to do of C and F. Are they redundant so I simply take them out and the above attempt is all I have to do to get it into the 2nd form (and 3rd!)?

Thanks

Upvotes: 1

Views: 484

Answers (3)

Mike Sherrill 'Cat Recall'
Mike Sherrill 'Cat Recall'

Reputation: 95612

F doesn't depend on anything?

No, you just haven't been given any explicit information about it in the form 

{something -> F}

And essentially the same can be said for C. You're expected to infer the other dependencies by applying Armstrong's axioms. (Probably.)

Think about how to finish this:

Given R (A,B,C,D,E,F)

  • {ABC -> ?}

[Later . . . I see that Jonathan Leffler has broken the suspense, so I'll just finish this.]

{ABC -> DEF} (By definition) therefore,

{ABC -> F} (By decomposition. Here's where F and C come in. And this is your third relation. ).

Upvotes: 0

Jonathan Leffler
Jonathan Leffler

Reputation: 754090

Given the definition of R that { A, B, C } is the primary key, then there is inherently a functional dependency:

  • ABC → ABCDEF

That says that the values of A, B and C inherently determine or control the values of D, E and F as well as the trivial fact that they determine their own values.

You have a few additional dependencies, identified by the set F (which is distinct from the attribute F - the notation is not very felicitous, and could be causing confusion*):

  • AB → DE
  • D → E

As you rightly diagnose, the system is in 1NF (because 1NF really means "it is a table"). It is not in 2NF or 3NF or BCNF etc because of the transitive dependency and because some of the attributes only depend on part of the key.

You are right that you will end up with the following two relations as part of your decomposition:

  • R1(D, E)
  • R2(A, B, D)

You also need the third relation:

  • R3(A, B, C, F)

From these, you can recreate the original relation R using joins. The set of relations { R1, R2, R3 } is a non-loss decomposition of the original relation R.

* If the F identifying the set of subsidiary functional dependencies is intended to be the same as the attribute F, then there is something very weird about the definition of that attribute. I'd need to see sample data for the relation R to have a chance of knowing how to interpret it.

Upvotes: 3

David Božjak
David Božjak

Reputation: 17627

I think the primary key of R is set wrong. If F isn't functionally related to anything it has to be a part of the key

So you have R( ABCF DE) which is now in the first normal form (with F = {AB->DE, D->E}) Now you can change it to the second normal form. DE isn't dependant on the whole key (partial dependency) so you put it in another relation to get to second normal form:

R( ABCF ) F = {}

R1( #AB DE) F = {AB->DE}

Now this relation doesn't have any transitive dependencies so it is already in third normal form.

Upvotes: 0

Related Questions