Sql query for testing triggers and stored procedures , PL-SQL Programming

Assignment Help:

Description:

Demonstrate your knowledge of PL/SQL programming by writing and thoroughly testing triggers and stored procedures associated with an e-commerce application that provides security logs for all transactions by user, product, and date. I will provide specific requirements and design details for this project below and we could have more discussion about the project in the Conferences area. Submit the scripts with all of your function SQL and PL/SQL code, and provide the results of running your scripts with the SQL*Plus spool command.

You MUST name your file to include your first initial of your first name and last name.
Total is 15 points or 15% to your final grade

Functional requirement:

We will develop a small online transaction application supported by our database, movie distributing and renting system. In the application, we should meet three functional requirements:
1) Track transaction events, and track runtime errors.
2) Handle movie distributing business process.
3) Application interface which allows users to process orders.
4) No additional database tables are required unless you think it is necessary to enhance your application.

Track transaction events, and track runtime errors: 3/15 points

The link below provides a script which allow you to create two tables for tracking purposes One table for tracking events and the other for errors.

Script to create logs tables

Track errors or exceptions during runtime.
Track any error or exception occurs during your application execution. this means that you should include tracking code in every program unit you develop.
Take a look of the table for error logging and see what data should be inserted into the table when error occurs. The code should be part of error handling in exception handle section.
Track event or application process during runtime.
This type of logs for recording the events during the code execution. Content of the log may contain part of the data being changed and who changed it. The code to track events should be located at the end of a process. It is not necessary to log any event that does not alter the database, such as query table and display the content.
Take a look of the table for event logging and see what data should be inserted into these tables after event occurs.

Handle movie distributing business process.
In this part of the project, you are asked to design a few stored procedure or functions to handle online movie distribution.
These procedures or functions should be able to

1)process orders for distributors. When a customer (movie store) places order, ordering information should be stored in database tables.

Customers provide movie title, number of copies they want, the distributed type they prefer(DVD or Video) and store name. Customers do not know movie_id or store_id, therefore, these cannot be used as input parameters for your procedure as customer data.

2)allow customers (movie stores) to check movie availability. if it is available, provide number in stock and unit price. If it is unavailable message the customer the movie requested is not available.

3) if the order has been filled, generate an invoice to send to the customer.

Note: This is one of the place where tracking event is necessary.

Errors and events should be both tracked as described before. Events that cause database change need to be tracked. Any other events which do not cause any database changes are not necessary to be tracked. For example, procedure contains only query activities, tracking event could be ignored.

Application interface which allows users to process either orders: 3/15 points

In general, the interface should provide GUI to users. However, PL/SQL does not have that feature. What we are going to do is to develop script which performs the similar functionality. Essentially calling the procedures and functions you created to complete order.

Script should be able to

1) take an order (ordering data)
2) place an order by calling all the procedures used to process order.
3) generate and print invoice based on order, the invoice should include order id, distributor name, movie title, number of copies, and unit price, and total payment and order completed date.
4) query event_logs and error_logs to show the result of the order transaction and/or any potential issues within the order.

Submit requirement:
The scripts to create procedures and function.
The script to execute the procedures to place orders
The text file for output of execution results.


Related Discussions:- Sql query for testing triggers and stored procedures

Parameter and keyword description - packages, Parameter and Keyword Descrip...

Parameter and Keyword Description: package_name: This construct identifies the package. AUTHID Clause: This determine whether all the packaged subprograms impleme

Explicitly specifying the join condition - sql, Explicitly specifying the j...

Explicitly specifying the join condition - SQL SELECT * FROM IS_CALLED JOIN IS_ENROLLED_ON ON ( IS_CALLED.StudentId = IS_ENROLLED_ON.StudentId ) Now, the key word JO

Sql operators, SQL Operators The PL/SQL uses all the SQL set, compariso...

SQL Operators The PL/SQL uses all the SQL set, comparison, and row operators in the SQL statements. This part briefly describes some of these operators.  1. Comparison Opera

Projection and existential quantification - sql, Projection and Existential...

Projection and Existential Quantification - SQL Intuitively it might seem that projection in SQL is simply a matter of specifying the required columns in the SELECT clause, a

Disjunction - sql, Disjunction (OR, ∨) Again we have nine rows instead...

Disjunction (OR, ∨) Again we have nine rows instead of just four and again, when unknown is not involved, the rows are as for 2VL. Also, when anything is paired with true, t

Check constraints in sql, CHECK Constraints in SQL A CHECK constraint ...

CHECK Constraints in SQL A CHECK constraint is a table constraint defined using the key word CHECK, as already illustrated in several examples in this chapter. In particular,

Updating tables in sql, Updating Tables in SQL The topic of updating b...

Updating Tables in SQL The topic of updating by describing the assignment operator, ":=" in Tutorial D. SQL uses a different syntax for assignment, using the key word SET and

Python function, This task involves developing some functions that extract ...

This task involves developing some functions that extract data from an SQL database. The scenario is that a company which owns an online vehicle search website wants to generate so

Example of unwrap operator - sql, Example of UNWRAP Operator - SQL Exa...

Example of UNWRAP Operator - SQL Example here shows how unwrapping can be done in longhand in SQL. Example: Unwrapping in SQL Letting CONTACT_INFO_WRAPPED denote the res

Fetching from a cursor variable, Fetching from a Cursor Variable The F...

Fetching from a Cursor Variable The FETCH statement retrieve rows one at a time from the product set of a multi-row query. The syntax for the same is as shown: FETCH {curso

Write Your Message!

Captcha
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