8kpVUgYc

1DROP TABLE IF EXISTS Apartment;
2    
3CREATE TABLE Apartment (
4  id INTEGER NOT NULL AUTO_INCREMENT,
5  country CHAR NULL DEFAULT NULL,
6  city CHAR NULL DEFAULT NULL,
7  address CHAR NULL DEFAULT NULL,
8  number_of_beds INTEGER NULL DEFAULT 1,
9  date_from DATE NULL DEFAULT NULL,
10  date_to DATE NULL DEFAULT NULL,
11  FK_user_id INTEGER NOT NULL,
12  PRIMARY KEY (id)
13);
14
15DROP TABLE IF EXISTS Application;
16    
17CREATE TABLE Application (
18  id INTEGER NOT NULL AUTO_INCREMENT,
19  date_from DATE NULL DEFAULT NULL,
20  date_to DATE NULL DEFAULT NULL,
21  status BINARY NULL DEFAULT NULL,
22  date_of_creation DATE NULL DEFAULT NULL,
23  comment TEXT NULL DEFAULT NULL,
24  FK_apartment_id INTEGER NOT NULL,
25  FK_user_id INTEGER NOT NULL,
26  PRIMARY KEY (id)
27);
28
29DROP TABLE IF EXISTS Photo_of_apartment;
30    
31CREATE TABLE Photo_of_apartment (
32  id INTEGER NOT NULL AUTO_INCREMENT,
33  FK_apartment_id INTEGER NOT NULL,
34  photo_url TEXT NOT NULL,
35  PRIMARY KEY (id)
36);
37
38DROP TABLE IF EXISTS Feedback;
39    
40CREATE TABLE Feedback (
41  id INTEGER NOT NULL AUTO_INCREMENT,
42  comment TEXT NULL DEFAULT NULL,
43  rating INTEGER NULL DEFAULT NULL,
44  FK_application_id INTEGER NOT NULL,
45  PRIMARY KEY (id)
46);
47
48
49DROP TABLE IF EXISTS Photo_of_feedback;
50    
51CREATE TABLE Photo_of_feedback (
52  id INTEGER NOT NULL AUTO_INCREMENT,
53  FK_feedback_id INTEGER NOT NULL,
54  photo_url TEXT NOT NULL,
55  PRIMARY KEY (id)
56);
57
58DROP TABLE IF EXISTS Role;
59    
60CREATE TABLE Role (
61  id INTEGER NOT NULL,
62  name_of_role CHAR NULL DEFAULT NULL,
63  PRIMARY KEY (id)
64);
65
66DROP TABLE IF EXISTS User;
67    
68CREATE TABLE User (
69  id INTEGER NOT NULL AUTO_INCREMENT,
70  name CHAR NULL DEFAULT NULL,
71  birthday DATE NULL DEFAULT NULL,
72  gender BINARY NULL DEFAULT NULL,
73  community_role CHAR NULL DEFAULT NULL,
74  living_place CHAR NULL DEFAULT NULL,
75  telephone CHAR NULL DEFAULT NULL,
76  mail CHAR NULL DEFAULT NULL,
77  interests TEXT NULL DEFAULT NULL,
78  date_of_registration DATE NULL DEFAULT NULL,
79  note TEXT NULL DEFAULT NULL,
80  PRIMARY KEY (id)
81);
82
83DROP TABLE IF EXISTS Photo_of_User;
84    
85CREATE TABLE Photo_of_User (
86  id INTEGER NOT NULL AUTO_INCREMENT,
87  FK_user_id INTEGER NOT NULL,
88  photo_url TEXT NOT NULL,
89  PRIMARY KEY (id)
90);
91
92DROP TABLE IF EXISTS Role_User;
93    
94CREATE TABLE Role_User (
95  FK_role_id INTEGER NOT NULL,
96  FK_user_id INTEGER NOT NULL,
97  PRIMARY KEY (FK_user_id)
98);
99
100DROP TABLE IF EXISTS Friends_with;
101    
102CREATE TABLE Friends_with (
103  FK_user_id_1 INTEGER NOT NULL,
104  FK_user_id_2 INTEGER NOT NULL,
105  date_of_start_friendship DATE NULL DEFAULT NULL,
106  PRIMARY KEY (FK_user_id_1)
107);
108
109ALTER TABLE Apartment ADD FOREIGN KEY (FK_user_id) REFERENCES User (id);
110ALTER TABLE Application ADD FOREIGN KEY (FK_apartment_id) REFERENCES Apartment (id);
111ALTER TABLE Application ADD FOREIGN KEY (FK_user_id) REFERENCES User (id);
112ALTER TABLE Photo_of_apartment ADD FOREIGN KEY (FK_apartment_id) REFERENCES Apartment (id);
113ALTER TABLE Feedback ADD FOREIGN KEY (FK_application_id) REFERENCES Application (id);
114ALTER TABLE Photo_of_feedback ADD FOREIGN KEY (FK_feedback_id) REFERENCES Feedback (id);
115ALTER TABLE Photo_of_User ADD FOREIGN KEY (FK_user_id) REFERENCES User (id);
116ALTER TABLE Role_User ADD FOREIGN KEY (FK_role_id) REFERENCES Role (id);
117ALTER TABLE Role_User ADD FOREIGN KEY (FK_user_id) REFERENCES User (id);
118ALTER TABLE Friends_with ADD FOREIGN KEY (FK_user_id_1) REFERENCES User (id);
119ALTER TABLE Friends_with ADD FOREIGN KEY (FK_user_id_2) REFERENCES User (id);