[Year 12 IT Apps] 3NF: Request for clarification

[Robert Timmer-Arends]

Hello Laurie

Customer table: primary key = ID; no repeating groups > 1NF; SN & FN are both dependent only on primary key > 2NF; neither SN or FN determine each other (ID is the only determinant) > 3NF

Phone table: first, why have the 'record number'? If it is an actual field then do you mean it to be a unique identifier? In other words, an ID field? 
If this is the intention, then you are right: phone number is dependent on the meaningless ID and we have 3NF.
But, that is the same for any ID. Remember that the primary key is the smallest set of columns that uniquely identify a row. The reason we introduce an ID field is that in a table with many columns, the number of columns that make up the primary key could become quite unwieldy. For your Customer table, for example, if you did not have ID, then the primary key would be SN+FN, and those two columns would have to be duplicated into any table you wished to link to the Customer table.
So, for the phone table - assuming it's going to be the 'destination' of a link, why bother with the RecordNo field at all. The primary key would be ID_FK + Phone, and 1NF, 2NF and 3NF are achieved.

I know where you are coming from though - a few months ago I was trying to come up with some simple examples and found that reducing the table to two columns makes things very difficult for 2NF and 3NF. In effect the problems that 2NF and 3NF are trying to fix just disappear so 2NF and 3NF become pointless. The second thing that makes 2NF and 3NF 'disappear' is the use of ID fields because the problems that 2NF and 3NF are trying to fix only crop up when the primary key is made up of two or more columns.

So, examples that are trying to explain 2NF and 3NF need multi-column primary keys and at least one more column than just the primary key.

Regards
Robert T-A


----- Original Message ----- 
  From: Savage, John L 
  To: Year 12 IT Applications Teachers' Mailing List 
  Sent: Monday, November 10, 2014 3:55 PM
  Subject: [Year 12 IT Apps] 3NF: Request for clarification


  I've been reading and re-reading material about 3NF and trying to look at it as an average student (which is easy). My problem is pretty simple and best dealt with by two examples.

   

  First, from Wikipedia is the classic example with which I have no problems, the DOB is uniquely dependent on the key field (Winner).

              Tournament Winners
             
              Tournament
             Year
             Winner
             
              Indiana Invitational
             1998
             Al Fredrickson
             
              Cleveland Open
             1999
             Bob Albertson
             
              Des Moines Masters
             1999
             Al Fredrickson
             
              Indiana Invitational
             1999
             Chip Masterson
             

         

        Here is my problem

              Customer
              
             Phone
             
              ID
             SN
             FN
             RecordNo
             ID_FK
             Phone
             
              SAV01
             Savage
             Laurie
             1
             SAV01
             043123456
             
              SMI05
             Smith
             Sam
             2
             SAV01
             0393062512
             
               
              
              
              
             3
             SMI05
             0438111222
             
               
              
              
              
             4
             SMI05
             0396541782
             
               
              
              
              
             5
             SMI05
             0412345678
             
       Winner Dates of Birth
             
              Winner
             Date of Birth
             
              Chip Masterson
             14 March 1977
             
              Al Fredrickson
             21 July 1975
             
              Bob Albertson
             28 September 1968
             

         
       

   

  These are in 3NF but in the Phones Table the phone number is only UNIQUELY dependant on the meaningless record number, which seems at variance with the "key and nothing but the key" motto OR is the key RecordNo+ID_FK.

   

  I'm definitely missing something in the definition aren't I?

   

  Laurie

   

   



   


  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 Early Childhood Development.



------------------------------------------------------------------------------


  _______________________________________________
  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/20141110/d6f51fbb/attachment-0001.html 
-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: image/png
Size: 16431 bytes
Desc: not available
Url : http://www.edulists.com.au/pipermail/itapps/attachments/20141110/d6f51fbb/attachment-0001.png