Implement in PL/SQL the following database transactions to demonstrate phantom phenomenon. The first transaction supposed to list the values of an applicant 000002 first name, last name, and the total number of positions the applicant applies for. Then, in the next step, the transaction supposed to increase salary 10% for a position 00000005. Finally, the transaction supposed to list again the values of applicant 000002 first name, last name, and the total number of positions the applicant applies for. The second transaction supposed to insert a new position 00000008 into the database. The values of the new position are up to you. The new position needs two skills: COOKING at level 3 and PAINTING at level 4. Finally the transaction inserts a new application for an applicant 000002 applies the new position 00000008. Both transactions supposed to run at READ COMMITTED isolation level. Save the transactions in the files solution1-1.sql and solution1-2.sql. Simulate a concurrent execution of the transaction such that it will reveal phantom phenomenon. To simulate a concurrent execution of the transactions use a PL/SQL procedure SLEEP from the standard PL/SQL package DBMS_LOCK. By "simulation of concurrent execution" we mean that the first transaction does a bit of work, then it is delayed for a certain period of time and in the same period of time another transaction is processed. Finally, after a delay the first transaction completes its job. A script file delay.sql shows a sample application of a procedure SLEEP. Explain as the comments included at the end of a file solution1-1.sql why a phantom phenomenon has occurred in the execution of one of the transactions. Save the reports from the execution of the transactions in the files solution1-1.lst and solution1-2.lst with SQL*Plus options ECHO and FEEDBACK set to ON. The files solution1-1.lst and solution1-2.lst will be submitted.

db.create

SET ECHO ON /* ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ */ /* Lookup tables */ /* ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ */

CREATE TABLE LPTitle( title VARCHAR(30) NOT NULL, /* Position title */ CONSTRAINT LPTitle_pkey PRIMARY KEY ( title ) );

INSERT INTO LPTitle VALUES( 'ASSISTANT' ); INSERT INTO LPTitle VALUES( 'ASSOC. LECTURER' ); INSERT INTO LPTitle VALUES( 'LECTURER' ); INSERT INTO LPTitle VALUES( 'SENIOR LECTURER' ); INSERT INTO LPTitle VALUES( 'ASSOC. PROFESSOR' ); INSERT INTO LPTitle VALUES( 'PROFESSOR' );

CREATE TABLE LState( state VARCHAR(3) NOT NULL, /* State name */ CONSTRAINT LState_pkey PRIMARY KEY ( state ) );

INSERT INTO LState VALUES ( 'NSW' ); INSERT INTO LState VALUES ( 'Vic' ); INSERT INTO LState VALUES ( 'SA' ); INSERT INTO LState VALUES ( 'ACT' ); INSERT INTO LState VALUES ( 'WA' ); INSERT INTO LState VALUES ( 'Que' ); INSERT INTO LState VALUES ( 'NT' ); INSERT INTO LState VALUES ( 'Tas' );

CREATE TABLE LSkill( skill VARCHAR(30) NOT NULL, /* Skill name */ CONSTRAINT LSkill_pkey PRIMARY KEY ( skill ) );

INSERT INTO LSkill VALUES ( 'C++ PROGRAMMING' ); INSERT INTO LSkill VALUES ( 'C PROGRAMMING' ); INSERT INTO LSkill VALUES ( 'C# PROGRAMMING' ); INSERT INTO LSkill VALUES ( 'JAVA PROGRAMMING' ); INSERT INTO LSkill VALUES ( 'DATABASE DESIGN' ); INSERT INTO LSkill VALUES ( 'SQL PROGRAMMING' ); INSERT INTO LSkill VALUES ( 'PYTHON PROGRAMMING' ); INSERT INTO LSkill VALUES ( 'DATABASE ADMIN.' ); INSERT INTO LSkill VALUES ( 'PAINTING' ); INSERT INTO LSkill VALUES ( 'COOKING' ); INSERT INTO LSKILL VALUES ( 'TRUCK DRIVING' ); INSERT INTO LSkill VALUES ( 'NETWORK ADMIN.' ); INSERT INTO LSkill VALUES ( 'INFORMATION SYST. ADMIN' );

/* ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ */ /* Database tables */ /* ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ */

