João Vitor Bernardi Severo| Home | Escopo e Cronograma | Processo | Design/Mockups | Configuração | Arquitetura | Código | BD | Qualidade | Utilização | Contratos |
|---|
Banco de Dados
Descrição
A modelagem, documentação e organização do banco de dados se situará nessa seção.
Sumário
Modelagem
Esquema Conceitual
Através do esquema conceitual é possível melhor entender as relações entre as tabelas, podemos ver, por exemplo, que um Course tem zero ou mais Degree associados à ele como descrito na realação entre eles.
Outra informação interessante que este modelo nos dá é que pode ser que um estudante siga vários professores, assim como pode não haver nenhum aluno seguindo nenhum professor. Ou o h_index que é só presente no professor e, por isso, foi feita uma especialização de usuário para aluno e professores.
Nós começamos a ver algumas regras de negócio já através desse esquema conceitual que vai nos ajudar e guiar a nossa implementação futuramente.
Esquema Lógico
O esquema lógico nos traz informações mais relacionadas a implementação no banco de fato. Então, a nomenclatura das foreign keys, colunas automáticas de updatedAt e createdAt já são facilmente vistas aqui.
Implementação
-- MySQL Script generated by MySQL Workbench -- Tue Apr 5 00:06:38 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 mydb
-- 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 DEFAULT 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.experiences
CREATE TABLE IF NOT EXISTS ucon.experiences (
id INT NOT NULL AUTO_INCREMENT,
role VARCHAR(64) NOT NULL,
location VARCHAR(64) NOT NULL,
from DATETIME NOT NULL,
to DATETIME NULL,
createdAt DATETIME NOT NULL,
updatedAt DATETIME NOT NULL,
userId INT NOT NULL,
PRIMARY KEY (id),
INDEX userId (userId ASC) VISIBLE,
CONSTRAINT experiences_ibfk_1
FOREIGN KEY (userId)
REFERENCES ucon.users (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;