kSucykT9

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