Q$1.$ Normalize the table below to make sure it satisfies $1$NF$,2$NF and $3$NF$.$ Do not forget to indicate the rules for each normal form $(27$points$).$

$\backslash$table$[[$passenger$,$name,email,flight no$,$departure,destination,date,time,seat no$]]$

Q$2.$ Answer the following questions by writing an SQL statement using the tables below Guest $($guest no$,$ fname, lname$,$ dob, dop, address, city$)$

Book $($ISBN$,$ title, no of pages, publisher, writer$)$

Reservation $($reservation id$,$ date out, date in$,$ guest no$,$ ISBN$)$

a$)$ Display first name, last name and address of all guests where city is same as Alex Bold.

b$)$ Display the guests fname, Iname and the name of the book they took out.

c$)$ Display the number of books each guest took out in total.

d$)$ Write a DDL to create the BOOK table $($You can make assumptions on the data type$).$

e$)$ A new guest came to the library. Use the below details to insert into GUEST table Id: $150$

frame: John

lname: smith

Dob: $01-05-1995$

Dop: London

Address: $10$ High Street

City: London

Q$3.$ What is the difference between delete and truncate.

Q$3.$ What is the purpose of VIEW.

Q$5.$ Explain how does COMMIT, SAVEPOINT and ROLLBACK work in SQL