JUU9bHXd

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 int(11) NOT NULL,
17
18    PRIMARY KEY(id)
19);
20
21CREATE TABLE IF NOT EXISTS `employee` (
22    id int(11) UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE,
23    person_id int(11) NOT NULL,
24    hours_worked int(11) NOT NULL,
25    office int(11) NOT NULL,
26
27    PRIMARY KEY(id),
28    FOREIGN KEY(person_id) REFERENCES person(id)
29);
30
31CREATE TABLE IF NOT EXISTS `office` (
32    id int(11) UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE,
33    office_number int(11) NOT NULL,
34    PRIMARY KEY(id)
35);
36
37CREATE TABLE IF NOT EXISTS `payment` (
38    id int(11) UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE,
39    customer_id int(11) NOT NULL,
40    room_id int(11) NOT NULL,
41    amount float NOT NULL,
42    reservation_id int(11) NOT NULL,
43    number_of_guests int(11) NOT NULL,
44
45    PRIMARY KEY(id),
46    FOREIGN KEY(reservation_id) REFERENCES reservation(id),
47    FOREIGN KEY(customer_id) REFERENCES customer(id)
48
49);
50
51CREATE TABLE IF NOT EXISTS `reservation` (
52    id int(11) UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE,
53    `status` int(11) NOT NULL,
54    number_of_guests int(11) NOT NULL,
55    start_date text NOT NULL,
56    end_date text NOT NULL,
57    room_id int(11) NOT NULL,
58    payment_id int(11) NOT NULL,
59    customer_id int(11) NOT NULL,
60
61    PRIMARY KEY(id),
62    FOREIGN KEY(customer_id) REFERENCES customer(id)
63
64);
65
66CREATE TABLE IF NOT EXISTS `roles` (
67    id int(11) UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE,
68    `name` text NOT NULL,
69    description text NOT NULL,
70    rate double NOT NULL,
71    hours int(11) NOT NULL,
72    customer_id int(11) NOT NULL,
73    PRIMARY KEY(id),
74    FOREIGN KEY(customer_id) REFERENCES customer(id)
75
76);
77
78CREATE TABLE IF NOT EXISTS `room` (
79
80    id int(11) UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE,
81    room_type int(11) NOT NULL,
82
83PRIMARY KEY(id)
84
85);
86
87
88CREATE TABLE IF NOT EXISTS `room_type`
89    (
90
91        id int(11) UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE,
92        description int(11) NOT NULL,
93        price double NOT NULL,
94        guests_max int(11) NOT NULL,
95        customer_id int(11) NOT NULL,
96
97        PRIMARY KEY(id),
98        FOREIGN KEY(customer_id) REFERENCES customer(id)
99
100    );