Re: Help with basic normalization (revised)

Previous post had some errors -please ignore.

Note where you see – or a similar number, somehow google changes hyphens and single quotes to this. Any ideas on this would be appreciated.

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).

1NF

APPLICANT: Applicant#, applicant-name, applicant-address, applicant-phone KEY = Applicant#

APP-JOB: Applicant#, job-reference#, Date, job-department, interview-date, interview_time KEY = Applicant#, job-reference#,  

2NF

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#

APP-JOB: Applicant#, job-reference#, Date, interview-date, interview_time KEY = Applicant#, job-reference#,

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

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

APPLICANT: Applicant#, applicant-name, applicant-address, applicant-phone KEY = Applicant#

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

INTERVIEWS: job-reference#, Applicant# interview-date, interview_time KEY job-reference#, Applicant#

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

It would be possible to form a new table for applicant-address (as several applicants COULD live at the same address), but this does not seem practical – if a new table was produced, would an additional field be needed to identify the address and link it to the Applicant table?

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 Colin Kinsella

– 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 Tue Nov 20 2001 - 00:02:00 CET