20QQuNhT

1CREATE TABLE person (
2
3    id INT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE,
4    name text NOT NULL,
5    address text NOT NULL,
6    email varchar(256) NOT NULL,
7    contact_number text NOT NULL,
8    date_of_birth DATE NOT NULL,
9    age INT NOT NULL,
10
11    PRIMARY KEY(id)
12);
13DROP TABLE IF EXISTS customer;
14CREATE TABLE customer (
15    id INT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE,
16    person_id INT UNSIGNED NOT NULL,
17    PRIMARY KEY(id),
18    FOREIGN KEY(person_id) REFERENCES person(id)
19);
20
21CREATE TABLE employee (
22    id INT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE,
23    person_id INT UNSIGNED NOT NULL,
24    hours_worked INT NOT NULL,
25    hourly_rate double NOT NULL,
26
27    PRIMARY KEY(id),
28    FOREIGN KEY(person_id) REFERENCES person(id)
29);
30
31CREATE TABLE management (
32    id INT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE,
33    role text NOT NULL,
34    employee_id INT UNSIGNED NOT NULL,   
35
36    PRIMARY KEY(id),
37    FOREIGN KEY(employee_id) REFERENCES employee(id)
38);
39
40CREATE TABLE general_staff (
41    id INT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE,
42    position text NOT NULL,
43    location text NOT NULL,
44    employee_id int UNSIGNED NOT NULL,
45
46    PRIMARY KEY(id),
47    FOREIGN KEY(employee_id) REFERENCES employee(id)
48);
49
50CREATE TABLE office (
51    id INT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE,
52    office_number INT NOT NULL,
53    employee_id INT UNSIGNED NOT NULL,
54
55    PRIMARY KEY(id),
56    FOREIGN KEY(employee_id) REFERENCES employee(id)
57);
58
59CREATE TABLE payment (
60    id INT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE,
61    reservation_id INT UNSIGNED NOT NULL,
62    customer_id INT UNSIGNED NOT NULL,
63    room_id INT UNSIGNED NOT NULL,
64    amount float NOT NULL,
65    number_of_guests INT NOT NULL,
66
67    PRIMARY KEY(id),
68    FOREIGN KEY(customer_id) REFERENCES customer(id)
69);
70
71
72CREATE TABLE room_type
73    (
74
75        id INT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE,
76        description text NOT NULL,
77        price double NOT NULL,
78        guest_max INT NOT NULL,
79
80        PRIMARY KEY(id)
81    );
82
83CREATE TABLE room (
84
85    id INT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE,
86    room_type_id INT UNSIGNED NOT NULL,
87
88PRIMARY KEY(id),
89FOREIGN KEY(room_type_id) REFERENCES room_type(id)
90
91);
92
93CREATE TABLE reservation (
94    id INT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE,
95    status INT NOT NULL,
96    number_of_guests INT NOT NULL,
97    start_date DATE NOT NULL,
98    end_date DATE NOT NULL,
99    room_id INT NOT NULL,
100    payment_id INT UNSIGNED NOT NULL,
101    customer_id INT UNSIGNED NOT NULL,
102
103    PRIMARY KEY(id),
104    FOREIGN KEY(customer_id) REFERENCES customer(id),
105    FOREIGN KEY(payment_id) REFERENCES payment(id)
106    );
107
108ALTER TABLE payment
109 ADD FOREIGN KEY(reservation_id) REFERENCES reservation(id),
110 ADD FOREIGN KEY(room_id) REFERENCES room(id);
111
112ALTER TABLE reservation
113    ADD FOREIGN KEY(room_id) REFERENCES room(id);
114
115    CREATE TABLE booking
116    (
117
118        id INT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE,
119        desk_booking text NOT NULL,
120        online_booking text NOT NULL,
121        PRIMARY KEY(id)
122    );