CSCI235 Database System

Tasks

Task 1. Stored procedure

Implement a stored PL/SQL procedure APPLICATIONS to list the applicants and their applications.

The names of applicants must be listed in the descending order of last names of applicants. The position number and title of a position applied by an applicant must be
listed in the ascending order of position number.

Execute the stored PL/SQL procedure APPLICATIONS. A fragment of expected sample printout is given below.

16 Zhi Chao Zhong:
2 Johnny Walker:
1 lecturer
6 professor
14 Ivan TheTerrible:
17 Richard TheLionheart:

Implement a solution as PL/SQL stored procedure and save it in SQL script file solution1.sql. Then, process the script and save a report in a file solution1.lst. It is explained in Cookbook, Recipe 2.5 "How to use SQL*Plus client ?", Step 9 how to create and how to save a report from processing of SQL script. Your report must include listing of all SQL statement processed. To achieve that put the following SQL*Plus commands:

SET ECHO ON
SET FEEDBACK ON
SET LINESIZE 100
SET PAGESIZE 100
SET SERVEROUTPUT ON

at the beginning of SQL script solution1.sql.

Deliverables

Submit a file solution1.lst with a report from processing of SQL script solution1.sql. The report MUST have no errors and the report MUST list all SQL statements processed. The report MUST include ONLY PL/SQL statements and control statements that implement and execute the procedure of Task 1 and NO OTHER statements.

Task 2. Stored function

Implement a stored PL/SQL function APPLICANTSKILLS that takes an applicant number (anumber) as a parameter, and finds all the skills possessed by the applicant. The function must return a string of characters that contains the first and last name of an applicant, skill name and level that the applicant possessed.Execute the stored PL/SQL function APPLICANTSKILLS for all applicants.

A fragment of sample printout is given below:

1 Harry Potter: C programming 4 Java programming 9 cooking 9

2 Johnny Walker: Java programming 9 driving 9

3 Mary Poppins: C++ programming 10 Java programming 9 painting 5

4 Michael Collins:

5 Margaret Finch: SQL programming 6

6 Claudia Kowalewski: SQL programming 8

Save your implementation of Task 2 in SQL script file solution2.sql. Then, process the script and save a report in a file solution2.lst. It is explained in Cookbook, Recipe 2.5 "How to use SQL*Plus client ?", Step 9 how to create and how to save a report from processing of SQL script. Your report must include listing of all SQL statement processed. To achieve that put the following SQL*Plus commands:

SET ECHO ON
SET FEEDBACK ON
SET LINESIZE 100
SET PAGESIZE 200
SET SERVEROUTPUT ON at the beginning of SQL script solution2.sql.

Deliverables

Submit a file solution2.lst with a report from processing of SQL script solution2.sql. The report MUST have no errors and the report MUST list all SQL statements processed. The report MUST include ONLY PL/SQL statements and control statements that implement and execute the function of Task 2 and NO OTHER statements.

Task 3 Statement trigger

Implement and comprehensively test a statement trigger that verifies the following consistency constraint. “A position cannot need more than 4 skills”. When ready save your CREATE TRIGGER statement and all SQL statements that comprehensively test a trigger in a script solution3.sql. Comprehensive testing means that the trigger must reject SQL statements that violate the consistency constraint and accept SQL statements that do not violate the consistency constraint. It is a part of your task to find what SQL statements should be tested. Whenever SQL statement violates the consistency constraint a trigger must return ORA-… error message. Use a procedure RAISE_APPLICATION_ERROR to return ORA-… error message. If SQL statement does not violate the consistency constraint then a trigger must return no messages. Process SQL script file solution3.sql and save a report from processing in a file solution3.lst.

Your report must include listing of all SQL statement processed. To achieve that put the following SQL*Plus commands:

SET ECHO ON
SET FEEDBACK ON at the beginning of SQL script solution3.sql.

Deliverables

Submit a file solution3.lst with a report from processing of SQL script solution3.sql. The report MUST have no errors other than reported by a trigger and the report MUST list all SQL statements processed. The report MUST include ONLY SQL statements and comprehensively test statements that implement the specifications of Task 3 and NO OTHER statements.

Task 4 Row trigger

Implement and comprehensively test a row trigger that verifies the following consistency constraint. “An applicant cannot apply for a position during the last 30 days from his/her the previous application for the same position”.

Hint:

No need to consider the UPDATE event. When ready save your CREATE TRIGGER statement and all SQL statements that comprehensively test a trigger in a script solution4.sql. Comprehensive testing means that the trigger must reject SQL statements that violate the consistency constraint and accept SQL statements that do not violate the consistency constraint. It is a part of your task to find what SQL statements should be tested. Whenever SQL statement violates the consistency constraint a trigger must return ORA-… error message. Use a procedure RAISE_APPLICATION_ERROR to return ORA-… error message. If SQL statement does not violate the consistency constraint then a trigger must return no messages.

Process SQL script file solution4.sql and save a report from processing in a file solution4.lst.

Your report must include listing of all SQL statement processed. To achieve that put the following SQL*Plus commands: