Question

New to DB's Please help!

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 and Create a Database is Creating an E-R Diagram by Identifying Entities, Attributes and Relationships between any two Entities to Create a Company Database Scheme. 1. 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 For each Entity given below: 2. Identify all the attributes for each Entity Identify the Attribute Types: Key attributes, Multi-Valued Attributes, Composite Attributes, and Derived Attributes. For any two entities and the given Compamy rules below: 4. Identify all relationships between 4 Entities below to create E-R Model for the Company database scheme Entities: Employee Department Dependent Project Company Rules: Every employee should work for one department. Every employees should work on the projects. A Manager is the one who manages a Department Every Department can have only one manager. Supervisor is the one who supervises his/her employees A department controlls projects A project can be controlled by only one department. Some departments are located in multiple office locations For each realtionship you identified Name the realtionship Identify the cardinality of the relationship between two entities as either 1 -1 1 - N or N N-M Identify participant types: Total or Partial Participation for each entity 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 lot budget did most one manager on ssn Manages Employees Manages Departments Translation to relational model? 1-to Many Many-to-1 Many-to-Many 1-to-1 Company EMPLOYEE NAME SSN BDATE SALARY ADDRESS SEX SUPERSSN John B Smith 123456789 09-Jan-55 731 Fondren, Houston, TX 30000 987654321 M Franklin T Wong 638 Voss, Houston, TX M 40000 333445555 08-Dec-45 888665555 Joyce A English 31-Jul-62 5631 Rice, Houston, TX 25000 453453453 333445555 Ramesh K Narayan 666884444 975 Fire Oak, Humble, TX M 38000 333445555 15-Sep-52 James E Borg 888665555 10-Nov-27 450 Stone, Houston, TX 55000 Jennifer S Wallace 20-Jun-31 291 Berry, Bellaire, TX 43000 888665555 987654321 Ahmad V Jabbar 980 Dallas, Houston, TX 987987987 29-Mar-59 M 25000 987654321 Alicia J Zelaya 999887777 19-Jul-58 3321 Castle, SPring, TX 25000 987654321 DEPARTMENT MGRSTARTDATE DLOCATION DNAME DNUMBER MGRSSN Headquarters 888665555 19-Jun-71 1 Houston |Stafford Administration 4 987654321 01-Jan-85 Research Bellaire, 5 333445555 22-May-78 Sugarland, Houston Cleveland Automation 7 123456789 06-Oct-05 DEPENDENT ESSN 123456789 123456789 DEPENDENT NAME Alice Elizabeth Michael Alices Joy Thecdore SEX BDATE 31-Dec-78 05-May-57 01 Jan-78 RELATIONSHIP Daughter Spouse F M Son 123456789 333445555 06-Apr-76 Daughter Spcuse Son 03-May-48 25-Oct-73 333445555 333445555 987654321 M Abner 29-Feb-32 Spouse PROJECT PNUMBER PNAME PLOCATION Belaire Sugarand DNUM ProductX ProductY Productz Computerization Reorganization Newbenetits Houston Stafford Houston Stattord 10 20 30 4 n

Answer 1

Identified the types of attributes and and depicted for all the entities

identified the relationship between all the entities according to given rules

identified the cardinality according to given rules

Identified the participation according to given rules

identified weak entity dependant

3iddle neme Adiaai Sabay Ser Baate Loronon Name 1 NOmbo Deportment Employee / Monages Supervijee eoniols Sapry va ού M HOuns N Pdoiect Super vision on Nome DRperi of Locah'on Numbr N Dependent Relationship No me Sex Biath do is