New

Learn smart - Learn online. Upto 88% off on courses for a limited time. View Courses

Securing Higher Grades Costing Your Pocket? Book Your Assignment at The Lowest Price Now!
loader
Add File

Error goes here

Files Missing!

Please upload all relevant files for quick & complete assistance.

Guaranteed Higher Grade!

Stuck on Your Question?

Get 24x7 live help from our Top Tutors. All subjects covered.

loader
250 words

Error goes here

Files Missing!

Please upload all relevant files for quick & complete assistance.

Students Who Viewed This Also Studied

6 Pages
ICT394 Data warehouse design exercise

This assessment addresses the following Unit Learning Outcomes: 1. Describe the common data sources that exist in organisations and their use in BI 2. Demonstrate practical skills in the processes ass ...

Course

ICT394

Type

Programming

Subject

Database

University

Murdoch University

15 Pages
MIS605 Systems Analysis and Design

Task: ABC University (ABCU) is seeking to develop an online student enrolment system (the System) whereby the students would be able to enrol themselves into eligible subjects at the beginning of eac ...

Course

MIS605

Type

Programming

Subject

Database

University

Laureate International Universities

9 Pages
CSIT115 Data Management and Security

The high school volleyball games are starting soon, and you are tasked to design a database that contains information about the volleyball teams, volleyball players, coaches, and games played by the t ...

Course

CSIT115

Type

Programming

Subject

Database

University

Singapore University of Social Sciences

22 Pages
CS1003 Programming with Data

Task Setting and Case Study: You will conduct in-depth study on the design and creation of a database system relating to real world concepts. The coursework tasks relate to a case study whose detai ...

Course

CS1003

Type

Programming

Subject

Database

University

University of St Andrews

ISYS224 Database Systems

Question

Answered

Task Specifications

Task One

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 'Assignment 2' 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

A template (stored_programs_template.sql) 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

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

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)

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 test_script.sql, and save it as <yourLastname_yourFirstname>_test_script.sql, and submit among other files.

ISYS224 Database Systems

Answer in Detail

Solved by qualified expert

Get Access to This Answer

Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Hac habitasse platea dictumst vestibulum rhoncus est pellentesque. Amet dictum sit amet justo donec enim diam vulputate ut. Neque convallis a cras semper auctor neque vitae. Elit at imperdiet dui accumsan. Nisl condimentum id venenatis a condimentum vitae sapien pellentesque. Imperdiet massa tincidunt nunc pulvinar sapien et ligula. Malesuada fames ac turpis egestas maecenas pharetra convallis posuere. Et ultrices neque ornare aenean euismod. Suscipit tellus mauris a diam maecenas sed enim. Potenti nullam ac tortor vitae purus faucibus ornare. Morbi tristique senectus et netus et malesuada. Morbi tristique senectus et netus et malesuada. Tellus pellentesque eu tincidunt tortor aliquam. Sit amet purus gravida quis blandit. Nec feugiat in fermentum posuere urna. Vel orci porta non pulvinar neque laoreet suspendisse interdum. Ultricies tristique nulla aliquet enim tortor at auctor urna. Orci sagittis eu volutpat odio facilisis mauris sit amet.

Tellus molestie nunc non blandit massa enim nec dui. Tellus molestie nunc non blandit massa enim nec dui. Ac tortor vitae purus faucibus ornare suspendisse sed nisi. Pharetra et ultrices neque ornare aenean euismod. Pretium viverra suspendisse potenti nullam ac tortor vitae. Morbi quis commodo odio aenean sed. At consectetur lorem donec massa sapien faucibus et. Nisi quis eleifend quam adipiscing vitae proin sagittis nisl rhoncus. Duis at tellus at urna condimentum mattis pellentesque. Vivamus at augue eget arcu dictum varius duis at. Justo donec enim diam vulputate ut. Blandit libero volutpat sed cras ornare arcu. Ac felis donec et odio pellentesque diam volutpat commodo. Convallis a cras semper auctor neque. Tempus iaculis urna id volutpat lacus. Tortor consequat id porta nibh.

Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Hac habitasse platea dictumst vestibulum rhoncus est pellentesque. Amet dictum sit amet justo donec enim diam vulputate ut. Neque convallis a cras semper auctor neque vitae. Elit at imperdiet dui accumsan. Nisl condimentum id venenatis a condimentum vitae sapien pellentesque. Imperdiet massa tincidunt nunc pulvinar sapien et ligula. Malesuada fames ac turpis egestas maecenas pharetra convallis posuere. Et ultrices neque ornare aenean euismod. Suscipit tellus mauris a diam maecenas sed enim. Potenti nullam ac tortor vitae purus faucibus ornare. Morbi tristique senectus et netus et malesuada. Morbi tristique senectus et netus et malesuada. Tellus pellentesque eu tincidunt tortor aliquam. Sit amet purus gravida quis blandit. Nec feugiat in fermentum posuere urna. Vel orci porta non pulvinar neque laoreet suspendisse interdum. Ultricies tristique nulla aliquet enim tortor at auctor urna. Orci sagittis eu volutpat odio facilisis mauris sit amet.

