Part 1: Atlas Corporate Human Resource Data Requirements

This document describes the needs of the Atlas Corporate Human Resources department in managing the data surrounded its employees and their benefits, including salaries, health insurance, and vacation time time.

This document contains descriptions of only part of the Atlas corporate operations. You are not expected to and should not model any other aspect of Atlas operations other than those described in this document.

While this document provides a complete overview of Atlas corporation needs in terms of managing employee centered data, it does not completely specify all details. For example, this document tells you the following facts: there are 37 employees, including four defined leaderhip employees; employees have only two sorts of dependents, spouses and children; individuals to be contacted in cases of employee work-place emergencies are either spouses, children, parents, or friends; there are three health insurance plans available to employees; and there are four restricted areas which must be managed. These facts must be modeled accurately.

The document does not specify the names of employees or other other information about the employees. The document does not specify the names of job positions, other than the four specified, nor the supervisory/subordinate relationships between employees. The details of the three insurance plans are not specified although the type of information required in your model is. The restricted access rooms are not described, although the information you are required about them is. You must define these aspects of your model yourselves.

This document gives you a framework from which to work, but should allow everyone to create distinct models of Atlas Corporation.

The following sections describes Atlas Corporation data needs Employees:

Atlas has 37 employees currently and wants to develop an information system to help it manage its growing company and personnel. Atlas needs to track some personal identification for each employee aw well as information affected the employees in the workplace.

Personal information kept by the company is limited to information required by the company to identify its employees and provide them their benefits. This includes the employees name, social security number, birthdate, gender, home address, personal phone numbers (used to contact the employee, the employee must have at least one, but may have multiple contact numbers).

Atlas also keeps information that is relevant to the employees work. One is the employee's date of hire, which is used to determine vacation time.

Atlas provides a corporate email address to some employees if required by their job position. Therefore, Atlas also needs to keep track of the job classifications (the type of work the employee does or his/her job title) and which employees work in which job classification. Atlas also provides each employee with an employee ID card. This card is keyed with an employee ID number.

The social security number is uniquely assigned by the US governement to each of the employees. Atlas assigns each employee a unique employee ID number when the employee is hired. The employee number appears on the employees ID card and is encoded in the card's magnetic stip, which allows the employee into some restricted areas of the company, depending on the employees job.

Employees may have dependents (spouse or children). For benefits reasons, Atlas needs to have the following information about benefits: social security number, name, birthday, gender, relationship (spouse, child). As before, the social security number is unique for each dependent.

Each employee must also supply Atlas the name and phone number of a person to contact if there is an accident or other emergency while the employee is at work. Atlas also asks for the relationship of the contact ot the employee (which must be one of the following: spouse, parent, child, or friend). The last piece of information informs Atlas of how much information they can give. You can assume that if two employees have the same contact name with the same number, this is the same person.

But a person can be listed as the emergency contact for more than one employee (the save person could be list as the spouse contact for one employee, a friend contact for another, and parent contact for another, for example).

The leadership of the company is comprised by four individuals, the owner, the human resource director, the information systems director, and the operations director. The last three individuals report directly to the owner (their supervisor). All other employees report either directly or indirectly to one of the last three individuals. Atlas needs to keep track of the supervisory relationships between its employees.

Employe Salary Information:

Atlas prides itself on providing excellent salary and other benefits for its employees and Atlas keeps information to ensure that all employees receive the salary and benefits they deserve.

In terms of salary, as do most companies its size, Atlas has both exempt employees (they have an annual salary and are not eligible for overtime pay) and non-exempt employees (they have an hourly pay rate and are eligible for overtime pay). Non-exempt employees also may be full-time (eligible for employee benefits) or part-time (not elibible for employee benefits). Some job classifications are held by only exempt employees, while other job classifications are only held by non-exempt employees. There are no job classifications which are held by both exempt and non-exempt employees.

In addition to paying the employees, Atlas must also withhold taxes on the amounts that they pay to each employee. Each employee has submitted a W4 tax form with has the number of dependents for that employee. Atlas must keep track of each employee's number of dependents in order to withhold the proper amount of tax.

Employee Benefit Information:

At Atlas, one of the most important employee benefits is health insurance. Atlas offers three separate health insurance plans for it employees to choose from and gives the employees the option to cover just themselves, or also cover their dependents. Employees can also opt out of the health insurance benefit that Atlas offers.

The different health insurance have the following information that describes each health plan: the health plan vendor (the insurance company), the health plan code, the health plan rate for individual coverage, the health plan rate for family coverage, the health plan individual dedecutible, the health plan family deductible, the health plan co-pay, the health plan co-insurance.

