· 5 years ago · Jun 02, 2020, 12:34 PM
1-- MySQL Script generated by MySQL Workbench
2-- Tue Jun 2 13:36:05 2020
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='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
9
10-- -----------------------------------------------------
11-- Schema projekt
12-- -----------------------------------------------------
13
14-- -----------------------------------------------------
15-- Schema projekt
16-- -----------------------------------------------------
17DROP database if exists `projekt`;
18CREATE SCHEMA IF NOT EXISTS `projekt` ;
19USE `projekt` ;
20
21-- -----------------------------------------------------
22-- Table `projekt`.`address`
23-- -----------------------------------------------------
24CREATE TABLE IF NOT EXISTS `projekt`.`address` (
25 `address_id` INT NOT NULL AUTO_INCREMENT,
26 `address` VARCHAR(50) NOT NULL,
27 `street` VARCHAR(50) NULL,
28 `house_number` VARCHAR(10) NOT NULL,
29 `postal_code` VARCHAR(6) NOT NULL,
30 `phone` VARCHAR(20) NOT NULL,
31 PRIMARY KEY (`address_id`))
32ENGINE = InnoDB;
33
34
35-- -----------------------------------------------------
36-- Table `projekt`.`person`
37-- -----------------------------------------------------
38CREATE TABLE IF NOT EXISTS `projekt`.`person` (
39 `person_id` INT NOT NULL AUTO_INCREMENT,
40 `first_neme` VARCHAR(45) NOT NULL,
41 `last_name` VARCHAR(45) NOT NULL,
42 `password` VARCHAR(45) NOT NULL,
43 `email` VARCHAR(50) NOT NULL,
44 `place_id` INT NULL,
45 `address_id` INT NOT NULL,
46 PRIMARY KEY (`person_id`)
47 /*INDEX `fk_staff_address_idx` (`address_id` ASC) VISIBLE
48 CONSTRAINT `fk_staff_address`
49 FOREIGN KEY (`address_id`)
50 REFERENCES `projekt`.`address` (`address_id`)
51 ON DELETE NO ACTION
52 ON UPDATE NO ACTION)
53*/)ENGINE = InnoDB;
54
55
56
57-- -----------------------------------------------------
58-- Table `projekt`.`mail`
59-- -----------------------------------------------------
60CREATE TABLE IF NOT EXISTS `projekt`.`mail` (
61 `mail_id` INT NOT NULL AUTO_INCREMENT,
62 `sender` VARCHAR(50) NOT NULL,
63 `reciver` VARCHAR(50) NOT NULL,
64 `content` VARCHAR(500) NOT NULL,
65 PRIMARY KEY (`mail_id`)
66 /*INDEX `fk_mail_person1_idx` (`sender` ASC) VISIBLE,
67 INDEX `fk_mail_person2_idx` (`reciver` ASC) VISIBLE,
68 CONSTRAINT `fk_mail_person1`
69 FOREIGN KEY (`sender`)
70 REFERENCES `projekt`.`person` (`person_id`)
71 ON DELETE NO ACTION
72 ON UPDATE NO ACTION,
73 CONSTRAINT `fk_mail_person2`
74 FOREIGN KEY (`reciver`)
75 REFERENCES `projekt`.`person` (`person_id`)
76 ON DELETE NO ACTION
77 ON UPDATE NO ACTION) */
78) ENGINE = InnoDB;
79
80
81-- -----------------------------------------------------
82-- Table `projekt`.`places`
83-- -----------------------------------------------------
84CREATE TABLE IF NOT EXISTS `projekt`.`places` (
85 `place_id` INT NOT NULL AUTO_INCREMENT,
86 `name` VARCHAR(45) NOT NULL,
87 `addres_id` INT NOT NULL,
88 `rooms` INT NOT NULL,
89 PRIMARY KEY (`place_id`)
90 /*INDEX `fk_places_address1_idx` (`addres_id` ASC) VISIBLE,
91 CONSTRAINT `fk_places_address1`
92 FOREIGN KEY (`addres_id`)
93 REFERENCES `projekt`.`address` (`address_id`)
94 ON DELETE NO ACTION
95 ON UPDATE NO ACTION)*/
96)ENGINE = InnoDB;
97
98
99-- -----------------------------------------------------
100-- Table `projekt`.`services`
101-- -----------------------------------------------------
102CREATE TABLE IF NOT EXISTS `projekt`.`services` (
103 `services_id` INT NOT NULL AUTO_INCREMENT,
104 `name` VARCHAR(150) NOT NULL,
105 `description` VARCHAR(500) NOT NULL,
106 `time` INT NOT NULL,
107 `time_type` VARCHAR(45) NOT NULL,
108 `prices` DECIMAL NOT NULL,
109 `place_id` INT NOT NULL,
110 PRIMARY KEY (`services_id`)
111 /*UNIQUE INDEX `name_UNIQUE` (`name` ASC) VISIBLE,
112 INDEX `fk_services_places1_idx` (`place_id` ASC) VISIBLE,
113 CONSTRAINT `fk_services_places1`
114 FOREIGN KEY (`place_id`)
115 REFERENCES `projekt`.`places` (`place_id`)
116 ON DELETE NO ACTION
117 ON UPDATE NO ACTION)*/
118)ENGINE = InnoDB;
119
120
121-- -----------------------------------------------------
122-- Table `projekt`.`orders`
123-- -----------------------------------------------------
124CREATE TABLE IF NOT EXISTS `projekt`.`orders` (
125 `orders_id` INT NOT NULL AUTO_INCREMENT,
126 `customer_id` INT NOT NULL,
127 `services_id` INT NOT NULL,
128 `staff_id` INT NOT NULL,
129 `animal_id` INT NOT NULL,
130 `date_start` DATETIME NOT NULL,
131 `date_end` DATETIME NOT NULL,
132 `complete` TINYINT NOT NULL,
133 `note` VARCHAR(250) NULL,
134 `payd` TINYINT NULL,
135 PRIMARY KEY (`orders_id`)
136/* INDEX `fk_orders_person1_idx` (`customer_id` ASC) VISIBLE,
137 INDEX `fk_orders_person2_idx` (`staff_id` ASC) VISIBLE,
138 INDEX `fk_orders_services1_idx` (`services_id` ASC) VISIBLE,
139 CONSTRAINT `fk_orders_person1`
140 FOREIGN KEY (`customer_id`)
141 REFERENCES `projekt`.`person` (`person_id`)
142 ON DELETE NO ACTION
143 ON UPDATE NO ACTION,
144 CONSTRAINT `fk_orders_person2`
145 FOREIGN KEY (`staff_id`)
146 REFERENCES `projekt`.`person` (`person_id`)
147 ON DELETE NO ACTION
148 ON UPDATE NO ACTION,
149 CONSTRAINT `fk_orders_services1`
150 FOREIGN KEY (`services_id`)
151 REFERENCES `projekt`.`services` (`services_id`)
152 ON DELETE NO ACTION
153 ON UPDATE NO ACTION)*/
154)ENGINE = InnoDB;
155
156
157-- -----------------------------------------------------
158-- Table `projekt`.`gatunek`
159-- -----------------------------------------------------
160CREATE TABLE IF NOT EXISTS `projekt`.`gatunek` (
161 `gatunek_id` INT NOT NULL AUTO_INCREMENT,
162 `name` VARCHAR(45) NOT NULL,
163 PRIMARY KEY (`gatunek_id`))
164ENGINE = InnoDB;
165
166
167-- -----------------------------------------------------
168-- Table `projekt`.`rasa`
169-- -----------------------------------------------------
170CREATE TABLE IF NOT EXISTS `projekt`.`rasa` (
171 `rasa_id` INT NOT NULL AUTO_INCREMENT,
172 `name` VARCHAR(45) NOT NULL,
173 `gatunek_id` INT NOT NULL,
174 PRIMARY KEY (`rasa_id`)
175 /* INDEX `fk_rasa_gatunek1_idx` (`gatunek_id` ASC) VISIBLE,
176 CONSTRAINT `fk_rasa_gatunek1`
177 FOREIGN KEY (`gatunek_id`)
178 REFERENCES `projekt`.`gatunek` (`gatunek_id`)
179 ON DELETE NO ACTION
180 ON UPDATE NO ACTION)*/
181) ENGINE = InnoDB;
182
183
184-- -----------------------------------------------------
185-- Table `projekt`.`animals`
186-- -----------------------------------------------------
187CREATE TABLE IF NOT EXISTS `projekt`.`animals` (
188 `animal_id` INT NOT NULL AUTO_INCREMENT,
189 `person_id` INT NOT NULL,
190 `place_id` INT NOT NULL,
191 `status` TINYINT NULL,
192 `rasa_id` INT NOT NULL,
193 PRIMARY KEY (`animal_id`)
194 /* INDEX `fk_animals_rasa1_idx` (`rasa_id` ASC) VISIBLE,
195 INDEX `fk_animals_places1_idx` (`place_id` ASC) VISIBLE,
196 INDEX `fk_animals_person1_idx` (`person_id` ASC) VISIBLE,
197 CONSTRAINT `fk_animals_rasa1`
198 FOREIGN KEY (`rasa_id`)
199 REFERENCES `projekt`.`rasa` (`rasa_id`)
200 ON DELETE NO ACTION
201 ON UPDATE NO ACTION,
202 CONSTRAINT `fk_animals_places1`
203 FOREIGN KEY (`place_id`)
204 REFERENCES `projekt`.`places` (`place_id`)
205 ON DELETE NO ACTION
206 ON UPDATE NO ACTION,
207 CONSTRAINT `fk_animals_person1`
208 FOREIGN KEY (`person_id`)
209 REFERENCES `projekt`.`person` (`person_id`)
210 ON DELETE NO ACTION
211 ON UPDATE NO ACTION)*/
212 )ENGINE = InnoDB;
213
214
215SET SQL_MODE=@OLD_SQL_MODE;
216SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
217SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;