1 / 20

$10

1. 2. 3. 4. 5. 6. 7. 8. 9. 10. $10. $30. $50. Kaimuki Kokua Theater. For reservations call: (808) 332-4525 and ask for Cathy. Reservation System. The ticket agency for the Kaimuki Kokua Theater has a Web-based system that allow users to perform the following queries:

yon
Download Presentation

$10

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. 1 2 3 4 5 6 7 8 9 10 $10 $30 $50 Kaimuki Kokua Theater For reservations call: (808) 332-4525 and ask for Cathy

  2. Reservation System • The ticket agency for the Kaimuki Kokua Theater has a Web-based system that allow users to perform the following queries: • Display all seats in the theater • Display seats by price • Display available seats, given a particular price • Display reserved seats, including customer name • Given a seat, cancel a reservation

  3. Design of Reservation DB • Customers (cusID, cusFName, cusLName, cusPhone) • Seats (seatID, seatReserved) • Prices (priceID, priceValue) • Value of price is separated from the Seats table,so that when prices are changed, only Prices table needs to be modified.

  4. Customers cusIDcuslNamecusfNamecusPhone Seats seatIDpriceIDcusIDseatReserved Prices priceID|priceValue 1 1 M M Tables

  5. Create DB • Create Reservation DB, using Access. • Link table, using relationship window. • Populate tables with sample data.

  6. Sample Data Customers Seats Prices

  7. SQL for “Show All Seats” • SELECT Seats.seatNum, Prices.priceValue, Seats.seatReserved FROM Seats, Prices WHERE Seats.priceID = Prices.priceIDORDER BY Seats.seatID

  8. Your Turn • Show all $10 seats • Show all available seats • Show all $10 seats that are available • Show all $10 or $20 seats that are available • Show all seats that are reserved (including their seadNum, prices, and customers) • Cancel reservation for seat No. 3

  9. Your Turn • Add a new customer named Kaye Kenton whose phone number is (808)592-5645. • Cancel a reservation for Carol Chang. • Remove the customer named Carol Chang.

  10. ASP to show all seats • Seats.asp

  11. Result of ASP Processing • HTML Page returned by seats.asp

  12. Answers to SQL Questions • SELECT Seats.seatid, Prices.priceValueFROM Seats, PricesWHERE Seats.priceID=Prices.priceIDAND Prices.priceValue=10 • SELECT Seats.seatNum, Prices.priceValueFROM Seats, PricesWHERE Seats.priceID=Prices.priceIDAND Seats.seatReserved=false • SELECT Seats.seatNum, Prices.priceValueFROM Seats, PricesWHERE Seats.priceID=Prices.priceIDAND Seats.seatReserved=falseAND Prices.priceValue=10

  13. Answers to SQL Questions • SELECT Seats.seatNum, Prices.priceValueFROM Seats, PricesWHERE Seats.priceID=Prices.priceIDAND Seats.seatReserved=falseAND (Prices.priceValue=10 OR Prices.priceValue=20) • SELECT Seats.seatNum, Prices.priceValue, Customers.cusLNameFROM Seats, Prices, CustomersWHERE Seats.priceID=Prices.priceIDAND Customers.cusID=Seats.seatIDAND Seats.seatReserved=true • UPDATE SeatsSET seatReserved=false, cusID=NullWHERE seatID=3

  14. Answers to SQL Questions • INSERTINTO Customers(cusFname, cusLname, cusPhone)VALUES (“Kaye”, “Kenton”, “(808)592-5645”); • UPDATE SeatsSET seatReserved = false, cusID = NULLWHERE Seats.cusID = (SELECT Customers.cusID FROM Customers WHERE cusFname = 'Carol‘ AND cusLName = 'Chang');

  15. Improved Reservation System • Problems with Reservations DB version. 1 • Usually, Customers ←→ Seats is a many-to-many relationship. • Version 1 does not allow for seat reservations on different days. • How can we improve the DB design?

  16. Customers cusIDcuslNamecusfNamecusPhone Seats seatIDpriceIDcusIDseatReserved Prices priceID|priceValue M 1 M M Improved Reservation System Introduce a linking table between Customers and Seats tables.

  17. Improved Reservation System Reserevations cusIDseatIDdateID Seats seatIDseatNumpriceIDcusIDseatReserved Customers cusIDcuslNamecusfNamecusPhone 1 1 M M M M 1 Dates dateID|perfDate 1 Prices priceID|priceValue

  18. Query on Reservations version 2 • Display seat number and prices of all seats. • Display seat number and prices of all unreserved seats • Display customers with reservations and their dates and seats

  19. Solutions • SELECT seatNum, priceValueFROM Seats, PricesWHERE Prices.priceID = Seats.priceID • SELECT seatNum, priceValueFROM Seats, PricesWHERE Prices.priceID = Seats.priceIDAND seatReserved = false;

  20. Solutions • SELECT Customers.cusFname, Customers.cusLname, Seats.seatNum, Dates.perfDateFROM Customers, Reservations, Dates, SeatsWHERE Customers.cusID = Reservations.cusIDAND Dates.datID = Reservations.datIDAND Seats.seatID = Reservations.seatIDAND Seats.seatReserved = True;

More Related