ER Diagram 03:

Temporary Employment Company

TEC solution

Determinants

CompanyID Company name, address, city, state, zip, phone, contact first name, contact last name

CompanyID OpeningID //Company can have many openings

CandidateID Last name, first name, address, city, state, zip, phone

// Candidate can fill many openings, can have many qualifications

CandidateID OpeningID, Qualification

// A standard list of codes and descriptions

QualificationCode Description

// Opening

OpeningID CompanyID, StartingDate, EndingDate, HourlyPay

OpeningID QualificationCodes

// Filled opening

OpeningID CandidateID, total hours worked

Fixing multivalued determinants

CompanyID, OpeningID

CandidateID, OpeningID

CandidateID, Qualification

OpeningID QualificationCode

Keys

COMPANY: CompanyID

CANDIDATE: CandidateID

JOB_OPENING: OpeningID

QUALIFICATION: QualificationCode

Entity classes:

Company, Candidate, Job opening, Qualification

Entity attributes:

COMPANY: CompanyID, CompanyName, Address, City, State, Zip, Phone, ContactFirstName, ContactLastName

CANDIDATE: CandidateID, LastName, FirstName, Address, City, State, Zip, Phone

JOB_OPENING: OpeningID, CompanyID, StartingDate, EndingDate, HourlyPay, CandidateID

QUALIFICATION: QualificationCode, Description

Relationships:

The notation below is a poor man's ER diagam. The > and < represent crow's feet.

A company can have many openings (and must have at least 1, or it's not in our database), and an opening has exactly 1 company (and it is required).

COMPANY |-|-------------------- |-< JOB_OPENING

An opening can require many qualifications, but must have at least 1. A qualification can be associated with many openings, but does not necessarily have to be associated with any specific opening.

JOB_OPENING >-0------------------ |-< QUALIFICATION

A candidate can have many qualifications (and must have at least 1). A qualification can be associated with many candidates, but does not necessarily have to be associated with any specific candidate.

CANDIDATE >-0----------------| < QUALIFICATION

We do not have to keep track of which jobs a candidate applied for, only the ones for which he was placed. The following reflects placements, not jobs for which an applicant applied or interviewed for. Looking at the right side below, a candidate can fill many jobs (over time), but does not necessarily have to have ever filled a job. Looking at the left side, a job opening can be filled by one candidate, but it may not be filled at all.

CANDIDATE |-0------------------0-< JOB_OPENING

Things to note:

         We have two N:M relationships. These will be handled by creating a new INTERSECTION TABLE using the keys from the two entities in the relationship.

Updated 2020.03.25