[Informatics] DB Normalisation

[Mark]

Yep, Sid. That's it in a nutshell...

1NF = no duplicate fields, one value per field, no duplicate records.

On 17 February 2016 at 08:12, S.Bagh <S.Bagh at stmonicas-epping.com> wrote:

> 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] *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>
> *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
> <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>
> *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
> <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
> <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.
>
> 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.
>
> _______________________________________________
> http://www.edulists.com.au - FAQ, resources, subscribe, unsubscribe
> VCE Informatics Mailing List kindly supported by
> http://www.vcaa.vic.edu.au/vce/studies/infotech/itapplications3-4.html -
> Victorian Curriculum and Assessment Authority <br>
> http://www.vitta.org.au  - VITTA Victorian Information Technology
> Teachers Association Inc <br>
> http://www.swinburne.edu.au/ict/schools - Swinburne University
>



-- 

Mark Kelly

mark at vceit.com
http://vceit.com
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://www.edulists.com.au/pipermail/informatics/attachments/20160217/c2bdc471/attachment-0001.html