· 7 years ago · Dec 16, 2018, 09:56 PM
1SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
2SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
3SET @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';
4
5-- -----------------------------------------------------
6-- Schema bioly_bazy
7-- -----------------------------------------------------
8
9-- -----------------------------------------------------
10-- Schema bioly_bazy
11-- -----------------------------------------------------
12CREATE SCHEMA IF NOT EXISTS DEFAULT CHARACTER SET utf8 ;
13USE ;
14
15-- -----------------------------------------------------
16-- Table `Adresy`
17-- -----------------------------------------------------
18CREATE TABLE IF NOT EXISTS `Adresy` (
19 `ID_ADRES` INT NOT NULL AUTO_INCREMENT,
20 `ULICA` VARCHAR(64) NOT NULL,
21 `MIEJSCOWOSC` VARCHAR(32) NOT NULL,
22 `KOD_POCZTOWY` VARCHAR(6) NOT NULL,
23 `NR_BLOKU` VARCHAR(5) NULL,
24 `NR_MIESZKANIA` VARCHAR(5) NULL,
25 PRIMARY KEY (`ID_ADRES`),
26 UNIQUE INDEX `ID_ADRES_UNIQUE` (`ID_ADRES` ASC) VISIBLE,
27 UNIQUE INDEX `ULICA_UNIQUE` (`ULICA` ASC) VISIBLE,
28 INDEX `ULICA` (`ULICA` ASC) VISIBLE,
29 INDEX `MIEJSCOWOSC` (`MIEJSCOWOSC` ASC) VISIBLE)
30ENGINE = InnoDB;
31
32
33-- -----------------------------------------------------
34-- Table `Osoby`
35-- -----------------------------------------------------
36CREATE TABLE IF NOT EXISTS `Osoby` (
37 `ID_OSOBA` INT NOT NULL AUTO_INCREMENT,
38 `IMIE` VARCHAR(20) NOT NULL,
39 `NAZWISKO` VARCHAR(25) NOT NULL,
40 `PESEL` CHAR(11) NOT NULL,
41 `NUMBER_DOWODU` VARCHAR(10) NOT NULL,
42 `TELEFON` VARCHAR(20) NULL,
43 `EMAIL` VARCHAR(64) NULL,
44 `ID_ADRES` INT NOT NULL,
45 PRIMARY KEY (`ID_OSOBA`, `ID_ADRES`),
46 UNIQUE INDEX `ID_OSOBA_UNIQUE` (`ID_OSOBA` ASC) VISIBLE,
47 UNIQUE INDEX `TELEFON_UNIQUE` (`TELEFON` ASC) VISIBLE,
48 UNIQUE INDEX `EMAIL_UNIQUE` (`EMAIL` ASC) VISIBLE,
49 INDEX `FK_ADRES` (`ID_ADRES` ASC) VISIBLE,
50 INDEX `IMIE` (`IMIE` ASC) VISIBLE,
51 INDEX `NAZWISKO` (`NAZWISKO` ASC) VISIBLE,
52 CONSTRAINT `fk_Osoby_Adresy1`
53 FOREIGN KEY (`ID_ADRES`)
54 REFERENCES `Adresy` (`ID_ADRES`)
55 ON DELETE NO ACTION
56 ON UPDATE NO ACTION)
57ENGINE = InnoDB;
58
59
60-- -----------------------------------------------------
61-- Table `Pracownicy`
62-- -----------------------------------------------------
63CREATE TABLE IF NOT EXISTS `Pracownicy` (
64 `ID_PRACOWNIK` INT NOT NULL AUTO_INCREMENT,
65 `ID_OSOBA` INT NOT NULL,
66 `DATA_ZATRUDNIENIA` DATE NOT NULL,
67 `PENSJA` INT NOT NULL,
68 `HASLO` VARCHAR(64) NOT NULL,
69 `RODZAJ_UMOWY` VARCHAR(32) NOT NULL,
70 `STANOWISKO` VARCHAR(45) NOT NULL,
71 UNIQUE INDEX `ID_PRACOWNIK_UNIQUE` (`ID_PRACOWNIK` ASC) VISIBLE,
72 PRIMARY KEY (`ID_PRACOWNIK`, `ID_OSOBA`),
73 UNIQUE INDEX `ID_OSOBA_UNIQUE` (`ID_OSOBA` ASC) VISIBLE,
74 INDEX `DATA_ZATRUDNIENIA` () VISIBLE,
75 CONSTRAINT `fk_Pracownicy_Osoby1`
76 FOREIGN KEY (`ID_OSOBA`)
77 REFERENCES `Osoby` (`ID_OSOBA`)
78 ON DELETE NO ACTION
79 ON UPDATE NO ACTION)
80ENGINE = InnoDB;
81
82
83-- -----------------------------------------------------
84-- Table `Marki`
85-- -----------------------------------------------------
86CREATE TABLE IF NOT EXISTS `Marki` (
87 `ID_MARKA` INT NOT NULL,
88 `INFO` VARCHAR(45) NULL,
89 `NAZWA` VARCHAR(45) NOT NULL,
90 PRIMARY KEY (`ID_MARKA`),
91 UNIQUE INDEX `ID_MARKA_UNIQUE` (`ID_MARKA` ASC) VISIBLE,
92 INDEX `NAZWA` () VISIBLE);
93
94
95-- -----------------------------------------------------
96-- Table `Modele`
97-- -----------------------------------------------------
98CREATE TABLE IF NOT EXISTS `Modele` (
99 `ID_MODEL` INT NOT NULL,
100 `NAZWA` VARCHAR(45) NOT NULL,
101 `INFO` VARCHAR(45) NULL,
102 `GENERACJA` VARCHAR(45) NOT NULL,
103 `ID_MARKA` INT NOT NULL,
104 PRIMARY KEY (`ID_MODEL`, `ID_MARKA`),
105 UNIQUE INDEX `ID_MODEL_UNIQUE` (`ID_MODEL` ASC) VISIBLE,
106 INDEX `fk_Modele_Marki1_idx` (`ID_MARKA` ASC) VISIBLE,
107 CONSTRAINT `fk_Modele_Marki1`
108 FOREIGN KEY (`ID_MARKA`)
109 REFERENCES `Marki` (`ID_MARKA`)
110 ON DELETE NO ACTION
111 ON UPDATE NO ACTION);
112
113
114-- -----------------------------------------------------
115-- Table `Samochody`
116-- -----------------------------------------------------
117CREATE TABLE IF NOT EXISTS `Samochody` (
118 `ID_SAMOCHOD` INT NOT NULL AUTO_INCREMENT,
119 `VIN` VARCHAR(17) NOT NULL,
120 `NUMER_REJESTRACYJNY` VARCHAR(10) NOT NULL,
121 `PRZEBIEG` INT NOT NULL,
122 `RODZAJ_PALIWA` VARCHAR(20) NOT NULL,
123 `POJEMNOSC_SILNIKA` INT NOT NULL,
124 `SKRZYNIA_BIEGOW` VARCHAR(20) NOT NULL,
125 `ROK_PRODUKCJI` YEAR NOT NULL,
126 `KOLOR` VARCHAR(20) NOT NULL,
127 `ZDJECIE` VARCHAR(64) NOT NULL,
128 `ID_MODEL` INT NOT NULL,
129 `ID_MARKA` INT NOT NULL,
130 PRIMARY KEY (`ID_SAMOCHOD`, `ID_MODEL`, `ID_MARKA`),
131 UNIQUE INDEX `ID_SAMOCHOD_UNIQUE` (`ID_SAMOCHOD` ASC) VISIBLE,
132 UNIQUE INDEX `VIN_UNIQUE` (`VIN` ASC) VISIBLE,
133 INDEX `fk_Samochody_Modele1_idx` (`ID_MODEL` ASC) VISIBLE,
134 INDEX `fk_Samochody_Marki1_idx` (`ID_MARKA` ASC) VISIBLE,
135 INDEX `RODZAJ_PALIWA` () VISIBLE,
136 INDEX `POJEMNOSC_SILNIKA` () VISIBLE,
137 CONSTRAINT `fk_Samochody_Modele1`
138 FOREIGN KEY (`ID_MODEL`)
139 REFERENCES `Modele` (`ID_MODEL`)
140 ON DELETE NO ACTION
141 ON UPDATE NO ACTION,
142 CONSTRAINT `fk_Samochody_Marki1`
143 FOREIGN KEY (`ID_MARKA`)
144 REFERENCES `Marki` (`ID_MARKA`)
145 ON DELETE NO ACTION
146 ON UPDATE NO ACTION)
147ENGINE = InnoDB;
148
149
150-- -----------------------------------------------------
151-- Table `Oferty`
152-- -----------------------------------------------------
153CREATE TABLE IF NOT EXISTS `Oferty` (
154 `ID_OFERTA` INT NOT NULL AUTO_INCREMENT,
155 `ID_PRACOWNIK` INT NOT NULL,
156 `ID_SAMOCHOD` INT NOT NULL,
157 `KWOTA_ZAKUPU` INT NULL,
158 `CENA` INT NOT NULL,
159 `DATA_WYSTAWIENIA` DATE NOT NULL,
160 `OPIS_OFERTY` TEXT NULL,
161 `ILOSC_WYSWIETLEN` INT NULL,
162 `AKTYWNA` TINYINT NULL,
163 PRIMARY KEY (`ID_OFERTA`, `ID_PRACOWNIK`, `ID_SAMOCHOD`),
164 INDEX `fk_Oferty_Pracownicy1_idx` (`ID_PRACOWNIK` ASC) VISIBLE,
165 INDEX `fk_Oferty_Samochody1_idx` (`ID_SAMOCHOD` ASC) VISIBLE,
166 UNIQUE INDEX `ID_OFERTA_UNIQUE` (`ID_OFERTA` ASC) VISIBLE,
167 CONSTRAINT `fk_Oferty_Pracownicy1`
168 FOREIGN KEY (`ID_PRACOWNIK`)
169 REFERENCES `Pracownicy` (`ID_PRACOWNIK`)
170 ON DELETE NO ACTION
171 ON UPDATE NO ACTION,
172 CONSTRAINT `fk_Oferty_Samochody1`
173 FOREIGN KEY (`ID_SAMOCHOD`)
174 REFERENCES `Samochody` (`ID_SAMOCHOD`)
175 ON DELETE NO ACTION
176 ON UPDATE NO ACTION)
177ENGINE = InnoDB;
178
179
180-- -----------------------------------------------------
181-- Table `Zasoby_finansowe`
182-- -----------------------------------------------------
183CREATE TABLE IF NOT EXISTS `Zasoby_finansowe` (
184 `ID_LOG` INT NOT NULL AUTO_INCREMENT,
185 `KAPITAL` INT NOT NULL,
186 PRIMARY KEY (`ID_LOG`))
187ENGINE = InnoDB;
188
189
190-- -----------------------------------------------------
191-- Table `Transakcje`
192-- -----------------------------------------------------
193CREATE TABLE IF NOT EXISTS `Transakcje` (
194 `ID_TRANSAKCJA` INT NOT NULL AUTO_INCREMENT,
195 `ID_PRACOWNIK` INT NOT NULL,
196 `ID_OSOBA` INT NOT NULL,
197 `ID_LOG` INT NOT NULL,
198 `ID_OFERTA` INT NOT NULL,
199 `RODZAJ_TRANSAKCJI` VARCHAR(9) NOT NULL,
200 `DATA_TRANSAKCJI` DATE NOT NULL,
201 `Kwota` INT NOT NULL,
202 `NUMER_FAKTURY` VARCHAR(45) NOT NULL,
203 INDEX `fk_Transakcje_Pracownicy1_idx` (`ID_PRACOWNIK` ASC) VISIBLE,
204 PRIMARY KEY (`ID_TRANSAKCJA`, `ID_PRACOWNIK`, `ID_OSOBA`, `ID_LOG`, `ID_OFERTA`),
205 UNIQUE INDEX `ID_TRANSAKCJA_UNIQUE` (`ID_TRANSAKCJA` ASC) VISIBLE,
206 INDEX `fk_Transakcje_Zasoby_finansowe1_idx` (`ID_LOG` ASC) VISIBLE,
207 INDEX `fk_Transakcje_Osoby1_idx` (`ID_OSOBA` ASC) VISIBLE,
208 INDEX `fk_Transakcje_Oferty1_idx` (`ID_OFERTA` ASC) VISIBLE,
209 CONSTRAINT `fk_Transakcje_Pracownicy1`
210 FOREIGN KEY (`ID_PRACOWNIK`)
211 REFERENCES `Pracownicy` (`ID_PRACOWNIK`)
212 ON DELETE NO ACTION
213 ON UPDATE NO ACTION,
214 CONSTRAINT `fk_Transakcje_Zasoby_finansowe1`
215 FOREIGN KEY (`ID_LOG`)
216 REFERENCES `Zasoby_finansowe` (`ID_LOG`)
217 ON DELETE NO ACTION
218 ON UPDATE NO ACTION,
219 CONSTRAINT `fk_Transakcje_Osoby1`
220 FOREIGN KEY (`ID_OSOBA`)
221 REFERENCES `Osoby` (`ID_OSOBA`)
222 ON DELETE NO ACTION
223 ON UPDATE NO ACTION,
224 CONSTRAINT `fk_Transakcje_Oferty1`
225 FOREIGN KEY (`ID_OFERTA`)
226 REFERENCES `Oferty` (`ID_OFERTA`)
227 ON DELETE NO ACTION
228 ON UPDATE NO ACTION)
229ENGINE = InnoDB;
230
231USE ;
232
233-- -----------------------------------------------------
234-- Placeholder table for view `AKTUALNE_OFERTY`
235-- -----------------------------------------------------
236CREATE TABLE IF NOT EXISTS `AKTUALNE_OFERTY` (`ID_OFERTA` INT, `IMIE` INT, `NAZWISKO` INT, `CENA` INT, `DATA_WYSTAWIENIA` INT, `OPIS_OFERTY` INT, `AKTYWNA` INT, `MARKA` INT, `MODEL` INT, `VIN` INT, `NUMER_REJESTRACYJNY` INT, `PRZEBIEG` INT, `RODZAJ_PALIWA` INT, `POJEMNOSC_SILNIKA` INT, `SKRZYNIA_BIEGOW` INT);
237
238-- -----------------------------------------------------
239-- Placeholder table for view `TRANSAKCJE_POWYZEJ`
240-- -----------------------------------------------------
241CREATE TABLE IF NOT EXISTS `TRANSAKCJE_POWYZEJ` (`id` INT);
242
243-- -----------------------------------------------------
244-- Placeholder table for view `WSZYSCY_PRACOWNICY`
245-- -----------------------------------------------------
246CREATE TABLE IF NOT EXISTS `WSZYSCY_PRACOWNICY` (`id` INT);
247
248-- -----------------------------------------------------
249-- View `AKTUALNE_OFERTY`
250-- -----------------------------------------------------
251DROP TABLE IF EXISTS `AKTUALNE_OFERTY`;
252USE
253CREATE OR REPLACE VIEW `AKTUALNE_OFERTY` AS
254 SELECT
255 ID_OFERTA,
256 Osoby.IMIE,
257 Osoby.NAZWISKO,
258 CENA,
259 DATA_WYSTAWIENIA,
260 OPIS_OFERTY,
261 AKTYWNA,
262 Samochody.MARKA,
263 Samochody.MODEL,
264 Samochody.VIN,
265 Samochody.NUMER_REJESTRACYJNY,
266 Samochody.PRZEBIEG,
267 Samochody.RODZAJ_PALIWA,
268 Samochody.POJEMNOSC_SILNIKA,
269 Samochody.SKRZYNIA_BIEGOW
270 FROM
271 Oferty,
272 Samochody,
273 Pracownicy,
274 Osoby
275 WHERE
276 AKTYWNA = 1
277 AND Oferty.ID_PRACOWNIK = Pracownicy.ID_PRACOWNIK
278 AND Oferty.ID_SAMOCHOD = Samochody.ID_SAMOCHOD
279 AND Pracownicy.ID_OSOBA = Osoby.ID_OSOBA;
280
281-- -----------------------------------------------------
282-- View `TRANSAKCJE_POWYZEJ`
283-- -----------------------------------------------------
284DROP TABLE IF EXISTS `TRANSAKCJE_POWYZEJ`;
285USE
286CREATE OR REPLACE VIEW `TRANSAKCJE_POWYZEJ` AS
287 SELECT
288 ID_TRANSAKCJA,
289 Osoby.IMIE,
290 Osoby.Nazwisko,
291 RODZAJ_TRANSAKCJI,
292 DATA_TRANSAKCJI,
293 KWOTA
294 FROM
295 Transakcje,
296 Osoby
297 WHERE
298 KWOTA > 15000
299 AND Transakcje.Osoby_ID == Osoby.ID_OSOBA;
300
301-- -----------------------------------------------------
302-- View `WSZYSCY_PRACOWNICY`
303-- -----------------------------------------------------
304DROP TABLE IF EXISTS `WSZYSCY_PRACOWNICY`;
305USE
306CREATE OR REPLACE VIEW `WSZYSCY_PRACOWNICY` AS
307 SELECT
308 ID_PRACOWNIK,
309 Osoby.IMIE,
310 Osoby.NAZWISKO,
311 Osoby.PESEL,
312 DATA_ZATRUDNIENIA,
313 PENSJA
314 FROM
315 Pracownicy,
316 Osoby
317 WHERE
318 PENSJA > 3000
319 AND Pracownicy.ID_OSOBA == Osoby.ID_OSOBA;
320USE
321
322DELIMITER $$
323USE $
324CREATE TRIGGER `updateTrigger` AFTER INSERT ON `Transakcje` FOR EACH ROW BEGIN
325 SELECT COUNT(`KAPITAL`) INTO @ifAny FROM Zasoby_finansowe;
326 IF @ifAny = 0 THEN SET @tmpKapital := 0;
327 END IF;
328
329 SELECT KAPITAL INTO @tmpKapital FROM Zasoby_finansowe ORDER BY `ID_LOG` DESC LIMIT 1;
330 IF LOWER(NEW.RODZAJ_TRANSAKCJI) = 'kupno' THEN
331 INSERT INTO `Zasoby_finansowe` (`ID_LOG`, `ID_TRANSAKCJA`, `KAPITAL`)
332 VALUES ("", new.ID_TRANSAKCJA, @tmpKapital - new.KWOTA);
333 ELSEIF
334 LOWER(NEW.RODZAJ_TRANSAKCJI) = 'sprzedaz' THEN
335 UPDATE `Oferty` SET AKTYWNA = 0 WHERE Oferty.ID_OFERTA = new.ID_OFERTA;
336 INSERT INTO `Zasoby_finansowe` (`ID_LOG`, `ID_TRANSAKCJA`, `KAPITAL`)
337 VALUES ("", new.ID_TRANSAKCJA, @tmpKapital+new.KWOTA);
338 END IF;
339END$$
340
341
342DELIMITER ;
343
344SET SQL_MODE=@OLD_SQL_MODE;
345SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
346SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;