· 7 years ago · Feb 19, 2019, 06:28 PM
1-- MySQL Script generated by MySQL Workbench
2-- Tue Feb 19 18:45:47 2019
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
10CREATE SCHEMA IF NOT EXISTS `technomarket_project`
11USE `technomarket_project` ;
12
13-- -----------------------------------------------------
14-- Table `technomarket_project`.`categories`
15-- -----------------------------------------------------
16CREATE TABLE IF NOT EXISTS `technomarket_project`.`categories` (
17 `category_id` INT(11) NOT NULL AUTO_INCREMENT,
18 `category_name` VARCHAR(100) NOT NULL,
19 PRIMARY KEY (`category_id`))
20ENGINE = InnoDB
21AUTO_INCREMENT = 7
22DEFAULT CHARACTER SET = utf8mb4
23COLLATE = utf8mb4_0900_ai_ci;
24
25
26-- -----------------------------------------------------
27-- Table `technomarket_project`.`a_categories`
28-- -----------------------------------------------------
29CREATE TABLE IF NOT EXISTS `technomarket_project`.`a_categories` (
30 `a_category_id` INT(11) NOT NULL AUTO_INCREMENT,
31 `a_category_name` VARCHAR(45) NULL DEFAULT NULL,
32 `category_id` INT(11) NULL DEFAULT NULL,
33 PRIMARY KEY (`a_category_id`),
34 INDEX `foreignkey_idx` (`category_id` ASC) VISIBLE,
35 CONSTRAINT `foreignkey`
36 FOREIGN KEY (`category_id`)
37 REFERENCES `technomarket_project`.`categories` (`category_id`))
38ENGINE = InnoDB
39AUTO_INCREMENT = 26
40DEFAULT CHARACTER SET = utf8mb4
41COLLATE = utf8mb4_0900_ai_ci;
42
43
44-- -----------------------------------------------------
45-- Table `technomarket_project`.`b_categories`
46-- -----------------------------------------------------
47CREATE TABLE IF NOT EXISTS `technomarket_project`.`b_categories` (
48 `b_categories_id` INT(11) NOT NULL AUTO_INCREMENT,
49 `b_categories_name` VARCHAR(255) NOT NULL,
50 `a_categories_id` INT(11) NULL DEFAULT NULL,
51 PRIMARY KEY (`b_categories_id`),
52 INDEX `aforeignkey_idx` (`a_categories_id` ASC) VISIBLE,
53 CONSTRAINT `aforeignkey`
54 FOREIGN KEY (`a_categories_id`)
55 REFERENCES `technomarket_project`.`a_categories` (`a_category_id`))
56ENGINE = InnoDB
57AUTO_INCREMENT = 28
58DEFAULT CHARACTER SET = utf8mb4
59COLLATE = utf8mb4_0900_ai_ci;
60
61
62-- -----------------------------------------------------
63-- Table `technomarket_project`.`c_categories`
64-- -----------------------------------------------------
65CREATE TABLE IF NOT EXISTS `technomarket_project`.`c_categories` (
66 `c_categories_id` INT(11) NOT NULL AUTO_INCREMENT,
67 `c_category_name` VARCHAR(255) NOT NULL,
68 `b_categories_id` INT(11) NULL DEFAULT NULL,
69 PRIMARY KEY (`c_categories_id`),
70 INDEX `bcategories_idx` (`b_categories_id` ASC) VISIBLE,
71 CONSTRAINT `bcategories`
72 FOREIGN KEY (`b_categories_id`)
73 REFERENCES `technomarket_project`.`b_categories` (`b_categories_id`))
74ENGINE = InnoDB
75AUTO_INCREMENT = 4
76DEFAULT CHARACTER SET = utf8mb4
77COLLATE = utf8mb4_0900_ai_ci;
78
79
80-- -----------------------------------------------------
81-- Table `technomarket_project`.`products`
82-- -----------------------------------------------------
83CREATE TABLE IF NOT EXISTS `technomarket_project`.`products` (
84 `product_id` INT(11) NOT NULL,
85 `product_name` VARCHAR(255) NOT NULL,
86 `quantity` INT(11) NOT NULL,
87 `category_id` INT(11) NOT NULL,
88 `discounted` INT(11) NOT NULL,
89 `quantity_on_order` INT(11) NOT NULL,
90 `price` INT(11) NOT NULL,
91 PRIMARY KEY (`product_id`),
92 INDEX `categoryid_idx` (`category_id` ASC) VISIBLE,
93 CONSTRAINT `categoryid`
94 FOREIGN KEY (`category_id`)
95 REFERENCES `technomarket_project`.`categories` (`category_id`))
96ENGINE = InnoDB
97DEFAULT CHARACTER SET = utf8mb4
98COLLATE = utf8mb4_0900_ai_ci;
99
100
101-- -----------------------------------------------------
102-- Table `technomarket_project`.`user_roles`
103-- -----------------------------------------------------
104CREATE TABLE IF NOT EXISTS `technomarket_project`.`user_roles` (
105 `user_role_id` INT(11) NOT NULL,
106 `user_role_description` VARCHAR(100) NOT NULL,
107 PRIMARY KEY (`user_role_id`))
108ENGINE = InnoDB
109DEFAULT CHARACTER SET = utf8mb4
110COLLATE = utf8mb4_0900_ai_ci;
111
112
113-- -----------------------------------------------------
114-- Table `technomarket_project`.`users`
115-- -----------------------------------------------------
116CREATE TABLE IF NOT EXISTS `technomarket_project`.`users` (
117 `user_id` INT(11) NOT NULL AUTO_INCREMENT,
118 `username` VARCHAR(50) NOT NULL,
119 `password` VARCHAR(45) NOT NULL,
120 `first_name` VARCHAR(50) NOT NULL,
121 `last_name` VARCHAR(50) NOT NULL,
122 `email` VARCHAR(100) NOT NULL,
123 `user_role_id` INT(11) NOT NULL,
124 `gender` TINYINT(1) NOT NULL,
125 PRIMARY KEY (`user_id`),
126 UNIQUE INDEX `user_id` (`user_id` ASC) VISIBLE,
127 INDEX `userroleid` (`user_role_id` ASC) VISIBLE,
128 CONSTRAINT `userroleid`
129 FOREIGN KEY (`user_role_id`)
130 REFERENCES `technomarket_project`.`user_roles` (`user_role_id`)
131 ON DELETE CASCADE)
132ENGINE = InnoDB
133DEFAULT CHARACTER SET = utf8mb4
134COLLATE = utf8mb4_0900_ai_ci;
135
136
137-- -----------------------------------------------------
138-- Table `technomarket_project`.`orders`
139-- -----------------------------------------------------
140CREATE TABLE IF NOT EXISTS `technomarket_project`.`orders` (
141 `order_id` INT(11) NOT NULL,
142 `product_id` INT(11) NOT NULL,
143 `user_id` INT(11) NOT NULL,
144 `product_quantity` TINYINT(4) NOT NULL,
145 `status` VARCHAR(50) NOT NULL,
146 `required_date` DATE NOT NULL,
147 `shipped_date` DATE NOT NULL,
148 PRIMARY KEY (`order_id`),
149 INDEX `productid_idx` (`product_id` ASC) VISIBLE,
150 INDEX `userid_idx` (`user_id` ASC) VISIBLE,
151 CONSTRAINT `productid`
152 FOREIGN KEY (`product_id`)
153 REFERENCES `technomarket_project`.`products` (`product_id`),
154 CONSTRAINT `userid`
155 FOREIGN KEY (`user_id`)
156 REFERENCES `technomarket_project`.`users` (`user_id`))
157ENGINE = InnoDB
158DEFAULT CHARACTER SET = utf8mb4
159COLLATE = utf8mb4_0900_ai_ci;
160
161
162-- -----------------------------------------------------
163-- Table `technomarket_project`.`orders_details`
164-- -----------------------------------------------------
165CREATE TABLE IF NOT EXISTS `technomarket_project`.`orders_details` (
166 `order_details_id` INT(11) NOT NULL,
167 `full_name` VARCHAR(150) NOT NULL,
168 `telephone` VARCHAR(20) NOT NULL,
169 `order_id` INT(11) NOT NULL,
170 `comments` VARCHAR(150) NULL DEFAULT NULL,
171 `city_name` VARCHAR(45) NOT NULL,
172 `postal_code` VARCHAR(45) NOT NULL,
173 `street_name` VARCHAR(70) NOT NULL,
174 `street_number` VARCHAR(45) NULL DEFAULT NULL,
175 `block` VARCHAR(45) NULL DEFAULT NULL,
176 `entrance` VARCHAR(45) NULL DEFAULT NULL,
177 `floor` VARCHAR(45) NULL DEFAULT NULL,
178 `apartament` VARCHAR(45) NULL DEFAULT NULL,
179 PRIMARY KEY (`order_details_id`),
180 INDEX `ordersid_idx` (`order_id` ASC) VISIBLE,
181 CONSTRAINT `ordersid`
182 FOREIGN KEY (`order_id`)
183 REFERENCES `technomarket_project`.`orders` (`order_id`))
184ENGINE = InnoDB
185DEFAULT CHARACTER SET = utf8mb4
186
187SET SQL_MODE=@OLD_SQL_MODE;
188SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
189SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;