[Informatics] DB Normalisation

Wed Feb 17 08:55:03 AEDT 2016

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

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.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://www.edulists.com.au/pipermail/informatics/attachments/20160216/0cbaa39d/attachment-0001.html 