1

I'm a student currently attending a database design course and I'm having some issues normalizing 'abstract' attribute relation schemas from 1NF all the way to 3NF.

When we used examples with 'explicit' values in the lecture I didn't have issues normalizing those relation schemas, but as soon as it gets 'abstract' I just can't do it.

Given the following relation schema (R, a) with a = {A, B, C, D, E, G, H, I} and functional dependencies FDs = {GH → A, AH → BC, CHI → B, B → C, A → B, CE → D, BC → H, D → A, AG → B, H → I}. I first reduced the left and right sides of the functional dependencies to find the minimal cover M = {GH → A, CH → B, B → C, A → B, CE → D, B → H, D → A, AG → B, H → I}.

In the lecture we agreed that this sort of 'abstract' relation schema would already be in 1NF.

I chose G, E and D as my key as G and E can't be determined by any other attribute and D seemed good as it determines A which then determines all other attributes.

I came to the following solution for the normalization to 2NF.

Solution

  • {D, A, B, C, H, I}
  • {G, H, A}
  • {E, C, D}

I'm pretty sure my solution is incorrect. I'm not even really sure where to start and I haven't started normalizing to 3NF as I think it would be good to at least have a correct 2NF to start from. Any help is much appreciated.

Where do I start to normalize the given relation schema to 2NF and 3NF?

  • 2
    I think you should start by finding all candidate keys, don't just choose one. – ypercubeᵀᴹ Jun 18 '18 at 20:07
  • Isn't G, E and D already a candidate key as I can't remove one of the three without loosing the ability to determine all other attributes? – Rodrigo Ehlers Jun 18 '18 at 20:10
  • Yes, GED is a candidate key. But isn't GEA as well? And GEH too? (and possibly others)? – ypercubeᵀᴹ Jun 18 '18 at 23:35
  • 2
    The definition of 2NF requires you know which attribute are prime and which not and what are all the candidate keys: "a relation is in 2NF if it is in 1NF and no non-prime attribute is dependent on any proper subset of any candidate key of the relation." – ypercubeᵀᴹ Jun 19 '18 at 9:38
  • 2
    I echo the comment from @ypercube. 2NF and 3NF are defined in terms of candidate keys, not just the primary key. In fact, the choice of which candidate key will become the primary key is a judgement call on the part of the designer. That call doesn't change how normalized the scema is. – Walter Mitty Jun 20 '18 at 17:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.