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 a single location.

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.

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.

Relationship types:

Manages 1:1

Works_For 1:N

Controls 1:N

Supervision 1: N

Workd_on M:N

Dependents_Of 1:N

Employee Is 1:1

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.

One employee must be either full time or part time employee.

One full time or part time employee must be an employee.

Draw an ER diagram with 1) entity types, 2) relationship types, 3) cardinalities, 4) keys and 5) related attributes