1. CREATE DATABASE hotel;
2.  
3. USE hotel
4. GO
5.  
6.  
7. -- Creating a Table Menu
8. CREATE TABLE Menu
9. (
10.  menuID INT IDENTITY PRIMARY KEY,
11.  itemName VARCHAR(100) UNIQUE,
12.  description VARCHAR(100),
13.  price FLOAT,
14.  category VARCHAR(100)
15. );
16.  
17. -- check for table creation
18. SELECT * FROM Menu;
19.  
20.  
21. -- create table user
22. CREATE TABLE Users
23. (
24.  userID INT IDENTITY PRIMARY KEY,
25.  username VARCHAR(100),
26.  password nvarchar(100),
27.  email VARCHAR(100),
28.  ROLE VARCHAR(100)
29. );
30.  
31. -- check for table creation
32. SELECT * FROM Users;
33.  
34. -- create table customer
35. CREATE TABLE Customer
36. (
37.  customerID INT IDENTITY PRIMARY KEY,
38.  userID INT,
39.  phoneNumber VARCHAR(15),
40.  address VARCHAR(100),
41.  checkIn datetime,
42.  checkOut datetime,
43.  FOREIGN KEY (userID) REFERENCES Users(userID)
44. );
45.  
46.  
47.  
48. -- check for table creation
49. SELECT * FROM Customer;
50.  
51.  
52. -- create table department
53. CREATE TABLE Department
54. (
55.  departmentID INT IDENTITY PRIMARY KEY,
56.  departmentName VARCHAR(100)
57. )
58. -- create table staff
59. CREATE TABLE Staffs
60. (
61.  staffID INT IDENTITY PRIMARY KEY,
62.  userID INT,
63.  departmentID INT,
64.  POSITION VARCHAR(100),
65.  salary FLOAT,
66.  FOREIGN KEY (userID) REFERENCES Users(userID),
67.  FOREIGN KEY (departmentID) REFERENCES Department(departmentID),
68. );
69.  
70.  
71.  
72. -- check for table creation
73. SELECT * FROM Staffs;
74.  
75. -- create table room
76. CREATE TABLE Rooms
77. (
78.  roomID INT IDENTITY PRIMARY KEY,
79.  roomNumber INT,
80.  beds INT,
81.  STATUS VARCHAR(20),
82. );
83.  
84.  
85.  
86. -- check for table creation
87. SELECT * FROM rooms;
88.  
89.  
90. -- create table order
91. CREATE TABLE Orders
92. (
93.  orderID INT IDENTITY PRIMARY KEY,
94.  menuID INT,
95.  customerID INT,
96.  roomID INT,
97.  quantity INT,
98.  orderTime datetime,
99.  STATUS VARCHAR(100),
100.  FOREIGN KEY (menuID) REFERENCES Menu(menuID),
101.  FOREIGN KEY (customerID) REFERENCES Customer(customerID),
102.  FOREIGN KEY (roomID) REFERENCES Rooms(roomID)
103.  
104. );
105.  
106.  
107.  
108. SELECT * FROM Orders;
109.  
110.  
111. -- create table reservation
112. CREATE TABLE Bookings
113. (
114.  bookingID INT IDENTITY PRIMARY KEY,
115.  customerID INT,
116.  roomID INT,
117.  checkIn datetime,
118.  checkOut datetime,
119.  FOREIGN KEY (customerID) REFERENCES Customer(customerID),
120.  FOREIGN KEY (roomID) REFERENCES Rooms(roomID)
121. );
122.  
123. SELECT * FROM bookings