createTables.sql

DROP TABLE ReviewForProperty;
CREATE TABLE ReviewForProperty(
GuestID INT,
PID INT ,
Created_Time Date NOT NULL,
Modified_Time Date,
CommentInReview VARCHAR(1000) ,
Cleanliness_Rating NUMBER(2,1),
Communication_Rating NUMBER(2,1),
CheckIn_Rating NUMBER(2,1),
Accuracy_Rating NUMBER(2,1),
Location_Rating NUMBER(2,1),
Value_Rating NUMBER(2,1),
Overall_Rating NUMBER(2,1),
PRIMARY KEY(GuestID,PID));

DROP TABLE PhotoForPropertyReview;
CREATE TABLE PhotoForPropertyReview(
GuestID INT ,
PID INT ,
PhotoName VARCHAR(50) ,
ImageFile BLOB NOT NULL,
PRIMARY KEY(GuestID,PID,PhotoName));

DROP TABLE PropertyIncludedInWishlist;
CREATE TABLE PropertyIncludedInWishlist(
PID INT ,
AirBnBUID INT ,
WishlistName VARCHAR(50) ,
CheckInDate DATE,
CheckOutDate DATE,
PRIMARY KEY(PID,AirBnBUID,WishlistName));

DROP TABLE WishList;
CREATE TABLE WishList(
AirBnBUID INT ,
WishlistName VARCHAR(50) NOT NULL UNIQUE,
Privacy CHAR(1),
PRIMARY KEY(AirBnBUID,WishlistName));

DROP TABLE ReviewForUsers;
CREATE TABLE ReviewForUsers(
HostUID INT ,
GuestUID INT ,
GuestRating NUMBER(2,1),
HostRating  NUMBER(2,1),
CommentForHost VARCHAR(1000),
CommentForGuest VARCHAR(1000),
ReviewForHostCreated DATE,
ReviewForGuestCreated DATE,
ReviewForHostModified DATE,
ReviewForGuestModified DATE,
PRIMARY KEY(HostUID,GuestUID));

DROP TABLE CreditCard;
CREATE TABLE CreditCard(
CardNum INT ,
CSV INT NOT NULL,
ExpirationDate DATE  NOT NULL,
CardholderName VARCHAR(50) NOT NULL,
CardType CHAR(6) NOT NULL,
Address VARCHAR(100),
PRIMARY KEY(CardNum));

DROP TABLE BankAccount;
CREATE TABLE BankAccount(
AccountNUMBER INT ,
RoutingNum INT NOT NULL,
AccountType VARCHAR(20) NOT NULL,
PRIMARY KEY(AccountNUMBER));

DROP TABLE Promo;
CREATE TABLE Promo(
Code VARCHAR(10),
Discount_Amt NUMBER NOT NULL,
PRIMARY KEY(Code));

DROP TABLE Message;
CREATE TABLE Message(
HostUID INT ,
GuestID INT ,
Created DATE NOT NULL,
Message_To INT NOT NULL,
Message_From INT NOT NULL,
Body VARCHAR(1000),
PRIMARY KEY(HostUID,GuestID));

DROP TABLE Guest;
CREATE TABLE Guest(
AirBnBUID INT ,
AvgRating NUMBER(2,1) ,
NumOfRatings INT DEFAULT 0,
CreditCardNum INT NOT NULL UNIQUE,
PRIMARY KEY(AirBnBUID));

DROP TABLE Host;
CREATE TABLE Host(
AirBnBUID INT ,
IsSuperHost CHAR(1) ,
AvgRating NUMBER(2,1),
NumOfRatings INT,
BankAccountNumber INT NOT NULL UNIQUE,
PRIMARY KEY(AirBnBUID));

DROP TABLE AirBnBUser;
CREATE TABLE AirBnBUser(
AirBnBUID INT,
DOB DATE,
Email VARCHAR(20) NOT NULL,
UserPassword VARCHAR(20) NOT NULL,
Gender CHAR(1),
About VARCHAR(100),
Phone VARCHAR(15) NOT NULL,
ProfilePhotoName VARCHAR(20),
ProfilePhoto BLOB,
Address VARCHAR(100),
Fname VARCHAR(20) NOT NULL,
MInitial VARCHAR(20),
LName  VARCHAR(20),
Created TIMESTAMP,
LoginCnt INT,
LastLogin TIMESTAMP,
EmName VARCHAR(20),
EmRelationship VARCHAR(20),
EmPreferredLang VARCHAR(15),
EmEmail VARCHAR(20) NOT NULL,
EmCountryCode VARCHAR(3) NOT NULL,
EmPhone VARCHAR(15) NOT NULL,
PRIMARY KEY(AirBnBUID)); 

