Cheap and Secure Web Hosting Provider : See Now

[Solved]: Functional dependencies with the same key?

, , No Comments
Problem Detail: 

let's consider a table with

carID | hireDate | manufactory | model | custID | custName | outletNo | outletLoc 

I want to evaluate all the functional dependencies to bring in first, second and then third normal form.

  • Functional dependencies

    carID,hireDate -> custID 
  • Partial dependencies

    carID->manufactory, model, outletNo** 
  • Transitive dependencies

    custID->custName outletNo->outletLoc 

Since a car is in a outlet only I have in the partial dependecies this:

carID->manufactory, model, outletNo** 

However this leads to anomalies in insertion (imagine adding a car with no outlet), so should not that be like this?

carID->manufactory, model carID->outletNo 

But isn't this still an normalisation anomaly?

Asked By : revisingcomplexity

Answered By : laurids

I think you're misjudging what an insertion anomaly is. It does make sense for the FD to be

carID -> manufactory, model, outletNo** 

since this is just a way to say that the same carID always has the same manufactory, model and outletNo.

Now, if you leave aside for a moment custId and custName, and you decompose the relation this way:

R1(carID, manufactory, model, outletNo), R2(outletNo, location) 

you see that this is a fine decomposition that meets 3NF and is free from insertion anomalies: infact you just cannot have, for example, the same carID in two distinct outlets, or the same outletNo with different location.

You're right that adding a car with no outletNo would be no good since outletNo is a foreign key to R2, infact most DBs won't allow you to do such a thing. But this is a different story. Not providing values for mandatory fields is just wrong, and that of course doesn't mean the design of the database is not correct.

Best Answer from StackOverflow

Question Source :

3.2K people like this

 Download Related Notes/Documents


Post a Comment

Let us know your responses and feedback