Skip to content

GitLab

banco_dados · Changes

Page history
Update banco_dados authored by Jhonata Saraiva Peres's avatar Jhonata Saraiva Peres
Hide whitespace changes
Inline Side-by-side
banco_dados.md
View page @ c5f21485
......@@ -18,13 +18,384 @@ A modelagem, documentação e organização do banco de dados se situará nessa
### Esquema Conceitual
TBD
![image](uploads/566a01d6ecc5e1a5362679137e5949f3/image.png)
### Esquema Lógico
TBD
[BD_ER.pdf](uploads/e1b8e19f7644dbf62a6682d60b3c8e4d/BD_ER.pdf)
![BD_ER](uploads/cdcf4487a086ec6817e57db7a7b9d856/BD_ER.png)
### Implementação
TBD
`-- MySQL Script generated by MySQL Workbench
-- Sun Apr 3 20:23:49 2022
-- Model: New Model Version: 1.0
-- MySQL Workbench Forward Engineering
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @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';
-- -----------------------------------------------------
-- Schema ucon
-- -----------------------------------------------------
-- -----------------------------------------------------
-- Schema ucon
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `ucon` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci ;
USE `ucon` ;
-- -----------------------------------------------------
-- Table `ucon`.`courses`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `ucon`.`courses` (
`id` INT NOT NULL AUTO_INCREMENT,
`course` VARCHAR(255) NOT NULL,
`createdAt` DATETIME NOT NULL,
`updatedAt` DATETIME NOT NULL,
PRIMARY KEY (`id`),
UNIQUE INDEX `course` (`course` ASC) VISIBLE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci;
-- -----------------------------------------------------
-- Table `ucon`.`degree_types`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `ucon`.`degree_types` (
`id` INT NOT NULL AUTO_INCREMENT,
`degreeType` VARCHAR(255) NOT NULL,
`createdAt` DATETIME NOT NULL,
`updatedAt` DATETIME NOT NULL,
PRIMARY KEY (`id`),
UNIQUE INDEX `degreeType` (`degreeType` ASC) VISIBLE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci;
-- -----------------------------------------------------
-- Table `ucon`.`genders`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `ucon`.`genders` (
`id` INT NOT NULL AUTO_INCREMENT,
`gender` VARCHAR(64) NOT NULL,
`createdAt` DATETIME NOT NULL,
`updatedAt` DATETIME NOT NULL,
PRIMARY KEY (`id`),
UNIQUE INDEX `gender` (`gender` ASC) VISIBLE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci;
-- -----------------------------------------------------
-- Table `ucon`.`users`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `ucon`.`users` (
`id` INT NOT NULL AUTO_INCREMENT,
`email` VARCHAR(255) NOT NULL,
`password` VARCHAR(255) NOT NULL,
`full_name` VARCHAR(64) NOT NULL,
`lattes_id` VARCHAR(255) NULL,
`birth_date` DATETIME NOT NULL,
`createdAt` DATETIME NOT NULL,
`updatedAt` DATETIME NOT NULL,
`genderId` INT NOT NULL,
PRIMARY KEY (`id`),
UNIQUE INDEX `email` (`email` ASC) VISIBLE,
INDEX `genderId` (`genderId` ASC) VISIBLE,
CONSTRAINT `users_ibfk_1`
FOREIGN KEY (`genderId`)
REFERENCES `ucon`.`genders` (`id`)
ON DELETE CASCADE
ON UPDATE CASCADE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci;
-- -----------------------------------------------------
-- Table `ucon`.`teachers`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `ucon`.`teachers` (
`h_index` INT NULL DEFAULT NULL,
`createdAt` DATETIME NOT NULL,
`updatedAt` DATETIME NOT NULL,
`userId` INT NOT NULL,
PRIMARY KEY (`userId`),
CONSTRAINT `teachers_ibfk_1`
FOREIGN KEY (`userId`)
REFERENCES `ucon`.`users` (`id`)
ON UPDATE CASCADE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci;
-- -----------------------------------------------------
-- Table `ucon`.`educational_institutions`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `ucon`.`educational_institutions` (
`id` INT NOT NULL AUTO_INCREMENT,
`eductionalInstitution` VARCHAR(255) NOT NULL,
`createdAt` DATETIME NOT NULL,
`updatedAt` DATETIME NOT NULL,
PRIMARY KEY (`id`),
UNIQUE INDEX `eductionalInstitution` (`eductionalInstitution` ASC) VISIBLE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci;
-- -----------------------------------------------------
-- Table `ucon`.`degrees`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `ucon`.`degrees` (
`id` INT NOT NULL AUTO_INCREMENT,
`createdAt` DATETIME NOT NULL,
`updatedAt` DATETIME NOT NULL,
`teacherId` INT NOT NULL,
`courseId` INT NOT NULL,
`degreeTypeId` INT NOT NULL,
`educationalInstitutionId` INT NOT NULL,
PRIMARY KEY (`id`),
INDEX `teacherId` (`teacherId` ASC) VISIBLE,
INDEX `courseId` (`courseId` ASC) VISIBLE,
INDEX `degreeTypeId` (`degreeTypeId` ASC) VISIBLE,
INDEX `educationalInstitutionId` (`educationalInstitutionId` ASC) VISIBLE,
CONSTRAINT `degrees_ibfk_1`
FOREIGN KEY (`teacherId`)
REFERENCES `ucon`.`teachers` (`userId`)
ON DELETE CASCADE
ON UPDATE CASCADE,
CONSTRAINT `degrees_ibfk_2`
FOREIGN KEY (`courseId`)
REFERENCES `ucon`.`courses` (`id`)
ON DELETE CASCADE
ON UPDATE CASCADE,
CONSTRAINT `degrees_ibfk_3`
FOREIGN KEY (`degreeTypeId`)
REFERENCES `ucon`.`degree_types` (`id`)
ON DELETE CASCADE
ON UPDATE CASCADE,
CONSTRAINT `degrees_ibfk_4`
FOREIGN KEY (`educationalInstitutionId`)
REFERENCES `ucon`.`educational_institutions` (`id`)
ON DELETE CASCADE
ON UPDATE CASCADE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci;
-- -----------------------------------------------------
-- Table `ucon`.`projects`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `ucon`.`projects` (
`id` INT NOT NULL AUTO_INCREMENT,
`name` VARCHAR(64) NOT NULL,
`description` VARCHAR(255) NOT NULL,
`openned` TINYINT(1) NOT NULL,
`createdAt` DATETIME NOT NULL,
`updatedAt` DATETIME NOT NULL,
`teacherId` INT NOT NULL,
PRIMARY KEY (`id`),
INDEX `teacherId` (`teacherId` ASC) VISIBLE,
CONSTRAINT `projects_ibfk_1`
FOREIGN KEY (`teacherId`)
REFERENCES `ucon`.`teachers` (`userId`)
ON DELETE CASCADE
ON UPDATE CASCADE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci;
-- -----------------------------------------------------
-- Table `ucon`.`opportunities`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `ucon`.`opportunities` (
`id` INT NOT NULL AUTO_INCREMENT,
`type` VARCHAR(64) NOT NULL,
`description` VARCHAR(255) NOT NULL,
`semesterStart` INT NULL DEFAULT NULL,
`semesterEnd` INT NULL DEFAULT NULL,
`openned` TINYINT(1) NOT NULL,
`createdAt` DATETIME NOT NULL,
`updatedAt` DATETIME NOT NULL,
`projectId` INT NOT NULL,
PRIMARY KEY (`id`),
INDEX `projectId` (`projectId` ASC) VISIBLE,
CONSTRAINT `opportunities_ibfk_1`
FOREIGN KEY (`projectId`)
REFERENCES `ucon`.`projects` (`id`)
ON DELETE CASCADE
ON UPDATE CASCADE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci;
-- -----------------------------------------------------
-- Table `ucon`.`students`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `ucon`.`students` (
`semester` INT NULL DEFAULT NULL,
`createdAt` DATETIME NOT NULL,
`updatedAt` DATETIME NOT NULL,
`userId` INT NOT NULL,
`courseId` INT NOT NULL,
`eductionalInstitutionId` INT NOT NULL,
PRIMARY KEY (`userId`),
INDEX `courseId` (`courseId` ASC) VISIBLE,
INDEX `eductionalInstitutionId` (`eductionalInstitutionId` ASC) VISIBLE,
CONSTRAINT `students_ibfk_1`
FOREIGN KEY (`userId`)
REFERENCES `ucon`.`users` (`id`)
ON UPDATE CASCADE,
CONSTRAINT `students_ibfk_2`
FOREIGN KEY (`courseId`)
REFERENCES `ucon`.`courses` (`id`)
ON DELETE CASCADE
ON UPDATE CASCADE,
CONSTRAINT `students_ibfk_3`
FOREIGN KEY (`eductionalInstitutionId`)
REFERENCES `ucon`.`educational_institutions` (`id`)
ON DELETE CASCADE
ON UPDATE CASCADE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci;
-- -----------------------------------------------------
-- Table `ucon`.`matches`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `ucon`.`matches` (
`id` INT NOT NULL AUTO_INCREMENT,
`from` VARCHAR(1) NOT NULL,
`status` VARCHAR(32) NOT NULL,
`createdAt` DATETIME NOT NULL,
`updatedAt` DATETIME NOT NULL,
`opportunityId` INT NOT NULL,
`studentId` INT NOT NULL,
PRIMARY KEY (`id`),
INDEX `opportunityId` (`opportunityId` ASC) VISIBLE,
INDEX `studentId` (`studentId` ASC) VISIBLE,
CONSTRAINT `matches_ibfk_1`
FOREIGN KEY (`opportunityId`)
REFERENCES `ucon`.`opportunities` (`id`)
ON DELETE CASCADE
ON UPDATE CASCADE,
CONSTRAINT `matches_ibfk_2`
FOREIGN KEY (`studentId`)
REFERENCES `ucon`.`students` (`userId`)
ON DELETE CASCADE
ON UPDATE CASCADE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci;
-- -----------------------------------------------------
-- Table `ucon`.`messages`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `ucon`.`messages` (
`id` INT NOT NULL AUTO_INCREMENT,
`message` VARCHAR(512) NOT NULL,
`createdAt` DATETIME NOT NULL,
`updatedAt` DATETIME NOT NULL,
`matchId` INT NOT NULL,
`authorId` INT NOT NULL,
PRIMARY KEY (`id`),
INDEX `matchId` (`matchId` ASC) VISIBLE,
INDEX `authorId` (`authorId` ASC) VISIBLE,
CONSTRAINT `messages_ibfk_1`
FOREIGN KEY (`matchId`)
REFERENCES `ucon`.`matches` (`id`)
ON DELETE CASCADE
ON UPDATE CASCADE,
CONSTRAINT `messages_ibfk_2`
FOREIGN KEY (`authorId`)
REFERENCES `ucon`.`users` (`id`)
ON DELETE CASCADE
ON UPDATE CASCADE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci;
-- -----------------------------------------------------
-- Table `ucon`.`research_areas`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `ucon`.`research_areas` (
`id` INT NOT NULL AUTO_INCREMENT,
`researchArea` VARCHAR(64) NOT NULL,
`createdAt` DATETIME NOT NULL,
`updatedAt` DATETIME NOT NULL,
PRIMARY KEY (`id`),
UNIQUE INDEX `researchArea` (`researchArea` ASC) VISIBLE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci;
-- -----------------------------------------------------
-- Table `ucon`.`students_follow_teachers`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `ucon`.`students_follow_teachers` (
`createdAt` DATETIME NOT NULL,
`updatedAt` DATETIME NOT NULL,
`studentUserId` INT NOT NULL,
`teacherUserId` INT NOT NULL,
PRIMARY KEY (`studentUserId`, `teacherUserId`),
INDEX `teacherUserId` (`teacherUserId` ASC) VISIBLE,
CONSTRAINT `students_follow_teachers_ibfk_1`
FOREIGN KEY (`studentUserId`)
REFERENCES `ucon`.`students` (`userId`)
ON DELETE CASCADE
ON UPDATE CASCADE,
CONSTRAINT `students_follow_teachers_ibfk_2`
FOREIGN KEY (`teacherUserId`)
REFERENCES `ucon`.`teachers` (`userId`)
ON DELETE CASCADE
ON UPDATE CASCADE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci;
-- -----------------------------------------------------
-- Table `ucon`.`users_has_research_areas`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `ucon`.`users_has_research_areas` (
`createdAt` DATETIME NOT NULL,
`updatedAt` DATETIME NOT NULL,
`researchAreaId` INT NOT NULL,
`userId` INT NOT NULL,
PRIMARY KEY (`researchAreaId`, `userId`),
INDEX `userId` (`userId` ASC) VISIBLE,
CONSTRAINT `users_has_research_areas_ibfk_1`
FOREIGN KEY (`researchAreaId`)
REFERENCES `ucon`.`research_areas` (`id`)
ON DELETE CASCADE
ON UPDATE CASCADE,
CONSTRAINT `users_has_research_areas_ibfk_2`
FOREIGN KEY (`userId`)
REFERENCES `ucon`.`users` (`id`)
ON DELETE CASCADE
ON UPDATE CASCADE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_0900_ai_ci;
SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
`