course; hence if there are several ways to normalize the above data it
should be on a strict theoretical basis, to 3rd normal form.
I understand that the inner and outer brackets on the Applicant Record
above are some clue to the first split – 1st Normal form.
We have been given no additional information than above. We are to
make our own assumptions and clarify them.
My assumptions:
APPLICANT-RECORD: (Applicant#, applicant-name, applicant-address,
applicant-phone, date, (job-reference#, job-department,
interview-date, interview_time)) Key = Applicant#
As part of a larger assignment we know that the scenario is; A very
large company with several departments who have 1 or more vacancies
within each department. Applicants will apply for these jobs. There
are likely to be 60 successful job appointments per day (on average).
This is part of a Systems Analysis course – not a database
course; hence if there are several ways to normalize the above data it
should be on a strict theoretical basis, to 3rd normal form.
I understand that the inner and outer brackets on the Applicant Record
above are some clue to the first split – 1st Normal form.
We have been given no additional information than above. We are to
make our own assumptions and clarify them.
MY ASSUMPTIONS
Applicants may apply for more than 1 job.
Several applicants may be interviewed at the same time (with different
interview panels).
Several people who live in the same house could apply for the same job
– or different jobs
The ‘applicant-phone’ attribute is to be used for the most
appropriate tel number ie landline or mobile (hence 2 people living at
the same address could have different contact numbers).
A parent and child with the same name, living at the same address will
not both apply for any job.
The “date” entity, I assume is the date of the job
application, hence an applicant could apply for several vacancies and
have a date for each application. Thus I feel the
“”hint” to keep it with the applicants contact
information is wrong. In my attempted answer I have included this
entity with the job-reference#
My attempted answer.
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 (always required).
APPLICANT: Applicant#, applicant-name, applicant-address,
applicant-phone,
Key = Applicant#
JOB: Date, Applicant#, job-reference#, job-department,
interview-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,
Key = Applicant#
INTERVIEW: Date, Applicant#, job-reference#, interview-date,
interview_time
Composite Key = Applicant# & job-reference#
DEPARTMENT: job-reference#, job-department
Key = job-reference#
3nd 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
APPLICANT: Applicant#, applicant-name, applicant-phone,
Key = Applicant#
ADDRESS: Applicant#, applicant-address
Key = Applicant#
If I have normalized ADDRESS correctly I am a little confused about
the benefits of this in a real database, as with more than 1 applicant
from the same address, the address would still need to be entered once
for each applicant – I think
APP-INTERVIEW: Applicant#, interview-date, interview_time
Key = Applicant#
