1 / 12

$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:

clinton-fletcher
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

  7. SQL for “Show All Seats” • SELECT Seats.seatID, 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 $30 seats that are available • Show all seats that are reserved (including their seadID, prices, and customers) • Make a reservation for Tom Jones for a $60 seat. • Cancel reservation for seat No. 3

  9. ASP to show all seats • Seats.asp

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

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

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

More Related