6aqYBH3P

1-- MySQL Script generated by MySQL Workbench
2-- Mon Mar 19 14:51:37 2018
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`.`stawka`
22-- -----------------------------------------------------
23CREATE TABLE IF NOT EXISTS `gabinet`.`stawka` (
24  `id_stawka` INT NOT NULL AUTO_INCREMENT,
25  `wartosc_stawka` VARCHAR(45) NOT NULL,
26  PRIMARY KEY (`id_stawka`))
27ENGINE = InnoDB;
28
29
30-- -----------------------------------------------------
31-- Table `gabinet`.`osoba`
32-- -----------------------------------------------------
33CREATE TABLE IF NOT EXISTS `gabinet`.`osoba` (
34  `id_osoba` INT NOT NULL AUTO_INCREMENT,
35  `imie_osoba` VARCHAR(45) NOT NULL,
36  `nazwisko_osoba` VARCHAR(45) NULL,
37  `karta_kredytowa_osoba` INT NOT NULL,
38  `ulica_osoba` VARCHAR(45) NOT NULL,
39  `adres1_osoba` VARCHAR(45) NOT NULL,
40  `adres2_osoba` VARCHAR(45) NOT NULL,
41  `adres3_osoba` VARCHAR(45) NOT NULL,
42  `nip_osoba` VARCHAR(45) NOT NULL,
43  `numer_telefonu_osoba` VARCHAR(45) NOT NULL,
44  `asystent` TINYINT NOT NULL,
45  `stomatolog` TINYINT NOT NULL,
46  `stawka_id_stawka` INT NOT NULL,
47  PRIMARY KEY (`id_osoba`, `stawka_id_stawka`),
48  INDEX `fk_osoba_stawka1_idx` (`stawka_id_stawka` ASC),
49  CONSTRAINT `fk_osoba_stawka1`
50    FOREIGN KEY (`stawka_id_stawka`)
51    REFERENCES `gabinet`.`stawka` (`id_stawka`)
52    ON DELETE NO ACTION
53    ON UPDATE NO ACTION)
54ENGINE = InnoDB;
55
56
57-- -----------------------------------------------------
58-- Table `gabinet`.`gabinet`
59-- -----------------------------------------------------
60CREATE TABLE IF NOT EXISTS `gabinet`.`gabinet` (
61  `id_gabinet` INT NOT NULL AUTO_INCREMENT,
62  `numer_gabinet` VARCHAR(45) NOT NULL,
63  PRIMARY KEY (`id_gabinet`))
64ENGINE = InnoDB;
65
66
67-- -----------------------------------------------------
68-- Table `gabinet`.`wizyta`
69-- -----------------------------------------------------
70CREATE TABLE IF NOT EXISTS `gabinet`.`wizyta` (
71  `id_wizyta` INT NOT NULL AUTO_INCREMENT,
72  `data_wizyta` DATE NOT NULL,
73  `zrealizowana` TINYINT NOT NULL,
74  `osoba_id_pacjent` INT NOT NULL,
75  `osoba_id_stomatolog` INT NOT NULL,
76  `osoba_id_asystent` INT NOT NULL,
77  `gabinet_id_gabinet` INT NOT NULL,
78  PRIMARY KEY (`id_wizyta`, `osoba_id_pacjent`, `osoba_id_stomatolog`, `osoba_id_asystent`, `gabinet_id_gabinet`),
79  INDEX `fk_wizyta_osoba_idx` (`osoba_id_pacjent` ASC),
80  INDEX `fk_wizyta_osoba1_idx` (`osoba_id_stomatolog` ASC),
81  INDEX `fk_wizyta_osoba2_idx` (`osoba_id_asystent` ASC),
82  INDEX `fk_wizyta_gabinet1_idx` (`gabinet_id_gabinet` ASC),
83  CONSTRAINT `fk_wizyta_osoba`
84    FOREIGN KEY (`osoba_id_pacjent`)
85    REFERENCES `gabinet`.`osoba` (`id_osoba`)
86    ON DELETE NO ACTION
87    ON UPDATE NO ACTION,
88  CONSTRAINT `fk_wizyta_osoba1`
89    FOREIGN KEY (`osoba_id_stomatolog`)
90    REFERENCES `gabinet`.`osoba` (`id_osoba`)
91    ON DELETE NO ACTION
92    ON UPDATE NO ACTION,
93  CONSTRAINT `fk_wizyta_osoba2`
94    FOREIGN KEY (`osoba_id_asystent`)
95    REFERENCES `gabinet`.`osoba` (`id_osoba`)
96    ON DELETE NO ACTION
97    ON UPDATE NO ACTION,
98  CONSTRAINT `fk_wizyta_gabinet1`
99    FOREIGN KEY (`gabinet_id_gabinet`)
100    REFERENCES `gabinet`.`gabinet` (`id_gabinet`)
101    ON DELETE NO ACTION
102    ON UPDATE NO ACTION)
103ENGINE = InnoDB;
104
105
106-- -----------------------------------------------------
107-- Table `gabinet`.`rodzaj wizyty`
108-- -----------------------------------------------------
109CREATE TABLE IF NOT EXISTS `gabinet`.`rodzaj wizyty` (
110  `id_rodzaj_wizyty` INT NOT NULL AUTO_INCREMENT,
111  `nazwa_rodzaj_wizyty` VARCHAR(45) NOT NULL,
112  PRIMARY KEY (`id_rodzaj_wizyty`))
113ENGINE = InnoDB;
114
115
116-- -----------------------------------------------------
117-- Table `gabinet`.`wizyta_has_rodzaj wizyty`
118-- -----------------------------------------------------
119CREATE TABLE IF NOT EXISTS `gabinet`.`wizyta_has_rodzaj wizyty` (
120  `wizyta_id_wizyta` INT NOT NULL,
121  `rodzaj wizyty_id_rodzaj_wizyty` INT NOT NULL,
122  PRIMARY KEY (`wizyta_id_wizyta`, `rodzaj wizyty_id_rodzaj_wizyty`),
123  INDEX `fk_wizyta_has_rodzaj wizyty_rodzaj wizyty1_idx` (`rodzaj wizyty_id_rodzaj_wizyty` ASC),
124  INDEX `fk_wizyta_has_rodzaj wizyty_wizyta1_idx` (`wizyta_id_wizyta` ASC),
125  CONSTRAINT `fk_wizyta_has_rodzaj wizyty_wizyta1`
126    FOREIGN KEY (`wizyta_id_wizyta`)
127    REFERENCES `gabinet`.`wizyta` (`id_wizyta`)
128    ON DELETE NO ACTION
129    ON UPDATE NO ACTION,
130  CONSTRAINT `fk_wizyta_has_rodzaj wizyty_rodzaj wizyty1`
131    FOREIGN KEY (`rodzaj wizyty_id_rodzaj_wizyty`)
132    REFERENCES `gabinet`.`rodzaj wizyty` (`id_rodzaj_wizyty`)
133    ON DELETE NO ACTION
134    ON UPDATE NO ACTION)
135ENGINE = InnoDB;
136
137
138-- -----------------------------------------------------
139-- Table `gabinet`.`jednostka`
140-- -----------------------------------------------------
141CREATE TABLE IF NOT EXISTS `gabinet`.`jednostka` (
142  `id_table1` INT NOT NULL AUTO_INCREMENT,
143  `nazwa_jednostka` VARCHAR(45) NOT NULL,
144  PRIMARY KEY (`id_table1`))
145ENGINE = InnoDB;
146
147
148-- -----------------------------------------------------
149-- Table `gabinet`.`material`
150-- -----------------------------------------------------
151CREATE TABLE IF NOT EXISTS `gabinet`.`material` (
152  `id_material` INT NOT NULL AUTO_INCREMENT,
153  `nazwa_material` VARCHAR(45) NOT NULL,
154  `cena_material` VARCHAR(45) NOT NULL,
155  `jednostka_id_table1` INT NOT NULL,
156  PRIMARY KEY (`id_material`, `jednostka_id_table1`),
157  INDEX `fk_material_jednostka1_idx` (`jednostka_id_table1` ASC),
158  CONSTRAINT `fk_material_jednostka1`
159    FOREIGN KEY (`jednostka_id_table1`)
160    REFERENCES `gabinet`.`jednostka` (`id_table1`)
161    ON DELETE NO ACTION
162    ON UPDATE NO ACTION)
163ENGINE = InnoDB;
164
165
166-- -----------------------------------------------------
167-- Table `gabinet`.`material_has_wizyta`
168-- -----------------------------------------------------
169CREATE TABLE IF NOT EXISTS `gabinet`.`material_has_wizyta` (
170  `material_id_material` INT NOT NULL,
171  `wizyta_id_wizyta` INT NOT NULL,
172  `ilość` VARCHAR(45) NOT NULL,
173  PRIMARY KEY (`material_id_material`, `wizyta_id_wizyta`),
174  INDEX `fk_material_has_wizyta_wizyta1_idx` (`wizyta_id_wizyta` ASC),
175  INDEX `fk_material_has_wizyta_material1_idx` (`material_id_material` ASC),
176  CONSTRAINT `fk_material_has_wizyta_material1`
177    FOREIGN KEY (`material_id_material`)
178    REFERENCES `gabinet`.`material` (`id_material`)
179    ON DELETE NO ACTION
180    ON UPDATE NO ACTION,
181  CONSTRAINT `fk_material_has_wizyta_wizyta1`
182    FOREIGN KEY (`wizyta_id_wizyta`)
183    REFERENCES `gabinet`.`wizyta` (`id_wizyta`)
184    ON DELETE NO ACTION
185    ON UPDATE NO ACTION)
186ENGINE = InnoDB;
187
188
189SET SQL_MODE=@OLD_SQL_MODE;
190SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
191SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;