· 5 years ago · Mar 27, 2020, 01:28 PM
1-- MySQL Script generated by MySQL Workbench
2-- Fri Mar 27 15:24:33 2020
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='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
9
10-- -----------------------------------------------------
11-- Schema villagekanta
12-- -----------------------------------------------------
13DROP SCHEMA IF EXISTS `villagekanta` ;
14
15-- -----------------------------------------------------
16-- Schema villagekanta
17-- -----------------------------------------------------
18CREATE SCHEMA IF NOT EXISTS `villagekanta` DEFAULT CHARACTER SET utf8 ;
19USE `villagekanta` ;
20
21-- -----------------------------------------------------
22-- Table `villagekanta`.`omistaja`
23-- -----------------------------------------------------
24DROP TABLE IF EXISTS `villagekanta`.`omistaja` ;
25
26CREATE TABLE IF NOT EXISTS `villagekanta`.`omistaja` (
27 `idomistaja` INT NOT NULL AUTO_INCREMENT,
28 `käyttäjänimi` VARCHAR(25) NOT NULL,
29 `password` VARCHAR(32) NOT NULL,
30 PRIMARY KEY (`idomistaja`));
31
32
33-- -----------------------------------------------------
34-- Table `villagekanta`.`asiakas`
35-- -----------------------------------------------------
36DROP TABLE IF EXISTS `villagekanta`.`asiakas` ;
37
38CREATE TABLE IF NOT EXISTS `villagekanta`.`asiakas` (
39 `idasiakas` INT NOT NULL AUTO_INCREMENT,
40 `etunimi` VARCHAR(45) NOT NULL,
41 `sukunimi` VARCHAR(45) NOT NULL,
42 `osoite` VARCHAR(45) NOT NULL,
43 `postitmp` VARCHAR(45) NOT NULL,
44 `postinro` VARCHAR(45) NOT NULL,
45 `email` VARCHAR(45) NOT NULL,
46 `puhnro` VARCHAR(45) NOT NULL,
47 `käyttäjänimi` VARCHAR(25) NOT NULL,
48 `salasana` VARCHAR(55) NOT NULL,
49 PRIMARY KEY (`idasiakas`))
50ENGINE = InnoDB;
51
52
53-- -----------------------------------------------------
54-- Table `villagekanta`.`alue`
55-- -----------------------------------------------------
56DROP TABLE IF EXISTS `villagekanta`.`alue` ;
57
58CREATE TABLE IF NOT EXISTS `villagekanta`.`alue` (
59 `idalue` INT NOT NULL AUTO_INCREMENT,
60 `nimi` VARCHAR(45) NOT NULL,
61 PRIMARY KEY (`idalue`))
62ENGINE = InnoDB;
63
64
65-- -----------------------------------------------------
66-- Table `villagekanta`.`mökki`
67-- -----------------------------------------------------
68DROP TABLE IF EXISTS `villagekanta`.`mökki` ;
69
70CREATE TABLE IF NOT EXISTS `villagekanta`.`mökki` (
71 `idmökki` INT NOT NULL AUTO_INCREMENT,
72 `nimi` VARCHAR(45) NOT NULL,
73 `osoite` VARCHAR(45) NOT NULL,
74 `postinro` VARCHAR(45) NOT NULL,
75 `postitmp` VARCHAR(45) NULL,
76 `idomistaja` INT NOT NULL,
77 `idalue` INT NOT NULL,
78 PRIMARY KEY (`idmökki`),
79 INDEX `fk_mökki_omistaja_idx` (`idomistaja` ASC) VISIBLE,
80 INDEX `fk_mökki_alue1_idx` (`idalue` ASC) VISIBLE,
81 CONSTRAINT `fk_mökki_omistaja`
82 FOREIGN KEY (`idomistaja`)
83 REFERENCES `villagekanta`.`omistaja` (`idomistaja`)
84 ON DELETE NO ACTION
85 ON UPDATE NO ACTION,
86 CONSTRAINT `fk_mökki_alue1`
87 FOREIGN KEY (`idalue`)
88 REFERENCES `villagekanta`.`alue` (`idalue`)
89 ON DELETE NO ACTION
90 ON UPDATE NO ACTION)
91ENGINE = InnoDB;
92
93
94-- -----------------------------------------------------
95-- Table `villagekanta`.`varaus`
96-- -----------------------------------------------------
97DROP TABLE IF EXISTS `villagekanta`.`varaus` ;
98
99CREATE TABLE IF NOT EXISTS `villagekanta`.`varaus` (
100 `idvaraus` INT NOT NULL AUTO_INCREMENT,
101 `alkupvm` DATETIME NOT NULL,
102 `loppupvm` DATETIME NOT NULL,
103 `idmökki` INT NOT NULL,
104 `idasiakas` INT NOT NULL,
105 PRIMARY KEY (`idvaraus`),
106 INDEX `fk_varaus_mökki1_idx` (`idmökki` ASC) VISIBLE,
107 INDEX `fk_varaus_asiakas1_idx` (`idasiakas` ASC) VISIBLE,
108 CONSTRAINT `fk_varaus_mökki1`
109 FOREIGN KEY (`idmökki`)
110 REFERENCES `villagekanta`.`mökki` (`idmökki`)
111 ON DELETE NO ACTION
112 ON UPDATE NO ACTION,
113 CONSTRAINT `fk_varaus_asiakas1`
114 FOREIGN KEY (`idasiakas`)
115 REFERENCES `villagekanta`.`asiakas` (`idasiakas`)
116 ON DELETE NO ACTION
117 ON UPDATE NO ACTION)
118ENGINE = InnoDB;
119
120
121-- -----------------------------------------------------
122-- Table `villagekanta`.`palvelu`
123-- -----------------------------------------------------
124DROP TABLE IF EXISTS `villagekanta`.`palvelu` ;
125
126CREATE TABLE IF NOT EXISTS `villagekanta`.`palvelu` (
127 `idpalvelu` INT NOT NULL AUTO_INCREMENT,
128 `nimi` VARCHAR(45) NOT NULL,
129 `hinta` DOUBLE NOT NULL,
130 `idvaraus` INT NOT NULL,
131 `idmökki` INT NOT NULL,
132 PRIMARY KEY (`idpalvelu`),
133 INDEX `fk_palvelu_varaus1_idx` (`idvaraus` ASC) VISIBLE,
134 INDEX `fk_palvelu_mökki1_idx` (`idmökki` ASC) VISIBLE,
135 CONSTRAINT `fk_palvelu_varaus1`
136 FOREIGN KEY (`idvaraus`)
137 REFERENCES `villagekanta`.`varaus` (`idvaraus`)
138 ON DELETE NO ACTION
139 ON UPDATE NO ACTION,
140 CONSTRAINT `fk_palvelu_mökki1`
141 FOREIGN KEY (`idmökki`)
142 REFERENCES `villagekanta`.`mökki` (`idmökki`)
143 ON DELETE NO ACTION
144 ON UPDATE NO ACTION)
145ENGINE = InnoDB;
146
147
148-- -----------------------------------------------------
149-- Table `villagekanta`.`lasku`
150-- -----------------------------------------------------
151DROP TABLE IF EXISTS `villagekanta`.`lasku` ;
152
153CREATE TABLE IF NOT EXISTS `villagekanta`.`lasku` (
154 `idlasku` INT NOT NULL AUTO_INCREMENT,
155 `idvaraus` INT NOT NULL,
156 `idasiakas` INT NOT NULL,
157 `loppusumma` DOUBLE NOT NULL,
158 PRIMARY KEY (`idlasku`),
159 INDEX `fk_lasku_varaus1_idx` (`idvaraus` ASC) VISIBLE,
160 INDEX `fk_lasku_asiakas1_idx` (`idasiakas` ASC) VISIBLE,
161 CONSTRAINT `fk_lasku_varaus1`
162 FOREIGN KEY (`idvaraus`)
163 REFERENCES `villagekanta`.`varaus` (`idvaraus`)
164 ON DELETE NO ACTION
165 ON UPDATE NO ACTION,
166 CONSTRAINT `fk_lasku_asiakas1`
167 FOREIGN KEY (`idasiakas`)
168 REFERENCES `villagekanta`.`asiakas` (`idasiakas`)
169 ON DELETE NO ACTION
170 ON UPDATE NO ACTION)
171ENGINE = InnoDB;
172
173
174SET SQL_MODE=@OLD_SQL_MODE;
175SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
176SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
177
178-- -----------------------------------------------------
179-- Data for table `villagekanta`.`omistaja`
180-- -----------------------------------------------------
181START TRANSACTION;
182USE `villagekanta`;
183INSERT INTO `villagekanta`.`omistaja` (`idomistaja`, `käyttäjänimi`, `password`) VALUES (1, 'RukaOwner', 'ruka123');
184INSERT INTO `villagekanta`.`omistaja` (`idomistaja`, `käyttäjänimi`, `password`) VALUES (2, 'KoliOwner', 'koli123');
185INSERT INTO `villagekanta`.`omistaja` (`idomistaja`, `käyttäjänimi`, `password`) VALUES (3, 'PallasOwner', 'pallas123');
186
187COMMIT;
188
189
190-- -----------------------------------------------------
191-- Data for table `villagekanta`.`asiakas`
192-- -----------------------------------------------------
193START TRANSACTION;
194USE `villagekanta`;
195INSERT INTO `villagekanta`.`asiakas` (`idasiakas`, `etunimi`, `sukunimi`, `osoite`, `postitmp`, `postinro`, `email`, `puhnro`, `käyttäjänimi`, `salasana`) VALUES (1, 'Maija', 'Mehiläinen', 'Katu 13', 'Kuopio', '70600', 'maija@hotmail.com', '050123123', 'maijameh', 'maija123');
196
197COMMIT;
198
199
200-- -----------------------------------------------------
201-- Data for table `villagekanta`.`alue`
202-- -----------------------------------------------------
203START TRANSACTION;
204USE `villagekanta`;
205INSERT INTO `villagekanta`.`alue` (`idalue`, `nimi`) VALUES (1, 'Ruka');
206INSERT INTO `villagekanta`.`alue` (`idalue`, `nimi`) VALUES (2, 'Koli');
207INSERT INTO `villagekanta`.`alue` (`idalue`, `nimi`) VALUES (3, 'Pallas');
208
209COMMIT;
210
211
212-- -----------------------------------------------------
213-- Data for table `villagekanta`.`mökki`
214-- -----------------------------------------------------
215START TRANSACTION;
216USE `villagekanta`;
217INSERT INTO `villagekanta`.`mökki` (`idmökki`, `nimi`, `osoite`, `postinro`, `postitmp`, `idomistaja`, `idalue`) VALUES (1, 'Koli Palace', 'Kolikuja 12', '81700', 'Koli', 2, 2);
218
219COMMIT;
220
221
222-- -----------------------------------------------------
223-- Data for table `villagekanta`.`varaus`
224-- -----------------------------------------------------
225START TRANSACTION;
226USE `villagekanta`;
227INSERT INTO `villagekanta`.`varaus` (`idvaraus`, `alkupvm`, `loppupvm`, `idmökki`, `idasiakas`) VALUES (1, '2020-04-01', '2020-04-05', 1, 1);
228
229COMMIT;
230
231
232-- -----------------------------------------------------
233-- Data for table `villagekanta`.`palvelu`
234-- -----------------------------------------------------
235START TRANSACTION;
236USE `villagekanta`;
237INSERT INTO `villagekanta`.`palvelu` (`idpalvelu`, `nimi`, `hinta`, `idvaraus`, `idmökki`) VALUES (1, 'Poroajelu', 100, 1, 1);
238
239COMMIT;
240
241
242-- -----------------------------------------------------
243-- Data for table `villagekanta`.`lasku`
244-- -----------------------------------------------------
245START TRANSACTION;
246USE `villagekanta`;
247INSERT INTO `villagekanta`.`lasku` (`idlasku`, `idvaraus`, `idasiakas`, `loppusumma`) VALUES (1, 1, 1, 350);
248
249COMMIT;