Help with database normalisation - 3NF

tincat

Hi all,

Any help with the question in hte last frame of this tutorial would be so appreciated (http://gmitweb.gmit.ie/computing/staff/dcostello/downloads/ca/Normalisation.pdf)

I'm studying for an exam in normalisation and trying to work through examples. I'm starting to get it but still a bit unsure, and this tutorial didn't give the answer.

Shipments (Shipment_ID, origination, destination, distance)

I have to say what normal form this is in, and get it to 3NF

I think it's in UNF (origination and destination could be repeating?)

1NF - remove repeating data
Shipment ID origination destination
Distance shipment_ID (combined key)

2NF - remove partial dependencies
Shipment_ID origination destination (orign & dest both depend on Primary Key PK)
Distance shipment_ID (all attributes make up the PK)

3NF - remove transitive dependencies (NonKey attributes depending on other NKA's)

It looks like the tables in blue are already in 3NF?

I would really appreciate knowing if I'm getting it right. Thanks.

paddyrg

Wow, the textbooks make it all feel so much harder than it really is. I used to do this stuff internally for the big Office and Operating Systems company and I'm struggling with the way the question is put and all the academic terms! The good news is that once you're out in the world, you'll never have to talk about trnasitive dependencies again...

tincat

Thanks for your reply.

I seem to have memorised the theory ok'ish, but when I try and put it into practice on examples, I get confused by what is dependent on what.

It's horrible.

paddyrg

Give me the data and I'll normalise it to whichever form is most appropriate in the circumstances (there are real-world instances where you want denormalised data) by sight - but talk cardinalities and transitive dependencies and I'm no help to you! So sorry!

RobTang

tincat wrote: »

Thanks for your reply.

I seem to have memorised the theory ok'ish, but when I try and put it into practice on examples, I get confused by what is dependent on what.

It's horrible.

Dont be worried those slides are terrible, it is either intentionally confusing or its one of the worst sets of slides on normalisation ive ever seen. If I were you id find somthing else to learn from.

I would argue the shipment example is also a bit ambigous.

Personally id say the implication is that the table is in 2nf and they want you to move distance to a seperate table so

Shipment ([pk]Shipment_id, [fk]origin, [fk]dest)
Distance ([pk]origin, [pk]dest, distance)

Normalisation (to 3nf) is not horrible or difficult, yet somehow the only way people can describe it is in horrible and difficult terms.

tincat

Thanks so much for your reply. After looking at your answer, that makes sense.

I kept thinking that the shipment_id had to repeat in both tables to tie them in, but if the origin and destination are a combined primary key it works.

I just wish I'd been able to see that.


As regards it being in 2NF, please could you explain if at all poss?

As far as I can see, if the table had data in it, there could be multiple shipments from one particular origin, or destination. If repeating data has to be removed, then that is the rule of 1NF?

Or have I got the rule wrong?

I have found much better stuff to learn from, (that is a really good one, but lots of others too). I am just trying to find practice questions and that slideshow had a question to resolve, but with no answer.

The link above was really good at explaining it all, but when I try to work things out, I'm not as intuitive as I want to be.