[Informatics] DB Normalisation
Wu, Rachel W
wu.rachel.w at edumail.vic.gov.au
Wed Feb 17 10:47:43 AEDT 2016
This is probably the simplest way I've been able to distil the normal form rules and is how I summarise to my students:
1NF: Each cell should contain only a single value and each record must be unique (no duplicates)
2NF: Each field must depend (relate) on the entire primary key
3NF: Each field must depend (relate) on only the primary key and not other non-key fields
Hope it helps!
--
Kind Regards,
Rachel Wu
Information Technology Teacher
Brentwood Secondary College
65-71 Watsons Rd
Glen Waverley, Melbourne
Victoria, 3150 Australia
Phone: (03) 8545 0300 (ext. 323)
Email: wu.rachel.w at edumail.vic.gov.au<mailto:wu.rachel.w at edumail.vic.gov.au>
Web: http://www.brentwood.vic.edu.au<http://www.brentwood.vic.edu.au/>
From: informatics-bounces at edulists.com.au [mailto:informatics-bounces at edulists.com.au] On Behalf Of Garth, Lucas A
Sent: Wednesday, 17 February 2016 9:27 AM
To: Year 12 VCE Informatics Teachers' Mailing List <informatics at edulists.com.au>
Subject: Re: [Informatics] DB Normalisation
Hi Scott
Yes you can have more than one table in 1NF.
1NF strictly speaking ensures:
- All values are atomic (data cannot be broken down further). An example would be storing red, blue in a colour field. You should not have this. Same with containing address fields or full name - both can be broken down further.
- Each field can be uniquely identified by a primary key, whether one key field or composite.
- There are no repeating groups*. An example could be a books table which includes the case of having fields for more than one author. One might be tempted to include author1, author2 but it would not be 1NF.
* This is where you would be correct to split the tables and achieve 1NF.
Here's a copy of a fairly detailed example, but sometimes it's a big example like this that can explain why you need to structure the data by using the 3 forms. Our examples in class that are unduly simplified (and occasionally those given in the exams) can be so trite that it ends up confusing the students.
http://agiledata.org/essays/dataNormalization.html
Lucas
Lucas
From: informatics-bounces at edulists.com.au [mailto:informatics-bounces at edulists.com.au] On Behalf Of McCleary Scott
Sent: Wednesday, 17 February 2016 8:55 AM
To: 'Year 12 VCE Informatics Teachers' Mailing List'
Subject: Re: [Informatics] DB Normalisation
That has been my understanding of it, Eugene. 1NF would have only one table, with possible repeated rows, but identified with a composite or concatenated key. I have seen examples where at the end of 1NF there are already two tables. Your example below would have a teacher table and a subjects table. But that seems like the first step in 2NF.
I know it's just a means to an end, semantics, and probably isn't an issue in real world, but I believe that for SAC one the students have to normalise the data and explain their steps?
I just want to make sure I'm getting them to explain it the right way.
Fundamentally, at the end of 1NF should there still only be one table or is it ok to have more?
From: informatics-bounces at edulists.com.au<mailto:informatics-bounces at edulists.com.au> [mailto:informatics-bounces at edulists.com.au] On Behalf Of Van Der Westhuizen, Eugene Y
Sent: Wednesday, 17 February 2016 8:40 AM
To: Year 12 VCE Informatics Teachers' Mailing List
Subject: Re: [Informatics] DB Normalisation
Sid,
For 1NF you can repeat the row once for each subject and have a composite PK of Teacher ID and Subject. For 2NF you would split these into two tables and have some linking table (of a foreign key)
Teacher ID | Teacher Name| Subjects | Campus
PK PK
T1234 Sid Computing Senior
T1234 Sid Mathematics Senior
T1234 Sid PE Senior
Eugene Van der Westhuizen
From: informatics-bounces at edulists.com.au<mailto:informatics-bounces at edulists.com.au> [mailto:informatics-bounces at edulists.com.au] On Behalf Of S.Bagh
Sent: Wednesday, 17 February 2016 8:13 AM
To: Year 12 VCE Informatics Teachers' Mailing List
Subject: Re: [Informatics] DB Normalisation
Hi all,
My interpretation of 1NF, please correct me if I am wrong
1NF example 1
Field names in table
Teacher ID | Teacher Name | Campus | subject 1 | subject 2 | subject 3 | subject 4
PK
Subject 1...4 may need to go to another table
1NF example 2
Field names in table
Teacher ID | Teacher Name| Subjects | Campus
PK
T1234 Sid Computing Senior
Mathematics
PE
List in subject field can be put into another table
Teacher ID Subjects
FK
T1234 Computing
T1234 Mathematics
T1234 PE
Thanks
Sid
From: informatics-bounces at edulists.com.au<mailto:informatics-bounces at edulists.com.au> [mailto:informatics-bounces at edulists.com.au] On Behalf Of Branson, Steven S
Sent: Tuesday, 16 February 2016 10:04 PM
To: Year 12 VCE Informatics Teachers' Mailing List <informatics at edulists.com.au<mailto:informatics at edulists.com.au>>
Subject: Re: [Informatics] DB Normalisation
Going by the text description it says for 1NF "that there must be no repeating groups in the table". My quick Google check seems to back this up.
Steven Branson
Hallam Senior College
9703 1266
From: informatics-bounces at edulists.com.au<mailto:informatics-bounces at edulists.com.au> [mailto:informatics-bounces at edulists.com.au] On Behalf Of McCleary Scott
Sent: Tuesday, 16 February 2016 9:29 PM
To: 'Year 12 VCE Informatics Teachers' Mailing List' <informatics at edulists.com.au<mailto:informatics at edulists.com.au>>
Subject: Re: [Informatics] DB Normalisation
This is pretty much how I've done it so far. But there seems to be a lot of examples around that actually split the original table, between about point 5 and 6 from your first normal form example below. They end up taking out the repeating fields and create a new table at this stage.
From: informatics-bounces at edulists.com.au<mailto:informatics-bounces at edulists.com.au> [mailto:informatics-bounces at edulists.com.au] On Behalf Of Branson, Steven S
Sent: Tuesday, 16 February 2016 9:19 PM
To: Year 12 VCE Informatics Teachers' Mailing List
Subject: Re: [Informatics] DB Normalisation
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> [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<mailto: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.
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.
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.
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.
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/82a60d6e/attachment-0001.html
More information about the informatics
mailing list