· 10 years ago · Sep 23, 2016, 04:12 AM
1SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
2SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
3SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';
4
5DROP SCHEMA IF EXISTS `Real_Ink` ;
6CREATE SCHEMA IF NOT EXISTS `Real_Ink` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ;
7USE `Real_Ink` ;
8
9-- -----------------------------------------------------
10-- Table `Real_Ink`.`User`
11-- -----------------------------------------------------
12DROP TABLE IF EXISTS `Real_Ink`.`User` ;
13
14CREATE TABLE IF NOT EXISTS `Real_Ink`.`User` (
15 `UserId` INT UNSIGNED NOT NULL ,
16 `Name` VARCHAR(100) NULL ,
17 `CPF` VARCHAR(11) NULL ,
18 `Email` VARCHAR(45) NULL ,
19 `Username` VARCHAR(45) NULL ,
20 `PhoneA` VARCHAR(45) NULL ,
21 `PhoneB` VARCHAR(45) NULL ,
22 `Password` VARCHAR(64) NULL ,
23 `Salt` VARCHAR(45) NULL ,
24 `BirthDate` DATE NULL ,
25 `Address` VARCHAR(256) NULL ,
26 `PostalCode` VARCHAR(45) NULL ,
27 `Gender` VARCHAR(1) NULL ,
28 `Studio_StudioId` INT UNSIGNED NOT NULL ,
29 PRIMARY KEY (`UserId`, `Studio_StudioId`) )
30ENGINE = InnoDB;
31
32
33-- -----------------------------------------------------
34-- Table `Real_Ink`.`Tattoo`
35-- -----------------------------------------------------
36DROP TABLE IF EXISTS `Real_Ink`.`Tattoo` ;
37
38CREATE TABLE IF NOT EXISTS `Real_Ink`.`Tattoo` (
39 `TattooId` INT UNSIGNED NOT NULL ,
40 `Title` VARCHAR(45) NULL ,
41 `Description` TEXT NULL ,
42 `UserId_FK` INT NOT NULL ,
43 PRIMARY KEY (`TattooId`, `UserId_FK`) ,
44 INDEX `fk_Tattoo_User_idx` (`UserId_FK` ASC) ,
45 CONSTRAINT `fk_Tattoo_User`
46 FOREIGN KEY (`UserId_FK` )
47 REFERENCES `Real_Ink`.`User` (`UserId` )
48 ON DELETE NO ACTION
49 ON UPDATE NO ACTION)
50ENGINE = InnoDB;
51
52
53-- -----------------------------------------------------
54-- Table `Real_Ink`.`Photo`
55-- -----------------------------------------------------
56DROP TABLE IF EXISTS `Real_Ink`.`Photo` ;
57
58CREATE TABLE IF NOT EXISTS `Real_Ink`.`Photo` (
59 `PhotoId` INT UNSIGNED NOT NULL ,
60 `UploadDate` DATETIME NULL ,
61 `TattooId_FK` INT NOT NULL ,
62 `UserId_FK` INT NOT NULL ,
63 PRIMARY KEY (`PhotoId`, `TattooId_FK`, `UserId_FK`) ,
64 INDEX `fk_Photo_Tattoo1_idx` (`TattooId_FK` ASC, `UserId_FK` ASC) ,
65 CONSTRAINT `fk_Photo_Tattoo1`
66 FOREIGN KEY (`TattooId_FK` , `UserId_FK` )
67 REFERENCES `Real_Ink`.`Tattoo` (`TattooId` , `UserId_FK` )
68 ON DELETE NO ACTION
69 ON UPDATE NO ACTION)
70ENGINE = InnoDB;
71
72
73-- -----------------------------------------------------
74-- Table `Real_Ink`.`Category`
75-- -----------------------------------------------------
76DROP TABLE IF EXISTS `Real_Ink`.`Category` ;
77
78CREATE TABLE IF NOT EXISTS `Real_Ink`.`Category` (
79 `CategoryId` INT UNSIGNED NOT NULL ,
80 `Name` VARCHAR(100) NULL ,
81 PRIMARY KEY (`CategoryId`) )
82ENGINE = InnoDB;
83
84
85-- -----------------------------------------------------
86-- Table `Real_Ink`.`Category_has_Tattoo`
87-- -----------------------------------------------------
88DROP TABLE IF EXISTS `Real_Ink`.`Category_has_Tattoo` ;
89
90CREATE TABLE IF NOT EXISTS `Real_Ink`.`Category_has_Tattoo` (
91 `Category_CategoryId` INT NOT NULL ,
92 `Tattoo_TattooId` INT NOT NULL ,
93 `Tattoo_UserId_FK` INT NOT NULL ,
94 PRIMARY KEY (`Category_CategoryId`, `Tattoo_TattooId`, `Tattoo_UserId_FK`) ,
95 INDEX `fk_Category_has_Tattoo_Tattoo1_idx` (`Tattoo_TattooId` ASC, `Tattoo_UserId_FK` ASC) ,
96 INDEX `fk_Category_has_Tattoo_Category1_idx` (`Category_CategoryId` ASC) ,
97 CONSTRAINT `fk_Category_has_Tattoo_Category1`
98 FOREIGN KEY (`Category_CategoryId` )
99 REFERENCES `Real_Ink`.`Category` (`CategoryId` )
100 ON DELETE NO ACTION
101 ON UPDATE NO ACTION,
102 CONSTRAINT `fk_Category_has_Tattoo_Tattoo1`
103 FOREIGN KEY (`Tattoo_TattooId` , `Tattoo_UserId_FK` )
104 REFERENCES `Real_Ink`.`Tattoo` (`TattooId` , `UserId_FK` )
105 ON DELETE NO ACTION
106 ON UPDATE NO ACTION)
107ENGINE = InnoDB;
108
109
110-- -----------------------------------------------------
111-- Table `Real_Ink`.`Quotation`
112-- -----------------------------------------------------
113DROP TABLE IF EXISTS `Real_Ink`.`Quotation` ;
114
115CREATE TABLE IF NOT EXISTS `Real_Ink`.`Quotation` (
116 `QuotationId` INT UNSIGNED NOT NULL ,
117 `Status` VARCHAR(45) NULL ,
118 `Description` VARCHAR(45) NULL ,
119 `Price` VARCHAR(45) NULL ,
120 `Date` DATETIME NULL ,
121 PRIMARY KEY (`QuotationId`) )
122ENGINE = InnoDB;
123
124
125-- -----------------------------------------------------
126-- Table `Real_Ink`.`Tattoo_has_Quotation`
127-- -----------------------------------------------------
128DROP TABLE IF EXISTS `Real_Ink`.`Tattoo_has_Quotation` ;
129
130CREATE TABLE IF NOT EXISTS `Real_Ink`.`Tattoo_has_Quotation` (
131 `TattooId` INT UNSIGNED NOT NULL ,
132 `UserId_FK` INT NOT NULL ,
133 `QuotationId` INT UNSIGNED NOT NULL ,
134 `Comment` VARCHAR(300) NULL ,
135 PRIMARY KEY (`TattooId`, `UserId_FK`, `QuotationId`) ,
136 INDEX `fk_Tattoo_has_Quotation_Quotation1_idx` (`QuotationId` ASC) ,
137 INDEX `fk_Tattoo_has_Quotation_Tattoo1_idx` (`TattooId` ASC, `UserId_FK` ASC) ,
138 CONSTRAINT `fk_Tattoo_has_Quotation_Tattoo1`
139 FOREIGN KEY (`TattooId` , `UserId_FK` )
140 REFERENCES `Real_Ink`.`Tattoo` (`TattooId` , `UserId_FK` )
141 ON DELETE NO ACTION
142 ON UPDATE NO ACTION,
143 CONSTRAINT `fk_Tattoo_has_Quotation_Quotation1`
144 FOREIGN KEY (`QuotationId` )
145 REFERENCES `Real_Ink`.`Quotation` (`QuotationId` )
146 ON DELETE NO ACTION
147 ON UPDATE NO ACTION)
148ENGINE = InnoDB;
149
150
151-- -----------------------------------------------------
152-- Table `Real_Ink`.`Studio`
153-- -----------------------------------------------------
154DROP TABLE IF EXISTS `Real_Ink`.`Studio` ;
155
156CREATE TABLE IF NOT EXISTS `Real_Ink`.`Studio` (
157 `StudioId` INT UNSIGNED NULL ,
158 `Name` VARCHAR(120) NULL ,
159 `Address` VARCHAR(120) NULL ,
160 `City` VARCHAR(45) NULL ,
161 `PostalCode` VARCHAR(45) NULL ,
162 `Phone1` VARCHAR(45) NULL ,
163 `Phone2` VARCHAR(45) NULL ,
164 `CelPhone` VARCHAR(45) NULL ,
165 `Email` VARCHAR(145) NULL ,
166 `Latitude` VARCHAR(45) NULL ,
167 `Longetude` VARCHAR(45) NULL ,
168 PRIMARY KEY (`StudioId`) )
169ENGINE = InnoDB;
170
171
172-- -----------------------------------------------------
173-- Table `Real_Ink`.`User_has_Studio`
174-- -----------------------------------------------------
175DROP TABLE IF EXISTS `Real_Ink`.`User_has_Studio` ;
176
177CREATE TABLE IF NOT EXISTS `Real_Ink`.`User_has_Studio` (
178 `User_UserId` INT UNSIGNED NOT NULL ,
179 `User_Studio_StudioId` INT UNSIGNED NOT NULL ,
180 `Studio_StudioId` INT UNSIGNED NOT NULL ,
181 `Moderetor` BIT(1) NULL ,
182 PRIMARY KEY (`User_UserId`, `User_Studio_StudioId`, `Studio_StudioId`) ,
183 INDEX `fk_User_has_Studio_Studio1_idx` (`Studio_StudioId` ASC) ,
184 INDEX `fk_User_has_Studio_User1_idx` (`User_UserId` ASC, `User_Studio_StudioId` ASC) ,
185 CONSTRAINT `fk_User_has_Studio_User1`
186 FOREIGN KEY (`User_UserId` , `User_Studio_StudioId` )
187 REFERENCES `Real_Ink`.`User` (`UserId` , `Studio_StudioId` )
188 ON DELETE NO ACTION
189 ON UPDATE NO ACTION,
190 CONSTRAINT `fk_User_has_Studio_Studio1`
191 FOREIGN KEY (`Studio_StudioId` )
192 REFERENCES `Real_Ink`.`Studio` (`StudioId` )
193 ON DELETE NO ACTION
194 ON UPDATE NO ACTION)
195ENGINE = InnoDB;
196
197USE `Real_Ink` ;
198
199
200SET SQL_MODE=@OLD_SQL_MODE;
201SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
202SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;