VFDHe5qX

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