r/Database • u/thewayofthewei • 2d 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.

4 Upvotes

83% Upvoted

10 comments sorted by

4

u/Significant_Tune9219 2d ago

The methodical version of the vibes step: start from the business rules, not the column names. Write every FD you can read off the scenario as a sentence (a fitting is identified by these attributes, this attribute determines that one), then close attribute sets under those FDs. For candidate keys: take the set of all attributes, subtract anything that appears on any right-hand side of an FD — what remains must be in every key, so it is your starting guess — then verify by taking closures until each reaches all attributes. Any unused attribute can also be added to a minimal key to hunt for alternate keys. Attribute closures do the arrow-drawing for you: X → A holds exactly when the closure of X contains A.

1

u/thewayofthewei 1d ago

But my issue with this approach is under exam pressure it’s very easy to mistakenly describe the business scenario. For example in this vehicle scenario the most important parts are probably the part being fitted on a certain day, but what if you accidentally understand it as “a mechanic is doing fitting of y part on x day”. Then you’ve immediately screwed yourself right off the bat and will end up with an incorrect solution that wastes time.

1

u/Significant_Tune9219 1d ago

Under time pressure, write the FDs from the wording you were given, not from the story you think they meant. If the prompt says a fitting is of a part on a day, that is the determinant; silently rewriting it as a mechanic doing the fitting changes the question. A quick check: list the nouns the scenario treats as identifiers, and only then ask what each determines. If two readings both fit the text, the safer move is the one that invents fewer entities.

1

u/thewayofthewei 1d ago

I see, this does make sense. The more I do it the more I feel that its 50% business scenario logic based. I do think in the final exam there won't be a descriptive sentence but I'm thinking my professor will at least provide a semi descriptive table name. Thanks!

2

u/LukaGOGO 2d ago

you are doing it backwards, draw the dependency arrows first before guessing any candidate keys. Any column missing from the right side must be in your key.

2

u/thewayofthewei 2d ago

Hmm yes this makes sense. My issue is i'm still incredibly bad at identifying the FDs especially when two attributes can describe one or more. Seems to me you need incredible analytical brainpower to do this in a short span of time. Or maybe I just have 0 analytical braincells lol.

1

u/squadette23 2d 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 2d 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.

1

u/PlayfulGrapefruit498 1d ago

start with candidate keys, then map dependencies step by step

1

u/thewayofthewei 1d ago

I've been told by my professor the exact opposite of this. Do not start with candidate keys as they are naturally determined when your dependencies are drawn.

It seems to me both approaches are valid as long as one suits your way of thinking more.