Consider a flight reservation system which allows customers/travelers to book their flight online. - A passenger is entitled to only one seat per flight. - A passenger is required to register his/her luggage (if heavier than 70kg ) during reservation. - A passenger can book for more than one flight if and only if the following condition holds; the take-off time for each flight in the flight-sequence is greater than or equal to the arrival time of the immediate previous flight AND the take-off airport of each flight is the destination airport of the immediate previous flight in the sequence. - A flight is attached to one and only one air route. - A flight is attached to only one airplane, but a route can be navigated by more than one airplane. - Every flight has a take-off airport, one or more optional stopover airport(s) and a destination airport a. Create an ER model for the system with clearly shown cardinalities and relationships. You can use either the Crow's foot or Chen model and you can state any reasonable assumptions made outside the ones provided. ( 6 marks) b. Write a conceptual schema for the ER model designed above and normalize your database to at least 2NF. (4 marks) a. Determine whether the following schedules are: i) conflict serializable ii) view serializable iii) recoverable iv) avoid cascade abort v) strict. 1. T1:W(X),T2:R(Y),T1:R(Y),T2:R(X) 2. T2: R(X),T3:W(X),T3: Commit, T1: W(Y),T1: Commit, T2: R(Y),T2:W(Z),T2: Commit (5 marks) b. Discuss the methods for deadlock prevention in Database management systems. (5 marks) a. Answer each of the following questions briefly. Using the relational schema: Emp(eid: integer, ename: string, age: integer, salary: real) Works(eid: integer, did: integer, pct time: integer) Dept(did: integer, dname: string, budget: real, managerid: integer) i. Give an example of a foreign key constraint that involves the Dept relation. What are the options for enforcing this constraint when a user attempts to delete a Dept tuple? (2 marks) ii. Write the SQL statements required to create the above relations, including primary and foreign key integrity constraints. (2 marks) iii. Define the Dept relation in SQL so that every department is guaranteed to have a manager. (2 marks) iv. Write an SQL statement to add 'John Doe' as an employee with eid =101, age =32 and salary =15; 000. (2 marks) y. Write an SQL statement to give every employee a 10% raise. ( 2 marks)