0
$\begingroup$

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?

$\endgroup$
1
+50
$\begingroup$

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.

$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.