rukVXJVS

1DROP database if exists hotels;
2CREATE database hotels;
3USE hotels;
4
5
6CREATE TABLE rooms(
7numOfRoom INT PRIMARY KEY UNIQUE,
8category VARCHAR (50) NOT NULL,
9numOfBeds INT NOT NULL,
10viewOfRoom VARCHAR (50) NOT NULL
11);
12
13CREATE TABLE clients(
14id INT auto_increment PRIMARY KEY,
15nameOfClient VARCHAR (255) NOT NULL,
16egn VARCHAR (20) NOT NULL,
17dateOfArrival DATE NOT NULL,
18daysOfStaying INT NOT NULL,
19dateOfLeaving DATE NOT NULL,
20room_id int,
21constraint foreign key (room_id) references rooms (numOfRoom)
22);
23
24CREATE TABLE services (
25id INT auto_increment PRIMARY KEY,
26typeOfServ ENUM ('Free Parking', 'Safe', 'Fitness', 'Spa', 'Therapy', 'Rent a car')
27);
28
29
30CREATE TABLE clients_services (
31clients_id int,
32services_id int,
33constraint foreign key (clients_id) references  clients (id),
34constraint foreign key (services_id) references services (id)
35);
36
37CREATE TABLE rating(
38id INT auto_increment PRIMARY KEY,
39rate ENUM ('1', '2', '3', '4', '5'),
40clients_id INT,
41CONSTRAINT FOREIGN KEY (clients_id) REFERENCES clients (id));
42
43
44INSERT INTO rooms(
45numOfRoom, category,numOfBeds, viewOfRoom) VALUES (101, 'Double room', 2, 'North view');
46INSERT INTO rooms(
47numOfRoom, category,numOfBeds, viewOfRoom) VALUES (207, 'Single room', 1, 'South view');
48INSERT INTO rooms(
49numOfRoom, category,numOfBeds, viewOfRoom) VALUES (306, 'Apartment', 4, 'West view');
50INSERT INTO rooms(
51numOfRoom, category,numOfBeds, viewOfRoom) VALUES (505, 'Studio', 3, 'East view');
52INSERT INTO rooms(
53numOfRoom, category,numOfBeds, viewOfRoom) VALUES (603, 'Apartment', 4, 'East view');
54
55INSERT INTO clients(
56nameOfClient, egn, dateOfArrival, daysOfStaying, dateOfLeaving, room_id) 
57VALUES ('Ivan Georgiev Dimitrov', 6405120033, "2016-05-11", 5, "2016-05-16", 101);
58INSERT INTO clients(
59nameOfClient, egn, dateOfArrival, daysOfStaying, dateOfLeaving, room_id) 
60VALUES ('Amy Steven Johnson', 263585946212, "2017-02-12", 3, "2017-02-15", 207);
61INSERT INTO clients(
62nameOfClient, egn, dateOfArrival, daysOfStaying, dateOfLeaving, room_id) 
63VALUES ('Sammy Elizabeth Smith', 10254585963, "2018-03-16", 10, "2018-03-26", 101);
64INSERT INTO clients(
65nameOfClient, egn, dateOfArrival, daysOfStaying, dateOfLeaving, room_id) 
66VALUES ('Maria Stefanova Angelova', 8501160563, "2018-01-15", 3, "2018-01-18", 505);
67
68INSERT INTO clients_services (clients_id, services_id) 
69VALUES (1, 3);
70
71INSERT INTO clients_services(clients_id, services_id) 
72VALUES (2, 2);
73
74INSERT INTO clients_services(clients_id, services_id) 
75VALUES (3, 1);
76
77
78INSERT INTO rating(
79rate, clients_id) VALUES ('5', 1);
80INSERT INTO rating(
81rate, clients_id) VALUES ('2', 2);
82INSERT INTO rating(
83rate, clients_id) VALUES ('5', 3);
84INSERT INTO rating(
85rate, clients_id) VALUES ('3', 4);