· 6 years ago · Jul 25, 2019, 01:00 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`.`USER_LEVEL`
19-- -----------------------------------------------------
20CREATE TABLE IF NOT EXISTS `mydb`.`USER_LEVEL` (
21 `level_id` INT NOT NULL,
22 `level_name` VARCHAR(45) NOT NULL,
23 PRIMARY KEY (`level_id`))
24ENGINE = InnoDB;
25
26
27-- -----------------------------------------------------
28-- Table `mydb`.`USER`
29-- -----------------------------------------------------
30CREATE TABLE IF NOT EXISTS `mydb`.`USER` (
31 `user_id` INT NOT NULL,
32 `name` VARCHAR(45) NOT NULL,
33 `lastname` VARCHAR(45) NOT NULL,
34 `password` VARCHAR(45) NOT NULL,
35 `email` VARCHAR(45) NOT NULL,
36 `access_level` INT NOT NULL,
37 `user_status` TINYINT(1) NOT NULL,
38 PRIMARY KEY (`user_id`),
39 INDEX `fk_USER_USER_LEVEL_idx` (`access_level` ASC) VISIBLE,
40 CONSTRAINT `fk_USER_USER_LEVEL`
41 FOREIGN KEY (`access_level`)
42 REFERENCES `mydb`.`USER_LEVEL` (`level_id`)
43 ON DELETE NO ACTION
44 ON UPDATE NO ACTION)
45ENGINE = InnoDB;
46
47
48-- -----------------------------------------------------
49-- Table `mydb`.`ACTIVITY_LOG`
50-- -----------------------------------------------------
51CREATE TABLE IF NOT EXISTS `mydb`.`ACTIVITY_LOG` (
52 `date_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
53 `activity_description` MEDIUMTEXT NULL,
54 `USER_user_id` INT NOT NULL,
55 PRIMARY KEY (`USER_user_id`),
56 INDEX `fk_ACTIVITY_LOG_USER1_idx` (`USER_user_id` ASC) VISIBLE,
57 CONSTRAINT `fk_ACTIVITY_LOG_USER1`
58 FOREIGN KEY (`USER_user_id`)
59 REFERENCES `mydb`.`USER` (`user_id`)
60 ON DELETE NO ACTION
61 ON UPDATE NO ACTION)
62ENGINE = InnoDB;
63
64
65-- -----------------------------------------------------
66-- Table `mydb`.`LANGUAGE`
67-- -----------------------------------------------------
68CREATE TABLE IF NOT EXISTS `mydb`.`LANGUAGE` (
69 `language_id` INT NOT NULL,
70 `langunage_name` VARCHAR(45) NOT NULL,
71 PRIMARY KEY (`language_id`))
72ENGINE = InnoDB;
73
74
75-- -----------------------------------------------------
76-- Table `mydb`.`COMPANY`
77-- -----------------------------------------------------
78CREATE TABLE IF NOT EXISTS `mydb`.`COMPANY` (
79 `company_id` INT NOT NULL,
80 `company_name` VARCHAR(45) NOT NULL,
81 `country` VARCHAR(45) NOT NULL,
82 `address` VARCHAR(45) NULL,
83 `contact` VARCHAR(45) NULL,
84 PRIMARY KEY (`company_id`))
85ENGINE = InnoDB;
86
87
88-- -----------------------------------------------------
89-- Table `mydb`.`DOCUMENT_TYPE`
90-- -----------------------------------------------------
91CREATE TABLE IF NOT EXISTS `mydb`.`DOCUMENT_TYPE` (
92 `doc_type_id` INT NOT NULL,
93 `doc_type_name` VARCHAR(45) NOT NULL,
94 PRIMARY KEY (`doc_type_id`))
95ENGINE = InnoDB;
96
97
98-- -----------------------------------------------------
99-- Table `mydb`.`DOCUMENT`
100-- -----------------------------------------------------
101CREATE TABLE IF NOT EXISTS `mydb`.`DOCUMENT` (
102 `document_id` INT NOT NULL,
103 `content` LONGTEXT NULL,
104 `commentary` MEDIUMTEXT NULL,
105 `date` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
106 `document_name` VARCHAR(45) NOT NULL,
107 `USER_user_id` INT NOT NULL,
108 `language_ID` INT NOT NULL,
109 `COMPANY_company_id` INT NOT NULL,
110 `DOCUMENT_TYPE_doc_type_id` INT NOT NULL,
111 PRIMARY KEY (`document_id`),
112 INDEX `fk_DOCUMENT_USER1_idx` (`USER_user_id` ASC) VISIBLE,
113 INDEX `fk_DOCUMENT_LANGUAGE1_idx` (`language_ID` ASC) VISIBLE,
114 INDEX `fk_DOCUMENT_COMPANY1_idx` (`COMPANY_company_id` ASC) VISIBLE,
115 INDEX `fk_DOCUMENT_DOCUMENT_TYPE1_idx` (`DOCUMENT_TYPE_doc_type_id` ASC) VISIBLE,
116 CONSTRAINT `fk_DOCUMENT_USER1`
117 FOREIGN KEY (`USER_user_id`)
118 REFERENCES `mydb`.`USER` (`user_id`)
119 ON DELETE NO ACTION
120 ON UPDATE NO ACTION,
121 CONSTRAINT `fk_DOCUMENT_LANGUAGE1`
122 FOREIGN KEY (`language_ID`)
123 REFERENCES `mydb`.`LANGUAGE` (`language_id`)
124 ON DELETE NO ACTION
125 ON UPDATE NO ACTION,
126 CONSTRAINT `fk_DOCUMENT_COMPANY1`
127 FOREIGN KEY (`COMPANY_company_id`)
128 REFERENCES `mydb`.`COMPANY` (`company_id`)
129 ON DELETE NO ACTION
130 ON UPDATE NO ACTION,
131 CONSTRAINT `fk_DOCUMENT_DOCUMENT_TYPE1`
132 FOREIGN KEY (`DOCUMENT_TYPE_doc_type_id`)
133 REFERENCES `mydb`.`DOCUMENT_TYPE` (`doc_type_id`)
134 ON DELETE NO ACTION
135 ON UPDATE NO ACTION)
136ENGINE = InnoDB;
137
138
139SET SQL_MODE=@OLD_SQL_MODE;
140SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
141SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;