The company is organized into departments. Each department has a name, a number, and an employee who manages the department. We keep track of the start date when that employee started managing the department. We store each employees name, social security number, address, salary, sex, and birth date. Employees are classified into managers, technicians, engineers, or secretaries based on their function. We also keep track of the direct supervision of each employee. Employees may have dependents. We want to keep track of the dependents of each employee for benefits purposes. We maintain each dependents name, sex, birth date, and relationship to the employee. We keep track of each project by number, name and location. Each project may engage several employees. An employee many manage several projects, but each project has only one manager. An employee is assigned to one department but may work on several projects. The department for which the employee works does not necessarily control the projects that the employee works on. We keep track of the number of hours per week that an employee works on each project. Each project may engage several employees. An employee may manage several projects, but each project has only one manager. Each project uses a certain number parts supplied by suppliers. Each part may be supplied by several suppliers (different suppliers may supply the same parts) and each supplier may supply many parts. We keep track of each supplier with supplier number, supplier name, and city (location). Parts are also kept track of using part number, part name, color, and weight. Each part may consist of several other parts. For any unspecified requirements, add your appropriate assumptions to make the specifications complete.

Assignment: 1. Based on the set of requirements identified, design the Information-Level Design of the database using DBDL and Entity-Relationship (ER) diagrams. Your conceptual design of the database should include the following:

a. Entities and Attributes

b. Relationships

c. Keys

d. Structural constraints (cardinality ratio and dependency constraints)

2. Transform the DBDL and ER schema of database you get from step 1 into the corresponding relational database. Specifically:

a. Specify all the key attributes of relations and any referential integrity constraints.

b. Specify the data item format for each attribute in each relation (table).

c. Specify all the functional dependencies you could infer from the requirements.

3. Normalize the relation schemas that you obtain in step 2 (if necessary) so that each relation is in 3NF.