[Informatics] DB Normalisation

[Branson, Steven S]

This is the way I presented to my kids. It is based on what I taught several years ago when I last had an ITApps class. Chances are you won't end up with separate tables at 1NF, but you will after that. I'm happy for anyone to contradict me on this.
First Normal Form

*         Convert data to correct format for each column- eg you can't have kg and g in the same field. Select one and stick to it

*         There should be no fields with data from a calculation- eg a field for total price is not needed as it is created from a calculation using data from other fields

*         No duplicated fields- eg phone1, phone2 should just be in Phone field under separate records

*         Break fields into component parts where necessary- eg address should be split into address (number and street), suburb, postcode and state

*         Records in each field should only contain a single value- eg subject field should only contain a single subject, if there are more subjects for the same record then they are placed in separate record

*         Identify key fields, including primary keys, from provided data

Second Normal Form

*         Each column of data should relate to the primary key, otherwise you need to create a new tables with new key fields

Third Normal Form

*         No duplicated data- eg repeated data in different records should be

*         Any new tables will need to be linked by a key field


Steven Branson
Hallam Senior College
9703 1266

From: informatics-bounces at edulists.com.au [mailto:informatics-bounces at edulists.com.au] On Behalf Of McCleary Scott
Sent: Tuesday, 16 February 2016 9:01 PM
To: 'informatics at edulists.com.au' <informatics at edulists.com.au>
Subject: [Informatics] DB Normalisation

Good evening everyone,

Just trying to get my head around the normalisation process and comparing what the text book says (well, the online version of chapter 1) with examples found on various websites.

The book has First Normal Form (1NF) as being a single table with a (possibly) concatenated primary key.  This is on page 31.  Most of the examples I have seen on the web seem to have split their original table at this stage if it has repeating groups.  See https://www.youtube.com/watch?v=x9BuWCUQawY as an example.

Is it ok to split tables and have multiples at this stage of the normalisation process or should we only have one table at 1NF?  Don't want to present contradictory info to my students is all.

Cheers,

Scott McCleary
John Paul College
This email and any attachments may be confidential. You must not disclose or use the information in this email if you are not the intended recipient. If you have received this email in error, please notify us immediately and delete the email and all copies. The College does not guarantee that this email is virus or error free. The attached files are provided and may only be used on the basis that the user assumes all responsibility for any loss, damage or consequence resulting directly or indirectly from the use of the attached files, whether caused by the negligence of the sender or not. The content and opinions in this email are not necessarily those of the College.

Important - This email and any attachments may be confidential. If received in error, please contact us and delete all copies. Before opening or using attachments check them for viruses and defects. Regardless of any loss, damage or consequence, whether caused by the negligence of the sender or not, resulting directly or indirectly from the use of any attached files our liability is limited to resupplying any affected attachments. Any representations or opinions expressed are those of the individual sender, and not necessarily those of the Department of Education and Training.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://www.edulists.com.au/pipermail/informatics/attachments/20160216/1d952222/attachment-0001.html