AttendanceManager.sql

Continguts del fitxer

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='TRADITIONAL';

DROP SCHEMA IF EXISTS `amdb` ;
CREATE SCHEMA IF NOT EXISTS `amdb` DEFAULT CHARACTER SET utf8 ;
USE `amdb`;

-- -----------------------------------------------------
-- Table `amdb`.`teacher`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `amdb`.`teacher` ;

CREATE  TABLE IF NOT EXISTS `amdb`.`teacher` (
  `idteacher` INT NOT NULL AUTO_INCREMENT ,
  `login` VARCHAR(45) NOT NULL ,
  `password` VARCHAR(45) NOT NULL COMMENT 'SHA1(\"password\")' ,
  `fullname` VARCHAR(45) NULL ,
  `email` VARCHAR(255) NULL ,
  `comment` VARCHAR(255) NULL ,
  `admin` TINYINT(1) NOT NULL DEFAULT FALSE COMMENT 'Admin users can edit:\nteacher, student, subject, group and classblock' ,
  PRIMARY KEY (`idteacher`) ,
  UNIQUE INDEX `login` (`login` ASC) )
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `amdb`.`student`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `amdb`.`student` ;

CREATE  TABLE IF NOT EXISTS `amdb`.`student` (
  `idstudent` INT NOT NULL AUTO_INCREMENT ,
  `surname1` VARCHAR(45) NULL ,
  `surname2` VARCHAR(45) NULL ,
  `name` VARCHAR(45) NOT NULL ,
  `email` VARCHAR(255) NULL DEFAULT NULL COMMENT 'para la notificación de faltas e incidencias' ,
  `smsphone` VARCHAR(45) NULL DEFAULT NULL COMMENT 'Teléfono para notificaciones' ,
  PRIMARY KEY (`idstudent`) )
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `amdb`.`subject`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `amdb`.`subject` ;

CREATE  TABLE IF NOT EXISTS `amdb`.`subject` (
  `idsubject` INT NOT NULL AUTO_INCREMENT ,
  `name` VARCHAR(45) NOT NULL ,
  `comment` VARCHAR(255) NULL ,
  PRIMARY KEY (`idsubject`) ,
  UNIQUE INDEX `name` (`name` ASC) )
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `amdb`.`group`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `amdb`.`group` ;

