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