CREATE TABLE Hotels $($

HotelID INT PRIMARY KEY,

Name VARCHAR$(255),$

Address VARCHAR$(255),$

City VARCHAR$(255),$

State VARCHAR$(255),$

Phone VARCHAR$(20)$

$)$;

CREATE TABLE Customers $($

CustomerID INT PRIMARY KEY,

Name VARCHAR$(255),$

Address VARCHAR$(255),$

Phone VARCHAR$(20),$

CreditCardInfo VARCHAR$(255)$

$)$;

CREATE TABLE Reservations $($

ReservationID INT PRIMARY KEY,

CustomerID INT,

HotelID INT,

RoomType INT,

StartDate DATE,

EndDate DATE,

Rate DECIMAL$(10,2),$

DateOfReservation DATE,

TotalAmount DECIMAL$(10,2),$

FOREIGN KEY $($CustomerID$)$ REFERENCES Customers$($CustomerID$),$

FOREIGN KEY $($HotelID$)$ REFERENCES Hotels$($HotelID$)$

$)$;

CREATE TABLE RoomTypes $($

RoomTypeID INT PRIMARY KEY,

HotelID INT,

RoomType VARCHAR$(255),$

PricePerNight DECIMAL$(10,2),$

Availability INT,

FOREIGN KEY $($HotelID$)$ REFERENCES Hotels$($HotelID$)$

$)$;

CREATE TABLE Services $($

ServiceID INT PRIMARY KEY,

ServiceType VARCHAR$(255),$

Price DECIMAL$(10,2)$

$)$;

CREATE TABLE ReservationServices $($

ReservationServiceID INT PRIMARY KEY,

ReservationID INT,

ServiceID INT,

Dt DATE,

Quantity INT,

TotalAmount DECIMAL$(10,2),$

FOREIGN KEY $($ReservationID$)$ REFERENCES Reservations$($ReservationID$),$

FOREIGN KEY $($ServiceID$)$ REFERENCES Services$($ServiceID$)$

$)$;

$--$ Creating the relationship: Reservations to RoomTypes $($Many$-$to$-$One$)$

ALTER TABLE Reservations

ADD FOREIGN KEY $($RoomType$)$ REFERENCES RoomTypes$($RoomTypeID$)$;

$--$ Insert records into Hotels table

INSERT INTO Hotels $($HotelID$,$ Name, Address, City, State, Phone$)$ VALUES

$(1,$ 'Grand Hotel', $\text{'}123$ Main St$\text{'},$ 'Springfield', $\text{'}$IL$\text{'},\text{'}217-555-1234\text{'})$;

INSERT INTO Hotels $($HotelID$,$ Name, Address, City, State, Phone$)$ VALUES

$(2,$ 'Ocean View', $\text{'}456$ Beach Rd$\text{'},$ 'Miami', $\text{'}$FL$\text{'},\text{'}305-555-5678\text{'})$;

INSERT INTO Hotels $($HotelID$,$ Name, Address, City, State, Phone$)$ VALUES

$(3,$ 'Mountain Inn', $\text{'}789$ Hill St$\text{'},$ 'Denver', $\text{'}$CO$\text{'},\text{'}303-555-9012\text{'})$;

INSERT INTO Hotels $($HotelID$,$ Name, Address, City, State, Phone$)$ VALUES

