Problem 5.1 Consider the AlRLINE relational database schema described below. Schema 1. AIRPORT (Airport_Code. City, State, Name) 2. AIRPLANE_TYPE(Airplane type name, Company, Max_Seats) 3. ALRPLANE(Airplane_id. Total_no_of_seats, Airplane_type_name 2 ) 4. FLIGHT Flight number, Airline, Weekdays) 5. FARE(Code. Elight numbert, Amount, Restrictions) 6. FLIGHT_LEGQhea number. Flight_numbert. Dep_airport_code:. Sched_dep_time. Arr_airport_code', Sched_arr_time) 7. LEG_INSTANCE(Date. Fight_numbert, heg number", Num_avail_seats. Dep_airport_code!, Dep_time, Arr_airport_code 1, Arr_time, Airplane_id? ) 8. SEAT (Seat number. Leg number", Fight number". Datet. Cust_name, Cust_phone) 9. CAN_LAND(Airplane type name? Airpot_Code't ) Description - Each FLIGHT is identified by a Flight_number, and consists of one or more FLIGHT_LEGs with Leg_numbers 1, 2, 3, and so on. - Each FLIGHT_LEG has scheduled arrival and departure times, airports, and one or more LEG_INSTANCEs - one for each Date on which the fight travels. - FAREs are kept for each FLIGHT, - For each FLIGHT_LEG instance, SEAT_RESERVATIONs are kept, as are the AIRPLANE used on the leg and the actual arrival and departure times and airports. - An AIRPLANE is identifed by an Airplane_id and is of a particular AlRPLANE_TYPE. - CAN_LAND relates AIRPLANE_TYPEs to the AIRPORTs at which they can land. - An AIRPORT is identified by an Airport_code. - Consider an update for the AIRLINE database to enter a reservation on a particular flight or fight leg on a given date Questions Write down relational expressions (using the relational algebra operators discussed in class) for the following queries: 1. For each flight, list the fight number, the departure airport for the first leg of the flight, and the arrival airport for the last leg of the flight. 2. List the flight numbers and weekdays of all fights or fight legs that depart from Houston Intercontinental Airport (airport code lah') and arrive in Los Angeles International Airport (airport code "lax'). 3. List the flight number, departure airport code, scheduled departure time, artival airport code, scheduled arrival time, and weekdays of all fights or fight legs that depart from some airport in the city of Houston and arrive at some airport in the city of Los Angeles. 4. List all fare information for flight number 'co19T. 5. Retrieve the number of available seats for fight number "co197' on "2009-10-09. Problem 5.2 Consider a LIBRARY relational database used to keep track of books, borrowers, and book loans schema described below. Schema 1. BOOK(Book id, Title, Publisher_name 3 ) 2. BOOK_AUTHORS(Author_name, Book_id 1 ) 3. PUBLISHER(Name, Address, Phone) 4. BOOK_COPIES(Book_id 1, Branch_id 6, No_of_copies) 5. BOOK_LOANS(Book id 1, Branch id 6, Card no 7, Date_out, Due_date) 6. LIBRARY_BRANCH(Branch_id, Branch_name, Address) 7. BORROWER(Card_no, Name, Address, Phone) Questions Write down relational expressions (using the relational algebra operators discussed in class) for the following queries: 1. How many copies of the book titled 'The Lost Tribe' are owned by the library branch whose name is 'Sharpstown'? 2. How many copies of the book titled 'The Lost Tribe' are owned by each library branch? 3. Retrieve the names of all borrowers who do not have any books checked out. 4. For each book that is loaned out from the 'Sharpstown' branch and whose Due_date is today, retrieve the book title, the borrower's name, and the borrower's address. 5. For each library branch, retrieve the branch name and the total number of books loaned out from that branch. 6. Retrieve the names, addresses, and number of books checked out for all borrowers who have more than five books checked out. 7. For each book authored (or coauthored) by Stephen King, retrieve the title and the number of copies owned by the library branch whose name is Central. Problems 5.3,5.4, and 5.5 This is where you get creative! Based on the schemas described in problems 5.1 and 5.2, make up 3 interesting queries in English, write them and their equivalent relational algebra and label them as 3 distinct numbered questions. You get points for more creative queries! Here's a sample: "You have a one week vacation and are feeling spontaneous. Look for the flight numbers for the 5 cheapest flights departing from San Francisco (SFO) in the next week" Problem 5.1 Consider the AlRLINE relational database schema described below. Schema 1. AIRPORT (Airport_Code. City, State, Name) 2. AIRPLANE_TYPE(Airplane type name, Company, Max_Seats) 3. ALRPLANE(Airplane_id. Total_no_of_seats, Airplane_type_name 2 ) 4. FLIGHT Flight number, Airline, Weekdays) 5. FARE(Code. Elight numbert, Amount, Restrictions) 6. FLIGHT_LEGQhea number. Flight_numbert. Dep_airport_code:. Sched_dep_time. Arr_airport_code', Sched_arr_time) 7. LEG_INSTANCE(Date. Fight_numbert, heg number", Num_avail_seats. Dep_airport_code!, Dep_time, Arr_airport_code 1, Arr_time, Airplane_id? ) 8. SEAT (Seat number. Leg number", Fight number". Datet. Cust_name, Cust_phone) 9. CAN_LAND(Airplane type name? Airpot_Code't ) Description - Each FLIGHT is identified by a Flight_number, and consists of one or more FLIGHT_LEGs with Leg_numbers 1, 2, 3, and so on. - Each FLIGHT_LEG has scheduled arrival and departure times, airports, and one or more LEG_INSTANCEs - one for each Date on which the fight travels. - FAREs are kept for each FLIGHT, - For each FLIGHT_LEG instance, SEAT_RESERVATIONs are kept, as are the AIRPLANE used on the leg and the actual arrival and departure times and airports. - An AIRPLANE is identifed by an Airplane_id and is of a particular AlRPLANE_TYPE. - CAN_LAND relates AIRPLANE_TYPEs to the AIRPORTs at which they can land. - An AIRPORT is identified by an Airport_code. - Consider an update for the AIRLINE database to enter a reservation on a particular flight or fight leg on a given date Questions Write down relational expressions (using the relational algebra operators discussed in class) for the following queries: 1. For each flight, list the fight number, the departure airport for the first leg of the flight, and the arrival airport for the last leg of the flight. 2. List the flight numbers and weekdays of all fights or fight legs that depart from Houston Intercontinental Airport (airport code lah') and arrive in Los Angeles International Airport (airport code "lax'). 3. List the flight number, departure airport code, scheduled departure time, artival airport code, scheduled arrival time, and weekdays of all fights or fight legs that depart from some airport in the city of Houston and arrive at some airport in the city of Los Angeles. 4. List all fare information for flight number 'co19T. 5. Retrieve the number of available seats for fight number "co197' on "2009-10-09. Problem 5.2 Consider a LIBRARY relational database used to keep track of books, borrowers, and book loans schema described below. Schema 1. BOOK(Book id, Title, Publisher_name 3 ) 2. BOOK_AUTHORS(Author_name, Book_id 1 ) 3. PUBLISHER(Name, Address, Phone) 4. BOOK_COPIES(Book_id 1, Branch_id 6, No_of_copies) 5. BOOK_LOANS(Book id 1, Branch id 6, Card no 7, Date_out, Due_date) 6. LIBRARY_BRANCH(Branch_id, Branch_name, Address) 7. BORROWER(Card_no, Name, Address, Phone) Questions Write down relational expressions (using the relational algebra operators discussed in class) for the following queries: 1. How many copies of the book titled 'The Lost Tribe' are owned by the library branch whose name is 'Sharpstown'? 2. How many copies of the book titled 'The Lost Tribe' are owned by each library branch? 3. Retrieve the names of all borrowers who do not have any books checked out. 4. For each book that is loaned out from the 'Sharpstown' branch and whose Due_date is today, retrieve the book title, the borrower's name, and the borrower's address. 5. For each library branch, retrieve the branch name and the total number of books loaned out from that branch. 6. Retrieve the names, addresses, and number of books checked out for all borrowers who have more than five books checked out. 7. For each book authored (or coauthored) by Stephen King, retrieve the title and the number of copies owned by the library branch whose name is Central. Problems 5.3,5.4, and 5.5 This is where you get creative! Based on the schemas described in problems 5.1 and 5.2, make up 3 interesting queries in English, write them and their equivalent relational algebra and label them as 3 distinct numbered questions. You get points for more creative queries! Here's a sample: "You have a one week vacation and are feeling spontaneous. Look for the flight numbers for the 5 cheapest flights departing from San Francisco (SFO) in the next week