Your assignment is to design a database for a vacation house rental company (lik
ID: 3590712 • Letter: Y
Question
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 member’s 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 house’s 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).
Explanation / Answer
# Creating table member with the given attributes
CREATE TABLE member
(
id varchar(8),
name varchar(20),
street varchar(20),
city varchar(15),
postal varchar(5),
state varchar(12),
phone varchar(10),
email varchar(30),
password varchar(15),
isHost varchar(1) CHECK isHost IN (‘Y’, ‘N’),
isGuest varchar(1) CHECK isGuest IN (‘Y’, ‘N’),
PRIMARY KEY (id)
);
# Creating table house with the given attributes
CREATE TABLE house
(
id varchar(8),
memberId varchar(8),
street varchar(8),
city varchar(15),
postal varchar(5),
state varchar(12),
capacity varchar(3),
numberOfRoom varchar(2),
startDate date,
endDate date,
price varchar(8),
PRIMARY KEY (id, memberId),
FOREIGN KEY (memberId) REFERENCES member (id)
ON DELETE CASCADE
);
# Creating table reservation with the given attributes
CREATE TABLE reservation
(
id varchar(8),
guestId varchar(8),
houseId varchar(8),
checkIn date,
checkout date,
numberGuest varchar(3),
amountDue varchar(8),
status varchar(10) NOT NULL CHECK status IN (‘requested’, ‘approved’, ‘denied’,’paid’),
PRIMARY KEY (id),
FOREIGN KEY (guestId) REFERENCES member (id)
ON DELETE CASCADE,
FOREIGN KEY (houseId) REFERENCES house (id)
ON DELETE CASCADE
);
# Inserting values to member table
INSERT INTO member VALUES (‘M-1’, ’John’, ’Park Street’, ’Fargo’, ’56032’, ’ND’, ’9999999999’, ’john@mail.com’, ’pass123’, ’Y’, ’N’);
INSERT INTO member VALUES (‘M-12’, ’Peter’, ’Park Avenue’, ’Dallas’, ’56532’, ’ND’, ’9559999999’, ’peter@mail.com’, ’passPeter’, ’Y’, ’Y’);
INSERT INTO member VALUES (‘M-15’, ’Bob’, ’Park Street’, ’Fargo’, ’56032’, ’ND’, ’9999999999’, ’bob@mail.com’, ’passBin’, ’N’, ’Y’);
# Inserting values to house table
INSERT INTO house VALUES (‘H-11’, ‘M-1’, ‘Street 1’, ‘St. Louis’, ‘53209’, ‘ND’, ‘4’, ‘1’, ‘2017-10-15’, ‘2017-11-30’, ‘300’);
INSERT INTO house VALUES (‘H-12’, ‘M-1’, ‘Street 3’, ‘St. Louis’, ‘53209’, ‘ND’, ‘6’, ‘2’, ‘2017-11-15’, ‘2017-12-30’, ‘400’);
# Inserting values into reservation table
INSERT INTO reservation VALUES (‘R-1’, ‘M-1’, ‘H-11’, ‘2017-10-16’, ‘2017-10-20’, ‘2’, ‘300’, ‘approved’);
INSERT INTO reservation VALUES (‘R-2’, ‘M-1’, ‘H-12’, ‘2017-11-16’, ‘2017-11-20’, ‘3’, ‘350’, ‘requested’);
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.