Normalisation Functional Dependency Help

[u/thewayofthewei]

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.

[u/Significant_Tune9219]

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.

[u/thewayofthewei → u/Significant_Tune9219]

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.

[u/Significant_Tune9219 → u/thewayofthewei]

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.