search

AlfaBD / ibpl-alfa-bd-2021

7 stars 6 forks source link

Modelagem MER de avaliação de fluencia de leitura #137

Open wylli opened 3 years ago

wylli commented 3 years ago

Criar Modelo Entidade Relacionamento MER conforme análise #136

wylli commented 3 years ago

MER atualizado junto aos outros TS:

image

SQL MER:

`-- MySQL Script generated by MySQL Workbench -- Wed May 26 11:03:45 2021 -- 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 alfabd

DROP SCHEMA IF EXISTS alfabd ;

-- Schema alfabd

CREATE SCHEMA IF NOT EXISTS alfabd DEFAULT CHARACTER SET utf8 ; SHOW WARNINGS; USE alfabd ;

-- Table alfabd.answer

DROP TABLE IF EXISTS alfabd.answer ;

SHOW WARNINGS; CREATE TABLE IF NOT EXISTS alfabd.answer ( ans_id INT NOT NULL, user_use_id INT NOT NULL, user_role_rol_id INT NOT NULL, task_tas_id INT NOT NULL, task_user_use_id INT NOT NULL, task_user_role_rol_id INT NOT NULL, task_question_que_id INT NOT NULL, ans_answer VARCHAR(45) NOT NULL, ans_score FLOAT NULL, ans_time_to_answer DATETIME NOT NULL, ans_attempt_number INT NOT NULL, ans_record_saund_path VARCHAR(45) NOT NULL, ans_modified_at DATETIME NULL, PRIMARY KEY (ans_id, user_use_id, user_role_rol_id, task_tas_id, task_user_use_id, task_user_role_rol_id, task_question_que_id), UNIQUE INDEX ans_id_UNIQUE (ans_id ASC) VISIBLE, INDEX fk_answer_user1_idx (user_use_id ASC, user_role_rol_id ASC) VISIBLE, INDEX fk_answer_task1_idx (task_tas_id ASC, task_user_use_id ASC, task_user_role_rol_id ASC, task_question_que_id ASC) VISIBLE, CONSTRAINT fk_answer_user1 FOREIGN KEY (user_use_id , user_role_rol_id) REFERENCES alfabd.user (use_id , role_rol_id) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT fk_answer_task1 FOREIGN KEY (task_tas_id , task_user_use_id , task_user_role_rol_id , task_question_que_id) REFERENCES alfabd.task (tas_id , user_use_id , user_role_rol_id , question_que_id) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB;

SHOW WARNINGS;

-- Table alfabd.attendence

DROP TABLE IF EXISTS alfabd.attendence ;

SHOW WARNINGS; CREATE TABLE IF NOT EXISTS alfabd.attendence ( att_id INT NOT NULL, user_use_id INT NOT NULL, user_role_rol_id INT NOT NULL, class_cla_id INT NOT NULL, class_scholl_sch_id INT NOT NULL, PRIMARY KEY (att_id, user_use_id, user_role_rol_id, class_cla_id, class_scholl_sch_id), UNIQUE INDEX att_id_UNIQUE (att_id ASC) VISIBLE, INDEX fk_attendence_user1_idx (user_use_id ASC, user_role_rol_id ASC) VISIBLE, INDEX fk_attendence_class1_idx (class_cla_id ASC, class_scholl_sch_id ASC) VISIBLE, CONSTRAINT fk_attendence_user1 FOREIGN KEY (user_use_id , user_role_rol_id) REFERENCES alfabd.user (use_id , role_rol_id) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT fk_attendence_class1 FOREIGN KEY (class_cla_id , class_scholl_sch_id) REFERENCES alfabd.class (cla_id , scholl_sch_id) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB;

SHOW WARNINGS;

-- Table alfabd.class

DROP TABLE IF EXISTS alfabd.class ;

SHOW WARNINGS; CREATE TABLE IF NOT EXISTS alfabd.class ( cla_id INT NOT NULL, scholl_sch_id INT NOT NULL, cla_name VARCHAR(45) NOT NULL, cla_status VARCHAR(45) NOT NULL, user_use_id INT NOT NULL, user_role_rol_id INT NOT NULL, PRIMARY KEY (cla_id, scholl_sch_id, user_use_id, user_role_rol_id), UNIQUE INDEX cla_id_UNIQUE (cla_id ASC) VISIBLE, INDEX fk_class_scholl1_idx (scholl_sch_id ASC) VISIBLE, INDEX fk_class_user1_idx (user_use_id ASC, user_role_rol_id ASC) VISIBLE, CONSTRAINT fk_class_scholl1 FOREIGN KEY (scholl_sch_id) REFERENCES alfabd.scholl (sch_id) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT fk_class_user1 FOREIGN KEY (user_use_id , user_role_rol_id) REFERENCES alfabd.user (use_id , role_rol_id) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB;

SHOW WARNINGS;

-- Table alfabd.fluency_metrics

DROP TABLE IF EXISTS alfabd.fluency_metrics ;

SHOW WARNINGS; CREATE TABLE IF NOT EXISTS alfabd.fluency_metrics ( flu_id INT NOT NULL, flu_velocity FLOAT NOT NULL, flu_precision FLOAT NOT NULL, flu_pauses FLOAT NOT NULL, answer_ans_id INT NOT NULL, PRIMARY KEY (flu_id, answer_ans_id), UNIQUE INDEX flu_id_UNIQUE (flu_id ASC) VISIBLE, INDEX fk_fluency_metrics_answer1_idx (answer_ans_id ASC) VISIBLE, CONSTRAINT fk_fluency_metrics_answer1 FOREIGN KEY (answer_ans_id) REFERENCES alfabd.answer (ans_id) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB;

SHOW WARNINGS;

-- Table alfabd.question

DROP TABLE IF EXISTS alfabd.question ;

SHOW WARNINGS; CREATE TABLE IF NOT EXISTS alfabd.question ( que_id INT NOT NULL, user_use_id INT NOT NULL, user_role_rol_id INT NOT NULL, que_question VARCHAR(255) NOT NULL, que_options VARCHAR(255) NOT NULL, que_answer VARCHAR(45) NOT NULL, que_score VARCHAR(45) NOT NULL, que_category VARCHAR(45) NOT NULL, que_pictureUrl VARCHAR(45) NULL, PRIMARY KEY (que_id), UNIQUE INDEX que_id_UNIQUE (que_id ASC) VISIBLE, INDEX fk_question_user1_idx (user_use_id ASC, user_role_rol_id ASC) VISIBLE, CONSTRAINT fk_question_user1 FOREIGN KEY (user_use_id , user_role_rol_id) REFERENCES alfabd.user (use_id , role_rol_id) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB;

SHOW WARNINGS;

-- Table alfabd.role

DROP TABLE IF EXISTS alfabd.role ;

SHOW WARNINGS; CREATE TABLE IF NOT EXISTS alfabd.role ( rol_id INT NOT NULL, rol_name VARCHAR(45) NOT NULL, PRIMARY KEY (rol_id), UNIQUE INDEX rol_id_UNIQUE (rol_id ASC) VISIBLE) ENGINE = InnoDB;

SHOW WARNINGS;

-- Table alfabd.scholl

DROP TABLE IF EXISTS alfabd.scholl ;

SHOW WARNINGS; CREATE TABLE IF NOT EXISTS alfabd.scholl ( sch_id INT NOT NULL, sch_cnpj VARCHAR(45) NOT NULL, sch_name VARCHAR(45) NOT NULL, sch_mecNumber VARCHAR(45) NOT NULL, sch_adress VARCHAR(45) NOT NULL, sch_city VARCHAR(45) NOT NULL, sch_state VARCHAR(45) NOT NULL, sch_status VARCHAR(45) NULL, PRIMARY KEY (sch_id), UNIQUE INDEX sch_id_UNIQUE (sch_id ASC) VISIBLE, UNIQUE INDEX sch_cnpj_UNIQUE (sch_cnpj ASC) VISIBLE) ENGINE = InnoDB;

SHOW WARNINGS;

-- Table alfabd.task

DROP TABLE IF EXISTS alfabd.task ;

SHOW WARNINGS; CREATE TABLE IF NOT EXISTS alfabd.task ( tas_id INT NOT NULL, tas_createDate DATETIME NOT NULL, user_use_id INT NULL, user_role_rol_id INT NULL, question_que_id INT NOT NULL, PRIMARY KEY (tas_id, user_use_id, user_role_rol_id, question_que_id), UNIQUE INDEX tas_id_UNIQUE (tas_id ASC) VISIBLE, INDEX fk_task_user1_idx (user_use_id ASC, user_role_rol_id ASC) VISIBLE, INDEX fk_task_question1_idx (question_que_id ASC) VISIBLE, CONSTRAINT fk_task_user1 FOREIGN KEY (user_use_id , user_role_rol_id) REFERENCES alfabd.user (use_id , role_rol_id) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT fk_task_question1 FOREIGN KEY (question_que_id) REFERENCES alfabd.question (que_id) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB;

SHOW WARNINGS;

-- Table alfabd.user

DROP TABLE IF EXISTS alfabd.user ;

SHOW WARNINGS; CREATE TABLE IF NOT EXISTS alfabd.user ( use_id INT NOT NULL, use_cpf VARCHAR(45) NOT NULL, use_name VARCHAR(45) NOT NULL, use_email VARCHAR(45) NOT NULL, use_password_hash VARCHAR(45) NOT NULL, use_birthDate DATETIME NOT NULL, use_telephone VARCHAR(45) NULL, use_primaryRole VARCHAR(45) NULL, use_secondaryRole VARCHAR(45) NULL, role_rol_id INT NOT NULL, PRIMARY KEY (use_id, role_rol_id), UNIQUE INDEX use_id_UNIQUE (use_id ASC) VISIBLE, UNIQUE INDEX use_cpf_UNIQUE (use_cpf ASC) VISIBLE, INDEX fk_user_role_idx (role_rol_id ASC) VISIBLE, CONSTRAINT fk_user_role FOREIGN KEY (role_rol_id) REFERENCES alfabd.role (rol_id) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB;

SHOW WARNINGS;

SET SQL_MODE=@OLD_SQL_MODE; SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS; SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS; `