Assignment $(60$ Points$)$

The COMPANY database keeps track of a company$$s employees, departments, and projects. Suppose that after the requirements collection and analysis phase, the database designers provide the following description of the miniworld the part of the company that will be represented in the database.

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 that employee began managing the department. A department may have several locations.

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

We store each employee$$s name, Social Security number, address, salary, sex$($gender$),$ and birth date. 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 current number of hours per week that an employee works on each project. We also keep track of the direct supervisor of each employee $($who is another employee$).$

We want to keep track of the dependents of each employee for insurance purposes. We keep each dependent$$s first name, sex, birth date, and relationship to the employee.

What you need to do this exercise

A Dia file has been created for you to extend and can be found on Blackboard Company.dia represents a completed ER schema which, models some of the information implemented in the system, as a starting point for this exercise.

$1.$ Understanding the ER schema for the Company database. $(20$ Points$)$

To demonstrate that you understand the information represented by the schema, explain using EMPLOYEE, DEPARTMENT, PROJECT and DEPENDENT as examples:

$$ attributes, entities and relationships $(12$ Points$)$

$$ cardinality & participation constraints on relationships $(8$ Points$)$

You don$$t need to explain the entities etc BUT you should explain question a and b using the schema you have been given to more easily explain your answers.

$2.$ Creating and Extending Entity Relationship $($EER$)$ Diagrams. $(40$ Points$)$

To demonstrate you can create entity relationship diagrams extend the ER as described in Company.dia by modelling new requirements as follows:

$$ Create subclasses to extend Employee. The employee type may be distinguished further based on the job type $($SECRETARY$,$ ENGINEER, MANAGER, and TECHNICIAN$)$ and based on the method of pay $($SALARIED$\_$EMPLOYEE, HOURLY$\_$EMPLOYEE$)$

SECRETARY entity type has the specific attribute Typing$\_$speed, whereas the ENGINEER entity type has the specific attribute Eng$\_$type, but SECRETARY and ENGINEER share their other inherited attributes from the EMPLOYEE entity type.

SALARIED$\_$EMPLOYEE entity type has the specific attribute Salary, whereas the HOURLY$\_$EMPLOYEES entity type has the specific attribute Pay$\_$scale. $(28$ Points$)$

$$ Now explain the superclass and subclasses that you have just created. $(8$ Points$)$

$$ Explain the disjointness $($overlap$/$disjoint$)$ and completeness $($partial$/$total$)$ constraints you have made while creating the specialization in question a$.(4$ Points$)$

$$ Save your report as Lab$3-$YourName.docx and your dia files as Lab$3-$YourName.dia