· 4 years ago · May 21, 2021, 08:24 PM
1-- -----------------------------------------------------
2-- Schema estate_agency_db
3-- -----------------------------------------------------
4CREATE SCHEMA `estate_agency_db`;
5USE `estate_agency_db` ;
6
7-- -----------------------------------------------------
8-- Table `estate_agency_db`.`address`
9-- -----------------------------------------------------
10CREATE TABLE IF NOT EXISTS `estate_agency_db`.`address` (
11 `id_address` INT NOT NULL AUTO_INCREMENT,
12 `street` VARCHAR(45) NOT NULL,
13 `home_number` INT NOT NULL,
14 `flat_number` INT NULL DEFAULT NULL,
15 `postalcode` VARCHAR(6) NOT NULL,
16 `town` VARCHAR(45) NOT NULL,
17 PRIMARY KEY (`id_address`));
18
19-- -----------------------------------------------------
20-- Table `estate_agency_db`.`type`
21-- -----------------------------------------------------
22CREATE TABLE IF NOT EXISTS `estate_agency_db`.`type` (
23 `id_type` INT NOT NULL AUTO_INCREMENT,
24 `name` VARCHAR(100) NOT NULL,
25 PRIMARY KEY (`id_type`));
26
27
28-- -----------------------------------------------------
29-- Table `estate_agency_db`.`transaction_type`
30-- -----------------------------------------------------
31CREATE TABLE IF NOT EXISTS `estate_agency_db`.`transaction_type` (
32 `id_transaction_type` INT NOT NULL AUTO_INCREMENT,
33 `transaction_name` VARCHAR(45) NOT NULL,
34 PRIMARY KEY (`id_transaction_type`));
35
36
37-- -----------------------------------------------------
38-- Table `estate_agency_db`.`property`
39-- -----------------------------------------------------
40CREATE TABLE IF NOT EXISTS `estate_agency_db`.`property` (
41 `id_property` INT NOT NULL AUTO_INCREMENT,
42 `id_address` VARCHAR(45) NOT NULL,
43 `price` INT NOT NULL,
44 `id_ transaction` INT NOT NULL,
45 `id_property_type` INT NOT NULL,
46 PRIMARY KEY (`idproperty`),
47 INDEX `id_property_type_idx` (`id_property_type` ASC) VISIBLE,
48 INDEX `id_transaction_type_idx` (`id_ transaction` ASC) VISIBLE,
49 CONSTRAINT `id_property_type`
50 FOREIGN KEY (`id_property_type`)
51 REFERENCES `estate_agency_db`.`type` (`id_type`),
52 CONSTRAINT `id_transaction_type`
53 FOREIGN KEY (`id_ transaction`)
54 REFERENCES `estate_agency_db`.`transaction_type` (`id_transaction_type`));
55
56
57-- -----------------------------------------------------
58-- Table `estate_agency_db`.`building`
59-- -----------------------------------------------------
60CREATE TABLE IF NOT EXISTS `estate_agency_db`.`building` (
61 `id_building` INT NOT NULL,
62 `living_space` FLOAT NULL DEFAULT NULL,
63 `bedrooms` INT NULL DEFAULT NULL,
64 `bathrooms` INT NULL DEFAULT NULL,
65 `rooms` INT NULL DEFAULT NULL,
66 `property_id` INT NOT NULL,
67 PRIMARY KEY (`id_building`),
68 INDEX `property_id_idx` (`property_id` ASC) VISIBLE,
69 CONSTRAINT `property_id`
70 FOREIGN KEY (`property_id`)
71 REFERENCES `estate_agency_db`.`property` (`id_property`));
72
73
74-- -----------------------------------------------------
75-- Table `estate_agency_db`.`building_type`
76-- -----------------------------------------------------
77CREATE TABLE IF NOT EXISTS `estate_agency_db`.`building_type` (
78 `id_building_type` INT NOT NULL,
79 `name` VARCHAR(45) NOT NULL,
80 `id_building_type` INT NOT NULL,
81 PRIMARY KEY (`idbuilding_type`),
82 INDEX `building_id_idx` (`id_building_type` ASC) VISIBLE,
83 CONSTRAINT `building_id`
84 FOREIGN KEY (`id_building_type`)
85 REFERENCES `estate_agency_db`.`building` (`id_building`));
86
87-- -----------------------------------------------------
88-- Table `estate_agency_db`.`contact`
89-- -----------------------------------------------------
90CREATE TABLE IF NOT EXISTS `estate_agency_db`.`contact` (
91 `id_contact` INT NOT NULL AUTO_INCREMENT,
92 `email` VARCHAR(45) NOT NULL,
93 `telephone_num` INT NOT NULL,
94 PRIMARY KEY (`id_contact`),
95 UNIQUE INDEX `email_UNIQUE` (`email` ASC) VISIBLE);
96
97
98-- -----------------------------------------------------
99-- Table `estate_agency_db`.`personal_data`
100-- -----------------------------------------------------
101CREATE TABLE IF NOT EXISTS `estate_agency_db`.`personal_data` (
102 `id_personal_data` INT NOT NULL AUTO_INCREMENT,
103 `date` DATE NOT NULL,
104 `identity_number` INT NULL DEFAULT NULL,
105 PRIMARY KEY (`id_personal_data`));
106
107
108-- -----------------------------------------------------
109-- Table `estate_agency_db`.`person`
110-- -----------------------------------------------------
111CREATE TABLE IF NOT EXISTS `estate_agency_db`.`person` (
112 `id_person` INT NOT NULL AUTO_INCREMENT,
113 `name` VARCHAR(45) NOT NULL,
114 `surname` VARCHAR(45) NOT NULL,
115 `identity_number` INT NOT NULL,
116 `id_address` INT NOT NULL,
117 `id_contact` INT NULL DEFAULT NULL,
118 `id_personal_data` INT NULL DEFAULT NULL,
119 PRIMARY KEY (`id_person`),
120 UNIQUE INDEX `identity_number_UNIQUE` (`identity_number` ASC) VISIBLE,
121 UNIQUE INDEX `id_address_UNIQUE` (`id_address` ASC) VISIBLE,
122 INDEX `id_contact_idx` (`id_contact` ASC) VISIBLE,
123 INDEX `id_personal_data_idx` (`id_personal_data` ASC) VISIBLE,
124 CONSTRAINT `id_address`
125 FOREIGN KEY (`id_address`)
126 REFERENCES `estate_agency_db`.`address` (`id_address`),
127 CONSTRAINT `id_contact`
128 FOREIGN KEY (`id_contact`)
129 REFERENCES `estate_agency_db`.`contact` (`id_contact`),
130 CONSTRAINT `id_personal_data`
131 FOREIGN KEY (`id_personal_data`)
132 REFERENCES `estate_agency_db`.`personal_data` (`id_personal_data`));
133
134
135-- podzielic na sprzedajacego i kupujacego
136CREATE TABLE IF NOT EXISTS `estate_agency_db`.`client` (
137 `id_client` INT NOT NULL AUTO_INCREMENT,
138 `id_person` INT NOT NULL,
139 `id_property` INT NULL DEFAULT NULL,
140 PRIMARY KEY (`id_client`),
141 INDEX `id_person_idx` (`id_person` ASC) VISIBLE,
142 INDEX `id_property_idx` (`id_property` ASC) VISIBLE,
143 CONSTRAINT `id_person`
144 FOREIGN KEY (`id_person`)
145 REFERENCES `estate_agency_db`.`person` (`id_person`),
146 CONSTRAINT `id_property`
147 FOREIGN KEY (`id_property`)
148 REFERENCES `estate_agency_db`.`property` (`id_property`));
149
150
151-- -----------------------------------------------------
152-- Table `estate_agency_db`.`employee`
153-- -----------------------------------------------------
154CREATE TABLE IF NOT EXISTS `estate_agency_db`.`employee` (
155 `id_employee` INT NOT NULL AUTO_INCREMENT,
156 `salary` INT NOT NULL,
157 `occupation` VARCHAR(45) NOT NULL,
158 `office` VARCHAR(45) NOT NULL,
159 `person_id` INT NOT NULL,
160 `case_id` INT NULL DEFAULT NULL,
161 PRIMARY KEY (`id_employee`),
162 INDEX `person_id_idx` (`person_id` ASC) VISIBLE,
163 INDEX `case_id_idx` (`case_id` ASC) VISIBLE,
164 CONSTRAINT `case_id`
165 FOREIGN KEY (`case_id`)
166 REFERENCES `estate_agency_db`.`property` (`id_property`),
167 CONSTRAINT `person_id`
168 FOREIGN KEY (`person_id`)
169 REFERENCES `estate_agency_db`.`person` (`id_person`));
170
171
172-- -----------------------------------------------------
173-- Table `estate_agency_db`.`owners`
174-- -----------------------------------------------------
175CREATE TABLE IF NOT EXISTS `estate_agency_db`.`owners` (
176 `id_owners` INT NOT NULL AUTO_INCREMENT,
177 `id_person` INT NOT NULL,
178 `id_property` INT NOT NULL,
179 PRIMARY KEY (`id_owners`),
180 INDEX `id_person_idx` (`id_person` ASC) VISIBLE,
181 INDEX `id_property_idx` (`id_property` ASC) VISIBLE,
182 CONSTRAINT `id_oproperty`
183 FOREIGN KEY (`id_property`)
184 REFERENCES `estate_agency_db`.`property` (`id_property`),
185 CONSTRAINT `id_powner`
186 FOREIGN KEY (`id_person`)
187 REFERENCES `estate_agency_db`.`person` (`id_person`));
188
189-- -----------------------------------------------------
190-- Table `estate_agency_db`.`parcel_type`
191-- -----------------------------------------------------
192CREATE TABLE IF NOT EXISTS `estate_agency_db`.`parcel_type` (
193 `id_parcel_type` INT NOT NULL,
194 `name` VARCHAR(100) NOT NULL,
195 PRIMARY KEY (`id_parcel_type`));
196
197
198-- -----------------------------------------------------
199-- Table `estate_agency_db`.`parcel`
200-- -----------------------------------------------------
201CREATE TABLE IF NOT EXISTS `estate_agency_db`.`parcel` (
202 `id_parcel` INT NOT NULL,
203 `id_parcel_type` INT NOT NULL,
204 `id_property` INT NOT NULL,
205 PRIMARY KEY (`id_parcel`),
206 INDEX `parcel_type_id_idx` (`id_parcel_type` ASC) VISIBLE,
207 INDEX `parcel_property_id_idx` (`id_property` ASC) VISIBLE,
208 CONSTRAINT `parcel_property_id`
209 FOREIGN KEY (`id_property`)
210 REFERENCES `estate_agency_db`.`property` (`id_property`),
211 CONSTRAINT `parcel_type_id`
212 FOREIGN KEY (`id_parcel_type`)
213 REFERENCES `estate_agency_db`.`parcel_type` (`id_parcel_type`));
214