Sailors (S ID, S name, S rating, S Age) Boats (B_ID, B_name, B_color) Reservatio

Question

Sailors (S ID, S name, S rating, S Age) Boats (B_ID, B_name, B_color) Reservations (S ID, B ID, R_date) 1. Execute the following statements and observe: Terminal Terminal 1delete from reserves where sid- 74 3 comit insert into In this section, you will open TWO separate terminals: imsert imto boats values (106,Seagull, yellow select elect rol select from boats 13 insert into sailors values (97. Hook. 10 '55. 15 17 rollback 19 selectfrom insert into boats values (10 insert into sailors values (97, Billy, 8 select from sailors elect Login in to the Linux Oracle server and access the database using sqlplus in each of the 23 24 select from sailors select from boats two terminals Display the SQL commands using: SET ECHO ON for each of the two terminals To enable manual commit for changes: SET AUTOCOMMIT OFF for each of the two a. (5 points) What happens after step 2 and why? b. (5 points) What happens after step 3 and why? C. (5 points) What happens after step 7 and why? d. (5 points) What happens after step 12 and why? e. (5 points) Explain the results (wait to see) of steps 13 to 16 and why? f. (5 points) What happens after step 17 and why? g. (5 points) What happens after step 21 and why? h. (5 points) What happens after step 24 and why? terminals Create transaction1 log file using in 1st terminal: SPOOL Create transaction 2 log file using in 2nd terminal: SPOOL 4-FirstLastName-Lab_T2.txt T1.txt

Explanation / Answer

a) After step 2, Terminal 2 shows a message that the record id deleted but Terminal 1 too issued the same command, but didnt commit so the changes were still temp for Terminal1. Because of this Terminal2 could see the record with sid = '74' and it too tries to delete, but again the changes are temp in Terminal2 session and for T2 it shows successfully deleted. So both for T1 and T2 the deletion changes are temp and both sees succcess message in their sessions but no one commits it permanently.

b) After Step 3 executes, T1 deletion changes are made permanent in DB, the record is deleted forever via commit stmt. Now when T2 issues commit, T2 will get an error message saying no such row exists because the row T2 selected for deletion is now permanently removed from DB physically. So when T1 issued commit, it executed, but when T2 issues commit it will throw error because the row selected for deletion is not found.

c) At step 5, T1 inserts a record again no commit so only T1 can see the newly inserted record as the changes are still temporary for T1 session. At step 6, T2 inserts a record into boats table again no commit so the record is temp in T2 session. After Step 7 executes, T1 can only see the newly created row that T1 inserted and not T2 newly inserted row as none has committed. Similarly T2 sees only his newly inserted row and not T1 new records as commit is not issued.

d) At step 9, T2 issues rollback so both T1 and T2 newly created records are flushed out. So no new records are inserted by either T1 or T2. Step 10, T2 does select * from boats, so no new records as T2 did a rollback. Step 11, T1 does a commit, so no new changes in DB, as both new records of T1 and T2 are already lost. After Step 12, T2 sees the same boat records, with no changes.

e) From Step 13 to Step 16, none of the newly created records of T1 and T2 are persisted permanently in DB. Step 13, t1 inserts a new record but it stemp to T1 session. Step 14, T2 inserts a new record but that too is temporary. Now Step 15, T1 inserts a new record into boats with the same Primary Key as T2 did earlier. Surprisingly both will be able to save the records with same PK as both are running private session. For T1 session, the PK is new so insert works, even for T2 session the PK is new so insert works. Hence, Step 15 is executed luckily for T1 and T2 hasnt committed the transaction. Same is Step 16, T2 inserts a new record into sailors Table with same PK as T1, but both are able to insert records in their private sessions, so no Primary Key Constraint Violation takes place.

Please let me know in case of any clarifications required. Thanks!

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.