· 9 years ago · Dec 01, 2016, 11:12 AM
1-- MySQL Script generated by MySQL Workbench
2-- 12/01/16 12:03:13
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 gabinet
12-- -----------------------------------------------------
13
14-- -----------------------------------------------------
15-- Schema gabinet
16-- -----------------------------------------------------
17CREATE SCHEMA IF NOT EXISTS `gabinet` DEFAULT CHARACTER SET utf8 COLLATE utf8_polish_ci ;
18USE `gabinet` ;
19
20-- -----------------------------------------------------
21-- Table `gabinet`.`uzytkownik`
22-- -----------------------------------------------------
23CREATE TABLE IF NOT EXISTS `gabinet`.`uzytkownik` (
24 `uzytkownik_id` INT NOT NULL AUTO_INCREMENT,
25 `uzytkownik_nazwa` VARCHAR(45) NOT NULL,
26 `imie` VARCHAR(45) NOT NULL,
27 `nazwisko` VARCHAR(120) NOT NULL,
28 `haslo` VARCHAR(120) NOT NULL,
29 `pracownik` TINYINT(1) NOT NULL DEFAULT 0,
30 PRIMARY KEY (`uzytkownik_id`),
31 UNIQUE INDEX `uzytkownik_id_UNIQUE` (`uzytkownik_id` ASC),
32 UNIQUE INDEX `uzytkownik_nazwa_UNIQUE` (`uzytkownik_nazwa` ASC))
33ENGINE = InnoDB;
34
35
36-- -----------------------------------------------------
37-- Table `gabinet`.`godziny`
38-- -----------------------------------------------------
39CREATE TABLE IF NOT EXISTS `gabinet`.`godziny` (
40 `godziny_id` INT NOT NULL AUTO_INCREMENT,
41 `uzytkownik_id` INT NOT NULL,
42 `pon_od` TIME NULL,
43 `pon_do` TIME NULL,
44 `wt_od` TIME NULL,
45 `wt_do` TIME NULL,
46 `sr_od` TIME NULL,
47 `sr_do` TIME NULL,
48 `cz_od` TIME NULL,
49 `cz_do` TIME NULL,
50 `pt_od` TIME NULL,
51 `pt_do` TIME NULL,
52 `so_od` TIME NULL,
53 `so_do` TIME NULL,
54 PRIMARY KEY (`godziny_id`),
55 UNIQUE INDEX `godziny_id_UNIQUE` (`godziny_id` ASC),
56 INDEX `fk_godziny_uzytkownik_idx` (`uzytkownik_id` ASC),
57 CONSTRAINT `fk_godziny_uzytkownik`
58 FOREIGN KEY (`uzytkownik_id`)
59 REFERENCES `gabinet`.`uzytkownik` (`uzytkownik_id`)
60 ON DELETE NO ACTION
61 ON UPDATE NO ACTION)
62ENGINE = InnoDB;
63
64
65-- -----------------------------------------------------
66-- Table `gabinet`.`uslugi`
67-- -----------------------------------------------------
68CREATE TABLE IF NOT EXISTS `gabinet`.`uslugi` (
69 `uslugi_id` INT NOT NULL AUTO_INCREMENT,
70 `nazwa` VARCHAR(80) NOT NULL,
71 `cena` DECIMAL(6,2) NOT NULL,
72 `czas` TIME NOT NULL,
73 `opis` TEXT NOT NULL,
74 PRIMARY KEY (`uslugi_id`))
75ENGINE = InnoDB;
76
77
78-- -----------------------------------------------------
79-- Table `gabinet`.`uzytkownik_usluga`
80-- -----------------------------------------------------
81CREATE TABLE IF NOT EXISTS `gabinet`.`uzytkownik_usluga` (
82 `uzytkownik_id` INT NOT NULL,
83 `uslugi_id` INT NOT NULL,
84 `modyfikacja` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
85 INDEX `fk_uzytkownik_usluga_uzytkownik1_idx` (`uzytkownik_id` ASC),
86 INDEX `fk_uzytkownik_usluga_uslugi1_idx` (`uslugi_id` ASC),
87 CONSTRAINT `fk_uzytkownik_usluga_uzytkownik1`
88 FOREIGN KEY (`uzytkownik_id`)
89 REFERENCES `gabinet`.`uzytkownik` (`uzytkownik_id`)
90 ON DELETE NO ACTION
91 ON UPDATE NO ACTION,
92 CONSTRAINT `fk_uzytkownik_usluga_uslugi1`
93 FOREIGN KEY (`uslugi_id`)
94 REFERENCES `gabinet`.`uslugi` (`uslugi_id`)
95 ON DELETE NO ACTION
96 ON UPDATE NO ACTION)
97ENGINE = InnoDB;
98
99
100-- -----------------------------------------------------
101-- Table `gabinet`.`klienci`
102-- -----------------------------------------------------
103CREATE TABLE IF NOT EXISTS `gabinet`.`klienci` (
104 `klienci_id` INT NOT NULL AUTO_INCREMENT,
105 `imie` VARCHAR(45) NOT NULL,
106 `nazwisko` VARCHAR(120) NOT NULL,
107 `email` VARCHAR(80) NULL,
108 `telefon` VARCHAR(15) NULL,
109 `ulica` VARCHAR(120) NULL,
110 `numer` VARCHAR(10) NULL,
111 `miejscowosc` VARCHAR(120) NULL,
112 `poczta` VARCHAR(10) NULL,
113 `modyfikacja` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
114 PRIMARY KEY (`klienci_id`))
115ENGINE = InnoDB;
116
117
118-- -----------------------------------------------------
119-- Table `gabinet`.`wizyty`
120-- -----------------------------------------------------
121CREATE TABLE IF NOT EXISTS `gabinet`.`wizyty` (
122 `wizyty_id` INT NOT NULL AUTO_INCREMENT,
123 `klienci_id` INT NOT NULL,
124 `uslugi_id` INT NOT NULL,
125 `uzytkownik_id` INT NOT NULL,
126 `rezerwacja_od` DATETIME NULL,
127 `rezerwacja_do` DATETIME NULL,
128 `status` ENUM('oczekuje', 'wykonana', 'rezygnacja') NULL,
129 `modyfikacja` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
130 PRIMARY KEY (`wizyty_id`),
131 INDEX `fk_wizyty_klienci1_idx` (`klienci_id` ASC),
132 INDEX `fk_wizyty_uslugi1_idx` (`uslugi_id` ASC),
133 INDEX `fk_wizyty_uzytkownik1_idx` (`uzytkownik_id` ASC),
134 CONSTRAINT `fk_wizyty_klienci1`
135 FOREIGN KEY (`klienci_id`)
136 REFERENCES `gabinet`.`klienci` (`klienci_id`)
137 ON DELETE NO ACTION
138 ON UPDATE NO ACTION,
139 CONSTRAINT `fk_wizyty_uslugi1`
140 FOREIGN KEY (`uslugi_id`)
141 REFERENCES `gabinet`.`uslugi` (`uslugi_id`)
142 ON DELETE NO ACTION
143 ON UPDATE NO ACTION,
144 CONSTRAINT `fk_wizyty_uzytkownik1`
145 FOREIGN KEY (`uzytkownik_id`)
146 REFERENCES `gabinet`.`uzytkownik` (`uzytkownik_id`)
147 ON DELETE NO ACTION
148 ON UPDATE NO ACTION)
149ENGINE = InnoDB;
150
151
152SET SQL_MODE=@OLD_SQL_MODE;
153SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
154SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;