Need ASAP
Medical Herbalist Database System CSE4DBF 2018 Assignment 2 (20%) Due date: 10.00am Wednesday, May 30th 2018 AIMS AND OBJECTIVES: to perform queries on a relational database system using SQL; to demonstrate an advanced knowledge of stored procedures, stored functions and triggers. This is an individual Assignment. You are not permitted to work as a group when writing this assignment. Copying, Plagiarism: Plagiarism is the submission of somebody else’s work in a manner that gives the impression that the work is your own. The Department of Computer Science and Information Technology treats plagiarism very seriously. When it is detected, penalties are strictly imposed. Students are referred to the Department of Computer Science and Information Technology’s Handbook and policy documents with regard to plagiarism and assignment return, and also to the section of ‘Academic Integrity’ on the subject learning guide. No extensions will be given: Penalties are applied to late assignments (5% of total assignment mark given is deducted per day, accepted up to 5 days after the due date only). If there are circumstances that prevent the assignment being submitted on time, an application for special consideration may be made. See Student Handbook for details. Note that delays caused by computer downtime cannot be accepted as a valid reason for a late submission without penalty. Students must plan their work to allow for both scheduled and unscheduled downtime. SUBMISSION GUIDELINES: Task 1 should be saved to a file named task1.txt using the SPOOL command. Task 2 should be saved to a file named task2.txt using the SPOOL command. Task 3 should be saved to a file named task3.txt using the SPOOL command. Note: an example of using the SPOOL command is given in the lab book. In the SPOOL file, you need to provide the query/procedure/function/trigger execution and the sample output. For the trigger, you need to show a sample test that demonstrates the successful execution of the trigger. All the tasks above are to be submitted in soft-copy format using the submission link provided on LMS by 10.00am Wednesday, May 30th, 2018. SUBMISSION CHECKLIST: The relevant SQL queries for the ‘House2Home Franchise Application’ Database System; The required stored procedures, stored function, and triggers. NOTE: No built-in ORACLE column numbering (such as ROWNUM) or other ORACLE ranking facilities (such as RANK) can be used in this assignment. Implement the following tasks using ORACLE SQL*Plus. Download the file H2HSchema.sql from the LMS site and run it on ORACLE SQL*Plus. This file contains all the CREATE and INSERT statements you will need for this assignment. To run the file, issue the following command: @D:\dbf\H2HSchema.sql Where D:\dbf is the location of the file (for example)1. 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) 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) NOTE: PK is printed underlined and FK is printed italic in italics. 1 Please note that if you are using sqlplus in latcs7 server through putty, you cannot refer to a file on your laptop or PC. This is because the server does not know about your local file path. 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. [5 marks] b. Show the name and address of all team members for store S001. [5 marks] c. 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. [5 marks] d. Show the team information and number of members for all successful franchise application. [5 marks] e. Show the details of the franchise application that took the longest time to reach a decision. [5 marks] f. 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. [5 marks] g. List the team member details (shareholder/employee details) 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) [10 marks] h. Show the top 4 employees (name, salary earned) that have earned the highest salary from January 2017 – December 2017. Assume that all casual employees work 12 hours per week and there are 45 working weeks a year. [10 marks] Task 2 [30 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 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 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. 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 procedure that accepts as input, an employee id and a month (in the same format), and calls the function to get the employee’s bonus amount and prints the following message: The employee named has earned a total bonus amount of for the month of by making outstanding sales. 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. [10 marks each] Task 3 [20 marks] Provide the implementation of the following triggers. For submission, please include both the PL/SQL code and a DML statement (insert, update or delete) 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. b. A trigger that automatically backs-up the oldest weekly sales report of a specific department when a new report is generated (for the same department) and the number of reports in the report table exceeds 30. To preserve the data, the trigger stores the selected report into a backup table: BackupReport (ReportID, StartDate, EndDate, SaleAmount) [10 marks each] Aims and Objectives: Note: 5 marks will be deducted if the students do not use SPOOL. ITEM MARKS ITEM MARKS Comments: Total marks: 1a 2a 1b 2b 1c 2c 1d 3a 1e 3b 1f 1g 1h CSE2/4DBF: Assignment 2 marking rubric Area Excellent Very good Good Acceptable Unacceptable Basic Queries (The marking criteria applies to each individual queries from questions 1a to 1f) (Each question is worth 5%) ● The query does not have any errors ● The query output is correct with all the required information or attribute (4 – 5 marks) ● The query has minor syntax errors (e.g. missing join, missing union), which may lead to incorrect answer. (2 – 3 marks) ● The query has major syntax errors and do not run (1) Or, ● The query is not present (0) Or, ● The query produces completely incorrect or irrelevant result. (0) (0 – 1 marks) Advanced Queries (g and h) (The marking criteria applies