Problem 11. Deadlock (15 points)

Watch this video https://www.youtube.com/watch?v=JitJRb5x9vI and simulate a dead lock on Oracle Developer. Present following supporting SQL, document and screen shots:

SQL to create and load table

SQL to simulate the dead lock process.

Document the sequence of actions how deadlock is generated.

Deadlock ORA-00060 message

Problem 12. Trigger (15 points)

A flight log database contains two tables, PILOT and FLIGHT_LOG as shown below. The PILOT table stores pilot data such as pilot id, name, etc. The FLIGHT_LOG table records the number of flight hours each pilot logged after flying a specific aircraft. The pilot_id field PILOT table is PK, the log_id in the FLIGHT_LOG table is also PK, the pilot_id field in FLIGHT_LOG table is a foreign key referencing to the pilot_id field in the PILOT table.

PILOT

pilot_id (PK)	pilot_fname	pilot_lname	hire_date	total_hours
1000	George	Kolmycz	March 2014	2200
2001	Rhonda	Lewis	April 2012	3500
3030	Rhett	Vandam	August 2013	0
3050	Anne	Jones	March 2014	4000

FLIGHT_LOG

log_id (PK)	pilot_id (FK)	aircraft	hours
101	1000	PA23-250	700
102	1000	C-90A	1000
103	1000	PA31-350	500
201	2001	PA23-250	1500
202	2001	C-90A	2000
403	3050	PA31-350	2500
404	3050	PA23-250	500
405	3050	C-90A	1000

Write the SQL to create table PILOT and FLIGHT_LOG with PK (pilot_id in PILOT, log_id in FLIGHT_LOG)

Write SQL to populate table PILOT and FLIGHT_LOG.

Write SQL to alter table FLIGHT_LOG set pilot_id as FK with respect to pilot_id in PILOT table

Write trigger or triggers to update total_hours in PILOT table whenever there is an INSERT, UPDATE or DELETE on FLIGHT_LOG table.

Test the trigger with all DML (insert, update, and delete). Post your screen shots here.