View the step-by-step solution to:

82 C hapter 3 The Relational Data Model and Relational Database Constraints AIRPORT I Airport_code I I Name I City I State FLIGHT I FlighCnumber

Write SQL statements for all questions in problems 6.17 and 6.18 (from Homework 4)
82 Chapter 3 The Relational Data Model and Relational Database Constraints AIRPORT I Airport_code I Name I City I State I FLIGHT I FlighCnumber Airline I Weekdays I Departure_airport_code FlighCnumber Scheduled_departure_time Scheduled_arrival_time LEG_I N STANCE FlighCnumber Departure_airport_code Arrival_time FARE Flight_number Restrictions AIRPLANE_TYPE I Airplane_type_name I Max seats Company I Airplane_type_name I Airport_code I AIRPLANE I Airplanejd I Total number of seats Airplane type SEAT_RESERVATION FlighCnumber SeaCnumber Customer_name Customer_phone Figure 3.8 The AIRLINE relational database schema. Consider the following SL,( relations for an order-processing database appli- cation in a company: CUSTOMER(Cust#, Cname, City) ORDER(Order#, Odate, Cust#, Ord_amt) ORDERJTEM(Order#, Item#, Oty)
Background image of page 1
188 Chapter 6 The Relational Algebra and Relational Calculus e. Retrieve the names of all employees who work on every project. f. Retrieve the names of all employees who do not work on any project. g. For each department, retrieve the department name and the average salary of all employees working in that department. h. Retrieve the average salary of all female employees. i. Find the names and addresses of all employees who work on at least one project located in Houston but whose department has no location in Houston. j. List the last names of all department managers who have no dependents. 6.17 Consider the AIRLINE relational database schema shown in Figure 3.8, which was described in Exercise 3.12. Specify the following queries in relational algebra: a. For each flight, list the flight number, the departure airport for the first leg of the flight, and the arrival airport for the last leg of the flight. b. List the flight numbers and weekdays of all flights or flight legs tha depart from Houston Intercontinental Airport (airport code 'IAH') and arrive in Los Angeles International Airport (airport code 'LAX'). c. List the fligh t number, departure airport code, scheduled departure time arrival airport code, scheduled arrival time, and weekdays of all flights or flight legs that depart from some airport in the city of Houston and arri\' at some airport in the city of Los Angeles. d. List all fare information for flight number 'C0 197'. e. Retrieve the number of available seats for flight number 'C0197' OJ' '2009-10-09'. 6.18. Consider the LIBRARY relational database schema shown in Figure 6.14. which is used to keep track of books, borrowers, and book loans. Referential integrity constraints are shown as directed arcs in Figure 6.14, as in the nota- tion of Figure 3.7. Write down relational expressions for the followin L queries: a. How many copies of the book titled The Lost Tribe are owned by the library branch whose name is 'Sharpstown'? b. How many copies of the book titled The Lost Tribe are owned by ead: library branch? c. Retrieve the names of all borrowers who do not have any books checked out. d. 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. e. For each library branch, retrieve the branch name and the total number of books loaned out from that branch.
Background image of page 2
Show entire document

Recently Asked Questions

Why Join Course Hero?

Course Hero has all the homework and study help you need to succeed! We’ve got course-specific notes, study guides, and practice tests along with expert tutors.

-

Educational Resources
  • -

    Study Documents

    Find the best study resources around, tagged to your specific courses. Share your own to gain free Course Hero access.

    Browse Documents
  • -

    Question & Answers

    Get one-on-one homework help from our expert tutors—available online 24/7. Ask your own questions or browse existing Q&A threads. Satisfaction guaranteed!

    Ask a Question