· 8 years ago · Jan 17, 2018, 05:00 AM
1-- MySQL Script generated by MySQL Workbench
2-- Wed Jan 17 01:22:54 2018
3-- Model: New Model Version: 1.0
4-- MySQL Workbench Forward Engineering
5
6SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
7SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
8SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';
9
10-- -----------------------------------------------------
11-- Schema mydb
12-- -----------------------------------------------------
13DROP SCHEMA IF EXISTS `mydb` ;
14
15-- -----------------------------------------------------
16-- Schema mydb
17-- -----------------------------------------------------
18CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 ;
19USE `mydb` ;
20
21-- -----------------------------------------------------
22-- Table `mydb`.`DIM_MARITAL_STATUS`
23-- -----------------------------------------------------
24DROP TABLE IF EXISTS `mydb`.`DIM_MARITAL_STATUS` ;
25
26CREATE TABLE IF NOT EXISTS `mydb`.`DIM_MARITAL_STATUS` (
27 `idDIM_MARITAL_STATUS` VARCHAR(1) NOT NULL,
28 `DESC_MARITAL_STATUS` VARCHAR(64) NULL,
29 PRIMARY KEY (`idDIM_MARITAL_STATUS`))
30ENGINE = InnoDB;
31
32
33-- -----------------------------------------------------
34-- Table `mydb`.`DIM_ADMINISTRATIVE_SEX`
35-- -----------------------------------------------------
36DROP TABLE IF EXISTS `mydb`.`DIM_ADMINISTRATIVE_SEX` ;
37
38CREATE TABLE IF NOT EXISTS `mydb`.`DIM_ADMINISTRATIVE_SEX` (
39 `idDIM_ADMINISTRATIVE_SEX` VARCHAR(1) NOT NULL,
40 `DESC_ADMINISTRATIVE_SEX` VARCHAR(45) NULL,
41 PRIMARY KEY (`idDIM_ADMINISTRATIVE_SEX`))
42ENGINE = InnoDB;
43
44
45-- -----------------------------------------------------
46-- Table `mydb`.`DIM_EDUCATION_LEVEL`
47-- -----------------------------------------------------
48DROP TABLE IF EXISTS `mydb`.`DIM_EDUCATION_LEVEL` ;
49
50CREATE TABLE IF NOT EXISTS `mydb`.`DIM_EDUCATION_LEVEL` (
51 `idDIM_EDUCATION_LEVEL` VARCHAR(5) NOT NULL,
52 `DESC_EDUCATION_LEVEL` VARCHAR(45) NULL,
53 PRIMARY KEY (`idDIM_EDUCATION_LEVEL`))
54ENGINE = InnoDB;
55
56
57-- -----------------------------------------------------
58-- Table `mydb`.`DIM_NATIONALITY`
59-- -----------------------------------------------------
60DROP TABLE IF EXISTS `mydb`.`DIM_NATIONALITY` ;
61
62CREATE TABLE IF NOT EXISTS `mydb`.`DIM_NATIONALITY` (
63 `idDIM_NATIONALITY` INT NOT NULL,
64 `DESC_NATIONALITY` VARCHAR(45) NULL,
65 PRIMARY KEY (`idDIM_NATIONALITY`))
66ENGINE = InnoDB;
67
68
69-- -----------------------------------------------------
70-- Table `mydb`.`DIM_BENEFICIARY_SECTION`
71-- -----------------------------------------------------
72DROP TABLE IF EXISTS `mydb`.`DIM_BENEFICIARY_SECTION` ;
73
74CREATE TABLE IF NOT EXISTS `mydb`.`DIM_BENEFICIARY_SECTION` (
75 `idDIM_BENEFICIARY_SECTION` VARCHAR(1) NOT NULL,
76 `DESC_BENEFICIARY_SECTION` VARCHAR(45) NULL,
77 PRIMARY KEY (`idDIM_BENEFICIARY_SECTION`))
78ENGINE = InnoDB;
79
80
81-- -----------------------------------------------------
82-- Table `mydb`.`DIM_SOCIAL_HEALTH_CARE`
83-- -----------------------------------------------------
84DROP TABLE IF EXISTS `mydb`.`DIM_SOCIAL_HEALTH_CARE` ;
85
86CREATE TABLE IF NOT EXISTS `mydb`.`DIM_SOCIAL_HEALTH_CARE` (
87 `idDIM_SOCIAL_HEALTH_CARE` VARCHAR(1) NOT NULL,
88 `DESC_SOCIAL_HEALTH_CARE` VARCHAR(45) NULL,
89 `idDIM_BENEFICIARY_SECTION` VARCHAR(1) NULL DEFAULT NULL,
90 PRIMARY KEY (`idDIM_SOCIAL_HEALTH_CARE`),
91 INDEX `fk_DIM_SOCIAL_WELFARE_DIM_BENEFICIARY_SECTION_idx` (`idDIM_BENEFICIARY_SECTION` ASC),
92 CONSTRAINT `fk_DIM_SOCIAL_WELFARE_DIM_BENEFICIARY_SECTION`
93 FOREIGN KEY (`idDIM_BENEFICIARY_SECTION`)
94 REFERENCES `mydb`.`DIM_BENEFICIARY_SECTION` (`idDIM_BENEFICIARY_SECTION`)
95 ON DELETE NO ACTION
96 ON UPDATE NO ACTION)
97ENGINE = InnoDB;
98
99
100-- -----------------------------------------------------
101-- Table `mydb`.`DIM_LIVING_ARRANGEMENT`
102-- -----------------------------------------------------
103DROP TABLE IF EXISTS `mydb`.`DIM_LIVING_ARRANGEMENT` ;
104
105CREATE TABLE IF NOT EXISTS `mydb`.`DIM_LIVING_ARRANGEMENT` (
106 `idDIM_LIVING_ARRANGEMENT` VARCHAR(1) NOT NULL,
107 `DESC_LIVING_ARRANGEMENT` VARCHAR(45) NULL,
108 PRIMARY KEY (`idDIM_LIVING_ARRANGEMENT`))
109ENGINE = InnoDB;
110
111
112-- -----------------------------------------------------
113-- Table `mydb`.`DIM_COUNTRY`
114-- -----------------------------------------------------
115DROP TABLE IF EXISTS `mydb`.`DIM_COUNTRY` ;
116
117CREATE TABLE IF NOT EXISTS `mydb`.`DIM_COUNTRY` (
118 `idDIM_COUNTRY` INT NOT NULL,
119 `NAME_DIM_COUNTRY` VARCHAR(45) NULL,
120 PRIMARY KEY (`idDIM_COUNTRY`))
121ENGINE = InnoDB;
122
123
124-- -----------------------------------------------------
125-- Table `mydb`.`DIM_REGION`
126-- -----------------------------------------------------
127DROP TABLE IF EXISTS `mydb`.`DIM_REGION` ;
128
129CREATE TABLE IF NOT EXISTS `mydb`.`DIM_REGION` (
130 `idDIM_REGION` INT NOT NULL,
131 `NAME_REGION` VARCHAR(45) NULL,
132 `idDIM_COUNTRY` INT NULL DEFAULT NULL,
133 PRIMARY KEY (`idDIM_REGION`),
134 INDEX `fk_DIM_REGION_DIM_COUNTRY1_idx` (`idDIM_COUNTRY` ASC),
135 CONSTRAINT `fk_DIM_REGION_DIM_COUNTRY1`
136 FOREIGN KEY (`idDIM_COUNTRY`)
137 REFERENCES `mydb`.`DIM_COUNTRY` (`idDIM_COUNTRY`)
138 ON DELETE NO ACTION
139 ON UPDATE NO ACTION)
140ENGINE = InnoDB;
141
142
143-- -----------------------------------------------------
144-- Table `mydb`.`DIM_CITY`
145-- -----------------------------------------------------
146DROP TABLE IF EXISTS `mydb`.`DIM_CITY` ;
147
148CREATE TABLE IF NOT EXISTS `mydb`.`DIM_CITY` (
149 `idDIM_CITY` INT NOT NULL,
150 `NAME_DIM_CITY` VARCHAR(45) NULL,
151 `idDIM_REGION` INT NULL DEFAULT NULL,
152 PRIMARY KEY (`idDIM_CITY`),
153 INDEX `fk_DIM_CITY_DIM_REGION1_idx` (`idDIM_REGION` ASC),
154 CONSTRAINT `fk_DIM_CITY_DIM_REGION1`
155 FOREIGN KEY (`idDIM_REGION`)
156 REFERENCES `mydb`.`DIM_REGION` (`idDIM_REGION`)
157 ON DELETE NO ACTION
158 ON UPDATE NO ACTION)
159ENGINE = InnoDB;
160
161
162-- -----------------------------------------------------
163-- Table `mydb`.`DIM_COUNTY_OR_COMUNNE`
164-- -----------------------------------------------------
165DROP TABLE IF EXISTS `mydb`.`DIM_COUNTY_OR_COMUNNE` ;
166
167CREATE TABLE IF NOT EXISTS `mydb`.`DIM_COUNTY_OR_COMUNNE` (
168 `idDIM_COUNTY_OR_COMUNNE` INT NOT NULL,
169 `NAME_COUNTY_OR_COMUNNE` VARCHAR(45) NULL,
170 `TYPE_COUNTY_OR_COMUNNE` VARCHAR(45) NULL,
171 `AIR_POLLUTION` DECIMAL(4,2) NULL,
172 `ACCESS_POINTS_TO_HEALTHY_FOOD` DECIMAL(4,2) NULL,
173 `CRIME_RATE` DECIMAL(4,2) NULL,
174 `SQUARE_METERS_OF_GREEN_AREAS` DECIMAL(4,2) NULL,
175 `idDIM_CITY` INT NULL DEFAULT NULL,
176 PRIMARY KEY (`idDIM_COUNTY_OR_COMUNNE`),
177 INDEX `fk_DIM_COUNTY_OR_COMUNNE_DIM_CITY1_idx` (`idDIM_CITY` ASC),
178 CONSTRAINT `fk_DIM_COUNTY_OR_COMUNNE_DIM_CITY1`
179 FOREIGN KEY (`idDIM_CITY`)
180 REFERENCES `mydb`.`DIM_CITY` (`idDIM_CITY`)
181 ON DELETE NO ACTION
182 ON UPDATE NO ACTION)
183ENGINE = InnoDB;
184
185
186-- -----------------------------------------------------
187-- Table `mydb`.`DIM_HOME_ADDRESS`
188-- -----------------------------------------------------
189DROP TABLE IF EXISTS `mydb`.`DIM_HOME_ADDRESS` ;
190
191CREATE TABLE IF NOT EXISTS `mydb`.`DIM_HOME_ADDRESS` (
192 `idDIM_HOME_ADDRESS` INT NOT NULL,
193 `idDIM_COUNTY_OR_COMUNNE` INT NULL DEFAULT NULL,
194 PRIMARY KEY (`idDIM_HOME_ADDRESS`),
195 INDEX `fk_DIM_HOME_ADDRESS_DIM_COUNTY_OR_COMUNNE1_idx` (`idDIM_COUNTY_OR_COMUNNE` ASC),
196 CONSTRAINT `fk_DIM_HOME_ADDRESS_DIM_COUNTY_OR_COMUNNE1`
197 FOREIGN KEY (`idDIM_COUNTY_OR_COMUNNE`)
198 REFERENCES `mydb`.`DIM_COUNTY_OR_COMUNNE` (`idDIM_COUNTY_OR_COMUNNE`)
199 ON DELETE NO ACTION
200 ON UPDATE NO ACTION)
201ENGINE = InnoDB;
202
203
204-- -----------------------------------------------------
205-- Table `mydb`.`DIM_DIAGNOSIS_CODE`
206-- -----------------------------------------------------
207DROP TABLE IF EXISTS `mydb`.`DIM_DIAGNOSIS_CODE` ;
208
209CREATE TABLE IF NOT EXISTS `mydb`.`DIM_DIAGNOSIS_CODE` (
210 `idDIM_DIAGNOSIS_CODE` VARCHAR(10) NOT NULL,
211 `DESC_DIM_DIAGNOSIS_CODE` VARCHAR(200) NULL,
212 PRIMARY KEY (`idDIM_DIAGNOSIS_CODE`))
213ENGINE = InnoDB;
214
215
216-- -----------------------------------------------------
217-- Table `mydb`.`DIM_PATIENT`
218-- -----------------------------------------------------
219DROP TABLE IF EXISTS `mydb`.`DIM_PATIENT` ;
220
221CREATE TABLE IF NOT EXISTS `mydb`.`DIM_PATIENT` (
222 `idDIM_PATIENT` INT NOT NULL,
223 `AGE` INT NULL,
224 `WEIGHT` DECIMAL(3,1) NULL,
225 `HEIGHT` DECIMAL(1,1) NULL,
226 `PER_CAPITA_INCOME` DECIMAL(8,2) NULL,
227 `idDIM_MARITAL_STATUS` VARCHAR(1) NULL DEFAULT NULL,
228 `idDIM_ADMINISTRATIVE_SEX` VARCHAR(1) NULL DEFAULT NULL,
229 `idDIM_LIVING_ARRANGEMENT` VARCHAR(1) NULL DEFAULT NULL,
230 `idDIM_EDUCATION_LEVEL` VARCHAR(5) NULL DEFAULT NULL,
231 `idDIM_NATIONALITY` INT NULL DEFAULT NULL,
232 `idDIM_SOCIAL_HEALTH_CARE` VARCHAR(1) NULL DEFAULT NULL,
233 `idDIM_HOME_ADDRESS` INT NULL DEFAULT NULL,
234 PRIMARY KEY (`idDIM_PATIENT`),
235 INDEX `fk_DIM_PATIENT_DIM_MARITAL_STATUS1_idx` (`idDIM_MARITAL_STATUS` ASC),
236 INDEX `fk_DIM_PATIENT_DIM_ADMINISTRATIVE_SEX1_idx` (`idDIM_ADMINISTRATIVE_SEX` ASC),
237 INDEX `fk_DIM_PATIENT_DIM_LIVING_ARRANGEMENT1_idx` (`idDIM_LIVING_ARRANGEMENT` ASC),
238 INDEX `fk_DIM_PATIENT_DIM_EDUCATION_LEVEL1_idx` (`idDIM_EDUCATION_LEVEL` ASC),
239 INDEX `fk_DIM_PATIENT_DIM_NATIONALITY1_idx` (`idDIM_NATIONALITY` ASC),
240 INDEX `fk_DIM_PATIENT_DIM_SOCIAL_HEALTH_CARE1_idx` (`idDIM_SOCIAL_HEALTH_CARE` ASC),
241 INDEX `fk_DIM_PATIENT_DIM_HOME_ADDRESS1_idx` (`idDIM_HOME_ADDRESS` ASC),
242 CONSTRAINT `fk_DIM_PATIENT_DIM_MARITAL_STATUS1`
243 FOREIGN KEY (`idDIM_MARITAL_STATUS`)
244 REFERENCES `mydb`.`DIM_MARITAL_STATUS` (`idDIM_MARITAL_STATUS`)
245 ON DELETE NO ACTION
246 ON UPDATE NO ACTION,
247 CONSTRAINT `fk_DIM_PATIENT_DIM_ADMINISTRATIVE_SEX1`
248 FOREIGN KEY (`idDIM_ADMINISTRATIVE_SEX`)
249 REFERENCES `mydb`.`DIM_ADMINISTRATIVE_SEX` (`idDIM_ADMINISTRATIVE_SEX`)
250 ON DELETE NO ACTION
251 ON UPDATE NO ACTION,
252 CONSTRAINT `fk_DIM_PATIENT_DIM_LIVING_ARRANGEMENT1`
253 FOREIGN KEY (`idDIM_LIVING_ARRANGEMENT`)
254 REFERENCES `mydb`.`DIM_LIVING_ARRANGEMENT` (`idDIM_LIVING_ARRANGEMENT`)
255 ON DELETE NO ACTION
256 ON UPDATE NO ACTION,
257 CONSTRAINT `fk_DIM_PATIENT_DIM_EDUCATION_LEVEL1`
258 FOREIGN KEY (`idDIM_EDUCATION_LEVEL`)
259 REFERENCES `mydb`.`DIM_EDUCATION_LEVEL` (`idDIM_EDUCATION_LEVEL`)
260 ON DELETE NO ACTION
261 ON UPDATE NO ACTION,
262 CONSTRAINT `fk_DIM_PATIENT_DIM_NATIONALITY1`
263 FOREIGN KEY (`idDIM_NATIONALITY`)
264 REFERENCES `mydb`.`DIM_NATIONALITY` (`idDIM_NATIONALITY`)
265 ON DELETE NO ACTION
266 ON UPDATE NO ACTION,
267 CONSTRAINT `fk_DIM_PATIENT_DIM_SOCIAL_HEALTH_CARE1`
268 FOREIGN KEY (`idDIM_SOCIAL_HEALTH_CARE`)
269 REFERENCES `mydb`.`DIM_SOCIAL_HEALTH_CARE` (`idDIM_SOCIAL_HEALTH_CARE`)
270 ON DELETE NO ACTION
271 ON UPDATE NO ACTION,
272 CONSTRAINT `fk_DIM_PATIENT_DIM_HOME_ADDRESS1`
273 FOREIGN KEY (`idDIM_HOME_ADDRESS`)
274 REFERENCES `mydb`.`DIM_HOME_ADDRESS` (`idDIM_HOME_ADDRESS`)
275 ON DELETE NO ACTION
276 ON UPDATE NO ACTION)
277ENGINE = InnoDB
278COMMENT = '\n';
279
280
281-- -----------------------------------------------------
282-- Table `mydb`.`DIM_TIME`
283-- -----------------------------------------------------
284DROP TABLE IF EXISTS `mydb`.`DIM_TIME` ;
285
286CREATE TABLE IF NOT EXISTS `mydb`.`DIM_TIME` (
287 `idDIM_TIME` INT NOT NULL,
288 `DATE_TIME` DATE NULL,
289 `YEAR_TIME` INT NULL,
290 `MONTH_TIME` INT NULL,
291 `DAY_TIME` INT NULL,
292 PRIMARY KEY (`idDIM_TIME`))
293ENGINE = InnoDB;
294
295
296-- -----------------------------------------------------
297-- Table `mydb`.`FAC_MEDICAL_ATTENTION`
298-- -----------------------------------------------------
299DROP TABLE IF EXISTS `mydb`.`FAC_MEDICAL_ATTENTION` ;
300
301CREATE TABLE IF NOT EXISTS `mydb`.`FAC_MEDICAL_ATTENTION` (
302 `idDIM_DIAGNOSIS_CODE` VARCHAR(10) NOT NULL,
303 `idDIM_PATIENT` INT NOT NULL,
304 `idDIM_TIME` INT NOT NULL,
305 `DESC_DIM_MEDICAL_ATTENTION` VARCHAR(500) NULL,
306 INDEX `fk_FAC_MEDICA_ATTENTION_DIM_DIAGNOSIS_CODE1_idx` (`idDIM_DIAGNOSIS_CODE` ASC),
307 INDEX `fk_FAC_MEDICA_ATTENTION_DIM_PATIENT1_idx` (`idDIM_PATIENT` ASC),
308 INDEX `fk_FAC_MEDICA_ATTENTION_DIM_TIME1_idx` (`idDIM_TIME` ASC),
309 CONSTRAINT `fk_FAC_MEDICA_ATTENTION_DIM_DIAGNOSIS_CODE1`
310 FOREIGN KEY (`idDIM_DIAGNOSIS_CODE`)
311 REFERENCES `mydb`.`DIM_DIAGNOSIS_CODE` (`idDIM_DIAGNOSIS_CODE`)
312 ON DELETE NO ACTION
313 ON UPDATE NO ACTION,
314 CONSTRAINT `fk_FAC_MEDICA_ATTENTION_DIM_PATIENT1`
315 FOREIGN KEY (`idDIM_PATIENT`)
316 REFERENCES `mydb`.`DIM_PATIENT` (`idDIM_PATIENT`)
317 ON DELETE NO ACTION
318 ON UPDATE NO ACTION,
319 CONSTRAINT `fk_FAC_MEDICA_ATTENTION_DIM_TIME1`
320 FOREIGN KEY (`idDIM_TIME`)
321 REFERENCES `mydb`.`DIM_TIME` (`idDIM_TIME`)
322 ON DELETE NO ACTION
323 ON UPDATE NO ACTION)
324ENGINE = InnoDB;
325
326
327SET SQL_MODE=@OLD_SQL_MODE;
328SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
329SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;