Implement the following tasks using ORACLE Application Express (APEX).

Download the file H2HSchema.sql from the LMS site and run it on APEX. This file contains all the CREATE and INSERT statements you will need for this assignment.

Run the schema file in APEX following the same process we followed to run a schema during the lab (you can refer to lab 6 recording).

NOTE: YOU DO NOT NEED TO INSERT MORE DATA INTO THE TABLES.

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)

SHAREHOLDER (S.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.

Task 1 [50 marks]

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

a. Show the store information along with its departments for all stores that opens the latest during weekend.

b. List the franchise application details for each department of H2H along with the department id and title. Include the departments that have not received any application yet.

c. Show the team information and number of members for all successful franchise applications.

d. Show the details of the franchise application that took the longest time to reach a decision.

e. List the team member details (including shareholder and employee details such as id, name, phone and email) for all the team members who are part of a franchise team of a successful application. For each of the members, also include the date on which their franchise application was granted. Hint: use UNION.

f. List the team member details (member id and team id) who has the maximum credit to his name, based on his percentage of share in the team and the positive account balance of the franchising department (Hint: account balance can be found in the Account table). Please note, it is safe to assume that only one team member has the highest percentage of share.

Task 2 [35 marks]

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 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. 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.

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.

Task 3 [15 marks]

Provide the implementation of the following triggers. For submission, please include both the PL/SQL code and a DML statement (insert to demonstrate the trigger functionality.

a. A trigger that prevents a full-time employee from applying for a franchise if he/she has less than 10 years of experience as an employee of H2H. Please note, there may be existing data in the database that does not follow this rule and you do not need to consider them when writing your trigger.