atabase Design Using ER/EER Data Model

Suppose that you find a job in the Human Resources Department of The DreamCompany. You are responsible for building a database to store various information about the employees, departments, and so on. In this part of the project, you will complete a conceptual design using ER/EER data model for a much simplifed HR database based on the following set of requirements:

The employees work in departments for some projects.

An employee can work on multiple projects that may be participated by several departments.

Any employee can only be either a salariedEmp or an hourlyEmp but not both. An employee is uniquely identified by a ssn. We should record dob(date of birth), name, and address of the employee. Name is composed of Fname, Minit, and Lname.

A salariedEmp has a monthly salary and an hourlyEmp has a rate of hourPay.

An employee may have dependents (described by name, and relationship). Dependents of different employees may have the same names.

Each department, identified by deptNum, has deptName and one or more locations. The numEmp (number of employees) can be regarded as a calculated attribute of the department.

A department has a manager who is also a salaried employee.

A project is identified by projName and projNum together. It has a description (projDesc).

Please draw a ER/EER diagram

Specify the domains of each attribute listed by relations. If you have special considerations such as Nullness and uniqueness, please point out.

Add semantic checks. SSN must be exactly nine digits, and hourPay must be at least $7.50.

Convert your ER/EER diagram into relational tables.

Specify the entity constraints (by underlining) and referential integrity constraints (by FK-PK relationship arrows).