xvvK39Xv

1DROP DATABASE IF EXISTS;
2
3CREATE DATABASE IF NOT EXISTS HotelReservation;
4
5USE HotelReservation;
6
7CREATE TABLE Customer (
8CustomerID INT NOT NULL AUTO_INCREMENT,
9FirstName VARCHAR(30) NOT NULL,
10LastName VARCHAR(30) NOT NULL,
11CellPhone VARCHAR(15) NOT NULL,
12Email VARCHAR(45) NOT NULL,
13Age TINYINT NOT NULL,
14PRIMARY KEY (CustomerID));
15
16CREATE TABLE Room (
17RoomID INT NOT NULL AUTO_INCREMENT,
18RoomNumber SMALLINT UNSIGNED NOT NULL,
19RoomType VARCHAR(10) NOT NULL,
20OccupancyLimit TINYINT NOT NULL,
21PRIMARY KEY (RoomID));
22
23CREATE TABLE Rate (
24RateID INT NOT NULL AUTO_INCREMENT,
25RateDate DATE NOT NULL,
26Price DECIMAL(9, 4) NOT NULL,
27PRIMARY KEY (RateID));
28
29CREATE TABLE Amenity (
30AmenityID INT NOT NULL AUTO_INCREMENT,
31Bed VARCHAR(10) NOT NULL,
32TV VARCHAR(10) NOT NULL,
33NumberOfBeds TINYINT NOT NULL,
34NumberOfTVs TINYINT NOT NULL,
35Fridge BOOLEAN NOT NULL,
36Jacuzzi BOOLEAN NOT NULL,
37MiniBar BOOLEAN NOT NULL,
38PRIMARY KEY (AmenityID));
39
40CREATE TABLE RoomRate (
41RateID INT NOT NULL,
42RoomID INT NOT NULL,
43CONSTRAINT fk_RoomRate_RateID
44FOREIGN KEY (RateID)
45REFERENCES Rate(RateID),
46CONSTRAINT fk_RoomRate_RoomID
47FOREIGN KEY (RoomID)
48REFERENCES Room(RoomID));
49
50CREATE TABLE RoomAmmenities (
51RoomID INT NOT NULL ,
52AmenityID INT NOT NULL,
53CONSTRAINT fk_RoomAmmenities_RoomID
54FOREIGN KEY (RoomID)
55REFERENCES Room(RoomID),
56CONSTRAINT fk_RoomAmmenities_AmenityID
57FOREIGN KEY (AmenityID) 
58REFERENCES Amenity(AmenityID));
59
60CREATE TABLE AddOn (
61AddOnID INT NOT NULL AUTO_INCREMENT,
62Description VARCHAR(30) NOT NULL,
63Price DECIMAL(9, 4) NOT NULL,
64AddOnDate DATE NOT NULL,
65PRIMARY KEY (AddOnID));
66
67CREATE TABLE Reservation (
68ReservationID INT NOT NULL AUTO_INCREMENT,
69StartDate DATE NOT NULL,
70EndDate DATE NOT NULL,
71CustomerID INT NOT NULL,
72PRIMARY KEY (ReservationID),
73CONSTRAINT fk_Reservation_CustomerID
74FOREIGN KEY (CustomerID)
75REFERENCES Customer(CustomerID));
76
77CREATE TABLE Guest (
78GuestID INT NOT NULL AUTO_INCREMENT,
79FirstName VARCHAR(30) NOT NULL,
80LastName VARCHAR(30) NOT NULL,
81Age TINYINT NOT NULL,
82ReservationID INT NOT NULL,
83PRIMARY KEY (GuestID),
84CONSTRAINT fk_Guest_ReservationID
85FOREIGN KEY (ReservationID)
86REFERENCES Reservation(ReservationID));
87
88CREATE TABLE RoomReservation (
89RoomID INT NOT NULL,
90ReservationID INT NOT NULL,
91CONSTRAINT fk_RoomReservation_RoomID
92FOREIGN KEY (RoomID)
93REFERENCES Room(RoomID),
94CONSTRAINT fk_RoomReservation_ReservationID
95FOREIGN KEY (ReservationID)
96REFERENCES Reservation(ReservationID));
97
98CREATE TABLE ReservationAddOns (
99AddOnID INT NOT NULL,
100ReservationID INT NOT NULL,
101CONSTRAINT fk_ReservationAddOns_AddOnID
102FOREIGN KEY (AddOnID)
103REFERENCES AddOn(AddOnID),
104CONSTRAINT fk_ReservationAddOns_ReservationID
105FOREIGN KEY (ReservationID)
106REFERENCES Reservation(ReservationID));
107
108CREATE TABLE Promotion (
109PromotionID INT NOT NULL AUTO_INCREMENT,
110StartDate DATE NOT NULL,
111EndDate DATE NOT NULL,
112Description VARCHAR(30) NOT NULL,
113PRIMARY KEY (PromotionID));
114
115CREATE TABLE Receipt (
116ReceiptID INT NOT NULL AUTO_INCREMENT,
117PromotionID INT NULL,
118Tax DECIMAL(9, 4) NOT NULL,
119Total DECIMAL(15, 4) NOT NULL,
120AddOnTotals DECIMAL(12, 4) NULL,
121PRIMARY KEY (ReceiptID),
122CONSTRAINT fk_Receipt_PromotionID
123FOREIGN KEY (PromotionID)
124REFERENCES Promotion(PromotionID));
125
126CREATE TABLE ReservationReceipt (
127ReservationID INT NOT NULL,
128ReceiptID INT NOT NULL,
129CONSTRAINT fk_ReservationReceipt_ReservationID
130FOREIGN KEY (ReservationID)
131REFERENCES Reservation(ReservationID),
132CONSTRAINT fk_ReservationReceipt_ReceiptID
133FOREIGN KEY (ReceiptID)
134REFERENCES Receipt(ReceiptID));