Please can someone help me with the solution to this problem and please show me
ID: 3919084 • Letter: P
Question
Please can someone help me with the solution to this problem and please show me proof to show your solution is correct. Thanks for your time.
SET DEFINE OFF;
--Delete preexisting tables
DROP TABLE boats cascade constraints;
DROP TABLE reserves cascade constraints;
DROP TABLE sailors cascade constraints;
--remove any ghost tables
PURGE RECYCLEBIN;
--create tables
CREATE TABLE boats( bid integer,
bname char(20),
color char(20),
PRIMARY KEY (bid));
CREATE TABLE sailors( sid integer,
sname char(30),
rating number,
age number,
PRIMARY KEY (sid));
CREATE TABLE reserves( sid integer,
bid integer,
day date,
PRIMARY KEY (sid,bid,day),
CONSTRAINT FK_BID_RES FOREIGN KEY (bid) REFERENCES boats(bid),
CONSTRAINT FK_SID_RES FOREIGN KEY (sid) REFERENCES sailors(sid));
--insert data into tables
--boats
INSERT INTO boats VALUES (101, 'Interlake', 'blue');
INSERT INTO boats VALUES (102, 'Interlake', 'red');
INSERT INTO boats VALUES (103, 'Clipper', 'green');
INSERT INTO boats VALUES (104, 'Marine', 'red');
commit;
--sailors
INSERT INTO sailors VALUES (22, 'Dustin', 7, '45.0');
INSERT INTO sailors VALUES (29, 'Brutus', 1, '33.0');
INSERT INTO sailors VALUES (31, 'Lubber', 8, '55.5');
INSERT INTO sailors VALUES (32, 'Andy', 8, '25.5');
INSERT INTO sailors VALUES (58, 'Rusty', 10, '35.0');
INSERT INTO sailors VALUES (64, 'Horatio', 7, '35.0');
INSERT INTO sailors VALUES (71, 'Zorba', 10, '16.0');
INSERT INTO sailors VALUES (74, 'Horatio', 9, '35.0');
INSERT INTO sailors VALUES (85, 'Art', 3, '25.5');
INSERT INTO sailors VALUES (95, 'Bob', 3, '63.5');
commit;
--reserves
INSERT INTO reserves VALUES (22, 101, TO_DATE('1998-10-10','YYYY-MM-DD'));
INSERT INTO reserves VALUES (22, 102, TO_DATE('1998-10-10','YYYY-MM-DD'));
INSERT INTO reserves VALUES (22, 103, TO_DATE('1998-10-08','YYYY-MM-DD'));
INSERT INTO reserves VALUES (22, 104, TO_DATE('1998-10-07','YYYY-MM-DD'));
INSERT INTO reserves VALUES (31, 102, TO_DATE('1998-11-10','YYYY-MM-DD'));
INSERT INTO reserves VALUES (31, 103, TO_DATE('1998-11-06','YYYY-MM-DD'));
INSERT INTO reserves VALUES (31, 104, TO_DATE('1998-11-12','YYYY-MM-DD'));
INSERT INTO reserves VALUES (64, 101, TO_DATE('1998-09-05','YYYY-MM-DD'));
INSERT INTO reserves VALUES (64, 102, TO_DATE('1998-09-08','YYYY-MM-DD'));
INSERT INTO reserves VALUES (74, 103, TO_DATE('1998-09-08','YYYY-MM-DD'));
commit;
Run the schema above and use it to answer the following question:
Part B: Transaction Processing:
In this section, you will open TWO separate terminals:
Login in to the Linux Oracle server and access the database using sqlplus in each of the two terminals•
Display the SQL commands using: SET ECHO ON for each of the two terminals•
To enable manual commit for changes: SET AUTOCOMMIT OFFfor each of the two terminals•
Create transaction1 log file using in 1stterminal:
SPOOL 4-FirstLastName-Lab_T1.txt•Create transaction2 log file using in 2ndterminal:
SPOOL 4-FirstLastName-Lab_T2.txt
questions:
a. (5points) What happens after step 2 and why?
b. (5points) What happens after step 3 and why?
c. (5points) What happens after step 7and why?
d. (5points) What happens afterstep 12and why?
e. (5points) Explain the results(wait to see) of steps 13 to 16and why?
f. (5 points) What happens after step 17and why?
g. (5 points) What happens after step 21and why?
h. (5 points) What happens after step 24and why?
1. Execute the following statements and observe Terminal 1 Terminal 2 1 delete from reserves where sid'74 2 3 commit 4 5insert into boats values (105, 'Nautica', white); delete from reserves where sid -'74" comm1 insert into boats values (106, Seagull', 'yellow); 7 select * from boats; select* from boats rollback; select * from boats; 10 11 commit; 12 13 insert into sailors values (97, Hook', 10, '55.0 select * from boats insert into boats values (107, Lark', 'gray'): insert into sailors values (97, Billy', 8, '27.5'); 15 insert into boats values (107, 'Raven', 'black 16 17 rollback; 18 select from sailors 19 select from boats 20 21 select * from sailors; select * from boats; commit select * from sailors; select * from boats; 23 24 a. (5 points) What happens after step 2 and why? b. (5 points) What happens after step 3 and why?Explanation / Answer
Answer A:
records with sid 74 in the reservations table will get deleted
Answer B: changes will be commited to the DB
Answer C:disaplays the newly inserted records from the tables and once you rollback all these changes will lost as we did not commit and next select will not display newly inserted records
Answer D : displays values from the boats and few insert statements will execute
Answer E: Boats and sailors table got inserted with few records
Answer F:rollback will get executed and newly inserted records will lost
Answer G:newly inserted data will be dispalyed
Answer H: commit to the DB and display the records