HCG-Interview-Test/CreateTable.sql at main · L3AZH/HCG-Interview-Test · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
-- CREATE ENTITY--
CREATE DATABASE bookingsystemdb;
USE bookingsystemdb;
CREATE TABLE IF NOT EXISTS `hotel` (
    `id` INT UNSIGNED AUTO_INCREMENT,
    `name` NVARCHAR(255),
    PRIMARY KEY (`id`)
)  ENGINE=INNODB;

CREATE TABLE IF NOT EXISTS `room_type` (
    `id` INT UNSIGNED AUTO_INCREMENT,
    `name` NVARCHAR(255),
    PRIMARY KEY (`id`)
)  ENGINE=INNODB;

CREATE TABLE IF NOT EXISTS `rate_plan` (
    `id` INT UNSIGNED AUTO_INCREMENT,
    `name` NVARCHAR(255),
    PRIMARY KEY (`id`)
)  ENGINE=INNODB;

CREATE TABLE IF NOT EXISTS `period` (
    `id` INT UNSIGNED AUTO_INCREMENT,
    `start_date` DATETIME NOT NULL,
    `end_date` DATETIME,
    PRIMARY KEY (`id`)
)  ENGINE=INNODB;

CREATE TABLE IF NOT EXISTS `room_rate` (
    `room_type_id` INT UNSIGNED,
    `date` DATE,
    `price` DECIMAL(20 , 2 ),
    `default_rate_plan` INT UNSIGNED,
    PRIMARY KEY (`room_type_id` , `date`),
    FOREIGN KEY (`room_type_id`)
        REFERENCES room_type (`id`)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    FOREIGN KEY (`default_rate_plan`)
        REFERENCES rate_plan (`id`)
        ON UPDATE CASCADE ON DELETE RESTRICT
)  ENGINE=INNODB;

CREATE TABLE IF NOT EXISTS `room_availability` (
    `room_type_id` INT UNSIGNED,
    `date` DATE,
    `available_to_sell` INT,
    PRIMARY KEY (`room_type_id` , `date`),
    FOREIGN KEY (`room_type_id`)
        REFERENCES room_type (`id`)
        ON UPDATE CASCADE ON DELETE RESTRICT
)  ENGINE=INNODB;

CREATE TABLE IF NOT EXISTS `guest` (
    `email` NVARCHAR(255),
    `phone_number` NVARCHAR(30),
    PRIMARY KEY (`email`)
)  ENGINE=INNODB;

CREATE TABLE IF NOT EXISTS `booking` (
    `id` INT UNSIGNED AUTO_INCREMENT,
    `guest_email` NVARCHAR(255),
    `price` DECIMAL(20 , 2 ),
    `room_type_id` INT UNSIGNED,
    `rate_plan_id` INT UNSIGNED,
    PRIMARY KEY (`id`),
    FOREIGN KEY (`guest_email`)
        REFERENCES guest (`email`)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    FOREIGN KEY (`room_type_id`)
        REFERENCES room_type (`id`)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    FOREIGN KEY (`rate_plan_id`)
        REFERENCES rate_plan (`id`)
        ON UPDATE CASCADE ON DELETE RESTRICT
)  ENGINE=INNODB;

-- CREATE RELATIONSHIP--

CREATE TABLE IF NOT EXISTS `hotel_rel_room_type` (
    `hotel_id` INT UNSIGNED,
    `room_type_id` INT UNSIGNED,
    PRIMARY KEY (`hotel_id` , `room_type_id`),
    FOREIGN KEY (`hotel_id`)
        REFERENCES hotel (`id`)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    FOREIGN KEY (`room_type_id`)
        REFERENCES room_type (`id`)
        ON UPDATE CASCADE ON DELETE RESTRICT
)  ENGINE=INNODB;

CREATE TABLE IF NOT EXISTS `hotel_rel_rate_plan` (
    `hotel_id` INT UNSIGNED,
    `rate_plan_id` INT UNSIGNED,
    PRIMARY KEY (`hotel_id` , `rate_plan_id`),
    FOREIGN KEY (`hotel_id`)
        REFERENCES hotel (`id`)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    FOREIGN KEY (`rate_plan_id`)
        REFERENCES rate_plan (`id`)
        ON UPDATE CASCADE ON DELETE RESTRICT
)  ENGINE=INNODB;

CREATE TABLE IF NOT EXISTS `period_rel_rate_plan` (
    `period_id` INT UNSIGNED,
    `rate_plan_id` INT UNSIGNED,
    PRIMARY KEY (`period_id` , `rate_plan_id`),
    FOREIGN KEY (`period_id`)
        REFERENCES period (`id`)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    FOREIGN KEY (`rate_plan_id`)
        REFERENCES rate_plan (`id`)
        ON UPDATE CASCADE ON DELETE RESTRICT
)  ENGINE=INNODB;

CREATE TABLE IF NOT EXISTS `room_type_rel_rate_plan`(
    `room_type_id` INT UNSIGNED,
    `rate_plan_id` INT UNSIGNED,
    PRIMARY KEY (`room_type_id`,`rate_plan_id`),
    FOREIGN KEY (`room_type_id`)
        REFERENCES room_type(`id`)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    FOREIGN KEY (`rate_plan_id`)
        REFERENCES rate_plan (`id`)
        ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE=INNODB;