· 6 years ago · Aug 13, 2019, 05:16 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 mydb
9-- -----------------------------------------------------
10
11-- -----------------------------------------------------
12-- Schema mydb
13-- -----------------------------------------------------
14CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 ;
15USE `mydb` ;
16
17-- -----------------------------------------------------
18-- Table `mydb`.`person`
19-- -----------------------------------------------------
20CREATE TABLE IF NOT EXISTS `mydb`.`person` (
21 `id` INT NOT NULL AUTO_INCREMENT,
22 `name` VARCHAR(255) NOT NULL,
23 `jmbg` CHAR(13) NOT NULL,
24 `title` VARCHAR(255) NULL,
25 `contact` VARCHAR(255) NULL,
26 `employment` VARCHAR(255) NULL,
27 `role_name` VARCHAR(255) NULL,
28 PRIMARY KEY (`id`))
29ENGINE = InnoDB;
30
31
32-- -----------------------------------------------------
33-- Table `mydb`.`category`
34-- -----------------------------------------------------
35CREATE TABLE IF NOT EXISTS `mydb`.`category` (
36 `id` INT NOT NULL,
37 `name` VARCHAR(255) NOT NULL,
38 PRIMARY KEY (`id`))
39ENGINE = InnoDB;
40
41
42-- -----------------------------------------------------
43-- Table `mydb`.`building`
44-- -----------------------------------------------------
45CREATE TABLE IF NOT EXISTS `mydb`.`building` (
46 `id` INT NOT NULL,
47 `code` VARCHAR(255) NOT NULL,
48 `name` VARCHAR(255) NOT NULL,
49 `geoLocation` VARCHAR(255) NULL,
50 PRIMARY KEY (`id`))
51ENGINE = InnoDB;
52
53
54-- -----------------------------------------------------
55-- Table `mydb`.`room`
56-- -----------------------------------------------------
57CREATE TABLE IF NOT EXISTS `mydb`.`room` (
58 `id` INT NOT NULL,
59 `name` VARCHAR(255) NOT NULL,
60 `code` VARCHAR(255) NOT NULL,
61 `building_id` INT NOT NULL,
62 PRIMARY KEY (`id`),
63 INDEX `fk_room_building1_idx` (`building_id` ASC) VISIBLE,
64 CONSTRAINT `fk_room_building1`
65 FOREIGN KEY (`building_id`)
66 REFERENCES `mydb`.`building` (`id`)
67 ON DELETE NO ACTION
68 ON UPDATE NO ACTION)
69ENGINE = InnoDB;
70
71
72-- -----------------------------------------------------
73-- Table `mydb`.`fixed_assets`
74-- -----------------------------------------------------
75CREATE TABLE IF NOT EXISTS `mydb`.`fixed_assets` (
76 `id` INT NOT NULL,
77 `number` INT(8) NOT NULL,
78 `status` CHAR(1) NOT NULL,
79 `description` VARCHAR(1024) NOT NULL,
80 `purchaseDate` DATE NOT NULL,
81 `initialPrice` DECIMAL(8,2) NOT NULL,
82 `amortization` INT(10) NOT NULL,
83 `person_id` INT NOT NULL,
84 `category_id` INT NOT NULL,
85 `room_id` INT NOT NULL,
86 PRIMARY KEY (`id`),
87 INDEX `fk_fixed_assets_person_idx` (`person_id` ASC) VISIBLE,
88 INDEX `fk_fixed_assets_category1_idx` (`category_id` ASC) VISIBLE,
89 INDEX `fk_fixed_assets_room1_idx` (`room_id` ASC) VISIBLE,
90 CONSTRAINT `fk_fixed_assets_person`
91 FOREIGN KEY (`person_id`)
92 REFERENCES `mydb`.`person` (`id`)
93 ON DELETE NO ACTION
94 ON UPDATE NO ACTION,
95 CONSTRAINT `fk_fixed_assets_category1`
96 FOREIGN KEY (`category_id`)
97 REFERENCES `mydb`.`category` (`id`)
98 ON DELETE NO ACTION
99 ON UPDATE NO ACTION,
100 CONSTRAINT `fk_fixed_assets_room1`
101 FOREIGN KEY (`room_id`)
102 REFERENCES `mydb`.`room` (`id`)
103 ON DELETE NO ACTION
104 ON UPDATE NO ACTION)
105ENGINE = InnoDB;
106
107
108-- -----------------------------------------------------
109-- Table `mydb`.`transfer`
110-- -----------------------------------------------------
111CREATE TABLE IF NOT EXISTS `mydb`.`transfer` (
112 `id` INT NOT NULL,
113 `dateCreated` DATE NOT NULL,
114 `fixed_assets_id` INT NOT NULL,
115 `personIdFrom` INT NULL,
116 `personIdTo` INT NULL,
117 `roomIdFrom` INT NULL,
118 `roomIdTo` INT NULL,
119 PRIMARY KEY (`id`),
120 INDEX `fk_transfer_fixed_assets1_idx` (`fixed_assets_id` ASC) VISIBLE,
121 INDEX `fk_transfer_person1_idx` (`personIdFrom` ASC) VISIBLE,
122 INDEX `fk_transfer_person2_idx` (`personIdTo` ASC) VISIBLE,
123 INDEX `fk_transfer_room1_idx` (`roomIdFrom` ASC) VISIBLE,
124 INDEX `fk_transfer_room2_idx` (`roomIdTo` ASC) VISIBLE,
125 CONSTRAINT `fk_transfer_fixed_assets1`
126 FOREIGN KEY (`fixed_assets_id`)
127 REFERENCES `mydb`.`fixed_assets` (`id`)
128 ON DELETE NO ACTION
129 ON UPDATE NO ACTION,
130 CONSTRAINT `fk_transfer_person1`
131 FOREIGN KEY (`personIdFrom`)
132 REFERENCES `mydb`.`person` (`id`)
133 ON DELETE NO ACTION
134 ON UPDATE NO ACTION,
135 CONSTRAINT `fk_transfer_person2`
136 FOREIGN KEY (`personIdTo`)
137 REFERENCES `mydb`.`person` (`id`)
138 ON DELETE NO ACTION
139 ON UPDATE NO ACTION,
140 CONSTRAINT `fk_transfer_room1`
141 FOREIGN KEY (`roomIdFrom`)
142 REFERENCES `mydb`.`room` (`id`)
143 ON DELETE NO ACTION
144 ON UPDATE NO ACTION,
145 CONSTRAINT `fk_transfer_room2`
146 FOREIGN KEY (`roomIdTo`)
147 REFERENCES `mydb`.`room` (`id`)
148 ON DELETE NO ACTION
149 ON UPDATE NO ACTION)
150ENGINE = InnoDB;
151
152
153SET SQL_MODE=@OLD_SQL_MODE;
154SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
155SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;