NZFDW7fP

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
71CREATE TABLE room (
72
73    id INT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE,
74    room_type_id INT UNSIGNED NOT NULL,
75
76PRIMARY KEY(id),
77);
78
79
80CREATE TABLE reservation (
81    id INT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE,
82    status INT NOT NULL,
83    number_of_guests INT NOT NULL,
84    start_date DATE NOT NULL,
85    end_date DATE NOT NULL,
86    room_id INT NOT NULL,
87    payment_id INT UNSIGNED NOT NULL,
88    customer_id INT UNSIGNED NOT NULL,
89
90    PRIMARY KEY(id),
91    FOREIGN KEY(customer_id) REFERENCES customer(id),
92    FOREIGN KEY(payment_id) REFERENCES payment(id),
93    FOREIGN KEY(room_id) REFERENCES room(id)
94    );
95
96
97
98CREATE TABLE room_type
99    (
100
101        id INT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE,
102        description text NOT NULL,
103        price double NOT NULL,
104        guest_max INT NOT NULL,
105
106        PRIMARY KEY(id)
107    );
108
109ALTER TABLE room
110    ADD FOREIGN KEY(room_type_id) REFERENCES room_type(id);
111
112ALTER TABLE payment
113 ADD FOREIGN KEY(reservation_id) REFERENCES reservation(id),
114 ADD FOREIGN KEY(room_id) REFERENCES room(id);
115
116    CREATE TABLE booking
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    );