DROP TABLE Amenity;
CREATE TABLE Amenity(
PID INT ,
AmenityName VARCHAR(20),
PRIMARY KEY(PID,AmenityName));

DROP TABLE PropertyPhoto;
CREATE TABLE PropertyPhoto(
PID INT ,
PropertyName VARCHAR(20),
ImageFile BLOB NOT NULL,
PRIMARY KEY(PID,PropertyName));

DROP TABLE HouseRule;
CREATE TABLE HouseRule(
PID INT ,
RuleName VARCHAR(20),
PRIMARY KEY(PID,RuleName));

DROP TABLE AvailableBookingSlot;
CREATE TABLE AvailableBookingSlot(
PID INT ,
StartDate DATE,
EndDate DATE,
PRIMARY KEY(PID,StartDate,EndDate));

DROP TABLE Bedroom;
CREATE TABLE Bedroom(
PropertyID INT ,
BedroomNumber VARCHAR(5),
BedType VARCHAR(10),
BedCnt INT,
PRIMARY KEY(PropertyID,BedroomNumber,BedType,BedCnt));

DROP TABLE Category;
CREATE TABLE Category(
PID INT ,
categoryName VARCHAR(20),
PRIMARY KEY(PID,categoryName));

DROP TABLE PropertyListing;
CREATE TABLE PropertyListing(
PID INT ,
PropertyName VARCHAR(50) ,
Zipcode INT NOT NULL,
BathroomCnt INT,
BedroomCnt INT DEFAULT 0,
GuestNum INT,
PricePerNight NUMBER(6,2),
CleaningFee NUMBER(4,2),
Created Date,
CheckInTime TIMESTAMP,
CheckOutTime TIMESTAMP,
IsRefundable CHAR(1),
CancellationPeriod INT,
CancellationType VARCHAR(10),
RefundRate NUMBER(2,1),
NumOfRatings INT DEFAULT 0,
AvgRatings NUMBER(2,1) DEFAULT 0,
HID INT,
Street VARCHAR(20),
City VARCHAR(20),
StateofResidence VARCHAR(20),
Country VARCHAR(20),
TaxRate NUMBER(2,1),
PRIMARY KEY(PID));

DROP TABLE Booking;
CREATE TABLE Booking(
BID INT,
CheckInDate DATE NOT NULL,
CheckOutDate DATE NOT NULL,
AmountPaid NUMBER(6,2),
BookingDate DATE NOT NULL,
ModifiedDate DATE,
SeniorGuestNum INT DEFAULT 0,
AdultGuestNum INT DEFAULT 0,
ChildGuestNum INT DEFAULT 0,
IsCancelled CHAR(1),
RefundPaid CHAR(1),
CancelDate DATE,
PromoCode  VARCHAR(10),
GuestUID INT,
PID INT,
TotalPrice NUMBER(6,2),
Tax NUMBER(4,2),
TotalPriceWTax NUMBER(6,2),
AmountDue NUMBER(4,2),
RefundAmt NUMBER(4,2),
PRIMARY KEY(BID));

