Your assignment is to design a database for a vacation house rental company (like Airbnb). You will design the database, insert some sample data, and implement a set of required features. Each feature will be implemented as one Oracle PL/SQL procedure (you can create sub-procedures that will be called by the main procedure for that feature). You do NOT need to write a graphic user interface. You also need to provide statements to execute your procedures. The instructor and TA will run the statements you provided to test your program.

Assumptions (Please read carefully)

You can make the following assumptions in this project.

The system needs to store data about members, houses, and reservations.

The member table stores members ID (integer), name, address (including street, city, state, and zip), phone number, email address, password, whether the member is a host (owner of house), and whether the member is a guest. A member can be host, guest, or both. Each house has one host. Each host can own multiple houses.

The house table stores house ID, member ID (for the host), street, city, state, zipcode, maximal capacity (as number of guests), number of bedrooms, the start date and the end date (the house will be available in between), and price per night.

The reservation table stores reservation ID (integer), guest ID (member ID of guest), house ID, check in date, check out date, number of guests, total amount due, status of the reservation (could be requested, approved or denied, and paid).

The guest first search for available houses, send a reservation request to the host. The host can approve or deny the request. If the request is approved, the guest pays the total amount one day before the check in date.

Required Features: please implement each of these features as a PL/SQL procedure. You should also test them by calling these procedures.

Look up contact information. Given a house ID, print out host's name, email and phone number. Please handle the case when the house does not exist.

Look up reservation requests for a host. Input: member id of host (house owner).

Find all reservation request (reservations with status = requested) for houses owned by the host. Please print out reservation id, guest name, house id, check in date, check out date, number of guests for these reservations.

Look up available houses at a given city and state and a given period. Input includes city and state, checkin date, and check out date. Print out house address, price per night of all houses that are in that city and state and available in that period. A house is available if it satisfies two conditions:

The houses startdate <= checkin date and enddate >= checkout date

There is no reservation on the same house overlap with that period.

For example, suppose a house at Ocean City, MD has start date 2017-6-1 and end date 2017-9-10 and there are two existing reservations on this house from 2017-8-1 to 2017-8-7, and 2017-8-15 to 2017-8-22. Suppose the input check in date is 2017-9-1 and check out date is 2017-9-20, this house will not be shown because it is not available after 2017-9-10.

Suppose the input check in date is 2017-8-5 and check out date is 2017-8-10, this house will not be shown because it overlaps with one of the existing reservations.

Suppose the input check in date is 2017-6-1 and check out date is 2017-6-10, then this house will be shown.

Deliverables

There will be 2 deliverables:

DELIVERABLE 1

SQL statements to create database tables (drop table, create table including primary keys and foreign keys) and to insert some sample data (at least 5 rows per table).