$(4,$ 'City Center Hotel', $\text{'}1010$ City Ave', 'New York', $\text{'}$NY$\text{'},\text{'}212-555-3456\text{'})$;

INSERT INTO Hotels $($HotelID$,$ Name, Address, City, State, Phone$)$ VALUES

$(5,$ 'Desert Retreat', $\text{'}2020$ Desert Rd$\text{'},$ 'Phoenix', $\text{'}$AZ$\text{'},\text{'}602-555-7890\text{'})$;

$--$ Insert records into Customers table

INSERT INTO Customers $($CustomerID$,$ Name, Address, Phone, CreditCardInfo$)$ VALUES

$(1,$ 'John Doe', $\text{'}321$ Oak St$\text{'},\text{'}217-555-4321\text{'},\text{'}4111-1111-1111-1111\text{'})$;

INSERT INTO Customers $($CustomerID$,$ Name, Address, Phone, CreditCardInfo$)$ VALUES

$(2,$ 'Jane Smith', $\text{'}654$ Pine St$\text{'},\text{'}305-555-8765\text{'},\text{'}4222-2222-2222-2222\text{'})$;

INSERT INTO Customers $($CustomerID$,$ Name, Address, Phone, CreditCardInfo$)$ VALUES

$(3,$ 'Robert Brown', $\text{'}987$ Maple St$\text{'},\text{'}303-555-2109\text{'},\text{'}4333-3333-3333-3333\text{'})$;

INSERT INTO Customers $($CustomerID$,$ Name, Address, Phone, CreditCardInfo$)$ VALUES

$(4,$ 'Emily Davis', $\text{'}1212$ Elm St$\text{'},\text{'}212-555-6543\text{'},\text{'}4444-4444-4444-4444\text{'})$;

INSERT INTO Customers $($CustomerID$,$ Name, Address, Phone, CreditCardInfo$)$ VALUES

$(5,$ 'Michael Johnson', $\text{'}3434$ Cedar St$\text{'},\text{'}602-555-9876\text{'},\text{'}4555-5555-5555-5555\text{'})$;

$--$ Insert records into RoomTypes table

INSERT INTO RoomTypes $($RoomTypeID$,$ HotelID, RoomType, PricePerNight, Availability$)$ VALUES

$(1,1,$ 'Single', $100\mathrm{.}00,10)$;

INSERT INTO RoomTypes $($RoomTypeID$,$ HotelID, RoomType, PricePerNight, Availability$)$ VALUES

$(2,1,$ 'Double', $150\mathrm{.}00,5)$;

INSERT INTO RoomTypes $($RoomTypeID$,$ HotelID, RoomType, PricePerNight, Availability$)$ VALUES

$(3,2,$ 'Suite', $200\mathrm{.}00,2)$;

INSERT INTO RoomTypes $($RoomTypeID$,$ HotelID, RoomType, PricePerNight, Availability$)$ VALUES

$(4,3,$ 'Single', $120\mathrm{.}00,8)$;

INSERT INTO RoomTypes $($RoomTypeID$,$ HotelID, RoomType, PricePerNight, Availability$)$ VALUES

$(5,4,$ 'Double', $180\mathrm{.}00,6)$;

$--$ Insert records into Services table

INSERT INTO Services $($ServiceID$,$ ServiceType, Price$)$ VALUES

$(1,$ 'Room Service', $25\mathrm{.}00)$;

INSERT INTO Services $($ServiceID$,$ ServiceType, Price$)$ VALUES

$(2,$ 'Spa', $100\mathrm{.}00)$;

INSERT INTO Services $($ServiceID$,$ ServiceType, Price$)$ VALUES

$(3,$ 'Gym Access', $15\mathrm{.}00)$;

INSERT INTO Services $($ServiceID$,$ ServiceType, Price$)$ VALUES

$(4,$ 'Laundry', $20\mathrm{.}00)$;

INSERT INTO Services $($ServiceID$,$ ServiceType, Price$)$ VALUES

$(5,$ 'Parking', $10\mathrm{.}00)$;

$--$ Insert records into Reservations table

INSERT INTO Reservations $($ReservationID$,$ CustomerID, HotelID, RoomType, StartDate, EndDate, Rate, DateOfReservation, TotalAmount$)$ VALUES

$(1,1,1,1,\text{'}07/01/2024\text{'},\text{'}07/05/2024\text{'},100\mathrm{.}00,\text{'}06/20/2024\text{'},500\mathrm{.}00)$;

INSERT INTO Reservations $($ReservationID$,$ CustomerID, HotelID, RoomType, StartDate, EndDate, Rate, DateOfReservation, TotalAmount$)$ VALUES

$(2,2,2,3,\text{'}07/10/2024\text{'},\text{'}07/15/2024\text{'},200\mathrm{.}00,\text{'}06/22/2024\text{'},1000\mathrm{.}00)$;

INSERT INTO Reservations $($ReservationID$,$ CustomerID, HotelID, RoomType, StartDate, EndDate, Rate, DateOfReservation, Tota