Question

An employment agency needs to convert its basic one-table management system into a modern information management system/database that can hold its improving business. You are hired to create this IMS and the first step you are carrying out is normalization. The table that the agency originally uses is the following:

APPLICATION(ApplicantName, AppicantPhone1, ApplicantPhone2, ApplicantAddress, ApplicantFieldOfInterest, ApplicanHighestLevelOfEducation, EmployerBusinessName, EmployerAddress, EmployerPhoneNumber, EmployerCity, EmployerEmail, JobPostingID, JobPostingTitle, JobYearlySalary, InterviewId, InterviewDate, NamesOfInterviewers, OfferDetailsIfApplicable)

Rules:

- Applicants can be identified by their phone number(s)

- No two employers can have the same name

- Employers can post more than 1 posting

- Applicant can apply for more than one posting

You are required to put the previous table in 4NF (step-by-step: 1NF, 2NF, 3NF and 4NF).

Answer 1

Original table

APPLICATION(

ApplicantName, AppicantPhone1, ApplicantPhone2, ApplicantAddress, ApplicantFieldOfInterest, ApplicanHighestLevelOfEducation,

EmployerBusinessName, EmployerAddress, EmployerPhoneNumber, EmployerCity, EmployerEmail,

JobPostingID, JobPostingTitle, JobYearlySalary,

InterviewId, InterviewDate, NamesOfInterviewers, OfferDetailsIfApplicable)

A) 1NF

In the first normal form, no groups should repeat. So repeating information is seperated into new tables.

1) EMPLOYER(EmployerEmail,EmployerBusinessName, EmployerAddress, EmployerPhoneNumber, EmployerCity)

APPLICANT_DATA1(AppicantPhone1, ApplicantPhone2, EmployerEmail,ApplicantName,ApplicantAddress, ApplicantFieldOfInterest, ApplicanHighestLevelOfEducation,

JobPostingID, JobPostingTitle, JobYearlySalary,

InterviewId, InterviewDate, NamesOfInterviewers, OfferDetailsIfApplicable

)

//HERE AppicantPhone1, ApplicantPhone2, EmployerEmail is the Primary key

3) From APPLICANT_DATA1 we can divide from JobPostingID as one employer can submit more than one posting and this will repeated data

JOBPOSTING(JobPostingID, JobPostingTitle, JobYearlySalary)

APPLICANT_DATA2(AppicantPhone1,EmployerEmail, JobPostingID,ApplicantPhone2, ,,ApplicantName,ApplicantAddress, ApplicantFieldOfInterest, ApplicanHighestLevelOfEducation,InterviewId, InterviewDate, NamesOfInterviewers, OfferDetailsIfApplicable)

4) From APPLICANT_DATA2 we can divide tables on InterviewId

INTERVIEW(InterviewId, InterviewDate, NamesOfInterviewers, OfferDetailsIfApplicable)

APPLICANTS(AppicantPhone1,EmployerEmail,JobPostingID,InterviewId, ApplicantPhone2, ,ApplicantName,ApplicantAddress, ApplicantFieldOfInterest, ApplicanHighestLevelOfEducation)

At the end of 1NF we get:

EMPLOYER(EmployerEmail,EmployerBusinessName, EmployerAddress, EmployerPhoneNumber, EmployerCity)

JOBPOSTING(JobPostingID, JobPostingTitle, JobYearlySalary)

INTERVIEW(InterviewId, InterviewDate, NamesOfInterviewers, OfferDetailsIfApplicable)

APPLICANTS(AppicantPhone1,EmployerEmail,JobPostingID,InterviewId,ApplicantPhone2, ,ApplicantName,ApplicantAddress, ApplicantFieldOfInterest, ApplicanHighestLevelOfEducation)

B) 2NF: We need to make sure that non-key attributes are dependent on a single Primary key

The table APPLICANTS can be divided as

APPLICANT_INFO(AppicantPhone1, ApplicantPhone2,ApplicantName,ApplicantAddress, ApplicantFieldOfInterest, ApplicanHighestLevelOfEducation)

EMPLOY(EmployerEmail,JobPostingID,InterviewId,ApplicantPhone1,ApplicantPhone2,ApplicantName)

At the end of 2NF we have the following:

EMPLOYER(EmployerEmail,EmployerBusinessName, EmployerAddress, EmployerPhoneNumber, EmployerCity)

JOBPOSTING(JobPostingID, JobPostingTitle, JobYearlySalary)

INTERVIEW(InterviewId, InterviewDate, NamesOfInterviewers, OfferDetailsIfApplicable)

EMPLOY(EmployerEmail,JobPostingID,InterviewId,ApplicantPhone1,ApplicantPhone2,ApplicantName)

APPLICANT_INFO(AppicantPhone1, ApplicantPhone2,ApplicantName,ApplicantAddress, ApplicantFieldOfInterest, ApplicanHighestLevelOfEducation)

C) 3NF: Non-key attributes cannot be dependent on other non-key attributes

EMPLOYMENT_DATA(EmployerEmail,JobPostingID,InterviewId,ApplicantName)

APPLIANT(ApplicantPhone1,ApplicantPhone2,ApplicantName)

At the end of 3NF we have:

EMPLOYER(EmployerEmail,EmployerBusinessName, EmployerAddress, EmployerPhoneNumber, EmployerCity)

JOBPOSTING(JobPostingID, JobPostingTitle, JobYearlySalary)

INTERVIEW(InterviewId, InterviewDate, NamesOfInterviewers, OfferDetailsIfApplicable)

APPLIANT(ApplicantPhone1,ApplicantPhone2,ApplicantName)

EMPLOYMENT_DATA(EmployerEmail,JobPostingID,InterviewId,ApplicantName)