VSwUmdKk

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 test1337
10-- -----------------------------------------------------
11
12-- -----------------------------------------------------
13-- Schema test1337
14-- -----------------------------------------------------
15CREATE SCHEMA IF NOT EXISTS `test1338` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci ;
16USE `test1338` ;
17
18-- -----------------------------------------------------
19-- Table `test1337`.`categories`
20-- -----------------------------------------------------
21CREATE TABLE IF NOT EXISTS `test1338`.`categories` (
22  `category_id` INT(11) NOT NULL AUTO_INCREMENT,
23  `category_name` VARCHAR(100) NOT NULL,
24  PRIMARY KEY (`category_id`))
25ENGINE = InnoDB
26DEFAULT CHARACTER SET = utf8mb4
27COLLATE = utf8mb4_0900_ai_ci;
28
29
30-- -----------------------------------------------------
31-- Table `test1337`.`a_categories`
32-- -----------------------------------------------------
33CREATE TABLE IF NOT EXISTS `test1338`.`a_categories` (
34  `a_category_id` INT(11) NOT NULL AUTO_INCREMENT,
35  `a_category_name` VARCHAR(45) NULL DEFAULT NULL,
36  `category_id` INT(11) NULL DEFAULT NULL,
37  PRIMARY KEY (`a_category_id`),
38  INDEX `foreignkey_idx` (`category_id` ASC) VISIBLE,
39  CONSTRAINT `FK_a_categories_category`
40    FOREIGN KEY (`category_id`)
41    REFERENCES `test1338`.`categories` (`category_id`))
42ENGINE = InnoDB
43DEFAULT CHARACTER SET = utf8mb4
44COLLATE = utf8mb4_0900_ai_ci;
45
46
47-- -----------------------------------------------------
48-- Table `test1337`.`b_categories`
49-- -----------------------------------------------------
50CREATE TABLE IF NOT EXISTS `test1338`.`b_categories` (
51  `b_category_id` INT(11) NOT NULL AUTO_INCREMENT,
52  `b_categories_name` VARCHAR(255) NOT NULL,
53  `a_category_id` INT(11) NULL DEFAULT NULL,
54  PRIMARY KEY (`b_category_id`),
55  INDEX `aforeignkey_idx` (`a_category_id` ASC) VISIBLE,
56  CONSTRAINT `FK_b_categories_category`
57    FOREIGN KEY (`a_category_id`)
58    REFERENCES `test1338`.`a_categories` (`a_category_id`))
59ENGINE = InnoDB
60DEFAULT CHARACTER SET = utf8mb4
61COLLATE = utf8mb4_0900_ai_ci;
62
63
64-- -----------------------------------------------------
65-- Table `test1337`.`c_categories`
66-- -----------------------------------------------------
67CREATE TABLE IF NOT EXISTS `test1338`.`c_categories` (
68  `c_category_id` INT(11) NOT NULL AUTO_INCREMENT,
69  `c_category_name` VARCHAR(255) NOT NULL,
70  `b_category_id` INT(11) NULL DEFAULT NULL,
71  PRIMARY KEY (`c_category_id`),
72  INDEX `bcategories_idx` (`b_category_id` ASC) VISIBLE,
73  CONSTRAINT `FK_c_categories_category`
74    FOREIGN KEY (`b_category_id`)
75    REFERENCES `test1338`.`b_categories` (`b_category_id`))
76ENGINE = InnoDB
77DEFAULT CHARACTER SET = utf8mb4
78COLLATE = utf8mb4_0900_ai_ci;
79
80
81-- -----------------------------------------------------
82-- Table `test1337`.`products`
83-- -----------------------------------------------------
84CREATE TABLE IF NOT EXISTS `test1338`.`products` (
85  `product_id` INT(11) NOT NULL AUTO_INCREMENT,
86  `product_name` VARCHAR(255) NOT NULL,
87  `price` INT(11) NOT NULL,
88  `quantity` INT(11) NOT NULL,
89  `quantity_on_order` INT(11) NULL DEFAULT NULL,
90  `category_id` INT(11) NOT NULL,
91  `a_category_id` INT(11) NULL DEFAULT NULL,
92  `b_category_id` INT(11) NULL DEFAULT NULL,
93  `c_category_id` INT(11) NULL DEFAULT NULL,
94  `discounted` INT(11) NULL DEFAULT NULL,
95  `characteristics` VARCHAR(1000) NULL DEFAULT NULL,
96  `product_image` VARCHAR(500) NULL DEFAULT NULL,
97  PRIMARY KEY (`product_id`),
98  INDEX `acategoryid_idx` (`a_category_id` ASC) VISIBLE,
99  INDEX `bcategoryid_idx` (`b_category_id` ASC) VISIBLE,
100  INDEX `categoryid_idx` (`category_id` ASC) VISIBLE,
101  INDEX `ccategoryid_idx` (`c_category_id` ASC) VISIBLE,
102  CONSTRAINT `FK_product_a_categories`
103    FOREIGN KEY (`a_category_id`)
104    REFERENCES `test1338`.`a_categories` (`a_category_id`),
105  CONSTRAINT `FK_product_b_categories`
106    FOREIGN KEY (`b_category_id`)
107    REFERENCES `test1338`.`b_categories` (`b_category_id`),
108  CONSTRAINT `FK_product_c_categories`
109    FOREIGN KEY (`c_category_id`)
110    REFERENCES `test1338`.`c_categories` (`c_category_id`),
111  CONSTRAINT `FK_product_category`
112    FOREIGN KEY (`category_id`)
113    REFERENCES `test1338`.`categories` (`category_id`))
114ENGINE = InnoDB
115DEFAULT CHARACTER SET = utf8mb4
116COLLATE = utf8mb4_0900_ai_ci;
117
118
119-- -----------------------------------------------------
120-- Table `test1337`.`comments`
121-- -----------------------------------------------------
122CREATE TABLE IF NOT EXISTS `test1338`.`comments` (
123  `id` INT(11) NOT NULL AUTO_INCREMENT,
124  `feedback` VARCHAR(1000) NULL DEFAULT NULL,
125  `product_id` INT(11) NOT NULL,
126  PRIMARY KEY (`id`),
127  INDEX `technomarket_updated` (`product_id` ASC) VISIBLE,
128  CONSTRAINT `FK_comment_product`
129    FOREIGN KEY (`product_id`)
130    REFERENCES `test1338`.`products` (`product_id`))
131ENGINE = InnoDB
132DEFAULT CHARACTER SET = utf8mb4
133COLLATE = utf8mb4_0900_ai_ci;
134
135
136-- -----------------------------------------------------
137-- Table `test1337`.`users`
138-- -----------------------------------------------------
139CREATE TABLE IF NOT EXISTS `test1338`.`users` (
140  `user_id` INT(11) NOT NULL AUTO_INCREMENT,
141  `username` VARCHAR(50) NOT NULL,
142  `password` VARCHAR(45) NOT NULL,
143  `first_name` VARCHAR(50) NOT NULL,
144  `last_name` VARCHAR(50) NOT NULL,
145  `email` VARCHAR(100) NOT NULL,
146  `user_role_id` INT(11) NULL DEFAULT '1',
147  `gender` VARCHAR(55) NOT NULL,
148  `verificated` VARCHAR(45) NULL DEFAULT NULL,
149  PRIMARY KEY (`user_id`),
150  UNIQUE INDEX `email` (`email` ASC) VISIBLE,
151  UNIQUE INDEX `user_id` (`user_id` ASC) VISIBLE,
152  UNIQUE INDEX `usersname` (`username` ASC) VISIBLE,
153  INDEX `userroleid` (`user_role_id` ASC) VISIBLE)
154ENGINE = InnoDB
155DEFAULT CHARACTER SET = utf8mb4
156COLLATE = utf8mb4_0900_ai_ci;
157
158
159-- -----------------------------------------------------
160-- Table `test1337`.`orders`
161-- -----------------------------------------------------
162CREATE TABLE IF NOT EXISTS `test1338`.`orders` (
163  `order_id` INT(11) NOT NULL AUTO_INCREMENT,
164  `product_id` INT(11) NOT NULL,
165  `user_id` INT(11) NOT NULL,
166  `product_quantity` TINYINT(4) NOT NULL,
167  `status` VARCHAR(50) NULL DEFAULT NULL,
168  `required_date` DATE NULL DEFAULT NULL,
169  `shipped_date` DATE NULL DEFAULT NULL,
170  PRIMARY KEY (`order_id`),
171  INDEX `productid_idx` (`product_id` ASC) VISIBLE,
172  INDEX `userid_idx` (`user_id` ASC) VISIBLE,
173  CONSTRAINT `FK_order_product`
174    FOREIGN KEY (`product_id`)
175    REFERENCES `test1338`.`products` (`product_id`),
176  CONSTRAINT `FK_order_user`
177    FOREIGN KEY (`user_id`)
178    REFERENCES `test1338`.`users` (`user_id`))
179ENGINE = InnoDB
180DEFAULT CHARACTER SET = utf8mb4
181COLLATE = utf8mb4_0900_ai_ci;
182
183
184-- -----------------------------------------------------
185-- Table `test1337`.`store_locations`
186-- -----------------------------------------------------
187CREATE TABLE IF NOT EXISTS `test1338`.`store_locations` (
188  `store_id` INT(11) NOT NULL AUTO_INCREMENT,
189  `store_name` VARCHAR(100) NOT NULL,
190  `city` VARCHAR(100) NOT NULL,
191  `street` VARCHAR(200) NOT NULL,
192  `phone` VARCHAR(15) NOT NULL,
193  `e-mail` VARCHAR(100) NOT NULL,
194  `work_time` VARCHAR(500) NOT NULL,
195  PRIMARY KEY (`store_id`))
196ENGINE = InnoDB
197DEFAULT CHARACTER SET = utf8mb4
198COLLATE = utf8mb4_0900_ai_ci;
199
200
201-- -----------------------------------------------------
202-- Table `test1337`.`user_addresses`
203-- -----------------------------------------------------
204CREATE TABLE IF NOT EXISTS `test1338`.`user_addresses` (
205  `order_details_id` INT(11) NOT NULL AUTO_INCREMENT,
206  `full_name` VARCHAR(150) NOT NULL,
207  `telephone` VARCHAR(20) NOT NULL,
208  `order_id` INT(11) NOT NULL,
209  `comments` VARCHAR(150) NULL DEFAULT NULL,
210  `city_name` VARCHAR(45) NOT NULL,
211  `postal_code` VARCHAR(45) NOT NULL,
212  `street_name` VARCHAR(70) NOT NULL,
213  `street_number` VARCHAR(45) NULL DEFAULT NULL,
214  `block` VARCHAR(45) NULL DEFAULT NULL,
215  `entrance` VARCHAR(45) NULL DEFAULT NULL,
216  `floor` VARCHAR(45) NULL DEFAULT NULL,
217  `apartament` VARCHAR(45) NULL DEFAULT NULL,
218  `user_id` INT(11) NULL DEFAULT NULL,
219  PRIMARY KEY (`order_details_id`),
220  INDEX `ordersid_idx` (`order_id` ASC) VISIBLE,
221  INDEX `useridaddresses_idx` (`user_id` ASC) VISIBLE,
222  CONSTRAINT `FK_user_address_order`
223    FOREIGN KEY (`order_id`)
224    REFERENCES `test1338`.`orders` (`order_id`),
225  CONSTRAINT `FK_user_address_user`
226    FOREIGN KEY (`user_id`)
227    REFERENCES `test1338`.`users` (`user_id`))
228ENGINE = InnoDB
229DEFAULT CHARACTER SET = utf8mb4
230COLLATE = utf8mb4_0900_ai_ci;
231
232
233-- -----------------------------------------------------
234-- Table `test1337`.`user_favorites`
235-- -----------------------------------------------------
236CREATE TABLE IF NOT EXISTS `test1338`.`user_favorites` (
237  `user_id` INT(11) NOT NULL UNIQUE AUTO_INCREMENT,
238  `product_id` INT(11) NOT NULL,
239  PRIMARY KEY (`user_id`, `product_id`),
240  INDEX `productidfavorites_idx` (`product_id` ASC) VISIBLE,
241  INDEX `useridfavorites` (`user_id` ASC) VISIBLE,
242  CONSTRAINT `FK_user_favorites_product`
243    FOREIGN KEY (`product_id`)
244    REFERENCES `test1337`.`products` (`product_id`),
245  CONSTRAINT `FK_user_favorites_user`
246    FOREIGN KEY (`user_id`)
247    REFERENCES `test1338`.`users` (`user_id`))
248ENGINE = InnoDB
249DEFAULT CHARACTER SET = utf8mb4
250COLLATE = utf8mb4_0900_ai_ci;
251
252
253-- -----------------------------------------------------
254-- Table `test1337`.`user_roles`
255-- -----------------------------------------------------
256CREATE TABLE IF NOT EXISTS `test1338`.`user_roles` (
257  `user_role_id` INT(11) NOT NULL UNIQUE AUTO_INCREMENT,
258  `user_role_description` VARCHAR(100) NOT NULL,
259  PRIMARY KEY (`user_role_id`))
260ENGINE = InnoDB
261DEFAULT CHARACTER SET = utf8mb4
262COLLATE = utf8mb4_0900_ai_ci;
263
264
265SET SQL_MODE=@OLD_SQL_MODE;
266SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
267SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
268
269INSERT INTO user_roles (user_role_id, user_role_description) VALUES (1, “Administrator”);
270INSERT INTO user_roles (user_role_id, user_role_description) VALUES (2, "Default User");
271
272INSERT INTO categories (category_id, category_name) VALUES (1, “DEFAULT");
273INSERT INTO categories (category_id, category_name) VALUES (2, “Televizori i audio”);
274INSERT INTO categories (category_id, category_name) VALUES (3, "Kompiutri i periferiq");
275
276insert into a_categories (a_category_id, a_category_name, category_id) VALUES (1, "DEFAULT", 1);
277insert into b_categories (b_category_id, b_categories_name, a_category_id) VALUES (1, "DEFAULT", 1);
278insert into c_categories (c_category_id, c_category_name, b_category_id) VALUES (1, "DEFAULT", 1);