8KzTCcKE

1USE master
2GO
3if exists (select * from sysdatabases where name='Hotel')
4	DROP DATABASE Hotel
5GO
6
7CREATE DATABASE Hotel
8COLLATE Cyrillic_General_CI_AS;
9GO
10USE Hotel
11GO
12
13----- Create Tables -----
14
15CREATE TABLE Bookings (
16	BookingID INTEGER NOT NULL,
17	MainGuestID INTEGER NOT NULL,
18	FromDate DATETIME,
19	ToDate DATETIME,
20	RoomsBookedID INTEGER NOT NULL,
21	TotalPrice DECIMAL(11,2),
22	Status NCHAR(30),
23	PaymentID INTEGER NOT NULL
24);
25
26CREATE TABLE GuestsForBookings (
27	GuestsBookedID INTEGER NOT NULL,
28	BookingID INTEGER NOT NULL,
29	GuestID INTEGER NOT NULL
30);
31
32CREATE TABLE Guests (
33	GuestID INTEGER NOT NULL,
34	FirstName NCHAR(50),
35	LastName NCHAR(50),
36	Address NVARCHAR(255),
37	CityFloor NCHAR(50),
38	Country NCHAR(50),
39	PhoneNumber NVARCHAR(22),
40	Gender NCHAR(1),
41	DocumentNumber NVARCHAR(50)
42);
43
44CREATE TABLE RoomsBooked (
45	RoomsBookedID INTEGER NOT NULL,
46	BookingID INTEGER NOT NULL,
47	RoomID INTEGER NOT NULL,
48	NightsCount INTEGER NOT NULL DEFAULT 1
49);
50
51CREATE TABLE Payments (
52	PaymentID INTEGER NOT NULL,
53	Type NCHAR(50),
54	Amount DECIMAL(11,2),
55	Status NCHAR(50)
56);
57
58CREATE TABLE Rooms (
59	RoomID INTEGER NOT NULL,
60	RoomNumber INTEGER,
61	FloorNumber INTEGER,
62	TypeID INTEGER NOT NULL,
63	Description NVARCHAR(MAX),
64	PricePerNight DECIMAL(11,2)
65);
66
67CREATE TABLE RoomTypes (
68	TypeID INTEGER NOT NULL,
69	Type NCHAR(50),
70	Description NVARCHAR(MAX),
71	MaxPeople INTEGER
72);
73
74CREATE TABLE Staff (
75	StaffID INTEGER NOT NULL,
76	FirstName NCHAR(50),
77	LastName NCHAR(50),
78	PhoneNumber NVARCHAR(22),
79	Position NVARCHAR(255),
80	Salary DECIMAL(11,2),
81	WorkingHours NVARCHAR(255)
82);
83
84CREATE TABLE RoomStaff (
85	RoomStaffID INTEGER NOT NULL,
86	StaffID INTEGER NOT NULL,
87	RoomID INTEGER NOT NULL
88);
89
90----- Create Constraints -----
91ALTER TABLE Bookings ADD CONSTRAINT PK_Bookings PRIMARY KEY(BookingID);
92
93ALTER TABLE GuestsForBookings ADD CONSTRAINT PK_GuestsForBookings PRIMARY KEY(GuestsBookedID);
94
95ALTER TABLE Guests ADD CONSTRAINT PK_Guests PRIMARY KEY(GuestID);
96
97ALTER TABLE RoomsBooked ADD CONSTRAINT PK_RoomsBooked PRIMARY KEY(RoomsBookedID);
98
99ALTER TABLE Payments ADD CONSTRAINT PK_Payments PRIMARY KEY(PaymentID);
100
101ALTER TABLE Rooms ADD CONSTRAINT PK_Rooms PRIMARY KEY(RoomID);
102
103ALTER TABLE RoomTypes ADD CONSTRAINT PK_RoomTypes PRIMARY KEY(TypeID);
104
105ALTER TABLE Staff ADD CONSTRAINT PK_Staff PRIMARY KEY(StaffID);
106
107ALTER TABLE RoomStaff ADD CONSTRAINT PK_RoomStaff PRIMARY KEY(RoomStaffID);
108
109ALTER TABLE Bookings ADD CONSTRAINT FK_Bookings_MainGuest FOREIGN KEY(MainGuestID) REFERENCES Guests(GuestID);
110
111ALTER TABLE Bookings ADD CONSTRAINT FK_Bookings_RoomsBooked FOREIGN KEY(RoomsBookedID) REFERENCES RoomsBooked(RoomsBookedID);
112
113ALTER TABLE Bookings ADD CONSTRAINT FK_Bookings_Payments FOREIGN KEY(PaymentID) REFERENCES Payments(PaymentID);
114
115ALTER TABLE GuestsForBookings ADD CONSTRAINT FK_GuestsForBookings_Bookings FOREIGN KEY(BookingID) REFERENCES Bookings(BookingID);
116
117ALTER TABLE GuestsForBookings ADD CONSTRAINT FK_GuestsForBookings_Guests FOREIGN KEY(GuestID) REFERENCES Guests(GuestID);
118
119ALTER TABLE RoomsBooked ADD CONSTRAINT FK_RoomsBooked_Rooms FOREIGN KEY(RoomID) REFERENCES Rooms(RoomID);
120
121ALTER TABLE Rooms ADD CONSTRAINT FK_Rooms_RoomTypes FOREIGN KEY(TypeID) REFERENCES RoomTypes(TypeID);
122
123ALTER TABLE RoomStaff ADD CONSTRAINT FK_RoomStaff_Rooms FOREIGN KEY(RoomID) REFERENCES Rooms(RoomID);
124
125ALTER TABLE RoomStaff ADD CONSTRAINT FK_RoomStaff_Staff FOREIGN KEY(StaffID) REFERENCES Staff(StaffID);