· 8 years ago · Nov 22, 2017, 04:50 AM
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='TRADITIONAL,ALLOW_INVALID_DATES';
4CREATE SCHEMA IF NOT EXISTS `trabalho_unesp_bd` DEFAULT CHARACTER SET utf8 ;
5USE `trabalho_unesp_bd` ;
6
7CREATE TABLE IF NOT EXISTS `trabalho_unesp_bd`.`branches` (
8 `id` INT NOT NULL,
9 `name` VARCHAR(45) NULL,
10 PRIMARY KEY (`id`))
11ENGINE = InnoDB;
12
13CREATE TABLE IF NOT EXISTS `trabalho_unesp_bd`.`suppliers` (
14 `id` INT NOT NULL,
15 `name` VARCHAR(45) NULL,
16 `cnpj` VARCHAR(16) NULL,
17 `city_name` VARCHAR(45) NULL,
18 `phone_number` VARCHAR(14) NULL,
19 `address` VARCHAR(255) NULL,
20 `contact_name` VARCHAR(45) NULL,
21 PRIMARY KEY (`id`))
22ENGINE = InnoDB;
23
24CREATE TABLE IF NOT EXISTS `trabalho_unesp_bd`.`supplier_branches` (
25 `supplier_id` INT NOT NULL,
26 `branch_id` INT NOT NULL,
27 PRIMARY KEY (`supplier_id`, `branch_id`),
28 INDEX `fk_suppliers_branches_branches_idx` (`branch_id` ASC),
29 INDEX `fk_suppliers_branches_suppliers_idx` (`supplier_id` ASC),
30 CONSTRAINT `fk_suppliers_branches_suppliers`
31 FOREIGN KEY (`supplier_id`)
32 REFERENCES `trabalho_unesp_bd`.`suppliers` (`id`),
33 CONSTRAINT `fk_suppliers_branches_branches`
34 FOREIGN KEY (`branch_id`)
35 REFERENCES `trabalho_unesp_bd`.`branches` (`id`))
36ENGINE = InnoDB;
37
38CREATE TABLE IF NOT EXISTS `trabalho_unesp_bd`.`payment_methods` (
39 `id` INT NOT NULL,
40 `name` VARCHAR(45) NULL,
41 PRIMARY KEY (`id`))
42ENGINE = InnoDB;
43
44CREATE TABLE IF NOT EXISTS `trabalho_unesp_bd`.`product_types` (
45 `id` INT NOT NULL,
46 `name` VARCHAR(45) NULL,
47 PRIMARY KEY (`id`))
48ENGINE = InnoDB;
49
50CREATE TABLE IF NOT EXISTS `trabalho_unesp_bd`.`contracts` (
51 `id` INT NOT NULL,
52 `supplier_id` INT NOT NULL,
53 `payment_method_id` INT NOT NULL,
54 `start_date` DATE NULL,
55 `end_date` DATE NULL,
56 `product_type_id` INT NOT NULL,
57 PRIMARY KEY (`id`),
58 INDEX `fk_contracts_suppliers_idx` (`supplier_id` ASC),
59 INDEX `fk_contracts_payment_methods_idx` (`payment_method_id` ASC),
60 INDEX `fk_contracts_product_types_idx` (`product_type_id` ASC),
61 CONSTRAINT `fk_contracts_suppliers`
62 FOREIGN KEY (`supplier_id`)
63 REFERENCES `trabalho_unesp_bd`.`suppliers` (`id`),
64 CONSTRAINT `fk_contracts_payment_methods`
65 FOREIGN KEY (`payment_method_id`)
66 REFERENCES `trabalho_unesp_bd`.`payment_methods` (`id`),
67 CONSTRAINT `fk_contracts_product_types1`
68 FOREIGN KEY (`product_type_id`)
69 REFERENCES `trabalho_unesp_bd`.`product_types` (`id`))
70ENGINE = InnoDB;
71
72CREATE TABLE IF NOT EXISTS `trabalho_unesp_bd`.`products` (
73 `id` INT NOT NULL,
74 `product_type_id` INT NOT NULL,
75 `name` VARCHAR(45) NULL,
76 PRIMARY KEY (`id`),
77 INDEX `fk_product_product_type_idx` (`product_type_id` ASC),
78 CONSTRAINT `fk_product_product_type`
79 FOREIGN KEY (`product_type_id`)
80 REFERENCES `trabalho_unesp_bd`.`product_types` (`id`))
81ENGINE = InnoDB;
82
83CREATE TABLE IF NOT EXISTS `trabalho_unesp_bd`.`departments` (
84 `id` INT NOT NULL,
85 `name` VARCHAR(45) NULL,
86 `employee_id` INT NULL,
87 PRIMARY KEY (`id`),
88 INDEX `fk_departments_employees_idx` (`employee_id` ASC),
89 CONSTRAINT `fk_departments_employees1`
90 FOREIGN KEY (`employee_id`)
91 REFERENCES `trabalho_unesp_bd`.`employees` (`id`))
92ENGINE = InnoDB;
93
94CREATE TABLE IF NOT EXISTS `trabalho_unesp_bd`.`employees` (
95 `id` INT NOT NULL,
96 `name` VARCHAR(45) NULL,
97 `cpf` VARCHAR(12) NULL,
98 `can_request` TINYINT(1) NULL,
99 `department_id` INT NOT NULL,
100 PRIMARY KEY (`id`),
101 INDEX `fk_employees_departments_idx` (`department_id` ASC),
102 CONSTRAINT `fk_employees_departments1`
103 FOREIGN KEY (`department_id`)
104 REFERENCES `trabalho_unesp_bd`.`departments` (`id`))
105ENGINE = InnoDB;
106
107CREATE TABLE IF NOT EXISTS `trabalho_unesp_bd`.`requisitions` (
108 `id` INT NOT NULL,
109 `product_type_id` INT NOT NULL,
110 `employee_id` INT NOT NULL,
111 PRIMARY KEY (`id`),
112 INDEX `fk_requisitions_product_types_idx` (`product_type_id` ASC),
113 INDEX `fk_requisitions_employees_idx` (`employee_id` ASC),
114 CONSTRAINT `fk_requisitions_product_types`
115 FOREIGN KEY (`product_type_id`)
116 REFERENCES `trabalho_unesp_bd`.`product_types` (`id`),
117 CONSTRAINT `fk_requisitions_employees`
118 FOREIGN KEY (`employee_id`)
119 REFERENCES `trabalho_unesp_bd`.`employees` (`id`))
120ENGINE = InnoDB;
121
122CREATE TABLE IF NOT EXISTS `trabalho_unesp_bd`.`requisition_products` (
123 `requisition_id` INT NOT NULL,
124 `product_id` INT NOT NULL,
125 `quantity` VARCHAR(45) NULL,
126 PRIMARY KEY (`requisition_id`, `product_id`),
127 INDEX `fk_requisitions_products_products_idx` (`product_id` ASC),
128 INDEX `fk_requisitions_products_requisitions_idx` (`requisition_id` ASC),
129 CONSTRAINT `fk_requisitions_products_requisitions`
130 FOREIGN KEY (`requisition_id`)
131 REFERENCES `trabalho_unesp_bd`.`requisitions` (`id`),
132 CONSTRAINT `fk_requisitions_products_products`
133 FOREIGN KEY (`product_id`)
134 REFERENCES `trabalho_unesp_bd`.`products` (`id`))
135ENGINE = InnoDB;
136
137CREATE TABLE IF NOT EXISTS `trabalho_unesp_bd`.`contract_products` (
138 `contract_id` INT NOT NULL,
139 `product_id` INT NOT NULL,
140 `id` INT NOT NULL,
141 `value` DECIMAL NULL,
142 PRIMARY KEY (`id`),
143 INDEX `fk_contracts_products_products_idx` (`product_id` ASC),
144 INDEX `fk_contracts_products_contracts_idx` (`contract_id` ASC),
145 CONSTRAINT `fk_contract_products_contracts`
146 FOREIGN KEY (`contract_id`)
147 REFERENCES `trabalho_unesp_bd`.`contracts` (`id`),
148 CONSTRAINT `fk_contract_products_products`
149 FOREIGN KEY (`product_id`)
150 REFERENCES `trabalho_unesp_bd`.`products` (`id`))
151ENGINE = InnoDB;
152
153CREATE TABLE IF NOT EXISTS `trabalho_unesp_bd`.`employees_contract_products` (
154 `employee_id` INT NOT NULL,
155 `opinion` VARCHAR(255) NULL,
156 `contract_product_id` INT NOT NULL,
157 PRIMARY KEY (`employee_id`, `contract_product_id`),
158 INDEX `fk_employees_contracts_products_employees_idx` (`employee_id` ASC),
159 INDEX `fk_employees_contracts_products_contracts_produ_idx` (`contract_product_id` ASC),
160 CONSTRAINT `fk_employees_contract_products_employees`
161 FOREIGN KEY (`employee_id`)
162 REFERENCES `trabalho_unesp_bd`.`employees` (`id`),
163 CONSTRAINT `fk_employees_contract_products_contract_products`
164 FOREIGN KEY (`contract_product_id`)
165 REFERENCES `trabalho_unesp_bd`.`contract_products` (`id`))
166ENGINE = InnoDB;
167
168
169SET SQL_MODE=@OLD_SQL_MODE;
170SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
171SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;