/*==============================================================*/
/* Table: ASPIRANT_DOCUMENTS                                    */
/*==============================================================*/
CREATE TABLE `aspirant_documents` (
  `serial_asdc` int(11) NOT NULL AUTO_INCREMENT,
  `serial_asstu` int(11) DEFAULT NULL,
  `serial_yasdc` int(11) DEFAULT NULL,
  `filename_pdc` varchar(500) DEFAULT NULL,
  `status_pdc` enum('PENDING','SUBMITTED') NOT NULL DEFAULT 'PENDING',
  PRIMARY KEY (`serial_asdc`),
  KEY `fk_serial_year_asdc_idx` (`serial_yasdc`),
  KEY `fk_serial_aspri_stu_idx` (`serial_asstu`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*==============================================================*/
/* Table: ASPIRANT_FAMILY                                       */
/*==============================================================*/
CREATE TABLE `aspirant_family` (
  `serial_asfml` int(11) NOT NULL AUTO_INCREMENT,
  `serial_asgu` int(11) NOT NULL,
  `first_surname_asfml` varchar(255) NOT NULL,
  `last_surname_asfml` varchar(255) DEFAULT NULL,
  `phone_asfml` varchar(255) DEFAULT NULL,
  `status_asfml` enum('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  `main_street` varchar(100) DEFAULT NULL,
  `secundary_street` varchar(100) DEFAULT NULL,
  `number_street` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`serial_asfml`),
  KEY `fk_serial_guardian3_idx` (`serial_asgu`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*==============================================================*/
/* Table: ASPIRANT_GUARDIAN                                     */
/*==============================================================*/
CREATE TABLE `aspirant_guardian` (
  `serial_asgu` int(11) NOT NULL AUTO_INCREMENT,
  `serial_asstu` int(11) DEFAULT NULL,
  `serial_wus` int(11) NOT NULL,
  `serial_emp` int(11) DEFAULT NULL,
  `serial_nnt` int(11) DEFAULT NULL,
  `serial_elg` int(11) DEFAULT NULL,
  `serial_ocg` int(11) DEFAULT NULL,
  `serial_dgr` int(11) DEFAULT NULL,
  `serial_sch` int(11) DEFAULT NULL,
  `id_grd` varchar(13) NOT NULL,
  `first_name_asgu` varchar(512) NOT NULL,
  `last_name_asgu` varchar(512) NOT NULL,
  `birthdate_asgu` date DEFAULT NULL,
  `gender_asgu` enum('MALE','FEMALE') NOT NULL,
  `marital_status_asgu` enum('SINGLE','MARRIED','DIVORCED','WIDOWED','UNMARRIED PARTNERS') NOT NULL DEFAULT 'MARRIED',
  `alumnus_asgu` enum('YES','NO') NOT NULL DEFAULT 'NO',
  `phone_asgu` varchar(30) NOT NULL,
  `mobile_asgu` varchar(30) DEFAULT NULL,
  `company_name_asgu` varchar(100) DEFAULT NULL,
  `company_activity_asgu` varchar(80) DEFAULT NULL,
  `role_in_company_asgu` varchar(60) DEFAULT NULL,
  `time_in_job_asgu` int(11) DEFAULT NULL,
  `company_addres_asgu` varchar(45) DEFAULT NULL,
  `company_phone_asgu` varchar(20) DEFAULT NULL,
  `email_asgu` varchar(256) DEFAULT NULL,
  `email2_asgu` varchar(150) DEFAULT NULL,
  `email3_asgu` varchar(150) DEFAULT NULL,
  `lives_with_student_grd` enum('YES','NO') DEFAULT 'YES',
  `allow_to_pick_student_grd` enum('YES','NO') DEFAULT 'NO',
  `observation_grd` text,
  `status_grd` enum('ACTIVE','INACTIVE','BLOCKED') NOT NULL DEFAULT 'ACTIVE',
  `academic_title_asgu` varchar(45) DEFAULT NULL,
  `university_acd_asgu` varchar(45) DEFAULT NULL,
  `graduate degree_asgu` varchar(45) DEFAULT NULL,
  `university_degree_asgu` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`serial_asgu`),
  KEY `serial_wus_idx` (`serial_wus`),
  KEY `serial_emp_idx` (`serial_emp`),
  KEY `serial_elg_idx` (`serial_elg`),
  KEY `serial_ocg_idx` (`serial_ocg`),
  KEY `serial_dgr_idx` (`serial_dgr`),
  KEY `serial_sch_idx` (`serial_sch`),
  KEY `fk_serial_nnt_idx` (`serial_nnt`),
  KEY `fk_aspirtant_stu_idx` (`serial_asstu`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*==============================================================*/
/* Table: ASPIRANT_GUARDIAN_ADDRES                              */
/*==============================================================*/
CREATE TABLE `aspirant_guardian_addres` (
  `serial_asguad` int(11) NOT NULL AUTO_INCREMENT COMMENT 'clave primaria de la tabla guardian_address, autoincremental',
  `serial_asgu` int(11) NOT NULL COMMENT 'clave foranea perteneciente a la tabla guardian',
  `serial_ste` int(11) NOT NULL COMMENT 'clave foranea perteneciante a la tabla provincia',
  `serial_cnt` int(11) NOT NULL COMMENT 'clave foranea perteneciente a la tabla canton',
  `serial_par` int(11) NOT NULL COMMENT 'clave foranea perteneciente a la tabla parroquia',
  `serial_zne` int(11) DEFAULT NULL COMMENT 'clave foranea perteneciente a la tabla zona',
  `main_street_gda` varchar(64) NOT NULL,
  `number_gda` varchar(32) NOT NULL,
  `secondary_street_gda` varchar(64) DEFAULT NULL,
  `address_type_gda` enum('HOME','WORK','OTHER') NOT NULL DEFAULT 'OTHER',
  `phone1_gda` varchar(32) DEFAULT NULL,
  `phone2_gda` varchar(32) DEFAULT NULL,
  `main_address_gda` enum('YES','NO') NOT NULL DEFAULT 'NO',
  `status_gda` enum('ACTIVE','INACTIVE') DEFAULT 'ACTIVE' COMMENT 'indica si la direccion esta en uso o esta borrada',
  PRIMARY KEY (`serial_asguad`),
  KEY `FK_addressg_has_state` (`serial_ste`),
  KEY `FK_addressg_has_county` (`serial_cnt`),
  KEY `FK_addressg_has_parish` (`serial_par`),
  KEY `FK_addressg_has_zone` (`serial_zne`),
  KEY `serial_asgu_idx` (`serial_asgu`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*==============================================================*/
/* Table: ASPIRANT_REPORT			                            */
/*==============================================================*/
CREATE TABLE `aspirant_report` (
  `serial_asre` int(11) NOT NULL AUTO_INCREMENT,
  `serial_asstu` int(11) DEFAULT NULL,
  `serial_tyrp` int(11) DEFAULT NULL,
  `comment1` varchar(45) DEFAULT NULL,
  `serial_usr1` int(11) DEFAULT NULL,
  `comment2` varchar(45) DEFAULT NULL,
  `serial_user2` int(11) DEFAULT NULL,
  PRIMARY KEY (`serial_asre`),
  KEY `serial_type_idx` (`serial_tyrp`),
  KEY `fk_serial_asstu3_idx` (`serial_asstu`) 
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*==============================================================*/
/* Table: ASPIRANT_SIBLINGS			                            */
/*==============================================================*/
CREATE TABLE `aspirant_siblings` (
  `serial_assi` int(11) NOT NULL AUTO_INCREMENT,
  `serial_asstu` int(11) DEFAULT NULL,
  `serial_sch` int(11) DEFAULT NULL,
  `Fisrt_name_sibling` varchar(45) DEFAULT NULL,
  `Last_name_sibling` varchar(45) DEFAULT NULL,
  `Birthday_sibling` date DEFAULT NULL,
  PRIMARY KEY (`serial_assi`),
  KEY `serail_asstu_idx` (`serial_asstu`),
  KEY `serial_sch_idx` (`serial_sch`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Hermanos del aspirante';
/*==============================================================*/
/* Table: ASPIRANT_STUDENT			                            */
/*==============================================================*/
CREATE TABLE `aspirant_student` (
  `serial_asstu` int(11) NOT NULL,
  `serial_wus` int(11) DEFAULT NULL,
  `serial_lvl` int(11) DEFAULT NULL,
  `serial_scy` int(11) DEFAULT NULL,
  `serial_set` int(11) DEFAULT NULL,
  `serial_spt` int(11) DEFAULT NULL,
  `serial_nnt` int(11) DEFAULT NULL,
  `serial_dgr` int(11) DEFAULT NULL,
  `serial_ste` int(11) DEFAULT NULL,
  `serial_asfml` int(11) DEFAULT NULL,
  `id_asstu` varchar(10) DEFAULT NULL,
  `request` varchar(20) NOT NULL,
  `first_name_asstu` varchar(512) NOT NULL,
  `last_name_asstu` varchar(512) NOT NULL,
  `birthdate_asstu` date NOT NULL,
  `gender_asstu` enum('MALE','FEMALE') NOT NULL,
  `address_asstu` text NOT NULL,
  `phone_asstu` varchar(30) NOT NULL,
  `first_name_father_asstu` varchar(100) DEFAULT NULL,
  `last_name_father_asstu` varchar(100) DEFAULT NULL,
  `first_name_mother_asstu` varchar(100) DEFAULT NULL,
  `last_name_mother_asstu` varchar(100) DEFAULT NULL,
  `city_birth_asstu` varchar(100) DEFAULT NULL,
  `blood_type_asstu` varchar(10) DEFAULT NULL,
  `allergies_asstu` text,
  `lives_with_asstu` enum('PARENT','SIBLING','GRANDPARENT','AUNT_UNCLE','OTHER') NOT NULL,
  `photo_asstu` varchar(1024) DEFAULT NULL,
  `email_asstu` varchar(256) DEFAULT NULL,
  `rep_first_name_asstu` varchar(100) DEFAULT NULL,
  `rep_last_name_asstu` varchar(100) DEFAULT NULL,
  `rep_relationship_asstu` enum('PARENT','SIBLING','GRANDPARENT','AUNT_UNCLE','OTHER') DEFAULT NULL,
  `rep_birthdate_asstu` date DEFAULT NULL,
  `rep_city_birth_asstu` varchar(100) DEFAULT NULL,
  `rep_marital_status_asstu` enum('SINGLE','MARRIED','DIVORCED','WIDOWED','UNMARRIED PARTNERS') DEFAULT NULL,
  `rep_occupation_asstu` varchar(100) DEFAULT NULL,
  `rep_address_asstu` text,
  `rep_phone_asstu` varchar(13) DEFAULT NULL,
  `rep_mobile_asstu` varchar(13) DEFAULT NULL,
  `rep_company_name_asstu` varchar(80) DEFAULT NULL,
  `rep_company_activity_asstu` text,
  `rep_company_phone_asstu` varchar(13) DEFAULT NULL,
  `rep_photo_asstu` varchar(1024) DEFAULT NULL,
  `rep_gender_asstu` enum('MALE','FEMALE') DEFAULT NULL,
  `rep_id_asstu` varchar(13) DEFAULT NULL,
  `rep_email_asstu` varchar(256) DEFAULT NULL,
  `is_listener_asstu` enum('YES','NO') NOT NULL DEFAULT 'NO',
  `submitted_docs_asstu` enum('YES','NO') NOT NULL DEFAULT 'NO',
  `siblings_asstu` int(11) DEFAULT NULL,
  `position_sibling_asstu` int(11) DEFAULT NULL,
  `siblings_in_school` enum('YES','NO') DEFAULT 'NO',
  `siblings_in_school_detail` varchar(50) DEFAULT NULL,
  `insert_date_asstu` datetime DEFAULT NULL,
  `modification_date_asstu` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `Parents_live_asstu` enum('together','separated') NOT NULL DEFAULT 'together',
  `Repeat_level` enum('YES','NO') NOT NULL DEFAULT 'NO',
  `reason_for_repeat` tinytext,
  `Appplication_status` enum('waiting_list','denied','progress','slope','slope_documents','ready to commission','approved','apply next year','collectorship','academic secretary') NOT NULL DEFAULT 'progress',
  `telling_of_entries` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`serial_asstu`),
  KEY `fk_relationship_205` (`serial_dgr`),
  KEY `fk_relationship_206` (`serial_nnt`),
  KEY `fk_relationship_242` (`serial_ste`),
  KEY `serial_lvl_idx` (`serial_lvl`),
  KEY `serial_scy_idx` (`serial_scy`),
  KEY `serial_set_idx` (`serial_set`),
  KEY `serial_spt_idx` (`serial_spt`),
  KEY `fk_serial_asfml_idx` (`serial_asfml`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='web_pre_registration';
/*==============================================================*/
/* Table: COMMITTE					                            */
/*==============================================================*/
CREATE TABLE `committe` (
  `serial_cmt` int(11) NOT NULL AUTO_INCREMENT,
  `serial_asstu` int(11) DEFAULT NULL,
  `report_cmt` int(11) DEFAULT NULL,
  `inset_date_cmt` date DEFAULT NULL,
  `modify_date_cmt` date DEFAULT NULL,
  PRIMARY KEY (`serial_cmt`),
  KEY `serial_asstu5_idx` (`serial_asstu`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*==============================================================*/
/* Table: DOCUMENTS					                            */
/*==============================================================*/
CREATE TABLE `documents` (
  `serial_dcm` int(11) NOT NULL AUTO_INCREMENT,
  `name_dcm` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`serial_dcm`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*==============================================================*/
/* Table: FEEDBACK_FORM				                            */
/*==============================================================*/
CREATE TABLE `feedback_form` (
  `serial_fdbf` int(11) NOT NULL AUTO_INCREMENT,
  `serial_asstu` int(11) NOT NULL,
  `serial_std` int(11) NOT NULL,
  `serial_fml` int(11) NOT NULL,
  `serial_crs` int(11) NOT NULL,
  `student_academic_perfrmance` tinytext,
  `student_social_integration` tinytext,
  `parent_comprimised` tinytext,
  `additional_comments` tinytext,
  PRIMARY KEY (`serial_fdbf`),
  KEY `fk_serial_asstu2_idx` (`serial_asstu`),
  KEY `fk_std_serial_2_idx` (`serial_std`),
  KEY `fk_serial:fml2_idx` (`serial_fml`),
  KEY `fk_serial_curse_idx` (`serial_crs`)  
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*==============================================================*/
/* Table: PREVIOUS_EDUCATION_ASPIRANT                           */
/*==============================================================*/
CREATE TABLE `previous_education_aspirant` (
  `serial_pred` int(11) NOT NULL AUTO_INCREMENT,
  `serial_asstu` int(11) NOT NULL,
  `serial_sch` int(11) DEFAULT NULL,
  `previous_level_asstu` varchar(45) DEFAULT NULL,
  `previous_year_asstu` int(11) DEFAULT NULL,
  `previous_note_asstu` int(11) DEFAULT NULL,
  `previous_note_disciplien_asstu` int(11) DEFAULT NULL,
  PRIMARY KEY (`serial_pred`),
  KEY `serial_asstu_idx` (`serial_asstu`),
  KEY `fk_serail_school_idx` (`serial_sch`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*==============================================================*/
/* Table: SCHOOL					                            */
/*==============================================================*/
CREATE TABLE `school` (
  `serial_sch` int(11) NOT NULL AUTO_INCREMENT,
  `name_sch` varchar(400) DEFAULT NULL,
  PRIMARY KEY (`serial_sch`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*==============================================================*/
/* Table: TYPE_PUBLICITY			                            */
/*==============================================================*/
CREATE TABLE `type_publicity` (
  `serial_typu` int(11) NOT NULL AUTO_INCREMENT,
  `name_typu` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`serial_typu`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*==============================================================*/
/* Table: TYPE_REPORT				                            */
/*==============================================================*/
CREATE TABLE `type_report` (
  `serial_tyrp` int(11) NOT NULL,
  `name_report` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`serial_tyrp`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*==============================================================*/
/* Table: WEB_USER_ASPIRANT			                            */
/*==============================================================*/
CREATE TABLE `web_user_aspirant` (
  `serial_wus` int(11) NOT NULL AUTO_INCREMENT,
  `email_wus` varchar(128) NOT NULL,
  `serial_asgu` int(11) DEFAULT NULL,
  `id_asgu` varchar(13) DEFAULT NULL,
  `password_wus` varchar(256) NOT NULL,
  `authentication_key_wus` varchar(1024) DEFAULT NULL,
  `insert_date_wus` datetime DEFAULT NULL,
  `modification_date_wus` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`serial_wus`),
  KEY `fk_serial_asgu2_idx` (`serial_asgu`) 
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='web_user';
/*==============================================================*/
/* Table: YEAR_ASPIRANT_DOCUMENTS                               */
/*==============================================================*/
CREATE TABLE `year_aspirant_documents` (
  `serial_yasdc` int(11) NOT NULL AUTO_INCREMENT,
  `serial_scy` int(11) DEFAULT NULL,
  `serial_dcm` int(11) DEFAULT NULL,
  `status_ypd` enum('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  `serial_typu` int(11) DEFAULT NULL,
  PRIMARY KEY (`serial_yasdc`),
  KEY `fk_relationship_261_idx` (`serial_scy`),
  KEY `fk_documetns_idx` (`serial_dcm`),
  KEY `fk_typu_idx` (`serial_typu`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*==============================================================*/
/* Table: FILTOS PARA LAS TABLAS DE ADMISION                    */
/*==============================================================*/
ALTER TABLE `aspirant_documents`
ADD CONSTRAINT `fk_serial_aspri_stu` FOREIGN KEY (`serial_asstu`) REFERENCES `aspirant_student` (`serial_asstu`) ON DELETE NO ACTION ON UPDATE NO ACTION,
ADD CONSTRAINT `fk_serial_year_asdc` FOREIGN KEY (`serial_yasdc`) REFERENCES `year_aspirant_documents` (`serial_yasdc`) ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `aspirant_family`
ADD CONSTRAINT `fk_serial_guardian3` FOREIGN KEY (`serial_asgu`) REFERENCES `aspirant_guardian` (`serial_asgu`) ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `aspirant_guardian`
ADD CONSTRAINT `fk_aspirtant_stu` FOREIGN KEY (`serial_asstu`) REFERENCES `aspirant_student` (`serial_asstu`) ON DELETE NO ACTION ON UPDATE NO ACTION,
ADD CONSTRAINT `fk_serial_nnt` FOREIGN KEY (`serial_nnt`) REFERENCES `nationality` (`serial_nnt`) ON DELETE NO ACTION ON UPDATE NO ACTION,
ADD CONSTRAINT `serial_elg` FOREIGN KEY (`serial_elg`) REFERENCES `education_level_guardian` (`serial_elg`) ON DELETE NO ACTION ON UPDATE NO ACTION,
ADD CONSTRAINT `serial_emp` FOREIGN KEY (`serial_emp`) REFERENCES `employee` (`serial_emp`) ON DELETE NO ACTION ON UPDATE NO ACTION,
ADD CONSTRAINT `serial_ocg` FOREIGN KEY (`serial_ocg`) REFERENCES `occupation_guardian` (`serial_ocg`) ON DELETE NO ACTION ON UPDATE NO ACTION;
  
ALTER TABLE `aspirant_guardian_addres`
ADD CONSTRAINT `serial_asgu` FOREIGN KEY (`serial_asgu`) REFERENCES `aspirant_guardian` (`serial_asgu`) ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `aspirant_report`
ADD CONSTRAINT `fk_serial_asstu3` FOREIGN KEY (`serial_asstu`) REFERENCES `aspirant_student` (`serial_asstu`) ON DELETE NO ACTION ON UPDATE NO ACTION,
ADD CONSTRAINT `serial_type` FOREIGN KEY (`serial_tyrp`) REFERENCES `type_report` (`serial_tyrp`) ON DELETE NO ACTION ON UPDATE NO ACTION;
  
ALTER TABLE `aspirant_siblings`
ADD CONSTRAINT `serail_asstu` FOREIGN KEY (`serial_asstu`) REFERENCES `aspirant_student` (`serial_asstu`) ON DELETE NO ACTION ON UPDATE NO ACTION,
ADD CONSTRAINT `serial_sch` FOREIGN KEY (`serial_sch`) REFERENCES `school` (`serial_sch`) ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `aspirant_student`
ADD CONSTRAINT `fk_serial_asfml` FOREIGN KEY (`serial_asfml`) REFERENCES `aspirant_family` (`serial_asfml`) ON DELETE NO ACTION ON UPDATE NO ACTION,
ADD CONSTRAINT `serial_dgr` FOREIGN KEY (`serial_dgr`) REFERENCES `degree` (`serial_dgr`) ON DELETE NO ACTION ON UPDATE NO ACTION,
ADD CONSTRAINT `serial_lvl` FOREIGN KEY (`serial_lvl`) REFERENCES `level` (`serial_lvl`) ON DELETE NO ACTION ON UPDATE NO ACTION,
ADD CONSTRAINT `serial_nnt` FOREIGN KEY (`serial_nnt`) REFERENCES `nationality` (`serial_nnt`) ON DELETE NO ACTION ON UPDATE NO ACTION,
ADD CONSTRAINT `serial_scy` FOREIGN KEY (`serial_scy`) REFERENCES `school_year` (`serial_scy`) ON DELETE NO ACTION ON UPDATE NO ACTION,
ADD CONSTRAINT `serial_set` FOREIGN KEY (`serial_set`) REFERENCES `section` (`serial_set`) ON DELETE NO ACTION ON UPDATE NO ACTION,
ADD CONSTRAINT `serial_spt` FOREIGN KEY (`serial_spt`) REFERENCES `specialty` (`serial_spt`) ON DELETE NO ACTION ON UPDATE NO ACTION,
ADD CONSTRAINT `serial_ste` FOREIGN KEY (`serial_ste`) REFERENCES `state` (`serial_ste`) ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `committe`
ADD CONSTRAINT `serial_asstu5` FOREIGN KEY (`serial_asstu`) REFERENCES `aspirant_student` (`serial_asstu`) ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `feedback_form`
ADD CONSTRAINT `fk_serial:fml2` FOREIGN KEY (`serial_fml`) REFERENCES `family` (`serial_fml`) ON DELETE NO ACTION ON UPDATE NO ACTION,
ADD CONSTRAINT `fk_serial_asstu2` FOREIGN KEY (`serial_asstu`) REFERENCES `aspirant_student` (`serial_asstu`) ON DELETE NO ACTION ON UPDATE NO ACTION,
ADD CONSTRAINT `fk_serial_curse` FOREIGN KEY (`serial_crs`) REFERENCES `course` (`serial_crs`) ON DELETE NO ACTION ON UPDATE NO ACTION,
ADD CONSTRAINT `fk_std_serial_2` FOREIGN KEY (`serial_std`) REFERENCES `student` (`serial_std`) ON DELETE NO ACTION ON UPDATE NO ACTION;
  
ALTER TABLE `previous_education_aspirant`
ADD CONSTRAINT `fk_serail_school` FOREIGN KEY (`serial_sch`) REFERENCES `school` (`serial_sch`) ON DELETE NO ACTION ON UPDATE NO ACTION,
ADD CONSTRAINT `serial_asstu` FOREIGN KEY (`serial_asstu`) REFERENCES `aspirant_student` (`serial_asstu`) ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `web_user_aspirant`
ADD CONSTRAINT `fk_serial_asgu2` FOREIGN KEY (`serial_asgu`) REFERENCES `aspirant_guardian` (`serial_asgu`) ON DELETE NO ACTION ON UPDATE NO ACTION;

ALTER TABLE `year_aspirant_documents`
ADD CONSTRAINT `fk_typu` FOREIGN KEY (`serial_typu`) REFERENCES `type_publicity` (`serial_typu`) ON DELETE NO ACTION ON UPDATE NO ACTION,
ADD CONSTRAINT `fk_documetns` FOREIGN KEY (`serial_dcm`) REFERENCES `documents` (`serial_dcm`) ON DELETE NO ACTION ON UPDATE NO ACTION,
ADD CONSTRAINT `fk_relationship_261_scy` FOREIGN KEY (`serial_scy`) REFERENCES `school_year` (`serial_scy`);  

ALTER TABLE `aspirant_guardian` 
ADD COLUMN `serial_nnt_2` INT NULL AFTER `serial_nnt`;

ALTER TABLE `aspirant_guardian` 
ADD COLUMN `academic_title_grd` INT NULL AFTER `status_grd`;

ALTER TABLE `aspirant_guardian` 
ADD COLUMN `university_acd_grd` INT NULL AFTER `academic_title_grd`;

ALTER TABLE `aspirant_guardian` 
ADD COLUMN `graduate_degree_grd` INT NULL AFTER `university_acd_grd`;

ALTER TABLE `aspirant_guardian` 
ADD COLUMN `university_degree_grd` INT NULL AFTER `graduate_degree_grd`;

ALTER TABLE `guardian_address` 
ADD COLUMN `reference` INT NULL AFTER `secondary_street_gda`;
