Stored functions are very similar to stored procedures, with the exception that they must return a value to the statement from which they are called.1 This means you cannot simply execute a stored function; you must "call" the function from within the SELECT line of a query.

The list of tables available for this assignment is the following: STORE (StoreID, StoreAddress, WeekDaysHours, WeekendHours) ACCOUNT (AccountNo., AccountName, Balance) DEPARTMENT (DepartmentID, DepartmentTitle, NoOfEmployees, AccountNo, StoreID, FranchiseTeamID) FRANCHISE_APP (AppNumber, AppDate, DecisionDate, Status, DepartmentID, TeamID) EMPLOYEE (EmployeeID, Name, Phone, Email, Address, Gender, DOB, JoiningDate, Centralorlocalemployment, EFTPOSID, CANumber, DepartmentID) CASUAL_EMPLOYEE (EmployeeID, HourlyRate) PART_TIME_EMPLOYEE (EmployeeID, WeeklyHours, Salary) FULL_TIME_EMPLOYEE (EmployeeID, LeaveDays, Salary, MemberID) TEAM MEMBER (MemberID) SHARHOLDER (H.ID, Name, Phone, Email, Address, NoofShares, MemberID) TEAM (TeamID, TeamName, TeamLeaderID) MEMBERSHIP (MemberID, TeamID, Percentageofshare) NOTE: PK is printed underlined and FK is printed italic in italics. Provide the implementation of the following stored procedures and function. For submission, please include both the PL/SQL code and an execute procedure/SQL statement to demonstrate the functionality. a. Write a procedure that takes an employee id as argument and lists the details of an employee's department and reporting boss. Note that an employee's reporting boss is the person who has the highest percentage of share in the franchise team. The reporting boss can be either a full-time employee or a shareholder. If the reporting boss is a shareholder, display number of shares along with contact details and if the boss is an employee, mention DOB, gender and joining date along with the contact information. Hint: First, try to identify the member id of the boss. Then, using that member id, check whether the member is a shareholder or full-time employee. [20 marks] b. Write a stored function that takes an employee id as input and returns how long (in years) an employee has been a part of the company. For convenience, we have provided an attribute in the employee table called 'joiningDate'. Use the function to write an sql query that lists down all the employees and their associated duration of employment. [15 marks]