[Year 12 IT Apps] Table Normalisation

[Timmer-Arends]

Hello Laura

I think there is not enough info to give definitive answers:

ticking 'First', one could say that nothing would happen. Reason: from the 3 records given there are no repeating groups - for example two boats for one name - so the table already is in 1NF.
However, one could argue that there appears to be no unique identifier for each record - there could be two Jon Rosses - so a primary key field needs to be added.

ticking 'Second', one could again say 'nothing'. Reason: We don't know what the primary key is, so, if we assume that 1NF adds a primary key field then the table is in 2NF already, so we don't do anything.  Basically 2NF doesn't allow a non-primary key column to be dependent on only part of the primary key. And from the info given there is no way to tell whether this is happening or not. So, if  we supposed that Name and memType formed the primary key (why I don't know but let's go with it), then 2NF would cause the three fees columns to disappear because they are presumably dependent only on memType; that is, they are not dependent on the complete primary key.

ticking 'Third', this time we can be more certain: the three fees columns would disappear (moved to create another table with memType as their primary key). Reason: the fees are dependent on memType (at least judging by the info given; eg there does not seem to be any dependence on Gender) and memType is presumably not a primary key in the given table. Therefore the fees belong in their own table (the problem is that if, for example, the club changed its fee structure then, with the table given, Sue might have her storage fee changed to the new amount, but Harry's might be overlooked - the db has lost integrity. 3NF prevents this.)

The distinction between 2NF and 3NF is this: 2NF requires non-key columns to be dependent on the whole key, while 3NF requires non-key columns to de dependent only on the key and nothing else


Regards
Robert T-A
Brighton SC


----- Original Message ----- 
  From: Laura Fihelly 
  To: Year 12 IT Applications Teachers' Mailing List 
  Sent: Thursday, March 31, 2011 10:59 AM
  Subject: [Year 12 IT Apps] Table Normalisation


  Hi everyone,

   

  Can someone please help me with this Sample Exam question. I am trying to understand table normalization. I have a general understanding of what it means, but I'm not really sure about the actual differences in this particular question between the second and third normal forms. As a class we have been going through the textbook definition, but I'm struggling to apply it to this particular sample question. 

   

  Also if anyone has some good resources on a simple (not so technical way) of how to explain this easier it would be greatly appreciated.

   

  Thanks

  Question 1

  Below is a table from a yacht club's relational database.

        Name
       Boat Name
       Address_Suburb
       State
       memType
       Fees $
       Gender
       Boat Storage Fees $
       Total Fees Due $
       
        Sue Trowsdale
       Suzy
       21 Mountain Rd Rosebud
       VIC
       Senior
       250.00
       F
       125.00
       375.00
       
        Jon Ross
       Wahoo
       64 View Rd McCrae
       VIC
       Junior
       140.00
       M
       -
       140.00
       
        Harry Wilson
       Victor
       35 Shady Ln Rosebud
       VIC
       Life
       -
       M
       125.00
       125.00
       

   

  2 marks

  a.     Describe what would happen when you apply one of the First, Second or Third Normal Forms to data in the table. Refer to the table in your answer. 

   

  Tick the box to indicate which normal form you are describing. 

     First o   or       Second  o     or         Third o 

  ____________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________

  ____________________________________________________________________________________

   

  Laura Fihelly

  Teacher of Business Management & Information Technologies

  Catholic Regional College Sydenham

  380 Sydenham Road 

  Sydenham 3037

  email: fihl at crcsydenham.net

  web: www.crcsydenham.net   

   



------------------------------------------------------------------------------


  _______________________________________________
  http://www.edulists.com.au - FAQ, resources, subscribe, unsubscribe
  IT Applications Mailing List kindly supported by
  http://www.vcaa.vic.edu.au/vce/studies/infotech/itapplications3-4.html - Victorian Curriculum and Assessment Authority and
  http://www.vitta.org.au  - VITTA Victorian Information Technology Teachers Association Inc
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://www.edulists.com.au/pipermail/itapps/attachments/20110331/193c0b3a/attachment-0001.html