A deductible is an amount of money that the employee must pay for certain medical bills before the insurance plan begins paying them. Each member of the employee's plan (the employee and any covered dependents) have an individual deductible. There is also a family deductible; if the sum of individual deductibles exceeds the family deductible, the insurance plan begins paying medical bills for any covered family member even if that member's deductible has not been reached. Deductibles are usually between $500-$4000.

A co-pay is an amount of money that the plan members are required to pay for each doctor visit. Common values for co-pays are between $15-$30.

Co-insurance is expressed as a percentage. It expresses the percentage of a medical bill that must be paid by the employee; the remaining percentage is covered by the employee. For example, if a play has 10% co-insurance, the employee will be required to pay 10% of specific medical bills, while the insurance plan covers the remaining 90% (but only after any deductible is met).

In addition to information on the three health plans that Atlas provides, it must also keep track of which plan, if any, each employee chooses, whether the employee is covering only him- or her- self or dependents, and a list of dependents covered by the plan.

Another benefit is vacation time. Employees must have worked for at least two months to eligible for vacation time. Employees begin with two weeks of vacation time. Employees earn an additional week of vaction every three years from their hire date.

Restricted Company Areas:

The company has four restricted areas that are only accessible by certain employees using their employeeIDcards.TheemployeeIDonthecardauthorizestheroomaccess. Certainjobcategories require access to certain areas. Supervisors of those employees in those job categories also have access to the same rooms. The four company leaders defined earlier have access to all rooms

Each room has a unique room number, a room name, an internal phone number, and an indication of whether it stores hazardous materials.

Part 2: Course Project Goals

The goal of the project is for you to design, develop, and implement a database for Atlas Corporation. Your project will have the following four parts.

Database Modeling Phase:

You will develop an entity-relationship model of Atlas Corporation using the information part 1 of this document.

Relational Database Schema Phase:

Using your entity-relationship model of Atlas Corporation, you will develop a Third Normal Form relational database schema for the Atlas Corporation database.

Relational Database Implementation Phase:

Using your entity-relationship model of Atlas Corporation, you will develop a Postgres SQL script consisting of the statements necessary to build a Postgres database for Atlas Corporation.

Relational Database Population Phase:

You will develop a Postgres SQL script to populate the Atlas Corporation database with the information outlined in Part 1 of this document.

Relational Dabase Testing Phase:

You will develop a script which demonstrates that you can extract necessary knowledge for the Atlas Corporation database. The information in part 1 and part 4 should guide you in your testing phase.

Part3: Course Project Deliverables

The project deliverable will consists three files: a design document file, an SQL file containing the Atlas Corporation database create script, an SQL file containing the Atlas Corporation population script, and an SQL file containing the Atlas Corporation database testing script (a set of select queries that extract information from the Atlas Corporation database).

The design document file will be a PDF file (produced from a word processor not scanned images) containing the Entity-Relationship Diagram (ERD), the relational database schema, and the three SQL scripts as Appendices.

In addition to the ERD and relational schema, your design document should provide a guide to the ERD to relational database development (which entities types and relationship types became which relational database tables, which relationship types were used to develop foreign key constraints).

The ERD must use the UML format described in your textbook and in class.

The relational schema can be presented in the following form:

Table_name( column_1: domain_1, column_2: domain_2, ..., column_n:domain_n)

Primary Key Columns: Alternate Key Columns:

.

.

.

Foreign Key Description: .

.

.

Other constraints:

Your document should identify any functional dependencies involve on the relational tables in order to verify that all tables are in Third Normal Form.

The appendix containing the database creation script should have a short description of constraints were implemented in the Postgres database.

The appendix containing the database population should have a short description of justifying your choice of database as to how it conforms to the information in part 1.

The appendix containing the database testing script should have a short section that presents a question that each of the select queries answers.

Part 4: Querying Information from the Atlas Corporation Database

For the Atlas Corporation database to be useful, it must allow the users to get the information they require in order to answer questions about Atlas corporation employees. These questions range from simple ones to complex and relationship questions to statistical questions.

I will provide some examples of questions about Atlas corporation in below. I will occasionally introduce data values; you do not need to use my data values in your own database.

What is the name of the Atlas Corporation owner?

What are the names of employees who directly reports to the Atlas Corporation human resources director?

What are the names of employees who have access to restricted room Laser Lab?

How many employees have chosen the Acme HMO health plan?

How many dependents does each employee have?

What is the name of employee John Smith's emergency contact?

What is employee John Smith's job description?

What are the restricted rooms to which John Smith has access (if any)?

Which of the health insurance plans has the most employees enrolled?

These are just sample questions. In particular, your database testing script should not be simply