ASSIGNMENT 1 SOLUTION GUIDE Company Database Requirements: We list the data requirements for the database here, and then create its conceptual schema step-by-step as we introduce the modeling concepts of the ER model. 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 miniworldthe 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. The database will 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. It is required to keep track of the current number of hours per week that an employee works on each project, as well as the direct supervisor of each employee (who is another employee). . The database will keep track of the dependents of each employee for insurance purposes, including each dependent's first name, sex, birth date, and relationship to the employee. Initial Conceptual Design of the COMPANY Database 1 We can now define the entity types for the COMPANY database, based on the requirements described in Section 3.2. After defining several entity types and their attributes here, we refine our design in Section 3.4 after we introduce the concept of a relationship. According to the requirements listed in Section 3.2, we can identify four entity typesone corresponding to each of the four items in the specification (see Figure 3.8): 1. An entity type DEPARTMENT with attributes Name, Number, Locations, Manager, and Manager_start_date. Locations is the only multivalued attribute. We can specify that both Name and Number are (separate) key attributes because each was specified to be unique. 2. An entity type PROJECT with attributes Name, Number, Location, and Controlling_department. Both Name and Number are (separate) key attributes. 3. An entity type EMPLOYEE with attributes Name, Ssn, Sex, Address, Salary, Birth_date, Department, and Supervisor. Both Name and Address may be composite attributes; however, this was not specified in the requirements. We must go back to the users to see if any of them will refer to the individual components of Name-First_name, Middle_initial, Last_nameor of Address. In our example, Name is modeled as a composite attribute, whereas Address is not, presumably after consultation with the users. 4. An entity type DEPENDENT with attributes Employee, Dependent_name, Sex, Birth_date, and Relationship (to the employee). II Name Number Locations DEPARTMENT Manager Manager_start_date Name Number Location PROJECT Controlling_department Fname Minit Lname Project Hours Name Sex Salary Works_on Ssn Department Supervisor EMPLOYEE Birth_date Address Birth_date Sex Employee Relationship Dependent_name DEPENDENT Figure 3.8 Preliminary design of entity types for the COMPANY database. Some of the shown attributes will be refined into relationships