ktBe6hfY

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