· 6 years ago · Feb 01, 2020, 12:36 PM
1-- MySQL Workbench Forward Engineering
2
3SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
4SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
5SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
6
7-- -----------------------------------------------------
8-- Schema Boeteafhandeling
9-- -----------------------------------------------------
10
11-- -----------------------------------------------------
12-- Schema Boeteafhandeling
13-- -----------------------------------------------------
14CREATE SCHEMA IF NOT EXISTS `Boeteafhandeling` DEFAULT CHARACTER SET utf8 ;
15USE `Boeteafhandeling` ;
16
17-- -----------------------------------------------------
18-- Table `Boeteafhandeling`.`Dim_eigenaar`
19-- -----------------------------------------------------
20CREATE TABLE IF NOT EXISTS `Boeteafhandeling`.`Dim_eigenaar` (
21 `eigenaar_id` INT NOT NULL,
22 `eigenaar_lastupdate` DATE NOT NULL,
23 `geslacht` VARCHAR(45) NOT NULL,
24 `voornaam` VARCHAR(45) NOT NULL,
25 `achternaam` VARCHAR(45) NOT NULL,
26 `geboortedatum` DATE NOT NULL,
27 `straatadres` VARCHAR(45) NOT NULL,
28 `stad` VARCHAR(45) NOT NULL,
29 `provincie` VARCHAR(45) NOT NULL,
30 `postcode` VARCHAR(45) NOT NULL,
31 `land` VARCHAR(45) NOT NULL,
32 `valid_from` DATE NOT NULL,
33 `valid_through` DATE NOT NULL,
34 PRIMARY KEY (`eigenaar_id`))
35ENGINE = InnoDB;
36
37
38-- -----------------------------------------------------
39-- Table `Boeteafhandeling`.`Dim_voertuig`
40-- -----------------------------------------------------
41CREATE TABLE IF NOT EXISTS `Boeteafhandeling`.`Dim_voertuig` (
42 `voertuig_id` INT NOT NULL,
43 `kenteken` VARCHAR(45) NOT NULL,
44 `voertuig_lastupdate` DATE NULL,
45 `merk` VARCHAR(45) NOT NULL,
46 `voertuigtype` VARCHAR(45) NOT NULL,
47 PRIMARY KEY (`voertuig_id`))
48ENGINE = InnoDB;
49
50
51-- -----------------------------------------------------
52-- Table `Boeteafhandeling`.`Dim_case`
53-- -----------------------------------------------------
54CREATE TABLE IF NOT EXISTS `Boeteafhandeling`.`Dim_case` (
55 `case_id` INT NOT NULL,
56 `case_lastupdate` DATE NULL,
57 `event` VARCHAR(45) NOT NULL,
58 `case` VARCHAR(45) NOT NULL,
59 `voertuig_id` VARCHAR(45) NULL,
60 `boete_type` VARCHAR(45) NOT NULL,
61 `starttijd` DATE NOT NULL,
62 `eindtijd` DATE NOT NULL,
63 `valid_from` DATE NOT NULL,
64 `valid_through` DATE NOT NULL,
65 PRIMARY KEY (`case_id`))
66ENGINE = InnoDB;
67
68
69-- -----------------------------------------------------
70-- Table `Boeteafhandeling`.`Dim_case_eigenaar_kenteken_bridge`
71-- -----------------------------------------------------
72CREATE TABLE IF NOT EXISTS `Boeteafhandeling`.`Dim_case_eigenaar_kenteken_bridge` (
73 `eigenaar_id` INT NOT NULL,
74 `case` VARCHAR(45) NOT NULL,
75 `kenteken` VARCHAR(45) NOT NULL,
76 INDEX `eigenaar_id_idx` (`eigenaar_id` ASC) VISIBLE,
77 INDEX `kenteken_idx` (`kenteken` ASC) VISIBLE,
78 INDEX `case_idx` (`case` ASC) VISIBLE,
79 PRIMARY KEY (`case`),
80 CONSTRAINT `eigenaar_id`
81 FOREIGN KEY (`eigenaar_id`)
82 REFERENCES `Boeteafhandeling`.`Dim_eigenaar` (`eigenaar_id`)
83 ON DELETE NO ACTION
84 ON UPDATE NO ACTION,
85 CONSTRAINT `kenteken`
86 FOREIGN KEY (`kenteken`)
87 REFERENCES `Boeteafhandeling`.`Dim_voertuig` (`kenteken`)
88 ON DELETE NO ACTION
89 ON UPDATE NO ACTION,
90 CONSTRAINT `case`
91 FOREIGN KEY (`case`)
92 REFERENCES `Boeteafhandeling`.`Dim_case` (`case`)
93 ON DELETE NO ACTION
94 ON UPDATE NO ACTION)
95ENGINE = InnoDB;
96
97
98-- -----------------------------------------------------
99-- Table `Boeteafhandeling`.`Fact_voertuigen`
100-- -----------------------------------------------------
101CREATE TABLE IF NOT EXISTS `Boeteafhandeling`.`Fact_voertuigen` (
102 `eigenaar_id` INT NOT NULL,
103 `aantal_voertuigen` INT NOT NULL,
104 INDEX `eigenaar_id_idx` (`eigenaar_id` ASC) VISIBLE,
105 CONSTRAINT `eigenaar_id`
106 FOREIGN KEY (`eigenaar_id`)
107 REFERENCES `Boeteafhandeling`.`Dim_case_eigenaar_kenteken_bridge` (`eigenaar_id`)
108 ON DELETE NO ACTION
109 ON UPDATE NO ACTION)
110ENGINE = InnoDB;
111
112
113-- -----------------------------------------------------
114-- Table `Boeteafhandeling`.`Fact_kenteken`
115-- -----------------------------------------------------
116CREATE TABLE IF NOT EXISTS `Boeteafhandeling`.`Fact_kenteken` (
117 `eigenaar_id` INT NOT NULL,
118 `aantal_kentekens` INT NOT NULL,
119 INDEX `eigenaar_id_idx` (`eigenaar_id` ASC) VISIBLE,
120 CONSTRAINT `eigenaar_id`
121 FOREIGN KEY (`eigenaar_id`)
122 REFERENCES `Boeteafhandeling`.`Dim_case_eigenaar_kenteken_bridge` (`eigenaar_id`)
123 ON DELETE NO ACTION
124 ON UPDATE NO ACTION)
125ENGINE = InnoDB;
126
127
128-- -----------------------------------------------------
129-- Table `Boeteafhandeling`.`Dim_date`
130-- -----------------------------------------------------
131CREATE TABLE IF NOT EXISTS `Boeteafhandeling`.`Dim_date` (
132 `date_id` INT NOT NULL,
133 `maand` DATE NOT NULL,
134 `jaar` DATE NOT NULL,
135 PRIMARY KEY (`date_id`))
136ENGINE = InnoDB;
137
138
139-- -----------------------------------------------------
140-- Table `Boeteafhandeling`.`Fact_overtreders`
141-- -----------------------------------------------------
142CREATE TABLE IF NOT EXISTS `Boeteafhandeling`.`Fact_overtreders` (
143 `eigenaar_id` INT NOT NULL,
144 `date_id` INT NOT NULL,
145 `aantal_overtreders` INT NOT NULL,
146 INDEX `eigenaar_id_idx` (`eigenaar_id` ASC) VISIBLE,
147 INDEX `date_id_idx` (`date_id` ASC) VISIBLE,
148 CONSTRAINT `eigenaar_id`
149 FOREIGN KEY (`eigenaar_id`)
150 REFERENCES `Boeteafhandeling`.`Dim_case_eigenaar_kenteken_bridge` (`eigenaar_id`)
151 ON DELETE NO ACTION
152 ON UPDATE NO ACTION,
153 CONSTRAINT `date_id`
154 FOREIGN KEY (`date_id`)
155 REFERENCES `Boeteafhandeling`.`Dim_date` (`date_id`)
156 ON DELETE NO ACTION
157 ON UPDATE NO ACTION)
158ENGINE = InnoDB;
159
160
161-- -----------------------------------------------------
162-- Table `Boeteafhandeling`.`Fact_boetes`
163-- -----------------------------------------------------
164CREATE TABLE IF NOT EXISTS `Boeteafhandeling`.`Fact_boetes` (
165 `eigenaar_id` INT NOT NULL,
166 `date_id` INT NOT NULL,
167 `aantal_boetes` INT NOT NULL,
168 `boetebedrag` FLOAT NOT NULL,
169 INDEX `eigenaar_id_idx` (`eigenaar_id` ASC) VISIBLE,
170 INDEX `date_id_idx` (`date_id` ASC) VISIBLE,
171 CONSTRAINT `eigenaar_id`
172 FOREIGN KEY (`eigenaar_id`)
173 REFERENCES `Boeteafhandeling`.`Dim_case_eigenaar_kenteken_bridge` (`eigenaar_id`)
174 ON DELETE NO ACTION
175 ON UPDATE NO ACTION,
176 CONSTRAINT `date_id`
177 FOREIGN KEY (`date_id`)
178 REFERENCES `Boeteafhandeling`.`Dim_date` (`date_id`)
179 ON DELETE NO ACTION
180 ON UPDATE NO ACTION)
181ENGINE = InnoDB;
182
183
184-- -----------------------------------------------------
185-- Table `Boeteafhandeling`.`Fact_voertuigen`
186-- -----------------------------------------------------
187CREATE TABLE IF NOT EXISTS `Boeteafhandeling`.`Fact_voertuigen` (
188 `eigenaar_id` INT NOT NULL,
189 `aantal_voertuigen` INT NOT NULL,
190 INDEX `eigenaar_id_idx` (`eigenaar_id` ASC) VISIBLE,
191 CONSTRAINT `eigenaar_id`
192 FOREIGN KEY (`eigenaar_id`)
193 REFERENCES `Boeteafhandeling`.`Dim_case_eigenaar_kenteken_bridge` (`eigenaar_id`)
194 ON DELETE NO ACTION
195 ON UPDATE NO ACTION)
196ENGINE = InnoDB;
197
198
199SET SQL_MODE=@OLD_SQL_MODE;
200SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
201SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;