Specification
The implementation task is to use the database tables found in the TPCHR workbench.
Tasks
Task 1: PL/SQL Function
Implement a stored PL/SQL function LISTNATION that finds the names of nations of a region specified by a region name. The function must return a string of characters that contains the region key, region name and all nation names. All names of nations must be separated by commas (,).
Execute the stored PL/SQL function LISTNATION. A fragment of sample printout is given below:
0 AFRICA: ALGERIA, ETHIOPIA, KENYA, MOROCCO, MOZAMBIQUE,
1 AMERICA: ARGENTINA, BRAZIL, CANADA, PERU, UNITED STATES,
2 ASIA: CHINA, INDIA, INDONESIA, JAPAN, VIETNAM,
3 EUROPE: FRANCE, GERMANY, ROMANIA, RUSSIA, UNITED KINGDOM,
4 MIDDLE EAST: EGYPT, IRAN, IRAQ, JORDAN, SAUDI ARABIA,
When finish the implementation and testing, execute an SQL script solution1.sql and record the results of processing in a file solution1.lst.
Deliverables
Submit a file solution1.lst with a report from the processing of SQL script solution1.sql. The report MUST have no errors the report MUST list all SQL statements processed. The report MUST include ONLY SQL statements and control statements that implement the specifications of Task 1 and NO OTHER statements.
Task 2
Implement a stored PL/SQL procedure number of suppliers that takes in as parameter an integer number X, where X is a number user will specify. The procedure is to list the name of the region, the name of the nation, and the number of suppliers in that nation such that the number of suppliers in a nation is greater than X. For example, the execution of the procedure in the following way will produce the result as shown:
Deliverables
Submit a file solution2.lst with a report from the processing of SQL script solution2.sql. The report MUST have no errors the report MUST list all SQL statements processed. The the report MUST include ONLY SQL statements and control statements that implement the specifications of Task 2 and NO OTHER statements.
Assignment 2
Scope:
The tasks of this assignment cover topics on PLSQL as well as Transaction
processing and concurrency.
Assessment criteria:
Marks will be awarded for:
- Correct,
- Comprehensive, and
- Appropriate
application of the materials covered in this subject.
Task 1
PL/SQL block
Implement PL/SQL block that modifies the prices of books and videos according to the following rules:
– if a product belongs to a category ‘book’ and it has not been purchased in the last 30 days then decrease its price by 3%,
– if a product belongs to a category ‘video’ and it has been purchased less than 5 times in the last 20 days then decrease its price by 5%.
To test your PL/SQL block implement SQL script that displays the numbers and prices of products included in Product table. Execute the PL/SQL block and displays the numbers and prices for the second time.
Deliverables
Hand in an SQL script and the report from execution of the script. The report must have no errors related to the implementation of your task and it must list all PL/SQL and SQL statements processed.
Remember to set ECHO option of SQL*Plus to ON!
Task 2
PL/SQL Procedure
Create a table UserChoice(p#, price) to store information about products (number and price) selected by a user. Leave the table UserChoice empty when first created.
Implement a stored PL/SQL procedure FindProducts(category, keyword) thatfinds all products categorized by the input parameter category and described by the input parameter keyword. For example, find products(‘book’, ‘database’) will find all books that are described or related to ‘database’. The procedure is to store the numbers and prices of all selected products in the UserChoice table. Consider the following categories of products – book, video, and cdrom when you test your procedure.
To test your procedure, (i) implement SQL script that displays the contents of empty UserChoice table, (ii) execute CREATE OR REPLACE PROCEDURE statement to store FindProducts in a data dictionary, and (iii) execute a stored
procedure FindProducts again to display the contents of the nonempty UserChoice table.
Deliverables
Hand in the SQL script and the report from the execution of scripts. The report must have no errors related to the implementation of your task and it must list all PL/SQL and SQL statements processed.
Remember to set ECHO option of SQL*Plus to ON!
Task 3
Stored trigger
Implement a database trigger such that for each row that is inserted into Pbasket table, the trigger activates a verification process to verify a value of credit card number attribute. Whenever a credit card number that is included in a new row has been already used by another customer, your trigger should abort the attempted insert operation.
To test your trigger, execute SQL script that contains CREATE OR REPLACE TRIGGER statement, INSERT statement to insert a row into Pbasket table with a new credit card number, and INSERT statement to insert another row into Pbasket table with a credit card number already used by another customer.
Deliverables
Hand in the SQL script and the report from the execution of scripts. Remember to set the ECHO option of SQL*Plus to ON
