DATABASE DESIGN CLASS

1. Draw an ER diagram for the following Company database (make sure you show all the cardinalities and participation ratios):

We need to store the SSN, name, birth_date, address, salary and sex of each employee. The company will need to know the employees first, middle and last names. The SSN will be unique for each employee.

We need to store the name, number and location of each department. A department may be located in more than one location. The department number and name will be unique for each department.

We need to store information about projects controlled by departments. Each project will have a number, name and location. Name and number will be unique for each project.

An employee may have a dependent, but a dependent must belong to an employee. A dependent cannot exist if the employee does not exist. For each dependent we will store the name, sex, birth date and relationship.

Additional information:

All employees must work for a department. One employee can only work for one department. Each department must have an employee working for it and can have more than one employee working for it.

An employee may manage a department. A department must be managed by an employee. The Start_date of the manager is dependent on the Employee as well as the department.

A department may control many projects. A project must be controlled by one department.

An employee must work on a project and can work on more than one project at a time. A project must have at least one employee working for it and may have many employees working for it. The number of hours that an employee works depends on the project.

2Map the ER diagram labeled

cStree cCity (cstate czip lastName phone addr FirstName email loginName Customer Info password a Month oDay OYear itemColor order price sad gtyIn Stock date item Size line Items Inventory Item OrderCartInfo type total Price order Cartrd qty0rdered has Instock itemNum picture ItemType name prn Ce belongsTo Category description categoryId