TjSzhzik

1/* Database creation */
2CREATE DATABASE IF NOT EXISTS db_hotel character set UTF8mb4 collate utf8mb4_bin;
3USE db_hotel;
4
5/* Tables creation */
6CREATE TABLE IF NOT EXISTS guests
7( 
8    id INT UNSIGNED auto_increment, 
9    room_number INT unsigned NOT NULL,
10    full_name VARCHAR(30) NOT NULL,
11    passport_number VARCHAR(10) NOT NULL,
12    residency VARCHAR(10) NOT NULL,
13    registration_date date NOT NULL,
14    vacation_date date NOT NULL,
15    phone_number VARCHAR(20) NOT NULL,
16    martial_status VARCHAR(20) NULL,
17    PRIMARY KEY (id) 
18);
19
20CREATE TABLE IF NOT EXISTS rooms 
21( 
22    id INT UNSIGNED auto_increment, 
23    stars VARCHAR(10) NOT NULL,
24    available BOOLEAN NOT NULL,
25    guest_id INT unsigned,
26    PRIMARY KEY (id),
27    CONSTRAINT fk_guest_room_id 
28    FOREIGN KEY (guest_id) REFERENCES guests(id)
29);
30
31CREATE TABLE IF NOT EXISTS employees
32( 
33    id INT UNSIGNED auto_increment,
34    title VARCHAR(20) NOT NULL,
35    full_name VARCHAR(30) NOT NULL,
36    birth_DATE DATE NOT NULL,
37    address VARCHAR(30) NOT NULL,
38    phone_number VARCHAR(20) NOT NULL,
39    martial_status VARCHAR(10) NULL,
40    PRIMARY KEY (id) 
41);
42
43CREATE TABLE IF NOT EXISTS jobs
44(
45    id INT UNSIGNED auto_increment,
46    title VARCHAR(30) NOT NULL,
47    PRIMARY KEY (id)
48);
49
50CREATE TABLE IF NOT EXISTS payments
51(
52    id INT UNSIGNED auto_increment,
53    guest_id INT unsigned,
54    amount float(5) NOT NULL,
55    payment_date DATE NOT NULL,
56    PRIMARY KEY (id),
57    CONSTRAINT fk_guest_payment_id 
58    FOREIGN KEY (guest_id) REFERENCES guests(id)
59);
60
61/* Table population */
62INSERT INTO guests (room_number, full_name, passport_number, residency, registration_date, vacation_date, phone_number) VALUES 
63    (101, 'Frank Billeti', 'P2154125', 'France', '2018/12/10', '2018/12/17', '123-456-00-00'),
64    (205, 'Jack Daniels',  'U1251252', 'Poland', '2018/12/12', '2018/12/15', '112-426-62-05'),
65    (108, 'Julia Frank', 'D6154125', 'Spain', '2018/12/10', '2018/12/30', '173-456-67-21'),
66    (306, 'Mike Johnson', 'S3124295', 'Jamaica', '2018/12/10', '2018/12/17', '162-457-26-00'),
67    (404, 'Andriy Shavko', 'P6156915', 'Ukraine', '2018/12/19', '2018/12/27', '733-456-10-20');
68    
69
70INSERT INTO jobs (title) VALUES
71    ('Receptionist'),
72    ('Manager'),
73    ('Security'),
74    ('Electrician'),
75    ('Plumber'),
76    ('Gardner'),
77    ('Janitor');