AEUwfcD6

1SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
2SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
3SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';
4
5DROP SCHEMA IF EXISTS bookingdb;
6CREATE SCHEMA bookingdb;
7USE bookingdb;
8
9CREATE TABLE users (
10	user_id INT AUTO_INCREMENT PRIMARY KEY,
11    user_role VARCHAR(45) NOT NULL UNIQUE
12);
13
14CREATE TABLE customer (
15    customer_id INT AUTO_INCREMENT PRIMARY KEY,
16    first_name VARCHAR(45) NOT NULL,
17    last_name VARCHAR(45) NOT NULL,
18    email VARCHAR(50) NOT NULL,
19    check_in DATETIME NOT NULL,
20    check_out DATETIME NOT NULL,
21    adults INT NOT NULL,
22    kids INT NOT NULL,
23    user_role VARCHAR(45) NOT NULL,
24    booking_reference VARCHAR(50) NOT NULL UNIQUE,
25    FOREIGN KEY (user_role)
26        REFERENCES users (user_role) ON DELETE CASCADE
27);
28
29CREATE TABLE room_types (
30	room_type_id INT PRIMARY KEY AUTO_INCREMENT,
31    room_type VARCHAR(50) UNIQUE,
32    room_description LONGTEXT,
33    rooms_available INT
34);
35
36CREATE TABLE rooms (
37	room_id INT PRIMARY KEY AUTO_INCREMENT,
38    room_status BOOLEAN NOT NULL,
39    booking_reference VARCHAR(50) NOT NULL,
40    room_type VARCHAR(50),
41    rooms_available INT NOT NULL,
42    FOREIGN KEY(booking_reference) REFERENCES customer(booking_reference) ON DELETE CASCADE,
43    FOREIGN KEY(room_type) REFERENCES room_types(room_type) ON DELETE CASCADE
44);	
45
46INSERT INTO users (user_role)
47VALUES 
48	('admin'),
49    ('user');
50
51INSERT INTO room_types (room_type, room_description, rooms_available)
52VALUES 
53	('Single', 'A room assigned to one person. May have one or more beds.', 5),
54    ('Double', 'A room assigned to two people. May have one or more beds.', 5), 
55	('Triple', 'A room assigned to three people. May have two or more beds.', 5),
56	('Quad', 'A room assigned to four people. May have two or more beds.', 5),
57    ('Queen', 'A room with a queen-sized bed. May be occupied by one or more people.', 5),
58	('King', 'A room with a king-sized bed. May be occupied by one or more people.', 5),
59	('Twin', 'A room with two beds. May be occupied by one or more people.', 5),
60	('Double-double','A room with two double (or perhaps queen) beds. May be occupied by one or more people.', 5),
61	('Studio','A room with a studio bed – a couch that can be converted into a bed. May also have an additional bed.', 5);