· 7 years ago · Oct 25, 2018, 11:20 AM
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 mydb
9-- -----------------------------------------------------
10-- -----------------------------------------------------
11-- Schema kurierska
12-- -----------------------------------------------------
13
14-- -----------------------------------------------------
15-- Table `klienci`
16-- -----------------------------------------------------
17
18DROP DATABASE IF EXISTS `kurrierska` ;
19CREATE DATABASE `kurrierska` ;
20
21DROP TABLE IF EXISTS `klienci` ;
22
23CREATE TABLE IF NOT EXISTS `klienci` (
24 `Id_klient` INT(4) NOT NULL,
25 `Nazwisko` VARCHAR(25) NOT NULL,
26 `Imie` VARCHAR(5) NOT NULL,
27 `Kod_poczta` VARCHAR(15) NOT NULL,
28 `Miasto` VARCHAR(30) NOT NULL,
29 `Ulica` VARCHAR(8) NOT NULL,
30 `Numer` VARCHAR(8) NOT NULL,
31 `Tel` VARCHAR(12) NOT NULL,
32 `Fax` VARCHAR(12) NOT NULL,
33 `E_mail` VARCHAR(30) NOT NULL,
34 PRIMARY KEY (`Id_klient`))
35ENGINE = InnoDB
36DEFAULT CHARACTER SET = utf8
37COLLATE = utf8_polish_ci;
38
39
40-- -----------------------------------------------------
41-- Table `paczki`
42-- -----------------------------------------------------
43DROP TABLE IF EXISTS `paczki` ;
44
45CREATE TABLE IF NOT EXISTS `paczki` (
46 `Id_paczka` INT(4) NOT NULL,
47 `klienci_Id_klient` INT(4) NOT NULL,
48 `Waga` INT(2) NOT NULL,
49 PRIMARY KEY (`Id_paczka`),
50 CONSTRAINT `fk_paczki_klienci1`
51 FOREIGN KEY (`klienci_Id_klient`)
52 REFERENCES `klienci` (`Id_klient`)
53 ON DELETE NO ACTION
54 ON UPDATE NO ACTION)
55ENGINE = InnoDB
56DEFAULT CHARACTER SET = utf8
57COLLATE = utf8_polish_ci;
58
59CREATE INDEX `fk_paczki_klienci1_idx` ON `paczki` (`klienci_Id_klient` ASC);
60
61
62-- -----------------------------------------------------
63-- Table `samochody`
64-- -----------------------------------------------------
65DROP TABLE IF EXISTS `samochody` ;
66
67CREATE TABLE IF NOT EXISTS `samochody` (
68 `Id_samochod` INT(4) NOT NULL,
69 `Marka` VARCHAR(40) NOT NULL,
70 `Model` VARCHAR(40) NOT NULL,
71 `Kolor` VARCHAR(40) NOT NULL,
72 `Rok produkcji` VARCHAR(40) NOT NULL,
73 PRIMARY KEY (`Id_samochod`))
74ENGINE = InnoDB
75DEFAULT CHARACTER SET = utf8
76COLLATE = utf8_polish_ci;
77
78
79-- -----------------------------------------------------
80-- Table `kurierzy`
81-- -----------------------------------------------------
82DROP TABLE IF EXISTS `kurierzy` ;
83
84CREATE TABLE IF NOT EXISTS `kurierzy` (
85 `Id_kurier` INT(4) NOT NULL,
86 `Nazwisko` VARCHAR(25) NOT NULL,
87 `Imie` VARCHAR(5) NOT NULL,
88 `Kod_poczta` VARCHAR(15) NOT NULL,
89 `Miasto` VARCHAR(30) NOT NULL,
90 `Ulica` VARCHAR(8) NOT NULL,
91 `Numer` VARCHAR(8) NOT NULL,
92 `Tel` VARCHAR(12) NOT NULL,
93 `Fax` VARCHAR(12) NOT NULL,
94 `E_mail` VARCHAR(30) NOT NULL,
95 PRIMARY KEY (`Id_kurier`))
96ENGINE = InnoDB
97DEFAULT CHARACTER SET = utf8
98COLLATE = utf8_polish_ci;
99
100
101-- -----------------------------------------------------
102-- Table `wyjazdy`
103-- -----------------------------------------------------
104DROP TABLE IF EXISTS `wyjazdy` ;
105
106CREATE TABLE IF NOT EXISTS `wyjazdy` (
107 `Id_wyjazd` INT(4) NOT NULL,
108 `samochody_Id_samochod` INT(4) NOT NULL,
109 `kurierzy_Id_kurier` INT(4) NOT NULL,
110 `Godzina wyjazdu` DATETIME NOT NULL,
111 PRIMARY KEY (`Id_wyjazd`),
112 CONSTRAINT `fk_wyjazdy_samochody1`
113 FOREIGN KEY (`samochody_Id_samochod`)
114 REFERENCES `samochody` (`Id_samochod`)
115 ON DELETE NO ACTION
116 ON UPDATE NO ACTION,
117 CONSTRAINT `fk_wyjazdy_kurierzy1`
118 FOREIGN KEY (`kurierzy_Id_kurier`)
119 REFERENCES `kurierzy` (`Id_kurier`)
120 ON DELETE NO ACTION
121 ON UPDATE NO ACTION)
122ENGINE = InnoDB
123DEFAULT CHARACTER SET = utf8
124COLLATE = utf8_polish_ci;
125
126CREATE INDEX `fk_wyjazdy_samochody1_idx` ON `wyjazdy` (`samochody_Id_samochod` ASC);
127
128CREATE INDEX `fk_wyjazdy_kurierzy1_idx` ON `wyjazdy` (`kurierzy_Id_kurier` ASC);
129
130
131-- -----------------------------------------------------
132-- Table `status`
133-- -----------------------------------------------------
134DROP TABLE IF EXISTS `status_paczki` ;
135
136CREATE TABLE IF NOT EXISTS `status_paczki` (
137 `Nr_paczki` INT(4) NOT NULL,
138 `paczki_Id_paczka` INT(4) NOT NULL,
139 `wyjazdy_Id_wyjazd` INT(4) NOT NULL,
140 `Stan` ENUM('Dostarczona', 'Niedostarczona', 'W trakcie realizacji') NOT NULL,
141 `Godzina doreczenia` DATETIME NOT NULL,
142 PRIMARY KEY (`Nr_paczki`),
143 CONSTRAINT `fk_status_paczki_paczki`
144 FOREIGN KEY (`paczki_Id_paczka`)
145 REFERENCES `paczki` (`Id_paczka`)
146 ON DELETE NO ACTION
147 ON UPDATE NO ACTION,
148 CONSTRAINT `fk_status_wyjazdy1`
149 FOREIGN KEY (`wyjazdy_Id_wyjazd`)
150 REFERENCES `wyjazdy` (`Id_wyjazd`)
151 ON DELETE NO ACTION
152 ON UPDATE NO ACTION)
153ENGINE = InnoDB
154DEFAULT CHARACTER SET = utf8
155COLLATE = utf8_polish_ci;
156
157CREATE INDEX `fk_status_paczki_paczki_idx` ON `status_paczki` (`paczki_Id_paczka` ASC);
158
159CREATE INDEX `fk_status_paczki_wyjazdy1_idx` ON `status_paczki` (`wyjazdy_Id_wyjazd` ASC);
160
161
162SET SQL_MODE=@OLD_SQL_MODE;
163SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
164SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;