CREATE  TABLE IF NOT EXISTS `amdb`.`group` (
  `idgroup` INT NOT NULL AUTO_INCREMENT ,
  `name` VARCHAR(45) NOT NULL ,
  `comment` VARCHAR(255) NULL ,
  `teacher_idteacher` INT NULL ,
  PRIMARY KEY (`idgroup`) ,
  UNIQUE INDEX `name` (`name` ASC) ,
  INDEX `fk_group_teacher1` (`teacher_idteacher` ASC) ,
  CONSTRAINT `fk_group_teacher1`
    FOREIGN KEY (`teacher_idteacher` )
    REFERENCES `amdb`.`teacher` (`idteacher` )
    ON DELETE SET NULL
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `amdb`.`term`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `amdb`.`term` ;

CREATE  TABLE IF NOT EXISTS `amdb`.`term` (
  `idterm` INT NOT NULL AUTO_INCREMENT ,
  `name` VARCHAR(45) NOT NULL ,
  `comment` VARCHAR(255) NULL ,
  `start` DATE NOT NULL ,
  `end` DATE NOT NULL ,
  PRIMARY KEY (`idterm`) ,
  UNIQUE INDEX `name` (`name` ASC) )
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `amdb`.`classblock`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `amdb`.`classblock` ;

CREATE  TABLE IF NOT EXISTS `amdb`.`classblock` (
  `idclassblock` INT NOT NULL AUTO_INCREMENT ,
  `teacher_idteacher` INT NOT NULL ,
  `subject_idsubject` INT NOT NULL ,
  `group_idgroup` INT NOT NULL ,
  `term_idterm` INT NOT NULL ,
  `day_of_week` INT NOT NULL COMMENT '1=sunday,\n2=monday,\n...' ,
  `start` TIME NOT NULL ,
  `end` TIME NOT NULL ,
  PRIMARY KEY (`idclassblock`) ,
  INDEX `fk_classblock_subject1` (`subject_idsubject` ASC) ,
  INDEX `fk_classblock_group1` (`group_idgroup` ASC) ,
  INDEX `fk_classblock_term1` (`term_idterm` ASC) ,
  INDEX `fk_classblock_teacher1` (`teacher_idteacher` ASC) ,
  CONSTRAINT `fk_classblock_subject1`
    FOREIGN KEY (`subject_idsubject` )
    REFERENCES `amdb`.`subject` (`idsubject` )
    ON DELETE CASCADE
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_classblock_group1`
    FOREIGN KEY (`group_idgroup` )
    REFERENCES `amdb`.`group` (`idgroup` )
    ON DELETE CASCADE
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_classblock_term1`
    FOREIGN KEY (`term_idterm` )
    REFERENCES `amdb`.`term` (`idterm` )
    ON DELETE CASCADE
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_classblock_teacher1`
    FOREIGN KEY (`teacher_idteacher` )
    REFERENCES `amdb`.`teacher` (`idteacher` )
    ON DELETE CASCADE
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `amdb`.`miss`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `amdb`.`miss` ;

CREATE  TABLE IF NOT EXISTS `amdb`.`miss` (
  `idmiss` INT NOT NULL AUTO_INCREMENT ,
  `type` INT NOT NULL COMMENT 'Permite distinguir entre: falta, falta justificada, incidencia, expulsión\n' ,
  `date` DATE NOT NULL ,
  `comment` VARCHAR(255) NULL ,
  `student_idstudent` INT NOT NULL ,
  `teacher_idteacher` INT NOT NULL ,
  `subject_idsubject` INT NOT NULL ,
  `classblock_idclassblock` INT NOT NULL ,
  PRIMARY KEY (`idmiss`) ,
  INDEX `fk_miss_student1` (`student_idstudent` ASC) ,
  INDEX `fk_miss_teacher1` (`teacher_idteacher` ASC) ,
  INDEX `fk_miss_subject1` (`subject_idsubject` ASC) ,
  INDEX `fk_miss_classblock1` (`classblock_idclassblock` ASC) ,
  CONSTRAINT `fk_miss_student1`
    FOREIGN KEY (`student_idstudent` )
    REFERENCES `amdb`.`student` (`idstudent` )
    ON DELETE CASCADE
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_miss_teacher1`
    FOREIGN KEY (`teacher_idteacher` )
    REFERENCES `amdb`.`teacher` (`idteacher` )
    ON DELETE CASCADE
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_miss_subject1`
    FOREIGN KEY (`subject_idsubject` )
    REFERENCES `amdb`.`subject` (`idsubject` )
    ON DELETE CASCADE
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_miss_classblock1`
    FOREIGN KEY (`classblock_idclassblock` )
    REFERENCES `amdb`.`classblock` (`idclassblock` )
    ON DELETE CASCADE
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `amdb`.`group_has_student`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `amdb`.`group_has_student` ;

CREATE  TABLE IF NOT EXISTS `amdb`.`group_has_student` (
  `group_idgroup` INT NOT NULL ,
  `student_idstudent` INT NOT NULL ,
  PRIMARY KEY (`group_idgroup`, `student_idstudent`) ,
  INDEX `fk_grupo_has_alumno_group1` (`group_idgroup` ASC) ,
  INDEX `fk_grupo_has_alumno_student1` (`student_idstudent` ASC) ,
  CONSTRAINT `fk_grupo_has_alumno_group1`
    FOREIGN KEY (`group_idgroup` )
    REFERENCES `amdb`.`group` (`idgroup` )
    ON DELETE CASCADE
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_grupo_has_alumno_student1`
    FOREIGN KEY (`student_idstudent` )
    REFERENCES `amdb`.`student` (`idstudent` )
    ON DELETE CASCADE
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;



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