wSfKWNC2

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