STORE (StoreID, StoreAddress, WeekDaysHours, WeekendHours)

ACCOUNT (AccountNo., AccountName, Balance)

DEPARTMENT (DepartmentID, DepartmentTitle, NoOfEmployees, AccountNo, StoreID, FranchiseTeamID)

WEEKLY_SALES_REPORT (ReportID, StartDate, EndDate, SaleAmount, ComRate, ComAmount, BrandID, BName, BSaleAmount, TypeID, TName, TSaleAmount, EmployeeID, Ename, ESaleAmount, EBonus, DepartmentID)

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)

SHAREHOLDER (S.H.ID, Name, Phone, Email, Address, NoOfShares, MemberID)

TEAM (TeamID, TeamName, TeamLeaderID)

MEMBERSHIP (MemberID, TeamID, PercentageOfShare)

Using the tables provided above, provide SQL statements for the following queries.

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 two dates as input (a duration) and updates the commission amount of every weekly report for which the start date falls under this duration.

The commission amount is the result of multiplication of sale amount and commission rate (%). The procedure also generates an output in the following format for every affected row: The commission amount for report has been updated to dollars, which is % of the total sale amount of dollars.

b. Write a procedure that takes an employee id as argument and lists the details of an employees department and reporting boss. Note that an employees 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.

c. Write a function that takes an employee id and a month-year (date of MON-YYYY format) and returns the total bonus amount the employee have earned by making exceptional sales during that month. If the employee was not awarded a bonus for that month, the function should return zero.

For the execution of the function, write a simple SQL query that lists down all the employees and their total bonus amount earned for the month of April 2018.

Hint: Although we did not maintain referential integrity constraint between EMPLOYEE and WEEKLY_SALES_REPORT, you can safely assume that the employee listed as the best performing employee in a sales report is a valid employee, who is present in the employee table.