Help with basic normalization

As part of a CIS course I need to normalize some date. I have made an attempt but would appreciate comments on my accuracy and the assumptions I have made.

Details below:

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:

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#

APP-JOB: Date, Applicant#, job-reference#, Key = Applicant#

DEPARTMENT: job-reference#, job-department Key = job-reference#

I would appreciate comments on my attempts at 1st, 2nd and 3rd normal form

And the appropriateness of the assumptions I have made (or not made).

Thanks In anticipation - Colin Kinsella Received on Sat Nov 17 2001 - 10:18:19 CST