The follwoing is the result of 2 students work on a Normalization
assignment, with a difference of opinion / uncertainty with 3NF.
We would appreciate your comments.
If you would prefer to see this in Word - please email me.
Thanks in anticipation - Colin Kinsella
Assumptions and comments at end (*= Key Field ie Star at front of
field and underlined), both * and underlined are used for readability
in both Word and basic email.
Applicant Record (Applicant#, applicant-name, applicant-address,
applicant-phone, date (Job-reference#, job-title, job-department,
interview-date, interview-time))
1st normal form
Remove Multiple Attributes (Fields) to new tables. Set Composite Key
where non key attributes (Fields) could not be uniquely identified by
a single Key (virtually always required).
APPLICANT: ( *Applicant#, applicant-name, applicant-address,
applicant-phone, Date)
JOB_APPLICATION: ( *Applicant#, *job-reference#, Job-title,
Job-Department, inteview-date, interview_time)
2nd normal form
All Attributes (Fields) which are not part of the Key, must be fully
functionally dependent on all of the Key.
Remove Attributes (Fields) which are dependent on one part of the Key
only
APPLICANT: ( *Applicant#, applicant-name, applicant-address,
applicant-phone, Date)
JOB_APPLICATION: ( *Applicant#, *Job-reference#, interview-date,
interview-time)
JOB ( *Job-reference#, job-title, job-department)
3rd normal form
All attributes (fields) must be fully functional on the whole of the
Key. Remove Attributes (Fields) which are dependent on another
attribute which is not the Key
3NF OPTION1 (no change from 2NF)
APPLICANT: ( *Applicant#, applicant-name, applicant-address,
applicant-phone, date)
JOB_APPLICATION: ( *Applicant#, *Job-reference#, interview-date,
interview-time)
JOB ( *Job-reference#, job-title, job-department)
One of my fellow students has produced option 1 for 3NF but has not
given any clear reasons.
3NF OPTION2
APPLICANT: ( *Applicant#, applicant-name, applicant-address,
applicant-phone, date)
JOB_APPLICATION: ( *Applicant#, Job-reference#,)
INTERVIEW ( *Applicant#, interview-date, interview-time)
JOB ( *Job-reference#, job-title, job-department)
My Option 2 for 3NF is based on the fact that Interview Time is
dependant on Interview Date for any given Applicant therefore
applicant number is sufficient to identify the date/time of interview.
BUT is this not really a result of the 2NF rule. In database terms
this will also mean that Applicant# in table INTERVIEW will have
duplicates (as each Applicant may have more than 1 interview.
I am inclined to go with my fellow students suggestion ie the table is
already in 3NF at the 2NF stage. But I would be surprised if our
assignment would have such a result.
Whatever option we choose we need to give clear reasons why we have
chosen it.
I would appreciate your comments on our Normalizations attempt and our
assumptions.
KNOWN FACTS
This is a large company who will have many interviews on the same day
and same time.
Each applicant may apply for several jobs
ASSUMPTIONS
“date” field is not defined – as it is inside the
first set of brackets we will keep it with applicant and presume it is
date of first application or date of birth.
Several applicants could apply from the same address but they could
have different phone numbers eg mobile
Several applicants with the same name could apply but they will not
live in the same house
Logically each applicant can only have 1 interview at one time but
could have more than one on the same day
Several Departments could use the same job title eg typist, admin
assistant
