Solved by: AllAcademicHelp.com
agency Shine manages its client companies’ advertising campaigns.
It has a number
staff members with specialized skills. A record is kept of each
client company, and of every
Each campaign is based on a unique theme. A Shine staff may work on
zero, or one or
campaigns at a time. When a campaign starts, an estimate of the cost
needs to be set and agreed upon. The actual cost of a campaign is
calculated from staff time devoted to it. Staff members are paid (and
clients charged) based on the hourly rate associated with their pay
the campaign is completed, an actual completion date and the actual
cost are recorded and a single invoice for the whole campaign is sent
to the client. The issue date and payment status of
invoice are also recorded. When the client pays, the date paid is
recorded. A relational data model for this domain is provided below
to facilitate your understanding of the domain.
DDL script (create_DB.sql) for creating the corresponding database,
and a DML script (populate_DB.sql) for populating this database with
some sample data are being provided in the Assignment 2 folder. Note
DDL script will also create another table (not
in the diagram), called alerts, which is meant to contain messages as
to which invoices are overdue.
the tables listed in the relational data model as shown in Figure 1,
as well as the alerts
by running the provided DDL script provided. Then insert sample
records to the tables by running the provided DML script provided.
Verify that the tables are created and populated as intended.
Two and Three
template (stored_programs_template.sql) is provided for you to write
(one trigger and two procedures). You must not change the “signature”
with their types, orders, etc.) these programs. It is best to take
this template and fill out the required codes.
considers any invoice that remains unpaid for more than 30 days after
it has been issued as
Write a trigger called tr_overdue that fires whenever the invoice
table is updated.
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,
‘Invoice with number: x is now overdue!’ as the message, where x is
the invoice number in question.
part of this task you will write two procedures:
Procedure sp_finish_campaign (in c_title varchar(30)) that takes as
the title of a campaign, and records that the corresponding campaign
updating the CAMPAIGNFINISHDATE to the current date, and
updating the ACTUALCOST. The actual cost of a campaign is calculated
from the number
hours different staff put into it on different dates, and the salary
grade they were on
days. The procedure should signal an error with the message ‘ERROR!
does not exist’ if the input does not match any existing campaign.
Procedure sync_invoice() that identifies the invoices which are still
(as value of STATUS) more than 30 days after the invoice was issued.
records, and sets the status to OVERDUE.
task involves testing the code developed in Task Two and Task Three.
you are required to test the programs you wrote against the sample
tests to be carried out, in the order given, is provided in the
These tests constitute a minimal test against a very small number
records. Look at this file carefully to understand what is being
tested and what should
the expected result. Note the roles of setting autocommit off and the
you carry out a more extensive test by testing the programs against a
larger set of
that are designed to easily expose any flaws in your programs. You do
records, adding records, or modifying the records in other ways, and
procedures. You accordingly modify the file test_script.sql, and save
will also prepare and submit a report. A word file template for this
purpose will be provided which you will complete.
report should have the following sections:
State of the database. Paste to the word file the screen shots
sample data in the tables.
Stored Programs. Paste into this section the programs you wrote (the
that you prepared).
Required Testing against Sample Database. Paste into this section the
initial tests you
(one by one) and then the corresponding results as screenshots. The
lists what tests to run, how, and in which order.
Extensive Testing. Explain what sort of changes you are going to make
what tests you are going to run, and why. Copy and paste from your
used to modify the tables followed by the screenshots of the records
in those tables;
copy and paste the procedure calls you made, and the screenshots of
the records in the
tables (or the error messages).
Let’s block ads! (Why?)
READY TO PLACE AN ORDER