Make an ER Diagram using ERDplus.com

PLEASE USE ERDplus.com AND PLEASE MAKE IT ACCURATE

Project Group 29

Human Resources Department Database

Project Group 29s Human Resources Department database will keep track of Employees, Departments, Insurance Selections, Insurance Options, Employee Roles, Job Descriptions, Training, Emergency Contacts, and Location.

For each employee, we will keep track of a unique employee identification number, date of birth, social security number, first name, middle name, last name, preferred name, phone number, personal email address, company assigned email address, physical address consisting of street address, city, state and zip code, date hired, date terminated, and employment status. Employment status will be shown as Active when date terminated is null and Inactive when there is a date terminated. Years of service can be derived from hire date and current date.

For each department, we will keep track of a unique department identification number, name, manager identification, which must be an active employee id, reports to identification number, which must be another department identification number or null for situations that indicate the top of an organizational chart such as the Board of Directors.

For each insurance option, we will track a unique identification number, description, provider, employee contribution amount, employer contribution amount, start date, and end date.

Each employee must have one or more insurance selection. For each insurance selection, we will track a unique identifier, the employee ID, insurance option id, start date, end date. The most recent insurance provided to the employee will be the one without an end date and should be presented as Current selection. Each time the employee changes an insurance option, an end date will be applied to current record and a new record created with the start date equal to current date.

Each job description will include unique identifier, title, description, last revision date, minimum pay rate, and maximum pay rate.

Because our organization can have employees located in different buildings, cities, or states, our database will include a location entity. Each location will have a unique identifier, descriptive name, street address, city, state, and zip code.

Each employee must have one or more employee role. For each employee role, we will track a unique identifier, start date, end date, employee identification number, department identification number, job description id, location identifier, compensation rate, rate type (hourly, weekly, monthly, annually), compensation status (exempt, non-exempt), and frequency paid (weekly, bi-monthly, monthly). The most recent employee role should be presented as Current role and is identified by the record with a null end date. The employees manager should be derived from the department identified and the current manager of the department. Time in position can be derived from start date and end date, or current date when it is the current role.

Each employee may have no or many training records. For each training record we will keep track of a unique identifier, employee identification number, date of training, name of training, organization that provided the training, cost of training, and expiration date that will be populated for training that must be renewed in the future.

Each employee must have one but can have many emergency contacts. For each emergency contact we will keep track of a unique identification number, employee identification number, name, relationship to employee, phone number, street address, city, state, zip code and date last updated.