· 6 years ago · May 02, 2019, 02:12 PM
1-- MySQL Workbench Forward Engineering
2
3SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
4SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
5SET @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';
6
7-- -----------------------------------------------------
8-- Schema Fog
9-- -----------------------------------------------------
10
11-- -----------------------------------------------------
12-- Schema Fog
13-- -----------------------------------------------------
14CREATE SCHEMA IF NOT EXISTS `Fog` DEFAULT CHARACTER SET utf8 ;
15USE `Fog` ;
16
17-- -----------------------------------------------------
18-- Table `Fog`.`category`
19-- -----------------------------------------------------
20CREATE TABLE IF NOT EXISTS `Fog`.`category` (
21 `id_category` INT(11) NOT NULL,
22 `category_name` VARCHAR(45) NOT NULL,
23 PRIMARY KEY (`id_category`))
24ENGINE = InnoDB;
25
26
27-- -----------------------------------------------------
28-- Table `Fog`.`materials`
29-- -----------------------------------------------------
30CREATE TABLE IF NOT EXISTS `Fog`.`materials` (
31 `id_material` INT NOT NULL,
32 `description` VARCHAR(200) NOT NULL,
33 `height` INT(11) NULL,
34 `width` INT(11) NULL,
35 `length` INT(11) NULL,
36 `price` DOUBLE NOT NULL,
37 `unit` VARCHAR(45) NOT NULL,
38 `id_category` INT(11) NOT NULL,
39 PRIMARY KEY (`id_material`),
40 INDEX `id_category_idx` (`id_category` ASC) VISIBLE,
41 CONSTRAINT `id_category`
42 FOREIGN KEY (`id_category`)
43 REFERENCES `Fog`.`category` (`id_category`)
44 ON DELETE NO ACTION
45 ON UPDATE NO ACTION)
46ENGINE = InnoDB;
47
48
49-- -----------------------------------------------------
50-- Table `Fog`.`order_details_category`
51-- -----------------------------------------------------
52CREATE TABLE IF NOT EXISTS `Fog`.`order_details_category` (
53 `id_order_details_category` INT(11) NOT NULL,
54 `details_category_name` VARCHAR(45) NOT NULL,
55 PRIMARY KEY (`id_order_details_category`))
56ENGINE = InnoDB;
57
58
59-- -----------------------------------------------------
60-- Table `Fog`.`customers`
61-- -----------------------------------------------------
62CREATE TABLE IF NOT EXISTS `Fog`.`customers` (
63 `id_customer` INT(11) NOT NULL,
64 `customer_name` VARCHAR(45) NOT NULL,
65 `phone` VARCHAR(45) NOT NULL,
66 `email` VARCHAR(45) NOT NULL,
67 PRIMARY KEY (`id_customer`))
68ENGINE = InnoDB;
69
70
71-- -----------------------------------------------------
72-- Table `Fog`.`roles`
73-- -----------------------------------------------------
74CREATE TABLE IF NOT EXISTS `Fog`.`roles` (
75 `id_role` INT(11) NOT NULL,
76 `role` VARCHAR(45) NOT NULL,
77 PRIMARY KEY (`id_role`))
78ENGINE = InnoDB;
79
80-- -----------------------------------------------------
81-- Table `Fog`.`employees`
82-- -----------------------------------------------------
83CREATE TABLE IF NOT EXISTS `Fog`.`employees` (
84 `id_employee` INT(11) NOT NULL,
85 `name` VARCHAR(45) NOT NULL,
86 `id_role` INT(11) NOT NULL,
87 PRIMARY KEY (`id_employee`),
88 INDEX `id_role_idx` (`id_role` ASC) VISIBLE,
89 FOREIGN KEY (`id_role`)
90 REFERENCES `Fog`.`roles` (`id_role`)
91 ON DELETE NO ACTION
92 ON UPDATE NO ACTION)
93ENGINE = InnoDB;
94
95
96-- -----------------------------------------------------
97-- Table `Fog`.`orders`
98-- -----------------------------------------------------
99CREATE TABLE IF NOT EXISTS `Fog`.`orders` (
100 `id_order` INT NOT NULL,
101 `status` VARCHAR(45) NOT NULL,
102 `order_width` INT(11) NOT NULL,
103 `order_length` INT(11) NOT NULL,
104 `incline` INT(11) NOT NULL,
105 `id_customer` INT(11) NOT NULL,
106 `id_employee` INT(11) NOT NULL,
107 `price` DOUBLE NOT NULL,
108 PRIMARY KEY (`id_order`),
109 INDEX `id_customer_idx` (`id_customer` ASC) VISIBLE,
110 INDEX `id_employee_idx` (`id_employee` ASC) VISIBLE,
111 CONSTRAINT `id_customer`
112 FOREIGN KEY (`id_customer`)
113 REFERENCES `Fog`.`customers` (`id_customer`)
114 ON DELETE NO ACTION
115 ON UPDATE NO ACTION,
116 CONSTRAINT `id_employee`
117 FOREIGN KEY (`id_employee`)
118 REFERENCES `Fog`.`employees` (`id_employee`)
119 ON DELETE NO ACTION
120 ON UPDATE NO ACTION)
121ENGINE = InnoDB;
122
123
124-- -----------------------------------------------------
125-- Table `Fog`.`order_details`
126-- -----------------------------------------------------
127CREATE TABLE IF NOT EXISTS `Fog`.`order_details` (
128 `id_order_detail` INT(11) NOT NULL,
129 `id_material` INT(11) NOT NULL,
130 `id_order` INT(11) NOT NULL,
131 `id_order_detail_category` INT(11) NOT NULL,
132 PRIMARY KEY (`id_order_detail`),
133 INDEX `id_material_idx` (`id_material` ASC) VISIBLE,
134 INDEX `id_order_idx` (`id_order` ASC) VISIBLE,
135 INDEX `id_order_detail_category_idx` (`id_order_detail_category` ASC) VISIBLE,
136 CONSTRAINT `id_material`
137 FOREIGN KEY (`id_material`)
138 REFERENCES `Fog`.`materials` (`id_material`)
139 ON DELETE NO ACTION
140 ON UPDATE NO ACTION,
141 CONSTRAINT `id_order`
142 FOREIGN KEY (`id_order`)
143 REFERENCES `Fog`.`orders` (`id_order`)
144 ON DELETE NO ACTION
145 ON UPDATE NO ACTION,
146 CONSTRAINT `id_order_detail_category`
147 FOREIGN KEY (`id_order_detail_category`)
148 REFERENCES `Fog`.`order_details_category` (`id_order_details_category`)
149 ON DELETE NO ACTION
150 ON UPDATE NO ACTION)
151ENGINE = InnoDB;
152
153
154SET SQL_MODE=@OLD_SQL_MODE;
155SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
156SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;