Tellus molestie nunc non blandit massa enim nec dui. Tellus molestie nunc non blandit massa enim nec dui. Ac tortor vitae purus faucibus ornare suspendisse sed nisi. Pharetra et ultrices neque ornare aenean euismod. Pretium viverra suspendisse potenti nullam ac tortor vitae. Morbi quis commodo odio aenean sed. At consectetur lorem donec massa sapien faucibus et. Nisi quis eleifend quam adipiscing vitae proin sagittis nisl rhoncus. Duis at tellus at urna condimentum mattis pellentesque. Vivamus at augue eget arcu dictum varius duis at. Justo donec enim diam vulputate ut. Blandit libero volutpat sed cras ornare arcu. Ac felis donec et odio pellentesque diam volutpat commodo. Convallis a cras semper auctor neque. Tempus iaculis urna id volutpat lacus. Tortor consequat id porta nibh.

Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Hac habitasse platea dictumst vestibulum rhoncus est pellentesque. Amet dictum sit amet justo donec enim diam vulputate ut. Neque convallis a cras semper auctor neque vitae. Elit at imperdiet dui accumsan. Nisl condimentum id venenatis a condimentum vitae sapien pellentesque. Imperdiet massa tincidunt nunc pulvinar sapien et ligula. Malesuada fames ac turpis egestas maecenas pharetra convallis posuere. Et ultrices neque ornare aenean euismod. Suscipit tellus mauris a diam maecenas sed enim. Potenti nullam ac tortor vitae purus faucibus ornare. Morbi tristique senectus et netus et malesuada. Morbi tristique senectus et netus et malesuada. Tellus pellentesque eu tincidunt tortor aliquam. Sit amet purus gravida quis blandit. Nec feugiat in fermentum posuere urna. Vel orci porta non pulvinar neque laoreet suspendisse interdum. Ultricies tristique nulla aliquet enim tortor at auctor urna. Orci sagittis eu volutpat odio facilisis mauris sit amet.

Tellus molestie nunc non blandit massa enim nec dui. Tellus molestie nunc non blandit massa enim nec dui. Ac tortor vitae purus faucibus ornare suspendisse sed nisi. Pharetra et ultrices neque ornare aenean euismod. Pretium viverra suspendisse potenti nullam ac tortor vitae. Morbi quis commodo odio aenean sed. At consectetur lorem donec massa sapien faucibus et. Nisi quis eleifend quam adipiscing vitae proin sagittis nisl rhoncus. Duis at tellus at urna condimentum mattis pellentesque. Vivamus at augue eget arcu dictum varius duis at. Justo donec enim diam vulputate ut. Blandit libero volutpat sed cras ornare arcu. Ac felis donec et odio pellentesque diam volutpat commodo. Convallis a cras semper auctor neque. Tempus iaculis urna id volutpat lacus. Tortor consequat id porta nibh.

15 More Pages to Come in This Document. Get access to the complete answer.

MyAssignmenthelp.com boasts a team of talented and highly skilled coursework writers based in UK who assist students in the best possible manner. Students, who need coursework help, find our services fulfilling and effective because we provide high quality help at affordable price. So, students who often search can someone do my coursework cheap or can experts Write my coursework for cheap get perfect solution at MyAssignmenthelp.com. They pay for best coursework and get it from us.

More ISYS224 ISYS224 Database Systems: Questions & Answers

Q
icon

We aren't endorsed by this University

ICT394 Data warehouse design exercise

This assessment addresses the following Unit Learning Outcomes: 1. Describe the common data sources that exist in organisations and their use in BI 2. Demonstrate practical skills in the processes associated with extraction, transformation and loading (ETL) of organisational data 3. Design and imple ...

View Answer
Q
icon

We aren't endorsed by this University

MIS605 Systems Analysis and Design

Task: ABC University (ABCU) is seeking to develop an online student enrolment system (the System) whereby the students would be able to enrol themselves into eligible subjects at the beginning of each trimester and pay their tuition fees online. You have been hired as the Business Analyst for this ...

View Answer
Q
icon

We aren't endorsed by this University

CSIT115 Data Management and Security

The high school volleyball games are starting soon, and you are tasked to design a database that contains information about the volleyball teams, volleyball players, coaches, and games played by the teams. A volleyball team is described by a team name, the name of school, and its address. An address ...

View Answer
Q
icon

We aren't endorsed by this University

CS1003 Programming with Data

Task Setting and Case Study: You will conduct in-depth study on the design and creation of a database system relating to real world concepts. The coursework tasks relate to a case study whose details are attached. If your database, your written work or final demo SQL does not relate to the case s ...

View Answer

Content Removal Request

If you are the original writer of this content and no longer wish to have your work published on Myassignmenthelp.com then please raise the content removal request.

Choose Our Best Expert to Help You

icon

5% Cashback

On APP - grab it while it lasts!

Download app now (or) Scan the QR code

*Offer eligible for first 3 orders ordered through app!

screener
ribbon
callback request mobile
Have any Query?