Cancelled

mysql workbench project

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:

<yourLastname_yourFirstname>_programs.sql.

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

statements.

• 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

other files.

Skills: MySQL, SQL, Oracle, Database Administration, Database Programming

See more: php mysql simple project database, experienced vb6 developer modify existing vb6 project, modify existing flyer, xml mysql database project sample, modify existing file, dnn modify existing module source, php mysql template project, mysql simple project, modify existing software, modify existing php project, things noted modify existing software project, project plan modify existing website, modify existing project, mysql workbench create model from existing database, how to execute stored procedure in mysql workbench, how to export stored procedure in mysql workbench, mysql workbench create template from existing table

About the Employer:
( 0 reviews ) Sydney, Australia

Project ID: #22053757

4 freelancers are bidding on average $19/hour for this job

truongnguyen86

Hello there, i'm expert on MySQL, i've done similar your project before within one trigger and 2 procedures in MySQL. Finally we will test by one test script to ensure all good. Waiting for your response if interested. More

$20 AUD / hour
(196 Reviews)
7.0
romansokolv708

Perfect job posting! Here is MySQL expert you are looking for. If you contact me, you can save your time. As a senior expert, your project is no problem for me. Please send me message and let's discuss about your proje More

$20 AUD / hour
(9 Reviews)
4.3
kaindo2017

Hello,I'm a Bsc degree holder in computer science. I have worked on many papers in technical writing,report writing,research writing.I have read and understood your requirement and I know you will never regret, thank y More

$20 AUD / hour
(13 Reviews)
4.2
Saimi786

Hello Thanks for your posting. I am a senior developer so i can do it very easily if you want.I’ve read your job description carefully and I am very interested in your project. I am sure that I can finish this project More

$16 AUD / hour
(1 Review)
1.6