1. Write down the appropriate SQL scripts (DDL statements) for creating the database and its

relational model. You can select any of the DBMS you wish.

2. Populate the database you just created again using SQL script file loaded with sample tuples.

(The tables should have enough number of tuples for the SELECT statements, asked in the

5th step, to be run accordingly.)

3. Write down 5 triggers. Triggers should be meaningful.

4. Write down 5 check constraints. Check constraints should be meaningful.

5. Write down the following SQL statements:

a. Write sample INSERT, DELETE and UPDATE statements for 5 of the tables you

choose.

b. Write 10 SELECT statements for the database you have implemented.

i. 3 of them should use minimum 2 tables.

ii. 4 of them should use minimum 3 tables.

iii. 3 of them should use minimum 4 tables.

c. Write 4 SELECT statements to exemplify nested and/or correlated nested queries.

d. Write 2 SELECT statements to exemplify EXISTS and NOT EXISTS statements.

e. Write 3 SELECT statements to exemplify LEFT, RIGHT and FULL OUTER JOIN

statements.

6. Create 5 views that are reasonable.

Restriction Amount Fare_code FARE N Flight number FARES (Comp_name Airline_name Airline_id weekdays N Comp.id Organize Airline 1 FLIGHT COMPANY Contact LEGS N scheduled departure_time Airplane type name Airport code City (Name State leg.no Max seats Air comp N Produces OEPARTURE AIRPORT N Transection record FLIGHT_LEG N AIRPLANE_TYPE CAN LAND AIRPORT HAS ARRIVAL JAPORE Checked physically N FFC TYPE N scheduled arrival time Arrival time Total number of seats an Departure time N AIRPLANE ARRIVES DEPARTS Airplane id 1 SNSTANCE OF Customer N ASSIGNED SEATS LEG_INSTANCE N 1 N Seat_reservation HAVE Number of available seats Date seat number passpord number country adress Customer_namg e-mail Customer phone