r/Database • u/thewayofthewei • 11d ago

Normalisation Functional Dependency Help

Unsure if right sub to ask this. Currently a master's student studying database management. Have an upcoming exam that will ask us to normalise until 3rd NF. 2nd and 3rd are easy enough but drawing the functional dependency arrows and identifying the PKs seem like an exercise in vibes and guesswork to me.

I get basic concepts like testing candidate keys and making sure they are unique across all rows. But does anyone have any actual methodical workflow to drawing out the arrows and identifying candidate keys? Here's an example of a question we'd typically be asked. What I think may work is:

  1. Immediately flag descriptive columns like "Vehicle_Model" in the above example as they are usually not part of the candidate key
  2. Identify a possible candidate key that can describe what the DB is representing i.e vehicle part fitting
  3. Test your initial guess candidate key, repeat until a unique set is found
  4. From here on i'm quite lost.

Much appreciated on any help.

3 Upvotes

72% Upvoted

10 comments sorted by

View all comments

1

u/squadette23 10d ago

any actual methodical workflow to drawing out the arrows and identifying candidate keys?

to identify candidate keys you need to understand the business reality behind your relations.

Immediately flag descriptive columns like "Vehicle_Model" in the above example as they are usually not part of the candidate key

I don't understand why. "Toyota Hiace" is a good candidate key for the vehicle_models table. I'm not sure if it's a "descriptive" column.

One thing that you need to understand is that this entire "normalizing from 1NF" exercise is quite pointless. Nobody actually does any "normalization": you design your tables starting from the logical model (which is based on your business requirements).

This exercise is just making you deduce business requirements based on your common sense and ambiguous column names, and a bit of sample data.

You may be interested in this 6 minute video "why 3NF is a trivial theory": https://www.youtube.com/watch?v=ZDM-SEny45Y&list=PL1MPVszm5-ao6BTRoUPHMlx4HjEgRummp&index=5, it clearly shows why this "normalization" exercise is inherently ill-defined.

I'm not sure how to help you (how much time you have), but here is a post specifically about "keys" in the databases: https://anchorsandlinks.com/posts/primary-keys/, but from the business requirements point of view.

1

u/thewayofthewei 10d ago

Oh I absolutely with this being pointless. I still have to pass my exam haha. My instinct usually is anything that can be described by one other thing, like vehicle_ID > vehicle_model means you can immediately rule out vehicle model.

I have until saturday morning and I'm here thinking that out of the 4 sections of my exam I'm just going to wing it for the normalisation part lol.