Normalization

Note: I forgot to remove weight and description from shipment_item in 2NF. They shouldn't be there in both 2NF and 3NF.

[@emmanueldjambatambashe701]

i am sooo disgusted how TH-cam doesn't promote your account , a concept which i have learned in more than a week and still not understand but with simples videos of less than 12 min. i understood everything. thank you sir for sharing your knowledge. i whish you can do more

[@deepeshkc]

Thanks a ton Prof!

[@user-oc1fe4dv3n]

Thank you so much

[@krisztiantamasi1493]

Thank you so much!

[@fouz1245]

Thanks a lot, as a beginner to this concept why is Customer ID not the Primary key?

[@trygvemb]

What program did you use to draw those tables in. It looked like a super helpfull tool to get an overview when working with databases

[@DustinOrmond]

It is my own custom built application which isn't publicly available yet.

[@apollyon25]

I don't understand why you would add captain_Id back into the shipment entity. Wouldnt that violate 3NF? Because ship_id determines captain_id. You could get that information by joining shipment with the ship relation and then ship with the captain relation.

[@DustinOrmond]

Because we have previously identified it as a transitive functional dependency. With multiple transitive functional dependencies, each one needs to be satisfied to be able to correctly connect the foreign keys to the primary keys.

[@apollyon25]

@@DustinOrmond I'm still confused, so ship_id no longer determines captain_Id? Why is that still not a transitive dependency? What do you mean by satisfied? Separated into a different entity? Why isn't it correctly connected without including captain_id?

[@apollyon25]

@@DustinOrmond Thank you for taking the time to respond btw, and so quickly no less. I appreciate it!

[@DustinOrmond]

@@apollyon25 ship_id would still determine captain_id. Have you watched this video on transitive functional dependencies where we discuss all the transitive dependencies covered here? th-cam.com/video/y7z62tW9MT0/w-d-xo.html

[@apollyon25]

@@DustinOrmond Yes I did, that's exactly why I'm asking. Shipment_Id determines ship and captain id, while the ship_id determines the captain_Id. That's a transitive dependency unless I'm missing something.

[@STFUZReadTY]

Thank you so much for this video, can i ask regarding the 3NF relational model is it ok if i split the description into another table with the price?

[@DustinOrmond]

I don't really see the need to do that. The attributes of price, description, and finish are all associated with the product. In other words, these are characteristics that describe the product.

[@tamysiby]

Thank you so much for this! By the way, can you tell us the diagram tool that you're using? Is it online?

[@DustinOrmond]

It is my own custom made tool. It is not publicly available...yet.

[@kaspergos]

@@DustinOrmond Will it be available soon? Looks like it is very usefull!

[@DustinOrmond]

@@kaspergos I am not sure at this moment.

[@user-ow8il4lh2p]

hi i was wondering if the result at the end match the logic model? if im doing a logical model should i just add captain id into the shipment entity thnx

[@DustinOrmond]

The way I have it at the end is what you should do. You should have captain_id in both SHIP and SHIPMENT referencing captain_id in CAPTAIN.

[@henriktunedal8075]

I watched the video on transitive dependencies and read your other reply about this, but I still don't understand how the last step (adding back captain_id to SHIPMENT) makes sense. If shipment_id uniquely identifies a captain_id (because "shipment_id -> ..., ship_id, ship_name, captain_id, captain_name") then that's no longer true in the final normalized form: looking up shipment_id now leads to two different copies of captain_id with potentially inconsistent values, so a particular shipment_id could e.g. identify captain_id 1 directly and captain_id 2 through ship_id, meaning that shipment_id no longer uniquely identifies a captain_id. What am I missing here?

[@DustinOrmond]

We aren't adding it "back". It already existed there but because of the two transitive dependencies, it appears we removed it with one and should add it back to the other. However, this is to satisfy the rule of each transitive functional dependency separately. Yes, you could just join the three tables together, but we were just evaluating based on the transitive functional dependencies alone. Also, this allows us to get captain information without needing shipment information.
Ultimately, this is just following the rules of transitive functional dependencies. You may argue that you wouldn't need this connection for the future and it might be fine to leave it out.

[@henriktunedal8075]

​@Dustin Ormond Thank you! I get it now: The video demonstrates the result of strictly normalizing to 3NF which results in the field being kept. Setting 3NF aside though, it does look like a kind of denormalization to keep two mutable storage locations for a single piece of data (captain_id) and thus creating a risk of inconsistencies (but perhaps also better performance because of simpler lookups, as you suggest). Perhaps the copy is removed in higher normalization forms?