Database Scenario

The COMPANY database keeps track of a company's employees, departments, and projects

The company is organized into departments. Each department has a unique name, a unique number, and a particular employee who manages the department. We keep track of the start date when the employee began managing the department. A department may have several locations.

A department controls a number of projects. Each project has a unique name, a unique number and multiple locations.

We store each employee's name, social security number, address, salary, sex and birthday. An employee is assigned to one department but may work on several projects, which are not necessarily controlled by the same department. We keep track of the number of hours per week that an employee works on each project. We also keep track of the direct supervisor of each employee. An employee must be either full-time or part-time.

We want to keep track of the dependents of each employee for insurance purpose. We keep each dependent's name, sex, birthday and relationship to the employees.

Manages 1:1 Works_For 1:N

Controls 1:N Supervision 1: N

Workd_on M:N Dependents_Of 1:N

Business Rules

One employee must work for one and only one department.

One department must have 4 or more employees.

One employee may manage one and only one department.

One department must be managed by one and only employee.

One department may control one or more projects.

One project must be controlled by one and only one department.

One employee must work on one or many projects.

One project must have one or many employees to work on.

One employee may have one or more dependents.

One dependent must belong to one and only one employee.

One employee may supervise many other employees.

One employee may be supervised by another employee.

Part time employee must be an employee.

Full time employee must be an employee.

Assignment requirements:

1. Draw an ER/EER diagram for your conceptual design by showing keys and attributes of entity types, relationship types, cardinality and participation. (USE VISIO OR LUCIDCHART)

2. Transfer the ER/EER diagram to a relation database and show all the tables in the relational schema.

3. Set up all the referential integrity constraints in the relational schema after the transformation.

PLEASE COMPLETE THE REQUIREMENTS!!!!