ALTER TABLE Guest ADD CONSTRAINT GuestFK_1 FOREIGN KEY(AirBnBUID) REFERENCES AirBnBUser(AirBnBUID) ON DELETE  CASCADE;
ALTER TABLE Guest ADD CONSTRAINT GuestFK_2 FOREIGN KEY(Creditcardnum) REFERENCES CREDITCARD(CARDNUM) ON DELETE  CASCADE;
ALTER TABLE Host ADD CONSTRAINT HostFK_1 FOREIGN KEY(AirBnBUID) REFERENCES  AirBnBUser(AirBnBUID) ON DELETE  CASCADE ;
ALTER TABLE Host ADD CONSTRAINT HostFK_2 FOREIGN KEY(BANKACCOUNTNUMBER) REFERENCES  BANKACCOUNT(ACCOUNTNUMBER) ON DELETE  CASCADE ;
ALTER TABLE ReviewForProperty ADD CONSTRAINT ReviewForPropertyFK_1 FOREIGN KEY(GuestID) REFERENCES Guest(AirBnBUID) ON DELETE  CASCADE ;	
ALTER TABLE ReviewForProperty ADD CONSTRAINT ReviewForPropertyFK_2 FOREIGN KEY(PID) REFERENCES  PropertyListing(PID) ON DELETE  CASCADE ;
ALTER TABLE PhotoForPropertyReview ADD CONSTRAINT PhotoForPropertyReviewFK_1 FOREIGN KEY(GuestID) REFERENCES Guest(AirBnBUID) ON DELETE  CASCADE ;	
ALTER TABLE PhotoForPropertyReview ADD CONSTRAINT PhotoForPropertyReviewFK_2 FOREIGN KEY(PID) REFERENCES  PropertyListing(PID) ON DELETE  CASCADE ;
ALTER TABLE PropertyIncludedInWishlist ADD CONSTRAINT PropertyIncludedInWishlistFK_1 FOREIGN KEY(AirBnBUID) REFERENCES Guest(AirBnBUID) ON DELETE  CASCADE ;	
ALTER TABLE PropertyIncludedInWishlist ADD CONSTRAINT PropertyIncludedInWishlistFK_2 FOREIGN KEY(PID) REFERENCES  PropertyListing(PID) ON DELETE  CASCADE ;
ALTER TABLE PropertyIncludedInWishlist ADD CONSTRAINT PropertyIncludedInWishlistFK_3 FOREIGN KEY(WishlistName) REFERENCES  WishList(WishlistName) ON DELETE  CASCADE;
ALTER TABLE WishList ADD CONSTRAINT WishListFK_1 FOREIGN KEY(AirBnBUID) REFERENCES Guest(AirBnBUID) ON DELETE  CASCADE;	
ALTER TABLE ReviewForUsers ADD CONSTRAINT ReviewForUsersFK_1 FOREIGN KEY(HostUID) REFERENCES Host(AirBnBUID) ON DELETE  CASCADE ;	
ALTER TABLE ReviewForUsers ADD CONSTRAINT ReviewForUsersFK_2 FOREIGN KEY(GuestUID) REFERENCES  Guest(AirBnBUID) ON DELETE  CASCADE ;
ALTER TABLE Message ADD CONSTRAINT MessageFK_1 FOREIGN KEY(HostUID) REFERENCES Host(AirBnBUID) ON DELETE  CASCADE ;	
ALTER TABLE Message ADD CONSTRAINT MessageFK_2 FOREIGN KEY(GuestID) REFERENCES  Guest(AirBnBUID) ON DELETE  CASCADE ;
ALTER TABLE Amenity ADD CONSTRAINT AmenityFK_1 FOREIGN KEY(PID) REFERENCES PropertyListing(PID) ON DELETE  CASCADE ;	
ALTER TABLE PropertyPhoto ADD CONSTRAINT PropertyPhotoFK_1 FOREIGN KEY(PID) REFERENCES PropertyListing(PID) ON DELETE  CASCADE ;	
ALTER TABLE HouseRule ADD CONSTRAINT HouseRuleFK_1 FOREIGN KEY(PID) REFERENCES PropertyListing(PID) ON DELETE  CASCADE ;	
ALTER TABLE AvailableBookingSlot ADD CONSTRAINT AvailableBookingSlotFK_1 FOREIGN KEY(PID) REFERENCES PropertyListing(PID) ON DELETE  CASCADE ;	
ALTER TABLE Bedroom ADD CONSTRAINT BedroomFK_1 FOREIGN KEY(PropertyID) REFERENCES PropertyListing(PID) ON DELETE  CASCADE ;	
ALTER TABLE Category ADD CONSTRAINT CategoryFK_1 FOREIGN KEY(PID) REFERENCES PropertyListing(PID) ON DELETE  CASCADE ;
ALTER TABLE PROPERTYLISTING ADD CONSTRAINT propertyFK_1 FOREIGN KEY(HID) REFERENCES Host(AIRBNBUID) ON DELETE  CASCADE ;
ALTER TABLE Booking ADD CONSTRAINT BookingFK_1 FOREIGN KEY(GuestUID) REFERENCES Guest(AirBnBUID) ON DELETE  CASCADE ;	
ALTER TABLE Booking ADD CONSTRAINT BookingFK_2 FOREIGN KEY(PID) REFERENCES  PropertyListing(PID) ON DELETE  CASCADE ;
ALTER TABLE Booking ADD CONSTRAINT BookingFK_3 FOREIGN KEY(PROMOCODE) REFERENCES  Promo(Code) ON DELETE  CASCADE ;