1:--schema for hotel reservations sample application using hsqldb.
   2:
   3:--hotel customer
   4:CREATE TABLE customer (
   5:  customer_id          INT NOT NULL IDENTITY,
   6:  last_name            VARCHAR(100) NOT NULL,
   7:  first_name           VARCHAR(100),
   8:  address              VARCHAR(300),      
   9:  phone                VARCHAR(15),
  10:  arrival_date         DATE NOT NULL,  
  11:  departing_date       DATE NOT NULL,  
  12:  payment_method       VARCHAR(10)
  13:);
  14:  
  15:--hotel room. 
  16:CREATE TABLE room (
  17:  room_id          INT NOT NULL IDENTITY,
  18:  floor            INT NOT NULL,
  19:  room_type        CHAR(1),
  20:  can_smoke        BOOLEAN,
  21:  room_number      INT NOT NULL,
  22:  reserve_status   CHAR  NOT NULL
  23:);
  24:
  25:--hotel reservations made by customer. 
  26:CREATE TABLE customer_rooms (
  27:  room_id          INT NOT NULL,
  28:  customer_id      INT NOT NULL,
  29:  day_reserved          DATE NOT NULL,
  30:  CONSTRAINT fk_customer_rooms_room FOREIGN KEY (room_id)
  31:     REFERENCES room (room_id),
  32:  CONSTRAINT fk_customer_rooms_customer FOREIGN KEY (customer_id)
  33:     REFERENCES customer (customer_id)
  34:);
  35:
  36:--available rooms for customer (1 room receives 365 entries). 
  37:--Provides a more intuitive view of what is available...
  38:CREATE TABLE available_rooms (
  39:  day_reserved          DATE NOT NULL,
  40:  room_id               INT NOT NULL,
  41:  reserve_status        CHAR  NOT NULL,
  42:  CONSTRAINT fk_available_rooms_room FOREIGN KEY (room_id)
  43:     REFERENCES room (room_id)
  44:);
  45:
  46:
  47:--Room data.
  48:INSERT INTO room(floor, room_number, reserve_status,room_type,can_smoke) VALUES(1,100, 'V','S',false);
  49:INSERT INTO room(floor, room_number, reserve_status,room_type,can_smoke) VALUES(1,101, 'V','D',false);
  50:INSERT INTO room(floor, room_number, reserve_status,room_type,can_smoke) VALUES(1,102, 'O','D',true);
  51:INSERT INTO room(floor, room_number, reserve_status,room_type,can_smoke) VALUES(1,103, 'O','S',false);
  52:INSERT INTO room(floor, room_number, reserve_status,room_type,can_smoke) VALUES(1,104, 'V','S',false);
  53:INSERT INTO room(floor, room_number, reserve_status,room_type,can_smoke) VALUES(1,105, 'V','D',true);
  54:
  55:
  56:INSERT INTO room(floor, room_number, reserve_status,room_type,can_smoke) VALUES(2,200,'V','S',true);
  57:INSERT INTO room(floor, room_number, reserve_status,room_type,can_smoke) VALUES(2,201,'O','S',true);
  58:INSERT INTO room(floor, room_number, reserve_status,room_type,can_smoke) VALUES(2,202,'V','D',false);
  59:INSERT INTO room(floor, room_number, reserve_status,room_type,can_smoke) VALUES(2,203,'V','D',false);
  60:INSERT INTO room(floor, room_number, reserve_status,room_type,can_smoke) VALUES(2,204,'V','D',false);
  61:INSERT INTO room(floor, room_number, reserve_status,room_type,can_smoke) VALUES(2,205,'O','D',true);
  62:
  63:
  64:INSERT INTO room(floor, room_number, reserve_status,room_type,can_smoke) VALUES(3,300,'V','S',true);
  65:INSERT INTO room(floor, room_number, reserve_status,room_type,can_smoke) VALUES(3,301,'V','S',true);
  66:INSERT INTO room(floor, room_number, reserve_status,room_type,can_smoke) VALUES(3,302,'V','S',false);
  67:INSERT INTO room(floor, room_number, reserve_status,room_type,can_smoke) VALUES(3,303,'V','D',false);
  68:INSERT INTO room(floor, room_number, reserve_status,room_type,can_smoke) VALUES(3,304,'O','D',false);
  69:INSERT INTO room(floor, room_number, reserve_status,room_type,can_smoke) VALUES(3,305,'V','S',true);
  70: