· 6 years ago · Jan 18, 2020, 06:02 PM
1-- MySQL Script generated by MySQL Workbench
2-- Sat Jan 18 18:59:10 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 mydb
12-- -----------------------------------------------------
13
14-- -----------------------------------------------------
15-- Schema mydb
16-- -----------------------------------------------------
17CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 ;
18USE `mydb` ;
19
20-- -----------------------------------------------------
21-- Table `mydb`.`User`
22-- -----------------------------------------------------
23CREATE TABLE IF NOT EXISTS `mydb`.`User` (
24 `idUser` INT NOT NULL AUTO_INCREMENT,
25 `username` VARCHAR(100) NOT NULL,
26 `password` VARCHAR(100) NOT NULL,
27 `email` VARCHAR(100) NOT NULL,
28 `firstName` VARCHAR(100) NOT NULL,
29 `lastName` VARCHAR(100) NOT NULL,
30 `phoneNumber` VARCHAR(45) NULL,
31 `country` VARCHAR(100) NULL,
32 `city` VARCHAR(100) NULL,
33 `imageLink` VARCHAR(250) NULL,
34 PRIMARY KEY (`idUser`),
35 UNIQUE INDEX `idUser_UNIQUE` (`idUser` ASC) VISIBLE,
36 UNIQUE INDEX `username_UNIQUE` (`username` ASC) VISIBLE,
37 UNIQUE INDEX `email_UNIQUE` (`email` ASC) VISIBLE)
38ENGINE = InnoDB;
39
40
41-- -----------------------------------------------------
42-- Table `mydb`.`Category`
43-- -----------------------------------------------------
44CREATE TABLE IF NOT EXISTS `mydb`.`Category` (
45 `idCategory` INT NOT NULL AUTO_INCREMENT,
46 `name` VARCHAR(100) NOT NULL,
47 PRIMARY KEY (`idCategory`),
48 UNIQUE INDEX `idCategory_UNIQUE` (`idCategory` ASC) VISIBLE)
49ENGINE = InnoDB;
50
51
52-- -----------------------------------------------------
53-- Table `mydb`.`Item`
54-- -----------------------------------------------------
55CREATE TABLE IF NOT EXISTS `mydb`.`Item` (
56 `idItem` INT NOT NULL AUTO_INCREMENT,
57 `name` VARCHAR(100) NOT NULL,
58 `description` VARCHAR(250) NULL,
59 `User_idUser` INT NOT NULL,
60 `Category_idCategory` INT NOT NULL,
61 PRIMARY KEY (`idItem`),
62 UNIQUE INDEX `idItem_UNIQUE` (`idItem` ASC) VISIBLE,
63 INDEX `fk_Item_User_idx` (`User_idUser` ASC) VISIBLE,
64 INDEX `fk_Item_Category1_idx` (`Category_idCategory` ASC) VISIBLE,
65 CONSTRAINT `fk_Item_User`
66 FOREIGN KEY (`User_idUser`)
67 REFERENCES `mydb`.`User` (`idUser`)
68 ON DELETE NO ACTION
69 ON UPDATE NO ACTION,
70 CONSTRAINT `fk_Item_Category1`
71 FOREIGN KEY (`Category_idCategory`)
72 REFERENCES `mydb`.`Category` (`idCategory`)
73 ON DELETE NO ACTION
74 ON UPDATE NO ACTION)
75ENGINE = InnoDB;
76
77
78-- -----------------------------------------------------
79-- Table `mydb`.`Image`
80-- -----------------------------------------------------
81CREATE TABLE IF NOT EXISTS `mydb`.`Image` (
82 `idImage` INT NOT NULL AUTO_INCREMENT,
83 `imageLink` VARCHAR(250) NOT NULL,
84 `Item_idItem` INT NOT NULL,
85 PRIMARY KEY (`idImage`),
86 UNIQUE INDEX `idImage_UNIQUE` (`idImage` ASC) VISIBLE,
87 INDEX `fk_Image_Item1_idx` (`Item_idItem` ASC) VISIBLE,
88 CONSTRAINT `fk_Image_Item1`
89 FOREIGN KEY (`Item_idItem`)
90 REFERENCES `mydb`.`Item` (`idItem`)
91 ON DELETE NO ACTION
92 ON UPDATE NO ACTION)
93ENGINE = InnoDB;
94
95
96-- -----------------------------------------------------
97-- Table `mydb`.`Bid`
98-- -----------------------------------------------------
99CREATE TABLE IF NOT EXISTS `mydb`.`Bid` (
100 `idBid` INT NOT NULL AUTO_INCREMENT,
101 `startingPrice` INT NOT NULL,
102 `currentPrice` INT NULL,
103 `startingDate` DATETIME NOT NULL,
104 `endDate` DATETIME NULL,
105 `idUserMAXBidder` INT NULL,
106 `Item_idItem` INT NOT NULL,
107 PRIMARY KEY (`idBid`),
108 UNIQUE INDEX `idBid_UNIQUE` (`idBid` ASC) VISIBLE,
109 INDEX `fk_Bid_User1_idx` (`idUserMAXBidder` ASC) VISIBLE,
110 INDEX `fk_Bid_Item1_idx` (`Item_idItem` ASC) VISIBLE,
111 CONSTRAINT `fk_Bid_User1`
112 FOREIGN KEY (`idUserMAXBidder`)
113 REFERENCES `mydb`.`User` (`idUser`)
114 ON DELETE NO ACTION
115 ON UPDATE NO ACTION,
116 CONSTRAINT `fk_Bid_Item1`
117 FOREIGN KEY (`Item_idItem`)
118 REFERENCES `mydb`.`Item` (`idItem`)
119 ON DELETE NO ACTION
120 ON UPDATE NO ACTION)
121ENGINE = InnoDB;
122
123
124-- -----------------------------------------------------
125-- Table `mydb`.`Message`
126-- -----------------------------------------------------
127CREATE TABLE IF NOT EXISTS `mydb`.`Message` (
128 `idMessage` INT NOT NULL AUTO_INCREMENT,
129 `content` VARCHAR(500) NOT NULL,
130 `idUserSender` INT NOT NULL,
131 `idUserReceiver` INT NOT NULL,
132 PRIMARY KEY (`idMessage`),
133 UNIQUE INDEX `idMessage_UNIQUE` (`idMessage` ASC) VISIBLE,
134 INDEX `fk_Message_User1_idx` (`idUserSender` ASC) VISIBLE,
135 INDEX `fk_Message_User2_idx` (`idUserReceiver` ASC) VISIBLE,
136 CONSTRAINT `fk_Message_User1`
137 FOREIGN KEY (`idUserSender`)
138 REFERENCES `mydb`.`User` (`idUser`)
139 ON DELETE NO ACTION
140 ON UPDATE NO ACTION,
141 CONSTRAINT `fk_Message_User2`
142 FOREIGN KEY (`idUserReceiver`)
143 REFERENCES `mydb`.`User` (`idUser`)
144 ON DELETE NO ACTION
145 ON UPDATE NO ACTION)
146ENGINE = InnoDB;
147
148
149-- -----------------------------------------------------
150-- Table `mydb`.`Rating`
151-- -----------------------------------------------------
152CREATE TABLE IF NOT EXISTS `mydb`.`Rating` (
153 `idRating` INT NOT NULL AUTO_INCREMENT,
154 `rating` INT NOT NULL,
155 `idUserSender` INT NOT NULL,
156 `idUserReceiver` INT NOT NULL,
157 UNIQUE INDEX `idRating_UNIQUE` (`idRating` ASC) VISIBLE,
158 PRIMARY KEY (`idRating`),
159 INDEX `fk_Rating_User1_idx` (`idUserSender` ASC) VISIBLE,
160 INDEX `fk_Rating_User2_idx` (`idUserReceiver` ASC) VISIBLE,
161 CONSTRAINT `fk_Rating_User1`
162 FOREIGN KEY (`idUserSender`)
163 REFERENCES `mydb`.`User` (`idUser`)
164 ON DELETE NO ACTION
165 ON UPDATE NO ACTION,
166 CONSTRAINT `fk_Rating_User2`
167 FOREIGN KEY (`idUserReceiver`)
168 REFERENCES `mydb`.`User` (`idUser`)
169 ON DELETE NO ACTION
170 ON UPDATE NO ACTION)
171ENGINE = InnoDB;
172
173
174SET SQL_MODE=@OLD_SQL_MODE;
175SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
176SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;