E-R Diagram 03: Temporary Employment Company

Updated 2020.03.25

Temporary Employment Corporation (TEC) places temporary workers in companies during peak periods. TECís manager gives you the following description of the business:

         TEC has a file of candidates who are willing to work. TEC keeps track of each candidate's contact information.

         Each candidate can have multiple qualifications, but must have at least one.

         If the candidate has worked before, that candidate has a specific job history, possibly consisting of multiple jobs. A job history is a list of openings which the candidate has filled. Note that not all candidates have necessarily worked before. Also note that a "job history" is NOT an entity.

         TEC has a list of companies that request temporary employees and TEC keeps basic contact information for each company (company name, address info, phone, contact person). Each company has made at least one request for a temporary employee.

         Each time a company requests a temporary employee, TEC makes an entry in the Openings folder. This folder contains an opening number (unique), company name requesting the employee, required qualification(s), starting date, anticipated ending date, and hourly pay.

         Each opening can require multiple qualifications.

         It is possible that there are certain qualifications that have never been requested by a company (imagine a book with a list of standard codes for qualifications).

         When a candidate matches the qualifications and is given the job, an entry is made in the Placement Record folder. This folder contains an opening number, candidate number, total hours worked, all related information for the placement.

         No more than one employee ever fills a given job opening. (If the employee quits, and the employer still needs somebody, that is considered a new opening.)

         It is possible that an opening has not been filled.

         TEC uses special codes to describe a candidateís qualifications for an opening. A partial list of codes is:

Code

Description

SEC-45

Secretarial work, at least 45 words per minute

SEC-60

Secretarial work, at least 60 words per minute

CLERK

General clerical work

PRG-VB

Visual Basic programmer

PRG-Java

Java programmer

DBA-ORA

Database administrator, Oracle

DBA-DB2

Database administrator, DB2

SYS-1

Systems Analyst, level 1

SYS-2

Systems Analyst, level 2

NW

Network administrator

 

TECís management wants to be able to:

         Contact a company.

         Contact a candidate/employee.

         Keep track of a candidate's qualifications (e.g. list all of the qualifications for a specific candidate or list all of the candidates who have a specific qualification).

         Keep track of a candidate's job history (the openings he has filled).

         Keep track of a company's job history (the openings that the company has had).

         Look up qualification codes (see table above).

         Look up current and past openings.

 

Do the following:

         Identify each entity class.

         Identify all attributes for each entity class.

         Identify all determinants. Note that this may require you to re-think your entity classes.

         Identify a key for each entity class that will uniquely identify a single entity. Note that the key may be more than a single field. Underline the key field.

         Identify each foreign key (not all entity classes will have a foreign key). Make foreign keys italic.

         For each entity class, identify relationships between it and other entity classes.

         Draw the ER diagram using MySQL Workbench.