CREATE TABLE `recommendation` (
  `serial_rec` INT NOT NULL AUTO_INCREMENT,
  `serial_att` INT NOT NULL,
  `serial_sbp` INT NOT NULL,
  `min_grade_rec` DECIMAL(6,2) NOT NULL,
  `max_grade_rec` DECIMAL(6,2) NOT NULL,
  `recommendation_text_rec` TEXT NOT NULL,
  `status_rec` ENUM('ACTIVE', 'INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  PRIMARY KEY (`serial_rec`),
  INDEX `fk_recommendation_has_subperiod_idx` (`serial_sbp` ASC),
  INDEX `fk_recommendation_has_activity_type_idx` (`serial_att` ASC),
  CONSTRAINT `fk_recommendation_has_subperiod`
    FOREIGN KEY (`serial_sbp`)
    REFERENCES `subperiod` (`serial_sbp`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_recommendation_has_activity_type`
    FOREIGN KEY (`serial_att`)
    REFERENCES `activity_type` (`serial_att`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);


CREATE TABLE `improvement_plan` (
  `serial_imp` INT NOT NULL AUTO_INCREMENT,
  `code_imp` VARCHAR(45) NULL,
  `serial_crs` INT NULL,
  `serial_prd` INT NULL,
  `min_grade_imp` DECIMAL(6,2) NULL,
  `max_grade_imp` DECIMAL(6,2) NULL,
  `improvement_plan_text_imp` TEXT NOT NULL,
  `status_imp` ENUM('ACTIVE','INACTIVE') NOT NULL,
  PRIMARY KEY (`serial_imp`),
  INDEX `fk_improvement_plan_has_course_idx` (`serial_crs` ASC),
  INDEX `fk_improvement_plan_has_period_idx` (`serial_prd` ASC),
  CONSTRAINT `fk_improvement_plan_has_course`
    FOREIGN KEY (`serial_crs`)
    REFERENCES `course` (`serial_crs`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,  
  CONSTRAINT `fk_improvement_plan_has_period`
    FOREIGN KEY (`serial_prd`)
    REFERENCES `period` (`serial_prd`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);

CREATE TABLE `student_improvement_plan` (
  `serial_std` INT NOT NULL,
  `serial_imp` INT NOT NULL,
  `status_sti` ENUM('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  PRIMARY KEY (`serial_std`, `serial_imp`),
  INDEX `fk_student_has_improvement_plan_idx` (`serial_imp` ASC),
  CONSTRAINT `fk_student_improvement_plan_has_improvement_plan`
    FOREIGN KEY (`serial_imp`)
    REFERENCES `improvement_plan` (`serial_imp`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_student_improvement_plan_has_student`
    FOREIGN KEY (`serial_std`)
    REFERENCES `student` (`serial_std`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);