Task One [2 marks]
Create the tables listed in the relational data model as shown in Figure 1, as well as the alerts
table, by running the provided DDL script provided in the folder. Then insert
sample records to the tables by running the provided DML script provided. Verify that the tables
are created and populated as intended.
Task Two and Three [10 marks]
A template ([login to view URL]) is provided for you to write the required
programs (one trigger and two procedures). You must not change the “signature” (name,
parameters with their types, orders, etc.) these programs. It is best to take this template and fill out
the required codes. Remember to put your name and student number at the top as required. You
should save this file with name of the following format, and submit it among other files:
Task Two [4 marks]
Shine considers any invoice that remains unpaid for more than 30 days after it has been issued as
overdue. Write a trigger called tr_overdue that fires whenever the invoice table is updated.
If the STATUS of any invoice is updated to “OVERDUE” from something else, then a new row is
inserted to the alerts table with the current date as the message_date, user as the origin,
and 'Invoice with number: x is now overdue!' as the message, where x is the
invoice number in question.
Task Three [6 marks]
As part of this task you will write two procedures:
1. Procedure sp_finish_campaign (in c_title varchar(30)) that takes as
input the title of a campaign, and records that the corresponding campaign has been
completed by (a) updating the CAMPAIGNFINISHDATE to the current date, and (b)
updating the ACTUALCOST. The actual cost of a campaign is calculated from the number
of hours different staff put into it on different dates, and the salary grade they were on
such days. The procedure should signal an error with the message 'ERROR! Campaign
title does not exist' if the input does not match any existing campaign.
2. Procedure sync_invoice() that identifies the invoices which are still recorded as
UNPAID (as value of STATUS) more than 30 days after the invoice was issued. It updates
those records, and sets the status to OVERDUE.
Task Four (Testing) [3 marks]
This task involves testing the code developed in Task Two and Task Three.
• First you are required to test the programs you wrote against the sample data provided.
The tests to be carried out, in the order given, is provided in the provided file:
test_script.sql. These tests constitute a minimal test against a very small number
of records. Look at this file carefully to understand what is being tested and what should
be the expected result. Note the roles of setting autocommit off and the rollback
• Next you carry out a more extensive test by testing the programs against a larger set of
records that are designed to easily expose any flaws in your programs. You do that by
deleting records, adding records, or modifying the records in other ways, and then calling
different procedures. You accordingly modify the file [login to view URL], and save it
as <yourLastname_yourFirstname>[login to view URL], and submit among