CREATE TABLE Applicant( a# NUMBER(6) NOT NULL, /* Staff number */ fname VARCHAR(20) NOT NULL, /* First name */ lname VARCHAR(30) NOT NULL, /* Last name */ address VARCHAR(50) NOT NULL, /* Street, home number, etc. */ city VARCHAR(30) NOT NULL, /* City */ state VARCHAR(20) NOT NULL, /* State */ phone# NUMBER(10) NOT NULL, /* Phone number */ fax# NUMBER(10), /* Fax number */ email VARCHAR(50), /* E-mail address */ acomment VARCHAR2(1000), /* Interesting comments from interviews */ CONSTRAINT Applicant_pkey PRIMARY KEY ( a# ), CONSTRAINT Applicant_fkey1 FOREIGN KEY ( state ) REFERENCES LState ( state ) );

INSERT INTO Applicant VALUES ( 000001, 'PETER', 'JONES', '7 STATION ST.', 'PERTH', 'WA', 645278453, NULL, NULL, 'Job expectations ? Money, money, money, ...' ); INSERT INTO Applicant VALUES ( 000002, 'JOHN', 'BLACK', '23 VICTORIA ST.', 'GEELONG', 'Vic', 63569784, 63569785, 'blunder@hotmail.com', 'Microsoft Access ? Oh yeah, kindergarten database system' ); INSERT INTO Applicant VALUES ( 000003, 'MARY', 'WHITE', '453 RITCHIE CR.', 'MELBOURNE', 'Vic', 62389541, NULL, 'mary@yahoo.com', 'Hard work ? You must be kidding !' ); INSERT INTO Applicant VALUES ( 000004, 'MICHAEL', 'COLLINS', '12 GREEN PL.', 'BRISBANE', 'Que', 63336666, NULL, 'mike@hotmail.com', 'Java ? My way !!!' ); INSERT INTO Applicant VALUES ( 000005, 'MARGARET', 'FINCH', '45 ANZAC PDE.', 'SYDNEY', 'NSW', 64573489, NULL, NULL, 'Microsoft ? Hmmm, does it still exist ?' ); INSERT INTO Applicant VALUES ( 000006, 'GARY', 'KASPAROW', '99 CHESS ST.', 'PERTH', 'WA', 645278434, NULL, NULL, 'C++ ? Check mate !' ); INSERT INTO Applicant VALUES ( 000007, 'CLAUDIA', 'HOFFMAN', '23 STATION ST.', 'HOBART', 'Tas', 64577744, NULL, 'cch@cs.odmg.org', 'Databases ? Oh yes, yes ! I like databases in Strogonoff sauce with a bit of rice and mushroom salad' ); INSERT INTO Applicant VALUES ( 000008, 'STEPHEN', 'STAUNTON', '23 HUXLEY AVE.', 'ALICE SPRINGS', 'WA', 623778453, NULL, NULL, 'The best project ? Hello world !' ); INSERT INTO Applicant VALUES ( 000009, 'JOSEPH', 'STAUNTON', '23 HUXLEY AVE.', 'ALICE SPRINGS', 'WA', 623778453, NULL, NULL, 'The favorite programming technique ? Shareware !' ); INSERT INTO Applicant VALUES ( 0000010, 'JANUSZ', 'GETTA', '10 ELLENBOROUGH PL', 'DAPTO WEST', 'NSW', 642214339, NULL, 'jrg@cs.uow.edu.au', 'Software Engineering ? You can''t be serious !' );

/* ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ */ CREATE TABLE SPossessed( a# NUMBER(6) NOT NULL, /* Applicant number */ sname VARCHAR(30) NOT NULL, /* Skill name */ skilllevel NUMBER(2) NOT NULL, /* Skill level */ CONSTRAINT SPossessed_pkey PRIMARY KEY ( a#, sname ), CONSTRAINT SPossessed_fkey1 FOREIGN KEY ( a# ) REFERENCES Applicant ( a# ) ON DELETE CASCADE, CONSTRAINT SPossessed_fkey2 FOREIGN KEY ( sname ) REFERENCES LSkill ( skill ), CONSTRAINT SPossessed_check1 CHECK ( skilllevel IN ( 1,2,3,4,5,6,7,8,9,10 ) ) );

INSERT INTO SPossessed VALUES ( 000001, 'JAVA PROGRAMMING', 9 ); INSERT INTO SPossessed VALUES ( 000001, 'C PROGRAMMING', 4 ); INSERT INTO SPossessed VALUES ( 000001, 'COOKING', 9 ); INSERT INTO SPossessed VALUES ( 000002, 'JAVA PROGRAMMING', 7 ); INSERT INTO SPossessed VALUES ( 000002, 'PYTHON PROGRAMMING', 7 ); INSERT INTO SPossessed VALUES ( 000002, 'TRUCK DRIVING', 9 ); INSERT INTO SPossessed VALUES ( 000003, 'C++ PROGRAMMING', 10 ); INSERT INTO SPossessed VALUES ( 000003, 'JAVA PROGRAMMING', 9 ); INSERT INTO SPossessed VALUES ( 000003, 'PAINTING', 5 ); INSERT INTO SPossessed VALUES ( 000008, 'COOKING', 9 ); INSERT INTO SPossessed VALUES ( 0000010, 'COOKING', 1 ); INSERT INTO SPossessed VALUES ( 0000010, 'PAINTING', 9 ); INSERT INTO SPossessed VALUES ( 0000010, 'SQL PROGRAMMING', 10 ); INSERT INTO SPossessed VALUES ( 000005, 'SQL PROGRAMMING', 6 ); INSERT INTO SPossessed VALUES ( 000006, 'SQL PROGRAMMING', 8 ); INSERT INTO SPossessed VALUES ( 000007, 'SQL PROGRAMMING', 9 ); INSERT INTO SPossessed VALUES ( 000007, 'C# PROGRAMMING', 7 ); INSERT INTO SPossessed VALUES ( 000007, 'PYTHON PROGRAMMING', 8 ); INSERT INTO SPossessed VALUES ( 000008, 'SQL PROGRAMMING', 3 ); INSERT INTO SPossessed VALUES ( 0000010, 'DATABASE DESIGN', 10 ); INSERT INTO SPossessed VALUES ( 000007, 'COOKING', 10 );

/* ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ */ CREATE TABLE Position( p# NUMBER(8) NOT NULL, /* Position number */ ptitle VARCHAR(30) NOT NULL, /* Position title */ employer VARCHAR(100) NOT NULL, /* Institution name */ salary NUMBER(9,2) NOT NULL, /* Salary */ extras VARCHAR(50), /* Extras */ specification VARCHAR2(500), /* Specification */ CONSTRAINT Position_pkey PRIMARY KEY ( p# ), CONSTRAINT Position_fkey1 FOREIGN KEY ( ptitle ) REFERENCES LPTitle ( title ) );

INSERT INTO Position VALUES ( 00000001, 'LECTURER', 'UNSW', 45000.00, 'computer', 'Teaching'); INSERT INTO Position VALUES ( 00000002, 'LECTURER', 'UOW', 450000.00, 'mouse pad', 'Research'); INSERT INTO Position VALUES ( 00000003, 'SENIOR LECTURER', 'UTS', 50000.00, NULL, 'A lot of hard work' ); INSERT INTO Position VALUES ( 00000004, 'ASSOC. PROFESSOR', 'UOW', 60000.00, NULL, NULL); INSERT INTO Position VALUES ( 00000005, 'PROFESSOR', 'UQ', 80000.00, 'chair', 'Research' ); INSERT INTO Position VALUES ( 00000006, 'PROFESSOR', 'UNSW', 80000.00, 'chair', 'Research' ); INSERT INTO Position VALUES ( 00000007, 'PROFESSOR', 'UOW', 80000.00, 'chair', 'Teaching and research');

/* ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ */ CREATE TABLE SNeeded( p# NUMBER(8) NOT NULL, /* Position number */ sname VARCHAR(30) NOT NULL, /* Skill name */ skilllevel NUMBER(2) NOT NULL, /* Skill level */ CONSTRAINT SNeeded_pkey PRIMARY KEY ( p#, sname ), CONSTRAINT SNeeded_fkey1 FOREIGN KEY ( p# ) REFERENCES Position ( p# ) ON DELETE CASCADE, CONSTRAINT SNeeded_fkey2 FOREIGN KEY ( sname ) REFERENCES LSkill ( skill ), CONSTRAINT SNeeded_check1 CHECK ( skilllevel IN ( 1,2,3,4,5,6,7,8,9,10 ) ) );

INSERT INTO SNeeded VALUES ( 00000001, 'JAVA PROGRAMMING', 8 ); INSERT INTO SNeeded VALUES ( 00000001, 'COOKING', 9 ); INSERT INTO SNeeded VALUES ( 00000002, 'JAVA PROGRAMMING', 3 ); INSERT INTO SNeeded VALUES ( 00000002, 'TRUCK DRIVING', 9 ); INSERT INTO SNeeded VALUES ( 00000003, 'C++ PROGRAMMING', 7 ); INSERT INTO SNeeded VALUES ( 00000003, 'JAVA PROGRAMMING', 10 ); INSERT INTO SNeeded VALUES ( 00000003, 'PAINTING', 4 ); INSERT INTO SNeeded VALUES ( 00000005, 'COOKING', 10 ); INSERT INTO SNeeded VALUES ( 00000001, 'C PROGRAMMING', 4 ); INSERT INTO SNeeded VALUES ( 00000006, 'COOKING', 2 ); INSERT INTO SNeeded VALUES ( 00000005, 'PAINTING', 6 ); INSERT INTO SNeeded VALUES ( 00000007, 'SQL PROGRAMMING', 10 ); INSERT INTO SNeeded VALUES ( 00000004, 'SQL PROGRAMMING', 6 ); INSERT INTO SNeeded VALUES ( 00000005, 'SQL PROGRAMMING', 3 ); INSERT INTO SNeeded VALUES ( 00000007, 'PAINTING', 5 ); INSERT INTO SNeeded VALUES ( 00000001, 'SQL PROGRAMMING', 3 ); INSERT INTO SNeeded VALUES ( 00000002, 'DATABASE DESIGN', 10 ); INSERT INTO SNeeded VALUES ( 00000007, 'COOKING', 8 );

/* ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ */ CREATE TABLE Applies( a# NUMBER(6) NOT NULL, /* Applicant number */ p# NUMBER(8) NOT NULL, /* Position number */ appdate DATE NOT NULL, /* Application date */ CONSTRAINT Applies_pkey PRIMARY KEY ( a#, p# ), CONSTRAINT Applies_fkey1 FOREIGN KEY ( a# ) REFERENCES Applicant ( a# ) ON DELETE CASCADE, CONSTRAINT Applies_fkey2 FOREIGN KEY ( p# ) REFERENCES Position ( p# ) ON DELETE CASCADE );

INSERT INTO Applies VALUES( 000001, 00000001, TO_DATE('13-DEC-2017','DD-MON-YYYY') ); INSERT INTO Applies VALUES( 000002, 00000001, TO_DATE('13-DEC-2017','DD-MON-YYYY') ); INSERT INTO Applies VALUES( 000003, 00000002, TO_DATE('14-NOV-2017','DD-MON-YYYY') ); INSERT INTO Applies VALUES( 000004, 00000002, TO_DATE('20-JAN-2018','DD-MON-YYYY') ); INSERT INTO Applies VALUES( 000005, 00000002, TO_DATE('22-JAN-2018','DD-MON-YYYY') ); INSERT INTO Applies VALUES( 000005, 00000003, TO_DATE('09-MAY-2017','DD-MON-YYYY') ); INSERT INTO Applies VALUES( 000006, 00000003, TO_DATE('17-JUN-2017','DD-MON-YYYY') ); INSERT INTO Applies VALUES( 000007, 00000003, TO_DATE('18-JUN-2017','DD-MON-YYYY') ); INSERT INTO Applies VALUES( 000007, 00000004, TO_DATE('13-APR-2017','DD-MON-YYYY') ); INSERT INTO Applies VALUES( 000008, 00000004, TO_DATE('13-APR-2017','DD-MON-YYYY') ); INSERT INTO Applies VALUES( 000009, 00000004, TO_DATE('14-APR-2017','DD-MON-YYYY') ); INSERT INTO Applies VALUES( 000010, 00000005, TO_DATE('23-SEP-2017','DD-MON-YYYY') ); INSERT INTO Applies VALUES( 000001, 00000006, TO_DATE('26-OCT-2017','DD-MON-YYYY') ); INSERT INTO Applies VALUES( 000002, 00000006, TO_DATE('27-OCT-2017','DD-MON-YYYY') ); INSERT INTO Applies VALUES( 000003, 00000006, TO_DATE('28-OCT-2017','DD-MON-YYYY') ); INSERT INTO Applies VALUES( 000004, 00000007, TO_DATE('01-JAN-2018','DD-MON-YYYY') ); INSERT INTO Applies VALUES( 000005, 00000007, TO_DATE('03-JAN-2018','DD-MON-YYYY') ); INSERT INTO Applies VALUES( 000006, 00000007, TO_DATE('04-JAN-2018','DD-MON-YYYY') ); INSERT INTO Applies VALUES( 000007, 00000007, TO_DATE('07-JAN-2018','DD-MON-YYYY') );

COMMIT; /* ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ */ /* End of script */ /* ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ */

db.drop

SET ECHO ON DROP TABLE Applies PURGE;

DROP TABLE SNeeded PURGE;

DROP TABLE Position PURGE;

DROP TABLE SPossessed PURGE;

DROP TABLE Applicant PURGE;

DROP TABLE LPTitle PURGE;

DROP TABLE LState PURGE;

DROP TABLE LSkill PURGE;

/* ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ */ /* End of script */ /* ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ */

delay

SET SERVEROUTPUT ON

/* Note, that SQL*Plus does not display DBMS_OUTPUT messages until the PL/SQL program completes. There is no mechanism for flushing the DBMS_OUTPUT buffers within the PL/SQL program. */

DECLARE SYSTS VARCHAR(60);

BEGIN SELECT TO_CHAR(SYSTIMESTAMP) INTO SYSTS FROM DUAL;

DBMS_OUTPUT.PUT_LINE(SYSTS);

DBMS_LOCK.SLEEP(10); /* Sleep for 10 seconds */

SELECT TO_CHAR(SYSTIMESTAMP) INTO SYSTS FROM DUAL;

DBMS_OUTPUT.PUT_LINE(SYSTS); END; /