[Year 12 IT Apps] Data Normalisation

Fri Apr 27 10:40:50 EST 2012

Hello Benn

> Did I need to include ProductID at all as this was something else that I 
> also decided to include to uniquely identify each book.
>I could probably just use the Books Name as the Primary Key could I not, as 
>each book is Unique?

In a real db probably the book name would not be a good primary key if only 
because we might consider, for example, paperbacks and hardcover versions of 
the same title as different products. So the productID is a good idea.

Your final tables look fine. All I'm pointing out is that with the 1NF you 
have drawn up you have italicised 'custID' presumably to indicate it is the 
primary key. But for 1NF in this table 'custID' by itself will not uniquely 
identify a record therefore it is not 1NF. To be 1NF, the primary needs to 
include a unique identifier for the product, which could be 'productID' or 
just 'book name' as you suggest.

1NF,2NF and 3NF are not just about the structure of the tables but also 
about their primary keys.

Regards
Robert T-A

----- Original Message ----- 
From: "Benn Pollock" <bennp86 at bigpond.com>
To: <itapps at edulists.com.au>
Sent: Thursday, April 26, 2012 6:55 PM
Subject: Re: [Year 12 IT Apps] Data Normalisation

>I did make an error that I noticed after I sent it, in the 3NF section 
>there
> is only meant to be ProdID, Book, Cost - not Item. The intention of adding 
> a
> CustID field was to add a Primary Key to the table, as it has to be 
> Unique,
> each CustID number relates to each individual customer - that was its
> intention anyway. My 1NF was simply just to split up data such as Name and
> Address into separate columns, which is suggested to do. Did I need to
> include ProductID at all as this was something else that I also decided to
> include to uniquely identify each book. I could probably just use the 
> Books
> Name as the Primary Key could I not, as each book is Unique? I understand
> the concept of Normalising data but I'm unsure where I've actually gone
> wrong here? I know in 3NF all fields must directly relate back to the
> Primary Key - I wasn't sure however if "Date of Purchase" was actually
> required in the table I've called "Orders"...
>
> The intention of this data from looking at it would be to have Customers
> details stored in a table ("Customers").
> A list of all the books for sale and their price ("Products").
> And a table to fill in for orders ("Orders").
>
> What step/s have I done wrong/missed out?
>
>
> -----Original Message-----
> From: itapps-bounces at edulists.com.au 
> [mailto:itapps-bounces at edulists.com.au]
> On Behalf Of itapps-request at edulists.com.au
> Sent: Thursday, 26 April 2012 6:11 PM
> To: itapps at edulists.com.au
> Subject: itapps Digest, Vol 86, Issue 32
>
> Send itapps mailing list submissions to
> itapps at edulists.com.au
>
> To subscribe or unsubscribe via the World Wide Web, visit
> http://www.edulists.com.au/mailman/listinfo/itapps
> or, via email, send a message with subject or body 'help' to
> itapps-request at edulists.com.au
>
> You can reach the person managing the list at
> itapps-owner at edulists.com.au
>
> When replying, please edit your Subject line so it is more specific than
> "Re: Contents of itapps digest..."
>
>
> Today's Topics:
>
>   1. Data Normalisation (POLLOCK, Benn(POL))
>   2. Re: Data Normalisation (Robert Timmer-Arends)
>
>
> ----------------------------------------------------------------------
>
> Message: 1
> Date: Thu, 26 Apr 2012 14:56:22 +1000
> From: "POLLOCK, Benn(POL)" <POL at wantirnacollege.vic.edu.au>
> Subject: [Year 12 IT Apps] Data Normalisation
> To: <itapps at edulists.com.au>
> Message-ID:
>
> <29963D9395C11C428DA4E6DA31456638032EA4C0 at want4.wantirnacollege.vic.edu.au>
>
> Content-Type: text/plain; charset="us-ascii"
>
> Covering Data Normlisation at the moment. First time I've attempted
> this/taught this so I would appreciate any feedback you can give. I've
> attached an excel document with some Data that I gave to the students to
> practice with, and I've just had a go at it myself - could someone tell me
> if It's correct? Going by everything I've read and other examples, it 
> looks
> right to me but I could be wrong! Appreciate any feedback.
>
>
>
> Benn Pollock
>
> Information Technology Teacher
>
> Wantirna College
>
> Ph: 9801 9700
>
>
>
>
>
> -------------- next part --------------
> An HTML attachment was scrubbed...
> URL:
> http://www.edulists.com.au/pipermail/itapps/attachments/20120426/da3b807a/at
> tachment-0001.html
> -------------- next part --------------
> A non-text attachment was scrubbed...
> Name: Normalisation Attempt.xlsx
> Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
> Size: 16680 bytes
> Desc: Normalisation Attempt.xlsx
> Url :
> http://www.edulists.com.au/pipermail/itapps/attachments/20120426/da3b807a/No
> rmalisationAttempt-0001.xlsx
>
> ------------------------------
>
> Message: 2
> Date: Thu, 26 Apr 2012 18:10:25 +1000
> From: "Robert Timmer-Arends" <timmer at westnet.com.au>
> Subject: Re: [Year 12 IT Apps] Data Normalisation
> To: "Year 12 IT Applications Teachers' Mailing List"
> <itapps at edulists.com.au>
> Message-ID: <1A7F11EB17E74DC9A6F84F53B37567CA at timmerarends>
> Content-Type: text/plain; charset="iso-8859-1"
>
> Hello Benn
>
> I'm not sure about your 2NF and 3NF because I'm not sure what 'cost', 
> 'item
> cost' and 'item' are in relation to the original field 'item cost'.
> However, as far as I can tell, except for one thing your first set of data
> is already in 1NF because no record has a repeating group; eg two book
> titles in the 'books purchased' field, or two equivalent fields such as
> 'books purchased 1' and 'books purchased 2'.
>
> What is missing is a primary key that uniquely identifies each record. As
> far as I can tell this would mean the primary key should be: 'name' and
> 'book purchased'
>
> In your 1NF you have split fields which, for this example, is probably a
> good idea, but not necessary to form 1NF; and you have added a 'cust id'
> field presumably as a primary key, but as pointed out above this will not
> uniquely identify a record, unless the intention is that 'cust id' is 
> unique
> for each book purchased by each customer. I suspect that this is not
> intended so 'book' should also be italicised, indicating that it forms 
> part
> of the primary key.
>
> Once set up like this, one recognises that it is not in 2NF because there
> are fields that are dependent only on part of the primary key, thereby
> leading to the split tables as you have done.
>
> Regards
> Robert T-A
>
>
> ----- Original Message -----
>  From: POLLOCK, Benn(POL)
>  To: itapps at edulists.com.au
>  Sent: Thursday, April 26, 2012 2:56 PM
>  Subject: [Year 12 IT Apps] Data Normalisation
>
>
>  Covering Data Normlisation at the moment. First time I've attempted
> this/taught this so I would appreciate any feedback you can give. I've
> attached an excel document with some Data that I gave to the students  to
> practice with, and I've just had a go at it myself - could someone tell me
> if It's correct? Going by everything I've read and other examples, it 
> looks
> right to me but I could be wrong! Appreciate any feedback.
>
>
>
>  Benn Pollock
>
>  Information Technology Teacher
>
>  Wantirna College
>
>  Ph: 9801 9700
>
>
>
>
>
>
>
> ----------------------------------------------------------------------------
> --
>
>
>  _______________________________________________
>  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 <br>
>  http://www.vitta.org.au  - VITTA Victorian Information Technology 
> Teachers
> Association Inc <br>
>  http://www.swinburne.edu.au/ict/schools - Swinburne University
> -------------- next part --------------
> An HTML attachment was scrubbed...
> URL:
> http://www.edulists.com.au/pipermail/itapps/attachments/20120426/1f302f05/at
> tachment.html
>
> ------------------------------
>
> _______________________________________________
> itapps mailing list
> itapps at edulists.com.au
> http://www.edulists.com.au/mailman/listinfo/itapps
>
>
> End of itapps Digest, Vol 86, Issue 32
> **************************************
>
> _______________________________________________
> 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 <br>
> http://www.vitta.org.au  - VITTA Victorian Information Technology Teachers 
> Association Inc <br>
> http://www.swinburne.edu.au/ict/schools - Swinburne University
> 