· 9 years ago · Dec 01, 2016, 12:56 PM
1-- MySQL Script generated by MySQL Workbench
2-- 12/01/16 13:53:46
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-- -----------------------------------------------------
13
14-- -----------------------------------------------------
15-- Schema mydb
16-- -----------------------------------------------------
17CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 ;
18USE `mydb` ;
19
20-- -----------------------------------------------------
21-- Table `mydb`.`membership`
22-- -----------------------------------------------------
23CREATE TABLE IF NOT EXISTS `mydb`.`membership` (
24 `idmembership` INT NOT NULL AUTO_INCREMENT,
25 `benaming` VARCHAR(45) NULL,
26 `kortingsPercentage` INT NULL,
27 PRIMARY KEY (`idmembership`))
28ENGINE = InnoDB;
29
30
31-- -----------------------------------------------------
32-- Table `mydb`.`creditcard`
33-- -----------------------------------------------------
34CREATE TABLE IF NOT EXISTS `mydb`.`creditcard` (
35 `idcreditcard` INT NOT NULL AUTO_INCREMENT,
36 `nummer` VARCHAR(45) NULL,
37 `geldigheidsdatum` DATE NULL,
38 PRIMARY KEY (`idcreditcard`))
39ENGINE = InnoDB;
40
41
42-- -----------------------------------------------------
43-- Table `mydb`.`account`
44-- -----------------------------------------------------
45CREATE TABLE IF NOT EXISTS `mydb`.`account` (
46 `idaccount` INT NOT NULL AUTO_INCREMENT,
47 `emailadres` VARCHAR(45) NULL,
48 `wachtwoord` VARCHAR(45) NULL,
49 `isBevestigd` TINYINT NULL,
50 `membership_idmembership` INT NULL,
51 `creditcard_idcreditcard` INT NULL,
52 PRIMARY KEY (`idaccount`),
53 INDEX `fk_account_membership1_idx` (`membership_idmembership` ASC),
54 INDEX `fk_account_creditcard1_idx` (`creditcard_idcreditcard` ASC),
55 CONSTRAINT `fk_account_membership1`
56 FOREIGN KEY (`membership_idmembership`)
57 REFERENCES `mydb`.`membership` (`idmembership`)
58 ON DELETE NO ACTION
59 ON UPDATE NO ACTION,
60 CONSTRAINT `fk_account_creditcard1`
61 FOREIGN KEY (`creditcard_idcreditcard`)
62 REFERENCES `mydb`.`creditcard` (`idcreditcard`)
63 ON DELETE NO ACTION
64 ON UPDATE NO ACTION)
65ENGINE = InnoDB;
66
67
68-- -----------------------------------------------------
69-- Table `mydb`.`klant`
70-- -----------------------------------------------------
71CREATE TABLE IF NOT EXISTS `mydb`.`klant` (
72 `idklant` INT NOT NULL AUTO_INCREMENT,
73 `naam` VARCHAR(45) NULL,
74 `postcode` VARCHAR(45) NULL,
75 `huisnummer` VARCHAR(45) NULL,
76 `mobiel` VARCHAR(45) NULL,
77 `account_idaccount` INT NULL,
78 PRIMARY KEY (`idklant`),
79 INDEX `fk_klant_account1_idx` (`account_idaccount` ASC),
80 CONSTRAINT `fk_klant_account1`
81 FOREIGN KEY (`account_idaccount`)
82 REFERENCES `mydb`.`account` (`idaccount`)
83 ON DELETE NO ACTION
84 ON UPDATE NO ACTION)
85ENGINE = InnoDB;
86
87
88-- -----------------------------------------------------
89-- Table `mydb`.`betaalwijze`
90-- -----------------------------------------------------
91CREATE TABLE IF NOT EXISTS `mydb`.`betaalwijze` (
92 `idbetaalwijze` INT NOT NULL AUTO_INCREMENT,
93 `naam` VARCHAR(45) NULL,
94 PRIMARY KEY (`idbetaalwijze`))
95ENGINE = InnoDB;
96
97
98-- -----------------------------------------------------
99-- Table `mydb`.`bestelling`
100-- -----------------------------------------------------
101CREATE TABLE IF NOT EXISTS `mydb`.`bestelling` (
102 `idbestelling` INT NOT NULL AUTO_INCREMENT,
103 `datum` DATE NULL,
104 `account_idaccount` INT NOT NULL,
105 `betaalwijze_idbetaalwijze` INT NOT NULL,
106 PRIMARY KEY (`idbestelling`),
107 INDEX `fk_bestelling_account1_idx` (`account_idaccount` ASC),
108 INDEX `fk_bestelling_betaalwijze1_idx` (`betaalwijze_idbetaalwijze` ASC),
109 CONSTRAINT `fk_bestelling_account1`
110 FOREIGN KEY (`account_idaccount`)
111 REFERENCES `mydb`.`account` (`idaccount`)
112 ON DELETE NO ACTION
113 ON UPDATE NO ACTION,
114 CONSTRAINT `fk_bestelling_betaalwijze1`
115 FOREIGN KEY (`betaalwijze_idbetaalwijze`)
116 REFERENCES `mydb`.`betaalwijze` (`idbetaalwijze`)
117 ON DELETE NO ACTION
118 ON UPDATE NO ACTION)
119ENGINE = InnoDB;
120
121
122-- -----------------------------------------------------
123-- Table `mydb`.`categorie`
124-- -----------------------------------------------------
125CREATE TABLE IF NOT EXISTS `mydb`.`categorie` (
126 `idcategorie` INT NOT NULL AUTO_INCREMENT,
127 `naam` VARCHAR(45) NULL,
128 PRIMARY KEY (`idcategorie`))
129ENGINE = InnoDB;
130
131
132-- -----------------------------------------------------
133-- Table `mydb`.`product`
134-- -----------------------------------------------------
135CREATE TABLE IF NOT EXISTS `mydb`.`product` (
136 `idproduct` INT NOT NULL AUTO_INCREMENT,
137 `eenheid` INT NULL,
138 `naam` VARCHAR(45) NULL,
139 `categorie_idcategorie` INT NOT NULL,
140 PRIMARY KEY (`idproduct`),
141 INDEX `fk_product_categorie1_idx` (`categorie_idcategorie` ASC),
142 CONSTRAINT `fk_product_categorie1`
143 FOREIGN KEY (`categorie_idcategorie`)
144 REFERENCES `mydb`.`categorie` (`idcategorie`)
145 ON DELETE NO ACTION
146 ON UPDATE NO ACTION)
147ENGINE = InnoDB;
148
149
150-- -----------------------------------------------------
151-- Table `mydb`.`bestelregel`
152-- -----------------------------------------------------
153CREATE TABLE IF NOT EXISTS `mydb`.`bestelregel` (
154 `idbestelregel` INT NOT NULL AUTO_INCREMENT,
155 `aantalPerEenheid` INT NULL,
156 `bestelling_idbestelling` INT NOT NULL,
157 `product_idproduct` INT NOT NULL,
158 PRIMARY KEY (`idbestelregel`),
159 INDEX `fk_bestelregel_bestelling1_idx` (`bestelling_idbestelling` ASC),
160 INDEX `fk_bestelregel_product1_idx` (`product_idproduct` ASC),
161 CONSTRAINT `fk_bestelregel_bestelling1`
162 FOREIGN KEY (`bestelling_idbestelling`)
163 REFERENCES `mydb`.`bestelling` (`idbestelling`)
164 ON DELETE NO ACTION
165 ON UPDATE NO ACTION,
166 CONSTRAINT `fk_bestelregel_product1`
167 FOREIGN KEY (`product_idproduct`)
168 REFERENCES `mydb`.`product` (`idproduct`)
169 ON DELETE NO ACTION
170 ON UPDATE NO ACTION)
171ENGINE = InnoDB;
172
173
174-- -----------------------------------------------------
175-- Table `mydb`.`BTW`
176-- -----------------------------------------------------
177CREATE TABLE IF NOT EXISTS `mydb`.`BTW` (
178 `idBTW` INT NOT NULL AUTO_INCREMENT,
179 `percentage` INT NULL,
180 PRIMARY KEY (`idBTW`))
181ENGINE = InnoDB;
182
183
184-- -----------------------------------------------------
185-- Table `mydb`.`prijshistorie`
186-- -----------------------------------------------------
187CREATE TABLE IF NOT EXISTS `mydb`.`prijshistorie` (
188 `idprijshistorie` INT NOT NULL AUTO_INCREMENT,
189 `datumVan` DATE NULL,
190 `datumTot` DATE NULL,
191 `prijsexBTW` DECIMAL(6,2) NULL,
192 `product_idproduct` INT NOT NULL,
193 `BTW_idBTW` INT NOT NULL,
194 PRIMARY KEY (`idprijshistorie`),
195 INDEX `fk_prijshistorie_product1_idx` (`product_idproduct` ASC),
196 INDEX `fk_prijshistorie_BTW1_idx` (`BTW_idBTW` ASC),
197 CONSTRAINT `fk_prijshistorie_product1`
198 FOREIGN KEY (`product_idproduct`)
199 REFERENCES `mydb`.`product` (`idproduct`)
200 ON DELETE NO ACTION
201 ON UPDATE NO ACTION,
202 CONSTRAINT `fk_prijshistorie_BTW1`
203 FOREIGN KEY (`BTW_idBTW`)
204 REFERENCES `mydb`.`BTW` (`idBTW`)
205 ON DELETE NO ACTION
206 ON UPDATE NO ACTION)
207ENGINE = InnoDB;
208
209
210-- -----------------------------------------------------
211-- Table `mydb`.`automerk`
212-- -----------------------------------------------------
213CREATE TABLE IF NOT EXISTS `mydb`.`automerk` (
214 `idautomerk` INT NOT NULL AUTO_INCREMENT,
215 `naam` VARCHAR(45) NULL,
216 PRIMARY KEY (`idautomerk`))
217ENGINE = InnoDB;
218
219
220-- -----------------------------------------------------
221-- Table `mydb`.`automodel`
222-- -----------------------------------------------------
223CREATE TABLE IF NOT EXISTS `mydb`.`automodel` (
224 `idautomodel` INT NOT NULL AUTO_INCREMENT,
225 `automerk_idautomerk` INT NOT NULL,
226 `naam` VARCHAR(45) NULL,
227 PRIMARY KEY (`idautomodel`),
228 INDEX `fk_automodel_automerk1_idx` (`automerk_idautomerk` ASC),
229 CONSTRAINT `fk_automodel_automerk1`
230 FOREIGN KEY (`automerk_idautomerk`)
231 REFERENCES `mydb`.`automerk` (`idautomerk`)
232 ON DELETE NO ACTION
233 ON UPDATE NO ACTION)
234ENGINE = InnoDB;
235
236
237-- -----------------------------------------------------
238-- Table `mydb`.`ProductModel`
239-- -----------------------------------------------------
240CREATE TABLE IF NOT EXISTS `mydb`.`ProductModel` (
241 `idProductModel` INT NOT NULL AUTO_INCREMENT,
242 `automodel_idautomodel` INT NOT NULL,
243 `product_idproduct` INT NOT NULL,
244 PRIMARY KEY (`idProductModel`),
245 INDEX `fk_ProductModel_automodel1_idx` (`automodel_idautomodel` ASC),
246 INDEX `fk_ProductModel_product1_idx` (`product_idproduct` ASC),
247 CONSTRAINT `fk_ProductModel_automodel1`
248 FOREIGN KEY (`automodel_idautomodel`)
249 REFERENCES `mydb`.`automodel` (`idautomodel`)
250 ON DELETE NO ACTION
251 ON UPDATE NO ACTION,
252 CONSTRAINT `fk_ProductModel_product1`
253 FOREIGN KEY (`product_idproduct`)
254 REFERENCES `mydb`.`product` (`idproduct`)
255 ON DELETE NO ACTION
256 ON UPDATE NO ACTION)
257ENGINE = InnoDB;
258
259
260SET SQL_MODE=@OLD_SQL_MODE;
261SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
262SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;