Reference no: EM132931337

DBAS 4002 - BASIC PROGRAMMABILITY & STORED PROCEDURES

Required Databases:

• Chinook

Assignment Instructions:
It is recommended you start with a fresh restore of the Chinook database, to reduce chance of problems resulting from past database modifications.

Create a single .sql script file containing all SQL statements required to meet all requirements listed below.

Assignment Scenario

The Chinook Music Company has identified some problems with their invoicing system. While their current system tracks the amount of money owed per invoice, it does not account for whether an invoice has been paid or not. In addition, invoice payments are occasionally done in installments, so are not fully paid all at once.

They would like to add functionality to their current database that will improve their invoice accounting and track any financial transactions, so they have a record of any payments received against an invoice. This will lead to better accounting practices, less errors, and therefore greater revenue.

The improvements they would like to add should be robust and protected from unexpected errors... nobody likes losing money due to software glitches!

Requirements

Your solution should add the following to the existing Chinook database:
1. A new column called Paid should be added to the existing Invoice table. It will be marked as True only when an invoice has been fully paid. When a new invoice is created, the new field should always be set to False until full payment has been received.
2. A new table called InvoicePayment will be added. See ERD below.
3. Four new procedures should be added to the database.
a. Each procedure must use transactions where appropriate and include basic error handling. If part of the procedure fails, the entirety of its tasks should be reverted.
b. Proc 1: A procedure named uspAddNewInvoice, which will be used to add new invoice records to the Invoice table. Note: For this assignment, an Invoice will only ever have a single InvoiceLine item associated with it. This proc will receive all values for the new Invoice and its line item and add them as new, related records to the Invoice and InvoiceLine tables. The Invoice Date should be auto-generated by the system, and the Invoice Total field should be calculated (Price * Quantity) instead of passed as a parameter.
c. Proc 2: A procedure named uspAddInvoicePayment, which will be used to add a new record to the new InvoicePayment table. Moving forward, this proc will be used to process new payment entries. The Payment Date should be auto-generated by the system.
d. Proc 3: A procedure called uspRunInvoices. The intent of this procedure is to be a one-time proc that will create invoice payment records for all existing records. (Moving forward, Invoices and Payments will use the other new stored procs.) This procedure will assume all existing Invoice have been paid in full. It will go through every existing Invoice record and use data from the existing Invoice records to create new InvoicePayment records. After gathering the appropriate data for an Invoice record, this procedure will call the uspAddInvoicePayment procedure to add the new Payment records.
e. Proc 4: A procedure called uspCheckPaidInvoices, which will iterate through every Invoice record and compare it to its associated Payment record. If the amount in both records is the same, the procedure will update the Paid field in the Invoice table to True.

Executing Procs & Testing Statements
To ensure the accuracy of your work, your script should include executing calls to the new stored procedures, in the proper order, and include any supporting statements that, when run together, ensure all work is completed to the requirements. Note that during tests that add new test records, hard-coded values are acceptable.
Your testing script should include:
• A statement to add a new Invoice/InvoiceLine record (only one InvoiceLine record is required), using your uspAddNewInvoice procedure.
• A statement to add a new invoice payment for the new Invoice added in Test 1, using your uspAddInvoicePayment procedure.
• A statement to revert ALL Invoice records to unpaid status.
• A statement to remove all existing records from the InvoicePayment table.
• A statement to re-create all InvoicePayment records, using your uspRunInvoices procedure.
• A statement to re-set all applicable Invoices to Paid or not, using your uspCheckPaidInvoices procedure.

Attachment:- PROGRAMMABILITY.rar