[Year 12 IT Apps] Table Normalisation

Thu Mar 31 15:21:00 EST 2011

OK, for better or worse, a few comments...

Why Normalise?
Two reasons; eliminate redundant data and to ensure data in each table is logically related.

Normalisation process

Create separate fields/tables as necessary to...

1.      eliminate multiple entries in fields (eg Name into Surname and first_name fields)

2.      eliminate duplicate columns

3.      remove columns that are not dependent upon the primary key

Establish relationships by...

4.      ensuring each table has a primary key

5.      using foreign keys as necessary
Plus remember that only data is stored in tables (eg not calculations)
Now where all this fits into 1NF, 2NF and 3NF is different depending on which source you look at.
Paula C, any chance of some guidance here? Is the 1NF, 2NF and 3NF stuff going to be examined?
If so we need some clear guidelines on what actually happens within the three Normal forms? (according to VCAA anyway)

thanks
Mark Scott
Luther College

PS Can't help Laura with an answer to her question, but I will post a normalised yacht club database onto the list when I find the time.

From: itapps-bounces at edulists.com.au [mailto:itapps-bounces at edulists.com.au] On Behalf Of Mark KELLY
Sent: Thursday, 31 March 2011 2:29 PM
To: Year 12 IT Applications Teachers' Mailing List
Subject: Re: [Year 12 IT Apps] Table Normalisation

I looked up normalisation levels 1-3 and had to have a Bex and a good lie down.

It's funny because normally I love discussing things like how NF2 aims to ensure that no non-prime attribute in the table is functionally dependent on a proper subset of a candidate key.

:-o
On 31 March 2011 13:02, Robert Hind <robert at yinnar.com<mailto:robert at yinnar.com>> wrote:
You might like to look at

http://databases.about.com/od/specificproducts/a/normalization.htm

Robert Hind (Semi-retired) OOF, GOM
Ashwood and Traralgon
robert at yinnar.com<mailto:robert at yinnar.com>

----- 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

_______________________________________________
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

--
Mark Kelly
Manager of ICT, Reporting, IT Learning Area
McKinnon Secondary College
McKinnon Rd McKinnon 3204, Victoria, Australia
Direct line / Voicemail: +613 8520 9085, Fax +613 9578 9253
kel at mckinnonsc.vic.edu.au<mailto:kel at mckinnonsc.vic.edu.au>
VCE IT Lecture Notes: http://vceit.com
Moderator: IT Applications Edulist

All generalisations are false, except this one.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://www.edulists.com.au/pipermail/itapps/attachments/20110331/9bbbb754/attachment-0001.html 