Dp
Implement the COMPANY database as defined in the schema given in Figure 6.1 on page 181 of the Elmarsi/Navathe text sixth edition. A. (15 points) You need to implement the database using MySQL RDBMS or any Open-source RDBMS of your choice. Examples of Open-source free RDBMS are SQLcl and SQL Developer Command Line, PostgreSQL, etc. B. (5 points) Populate the database with the tuples given in Figure 5.6 on page 162 of the Elmarsi textbook. C. ( 30 points) after the database is defined and populated, you then need to perform the following queries: 1. List the names of all male employees with two or more dependents 2. Find the names of all employees who are directly supervised by the employee whose SSN is ' 888665555 ' 3. For each project, list the project name and the total hours per week (by all employees) spent on that project. 4. Retrieve the names of all employees who work in the department that has the employee with the highest salary among all employees 5. List the names of all employees who work on more than two projects 6. For each department whose average employee salary is more than $30,000, retrieve the department name and the number of employees working for that department. (Hint1: you may need to debug the schema and data for possible errors. Also, the orders by which you create the tables are important.) Note: make sure that ALL constraints originally defined in Figure 6.1 are specified in your DB. Submit the solution by the due date. Your submission must include: 1. Ensure that the schema definition and actual state (Data), along with all the constraints (FK/PK and NOT NULL, etc) as defined in the schema, are included (see figure 6.1. 162 Chapter 5 The Relational Data Model and Relational Database Constraints 6.1 SQL Data Definition and Data Types 181 Implement the COMPANY database as defined in the schema given in Figure 6.1 on page 181 of the Elmarsi/Navathe text sixth edition. A. (15 points) You need to implement the database using MySQL RDBMS or any Open-source RDBMS of your choice. Examples of Open-source free RDBMS are SQLcl and SQL Developer Command Line, PostgreSQL, etc. B. (5 points) Populate the database with the tuples given in Figure 5.6 on page 162 of the Elmarsi textbook. C. ( 30 points) after the database is defined and populated, you then need to perform the following queries: 1. List the names of all male employees with two or more dependents 2. Find the names of all employees who are directly supervised by the employee whose SSN is ' 888665555 ' 3. For each project, list the project name and the total hours per week (by all employees) spent on that project. 4. Retrieve the names of all employees who work in the department that has the employee with the highest salary among all employees 5. List the names of all employees who work on more than two projects 6. For each department whose average employee salary is more than $30,000, retrieve the department name and the number of employees working for that department. (Hint1: you may need to debug the schema and data for possible errors. Also, the orders by which you create the tables are important.) Note: make sure that ALL constraints originally defined in Figure 6.1 are specified in your DB. Submit the solution by the due date. Your submission must include: 1. Ensure that the schema definition and actual state (Data), along with all the constraints (FK/PK and NOT NULL, etc) as defined in the schema, are included (see figure 6.1. 162 Chapter 5 The Relational Data Model and Relational Database Constraints 6.1 SQL Data Definition and Data Types 181
