· 9 years ago · Nov 06, 2016, 05:14 PM
1-- MySQL Script generated by MySQL Workbench
2-- 11/06/16 18:12:17
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`.`user`
22-- -----------------------------------------------------
23CREATE TABLE IF NOT EXISTS `mydb`.`user` (
24 `username` (16) NOT NULL,
25 `email` (255) NULL,
26 `password` (32) NOT NULL,
27 `create_time` NULL DEFAULT CURRENT_TIMESTAMP);
28
29
30-- -----------------------------------------------------
31-- Table `mydb`.`7_Wydawnictwa`
32-- -----------------------------------------------------
33CREATE TABLE IF NOT EXISTS `mydb`.`7_Wydawnictwa` (
34 `IdWydawnictwa` INT NOT NULL,
35 `NazwaWydawnictwa` VARCHAR(50) NOT NULL,
36 `AdresWydawnictwa` VARCHAR(50) NOT NULL,
37 `Telefon` VARCHAR(20) NOT NULL,
38 PRIMARY KEY (`IdWydawnictwa`),
39 UNIQUE INDEX `IdWydawnictwa_UNIQUE` (`IdWydawnictwa` ASC))
40ENGINE = InnoDB;
41
42
43-- -----------------------------------------------------
44-- Table `mydb`.`7_Autorzy`
45-- -----------------------------------------------------
46CREATE TABLE IF NOT EXISTS `mydb`.`7_Autorzy` (
47 `IdAutora` INT NOT NULL,
48 `ImieAutora` VARCHAR(50) NOT NULL,
49 `NazwiskoAutora` VARCHAR(50) NOT NULL,
50 `NarodowoscAutora` VARCHAR(50) NOT NULL,
51 PRIMARY KEY (`IdAutora`),
52 UNIQUE INDEX `IdAutora_UNIQUE` (`IdAutora` ASC));
53
54
55-- -----------------------------------------------------
56-- Table `mydb`.`7_Ksiazki`
57-- -----------------------------------------------------
58CREATE TABLE IF NOT EXISTS `mydb`.`7_Ksiazki` (
59 `IdKsiazki` INT NOT NULL,
60 `ISBN` VARCHAR(20) NOT NULL,
61 `Tytul` VARCHAR(50) NOT NULL,
62 `Autor` VARCHAR(30) NOT NULL,
63 `Wydawnictwo` INT(4) NOT NULL,
64 `RokWydania` INT(4) NOT NULL,
65 `Gatunek` VARCHAR(30) NOT NULL,
66 `Cena` FLOAT(30) NOT NULL,
67 `IdWydawnictwa` INT NOT NULL,
68 PRIMARY KEY (`IdKsiazki`),
69 UNIQUE INDEX `IdKsiazki_UNIQUE` (`IdKsiazki` ASC),
70 UNIQUE INDEX `ISBN_UNIQUE` (`ISBN` ASC),
71 INDEX `IdWydawnictwa_idx` (`IdWydawnictwa` ASC),
72 CONSTRAINT `IdWydawnictwa`
73 FOREIGN KEY (`IdWydawnictwa`)
74 REFERENCES `mydb`.`7_Wydawnictwa` (`IdWydawnictwa`)
75 ON DELETE NO ACTION
76 ON UPDATE NO ACTION);
77
78
79-- -----------------------------------------------------
80-- Table `mydb`.`7_Zamowienia`
81-- -----------------------------------------------------
82CREATE TABLE IF NOT EXISTS `mydb`.`7_Zamowienia` (
83 `IdZamowienia` INT NOT NULL,
84 `IdKlienta` INT NOT NULL,
85 `DataUtworzenia` DATE NOT NULL,
86 `DataZrealizowania` DATE NOT NULL,
87 `Stan` VARCHAR(30) NOT NULL,
88 `Operator` VARCHAR(50) NULL,
89 `Zamawiajacy` VARCHAR(50) NOT NULL,
90 `Wartosc` FLOAT(30) NULL,
91 PRIMARY KEY (`IdZamowienia`),
92 UNIQUE INDEX `IdZamowienia_UNIQUE` (`IdZamowienia` ASC));
93
94
95-- -----------------------------------------------------
96-- Table `mydb`.`7_Zamowienia_Ksiazki`
97-- -----------------------------------------------------
98CREATE TABLE IF NOT EXISTS `mydb`.`7_Zamowienia_Ksiazki` (
99 `IdZamowieniaKsiazki` INT NOT NULL,
100 `IdZamowienia` INT NOT NULL,
101 `IdKsiazki` INT NOT NULL,
102 `IloscEgzemplarzy` INT NOT NULL,
103 PRIMARY KEY (`IdZamowieniaKsiazki`),
104 UNIQUE INDEX `LiczbaEgzemplarzy_UNIQUE` (`IdZamowieniaKsiazki` ASC),
105 UNIQUE INDEX `IdZamowienia_UNIQUE` (`IdZamowienia` ASC),
106 INDEX `IdKsiazki_idx` (`IdKsiazki` ASC),
107 CONSTRAINT `IdZamowienia`
108 FOREIGN KEY (`IdZamowienia`)
109 REFERENCES `mydb`.`7_Zamowienia` (`IdZamowienia`)
110 ON DELETE NO ACTION
111 ON UPDATE NO ACTION,
112 CONSTRAINT `IdKsiazki`
113 FOREIGN KEY (`IdKsiazki`)
114 REFERENCES `mydb`.`7_Ksiazki` (`IdKsiazki`)
115 ON DELETE NO ACTION
116 ON UPDATE NO ACTION);
117
118
119-- -----------------------------------------------------
120-- Table `mydb`.`7_Klienci`
121-- -----------------------------------------------------
122CREATE TABLE IF NOT EXISTS `mydb`.`7_Klienci` (
123 `IdKlienta` INT NOT NULL,
124 `ImieKllienta` VARCHAR(50) NOT NULL,
125 `NazwiskoKlienta` VARCHAR(50) NOT NULL,
126 `Adres` VARCHAR(50) NOT NULL,
127 `Kontakt` VARCHAR(50) NOT NULL,
128 `DataUrodzenia` DATE NOT NULL,
129 PRIMARY KEY (`IdKlienta`),
130 UNIQUE INDEX `IdKlienta_UNIQUE` (`IdKlienta` ASC));
131
132
133-- -----------------------------------------------------
134-- Table `mydb`.`7_Konta`
135-- -----------------------------------------------------
136CREATE TABLE IF NOT EXISTS `mydb`.`7_Konta` (
137 `IdKonta` INT NOT NULL,
138 `IdKlienta` INT NOT NULL,
139 `AdresEmail` VARCHAR(50) NOT NULL,
140 `DataRejestracji` DATE NOT NULL,
141 UNIQUE INDEX `AdresEmail_UNIQUE` (`AdresEmail` ASC),
142 PRIMARY KEY (`IdKonta`),
143 INDEX `IdKlienta_idx` (`IdKlienta` ASC),
144 CONSTRAINT `IdKlienta`
145 FOREIGN KEY (`IdKlienta`)
146 REFERENCES `mydb`.`7_Klienci` (`IdKlienta`)
147 ON DELETE NO ACTION
148 ON UPDATE NO ACTION);
149
150
151-- -----------------------------------------------------
152-- Table `mydb`.`7_Opinie`
153-- -----------------------------------------------------
154CREATE TABLE IF NOT EXISTS `mydb`.`7_Opinie` (
155 `IdOpinii` INT NOT NULL,
156 `IdKsiazki` INT NOT NULL,
157 `Tresc` VARCHAR(300) NOT NULL,
158 `DataWystawienia` DATE NOT NULL,
159 UNIQUE INDEX `IdOpinii_UNIQUE` (`IdOpinii` ASC),
160 PRIMARY KEY (`IdOpinii`),
161 INDEX `IdKsiazki_idx` (`IdKsiazki` ASC),
162 CONSTRAINT `IdKsiazki`
163 FOREIGN KEY (`IdKsiazki`)
164 REFERENCES `mydb`.`7_Ksiazki` (`IdKsiazki`)
165 ON DELETE NO ACTION
166 ON UPDATE NO ACTION);
167
168
169-- -----------------------------------------------------
170-- Table `mydb`.`7_Pracownicy`
171-- -----------------------------------------------------
172CREATE TABLE IF NOT EXISTS `mydb`.`7_Pracownicy` (
173 `IdPracownika` INT NOT NULL,
174 `ImiePracownika` VARCHAR(50) NOT NULL,
175 `NazwiskoPracownika` VARCHAR(50) NOT NULL,
176 `Adres` VARCHAR(50) NOT NULL,
177 `Kontakt` VARCHAR(50) NOT NULL,
178 `Stanowisko` VARCHAR(50) NOT NULL,
179 `DataUrodzenia` DATE NULL,
180 PRIMARY KEY (`IdPracownika`),
181 UNIQUE INDEX `IdPracownika_UNIQUE` (`IdPracownika` ASC));
182
183
184-- -----------------------------------------------------
185-- Table `mydb`.`7_Pracownicy_Zamowienia`
186-- -----------------------------------------------------
187CREATE TABLE IF NOT EXISTS `mydb`.`7_Pracownicy_Zamowienia` (
188 `PracownikZamowienie` INT NOT NULL,
189 `IdPracownika` INT NULL,
190 `IdZamowienia` INT NULL,
191 PRIMARY KEY (`PracownikZamowienie`),
192 INDEX `IdZamowienia_idx` (`IdZamowienia` ASC),
193 INDEX `IdPracownika_idx` (`IdPracownika` ASC),
194 CONSTRAINT `IdPracownika`
195 FOREIGN KEY (`IdPracownika`)
196 REFERENCES `mydb`.`7_Pracownicy` (`IdPracownika`)
197 ON DELETE NO ACTION
198 ON UPDATE NO ACTION,
199 CONSTRAINT `IdZamowienia`
200 FOREIGN KEY (`IdZamowienia`)
201 REFERENCES `mydb`.`7_Zamowienia` (`IdZamowienia`)
202 ON DELETE NO ACTION
203 ON UPDATE NO ACTION)
204ENGINE = InnoDB;
205
206
207-- -----------------------------------------------------
208-- Table `mydb`.`7_Konta_Opinie`
209-- -----------------------------------------------------
210CREATE TABLE IF NOT EXISTS `mydb`.`7_Konta_Opinie` (
211 `IdKontoOpinia` INT NOT NULL,
212 `IdKonta` INT NOT NULL,
213 `IdOpinii` INT NOT NULL,
214 PRIMARY KEY (`IdKontoOpinia`),
215 INDEX `AdresEmail_idx` (`IdKonta` ASC),
216 INDEX `IdOpinii_idx` (`IdOpinii` ASC),
217 CONSTRAINT `IdKonta`
218 FOREIGN KEY (`IdKonta`)
219 REFERENCES `mydb`.`7_Konta` (`IdKonta`)
220 ON DELETE NO ACTION
221 ON UPDATE NO ACTION,
222 CONSTRAINT `IdOpinii`
223 FOREIGN KEY (`IdOpinii`)
224 REFERENCES `mydb`.`7_Opinie` (`IdOpinii`)
225 ON DELETE NO ACTION
226 ON UPDATE NO ACTION)
227ENGINE = InnoDB;
228
229
230-- -----------------------------------------------------
231-- Table `mydb`.`7_Gatunki`
232-- -----------------------------------------------------
233CREATE TABLE IF NOT EXISTS `mydb`.`7_Gatunki` (
234 `IdGatunku` INT NOT NULL,
235 `Gatunek` VARCHAR(45) NULL,
236 PRIMARY KEY (`IdGatunku`))
237ENGINE = InnoDB;
238
239
240-- -----------------------------------------------------
241-- Table `mydb`.`7_Ksiazki_Gatunki`
242-- -----------------------------------------------------
243CREATE TABLE IF NOT EXISTS `mydb`.`7_Ksiazki_Gatunki` (
244 `IdKsiazkaGatunek` INT NOT NULL,
245 `IdKsiazki` INT NULL,
246 `IdGatunku` INT NOT NULL,
247 PRIMARY KEY (`IdKsiazkaGatunek`),
248 INDEX `IDKsiazki_idx` (`IdKsiazki` ASC),
249 UNIQUE INDEX `IdKsiazkaGatunek_UNIQUE` (`IdKsiazkaGatunek` ASC),
250 CONSTRAINT `IdGatunku`
251 FOREIGN KEY (`IdKsiazkaGatunek`)
252 REFERENCES `mydb`.`7_Gatunki` (`IdGatunku`)
253 ON DELETE NO ACTION
254 ON UPDATE NO ACTION,
255 CONSTRAINT `IDKsiazki`
256 FOREIGN KEY (`IdKsiazki`)
257 REFERENCES `mydb`.`7_Ksiazki` (`IdKsiazki`)
258 ON DELETE NO ACTION
259 ON UPDATE NO ACTION)
260ENGINE = InnoDB;
261
262
263-- -----------------------------------------------------
264-- Table `mydb`.`7_Ksiazki_Autorzy`
265-- -----------------------------------------------------
266CREATE TABLE IF NOT EXISTS `mydb`.`7_Ksiazki_Autorzy` (
267 `IdKsiazkaAutor` INT NOT NULL,
268 `IdAutora` INT NOT NULL,
269 `IdKsiazki` INT NOT NULL,
270 PRIMARY KEY (`IdKsiazkaAutor`),
271 INDEX `IdKsiazki_idx` (`IdKsiazki` ASC),
272 INDEX `IdAutora_idx` (`IdAutora` ASC),
273 CONSTRAINT `IdKsiazki`
274 FOREIGN KEY (`IdKsiazki`)
275 REFERENCES `mydb`.`7_Ksiazki` (`IdKsiazki`)
276 ON DELETE NO ACTION
277 ON UPDATE NO ACTION,
278 CONSTRAINT `IdAutora`
279 FOREIGN KEY (`IdAutora`)
280 REFERENCES `mydb`.`7_Autorzy` (`IdAutora`)
281 ON DELETE NO ACTION
282 ON UPDATE NO ACTION)
283ENGINE = InnoDB;
284
285
286SET SQL_MODE=@OLD_SQL_MODE;
287SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
288SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;