ITEC624 Database Systems Assignment Problem

Assignment Help Database Management System
Reference no: EM132390572

ISYS224/ITEC624 Database Systems - Database Programming and Implementation Assignment - Macquarie University, Australia

1. Problem Domain

The problem domain is a simplified portion of what you came across earlier in Assignment 1.

Advertising agency Shine manages its client companies' advertising campaigns. It has a number of staff members with specialized skills. A record is kept of each client company, and of every campaign. Each campaign is based on a unique theme. A Shine staff may work on zero, or one or more 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 grade.

When 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 the 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. A 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 that the DDL script will also create another table (not included in the diagram), called alerts, which is meant to contain messages as to which invoices are overdue.

2. Task Specifications

Task One -

Create the tables listed in the relational data model as shown in Figure 1 (attached), 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.

3. Report Preparation

Your report should have the following sections:

1. Initial State of the database. Paste to the word file the screen shots showing the provided sample data in the tables.

2. Stored Programs. Paste into this section the programs you wrote (the sql file <yourLastname_yourFirstname>_programs.sql that you prepared).

3. Required Testing against Sample Database. Paste into this section the initial tests you ran (one by one) and then the corresponding results as screenshots. The provided script file lists what tests to run, how, and in which order.

4. More Extensive Testing. Explain what sort of changes you are going to make to which tables, what tests you are going to run, and why. Copy and paste from your file <yourLastname_yourFirstname>_test_script.sql the DML statements you used to modify the tables followed by the screenshots of the records in those tables; then copy and paste the procedure calls you made, and the screenshots of the records in the relevant tables (or the error messages).

5. Notes (optional). In this optional section, you might wish to note anything, such as whether you faced any particular difficulty in completing any of these tasks, the nature and extent of any help you received from anyone, and why.

Attachment:- Database Programming Assignment Files.rar

Verified Expert

In this assignment,I read the case studied where I found the requirement of workbench mysql. I given case study,ER diagram has given and found the relation diagram which gives the idea of database.I have install the workbench and write the sql queries.

Reference no: EM132390572

Questions Cloud

Explain why studying management history is important : Explain why studying management history is important. Then, choose two management approaches among the many listed in the book
Employees in deciding their firm strategic goals : Organizations like Whole Foods have traditionally involved all of their employees in deciding their firm's strategic goals
United states-based businesses : What are 3 governmental policy trends faced by many United States-based businesses?
Perform the project sizing : How to determine the criteria that must be considered to perform the project sizing.
ITEC624 Database Systems Assignment Problem : ISYS224/ITEC624 Database Systems - Database Programming and Implementation Assignment Help and Solution - Macquarie University, Australia
Most important aspects of managing risk for project : One of the most important aspects of managing risk for a project is to accurately define the size of the project.
Process to ensure records and reports of competency : Develop a process to ensure records and reports of competency are documented and maintained within the organisation's systems and procedures to inform in future
How do you define financial status : a) What impact, if any, do you think the level of education has on someone financial status? b) How do you define "financial Status"?
Understand the behavior of a dependent variable : Give an example of a scenario where simulation would be effective approach to understand the behavior of a dependent variable.

Reviews

Write a Review

Database Management System Questions & Answers

  Knowledge and data warehousing

Design a dimensional model for analysing Purchases for Adventure Works Cycles and implement it as cubes using SQL Server Analysis Services. The AdventureWorks OLTP sample database is the data source for you BI analysis.

  Design a database schema

Design a Database schema

  Entity-relationship diagram

Create an entity-relationship diagram and design accompanying table layout using sound relational modeling practices and concepts.

  Implement a database of courses and students for a school

Implement a database of courses and students for a school.

  Prepare the e-r diagram for the movie database

Energy in the home, personal energy use and home energy efficiency and Efficient use of ‘waste' heat and renewable heat sources

  Design relation schemas for the entire database

Design relation schemas for the entire database.

  Prepare the relational schema for database

Prepare the relational schema for database

  Data modeling and normalization

Data Modeling and Normalization

  Use cases perform a requirements analysis for the case study

Use Cases Perform a requirements analysis for the Case Study

  Knowledge and data warehousing

Knowledge and Data Warehousing

  Stack and queue data structure

Identify and explain the differences between a stack and a queue data structure

  Practice on topic of normalization

Practice on topic of Normalization

Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd