· 4 years ago · Aug 06, 2021, 12:20 PM
1-- MySQL Script generated by MySQL Workbench
2-- Fri Aug 6 15:12:19 2021
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 Gustos_Wine_DB
12-- -----------------------------------------------------
13
14-- -----------------------------------------------------
15-- Schema Gustos_Wine_DB
16-- -----------------------------------------------------
17CREATE SCHEMA IF NOT EXISTS `Gustos_Wine_DB` DEFAULT CHARACTER SET utf8 ;
18USE `Gustos_Wine_DB` ;
19
20-- -----------------------------------------------------
21-- Table `Gustos_Wine_DB`.`Country`
22-- -----------------------------------------------------
23CREATE TABLE IF NOT EXISTS `Gustos_Wine_DB`.`Country` (
24 `idCountry` INT NOT NULL AUTO_INCREMENT,
25 `Country` VARCHAR(45) NULL,
26 PRIMARY KEY (`idCountry`))
27ENGINE = InnoDB;
28
29
30-- -----------------------------------------------------
31-- Table `Gustos_Wine_DB`.`Region`
32-- -----------------------------------------------------
33CREATE TABLE IF NOT EXISTS `Gustos_Wine_DB`.`Region` (
34 `idRegion` INT NOT NULL AUTO_INCREMENT,
35 `Region` VARCHAR(100) NULL,
36 `Country_idCountry` INT NOT NULL,
37 PRIMARY KEY (`idRegion`),
38 INDEX `fk_Region_Country1_idx` (`Country_idCountry` ASC) VISIBLE,
39 CONSTRAINT `fk_Region_Country1`
40 FOREIGN KEY (`Country_idCountry`)
41 REFERENCES `Gustos_Wine_DB`.`Country` (`idCountry`)
42 ON DELETE NO ACTION
43 ON UPDATE NO ACTION)
44ENGINE = InnoDB;
45
46
47-- -----------------------------------------------------
48-- Table `Gustos_Wine_DB`.`Color`
49-- -----------------------------------------------------
50CREATE TABLE IF NOT EXISTS `Gustos_Wine_DB`.`Color` (
51 `idColor` INT NOT NULL AUTO_INCREMENT,
52 `Color` VARCHAR(45) NULL,
53 PRIMARY KEY (`idColor`))
54ENGINE = InnoDB;
55
56
57-- -----------------------------------------------------
58-- Table `Gustos_Wine_DB`.`Winery`
59-- -----------------------------------------------------
60CREATE TABLE IF NOT EXISTS `Gustos_Wine_DB`.`Winery` (
61 `idWinery` INT NOT NULL AUTO_INCREMENT,
62 `Winery` VARCHAR(100) NULL,
63 `Country_idCountry` INT NOT NULL,
64 PRIMARY KEY (`idWinery`),
65 INDEX `fk_Winery_Country1_idx` (`Country_idCountry` ASC) VISIBLE,
66 CONSTRAINT `fk_Winery_Country1`
67 FOREIGN KEY (`Country_idCountry`)
68 REFERENCES `Gustos_Wine_DB`.`Country` (`idCountry`)
69 ON DELETE NO ACTION
70 ON UPDATE NO ACTION)
71ENGINE = InnoDB;
72
73
74-- -----------------------------------------------------
75-- Table `Gustos_Wine_DB`.`Wine`
76-- -----------------------------------------------------
77CREATE TABLE IF NOT EXISTS `Gustos_Wine_DB`.`Wine` (
78 `idWine` INT NOT NULL AUTO_INCREMENT,
79 `Region_idRegion` INT NULL,
80 `Color_idColor` INT NOT NULL,
81 `Country_idCountry` INT NOT NULL,
82 `Winery_idWinery` INT NOT NULL,
83 `Wine_Name` MEDIUMTEXT NOT NULL,
84 PRIMARY KEY (`idWine`),
85 INDEX `fk_Wine_Region_idx` (`Region_idRegion` ASC) VISIBLE,
86 INDEX `fk_Wine_Color1_idx` (`Color_idColor` ASC) VISIBLE,
87 INDEX `fk_Wine_Country1_idx` (`Country_idCountry` ASC) VISIBLE,
88 INDEX `fk_Wine_Winery1_idx` (`Winery_idWinery` ASC) VISIBLE,
89 INDEX `Wine_Name_Index` (`Wine_Name` ASC) INVISIBLE,
90 CONSTRAINT `fk_Wine_Region`
91 FOREIGN KEY (`Region_idRegion`)
92 REFERENCES `Gustos_Wine_DB`.`Region` (`idRegion`)
93 ON DELETE NO ACTION
94 ON UPDATE NO ACTION,
95 CONSTRAINT `fk_Wine_Color1`
96 FOREIGN KEY (`Color_idColor`)
97 REFERENCES `Gustos_Wine_DB`.`Color` (`idColor`)
98 ON DELETE NO ACTION
99 ON UPDATE NO ACTION,
100 CONSTRAINT `fk_Wine_Country1`
101 FOREIGN KEY (`Country_idCountry`)
102 REFERENCES `Gustos_Wine_DB`.`Country` (`idCountry`)
103 ON DELETE NO ACTION
104 ON UPDATE NO ACTION,
105 CONSTRAINT `fk_Wine_Winery1`
106 FOREIGN KEY (`Winery_idWinery`)
107 REFERENCES `Gustos_Wine_DB`.`Winery` (`idWinery`)
108 ON DELETE NO ACTION
109 ON UPDATE NO ACTION)
110ENGINE = InnoDB;
111
112
113-- -----------------------------------------------------
114-- Table `Gustos_Wine_DB`.`Concurs`
115-- -----------------------------------------------------
116CREATE TABLE IF NOT EXISTS `Gustos_Wine_DB`.`Concurs` (
117 `idConcurs` INT NOT NULL AUTO_INCREMENT,
118 `Name_Concurs` VARCHAR(45) NULL,
119 `Description` VARCHAR(45) NULL,
120 PRIMARY KEY (`idConcurs`))
121ENGINE = InnoDB;
122
123
124-- -----------------------------------------------------
125-- Table `Gustos_Wine_DB`.`Contest`
126-- -----------------------------------------------------
127CREATE TABLE IF NOT EXISTS `Gustos_Wine_DB`.`Contest` (
128 `idContest` INT NOT NULL AUTO_INCREMENT,
129 `Country` VARCHAR(45) NULL,
130 `Year` VARCHAR(45) NULL,
131 `Concurs_idConcurs` INT NOT NULL,
132 `Country_idCountry` INT NOT NULL,
133 `Contestcol` VARCHAR(45) NULL,
134 PRIMARY KEY (`idContest`),
135 INDEX `fk_Contest_Concurs1_idx` (`Concurs_idConcurs` ASC) VISIBLE,
136 INDEX `fk_Contest_Country1_idx` (`Country_idCountry` ASC) VISIBLE,
137 INDEX `Contest_Index` () VISIBLE,
138 CONSTRAINT `fk_Contest_Concurs1`
139 FOREIGN KEY (`Concurs_idConcurs`)
140 REFERENCES `Gustos_Wine_DB`.`Concurs` (`idConcurs`)
141 ON DELETE NO ACTION
142 ON UPDATE NO ACTION,
143 CONSTRAINT `fk_Contest_Country1`
144 FOREIGN KEY (`Country_idCountry`)
145 REFERENCES `Gustos_Wine_DB`.`Country` (`idCountry`)
146 ON DELETE NO ACTION
147 ON UPDATE NO ACTION)
148ENGINE = InnoDB;
149
150
151-- -----------------------------------------------------
152-- Table `Gustos_Wine_DB`.`Medal`
153-- -----------------------------------------------------
154CREATE TABLE IF NOT EXISTS `Gustos_Wine_DB`.`Medal` (
155 `idMedal` INT NOT NULL AUTO_INCREMENT,
156 `Medal` VARCHAR(45) NULL,
157 `Contest_idContest` INT NOT NULL,
158 PRIMARY KEY (`idMedal`),
159 INDEX `fk_Medal_Contest1_idx` (`Contest_idContest` ASC) VISIBLE,
160 CONSTRAINT `fk_Medal_Contest1`
161 FOREIGN KEY (`Contest_idContest`)
162 REFERENCES `Gustos_Wine_DB`.`Contest` (`idContest`)
163 ON DELETE NO ACTION
164 ON UPDATE NO ACTION)
165ENGINE = InnoDB;
166
167
168-- -----------------------------------------------------
169-- Table `Gustos_Wine_DB`.`Wine_Image`
170-- -----------------------------------------------------
171CREATE TABLE IF NOT EXISTS `Gustos_Wine_DB`.`Wine_Image` (
172 `idWine_Image` INT NOT NULL AUTO_INCREMENT,
173 `Wine_Image` TEXT NULL,
174 PRIMARY KEY (`idWine_Image`))
175ENGINE = InnoDB;
176
177
178-- -----------------------------------------------------
179-- Table `Gustos_Wine_DB`.`Additional_Information`
180-- -----------------------------------------------------
181CREATE TABLE IF NOT EXISTS `Gustos_Wine_DB`.`Additional_Information` (
182 `idAdditional_Information` INT NOT NULL AUTO_INCREMENT,
183 `Additional_Information` JSON NULL,
184 PRIMARY KEY (`idAdditional_Information`))
185ENGINE = InnoDB;
186
187
188-- -----------------------------------------------------
189-- Table `Gustos_Wine_DB`.`Year`
190-- -----------------------------------------------------
191CREATE TABLE IF NOT EXISTS `Gustos_Wine_DB`.`Year` (
192 `idVintage` INT NOT NULL AUTO_INCREMENT,
193 `Year` INT NULL,
194 `Wine_Name_Wine_Index` INT NOT NULL,
195 `Wine_idWine` INT NOT NULL,
196 `Alcohol` VARCHAR(45) NULL,
197 `Sugar` VARCHAR(45) NULL,
198 `Wine_Image_idWine_Image` INT NOT NULL,
199 `Additional_Information_idAdditional_Information` INT NOT NULL,
200 `Price` VARCHAR(128) NULL,
201 PRIMARY KEY (`idVintage`),
202 INDEX `fk_Year_Wine1_idx` (`Wine_idWine` ASC) VISIBLE,
203 INDEX `fk_Year_Wine_Image1_idx` (`Wine_Image_idWine_Image` ASC) VISIBLE,
204 INDEX `fk_Year_Additional_Information1_idx` (`Additional_Information_idAdditional_Information` ASC) VISIBLE,
205 CONSTRAINT `fk_Year_Wine1`
206 FOREIGN KEY (`Wine_idWine`)
207 REFERENCES `Gustos_Wine_DB`.`Wine` (`idWine`)
208 ON DELETE NO ACTION
209 ON UPDATE NO ACTION,
210 CONSTRAINT `fk_Year_Wine_Image1`
211 FOREIGN KEY (`Wine_Image_idWine_Image`)
212 REFERENCES `Gustos_Wine_DB`.`Wine_Image` (`idWine_Image`)
213 ON DELETE NO ACTION
214 ON UPDATE NO ACTION,
215 CONSTRAINT `fk_Year_Additional_Information1`
216 FOREIGN KEY (`Additional_Information_idAdditional_Information`)
217 REFERENCES `Gustos_Wine_DB`.`Additional_Information` (`idAdditional_Information`)
218 ON DELETE NO ACTION
219 ON UPDATE NO ACTION)
220ENGINE = InnoDB;
221
222
223-- -----------------------------------------------------
224-- Table `Gustos_Wine_DB`.`Year_has_Contest`
225-- -----------------------------------------------------
226CREATE TABLE IF NOT EXISTS `Gustos_Wine_DB`.`Year_has_Contest` (
227 `Year_idVintage` INT NOT NULL,
228 `Medal_idMedal` INT NOT NULL,
229 INDEX `fk_Year_has_Contest_Year1_idx` (`Year_idVintage` ASC) VISIBLE,
230 INDEX `fk_Year_has_Contest_Medal1_idx` (`Medal_idMedal` ASC) VISIBLE,
231 CONSTRAINT `fk_Year_has_Contest_Year1`
232 FOREIGN KEY (`Year_idVintage`)
233 REFERENCES `Gustos_Wine_DB`.`Year` (`idVintage`)
234 ON DELETE NO ACTION
235 ON UPDATE NO ACTION,
236 CONSTRAINT `fk_Year_has_Contest_Medal1`
237 FOREIGN KEY (`Medal_idMedal`)
238 REFERENCES `Gustos_Wine_DB`.`Medal` (`idMedal`)
239 ON DELETE NO ACTION
240 ON UPDATE NO ACTION)
241ENGINE = InnoDB;
242
243
244SET SQL_MODE=@OLD_SQL_MODE;
245SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
246SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
247