You are designing and creating a database named COMPANY for an HR (Human Resource) Dept of a Software Consulting Company to manage their employees and their assigned work (projects) in the company. First Step to Design a Database Scheme (table structures) and Create a Database is Creating an E-R Diagram by Identifying Entities, Attributes and Relationships between any two Entities. In this lab, Create an E-R Diagram for the Company database from the raw data files given below and the business rules of the company given below using the symbols of E-R Diagram. To create the E-R Diagram for the Company database, we identified 4 Entities in class as below. Entities: Employee Department Dependent - Weak Entity Project . For each Entity: 1. Identify all the attributes for each Entity 2. Identify the Attribute Type of each attribute per Entity Multi-Valued Attribute, Composite Attributes, Key attribute: If there are more than one key attribute, mark all as a possible key attribute. If there is no key attribute in an Entity, identify multiple attributes as a composite key attribute. Derived Attributes. . 3. With the given raw data at the end of this lab sheet and the given Compamy rules as specified below, Identify a relationship between any two Entities to create an E-R Diagram for the Company database scheme. Note that there could be more than one relationship between two entities. For example, between Employee and Department, there are more than one relationship. There could be no relationship between some pairs of two entities. Note that it is decided here that we don't create a separate entity for Supervisor role and Manager role separately as an Entity. The Supervisor role and the Manager role are combined as one integrated Employee Entity. Company Rules: . . . Every employee should work for one department. A Dept could have no employee. Every employee should work on at least one project or more. Every project should have at least one employee or more that work(s) on A Project can be worked by multiple employees A Manager is the one who manages a Department. Every Department can have only one manager. A Supervisor is the one who supervises his/her regular employees. A department controlls projects. A project can be controlled by only one department. A dependent should have only one employee that he/she belongs to. . . . For each realtionship, identify the followings: Name the realtionship Identify the Cardinality of each relationship between two entities as either 1-1 1-N or N-1 N-M Identify participant types: Total or Partial Participation for each entity that participates in a relationship If there is no clear indication either from raw data or the rules for participation info of a relationship, leave it as partial participation as default Example of a Relationship with Cardinality between Employee and Departmemnt in E-R diagram Cardinality Ratio (1:1, 1:N, M:N) since name dname 1:1 Each dept has at most one manager on Manages ssn lot did budget Employees Manages Departments C. Translation to relational model ? 1-to Many Many co-1 Many-to-Many Company EMPLOYEE NAME SSN BDATE ADDRESS SEX SALARY SUPERSSN M 30000 987654321 John B Smith Franklin T Wong 123456789 333445555 09-Jan-55 731 Fondren, Houston, TX 08-Dec-45 638 Voss, Houston, TX 31-Jul-62 5631 Rice, Houston, TX M 40000 888665555 Joyce A English 453453453 F 25000 333445555 Ramesh K Narayan 666884444 M 38000 333445555 888665555 M 55000 F 43000 15-Sep-52 975 Fire Oak, Humble, TX 10-Nov-27 450 Stone, Houston, TX 20-Jun-31 291 Berry, Bellaire, TX 29-Mar-59 980 Dallas, Houston, TX 19-Jul-58 3321 Castle, Spring, TX James E Borg Jennifer S Wallace Ahmad V Jabbar Alicia ) Zelaya 888665555 987654321 987987987 M 25000 987654321 999887777 F 25000 987654321 DEPARTMENT DNAME DNUMBER Headquarters 1 Administration 4 Research 5 MGRSSN 888665555 987654321 333445555 MGRSTARTDATE DLOCATION 19-Jun-71 Houston 01-Jan-85 Stafford 22-May-78 Bellaire, Sugarland, Houston 06-Oct-05 Cleveland Automation 7 123456789 DEPENDENT ESSN DEPENDENT NAME 123456789 Alice 123456789 Elizabeth 123456789 Michael 333445555 Alice 333445555 Joy 333445555 Theodore 987654321 Abner SEX F F M F F M M BDATE 31-Dec-78 05-May-57 01-Jan-78 05-Apr-76 03-May-48 25-Oct-73 29-Feb-32 RELATIONSHIP Daughter Spouse Son Daughter Spouse Son Spouse PROJECT PNAME ProductX Product Productz Computerization Reorganization Newbenefits PNUMBER 1 2 3 10 20 30 PLOCATION Bellaire Sugarland Houston Stafford Houston Stafford DNUM 5 5 5 4 1 4