iY1GWJMp

1CREATE TABLE IF NOT EXISTS 'person' (
2
3    id int(11) NOT NULL UNSIGNED 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 NOT NULL UNSIGNED 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) NOT NULL UNSIGNED 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) NOT NULL UNSIGNED AUTO_INCREMENT UNIQUE,
33    office_number int(11) NOT NULL PRIMARY KEY(id),
34);
35
36CREATE TABLE IF NOT EXISTS 'payment' (
37    id int(11) NOT NULL UNSIGNED AUTO_INCREMENT UNIQUE,
38    customer_id int(11) NOT NULL,
39    room_id int(11) NOT NULL,
40    amount float NOT NULL,
41    reservation_id int(11) NOT NULL,
42    number_of_guests int(11) NOT NULL
43
44    PRIMARY KEY(id),
45    FOREIGN KEY(reservation_id) REFERENCES reservation(id),
46    FOREIGN KEY(customer_id) REFERENCES customer(id),
47
48);
49
50CREATE TABLE IF NOT EXISTS 'reservation' (
51    id int(11) NOT NULL UNSIGNED AUTO_INCREMENT UNIQUE,
52    status int(11) NOT NULL,
53    number_of_guests int(11) NOT NULL,
54    start_date text NOT NULL,
55    end_date text NOT NULL,
56    room_id int(11) NOT NULL,
57    payment_id int(11) NOT NULL
58
59    PRIMARY KEY(id),
60    FOREIGN KEY(customer_id) REFERENCES customer(id),
61
62);
63
64CREATE TABLE IF NOT EXISTS 'roles' (
65    id int(11) NOT NULL UNSIGNED AUTO_INCREMENT UNIQUE,
66    name text NOT NULL,
67    description text NOT NULL,
68    rate double NOT NULL,
69    hours int(11) NOT NULL
70
71    PRIMARY KEY(id),
72    FOREIGN KEY(customer_id) REFERENCES customer(id),
73
74);
75
76CREATE TABLE IF NOT EXISTS 'room' // chek dis // check if you can have room_type and roomtype entity.
77
78    id int(11) NOT NULL UNSIGNED AUTO_INCREMENT UNIQUE,
79    room_type int(11) NOT NULL
80
81PRIMARY KEY(id),
82
83);
84
85
86CREATE TABLE IF NOT EXISTS 'room_type' // chek dis
87    (
88
89        id int(11) NOT NULL UNSIGNED AUTO_INCREMENT UNIQUE,
90        description int(11) NOT NULL,
91        price double NOT NULL,
92        guests_max int(11) NOT NULL
93
94        PRIMARY KEY(id),
95        FOREIGN KEY(customer_id) REFERENCES customer(id),
96
97    );