Question

5.A lawn and tree care company RealGreen uses the paper form as below to record the weekly (just one week in this question!) information about its part-time employees (one form for one employee). The company asks you to create a database application to keep track of the information of the part-time employees. Please (a) develop the E-R diagram (8 points). (b) develop the table structures. Each table should include table name, attributes, and the primary key (6 points). Employee ID: Employee Name: Employee Pay Rate: Employee Weekly Working Hours: Department ID: Department Name: (c). write the SQL statements to answer (16 points): cl. List name of employees in "Tree" Department (only department name is known) (use a join). c2. List name of employees in "Tree" Department (only department c3. Calculate the weekly pay (PayRate*Hours) for each employee. c4. Calculate the average pay rate grouped by department na name is known) (use a subquery). ame.

Answer 1

a ER diagram It is n:1 relationship because an employee works in only one department But the department has more than one employee Employee Id is key attribute for employee entity Department Id is key attribute for department entity

Department ID Employee Department name name (employeelD 1:M 1 works in Employee Deprtment Employee Employee weekly working hours pay rate

b) Table Structures Employee(Employee_ID, Employee_Name, EmployeePayRate EmployeeWorkingHours Department_ID) Department(Department_ID, Department_Name) Table name:Employee Attributes Employee_ID Integer Employee_Name char(30) EmployeePayRate Integer EmployeeWorkingHours Integer Department_ID Integer Primary key Employee_ID Foreign key DeparmentlD Table name:Department Attributes Department_ID Integer, Department_Name char(30) Primary key Department_ID

cl.) Sql query SELECT Employee_Name FROM Employee e JOIN Department d ON e.Department_ID = d.Department ID c2.) Sql query SELECT Employee_Name FROM Employee WHERE Department_ID IN (SELECT Department_ID FROM Department); c3.) Sql query SELECT sum(EmployeePayRate * EmployeeHours) FROM Employee GROUP BY Employee_ID; c4.) Sql query SELECT avg(EmployeePayRate) FROM Employee e JOIN Department d ON e.Department_ID = d.Department_ID GROUP BY Department_Name