Looking for HW double check on database design.

Question

hey everyone... so i'm working on a database design class for university. I've got the question below and my attempt at the diagram here http://tinypic.com/view.php?pic=httchc&s=3.. would anyone mind taking a look and offering suggestions? thanks for the help!!

QUESTION:

Question 3 The following situation describes a company that would like to implement an information system. The company would like to keep track of its employees, departments, and projects. Suppose that the MIS department of the company did the requirements collection and analysis phase, and gave you a specification report with the following descriptions.

The company is organized into departments, which can have several locations. Each department has a unique name, a unique number, and a manager. The company keeps track of the date when each employee began managing a department.

Each department controls a number of projects, each of which has a unique name, a unique number, and a single location.

The company stores each employee’s name, social insurance number, address, salary, sex, and birth date. Each employee is assigned to only one department, but may work on several projects that are not necessarily controlled by the same department. The company keeps track of the number of hours per week that an employee works on each project. The company also keeps track of each employee’s direct supervisor.

For insurance purposes, the company would also like to keep track of each employee’s dependents. The company wants to record each dependent’s first name, sex, birth date, and relationship to the employee.

Draw an EER Diagram for this situation.

Answer 1

Here's the physical model - I leave it to you or someone else to draw it:

DEPARTMENTS table

• DEPARTMENT_ID (pk)
• DEPARTMENT_NAME (uk)

LOCATIONS table

• LOCATION_ID (pk)
• LOCATION_NAME

DEPT_LOCATIONS_XREF table

• DEPARTMENT_ID (pk, fk)
• LOCATION_ID (pk, fk)

DEPT_MANAGER_XREF table

• DEPARTMENT_ID (pk, fk)
• EMPLOYEE_ID (pk, fk)
• EFFECTIVE_DATE (pk) --this composite key allows for someone to be manager for the same dept 2+ times, as long as it is not starting on the same date.
• EXPIRY_DATE (not null)

PROJECTS table

• PROJECT_ID (pk)
• DEPARTMENT_ID (fk)
• PROJECT_NAME (uk)
• LOCATION_ID (fk)

EMPLOYEES table

• EMPLOYEE_ID (pk)
• DEPARTMENT_ID (fk)
• FIRST_NAME
• MIDDLE_NAME
• LAST_NAME
• SIN
• SALARY
• SEX
• BIRTH_DATE

EMP_PROJECTS_XREF table

• EMPLOYEE_ID (pk, fk)
• PROJECT_ID (pk, fk)

DEPENDENT_RELATIONSHIP_CODES table

• DEPENDENT_RELATIONSHIP_CODE (pk)
• DESCRIPTION

DEPENDENTS table

• DEPENDENT_ID (pk)
• EMPLOYEE_ID (fk)
• FIRST_NAME
• BIRTH_DATE
• SEX
• DEPENDENT_RELATIONSHIP_CODE (fk)

Answer 2

Thanks.. Didn't want the answer but I'll compare it to what I have.

Answer 3

Having a 'Manages' table would be needed for a many-to-many relationship (similar to the 'Resides' table, which is many-to-many). Because each department has only 1 manager, you could instead have a single EmployeeIdOfManager field (and a DateStarted) in the Department table.

I like rexem's naming convention for the tables which model a many-to-many relationship, e.g. 'EMP_PROJECTS_XREF' is better for me than 'InvolvedWith'.

The DirectSupervisor field has to be nullable (e.g. for the top boss).

I believe there shouldn't be a DepartmentId field in the InvolvedWith table.

You should query whether more than one department can exist at the same location. If so then there shouldn't be a DepartmentId field in the Location table, and if not then you don't need a Resides table.