L0uLCHgc

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='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
4
5-- -----------------------------------------------------
6-- Schema mydb
7-- -----------------------------------------------------
8-- -----------------------------------------------------
9-- Schema test1339
10-- -----------------------------------------------------
11
12-- -----------------------------------------------------
13-- Schema test1339
14-- -----------------------------------------------------
15CREATE SCHEMA IF NOT EXISTS `test1339` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci ;
16-- -----------------------------------------------------
17-- Schema test1337
18-- -----------------------------------------------------
19
20-- -----------------------------------------------------
21-- Schema test1337
22-- -----------------------------------------------------
23CREATE SCHEMA IF NOT EXISTS `test1337` ;
24USE `test1339` ;
25
26-- -----------------------------------------------------
27-- Table `test1339`.`categories`
28-- -----------------------------------------------------
29CREATE TABLE IF NOT EXISTS `test1339`.`categories` (
30  `category_id` INT(11) NOT NULL AUTO_INCREMENT,
31  `category_name` VARCHAR(100) NOT NULL,
32  `parent_id` INT(11) NULL DEFAULT NULL,
33  PRIMARY KEY (`category_id`))
34ENGINE = InnoDB
35DEFAULT CHARACTER SET = utf8mb4
36COLLATE = utf8mb4_0900_ai_ci;
37
38
39-- -----------------------------------------------------
40-- Table `test1339`.`products`
41-- -----------------------------------------------------
42CREATE TABLE IF NOT EXISTS `test1339`.`products` (
43  `product_id` INT(11) NOT NULL AUTO_INCREMENT,
44  `product_name` VARCHAR(255) NOT NULL,
45  `price` FLOAT NOT NULL,
46  `quantity` INT(11) NOT NULL,
47  `quantity_on_order` INT(11) NULL DEFAULT '0',
48  `category_id` INT(11) NOT NULL DEFAULT '1',
49  `discounted` INT(11) NULL DEFAULT '0',
50  `characteristics` VARCHAR(1000) NULL DEFAULT NULL,
51  `product_image` VARCHAR(500) NULL DEFAULT NULL,
52  PRIMARY KEY (`product_id`),
53  INDEX `categoryid_idx` (`category_id` ASC) VISIBLE,
54  CONSTRAINT `FK_product_category`
55    FOREIGN KEY (`category_id`)
56    REFERENCES `test1339`.`categories` (`category_id`)
57    ON UPDATE CASCADE)
58ENGINE = InnoDB
59DEFAULT CHARACTER SET = utf8mb4
60COLLATE = utf8mb4_0900_ai_ci;
61
62
63-- -----------------------------------------------------
64-- Table `test1339`.`comments`
65-- -----------------------------------------------------
66CREATE TABLE IF NOT EXISTS `test1339`.`comments` (
67  `id` INT(11) NOT NULL AUTO_INCREMENT,
68  `feedback` VARCHAR(1000) NULL DEFAULT NULL,
69  `product_id` INT(11) NOT NULL,
70  PRIMARY KEY (`id`),
71  INDEX `technomarket_updated` (`product_id` ASC) VISIBLE,
72  CONSTRAINT `FK_comment_product`
73    FOREIGN KEY (`product_id`)
74    REFERENCES `test1339`.`products` (`product_id`))
75ENGINE = InnoDB
76DEFAULT CHARACTER SET = utf8mb4
77COLLATE = utf8mb4_0900_ai_ci;
78
79
80-- -----------------------------------------------------
81-- Table `test1339`.`users`
82-- -----------------------------------------------------
83CREATE TABLE IF NOT EXISTS `test1339`.`users` (
84  `user_id` INT(11) NOT NULL AUTO_INCREMENT,
85  `email` VARCHAR(100) NOT NULL,
86  `password` VARCHAR(60) NOT NULL,
87  `first_name` VARCHAR(50) NOT NULL,
88  `last_name` VARCHAR(50) NOT NULL,
89  `user_role_id` INT(11) NULL DEFAULT '1',
90  `gender` VARCHAR(55) NOT NULL,
91  `verified` TINYINT(11) NULL DEFAULT '0',
92  `image` VARCHAR(500) NULL DEFAULT NULL,
93  PRIMARY KEY (`user_id`),
94  UNIQUE INDEX `email` (`email` ASC) VISIBLE,
95  UNIQUE INDEX `user_id` (`user_id` ASC) VISIBLE,
96  INDEX `userroleid` (`user_role_id` ASC) VISIBLE)
97ENGINE = InnoDB
98DEFAULT CHARACTER SET = utf8mb4
99COLLATE = utf8mb4_0900_ai_ci;
100
101
102-- -----------------------------------------------------
103-- Table `test1339`.`orders`
104-- -----------------------------------------------------
105CREATE TABLE IF NOT EXISTS `test1339`.`orders` (
106  `order_id` INT(11) NOT NULL AUTO_INCREMENT,
107  `product_id` INT(11) NOT NULL,
108  `user_id` INT(11) NOT NULL,
109  `product_quantity` TINYINT(4) NOT NULL,
110  `status` VARCHAR(50) NULL DEFAULT NULL,
111  `required_date` DATE NULL DEFAULT NULL,
112  `shipped_date` DATE NULL DEFAULT NULL,
113  PRIMARY KEY (`order_id`),
114  INDEX `productid_idx` (`product_id` ASC) VISIBLE,
115  INDEX `userid_idx` (`user_id` ASC) VISIBLE,
116  CONSTRAINT `FK_order_product`
117    FOREIGN KEY (`product_id`)
118    REFERENCES `test1339`.`products` (`product_id`),
119  CONSTRAINT `FK_order_user`
120    FOREIGN KEY (`user_id`)
121    REFERENCES `test1339`.`users` (`user_id`))
122ENGINE = InnoDB
123DEFAULT CHARACTER SET = utf8mb4
124COLLATE = utf8mb4_0900_ai_ci;
125
126
127-- -----------------------------------------------------
128-- Table `test1339`.`store_locations`
129-- -----------------------------------------------------
130CREATE TABLE IF NOT EXISTS `test1339`.`store_locations` (
131  `store_id` INT(11) NOT NULL AUTO_INCREMENT,
132  `store_name` VARCHAR(100) NOT NULL,
133  `city` VARCHAR(100) NOT NULL,
134  `street` VARCHAR(200) NOT NULL,
135  `phone` VARCHAR(15) NOT NULL,
136  `e-mail` VARCHAR(100) NOT NULL,
137  `work_time` VARCHAR(500) NOT NULL,
138  PRIMARY KEY (`store_id`))
139ENGINE = InnoDB
140DEFAULT CHARACTER SET = utf8mb4
141COLLATE = utf8mb4_0900_ai_ci;
142
143
144-- -----------------------------------------------------
145-- Table `test1339`.`user_addresses`
146-- -----------------------------------------------------
147CREATE TABLE IF NOT EXISTS `test1339`.`user_addresses` (
148  `order_details_id` INT(11) NOT NULL AUTO_INCREMENT,
149  `full_name` VARCHAR(150) NOT NULL,
150  `telephone` VARCHAR(20) NOT NULL,
151  `order_id` INT(11) NOT NULL,
152  `comments` VARCHAR(150) NULL DEFAULT NULL,
153  `city_name` VARCHAR(45) NOT NULL,
154  `postal_code` VARCHAR(45) NOT NULL,
155  `street_name` VARCHAR(70) NOT NULL,
156  `street_number` VARCHAR(45) NULL DEFAULT NULL,
157  `block` VARCHAR(45) NULL DEFAULT NULL,
158  `entrance` VARCHAR(45) NULL DEFAULT NULL,
159  `floor` VARCHAR(45) NULL DEFAULT NULL,
160  `apartament` VARCHAR(45) NULL DEFAULT NULL,
161  `user_id` INT(11) NULL DEFAULT NULL,
162  PRIMARY KEY (`order_details_id`),
163  INDEX `ordersid_idx` (`order_id` ASC) VISIBLE,
164  INDEX `useridaddresses_idx` (`user_id` ASC) VISIBLE,
165  CONSTRAINT `FK_user_address_order`
166    FOREIGN KEY (`order_id`)
167    REFERENCES `test1339`.`orders` (`order_id`),
168  CONSTRAINT `FK_user_address_user`
169    FOREIGN KEY (`user_id`)
170    REFERENCES `test1339`.`users` (`user_id`))
171ENGINE = InnoDB
172DEFAULT CHARACTER SET = utf8mb4
173COLLATE = utf8mb4_0900_ai_ci;
174
175
176-- -----------------------------------------------------
177-- Table `test1339`.`user_favorites`
178-- -----------------------------------------------------
179CREATE TABLE IF NOT EXISTS `test1339`.`user_favorites` (
180  `user_id` INT(11) NOT NULL,
181  `product_id` INT(11) NOT NULL,
182  PRIMARY KEY (`user_id`, `product_id`),
183  INDEX `productidfavorites_idx` (`product_id` ASC) VISIBLE,
184  INDEX `useridfavorites` (`user_id` ASC) VISIBLE,
185  CONSTRAINT `FK_user_favorites_product`
186    FOREIGN KEY (`product_id`)
187    REFERENCES `test1337`.`products` (`product_id`)
188    ON UPDATE CASCADE,
189  CONSTRAINT `FK_user_favorites_user`
190    FOREIGN KEY (`user_id`)
191    REFERENCES `test1339`.`users` (`user_id`)
192    ON UPDATE CASCADE)
193ENGINE = InnoDB
194DEFAULT CHARACTER SET = utf8mb4
195COLLATE = utf8mb4_0900_ai_ci;
196
197
198-- -----------------------------------------------------
199-- Table `test1339`.`user_roles`
200-- -----------------------------------------------------
201CREATE TABLE IF NOT EXISTS `test1339`.`user_roles` (
202  `user_role_id` INT(11) NOT NULL AUTO_INCREMENT,
203  `user_role_description` VARCHAR(100) NOT NULL,
204  PRIMARY KEY (`user_role_id`),
205  UNIQUE INDEX `user_role_id` (`user_role_id` ASC) VISIBLE)
206ENGINE = InnoDB
207DEFAULT CHARACTER SET = utf8mb4
208COLLATE = utf8mb4_0900_ai_ci;
209
210USE `test1337` ;
211
212SET SQL_MODE=@OLD_SQL_MODE;
213SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
214SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;