Homework 2

General Instructions

  1. If you do not have the book and need to see an electronic copy of the homework, I have uploaded a scanned version of the relevant pages in the textbook to the assignments section of blackboard.

  2. Please submit this homework in an ascii text file named hw2.txt, with each exercise clearly marked. For example:
    6.7 SELECT * FROM Hotel;
    6.9 SELECT GuestName, GuestAddress FROM Guest
           WHERE GuestAddress LIKE '%London%';
    We will be creating our own test data, then copying and pasting your answers into the mysql interpreter.

  1. Use the CREATE TABLE command to create each of the relations shown in the Hotel Schema defined at the start of the Exercises at the end of Chapter 4 and insert some of your own sample data into each table so you can test your queries in part 2 of this problem. Assume the following data types:

    1. Hotel
      1. hotelNo: integer, 4 digits
      2. hotelName: string, 30 characters, variable-length string
      3. city: string, 20 characters, variable-length string
    2. Room
      1. roomNo: integer, 3 digits
      2. hotelNo: see Hotel
      3. type: string, 9 characters, variable length string--acceptable values are 'family', 'double', 'king', 'penthouse', 'queen' (don't worry in this assignment about declaring a domain constraint, that will come in the next assignment)
      4. price: decimal, 5 digits, 2 decimal digits (max is 999.99)
    3. Booking
      1. hotelNo: see Hotel
      2. guestNo: integer, 7 digits
      3. dateFrom: date type
      4. dateTo: date type
      5. roomNo: see Room
    4. Guest
      1. guestNo: see Booking
      2. guestName: string, 30 characters, variable-length string
      3. guestAddress: string, 50 characters, variable-length string (sample data would be something like "1678 Cardiff Rd.; Columbus, OH 43221")

  2. The following exercises: