Employees (employeeNumber, lastName, firstName, DOB, HireDate, Position, Salary, Dept) Primary Key: employeeNumber Foreign key: Dept refers...

  

Transcribed Image Text:

Employees (employeeNumber, lastName, firstName, DOB, HireDate, Position, Salary, Dept) Primary Key: employeeNumber Foreign key: Dept refers to Dept ID in Departments Departments (Dept ID, DeptName, DeptLocation) Primary Key: Dept ID (2 marks) Consider the following MySQL stored procedure: CREATE PROCEDURE Find EmployeeNo( IN fName CHAR (30), IN 1Name CHAR (30), BEGIN SELECT employeeNumber INTO employeeNo FROM employees WHERE fName = firstName AND 1Name= last Name; END OUT employeeNumber INT(10)) Discuss the two error conditions you may come across as a result of the execution of the procedure. You may use the following procedure call to explain your answer: CALL Find EmployeeNo ('Justin', 'Thyme', ON). (4 marks) Modify the procedure Find EmployeeNo given in (a) above so that neither error you have identified above results in the abrupt termination of the execution of the procedure body. (4 marks) Write a trigger to enforce the following business rule: cach department has only one manager. You may use the following trigger template for ensuring this business rule while new rows are inserted into Employees: CREATE TRIGGER TR_MANAGER BEFORE INSERT ON Employees FOR EACH ROW BEGIN END Employees (employeeNumber, lastName, firstName, DOB, HireDate, Position, Salary, Dept) Primary Key: employeeNumber Foreign key: Dept refers to Dept ID in Departments Departments (Dept ID, DeptName, DeptLocation) Primary Key: Dept ID (2 marks) Consider the following MySQL stored procedure: CREATE PROCEDURE Find EmployeeNo( IN fName CHAR (30), IN 1Name CHAR (30), BEGIN SELECT employeeNumber INTO employeeNo FROM employees WHERE fName = firstName AND 1Name= last Name; END OUT employeeNumber INT(10)) Discuss the two error conditions you may come across as a result of the execution of the procedure. You may use the following procedure call to explain your answer: CALL Find EmployeeNo ('Justin', 'Thyme', ON). (4 marks) Modify the procedure Find EmployeeNo given in (a) above so that neither error you have identified above results in the abrupt termination of the execution of the procedure body. (4 marks) Write a trigger to enforce the following business rule: cach department has only one manager. You may use the following trigger template for ensuring this business rule while new rows are inserted into Employees: CREATE TRIGGER TR_MANAGER BEFORE INSERT ON Employees FOR EACH ROW BEGIN END

Answer rating: 100% (QA)

CREATE DEFINERrootlocalhost PROCEDURE FindEmployeeNoIN fName CHAR30 IN lName CHAR30 OUT employeeNo I... View the full answer