[Year 12 IT Apps] Table Normalisation

[Mark Scott]

Well I'm sure that clears it up for everybody?

:-)

Mark Scott
Luther College

PS Paula, we need help with this one.

________________________________
From: itapps-bounces at edulists.com.au [itapps-bounces at edulists.com.au] On Behalf Of Timmer-Arends [timmer at melbpc.org.au]
Sent: Thursday, March 31, 2011 10:21 PM
To: Year 12 IT Applications Teachers' Mailing List
Subject: Re: [Year 12 IT Apps] Table Normalisation

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<mailto:fihl at crcsydenham.net>
To: Year 12 IT Applications Teachers' Mailing List<mailto:itapps at edulists.com.au>
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 •   or       Second  •     or         Third •

____________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________

____________________________________________________________________________________

Laura Fihelly
Teacher of Business Management & Information Technologies
Catholic Regional College Sydenham
380 Sydenham Road
Sydenham 3037
email: fihl at crcsydenham.net<mailto:fihl at crcsydenham.net>
web: www.crcsydenham.net<http://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/20110401/6f69b4a2/attachment-0001.html