DB Nuevo Ingreso

nuevo_ingreso.sql

-- MySQL Script generated by MySQL Dump
-- Tue Oct 11 23:01:35 2022
-- Model: New Model    Version: 1.0

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 nuevo_ingreso
-- -----------------------------------------------------
DROP SCHEMA IF EXISTS `nuevo_ingreso` ;

-- -----------------------------------------------------
-- Schema nuevo_ingreso
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `nuevo_ingreso` DEFAULT CHARACTER SET utf8mb4 ;
USE `nuevo_ingreso` ;

-- -----------------------------------------------------
-- Table `nuevo_ingreso`.`planteles`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `nuevo_ingreso`.`planteles` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `nombre_plantel` VARCHAR(45) NOT NULL,
  `direccion` VARCHAR(120) NOT NULL,
  `telefono` VARCHAR(15) NULL,
  `fh_creacion` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `fh_modficacion` TIMESTAMP NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `nuevo_ingreso`.`carreras`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `nuevo_ingreso`.`carreras` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `clave` CHAR(5) NOT NULL,
  `nombre_carrera` VARCHAR(60) NOT NULL,
  `fh_creacion` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `fh_modificacion` TIMESTAMP NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `nuevo_ingreso`.`rel_plantel_carreras`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `nuevo_ingreso`.`rel_plantel_carreras` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `planteles_id` INT NOT NULL,
  `carreras_id` INT NOT NULL,
  `fh_creacion` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `fh_modificacion` TIMESTAMP NULL,
  `fh_eliminacion` TIMESTAMP NULL,
  INDEX `fk_rel_plantel_carreras_planteles_idx` (`planteles_id` ASC) VISIBLE,
  INDEX `fk_rel_plantel_carreras_carreras1_idx` (`carreras_id` ASC) VISIBLE,
  PRIMARY KEY (`id`),
  CONSTRAINT `fk_rel_plantel_carreras_planteles`
    FOREIGN KEY (`planteles_id`)
    REFERENCES `nuevo_ingreso`.`planteles` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_rel_plantel_carreras_carreras1`
    FOREIGN KEY (`carreras_id`)
    REFERENCES `nuevo_ingreso`.`carreras` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `nuevo_ingreso`.`alumnos`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `nuevo_ingreso`.`alumnos` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `carrera_id` INT NOT NULL,
  `matricula` VARCHAR(15) NOT NULL,
  `nombre` VARCHAR(30) NOT NULL,
  `ap_paterno` VARCHAR(30) NOT NULL,
  `ap_materno` VARCHAR(30) NOT NULL,
  `edad` TINYINT NOT NULL,
  `sexo` ENUM('Masculino', 'Femenino', 'ND') NOT NULL DEFAULT 'ND',
  `fh_nacimiento` DATE NULL,
  `email` VARCHAR(50) NOT NULL,
  `baja` BIT NOT NULL DEFAULT 0,
  `fh_creacion` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `fh_modificacion` TIMESTAMP NULL,
  `fh_baja` TIMESTAMP NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `matricula_UNIQUE` (`matricula` ASC) VISIBLE,
  INDEX `fk_alumnos_rel_plantel_carreras1_idx` (`carrera_id` ASC) VISIBLE,
  CONSTRAINT `fk_alumnos_rel_plantel_carreras1`
    FOREIGN KEY (`carrera_id`)
    REFERENCES `nuevo_ingreso`.`rel_plantel_carreras` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `nuevo_ingreso`.`grupos`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `nuevo_ingreso`.`grupos` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `semestre` TINYINT NOT NULL DEFAULT 1,
  `nombre_grupo` CHAR(1) NOT NULL,
  `fh_creacion` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `fh_modificacion` TIMESTAMP NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `nuevo_ingreso`.`semestres`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `nuevo_ingreso`.`semestres` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `semestre` TINYINT NOT NULL DEFAULT 1,
  `fh_creacion` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `fh_modificacion` TIMESTAMP NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `nuevo_ingreso`.`turnos`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `nuevo_ingreso`.`turnos` (
  `id` TINYINT NOT NULL AUTO_INCREMENT,
  `nombre_turno` VARCHAR(35) NOT NULL,
  `hr_entrada` TIME NOT NULL DEFAULT NOW(),
  `hr_salida` TIME NOT NULL DEFAULT NOW(),
  `fh_creacion` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `fh_modificacion` TIMESTAMP NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `nuevo_ingreso`.`rel_grupo_semestre_turno`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `nuevo_ingreso`.`rel_grupo_semestre_turno` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `semestres_id` INT NOT NULL,
  `turnos_id` TINYINT NOT NULL,
  `grupos_id` INT NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_rel_grupo_semestre_turno_semestres1_idx` (`semestres_id` ASC) VISIBLE,
  INDEX `fk_rel_grupo_semestre_turno_turnos1_idx` (`turnos_id` ASC) VISIBLE,
  INDEX `fk_rel_grupo_semestre_turno_grupos1_idx` (`grupos_id` ASC) VISIBLE,
  CONSTRAINT `fk_rel_grupo_semestre_turno_semestres1`
    FOREIGN KEY (`semestres_id`)
    REFERENCES `nuevo_ingreso`.`semestres` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_rel_grupo_semestre_turno_turnos1`
    FOREIGN KEY (`turnos_id`)
    REFERENCES `nuevo_ingreso`.`turnos` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_rel_grupo_semestre_turno_grupos1`
    FOREIGN KEY (`grupos_id`)
    REFERENCES `nuevo_ingreso`.`grupos` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `nuevo_ingreso`.`rel_alumno_grupo`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `nuevo_ingreso`.`rel_alumno_grupo` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `alumno_id` BIGINT NOT NULL,
  `rel_grupo_id` INT NOT NULL,
  `fh_creacion` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `fh_modficacion` TIMESTAMP NULL,
  INDEX `fk_rel_alumno_grupo_alumnos1_idx` (`alumno_id` ASC) VISIBLE,
  INDEX `fk_rel_alumno_grupo_semestre_turno_idx` (`alumno_id` ASC) VISIBLE,
  PRIMARY KEY (`id`),
  INDEX `fk_rel_alumno_grupo_rel_grupo_semestre_turno1_idx` (`rel_grupo_id` ASC) VISIBLE,
  CONSTRAINT `fk_rel_alumno_grupo_alumnos1`
    FOREIGN KEY (`alumno_id`)
    REFERENCES `nuevo_ingreso`.`alumnos` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_rel_alumno_grupo_rel_grupo_semestre_turno1`
    FOREIGN KEY (`rel_grupo_id`)
    REFERENCES `nuevo_ingreso`.`rel_grupo_semestre_turno` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `nuevo_ingreso`.`materias`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `nuevo_ingreso`.`materias` (
  `id` TINYINT NOT NULL AUTO_INCREMENT,
  `nombre_materia` VARCHAR(35) NOT NULL,
  `horas` TINYINT NOT NULL DEFAULT 120,
  `es_opcional` BIT NOT NULL DEFAULT 0,
  `fh_creacion` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `fh_modificacion` TIMESTAMP NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `nuevo_ingreso`.`rel_materias_carrera`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `nuevo_ingreso`.`rel_materias_carrera` (
  `carrera_id` INT NOT NULL,
  `materia_id` TINYINT NOT NULL,
  `semestre_id` INT NOT NULL,
  `fh_creacion` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `fh_modificacion` TIMESTAMP NULL,
  `fh_eliminacion` TIMESTAMP NULL,
  INDEX `fk_rel_materias_carrera_carreras1_idx` (`carrera_id` ASC) VISIBLE,
  INDEX `fk_rel_materias_carrera_materias1_idx` (`materia_id` ASC) VISIBLE,
  INDEX `fk_rel_materias_carrera_semestres1_idx` (`semestre_id` ASC) VISIBLE,
  CONSTRAINT `fk_rel_materias_carrera_carreras1`
    FOREIGN KEY (`carrera_id`)
    REFERENCES `nuevo_ingreso`.`carreras` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_rel_materias_carrera_materias1`
    FOREIGN KEY (`materia_id`)
    REFERENCES `nuevo_ingreso`.`materias` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_rel_materias_carrera_semestres1`
    FOREIGN KEY (`semestre_id`)
    REFERENCES `nuevo_ingreso`.`semestres` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `nuevo_ingreso`.`profesores`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `nuevo_ingreso`.`profesores` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `cedula` VARCHAR(25) NOT NULL,
  `titulo` VARCHAR(60) NOT NULL,
  `nombre` VARCHAR(30) NOT NULL,
  `ap_paterno` VARCHAR(30) NOT NULL,
  `ap_materno` VARCHAR(30) NOT NULL,
  `edad` TINYINT NOT NULL,
  `sexo` ENUM('Masculino', 'Femenino', 'ND') NOT NULL DEFAULT 'ND',
  `fh_nacimiento` DATE NULL,
  `email` VARCHAR(50) NOT NULL,
  `fh_creacion` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `fh_modificacion` TIMESTAMP NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `matricula_UNIQUE` (`cedula` ASC) VISIBLE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `nuevo_ingreso`.`rel_profesor_materias`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `nuevo_ingreso`.`rel_profesor_materias` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `profesor_id` BIGINT NOT NULL,
  `materias_id` TINYINT NOT NULL,
  `fh_creacion` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `fh_modificacion` TIMESTAMP NULL,
  `fh_eliminacion` TIMESTAMP NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_rel_profesor_materias_profesores1_idx` (`profesor_id` ASC) VISIBLE,
  INDEX `fk_rel_profesor_materias_materias1_idx` (`materias_id` ASC) VISIBLE,
  CONSTRAINT `fk_rel_profesor_materias_profesores1`
    FOREIGN KEY (`profesor_id`)
    REFERENCES `nuevo_ingreso`.`profesores` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_rel_profesor_materias_materias1`
    FOREIGN KEY (`materias_id`)
    REFERENCES `nuevo_ingreso`.`materias` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `nuevo_ingreso`.`horarios`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `nuevo_ingreso`.`horarios` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `rel_grupo_st_id` INT NOT NULL,
  `rel_profesor_materia_id` INT NOT NULL,
  `hr_inicio` TIME NOT NULL DEFAULT NOW(),
  `hr_fin` TIME NOT NULL DEFAULT NOW(),
  PRIMARY KEY (`id`),
  INDEX `fk_horarios_rel_grupo_semestre_turno1_idx` (`rel_grupo_st_id` ASC) VISIBLE,
  INDEX `fk_horarios_rel_profesor_materias1_idx` (`rel_profesor_materia_id` ASC) VISIBLE,
  CONSTRAINT `fk_horarios_rel_grupo_semestre_turno1`
    FOREIGN KEY (`rel_grupo_st_id`)
    REFERENCES `nuevo_ingreso`.`rel_grupo_semestre_turno` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_horarios_rel_profesor_materias1`
    FOREIGN KEY (`rel_profesor_materia_id`)
    REFERENCES `nuevo_ingreso`.`rel_profesor_materias` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `nuevo_ingreso`.`calificaciones`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `nuevo_ingreso`.`calificaciones` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `rel_alumno_grupo_id` BIGINT NOT NULL,
  `rel_pmateria_id` INT NOT NULL,
  `calificacion` TINYINT NOT NULL DEFAULT 0,
  `notas` TEXT NULL,
  `fh_creacion` TIMESTAMP NOT NULL,
  `fh_modificacion` TIMESTAMP NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_calificaciones_rel_alumno_grupo1_idx` (`rel_alumno_grupo_id` ASC) VISIBLE,
  INDEX `fk_calificaciones_rel_profesor_materias1_idx` (`rel_pmateria_id` ASC) VISIBLE,
  CONSTRAINT `fk_calificaciones_rel_alumno_grupo1`
    FOREIGN KEY (`rel_alumno_grupo_id`)
    REFERENCES `nuevo_ingreso`.`rel_alumno_grupo` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_calificaciones_rel_profesor_materias1`
    FOREIGN KEY (`rel_pmateria_id`)
    REFERENCES `nuevo_ingreso`.`rel_profesor_materias` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


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