Question

QUESTION B1. (7 marks) This question assumes the following relational schema Employee (employeeNumber, lastName, firstName, DOB, HireDate, Position Salary, Dept) Primary Key: employeeNumber Foreign key: Dept refers to DeptID in Department Department (DeptID, DeptName, DeptLocation) Primary Key: DeptID You have been given the following MySQL stored procedure: CREATE PROCEDURE Find_EmployeeName (IN employeeNo INT (11), OUT employeeName VARCHAR (60)) BEGIN SELECT concat(firstName, '', lastName) INTO employeeName FROM employees WHERE employeeNumber employeeNo; END (a) (2 marks) Name the two types of parameters that have been used in the declaration of (b) (2 marks) What is a trigger? Explain how triggers can be used to enhance data (c) (3 marks) Create a stored procedure that takes the department name as an input and the procedure Find EmployeeName. List at least one characteristic of each type integrity? prints the details of all the employees who are currently working for the department You may use the following procedure template CREATE PROCEDURE Find_Employee (IN deptName VARCHAR (50)) BEGIN DECLARE empRec CURSOR FOR OPEN empRec; REPEAT END REPEAT; CLOSE empRec; END

Answer 1

Question B1

a) The two types of parameters that have been used in the declaration of the procedure are the IN parameter and the OUT parameter.

    IN parameter:

                   These types of parameters are used to send values to stored procedures. This type of parameter is a read only parameter. We can assign the value of IN type parameter to a variable or use it in a query, but we cannot change its value inside the procedure.

   OUT parameter:

          The OUT parameters are used to send the OUTPUT from a procedure or a function. This is a write-only parameter i.e, we cannot pass values to OUT parameters while executing the stored procedure, but we can assign values to OUT parameter inside the stored procedure and the calling program can receive this output value.

b) A trigger is a procedure that is automatically invoked by the DBMS in response to specified changes to the database.

    Data Integrity: This refers to the accuracy and consistency of data stored in a database, data warehouse, data mart or other construct.

Triggers help to enhance data integrity when there is a referential integrity constraint on the tables or data we are working with and these tables are present in different nodes or servers.

Example: consider a medicine ordering system which contains data from two different servers say server ‘A’ and server ‘B’

Server A contains a database which consists of the tables that store details about the customer orders and the status of order.

Server B contains a database that stored the details about the orders ,products ordered, their availability.

   And suppose a product is not available in the stock then the order that is placed for that product is deleted in the server B. So, when the order is deleted in the server B then the order deletion must also reflect in orders status in the the server A so that the data integrity is maintained.otherwise the customer will be waiting for the completion of that order.So here in such cases we use TRIGGERS to enhance data integrity by creating a trigger that updates the order status in the server A whenever a deletion of order happens in server B.