# ReserveWithUs schema
# Authors: Philippe Bonnet, Dennis Shasha
# $Log$
# 
# ReserveWithUs is an electronic portal for hotel rooms.
# It buys rooms at discounted rates and sells them for a bit more.
# Its clientele is international so the website is multi-lingual.
# 
# This is the schema for the ReserveWithUs tuning case study.
# 
# This original schema defines:
# - HOTEL: The hotels available through ReserveWithUs.
# - ROOM: ROOM_TYPE keeps track of the types of rooms per hotel
#         ROOM_DESC stores the multilanguage representation for each room
#         ROOM_DATE table keeps track of the number of available rooms and the number of rooms
#         that have already been taken. Triggers ensure that numtaken is in the range [0,numavail]
#         on insertions and updates.
#         Descriptions (possibly in multiple languages) are associated to those rooms.
# - CUSTOMER: stores information about the ReserveWithUs customers.
# - ORDERS:  The BOOKED table is used to regiser all booking transactions.
#            The SHOPPING_CART keeps track of the active shopping carts.
# - TOURIST INFO: Some PR material about various tourist destinations.
#
# Notes:
# The storage engine used to represent the schema is the default storage engine. The default storage engine
# should be defined so that it enforces foreign keys constraints are enforced (e.g., InnoDB).
# See http://dev.mysql.com/doc/refman/5.1/en/storage-engine-setting.html for details on setting the storage engine.
# The characters are represented using the default character set.
# We use int to represent ids as we do not expect to manipulate more than 10s of millions of tuples
#   per table. 
# We use decimal to represent real because we do not need the floating point representation, and because
# we want to avoid the problems associated with comparing those to decimal.
# See http://dev.mysql.com/doc/refman/5.1/en/numeric-type-overview.html for details on numeric types.
# See http://dev.mysql.com/doc/refman/5.1/en/problems-with-float.html for details on the problems with floats.

# HOTEL
create table HOTEL (
  hotel_id int,
  name varchar(50),
  street varchar(100),
  city varchar(30),
  zip_code int,
  state varchar(30),
  country varchar(30),
  rating int,  			-- from 0 to 5 stars
  distance_to_center decimal,     -- distance to the center of the given city
  PRIMARY KEY (hotel_id) 
) engine = InnoDB;

# ROOM
create table ROOM_TYPE (
  room_type_id int,
  hotel_id int,
  room_type varchar(100),
  PRIMARY KEY (room_type_id),	
  FOREIGN KEY (hotel_id) REFERENCES HOTEL (hotel_id)
)  engine = InnoDB;

create table ROOM_DESC (
  room_type_id int,
  language varchar(10),
  description text,
  PRIMARY KEY (room_type_id, language),
  FOREIGN KEY (room_type_id) REFERENCES ROOM_TYPE (room_type_id)
) engine = InnoDB;

create table ROOM_DATE (
  room_type_id int,
  date date,
  numavail int, -- number of rooms of the given type, in the given hotel, on a given day. 
  numtaken int, -- number of those rooms already taken 
  price decimal,
  PRIMARY KEY (room_type_id, date),
  FOREIGN KEY (room_type_id) REFERENCES ROOM_TYPE (room_type_id)
) engine = InnoDB;


/* Enforces: numtaken range [o,numavail] - numavail, price >= 0  */
/* MySQL does NOT allow to write triggers that force a transaction to abort
   We implement the check using an updateable view  */
create view ROOM_DATE_WITH_CHECK as
SELECT * FROM ROOM_DATE
WHERE numtaken >= 0 and numtaken <= numavail
  AND numavail >= 0
  AND price >= 0
with cascaded check option;


# CUSTOMER
create table CUSTOMER (
  customer_id int,
  username varchar(8),
  password blob,
  first_name varchar(30),
  last_name varchar(30), 
  home_street varchar(100),
  home_city varchar(30),
  home_zip_code int,
  home_state varchar(30),
  business_street varchar(100),
  business_city varchar(30),
  business_zip_code int,
  business_state varchar(30),
  home_phone varchar(10),
  business_phone varchar(10),
  email varchar(20),
  language varchar(10),
  PRIMARY KEY (customer_id)
) engine = InnoDB;
 
#  ORDERS
create table BOOKED (
  customer_id int,
  date date,
  room_type_id int,
  numtaken int,
  price decimal,	-- Multiple languages, single currency 
  checkout_status int,  -- 0 REVIEWING, 1 PENDING, 2 CONFIRMED
  PRIMARY KEY (customer_id, room_type_id, date),
  FOREIGN KEY (customer_id) REFERENCES CUSTOMER (customer_id),
  FOREIGN KEY (room_type_id) REFERENCES ROOM_TYPE (room_type_id)
);

create table SHOPPING_CART (
  customer_id int,
  date_start date,
  date_stop date,
  room_type_id int,
  numtaken int,
  total_price decimal,   -- price for the reservation period
  PRIMARY KEY (customer_id, room_type_id, date_start,date_stop),
  FOREIGN KEY (customer_id) REFERENCES CUSTOMER (customer_id),
  FOREIGN KEY (room_type_id) REFERENCES ROOM_TYPE (room_type_id)
);

# TOURIST INFO
create table TOURIST_DESTINATION (
  destination_id int,
  name varchar(15),
  multimedia blob,
  PRIMARY KEY (destination_id)
);