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.

Task 2 should be saved to a file named task2.txt.

Task 3 should be saved to a file named task3.txt.

All the tasks above are to be submitted in soft-copy format using the CSE2DBF submission link provided on LMS by 11:59pm Wednesday, Jun 1st 2022.

SUBMISSION CHECKLIST:

ü The relevant SQL queries for the ‘ServiceMatch’ 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 APEX.

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

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

The list of tables available for this assignment is the following:

CLIENT (ClientNo, ClientName, ClientAddress, ClientPhone, ClientEmail)

CORPORATE_CLIENT (ClientNo, CorporationName, BusinessAddress) INDIVIDUAL_CLIENT (ClientNo, PropertyOwner)

BUSINESS (ABNNumber, BusinessName, ContactName, ContactNumber, ContactEmail, BusinessAddress, BusinessPostcode)               FREELANCER_BUSINESS (ABNNumber, EliteMemberID)                 CORPORATE_BUSINESS (ABNNumber)

JOB (JobID, JobDescription, UrgencyLevel, JobAddress, Postcode, SelectedBusinessABNNumber, IndustryID)

CONTRACT_JOB (JobID, StartDate, EndDate, CorporateClientNo) CASUAL_JOB (JobID, IndividualClientNo)

INDUSTRY (IndustryID, IndustryTitle, UnionID)

TRADE_UNION (UnionID, UnionTitle, UnionContactName,          UnionContactNumber, UnionEmail, UnionAddress, EliteMemberID) SUBURB (Postcode, SuburbName)

INVOICE (InvoiceNo, Amount, JobID)

SEMINAR (SeminarID, SeminarTitle, SeminarDataTime, SeminarVenue) QUOTATION (JobID, ABNNumber, QuoteAmount)

BUSINESS_INDUSTRY (ABNNumber, IndustryID)

ADJACENT_SUBURB (PostCode, AdjacentPostCode)

ELITE_MEMBER (EliteMemberID)

SEMINAR_ATTENDIES (EliteMemberID, SeminarID)

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.  Display the ID and title for the most popular industry (industry that has the highest number of jobs).

b.  Display the ID for the job that has the longest contract duration. (Hint: in SQL, if you subtract two dates, whatyou get is a difference in days between those dates)

c.  Suppose the manager of ServiceMatch wants to notify the businesses about a new job with job ID '11'. List the surrounding businesses (ABN number and business name) in the same industry for that job. (Hint: business table has an attribute called ‘BusinessPostcode’. Job table has references to thejob suburb and industry).

d.  List  down  the  details  of those  freelance  businesses  who  do  not  have  seminar  attendance privileges.

e.  List all the clients and their assiciated total job expenses. If there is a client who did not post any job, or no invoice was generated for the posted jobs, the total expense should appear as zero for that client. Your query  should list the details in three columns, ‘Client Number’, ‘Client Name’, ‘Total Job Expense’ . (Hint: the invoice table lists the expensesfor thejobs).

f.  List the seminar titles, and date on which they were held, along with the number of participants, for the seminars arranged during the afternoon part of the day (between  12 PM and 3 PM). Count 1 participation for each elite member even ifthe elite member is a trade union.

[a – d: 8 marks each, e-f: 9 marks each – 50%]

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 (for the procedure)/SQL statement (for the function) to demonstrate the functionality.

a. Write a stored procedure that takes an industry ID as input and lists down the representing trade union and all the registered businesses for that industry. The first line of the output should show the trade union title. The ABN and business name for each business should then be shown on separate lines.

b.  Write a stored function that takes a job ID as input and returns the lowest quote amount for that job. Ifthe job did not receive any quote, the functions returns zero.