CS561WS Homework 4 Solutions Stevens Institute Of Technology CS 561

Week 4 Homework Solutions All questions relate to the Airline Database that was the subject of HW1 In some cases lines from ’s or ’s to their subscripts don’t show up unless you view this document in Print Layout. Be sure to develop complex queries in stages, using strategies written down in English – as is done in Lecture 4; without them I won’t be able to understand what you’ve done – and you won’t be able to understand if you come back to them after they’ve been graded. (Hint: this means that grades on complex queries will depend on use of clearly written strategies.) Write each of the following queries in un-extended relational algebra. That is, use only , , X,  and . 1. Find every flight for which there are no flight legs listed in the database. FLIGHT.numberFLIGHT) - FLIGHT_LEG.flight_numberFLIGHT_LEG) 2. Find the flight(s) that have the highest fare(s). SHOULD BE LOWER.AMOUNT and HIGHER.AMOUNT (set of all flights) - (set of flights that don’t have the highest fare) = (set of all flights) - (set of flights such that there’s a flight with a higher fare) NUMBER(FLIGHT) - [LOWER(FARES) X HIGHER(FARES)))] LOW ER.flight_number LOW ER.dnumber HIGHER.dnumber 3. Find every type of airplane that can land at all the airports. (set of all airplane types) - (set of all airplane types for which there is at least one airport at which airplanes of that airplane type can’t land) **********************************************************************************************************8 (set of all airplane types) - (set of all airplane types for which there is at least one airport at which airplanes of that airplane type can’t land) = (set of ordered pairs (x,y) such that x is an airplane type and y is an airport code) - (set of ordered pairs (x,y) such that x is an airplane type and y is an airport code and airplanes of airplane type x can land at the airport with airport code y)  type_name (airplane_type) -  type_name [pairs(type_name, airport_code) [(airplane__name, rt_code(airplane_type X airport)) - (airplane_type, airport_code(can_land)