Question

You will need draw a normalized entity relationship diagram (ERD) for the case described below. You only need to show your final ERD in 3NF.

Case The North Carolina Division of Employment Security wants to build a system that tracks job seeking activity for unemployed individuals. When each individual applies for unemployment (referred to as an applicant) he/she is assigned a job coach who is responsible for monitoring the applicant’s job seeking activity. The development team has tentatively identified the following attributes for each applicant:

APPLICATION (This is the initial entity name. You must properly identify all other entities.)

Applicant ID

Applicant Last Name

Applicant First Name

Applicant Birthdate

Applicant Age

Job Coach Name

Job Coach Phone

Positions Applied For (repeat 1-n times)

Position Code

Position Description

Date Position became available

Number of days position open

Answer 1

Under the guidance of the given scenario, i have created four table, first one is an employee table which store the information about the employees.Second one is a coach table in which it store information about coach through which he/she will assigned job coach easily. Third one is Application table which consist information of applicant table and last or fourth one is job_coach table which is keeping the record of job related information.Besides this, i have also fulfill the 3NF features to keep record consistence and main purpose is to avoid the redundant data.

Note: I have drawn the entity relationship diagram by using the online tools that is draw.io. So, i am attaching the entity relationship diagram images as my answer of the provided question.