CSE2/4DBF-Assignment 2-Semester 2-2020 Page 1 of 4
CSE2/4DBF 2020
Assignment 2 (20%)
Due date: 10.00am Tuesday, October 13th 2020 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 sheet. 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 CSE2/4DBF submission link provided on LMS by 10.00am Tuesday, October 13th, 2020.
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.
CSE2/4DBF-Assignment 2-Semester 2-2020 Page 2 of 4 Implement the following tasks using ORACLE SQL*Plus.
Download the file SMatchSchema.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.
Run the schema file in Oracle following the same process we followed to run a schema during the lab. As a reference, you can see the instructional video on LMS on how you can copy a schema file to latcs7 server and execute it in Oracle.
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.
CSE2/4DBF-Assignment 2-Semester 2-2020 Page 3 of 4
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, what you 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 the job 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 expenses for the jobs).
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 if the 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. If the job did not receive any quote, the functions returns zero.
[a: 20 marks, b: 15 marks – 35%]
CSE2/4DBF-Assignment 2-Semester 2-2020 Page 4 of 4
Task 3 [15 marks]
Provide the implementation of the following trigger. For submission, please include both the PL/SQL code and an insert statement to demonstrate the trigger functionality.
a. A Trigger which automatically stores the information of a job in a backup table called DeletedJobs, when the job is deleted from the job table. Do not use Job ID as the primary key in the backup table, instead, use a database sequence object.
[15 marks – 15%]