hiDc345q

1DROP DATABASE IF EXISTS Hotels_Zhong;
2
3CREATE DATABASE Hotels_Zhong;
4
5USE Hotels_Zhong;
6
7CREATE TABLE roomtypes(
8RoomTypeId INT AUTO_INCREMENT PRIMARY KEY NOT NULL,
9RoomName VARCHAR(30)
10);
11
12CREATE TABLE RoomAttributes(
13AttributeId INT AUTO_INCREMENT PRIMARY KEY NOT NULL,
14AttributeName VARCHAR(30)
15);
16
17CREATE TABLE rooms(
18RoomId INT AUTO_INCREMENT PRIMARY KEY NOT NULL,
19RoomTypeId INT NOT NULL,
20AttributeId INT NOT NULL,
21CONSTRAINT fk_room_roomtype FOREIGN KEY (RoomTypeId)
22	REFERENCES roomtypes(RoomTypeId),
23CONSTRAINT fk_room_attribute FOREIGN KEY (AttributeId)
24	REFERENCES RoomAttributes(AttributeId)
25);
26
27CREATE TABLE guests(
28GuestId INT AUTO_INCREMENT PRIMARY KEY NOT NULL,
29FirstName VARCHAR(30) NOT NULL,
30LastName VARCHAR(30) NOT NULL,
31PhoneNumber INT NULL
32);
33
34CREATE TABLE reservations(
35ReservationId INT AUTO_INCREMENT PRIMARY KEY NOT NULL,
36StartDate DATETIME NOT NULL,
37EndDate DATETIME NOT NULL,
38GuestId INT NOT NULL,
39RoomId INT NOT NULL,
40CONSTRAINT fk_reservations_guest FOREIGN KEY(GuestId)
41	REFERENCES guests(GuestId),
42CONSTRAINT fk_reservations_room FOREIGN KEY(RoomId)
43	REFERENCES rooms(RoomId)
44);