-- phpMyAdmin SQL Dump
-- version 3.2.4
-- http://www.phpmyadmin.net
--
-- Servidor: localhost
-- Tiempo de generación: 09-02-2011 a las 15:05:37
-- Versión del servidor: 5.1.41
-- Versión de PHP: 5.3.1

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- Base de datos: `RUTADEMIC`
--

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `academic_area`
--

CREATE TABLE IF NOT EXISTS `academic_area` (
  `serial_aca` int(11) NOT NULL AUTO_INCREMENT,
  `name_aca` varchar(150) NOT NULL,
  `description_aca` text NOT NULL,
  `status_aca` enum('ACTIVE','INACTIVE') NOT NULL,
  PRIMARY KEY (`serial_aca`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

--
-- Volcar la base de datos para la tabla `academic_area`
--

INSERT INTO `academic_area` (`serial_aca`, `name_aca`, `description_aca`, `status_aca`) VALUES
(1, 'Física', 'asd as as das dsa  as dfgsfgdfg', 'ACTIVE'),
(2, 'Matemáticas', 'as das sad  d der f s fd', 'ACTIVE'),
(3, 'Ciencias Ocultas', 'asd sa das das dsa  a', 'ACTIVE');

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `access_log`
--

CREATE TABLE IF NOT EXISTS `access_log` (
  `serial_alg` int(11) NOT NULL AUTO_INCREMENT,
  `serial_usr` int(11) DEFAULT NULL,
  `serial_prf` int(11) DEFAULT NULL,
  `login_date_alg` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`serial_alg`),
  KEY `fk_relationship_143` (`serial_usr`,`serial_prf`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;

--
-- Volcar la base de datos para la tabla `access_log`
--

INSERT INTO `access_log` (`serial_alg`, `serial_usr`, `serial_prf`, `login_date_alg`) VALUES
(1, 2, 2, '2011-02-07 16:43:51'),
(2, 1, 1, '2011-02-07 16:48:03'),
(3, 1, 1, '2011-02-08 09:16:22'),
(4, 1, 1, '2011-02-08 14:51:00'),
(5, 2, 2, '2011-02-08 15:33:59');

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `activity`
--

CREATE TABLE IF NOT EXISTS `activity` (
  `serial_act` int(11) NOT NULL AUTO_INCREMENT,
  `name_act` varchar(256) NOT NULL,
  `description_act` text NOT NULL,
  `send_date_act` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `due_date_act` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `status_act` enum('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  PRIMARY KEY (`serial_act`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

--
-- Volcar la base de datos para la tabla `activity`
--


--
-- (Evento) desencadenante `activity`
--
DROP TRIGGER IF EXISTS `activity_trg`;
DELIMITER //
CREATE TRIGGER `activity_trg` BEFORE UPDATE ON `activity`
 FOR EACH ROW BEGIN 
	IF NEW.status_act = 'INACTIVE' THEN
		/*change activity_applied_criterias to INACTIVE if activity is INACTIVE*/
		UPDATE activity_applied_criterias aac
		SET aac.status_aac = 'INACTIVE'
		WHERE aac.serial_act = OLD.serial_act;
		/*change activity_attachment to INACTIVE if activity is INACTIVE*/
		UPDATE activity_attachment ath
		SET ath.status_ath = 'INACTIVE'
		WHERE ath.serial_act = OLD.serial_act;
	ELSEIF NEW.status_act = 'ACTIVE' THEN
		/*change activity_applied_criterias to ACTIVE if activity is ACTIVE*/
		UPDATE activity_applied_criterias aac
		SET aac.status_aac = 'ACTIVE'
		WHERE aac.serial_act = OLD.serial_act;
		/*change activity_attachment to ACTIVE if activity is ACTIVE*/
		UPDATE activity_attachment ath
		SET ath.status_ath = 'ACTIVE'
		WHERE ath.serial_act = OLD.serial_act
		AND ath.status_ath = 'INACTIVE';
	END IF;
END
//
DELIMITER ;

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `activity_applied_criterias`
--

CREATE TABLE IF NOT EXISTS `activity_applied_criterias` (
  `serial_att` int(11) NOT NULL,
  `serial_prd` int(11) NOT NULL,
  `serial_sbc` int(11) NOT NULL,
  `serial_sbj` int(11) NOT NULL,
  `serial_gct` int(11) NOT NULL,
  `serial_act` int(11) NOT NULL,
  `weight_aac` decimal(5,2) NOT NULL,
  `status_aac` enum('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  PRIMARY KEY (`serial_att`,`serial_prd`,`serial_sbc`,`serial_gct`,`serial_act`),
  KEY `fk_activity_header` (`serial_act`),
  KEY `fk_relationship_95` (`serial_att`,`serial_prd`,`serial_sbc`,`serial_sbj`,`serial_gct`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Volcar la base de datos para la tabla `activity_applied_criterias`
--


--
-- (Evento) desencadenante `activity_applied_criterias`
--
DROP TRIGGER IF EXISTS `activity_applied_criterias_trg`;
DELIMITER //
CREATE TRIGGER `activity_applied_criterias_trg` BEFORE UPDATE ON `activity_applied_criterias`
 FOR EACH ROW BEGIN
	/*change student_criteria_grade to INACTIVE if activity_applied_criterias is INACTIVE
	also set de grade NULL*/
	IF NEW.status_aac = 'INACTIVE' THEN
		UPDATE student_criteria_grade scg
		SET scg.status_scg = 'INACTIVE',
		    scg.grade_scg = NULL
		WHERE scg.serial_att = OLD.serial_att
		AND scg.serial_prd = OLD.serial_prd
		AND scg.serial_sbc = OLD.serial_sbc
		AND scg.serial_gct = OLD.serial_gct
		AND scg.serial_act = OLD.serial_act;
	ELSEIF NEW.status_aac = 'ACTIVE' THEN
	/*change student_criteria_grade to ACTIVE if activity_applied_criterias is ACTIVE*/
		UPDATE student_criteria_grade scg
		SET scg.status_scg = 'ACTIVE'
		WHERE scg.serial_att = OLD.serial_att
		AND scg.serial_prd = OLD.serial_prd
		AND scg.serial_sbc = OLD.serial_sbc
		AND scg.serial_gct = OLD.serial_gct
		AND scg.serial_act = OLD.serial_act;
	/*change student_criteria_grade set the other criterias,serial_gct != OLD.serial_gct,as incomplete*/
		UPDATE student_criteria_grade scg
		SET scg.is_complete_scg = 'NO'
		WHERE scg.serial_att = OLD.serial_att
		AND scg.serial_prd = OLD.serial_prd
		AND scg.serial_sbc = OLD.serial_sbc
		AND scg.serial_gct != OLD.serial_gct
		AND scg.serial_act = OLD.serial_act;
	END IF;
END
//
DELIMITER ;

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `activity_attachment`
--

CREATE TABLE IF NOT EXISTS `activity_attachment` (
  `serial_ath` int(11) NOT NULL AUTO_INCREMENT,
  `serial_act` int(11) DEFAULT NULL,
  `name_ath` varchar(256) NOT NULL,
  `filename_ath` varchar(256) NOT NULL,
  `status_ath` enum('ACTIVE','INACTIVE','DELETED') NOT NULL DEFAULT 'ACTIVE',
  PRIMARY KEY (`serial_ath`),
  KEY `fk_relationship_96` (`serial_act`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

--
-- Volcar la base de datos para la tabla `activity_attachment`
--


-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `activity_policy_violations`
--

CREATE TABLE IF NOT EXISTS `activity_policy_violations` (
  `serial_opa` int(11) DEFAULT NULL,
  `serial_act` int(11) DEFAULT NULL,
  `serial_emp` int(11) DEFAULT NULL,
  `start_date_apv` date NOT NULL,
  `end_date_apv` date NOT NULL,
  KEY `fk_relationship_146` (`serial_act`),
  KEY `fk_relationship_147` (`serial_opa`),
  KEY `fk_relationship_148` (`serial_emp`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Volcar la base de datos para la tabla `activity_policy_violations`
--


-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `activity_type`
--

CREATE TABLE IF NOT EXISTS `activity_type` (
  `serial_att` int(11) NOT NULL AUTO_INCREMENT,
  `name_att` varchar(256) NOT NULL,
  `description_att` text NOT NULL,
  `status_att` enum('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  PRIMARY KEY (`serial_att`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;

--
-- Volcar la base de datos para la tabla `activity_type`
--

INSERT INTO `activity_type` (`serial_att`, `name_att`, `description_att`, `status_att`) VALUES
(1, 'Disciplina', 'Disciplina', 'ACTIVE'),
(2, 'Prueba', 'dfs fsd fsd fsd fs fd', 'ACTIVE'),
(3, 'Examen', 'dasf s fs sd f', 'ACTIVE'),
(4, 'Consulta', 'asd sd fs dfsd', 'ACTIVE'),
(5, 'Monografía', 'as ds  as da', 'ACTIVE');

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `allowed_pages_by_process`
--

CREATE TABLE IF NOT EXISTS `allowed_pages_by_process` (
  `serial_prc` int(11) DEFAULT NULL,
  `url_dsc` varchar(1024) NOT NULL,
  KEY `fk_child_pages` (`serial_prc`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Volcar la base de datos para la tabla `allowed_pages_by_process`
--


-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `article`
--

CREATE TABLE IF NOT EXISTS `article` (
  `serial_art` int(11) NOT NULL AUTO_INCREMENT,
  `code_art` varchar(10) NOT NULL,
  `name_art` varchar(250) NOT NULL,
  `stock_art` int(11) NOT NULL,
  `status_art` enum('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  PRIMARY KEY (`serial_art`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

--
-- Volcar la base de datos para la tabla `article`
--


-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `authority`
--

CREATE TABLE IF NOT EXISTS `authority` (
  `serial_aut` int(11) NOT NULL AUTO_INCREMENT,
  `serial_atc` int(11) DEFAULT NULL,
  `id_aut` varchar(15) NOT NULL,
  `degree_aut` varchar(50) NOT NULL,
  `first_name_aut` varchar(100) NOT NULL,
  `last_name_aut` varchar(100) NOT NULL,
  `in_charge_aut` enum('YES','NO') NOT NULL,
  PRIMARY KEY (`serial_aut`),
  KEY `fk_has_charge` (`serial_atc`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

--
-- Volcar la base de datos para la tabla `authority`
--

INSERT INTO `authority` (`serial_aut`, `serial_atc`, `id_aut`, `degree_aut`, `first_name_aut`, `last_name_aut`, `in_charge_aut`) VALUES
(1, 1, '1717171717', 'Mr', 'Osama', 'Guerrero', 'NO'),
(2, 2, '1715462656', 'Ing', 'Rin', 'Elwey', 'NO'),
(3, 3, '0103438180', 'Mr', 'Jhon', 'Corrales', 'NO'),
(4, 4, '0103438172', 'Don', 'manuel', 'Tintin', 'NO');

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `authority_charge`
--

CREATE TABLE IF NOT EXISTS `authority_charge` (
  `serial_atc` int(11) NOT NULL AUTO_INCREMENT,
  `name_atc` varchar(100) NOT NULL,
  `description_atc` text,
  `status_atc` enum('ACTIVE','INACTIVE') NOT NULL,
  PRIMARY KEY (`serial_atc`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=8 ;

--
-- Volcar la base de datos para la tabla `authority_charge`
--

INSERT INTO `authority_charge` (`serial_atc`, `name_atc`, `description_atc`, `status_atc`) VALUES
(1, 'Rector', 'Rector', 'ACTIVE'),
(2, 'Vicerector', 'Vicerector', 'ACTIVE'),
(3, 'Secretaria', 'Secretaria', 'ACTIVE'),
(4, 'Secretaria General', 'Secretaria General', 'ACTIVE'),
(5, 'Primer Vocal', 'Primer Vocal', 'ACTIVE'),
(6, 'Segundo Vocal', 'Segundo Vocal', 'ACTIVE'),
(7, 'Tercer Vocal', 'Tercer Vocal', 'ACTIVE');

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `bus`
--

CREATE TABLE IF NOT EXISTS `bus` (
  `serial_bus` int(11) NOT NULL AUTO_INCREMENT,
  `code_bus` varchar(13) NOT NULL,
  `plate_num_bus` varchar(12) NOT NULL,
  `description_bus` text NOT NULL,
  `capacity_bus` int(11) NOT NULL,
  `status_bus` enum('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  PRIMARY KEY (`serial_bus`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

--
-- Volcar la base de datos para la tabla `bus`
--


-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `bus_driver`
--

CREATE TABLE IF NOT EXISTS `bus_driver` (
  `serial_bdr` int(11) NOT NULL AUTO_INCREMENT,
  `id_bdr` varchar(13) NOT NULL,
  `first_name_bdr` varchar(512) NOT NULL,
  `last_name_bdr` varchar(512) NOT NULL,
  `mobile1_bdr` varchar(30) DEFAULT NULL,
  `mobile2_bdr` varchar(30) DEFAULT NULL,
  `phone1_bdr` varchar(30) NOT NULL,
  `phone2_bdr` varchar(30) DEFAULT NULL,
  `phone3_bdr` varchar(30) DEFAULT NULL,
  `address_bdr` text NOT NULL,
  `drive_license_bdr` varchar(20) NOT NULL,
  `license_expire_date_bdr` date NOT NULL,
  `status_bdr` enum('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  PRIMARY KEY (`serial_bdr`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

--
-- Volcar la base de datos para la tabla `bus_driver`
--


-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `bus_shift_year`
--

CREATE TABLE IF NOT EXISTS `bus_shift_year` (
  `serial_bdr` int(11) NOT NULL,
  `serial_rot` int(11) NOT NULL,
  `serial_scy` int(11) NOT NULL,
  `serial_bus` int(11) NOT NULL,
  `serial_sft` int(11) NOT NULL,
  `bus_serial_bdr` int(11) DEFAULT NULL,
  `status_bpy` enum('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  PRIMARY KEY (`serial_rot`,`serial_scy`,`serial_bus`,`serial_sft`),
  KEY `fk_relationship_76` (`serial_scy`),
  KEY `fk_relationship_77` (`serial_bdr`),
  KEY `fk_relationship_78` (`serial_bus`),
  KEY `fk_relationship_80` (`serial_sft`),
  KEY `fk_relationship_81` (`bus_serial_bdr`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Volcar la base de datos para la tabla `bus_shift_year`
--


-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `bus_year_user`
--

CREATE TABLE IF NOT EXISTS `bus_year_user` (
  `serial_usr` int(11) DEFAULT NULL,
  `serial_rot` int(11) DEFAULT NULL,
  `serial_scy` int(11) DEFAULT NULL,
  `serial_bus` int(11) DEFAULT NULL,
  `serial_sft` int(11) DEFAULT NULL,
  `status_byu` enum('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  KEY `fk_relationship_97` (`serial_rot`,`serial_scy`,`serial_bus`,`serial_sft`),
  KEY `fk_relationship_98` (`serial_usr`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Volcar la base de datos para la tabla `bus_year_user`
--


-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `cat_period`
--

CREATE TABLE IF NOT EXISTS `cat_period` (
  `serial_cpd` int(11) NOT NULL AUTO_INCREMENT,
  `name_cpd` varchar(100) NOT NULL,
  `description_cpd` text,
  `status_cpd` enum('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  PRIMARY KEY (`serial_cpd`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

--
-- Volcar la base de datos para la tabla `cat_period`
--

INSERT INTO `cat_period` (`serial_cpd`, `name_cpd`, `description_cpd`, `status_cpd`) VALUES
(1, 'Trimestre', 'asd as as d', 'ACTIVE');

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `cat_subject`
--

CREATE TABLE IF NOT EXISTS `cat_subject` (
  `serial_csb` int(11) NOT NULL AUTO_INCREMENT,
  `cat_serial_csb` int(11) DEFAULT NULL,
  `serial_aca` int(11) DEFAULT NULL,
  `serial_lng` int(11) DEFAULT NULL,
  `code_csb` varchar(20) NOT NULL,
  `name_csb` varchar(255) NOT NULL,
  `status_csb` enum('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  PRIMARY KEY (`serial_csb`),
  KEY `fk_child_parent` (`cat_serial_csb`),
  KEY `fk_relationship_62` (`serial_aca`),
  KEY `fk_relationship_68` (`serial_lng`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;

--
-- Volcar la base de datos para la tabla `cat_subject`
--

INSERT INTO `cat_subject` (`serial_csb`, `cat_serial_csb`, `serial_aca`, `serial_lng`, `code_csb`, `name_csb`, `status_csb`) VALUES
(1, NULL, 3, 1, 'CO-001', 'Hechizos 1', 'ACTIVE'),
(2, NULL, 3, 1, 'CO-002', 'Hechizo Avanzado', 'ACTIVE'),
(3, 2, 3, 1, 'CO-002-001', 'Laboratorio', 'ACTIVE'),
(4, 2, 3, 1, 'CO-002-002', 'Teoría', 'ACTIVE'),
(5, NULL, 1, 1, 'FI-001', 'Fisica Cuantica', 'ACTIVE'),
(6, NULL, 2, 1, 'MA-001', 'Fisica Nuclear', 'ACTIVE');

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `change_reason`
--

CREATE TABLE IF NOT EXISTS `change_reason` (
  `serial_chr` int(11) NOT NULL AUTO_INCREMENT,
  `name_chr` varchar(256) NOT NULL,
  `type_chr` enum('DISCIPLINE','ACTIVITY','BOTH') NOT NULL DEFAULT 'BOTH',
  `status_chr` enum('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  PRIMARY KEY (`serial_chr`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

--
-- Volcar la base de datos para la tabla `change_reason`
--

INSERT INTO `change_reason` (`serial_chr`, `name_chr`, `type_chr`, `status_chr`) VALUES
(1, 'Exclusión de Actividad', 'ACTIVITY', 'ACTIVE'),
(2, 'Error Tipeo', 'BOTH', 'ACTIVE'),
(3, 'Recalificación', 'ACTIVITY', 'ACTIVE'),
(4, 'Atraso', 'DISCIPLINE', 'ACTIVE');

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `classroom`
--

CREATE TABLE IF NOT EXISTS `classroom` (
  `serial_clr` int(11) NOT NULL AUTO_INCREMENT,
  `name_clr` varchar(80) NOT NULL,
  `capacity_clr` int(11) NOT NULL,
  `description_clr` text NOT NULL,
  `status_clr` enum('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  PRIMARY KEY (`serial_clr`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

--
-- Volcar la base de datos para la tabla `classroom`
--

INSERT INTO `classroom` (`serial_clr`, `name_clr`, `capacity_clr`, `description_clr`, `status_clr`) VALUES
(1, '207', 32, 'd dasd fsf', 'ACTIVE'),
(2, '206', 31, 'sdf sd fs f', 'ACTIVE'),
(3, '203', 21, 'ads df s fs fsd fsd sd f', 'ACTIVE');

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `class_group`
--

CREATE TABLE IF NOT EXISTS `class_group` (
  `serial_clg` int(11) NOT NULL AUTO_INCREMENT,
  `name_clg` varchar(150) NOT NULL,
  `description_clg` text,
  `status_clg` enum('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  PRIMARY KEY (`serial_clg`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

--
-- Volcar la base de datos para la tabla `class_group`
--

INSERT INTO `class_group` (`serial_clg`, `name_clg`, `description_clg`, `status_clg`) VALUES
(1, 'A', 'sdf sd fds fs d', 'ACTIVE'),
(2, 'B', 'sdf s fsd fsd f s', 'ACTIVE'),
(3, 'C', 'sf fs fdf s f', 'ACTIVE');

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `country`
--

CREATE TABLE IF NOT EXISTS `country` (
  `serial_cou` int(11) NOT NULL AUTO_INCREMENT,
  `name_cou` varchar(100) DEFAULT NULL,
  `status_cou` enum('ACTIVE','INACTIVE') DEFAULT 'ACTIVE',
  PRIMARY KEY (`serial_cou`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=233 ;

--
-- Volcar la base de datos para la tabla `country`
--

INSERT INTO `country` (`serial_cou`, `name_cou`, `status_cou`) VALUES
(1, 'Andorra', 'ACTIVE'),
(2, 'United arab emirates', 'ACTIVE'),
(3, 'Afghanistan', 'ACTIVE'),
(4, 'Antigua and barbuda', 'ACTIVE'),
(5, 'Anguilla', 'ACTIVE'),
(6, 'Albania', 'ACTIVE'),
(7, 'Armenia', 'ACTIVE'),
(8, 'Netherlands antilles', 'ACTIVE'),
(9, 'Angola', 'ACTIVE'),
(10, 'Antarctica', 'ACTIVE'),
(11, 'Argentina', 'ACTIVE'),
(12, 'Americansamoa', 'ACTIVE'),
(13, 'Austria', 'ACTIVE'),
(14, 'Australia', 'ACTIVE'),
(15, 'Aruba', 'ACTIVE'),
(16, 'Azerbaijan', 'ACTIVE'),
(17, 'Bosnia and herzegovina', 'ACTIVE'),
(18, 'Barbados', 'ACTIVE'),
(19, 'Bangladesh', 'ACTIVE'),
(20, 'Belgium', 'ACTIVE'),
(21, 'Burkinafaso', 'ACTIVE'),
(22, 'Bulgaria', 'ACTIVE'),
(23, 'Bahrain', 'ACTIVE'),
(24, 'Burundi', 'ACTIVE'),
(25, 'Benin', 'ACTIVE'),
(26, 'Bermuda', 'ACTIVE'),
(27, 'Bruneida russalam', 'ACTIVE'),
(28, 'Bolivia', 'ACTIVE'),
(29, 'Brazil', 'ACTIVE'),
(30, 'Bahamas', 'ACTIVE'),
(31, 'Bhutan', 'ACTIVE'),
(32, 'Bouvetisland', 'ACTIVE'),
(33, 'Botswana', 'ACTIVE'),
(34, 'Belarus', 'ACTIVE'),
(35, 'Belize', 'ACTIVE'),
(36, 'Canada', 'ACTIVE'),
(37, 'Cocos(keeling)islands', 'ACTIVE'),
(38, 'Congo', 'ACTIVE'),
(39, 'Central african republic', 'ACTIVE'),
(40, 'Congo', 'ACTIVE'),
(41, 'Switzerland', 'ACTIVE'),
(42, 'Coted''ivoire', 'ACTIVE'),
(43, 'Cookislands', 'ACTIVE'),
(44, 'Chile', 'ACTIVE'),
(45, 'Cameroon', 'ACTIVE'),
(46, 'China', 'ACTIVE'),
(47, 'Colombia', 'ACTIVE'),
(48, 'Costarica', 'ACTIVE'),
(49, 'Serbia and montenegro', 'ACTIVE'),
(50, 'Cuba', 'ACTIVE'),
(51, 'Capeverde', 'ACTIVE'),
(52, 'Christmas island', 'ACTIVE'),
(53, 'Cyprus', 'ACTIVE'),
(54, 'Czech republic', 'ACTIVE'),
(55, 'Germany', 'ACTIVE'),
(56, 'Djibouti', 'ACTIVE'),
(57, 'Denmark', 'ACTIVE'),
(58, 'Dominica', 'ACTIVE'),
(59, 'Dominican republic', 'ACTIVE'),
(60, 'Algeria', 'ACTIVE'),
(61, 'Ecuador', 'ACTIVE'),
(62, 'Estonia', 'ACTIVE'),
(63, 'Egypt', 'ACTIVE'),
(64, 'Western sahara', 'ACTIVE'),
(65, 'Eritrea', 'ACTIVE'),
(66, 'Spain', 'ACTIVE'),
(67, 'Ethiopia', 'ACTIVE'),
(68, 'Finland', 'ACTIVE'),
(69, 'Fiji', 'ACTIVE'),
(70, 'Micronesia', 'ACTIVE'),
(71, 'Faroe islands', 'ACTIVE'),
(72, 'France', 'ACTIVE'),
(73, 'Gabon', 'ACTIVE'),
(74, 'United kingdom', 'ACTIVE'),
(75, 'Grenada', 'ACTIVE'),
(76, 'Georgia', 'ACTIVE'),
(77, 'French guiana', 'ACTIVE'),
(78, 'Ghana', 'ACTIVE'),
(79, 'Gibraltar', 'ACTIVE'),
(80, 'Greenland', 'ACTIVE'),
(81, 'Gambia', 'ACTIVE'),
(82, 'Guinea', 'ACTIVE'),
(83, 'Guadeloupe', 'ACTIVE'),
(84, 'Equatorial guinea', 'ACTIVE'),
(85, 'Greece', 'ACTIVE'),
(86, 'Guatemala', 'ACTIVE'),
(87, 'Guam', 'ACTIVE'),
(88, 'Guinea-bissau', 'ACTIVE'),
(89, 'Guyana', 'ACTIVE'),
(90, 'Hong kong', 'ACTIVE'),
(91, 'Honduras', 'ACTIVE'),
(92, 'Croatia', 'ACTIVE'),
(93, 'Haiti', 'ACTIVE'),
(94, 'Hungary', 'ACTIVE'),
(95, 'Indonesia', 'ACTIVE'),
(96, 'Ireland', 'ACTIVE'),
(97, 'Israel', 'ACTIVE'),
(98, 'India', 'ACTIVE'),
(99, 'Iraq', 'ACTIVE'),
(100, 'Iran,islamic republic of', 'ACTIVE'),
(101, 'Iceland', 'ACTIVE'),
(102, 'Italy', 'ACTIVE'),
(103, 'Jamaica', 'ACTIVE'),
(104, 'Jordan', 'ACTIVE'),
(105, 'Japan', 'ACTIVE'),
(106, 'Kenya', 'ACTIVE'),
(107, 'Kyrgyzstan', 'ACTIVE'),
(108, 'Cambodia', 'ACTIVE'),
(109, 'Kiribati', 'ACTIVE'),
(110, 'Comoros', 'ACTIVE'),
(111, 'Saint kitts and nevis', 'ACTIVE'),
(112, 'Korea', 'ACTIVE'),
(113, 'Korea,republic of', 'ACTIVE'),
(114, 'Kuwait', 'ACTIVE'),
(115, 'Cayman islands', 'ACTIVE'),
(116, 'Kazakhstan', 'ACTIVE'),
(117, 'Lao', 'ACTIVE'),
(118, 'Lebanon', 'ACTIVE'),
(119, 'Saint lucia', 'ACTIVE'),
(120, 'Liechtenstein', 'ACTIVE'),
(121, 'Srilanka', 'ACTIVE'),
(122, 'Liberia', 'ACTIVE'),
(123, 'Lesotho', 'ACTIVE'),
(124, 'Lithuania', 'ACTIVE'),
(125, 'Luxembourg', 'ACTIVE'),
(126, 'Latvia', 'ACTIVE'),
(127, 'Libyanarabjamahiriya', 'ACTIVE'),
(128, 'Morocco', 'ACTIVE'),
(129, 'Monaco', 'ACTIVE'),
(130, 'Moldova,republic of', 'ACTIVE'),
(131, 'Madagascar', 'ACTIVE'),
(132, 'Marshall islands', 'ACTIVE'),
(133, 'Macedonia', 'ACTIVE'),
(134, 'Mali', 'ACTIVE'),
(135, 'Myanmar', 'ACTIVE'),
(136, 'Mongolia', 'ACTIVE'),
(137, 'Macao', 'ACTIVE'),
(138, 'Northern mariana islands', 'ACTIVE'),
(139, 'Martinique', 'ACTIVE'),
(140, 'Mauritania', 'ACTIVE'),
(141, 'Montserrat', 'ACTIVE'),
(142, 'Malta', 'ACTIVE'),
(143, 'Mauritius', 'ACTIVE'),
(144, 'Maldives', 'ACTIVE'),
(145, 'Malawi', 'ACTIVE'),
(146, 'Mexico', 'ACTIVE'),
(147, 'Malaysia', 'ACTIVE'),
(148, 'Mozambique', 'ACTIVE'),
(149, 'Namibia', 'ACTIVE'),
(150, 'New caledonia', 'ACTIVE'),
(151, 'Niger', 'ACTIVE'),
(152, 'Norfolk island', 'ACTIVE'),
(153, 'Nigeria', 'ACTIVE'),
(154, 'Nicaragua', 'ACTIVE'),
(155, 'Netherlands', 'ACTIVE'),
(156, 'Norway', 'ACTIVE'),
(157, 'Nepal', 'ACTIVE'),
(158, 'Nauru', 'ACTIVE'),
(159, 'Niue', 'ACTIVE'),
(160, 'New zealand', 'ACTIVE'),
(161, 'Oman', 'ACTIVE'),
(162, 'Panama', 'ACTIVE'),
(163, 'Peru', 'ACTIVE'),
(164, 'French polynesia', 'ACTIVE'),
(165, 'Papua new guinea', 'ACTIVE'),
(166, 'Philippines', 'ACTIVE'),
(167, 'Pakistan', 'ACTIVE'),
(168, 'Poland', 'ACTIVE'),
(169, 'Saint pierre and miquelon', 'ACTIVE'),
(170, 'Pitcairn', 'ACTIVE'),
(171, 'Puerto rico', 'ACTIVE'),
(172, 'Palestina', 'ACTIVE'),
(173, 'Portugal', 'ACTIVE'),
(174, 'Palau', 'ACTIVE'),
(175, 'Paraguay', 'ACTIVE'),
(176, 'Qatar', 'ACTIVE'),
(177, 'Reunion', 'ACTIVE'),
(178, 'Romania', 'ACTIVE'),
(179, 'Russian federation', 'ACTIVE'),
(180, 'Rwanda', 'ACTIVE'),
(181, 'Saudi arabia', 'ACTIVE'),
(182, 'Solomon islands', 'ACTIVE'),
(183, 'Seychelles', 'ACTIVE'),
(184, 'Sudan', 'ACTIVE'),
(185, 'Sweden', 'ACTIVE'),
(186, 'Singapore', 'ACTIVE'),
(187, 'Saint helena', 'ACTIVE'),
(188, 'Slovenia', 'ACTIVE'),
(189, 'Svalbard and janmayen', 'ACTIVE'),
(190, 'Slovakia', 'ACTIVE'),
(191, 'Sierra leone', 'ACTIVE'),
(192, 'San marino', 'ACTIVE'),
(193, 'Senegal', 'ACTIVE'),
(194, 'Somalia', 'ACTIVE'),
(195, 'Suriname', 'ACTIVE'),
(196, 'Saotome and principe', 'ACTIVE'),
(197, 'El salvador', 'ACTIVE'),
(198, 'Syrian arab republic', 'ACTIVE'),
(199, 'Swaziland', 'ACTIVE'),
(200, 'Turks and caicos islands', 'ACTIVE'),
(201, 'Chad', 'ACTIVE'),
(202, 'Togo', 'ACTIVE'),
(203, 'Thailand', 'ACTIVE'),
(204, 'Tajikistan', 'ACTIVE'),
(205, 'Tokelau', 'ACTIVE'),
(206, 'Timor-leste', 'ACTIVE'),
(207, 'Turkmenistan', 'ACTIVE'),
(208, 'Tunisia', 'ACTIVE'),
(209, 'Tonga', 'ACTIVE'),
(210, 'Turkey', 'ACTIVE'),
(211, 'Trinidad and tobago', 'ACTIVE'),
(212, 'Tuvalu', 'ACTIVE'),
(213, 'Taiwan,province of china', 'ACTIVE'),
(214, 'Tanzania,united republic of', 'ACTIVE'),
(215, 'Ukraine', 'ACTIVE'),
(216, 'Uganda', 'ACTIVE'),
(217, 'United states', 'ACTIVE'),
(218, 'Uruguay', 'ACTIVE'),
(219, 'Uzbekistan', 'ACTIVE'),
(220, 'Holysee(vatican city state)', 'ACTIVE'),
(221, 'Venezuela', 'ACTIVE'),
(222, 'Virgin islands,british', 'ACTIVE'),
(223, 'Virgin islands,u.s.', 'ACTIVE'),
(224, 'Vietnam', 'ACTIVE'),
(225, 'Vanuatu', 'ACTIVE'),
(226, 'Wallis and futuna', 'ACTIVE'),
(227, 'Samoa', 'ACTIVE'),
(228, 'Yemen', 'ACTIVE'),
(229, 'Mayotte', 'ACTIVE'),
(230, 'South africa', 'ACTIVE'),
(231, 'Zambia', 'ACTIVE'),
(232, 'Zimbabwe', 'ACTIVE');

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `course`
--

CREATE TABLE IF NOT EXISTS `course` (
  `serial_crs` int(11) NOT NULL AUTO_INCREMENT,
  `serial_clg` int(11) DEFAULT NULL,
  `serial_spt` int(11) DEFAULT NULL,
  `serial_lvl` int(11) DEFAULT NULL,
  `serial_scy` int(11) DEFAULT NULL,
  `serial_set` int(11) DEFAULT NULL,
  `attendance_crs` enum('DAY','CLASS') NOT NULL DEFAULT 'DAY',
  `status_crs` enum('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  PRIMARY KEY (`serial_crs`),
  KEY `fk_selected_class` (`serial_clg`),
  KEY `fk_selected_level_speciality` (`serial_spt`,`serial_lvl`,`serial_scy`,`serial_set`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=13 ;

--
-- Volcar la base de datos para la tabla `course`
--

INSERT INTO `course` (`serial_crs`, `serial_clg`, `serial_spt`, `serial_lvl`, `serial_scy`, `serial_set`, `attendance_crs`, `status_crs`) VALUES
(1, 1, 1, 1, 1, 1, 'DAY', 'ACTIVE'),
(2, 2, 1, 1, 1, 1, 'DAY', 'ACTIVE'),
(3, 1, 1, 2, 1, 1, 'DAY', 'ACTIVE'),
(4, 2, 1, 2, 1, 1, 'DAY', 'ACTIVE'),
(5, 1, 2, 3, 1, 2, 'DAY', 'ACTIVE'),
(6, 2, 2, 3, 1, 2, 'DAY', 'ACTIVE'),
(7, 1, 4, 3, 1, 2, 'DAY', 'ACTIVE'),
(8, 2, 4, 3, 1, 2, 'DAY', 'ACTIVE'),
(9, 1, 2, 4, 1, 2, 'DAY', 'ACTIVE'),
(10, 2, 2, 4, 1, 2, 'DAY', 'ACTIVE'),
(11, 1, 4, 4, 1, 2, 'CLASS', 'ACTIVE'),
(12, 2, 4, 4, 1, 2, 'CLASS', 'ACTIVE');

--
-- (Evento) desencadenante `course`
--
DROP TRIGGER IF EXISTS `course_trg`;
DELIMITER //
CREATE TRIGGER `course_trg` BEFORE UPDATE ON `course`
 FOR EACH ROW BEGIN
	IF NEW.status_crs = 'INACTIVE' THEN
		/*change dependent course_subject status to INACTIVE 
		when a course is changed to INACTIVE*/
		UPDATE subject_course sbc
		SET sbc.status_sbc = 'INACTIVE'
		WHERE sbc.serial_crs = OLD.serial_crs;
		/*change student by course status to INACTIVE 
		when a course is changed to INACTIVE*/
		UPDATE student_by_course stc
		SET stc.status_stc = 'INACTIVE'
		WHERE stc.serial_crs = OLD.serial_crs;
	END IF;
END
//
DELIMITER ;

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `course_director`
--

CREATE TABLE IF NOT EXISTS `course_director` (
  `serial_emp` int(11) DEFAULT NULL,
  `serial_crs` int(11) DEFAULT NULL,
  `status_crd` enum('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  KEY `fk_relationship_144` (`serial_emp`),
  KEY `fk_relationship_145` (`serial_crs`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Volcar la base de datos para la tabla `course_director`
--


-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `credit_card`
--

CREATE TABLE IF NOT EXISTS `credit_card` (
  `serial_ccd` int(11) NOT NULL AUTO_INCREMENT,
  `name_ccd` varchar(200) NOT NULL,
  `status_ccd` enum('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  PRIMARY KEY (`serial_ccd`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

--
-- Volcar la base de datos para la tabla `credit_card`
--


-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `currency`
--

CREATE TABLE IF NOT EXISTS `currency` (
  `serial_crr` int(11) NOT NULL AUTO_INCREMENT,
  `abrevation_crr` varchar(4) DEFAULT NULL,
  `name_crr` varchar(300) NOT NULL,
  `status_crr` enum('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  PRIMARY KEY (`serial_crr`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=49 ;

--
-- Volcar la base de datos para la tabla `currency`
--

INSERT INTO `currency` (`serial_crr`, `abrevation_crr`, `name_crr`, `status_crr`) VALUES
(1, 'ARS', 'Argentine Peso', 'ACTIVE'),
(2, 'AUD', 'Australian Dollar', 'ACTIVE'),
(3, 'BHD', 'Bahrain Dinar', 'ACTIVE'),
(4, 'BWP', 'Botswana Pula', 'ACTIVE'),
(5, 'BRL', 'Brazilian Real', 'ACTIVE'),
(6, 'BND', 'Brunei Dollar', 'ACTIVE'),
(7, 'CAD', 'Canadian Dollar', 'ACTIVE'),
(8, 'CLP', 'Chilean Peso', 'ACTIVE'),
(9, 'CNY', 'Chinese Yuan', 'ACTIVE'),
(10, 'COP', 'Colombian Peso', 'ACTIVE'),
(11, 'CYP', 'Cyprus Pound', 'ACTIVE'),
(12, 'CZK', 'Czech Koruna', 'ACTIVE'),
(13, 'DKK', 'Danish Krone', 'ACTIVE'),
(14, 'EUR', 'Euro', 'ACTIVE'),
(15, 'HUF', 'Hungarian Forint', 'ACTIVE'),
(16, 'ISK', 'Icelandic Krona', 'ACTIVE'),
(17, 'INR', 'Indian Rupee', 'ACTIVE'),
(18, 'IDR', 'Indonesian Rupiah', 'ACTIVE'),
(19, 'IRR', 'Iranian Rial', 'ACTIVE'),
(20, 'ILS', 'Israeli New Sheqel', 'ACTIVE'),
(21, 'JPY', 'Japanese Yen', 'ACTIVE'),
(22, 'KRW', 'Korean Won', 'ACTIVE'),
(23, 'KWD', 'Kuwaiti Dinar', 'ACTIVE'),
(24, 'LYD', 'Libyan Dinar', 'ACTIVE'),
(25, 'MYR', 'Malaysian Ringgit', 'ACTIVE'),
(26, 'MTL', 'Maltese Lira', 'ACTIVE'),
(27, 'MUR', 'Mauritian Rupee', 'ACTIVE'),
(28, 'MXN', 'Mexican Peso', 'ACTIVE'),
(29, 'NPR', 'Nepalese Rupee', 'ACTIVE'),
(30, 'NZD', 'New Zealand Dollar', 'ACTIVE'),
(31, 'NOK', 'Norwegian Krone', 'ACTIVE'),
(32, 'OMR', 'Omani Rial', 'ACTIVE'),
(33, 'PKR', 'Pakistan Rupee', 'ACTIVE'),
(34, 'PLN', 'Polish Zloty', 'ACTIVE'),
(35, 'GBP', 'Pound Sterling', 'ACTIVE'),
(36, 'QAR', 'Qatar Riyal', 'ACTIVE'),
(37, 'SAR', 'Saudi Arabian Riyal', 'ACTIVE'),
(38, 'SGD', 'Singapore Dollar', 'ACTIVE'),
(39, 'SIT', 'Slovenian Tolar', 'ACTIVE'),
(40, 'ZAR', 'South African Rand', 'ACTIVE'),
(41, 'LKR', 'Sri Lanka Rupee', 'ACTIVE'),
(42, 'SEK', 'Swedish Krona', 'ACTIVE'),
(43, 'CHF', 'Swiss Franc', 'ACTIVE'),
(44, 'THB', 'Thai Baht', 'ACTIVE'),
(45, 'TTD', 'Trinidad and Tobago Dollar', 'ACTIVE'),
(46, 'AED', 'U.A.E.Dirham', 'ACTIVE'),
(47, 'USD', 'U.S.Dollar', 'ACTIVE'),
(48, 'VEB', 'Venezuelan Bolivar', 'ACTIVE');

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `current_deferred`
--

CREATE TABLE IF NOT EXISTS `current_deferred` (
  `serial_cdf` int(11) NOT NULL AUTO_INCREMENT,
  `serial_deb` int(11) DEFAULT NULL,
  `total_quotas_cdf` int(11) NOT NULL,
  `quota_amount_cdf` decimal(6,2) NOT NULL,
  `paid_quotas_cdf` int(11) NOT NULL,
  `last_month_paid_quota_cdf` varchar(3) DEFAULT NULL,
  `status_cdf` enum('ACTIVE','INACTIVE','PAID') NOT NULL DEFAULT 'ACTIVE',
  PRIMARY KEY (`serial_cdf`),
  KEY `fk_relationship_170` (`serial_deb`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

--
-- Volcar la base de datos para la tabla `current_deferred`
--


-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `degree`
--

CREATE TABLE IF NOT EXISTS `degree` (
  `serial_dgr` int(11) NOT NULL AUTO_INCREMENT,
  `name_dgr` varchar(255) NOT NULL,
  `status_dgr` enum('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  PRIMARY KEY (`serial_dgr`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=23 ;

--
-- Volcar la base de datos para la tabla `degree`
--

INSERT INTO `degree` (`serial_dgr`, `name_dgr`, `status_dgr`) VALUES
(1, 'Artes aplicadas', 'ACTIVE'),
(2, 'Auxiliar de laboratorio de ciencias naturales y Biología', 'ACTIVE'),
(3, 'Ciencias exactas', 'ACTIVE'),
(4, 'Ciencias sociales', 'ACTIVE'),
(5, 'Comercio y administración', 'ACTIVE'),
(6, 'Comunicación educativa', 'ACTIVE'),
(7, 'Contabilidad y administración', 'ACTIVE'),
(8, 'Diseño y dibujo', 'ACTIVE'),
(9, 'Docencia y desarrollo comunitario', 'ACTIVE'),
(10, 'Docencia y gestión en educación básica', 'ACTIVE'),
(11, 'Ecoturismo', 'ACTIVE'),
(12, 'Educación artística', 'ACTIVE'),
(13, 'Educación primaria', 'ACTIVE'),
(14, 'Educación especial', 'ACTIVE'),
(15, 'Educación inicial', 'ACTIVE'),
(16, 'Educación parvularia', 'ACTIVE'),
(17, 'Educación superior', 'ACTIVE'),
(18, 'Electricidad', 'ACTIVE'),
(19, 'Diplomado', 'ACTIVE'),
(20, 'Especialista', 'ACTIVE'),
(21, 'Phd', 'ACTIVE'),
(22, 'Máster', 'ACTIVE');

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `discipline`
--

CREATE TABLE IF NOT EXISTS `discipline` (
  `serial_prd` int(11) NOT NULL,
  `serial_std` int(11) NOT NULL,
  `serial_spt` int(11) NOT NULL,
  `serial_lvl` int(11) NOT NULL,
  `serial_scy` int(11) NOT NULL,
  `serial_set` int(11) NOT NULL,
  `supervisor_grade_dsc` decimal(5,2) DEFAULT NULL,
  `subjects_grade_dsc` decimal(5,2) DEFAULT NULL,
  `general_grade_dsc` decimal(5,2) DEFAULT NULL,
  PRIMARY KEY (`serial_prd`,`serial_std`,`serial_spt`,`serial_lvl`,`serial_scy`,`serial_set`),
  KEY `fk_relationship_130` (`serial_std`,`serial_spt`,`serial_lvl`,`serial_scy`,`serial_set`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Volcar la base de datos para la tabla `discipline`
--


-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `discount`
--

CREATE TABLE IF NOT EXISTS `discount` (
  `serial_dsc` int(11) NOT NULL AUTO_INCREMENT,
  `name_dsc` varchar(200) NOT NULL,
  `status_dsc` enum('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  PRIMARY KEY (`serial_dsc`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

--
-- Volcar la base de datos para la tabla `discount`
--


-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `discount_payment_concept`
--

CREATE TABLE IF NOT EXISTS `discount_payment_concept` (
  `serial_ydc` int(11) NOT NULL,
  `serial_pct` int(11) NOT NULL,
  `status_dpc` enum('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  PRIMARY KEY (`serial_ydc`,`serial_pct`),
  KEY `fk_relationship_227` (`serial_pct`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Volcar la base de datos para la tabla `discount_payment_concept`
--


-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `employee`
--

CREATE TABLE IF NOT EXISTS `employee` (
  `serial_emp` int(11) NOT NULL AUTO_INCREMENT,
  `serial_tdg` int(11) DEFAULT NULL,
  `serial_usr` int(11) NOT NULL,
  `serial_grd` int(11) DEFAULT NULL,
  `serial_mdg` int(11) DEFAULT NULL,
  `serial_ste` int(11) DEFAULT NULL,
  `serial_cou` int(11) DEFAULT NULL,
  `serial_nnt` int(11) DEFAULT NULL,
  `serial_tst` int(11) DEFAULT NULL,
  `serial_unv` int(11) DEFAULT NULL,
  `serial_tmd` int(11) DEFAULT NULL,
  `serial_dgr` int(11) DEFAULT NULL,
  `id_emp` varchar(13) NOT NULL,
  `type_emp` set('TEACHING','ADMINISTRATIVE','SERVICES') DEFAULT NULL,
  `status_emp` enum('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  `first_name_emp` varchar(255) NOT NULL,
  `last_name_emp` varchar(255) NOT NULL,
  `specialty_emp` varchar(150) DEFAULT NULL,
  `gender_emp` enum('MALE','FEMALE') NOT NULL,
  `marital_status_emp` enum('SINGLE','MARRIED','DIVORCED','WIDOWED','UNMARRIED PARTNERS') DEFAULT NULL,
  `birthdate_emp` date NOT NULL,
  `address_emp` text NOT NULL,
  `city_emp` varchar(150) NOT NULL,
  `parish_emp` varchar(100) DEFAULT NULL,
  `sector_emp` varchar(50) DEFAULT NULL,
  `phone_emp` varchar(30) NOT NULL,
  `phone2_emp` varchar(30) DEFAULT NULL,
  `phone3_emp` varchar(30) DEFAULT NULL,
  `cellphone_emp` varchar(30) DEFAULT NULL,
  `email_emp` varchar(150) DEFAULT NULL,
  `email2_emp` varchar(150) DEFAULT NULL,
  `family_dependents_emp` int(11) DEFAULT NULL,
  `student_dependents_emp` int(11) DEFAULT NULL,
  `teaching_since_tea_emp` date DEFAULT NULL,
  `seniority_university_tea_emp` int(11) DEFAULT NULL,
  `date_start_in_school_emp` date NOT NULL,
  PRIMARY KEY (`serial_emp`),
  KEY `fk_employee_birth_country` (`serial_cou`),
  KEY `fk_employee_birth_state` (`serial_ste`),
  KEY `fk_employee_guardian2` (`serial_grd`),
  KEY `fk_employee_user` (`serial_usr`),
  KEY `fk_relationship_54` (`serial_nnt`),
  KEY `fk_relationship_55` (`serial_tdg`),
  KEY `fk_relationship_56` (`serial_tmd`),
  KEY `fk_relationship_57` (`serial_tst`),
  KEY `fk_relationship_58` (`serial_dgr`),
  KEY `fk_relationship_59` (`serial_mdg`),
  KEY `fk_relationship_60` (`serial_unv`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

--
-- Volcar la base de datos para la tabla `employee`
--

INSERT INTO `employee` (`serial_emp`, `serial_tdg`, `serial_usr`, `serial_grd`, `serial_mdg`, `serial_ste`, `serial_cou`, `serial_nnt`, `serial_tst`, `serial_unv`, `serial_tmd`, `serial_dgr`, `id_emp`, `type_emp`, `status_emp`, `first_name_emp`, `last_name_emp`, `specialty_emp`, `gender_emp`, `marital_status_emp`, `birthdate_emp`, `address_emp`, `city_emp`, `parish_emp`, `sector_emp`, `phone_emp`, `phone2_emp`, `phone3_emp`, `cellphone_emp`, `email_emp`, `email2_emp`, `family_dependents_emp`, `student_dependents_emp`, `teaching_since_tea_emp`, `seniority_university_tea_emp`, `date_start_in_school_emp`) VALUES
(1, NULL, 3, NULL, NULL, 19, 61, 1, NULL, NULL, NULL, NULL, '0103438180', 'TEACHING', 'ACTIVE', 'Manuel', 'Chicaiza', '', 'MALE', 'SINGLE', '1981-02-11', 'tin tin dsfsf ', 'Quito', '', '', '43242424', '', '', '', '', '', 0, 0, NULL, NULL, '2011-02-07'),
(2, NULL, 2, 1, NULL, 4, 61, 11, NULL, NULL, NULL, 2, '1715492656', 'TEACHING', 'ACTIVE', 'Nelson', 'Mandela', '', 'MALE', 'MARRIED', '1986-02-12', 'casa', 'Tulcán', '', '', '222222222', '', '', '', '', '', 0, 0, NULL, NULL, '2011-02-07'),
(3, NULL, 4, NULL, NULL, 48, 24, 19, NULL, NULL, NULL, NULL, '0103438172', 'TEACHING', 'ACTIVE', 'Manuela', 'Herdoiza', '', 'FEMALE', 'SINGLE', '1978-09-29', 'd gdf df gdf gdf gfff', 'Chagultepec', '', '', '443433434', '', '', '', '', '', 0, 0, NULL, NULL, '2011-02-07');

--
-- (Evento) desencadenante `employee`
--
DROP TRIGGER IF EXISTS `employee_employee_area_trg`;
DELIMITER //
CREATE TRIGGER `employee_employee_area_trg` BEFORE UPDATE ON `employee`
 FOR EACH ROW BEGIN
	/*change employee_area status to INACTIVE 
	when an employee status is changed to INACTIVE*/
	IF NEW.status_emp = 'INACTIVE' THEN
		UPDATE employee_area ea
		SET ea.status_eba = 'INACTIVE',
			ea.is_director_eba = 'NO'
		WHERE ea.serial_emp = OLD.serial_emp;
	END IF;
END
//
DELIMITER ;

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `employee_area`
--

CREATE TABLE IF NOT EXISTS `employee_area` (
  `serial_aca` int(11) NOT NULL,
  `serial_emp` int(11) NOT NULL,
  `is_director_eba` enum('YES','NO') DEFAULT 'NO',
  `status_eba` enum('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  PRIMARY KEY (`serial_aca`,`serial_emp`),
  KEY `fk_employee_in_area` (`serial_emp`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Volcar la base de datos para la tabla `employee_area`
--

INSERT INTO `employee_area` (`serial_aca`, `serial_emp`, `is_director_eba`, `status_eba`) VALUES
(1, 1, 'NO', 'ACTIVE'),
(1, 3, 'NO', 'ACTIVE'),
(2, 2, 'NO', 'ACTIVE'),
(2, 3, 'NO', 'ACTIVE'),
(3, 1, 'NO', 'ACTIVE'),
(3, 2, 'NO', 'ACTIVE');

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `financial_institution`
--

CREATE TABLE IF NOT EXISTS `financial_institution` (
  `serial_fin` int(11) NOT NULL AUTO_INCREMENT,
  `name_fin` varchar(255) NOT NULL,
  `phone1_fin` varchar(30) NOT NULL,
  `phone2_fin` varchar(30) NOT NULL,
  `status_fin` enum('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  PRIMARY KEY (`serial_fin`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

--
-- Volcar la base de datos para la tabla `financial_institution`
--


-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `general_activity`
--

CREATE TABLE IF NOT EXISTS `general_activity` (
  `serial_gac` int(11) NOT NULL AUTO_INCREMENT,
  `title_gac` varchar(50) NOT NULL,
  `description_gac` text NOT NULL,
  `create_date_gac` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `from_date_gac` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `to_date_gac` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `type_gac` enum('GENERAL','PERSONAL') NOT NULL DEFAULT 'GENERAL',
  `attachment_gac` varchar(128) DEFAULT NULL,
  `filename_gac` varchar(128) DEFAULT NULL,
  `status_gac` enum('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  PRIMARY KEY (`serial_gac`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

--
-- Volcar la base de datos para la tabla `general_activity`
--


--
-- (Evento) desencadenante `general_activity`
--
DROP TRIGGER IF EXISTS `general_activity_trg`;
DELIMITER //
CREATE TRIGGER `general_activity_trg` BEFORE UPDATE ON `general_activity`
 FOR EACH ROW BEGIN
	/*change general_activity_attendees to INACTIVE if general_activity is INACTIVE*/
	IF NEW.status_gac LIKE '%INACTIVE%' THEN
		UPDATE general_activity_attendees gaa
		SET gaa.status_gaa = 'INACTIVE'
		WHERE gaa.serial_gac = OLD.serial_gac;
	END IF;
END
//
DELIMITER ;

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `general_activity_attendees`
--

CREATE TABLE IF NOT EXISTS `general_activity_attendees` (
  `serial_gaa` int(11) NOT NULL AUTO_INCREMENT,
  `serial_scy` int(11) DEFAULT NULL,
  `serial_gac` int(11) DEFAULT NULL,
  `type_gaa` enum('ALL','SECTION','COURSE','PERSONAL','TEACHER','EMPLOYEE','ACADEMIC_AREA') NOT NULL,
  `remote_id_gaa` int(11) DEFAULT NULL,
  `comments_gaa` text,
  `status_gaa` enum('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  PRIMARY KEY (`serial_gaa`),
  KEY `fk_relationship_139` (`serial_scy`),
  KEY `fk_relationship_140` (`serial_gac`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

--
-- Volcar la base de datos para la tabla `general_activity_attendees`
--


-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `general_discipline_log`
--

CREATE TABLE IF NOT EXISTS `general_discipline_log` (
  `serial_usr` int(11) DEFAULT NULL,
  `serial_chr` int(11) DEFAULT NULL,
  `serial_prd` int(11) DEFAULT NULL,
  `serial_std` int(11) DEFAULT NULL,
  `serial_spt` int(11) DEFAULT NULL,
  `serial_lvl` int(11) DEFAULT NULL,
  `serial_scy` int(11) DEFAULT NULL,
  `serial_set` int(11) DEFAULT NULL,
  `change_date_gdl` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `last_grade_gdl` decimal(5,2) NOT NULL,
  `comment_gdl` text,
  KEY `fk_relationship_151` (`serial_usr`),
  KEY `fk_relationship_152` (`serial_prd`,`serial_std`,`serial_spt`,`serial_lvl`,`serial_scy`,`serial_set`),
  KEY `fk_relationship_153` (`serial_chr`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Volcar la base de datos para la tabla `general_discipline_log`
--


-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `grade_change_log`
--

CREATE TABLE IF NOT EXISTS `grade_change_log` (
  `serial_usr` int(11) DEFAULT NULL,
  `serial_chr` int(11) DEFAULT NULL,
  `serial_sbc` int(11) DEFAULT NULL,
  `serial_att` int(11) DEFAULT NULL,
  `serial_prd` int(11) DEFAULT NULL,
  `serial_gct` int(11) DEFAULT NULL,
  `serial_act` int(11) DEFAULT NULL,
  `serial_std` int(11) DEFAULT NULL,
  `serial_crs` int(11) DEFAULT NULL,
  `change_date_gcl` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `last_grade_gcl` decimal(5,2) DEFAULT NULL,
  `comment_gcl` text,
  KEY `fk_relationship_123` (`serial_sbc`,`serial_att`,`serial_prd`,`serial_gct`,`serial_act`,`serial_std`,`serial_crs`),
  KEY `fk_relationship_124` (`serial_usr`),
  KEY `fk_relationship_150` (`serial_chr`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Volcar la base de datos para la tabla `grade_change_log`
--


-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `grade_criteria`
--

CREATE TABLE IF NOT EXISTS `grade_criteria` (
  `serial_gct` int(11) NOT NULL AUTO_INCREMENT,
  `name_gct` varchar(256) NOT NULL,
  `description_gct` text NOT NULL,
  `status_gct` enum('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  PRIMARY KEY (`serial_gct`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

--
-- Volcar la base de datos para la tabla `grade_criteria`
--

INSERT INTO `grade_criteria` (`serial_gct`, `name_gct`, `description_gct`, `status_gct`) VALUES
(1, '* General', 'Criterio General,se usa cuando no se va a aplicar criterios de calificación.', 'ACTIVE'),
(2, 'Orden', 'ads sdf sd f', 'ACTIVE'),
(3, 'Consistencia', 'fd s fsd fs f', 'ACTIVE'),
(4, 'Aseo', 'ad fs fs f', 'ACTIVE');

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `grade_score_change_log`
--

CREATE TABLE IF NOT EXISTS `grade_score_change_log` (
  `serial_chr` int(11) DEFAULT NULL,
  `serial_usr` int(11) DEFAULT NULL,
  `serial_ygs` int(11) DEFAULT NULL,
  `serial_std` int(11) DEFAULT NULL,
  `change_date_gsl` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `last_grade_gsl` decimal(5,2) NOT NULL,
  `comment_gsl` text,
  KEY `fk_relationship_237` (`serial_ygs`,`serial_std`),
  KEY `fk_relationship_238` (`serial_chr`),
  KEY `fk_relationship_239` (`serial_usr`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Volcar la base de datos para la tabla `grade_score_change_log`
--


-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `grade_subject_score`
--

CREATE TABLE IF NOT EXISTS `grade_subject_score` (
  `serial_ygs` int(11) NOT NULL,
  `serial_std` int(11) NOT NULL,
  `serial_spt` int(11) DEFAULT NULL,
  `serial_lvl` int(11) DEFAULT NULL,
  `serial_scy` int(11) DEFAULT NULL,
  `serial_set` int(11) DEFAULT NULL,
  `score_gss` decimal(5,2) DEFAULT NULL,
  `status_gss` enum('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  PRIMARY KEY (`serial_ygs`,`serial_std`),
  KEY `fk_relationship_177` (`serial_std`,`serial_spt`,`serial_lvl`,`serial_scy`,`serial_set`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Volcar la base de datos para la tabla `grade_subject_score`
--


-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `group_hour`
--

CREATE TABLE IF NOT EXISTS `group_hour` (
  `serial_gph` int(11) NOT NULL AUTO_INCREMENT,
  `name_gph` varchar(100) NOT NULL,
  `description_gph` text,
  `status_gph` enum('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  PRIMARY KEY (`serial_gph`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

--
-- Volcar la base de datos para la tabla `group_hour`
--

INSERT INTO `group_hour` (`serial_gph`, `name_gph`, `description_gph`, `status_gph`) VALUES
(1, 'Primaria', 'sf sd fsd  fs', 'ACTIVE');

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `group_hour_level`
--

CREATE TABLE IF NOT EXISTS `group_hour_level` (
  `serial_gph` int(11) NOT NULL,
  `serial_lvl` int(11) NOT NULL,
  `serial_scy` int(11) NOT NULL,
  `serial_set` int(11) NOT NULL,
  `working_days_ghl` set('MONDAY','TUESDAY','WEDNESDAY','THURSDAY','FRIDAY','SATURDAY','SUNDAY') DEFAULT NULL,
  `status_ghl` enum('ACTIVE','INACTIVE') DEFAULT 'ACTIVE',
  PRIMARY KEY (`serial_gph`,`serial_lvl`,`serial_scy`,`serial_set`),
  KEY `fk_levels_in_group_hour` (`serial_lvl`,`serial_scy`,`serial_set`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Volcar la base de datos para la tabla `group_hour_level`
--

INSERT INTO `group_hour_level` (`serial_gph`, `serial_lvl`, `serial_scy`, `serial_set`, `working_days_ghl`, `status_ghl`) VALUES
(1, 1, 1, 1, 'MONDAY,TUESDAY,WEDNESDAY,THURSDAY,FRIDAY', 'ACTIVE'),
(1, 2, 1, 1, 'MONDAY,TUESDAY,WEDNESDAY,THURSDAY,FRIDAY', 'ACTIVE'),
(1, 3, 1, 2, 'MONDAY,TUESDAY,WEDNESDAY,THURSDAY', 'ACTIVE'),
(1, 4, 1, 2, 'MONDAY,TUESDAY,WEDNESDAY,THURSDAY', 'ACTIVE');

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `guardian`
--

CREATE TABLE IF NOT EXISTS `guardian` (
  `serial_grd` int(11) NOT NULL AUTO_INCREMENT,
  `serial_usr` int(11) NOT NULL,
  `serial_emp` int(11) DEFAULT NULL,
  `id_grd` varchar(13) NOT NULL,
  `first_name_grd` varchar(512) NOT NULL,
  `last_name_grd` varchar(512) NOT NULL,
  `birthdate_grd` date NOT NULL,
  `gender_grd` enum('MALE','FEMALE') NOT NULL,
  `address_grd` text NOT NULL,
  `phone_grd` varchar(30) NOT NULL,
  `occupation_grd` varchar(50) DEFAULT NULL,
  `company_name_grd` varchar(100) DEFAULT NULL,
  `company_activity_grd` varchar(80) DEFAULT NULL,
  `company_phone_grd` varchar(13) DEFAULT NULL,
  `email_grd` varchar(256) DEFAULT NULL,
  `email2_grd` varchar(150) DEFAULT NULL,
  `status_grd` enum('ACTIVE','INACTIVE','BLOCKED') NOT NULL DEFAULT 'ACTIVE',
  PRIMARY KEY (`serial_grd`),
  KEY `fk_employee_guardian` (`serial_emp`),
  KEY `fk_guardian_user` (`serial_usr`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

--
-- Volcar la base de datos para la tabla `guardian`
--

INSERT INTO `guardian` (`serial_grd`, `serial_usr`, `serial_emp`, `id_grd`, `first_name_grd`, `last_name_grd`, `birthdate_grd`, `gender_grd`, `address_grd`, `phone_grd`, `occupation_grd`, `company_name_grd`, `company_activity_grd`, `company_phone_grd`, `email_grd`, `email2_grd`, `status_grd`) VALUES
(1, 2, 2, '1715492656', 'Nelson', 'Mandela', '1986-02-12', 'MALE', 'casa', '222222222', 'Profe', '', '', '', '', '', 'ACTIVE');

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `guest`
--

CREATE TABLE IF NOT EXISTS `guest` (
  `serial_gst` int(11) NOT NULL AUTO_INCREMENT,
  `id_gst` varchar(20) NOT NULL,
  `first_name_gst` varchar(128) NOT NULL,
  `last_name_gst` varchar(128) NOT NULL,
  `birthdate_gst` date NOT NULL,
  `gender_gst` enum('MALE','FEMALE') NOT NULL,
  `status_gst` enum('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  PRIMARY KEY (`serial_gst`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

--
-- Volcar la base de datos para la tabla `guest`
--


-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `hour`
--

CREATE TABLE IF NOT EXISTS `hour` (
  `serial_hrs` int(11) NOT NULL AUTO_INCREMENT,
  `start_hrs` time NOT NULL,
  `end_hrs` time NOT NULL,
  `status_hrs` enum('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  PRIMARY KEY (`serial_hrs`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

--
-- Volcar la base de datos para la tabla `hour`
--

INSERT INTO `hour` (`serial_hrs`, `start_hrs`, `end_hrs`, `status_hrs`) VALUES
(1, '07:00:00', '09:00:00', 'ACTIVE'),
(2, '09:00:00', '09:30:00', 'ACTIVE'),
(3, '09:30:00', '12:00:00', 'ACTIVE');

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `hour_by_group`
--

CREATE TABLE IF NOT EXISTS `hour_by_group` (
  `serial_hbg` int(11) NOT NULL AUTO_INCREMENT,
  `serial_hrs` int(11) DEFAULT NULL,
  `serial_gph` int(11) DEFAULT NULL,
  `status_hbg` enum('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  `credits_hbg` int(11) NOT NULL,
  `type_hbg` enum('NORMAL','LUNCH') NOT NULL DEFAULT 'NORMAL',
  PRIMARY KEY (`serial_hbg`),
  KEY `fk_for_group` (`serial_gph`),
  KEY `fk_is_in_group` (`serial_hrs`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

--
-- Volcar la base de datos para la tabla `hour_by_group`
--

INSERT INTO `hour_by_group` (`serial_hbg`, `serial_hrs`, `serial_gph`, `status_hbg`, `credits_hbg`, `type_hbg`) VALUES
(1, 1, 1, 'ACTIVE', 2, 'NORMAL'),
(2, 2, 1, 'ACTIVE', 0, 'LUNCH'),
(3, 3, 1, 'ACTIVE', 3, 'NORMAL');

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `incident`
--

CREATE TABLE IF NOT EXISTS `incident` (
  `serial_inc` int(11) NOT NULL AUTO_INCREMENT,
  `serial_usr` int(11) DEFAULT NULL,
  `use_serial_usr` int(11) DEFAULT NULL,
  `date_inc` date NOT NULL,
  `report_date_inc` date NOT NULL,
  `description_inc` text NOT NULL,
  PRIMARY KEY (`serial_inc`),
  KEY `fk_relationship_194` (`use_serial_usr`),
  KEY `fk_relationship_195` (`serial_usr`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

--
-- Volcar la base de datos para la tabla `incident`
--


-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `income_stock`
--

CREATE TABLE IF NOT EXISTS `income_stock` (
  `serial_ist` int(11) NOT NULL AUTO_INCREMENT,
  `serial_prv` int(11) DEFAULT NULL,
  `serial_usr` int(11) DEFAULT NULL,
  `serial_mti` int(11) DEFAULT NULL,
  `income_ist` decimal(5,2) NOT NULL,
  `cost_ist` decimal(5,2) NOT NULL,
  `income_date_ist` date NOT NULL,
  `available_ist` decimal(5,2) NOT NULL,
  PRIMARY KEY (`serial_ist`),
  KEY `fk_relationship_184` (`serial_prv`),
  KEY `fk_relationship_185` (`serial_mti`),
  KEY `fk_relationship_186` (`serial_usr`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

--
-- Volcar la base de datos para la tabla `income_stock`
--


-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `invoice`
--

CREATE TABLE IF NOT EXISTS `invoice` (
  `serial_phy` int(11) NOT NULL AUTO_INCREMENT,
  `payment_date_phy` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `subtotal_phy` decimal(6,2) NOT NULL,
  `taxes_phy` decimal(6,2) NOT NULL,
  `total_phy` decimal(6,2) NOT NULL,
  `id_paying_phy` varchar(15) NOT NULL,
  `name_paying_phy` varchar(512) NOT NULL,
  `phone_paying_phy` varchar(30) DEFAULT NULL,
  `address_paying_phy` varchar(1024) DEFAULT NULL,
  `physical_number_phy` varchar(128) NOT NULL,
  `due_date_phy` date NOT NULL,
  `status_phy` enum('ACTIVE','INACTIVE','PAID','VOID') NOT NULL DEFAULT 'ACTIVE',
  PRIMARY KEY (`serial_phy`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

--
-- Volcar la base de datos para la tabla `invoice`
--


-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `invoice_detail`
--

CREATE TABLE IF NOT EXISTS `invoice_detail` (
  `serial_cdf` int(11) DEFAULT NULL,
  `serial_sva` int(11) DEFAULT NULL,
  `serial_deb` int(11) DEFAULT NULL,
  `serial_phy` int(11) DEFAULT NULL,
  `status_psd` enum('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  KEY `fk_relationship_155` (`serial_phy`),
  KEY `fk_relationship_156` (`serial_deb`),
  KEY `fk_relationship_171` (`serial_cdf`),
  KEY `fk_relationship_231` (`serial_sva`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Volcar la base de datos para la tabla `invoice_detail`
--


-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `item_category`
--

CREATE TABLE IF NOT EXISTS `item_category` (
  `serial_ict` int(11) NOT NULL AUTO_INCREMENT,
  `name_ict` varchar(160) NOT NULL,
  `description_ict` text NOT NULL,
  `status_ict` enum('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  PRIMARY KEY (`serial_ict`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

--
-- Volcar la base de datos para la tabla `item_category`
--


-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `language`
--

CREATE TABLE IF NOT EXISTS `language` (
  `serial_lng` int(11) NOT NULL AUTO_INCREMENT,
  `abbreviation_lng` varchar(3) DEFAULT NULL,
  `name_lng` varchar(50) NOT NULL,
  PRIMARY KEY (`serial_lng`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

--
-- Volcar la base de datos para la tabla `language`
--

INSERT INTO `language` (`serial_lng`, `abbreviation_lng`, `name_lng`) VALUES
(1, 'ES', 'Español'),
(2, 'EN', 'Inglés');

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `level`
--

CREATE TABLE IF NOT EXISTS `level` (
  `serial_lvl` int(11) NOT NULL AUTO_INCREMENT,
  `name_lvl` varchar(150) NOT NULL,
  `description_lvl` text,
  `status_lvl` enum('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  PRIMARY KEY (`serial_lvl`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

--
-- Volcar la base de datos para la tabla `level`
--

INSERT INTO `level` (`serial_lvl`, `name_lvl`, `description_lvl`, `status_lvl`) VALUES
(1, '8vo Básica', 'sd sd fds fs f ds', 'ACTIVE'),
(2, '9no Básica', 'sd fd fs f f', 'ACTIVE'),
(3, '1ro Bachillerato', 'sfd sf sdf', 'ACTIVE'),
(4, '2do Bachillerato', 's d fs f fs f', 'ACTIVE');

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `level_optional_group`
--

CREATE TABLE IF NOT EXISTS `level_optional_group` (
  `serial_lop` int(11) NOT NULL AUTO_INCREMENT,
  `serial_spt` int(11) DEFAULT NULL,
  `serial_lvl` int(11) DEFAULT NULL,
  `serial_scy` int(11) DEFAULT NULL,
  `serial_set` int(11) DEFAULT NULL,
  `name_lop` varchar(256) NOT NULL,
  `status_lop` enum('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  `hours_week_lop` int(11) NOT NULL,
  `hours_distribution_lop` varchar(512) DEFAULT NULL,
  PRIMARY KEY (`serial_lop`),
  KEY `fk_relationship_243` (`serial_spt`,`serial_lvl`,`serial_scy`,`serial_set`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=9 ;

--
-- Volcar la base de datos para la tabla `level_optional_group`
--

INSERT INTO `level_optional_group` (`serial_lop`, `serial_spt`, `serial_lvl`, `serial_scy`, `serial_set`, `name_lop`, `status_lop`, `hours_week_lop`, `hours_distribution_lop`) VALUES
(1, 1, 1, 1, 1, 'Artes', 'ACTIVE', 1, '1'),
(2, 1, 1, 1, 1, 'Música', 'ACTIVE', 2, '1,1'),
(3, 1, 2, 1, 1, 'Artes', 'ACTIVE', 1, '1'),
(4, 1, 2, 1, 1, 'Música', 'ACTIVE', 2, '1,1'),
(5, 2, 3, 1, 2, 'Ciencia', 'ACTIVE', 2, '2'),
(6, 2, 4, 1, 2, 'Social', 'ACTIVE', 1, '1'),
(7, 4, 3, 1, 2, 'Deportes', 'ACTIVE', 1, '1'),
(8, 4, 4, 1, 2, 'Plasticas', 'ACTIVE', 1, '1');

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `level_optional_group_detail`
--

CREATE TABLE IF NOT EXISTS `level_optional_group_detail` (
  `serial_prd` int(11) NOT NULL,
  `serial_lop` int(11) NOT NULL,
  `weight_ogd` decimal(5,2) NOT NULL,
  `status_ogd` enum('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  PRIMARY KEY (`serial_prd`,`serial_lop`),
  KEY `fk_relationship_244` (`serial_lop`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Volcar la base de datos para la tabla `level_optional_group_detail`
--

INSERT INTO `level_optional_group_detail` (`serial_prd`, `serial_lop`, `weight_ogd`, `status_ogd`) VALUES
(1, 1, '30.00', 'ACTIVE'),
(1, 2, '40.00', 'ACTIVE'),
(1, 3, '33.00', 'ACTIVE'),
(1, 4, '30.00', 'ACTIVE'),
(1, 5, '30.00', 'ACTIVE'),
(1, 6, '30.00', 'ACTIVE'),
(1, 7, '40.00', 'ACTIVE'),
(1, 8, '30.00', 'ACTIVE'),
(2, 1, '30.00', 'ACTIVE'),
(2, 2, '30.00', 'ACTIVE'),
(2, 3, '33.00', 'ACTIVE'),
(2, 4, '30.00', 'ACTIVE'),
(2, 5, '30.00', 'ACTIVE'),
(2, 6, '30.00', 'ACTIVE'),
(2, 7, '40.00', 'ACTIVE'),
(2, 8, '40.00', 'ACTIVE'),
(3, 1, '40.00', 'ACTIVE'),
(3, 2, '30.00', 'ACTIVE'),
(3, 3, '34.00', 'ACTIVE'),
(3, 4, '40.00', 'ACTIVE'),
(3, 5, '40.00', 'ACTIVE'),
(3, 6, '40.00', 'ACTIVE'),
(3, 7, '20.00', 'ACTIVE'),
(3, 8, '30.00', 'ACTIVE');

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `level_section_year`
--

CREATE TABLE IF NOT EXISTS `level_section_year` (
  `serial_pgy` int(11) DEFAULT NULL,
  `serial_lvl` int(11) NOT NULL,
  `discipline_lsy` enum('SUPERVISOR','TEACHERS','BOTH') DEFAULT NULL,
  `supervisor_grade_weight_lsy` decimal(5,2) DEFAULT NULL,
  `teacher_grade_weight_lsy` decimal(5,2) DEFAULT NULL,
  `order_lsy` int(11) DEFAULT NULL,
  `serial_scy` int(11) NOT NULL,
  `serial_set` int(11) NOT NULL,
  `status_lsy` enum('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  PRIMARY KEY (`serial_lvl`,`serial_scy`,`serial_set`),
  KEY `fk_period_group_level` (`serial_pgy`),
  KEY `fk_section_for_level` (`serial_scy`,`serial_set`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Volcar la base de datos para la tabla `level_section_year`
--

INSERT INTO `level_section_year` (`serial_pgy`, `serial_lvl`, `discipline_lsy`, `supervisor_grade_weight_lsy`, `teacher_grade_weight_lsy`, `order_lsy`, `serial_scy`, `serial_set`, `status_lsy`) VALUES
(1, 1, '', '0.00', '0.00', NULL, 1, 1, 'ACTIVE'),
(1, 2, '', '0.00', '0.00', NULL, 1, 1, 'ACTIVE'),
(1, 3, '', '0.00', '0.00', NULL, 1, 2, 'ACTIVE'),
(1, 4, '', '0.00', '0.00', NULL, 1, 2, 'ACTIVE');

--
-- (Evento) desencadenante `level_section_year`
--
DROP TRIGGER IF EXISTS `level_seccion_year_trg`;
DELIMITER //
CREATE TRIGGER `level_seccion_year_trg` BEFORE UPDATE ON `level_section_year`
 FOR EACH ROW BEGIN
	/*change level section year specialty status to INACTIVE 
	when a level section Year is changed to INACTIVE*/
	IF NEW.status_lsy = 'INACTIVE' THEN
		UPDATE level_section_year_specialty lsys
		SET lsys.status_lss = 'INACTIVE'
		WHERE lsys.serial_lvl = OLD.serial_lvl
		AND	lsys.serial_scy = OLD.serial_scy
		AND	lsys.serial_set = OLD.serial_set;
		/*change group hour level status to INACTIVE 
		when a level section Year is changed to INACTIVE*/
		UPDATE group_hour_level ghl
		SET ghl.status_ghl = 'INACTIVE'
		WHERE ghl.serial_lvl = OLD.serial_lvl
		AND	ghl.serial_scy = OLD.serial_scy
		AND	ghl.serial_set = OLD.serial_set;
	END IF;
END
//
DELIMITER ;

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `level_section_year_specialty`
--

CREATE TABLE IF NOT EXISTS `level_section_year_specialty` (
  `serial_spt` int(11) NOT NULL,
  `serial_lvl` int(11) NOT NULL,
  `serial_scy` int(11) NOT NULL,
  `serial_set` int(11) NOT NULL,
  `status_lss` enum('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  PRIMARY KEY (`serial_spt`,`serial_lvl`,`serial_scy`,`serial_set`),
  KEY `fk_selected_level` (`serial_lvl`,`serial_scy`,`serial_set`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Volcar la base de datos para la tabla `level_section_year_specialty`
--

INSERT INTO `level_section_year_specialty` (`serial_spt`, `serial_lvl`, `serial_scy`, `serial_set`, `status_lss`) VALUES
(1, 1, 1, 1, 'ACTIVE'),
(1, 2, 1, 1, 'ACTIVE'),
(2, 3, 1, 2, 'ACTIVE'),
(2, 4, 1, 2, 'ACTIVE'),
(4, 3, 1, 2, 'ACTIVE'),
(4, 4, 1, 2, 'ACTIVE');

--
-- (Evento) desencadenante `level_section_year_specialty`
--
DROP TRIGGER IF EXISTS `specialty_level_seccion_trg`;
DELIMITER //
CREATE TRIGGER `specialty_level_seccion_trg` BEFORE UPDATE ON `level_section_year_specialty`
 FOR EACH ROW BEGIN
	IF NEW.status_lss = 'INACTIVE' THEN
	/*change course status to INACTIVE 
	when a level section Year specialty is changed to INACTIVE*/
		UPDATE course c
		SET c.status_crs = 'INACTIVE'
		WHERE c.serial_spt = OLD.serial_spt
		AND	c.serial_lvl = OLD.serial_lvl
		AND	c.serial_scy = OLD.serial_scy
		AND	c.serial_set = OLD.serial_set;
	/*change dependent subject status to INACTIVE 
	when a level section Year specialty is changed to INACTIVE*/	
		UPDATE subject sbj
		SET sbj.status_sbj = 'INACTIVE'
		WHERE sbj.serial_spt = OLD.serial_spt
		AND	sbj.serial_lvl = OLD.serial_lvl
		AND	sbj.serial_scy = OLD.serial_scy
		AND	sbj.serial_set = OLD.serial_set;
	END IF;
END
//
DELIMITER ;

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `maintenance_item`
--

CREATE TABLE IF NOT EXISTS `maintenance_item` (
  `serial_mti` int(11) NOT NULL AUTO_INCREMENT,
  `serial_ict` int(11) DEFAULT NULL,
  `name_mti` varchar(160) NOT NULL,
  `description_mti` text NOT NULL,
  `brand_mti` varchar(128) NOT NULL,
  `status_mti` enum('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  PRIMARY KEY (`serial_mti`),
  KEY `fk_relationship_183` (`serial_ict`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

--
-- Volcar la base de datos para la tabla `maintenance_item`
--


-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `master_degree`
--

CREATE TABLE IF NOT EXISTS `master_degree` (
  `serial_mdg` int(11) NOT NULL AUTO_INCREMENT,
  `name_mdg` varchar(255) NOT NULL,
  `status_mdg` enum('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  PRIMARY KEY (`serial_mdg`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

--
-- Volcar la base de datos para la tabla `master_degree`
--


-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `medical_file`
--

CREATE TABLE IF NOT EXISTS `medical_file` (
  `serial_mdf` int(11) NOT NULL AUTO_INCREMENT,
  `serial_usr` int(11) DEFAULT NULL,
  `serial_gst` int(11) DEFAULT NULL,
  `chronic_disease_mdf` text,
  `weight_mdf` decimal(5,2) NOT NULL,
  `height_mdf` decimal(5,2) NOT NULL,
  `allergy_mdf` text,
  `allergy_medication_mdf` text,
  `reaction_medicine_mdf` text,
  `fever_medication_mdf` text NOT NULL,
  `headache_medication_mdf` text NOT NULL,
  `stomachache_medication_mdf` text NOT NULL,
  `registration_date_mdf` date NOT NULL,
  `status_mdf` enum('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  `family_physician_mdf` varchar(256) DEFAULT NULL,
  `emergency_call_name_mdf` varchar(256) DEFAULT NULL,
  `emergency_call_phone_mdf` varchar(13) DEFAULT NULL,
  `hospital_preference1_mdf` varchar(200) DEFAULT NULL,
  `hospital_preference2_mdf` varchar(200) DEFAULT NULL,
  `blood_type_mdf` varchar(10) DEFAULT NULL,
  `restrictions_mdf` text,
  `emergency_auth_mdf` enum('YES','NO') NOT NULL DEFAULT 'NO',
  PRIMARY KEY (`serial_mdf`),
  KEY `fk_relationship_196` (`serial_gst`),
  KEY `fk_relationship_65` (`serial_usr`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

--
-- Volcar la base de datos para la tabla `medical_file`
--


-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `message`
--

CREATE TABLE IF NOT EXISTS `message` (
  `serial_msg` int(11) NOT NULL AUTO_INCREMENT,
  `serial_usr_receive` int(11) DEFAULT NULL,
  `serial_prf` int(11) DEFAULT NULL,
  `serial_usr_send` int(11) DEFAULT NULL,
  `type_msg` enum('PRIVATE','PUBLIC') NOT NULL DEFAULT 'PUBLIC',
  `user_from_name_msg` varchar(256) NOT NULL,
  `user_to_name_msg` varchar(256) NOT NULL,
  `subject_msg` varchar(128) NOT NULL,
  `message_msg` text NOT NULL,
  `checked_msg` enum('YES','NO') NOT NULL DEFAULT 'NO',
  `saved_msg` enum('YES','NO') DEFAULT 'NO',
  `deleted_msg` enum('YES','NO') DEFAULT 'NO',
  `removed_permanently_msg` enum('YES','NO') DEFAULT 'NO',
  `send_date_msg` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `check_date_msg` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `filename_msg` varchar(50) DEFAULT NULL,
  `attachment_msg` varchar(50) DEFAULT NULL,
  `priority_msg` enum('URGENT','NORMAL') NOT NULL DEFAULT 'NORMAL',
  PRIMARY KEY (`serial_msg`),
  KEY `fk_relationship_136` (`serial_usr_send`),
  KEY `fk_relationship_137` (`serial_usr_receive`),
  KEY `fk_relationship_154` (`serial_prf`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

--
-- Volcar la base de datos para la tabla `message`
--

INSERT INTO `message` (`serial_msg`, `serial_usr_receive`, `serial_prf`, `serial_usr_send`, `type_msg`, `user_from_name_msg`, `user_to_name_msg`, `subject_msg`, `message_msg`, `checked_msg`, `saved_msg`, `deleted_msg`, `removed_permanently_msg`, `send_date_msg`, `check_date_msg`, `filename_msg`, `attachment_msg`, `priority_msg`) VALUES
(1, 2, 1, 1, 'PUBLIC', 'Administrador', 'Nelson Mandela', 'Inicio del Per&iacute;odo de Preinscripciones', 'El per&iacute;odo de Preinscripciones se encuentra abierto.<br/> Para el A&ntilde;o Lectivo: <b>Sierra 2010-2011</b>. Las Fechas de las preinscripciones son desde el <b>07/02/2011</b> hasta el <b>09/02/2011</b>', 'NO', 'NO', 'NO', 'NO', '2011-02-07 16:43:15', '2011-02-07 16:43:15', NULL, NULL, 'NORMAL');

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `message_between_profiles`
--

CREATE TABLE IF NOT EXISTS `message_between_profiles` (
  `serial_prf` int(11) NOT NULL,
  `pro_serial_prf` int(11) NOT NULL,
  `status_mbp` enum('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  PRIMARY KEY (`serial_prf`,`pro_serial_prf`),
  KEY `fk_relationship_142` (`pro_serial_prf`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Volcar la base de datos para la tabla `message_between_profiles`
--

INSERT INTO `message_between_profiles` (`serial_prf`, `pro_serial_prf`, `status_mbp`) VALUES
(1, 1, 'ACTIVE'),
(1, 2, 'ACTIVE'),
(1, 3, 'ACTIVE'),
(1, 4, 'ACTIVE'),
(1, 5, 'ACTIVE'),
(2, 4, 'ACTIVE'),
(2, 5, 'ACTIVE'),
(3, 3, 'ACTIVE'),
(3, 4, 'ACTIVE'),
(4, 2, 'ACTIVE'),
(4, 3, 'ACTIVE'),
(4, 4, 'ACTIVE'),
(5, 3, 'ACTIVE'),
(5, 4, 'ACTIVE'),
(5, 5, 'ACTIVE');

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `message_thread`
--

CREATE TABLE IF NOT EXISTS `message_thread` (
  `serial_mth` int(11) NOT NULL AUTO_INCREMENT,
  `serial_usr` int(11) DEFAULT NULL,
  `start_date_mth` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`serial_mth`),
  KEY `fk_relationship_138` (`serial_usr`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

--
-- Volcar la base de datos para la tabla `message_thread`
--


-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `message_thread_messages`
--

CREATE TABLE IF NOT EXISTS `message_thread_messages` (
  `serial_mtm` int(11) NOT NULL AUTO_INCREMENT,
  `serial_mth` int(11) DEFAULT NULL,
  `serial_msg` int(11) DEFAULT NULL,
  PRIMARY KEY (`serial_mtm`),
  KEY `fk_relationship_134` (`serial_mth`),
  KEY `fk_relationship_135` (`serial_msg`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

--
-- Volcar la base de datos para la tabla `message_thread_messages`
--


-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `ministry_resit_change_log`
--

CREATE TABLE IF NOT EXISTS `ministry_resit_change_log` (
  `serial_usr` int(11) DEFAULT NULL,
  `serial_chr` int(11) DEFAULT NULL,
  `serial_msb` int(11) DEFAULT NULL,
  `serial_std` int(11) DEFAULT NULL,
  `serial_spt` int(11) DEFAULT NULL,
  `serial_lvl` int(11) DEFAULT NULL,
  `serial_scy` int(11) DEFAULT NULL,
  `serial_set` int(11) DEFAULT NULL,
  `change_date_mrc` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `last_grade_mrc` decimal(5,2) NOT NULL,
  `comment_mrc` text,
  KEY `fk_relationship_254` (`serial_msb`,`serial_std`,`serial_spt`,`serial_lvl`,`serial_scy`,`serial_set`),
  KEY `fk_relationship_257` (`serial_usr`),
  KEY `fk_relationship_258` (`serial_chr`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Volcar la base de datos para la tabla `ministry_resit_change_log`
--


-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `ministry_resit_student`
--

CREATE TABLE IF NOT EXISTS `ministry_resit_student` (
  `serial_msb` int(11) NOT NULL,
  `serial_std` int(11) NOT NULL,
  `serial_spt` int(11) NOT NULL,
  `serial_lvl` int(11) NOT NULL,
  `serial_scy` int(11) NOT NULL,
  `serial_set` int(11) NOT NULL,
  `required_grade_mrs` decimal(5,2) NOT NULL,
  `grade_mrs` decimal(5,2) DEFAULT NULL,
  `status_mrs` enum('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  PRIMARY KEY (`serial_msb`,`serial_std`,`serial_spt`,`serial_lvl`,`serial_scy`,`serial_set`),
  KEY `fk_relationship_255` (`serial_std`,`serial_spt`,`serial_lvl`,`serial_scy`,`serial_set`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Volcar la base de datos para la tabla `ministry_resit_student`
--


-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `ministry_subject`
--

CREATE TABLE IF NOT EXISTS `ministry_subject` (
  `serial_msb` int(11) NOT NULL AUTO_INCREMENT,
  `name_msb` varchar(256) NOT NULL,
  `description_msb` text NOT NULL,
  `grade_type_msb` enum('QUALITATIVE','QUANTITATIVE') NOT NULL DEFAULT 'QUANTITATIVE',
  `status_msb` enum('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  PRIMARY KEY (`serial_msb`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

--
-- Volcar la base de datos para la tabla `ministry_subject`
--

INSERT INTO `ministry_subject` (`serial_msb`, `name_msb`, `description_msb`, `grade_type_msb`, `status_msb`) VALUES
(1, 'Matemáticas', 'sdfs d fsd fs dfs df', 'QUALITATIVE', 'ACTIVE'),
(2, 'Sociales', 'sdfdddddddd rr r  rb', 'QUANTITATIVE', 'ACTIVE'),
(3, 'Música', 'asd aaaa a', 'QUALITATIVE', 'ACTIVE');

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `nationality`
--

CREATE TABLE IF NOT EXISTS `nationality` (
  `serial_nnt` int(11) NOT NULL AUTO_INCREMENT,
  `name_nnt` varchar(255) NOT NULL,
  `status_nnt` enum('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  PRIMARY KEY (`serial_nnt`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=23 ;

--
-- Volcar la base de datos para la tabla `nationality`
--

INSERT INTO `nationality` (`serial_nnt`, `name_nnt`, `status_nnt`) VALUES
(1, 'ECUATORIANA', 'ACTIVE'),
(2, 'AFGANA', 'ACTIVE'),
(3, 'ALBANA', 'ACTIVE'),
(4, 'ALEMANA', 'ACTIVE'),
(5, 'ARGENTINA', 'ACTIVE'),
(6, 'BAHAMES', 'ACTIVE'),
(7, 'BELGA', 'ACTIVE'),
(8, 'BOLIVIANA', 'ACTIVE'),
(9, 'BRASILEÑA', 'ACTIVE'),
(10, 'CHILENA', 'ACTIVE'),
(11, 'COLOMBIANA', 'ACTIVE'),
(12, 'COSTARRICENSE', 'ACTIVE'),
(13, 'ESPAÑOLA', 'ACTIVE'),
(14, 'ESTADOUNIDENSE', 'ACTIVE'),
(15, 'ITALIANA', 'ACTIVE'),
(16, 'MEXICANA', 'ACTIVE'),
(17, 'PARAGUAYA', 'ACTIVE'),
(18, 'PERUANA', 'ACTIVE'),
(19, 'PORTUGUESA', 'ACTIVE'),
(20, 'PUERTORRIQUEÑA', 'ACTIVE'),
(21, 'SUIZA', 'ACTIVE'),
(22, 'VENEZOLANA', 'ACTIVE');

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `optional_subject`
--

CREATE TABLE IF NOT EXISTS `optional_subject` (
  `serial_osb` int(11) NOT NULL AUTO_INCREMENT,
  `serial_pgy` int(11) DEFAULT NULL,
  `serial_csb` int(11) DEFAULT NULL,
  `per_serial_pgy` int(11) DEFAULT NULL,
  `serial_aca` int(11) DEFAULT NULL,
  `serial_emp` int(11) DEFAULT NULL,
  `status_osb` enum('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  `grade_type_sbc` enum('QUALITATIVE','QUANTITATIVE') NOT NULL DEFAULT 'QUANTITATIVE',
  PRIMARY KEY (`serial_osb`),
  KEY `fk_relationship_116` (`per_serial_pgy`),
  KEY `fk_relationship_127` (`serial_aca`,`serial_emp`),
  KEY `fk_relationship_249` (`serial_pgy`),
  KEY `fk_relationship_53` (`serial_csb`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

--
-- Volcar la base de datos para la tabla `optional_subject`
--

INSERT INTO `optional_subject` (`serial_osb`, `serial_pgy`, `serial_csb`, `per_serial_pgy`, `serial_aca`, `serial_emp`, `status_osb`, `grade_type_sbc`) VALUES
(1, 1, 6, NULL, 2, 2, 'ACTIVE', 'QUALITATIVE');

--
-- (Evento) desencadenante `optional_subject`
--
DROP TRIGGER IF EXISTS `optional_subject_trg`;
DELIMITER //
CREATE TRIGGER `optional_subject_trg` BEFORE UPDATE ON `optional_subject`
 FOR EACH ROW BEGIN 
	IF NEW.status_osb = 'INACTIVE' THEN
		/*change optional_subject_level to INACTIVE if optional_subject is INACTIVE*/
		UPDATE optional_subject_level osl
		SET osl.status_osl = 'INACTIVE'
		WHERE osl.serial_osb = OLD.serial_osb;
		/*change optional_subject_period to INACTIVE if optional_subject is INACTIVE*/
		UPDATE optional_subject_period osp
		SET osp.status_osp = 'INACTIVE'
		WHERE osp.serial_osb = OLD.serial_osb;
		/*change student_optional_subject to INACTIVE if optional_subject is INACTIVE*/
		UPDATE student_optional_subject sos
		SET sos.status_sos = 'INACTIVE'
		WHERE sos.serial_osb = OLD.serial_osb;
	END IF;
END
//
DELIMITER ;

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `optional_subject_level`
--

CREATE TABLE IF NOT EXISTS `optional_subject_level` (
  `serial_osb` int(11) DEFAULT NULL,
  `serial_spt` int(11) DEFAULT NULL,
  `serial_lvl` int(11) DEFAULT NULL,
  `serial_scy` int(11) DEFAULT NULL,
  `serial_set` int(11) DEFAULT NULL,
  `status_osl` enum('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  KEY `fk_relationship_104` (`serial_spt`,`serial_lvl`,`serial_scy`,`serial_set`),
  KEY `fk_relationship_105` (`serial_osb`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Volcar la base de datos para la tabla `optional_subject_level`
--

INSERT INTO `optional_subject_level` (`serial_osb`, `serial_spt`, `serial_lvl`, `serial_scy`, `serial_set`, `status_osl`) VALUES
(1, 1, 1, 1, 1, 'ACTIVE'),
(1, 1, 2, 1, 1, 'ACTIVE'),
(1, 2, 3, 1, 2, 'ACTIVE'),
(1, 4, 3, 1, 2, 'ACTIVE'),
(1, 2, 4, 1, 2, 'ACTIVE'),
(1, 4, 4, 1, 2, 'ACTIVE');

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `optional_subject_period`
--

CREATE TABLE IF NOT EXISTS `optional_subject_period` (
  `serial_prd` int(11) NOT NULL,
  `serial_osb` int(11) NOT NULL,
  `status_osp` enum('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  PRIMARY KEY (`serial_prd`,`serial_osb`),
  KEY `fk_relationship_106` (`serial_osb`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Volcar la base de datos para la tabla `optional_subject_period`
--

INSERT INTO `optional_subject_period` (`serial_prd`, `serial_osb`, `status_osp`) VALUES
(1, 1, 'ACTIVE'),
(2, 1, 'ACTIVE'),
(3, 1, 'ACTIVE');

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `optional_subject_retest`
--

CREATE TABLE IF NOT EXISTS `optional_subject_retest` (
  `serial_osr` int(11) NOT NULL AUTO_INCREMENT,
  `serial_prd` int(11) DEFAULT NULL,
  `serial_osb` int(11) DEFAULT NULL,
  `description_osr` text NOT NULL,
  `sent_date_osr` date NOT NULL,
  `retest_date_osr` date NOT NULL,
  `weight_osr` decimal(5,2) NOT NULL,
  `status_osr` enum('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  PRIMARY KEY (`serial_osr`),
  KEY `fk_relationship_198` (`serial_prd`,`serial_osb`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

--
-- Volcar la base de datos para la tabla `optional_subject_retest`
--


-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `optional_subject_retest_student`
--

CREATE TABLE IF NOT EXISTS `optional_subject_retest_student` (
  `serial_std` int(11) NOT NULL,
  `serial_osr` int(11) NOT NULL,
  `grade_org` decimal(5,2) DEFAULT NULL,
  `status_org` enum('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  PRIMARY KEY (`serial_std`,`serial_osr`),
  KEY `fk_relationship_199` (`serial_osr`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Volcar la base de datos para la tabla `optional_subject_retest_student`
--


-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `opt_activity`
--

CREATE TABLE IF NOT EXISTS `opt_activity` (
  `serial_opa` int(11) NOT NULL AUTO_INCREMENT,
  `name_opa` varchar(256) NOT NULL,
  `description_opa` text NOT NULL,
  `send_date_opa` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `due_date_opa` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `status_opa` enum('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  PRIMARY KEY (`serial_opa`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

--
-- Volcar la base de datos para la tabla `opt_activity`
--


--
-- (Evento) desencadenante `opt_activity`
--
DROP TRIGGER IF EXISTS `opt_activity_trg`;
DELIMITER //
CREATE TRIGGER `opt_activity_trg` BEFORE UPDATE ON `opt_activity`
 FOR EACH ROW BEGIN 
	IF NEW.status_opa = 'INACTIVE' THEN
		/*change opt_activity_applied_criterias to INACTIVE if opt_activity is INACTIVE*/
		UPDATE opt_activity_applied_criterias oaa
		SET oaa.status_oaa = 'INACTIVE'
		WHERE oaa.serial_opa = OLD.serial_opa;
		/*change opt_activity_attachment to INACTIVE if opt_activity is INACTIVE*/
		UPDATE opt_activity_attachment oat
		SET oat.status_oat = 'INACTIVE'
		WHERE oat.serial_opa = OLD.serial_opa;
	ELSEIF NEW.status_opa = 'ACTIVE' THEN
		/*change opt_activity_applied_criterias to ACTIVE if opt_activity is ACTIVE*/
		UPDATE opt_activity_applied_criterias oaa
		SET oaa.status_oaa = 'ACTIVE'
		WHERE oaa.serial_opa = OLD.serial_opa;
		/*change opt_activity_attachment to ACTIVE if opt_activity is ACTIVE*/
		UPDATE opt_activity_attachment oat
		SET oat.status_oat = 'ACTIVE'
		WHERE oat.serial_opa = OLD.serial_opa
		AND oat.status_oat = 'INACTIVE';
	END IF;
END
//
DELIMITER ;

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `opt_activity_applied_criterias`
--

CREATE TABLE IF NOT EXISTS `opt_activity_applied_criterias` (
  `serial_opa` int(11) NOT NULL,
  `serial_gct` int(11) NOT NULL,
  `serial_osb` int(11) NOT NULL,
  `serial_att` int(11) NOT NULL,
  `serial_prd` int(11) NOT NULL,
  `weight_oaa` decimal(5,2) DEFAULT NULL,
  `status_oaa` enum('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  PRIMARY KEY (`serial_opa`,`serial_gct`,`serial_osb`,`serial_att`,`serial_prd`),
  KEY `fk_relationship_117` (`serial_gct`,`serial_osb`,`serial_att`,`serial_prd`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Volcar la base de datos para la tabla `opt_activity_applied_criterias`
--


--
-- (Evento) desencadenante `opt_activity_applied_criterias`
--
DROP TRIGGER IF EXISTS `opt_activity_applied_criterias_trg`;
DELIMITER //
CREATE TRIGGER `opt_activity_applied_criterias_trg` BEFORE UPDATE ON `opt_activity_applied_criterias`
 FOR EACH ROW BEGIN
	/*change opt_student_criteria_grade to INACTIVE if opt_activity_applied_criterias is INACTIVE
	also set de grade to NULL*/
	IF NEW.status_oaa = 'INACTIVE' THEN
		UPDATE opt_student_criteria_grade osg
		SET osg.status_osg = 'INACTIVE',
		    osg.grade_osg = NULL
		WHERE osg.serial_att = OLD.serial_att
		AND osg.serial_prd = OLD.serial_prd
		AND osg.serial_osb = OLD.serial_osb
		AND osg.serial_gct = OLD.serial_gct
		AND osg.serial_opa = OLD.serial_opa;
	ELSEIF NEW.status_oaa = 'ACTIVE' THEN
	/*change opt_student_criteria_grade to ACTIVE if opt_activity_applied_criterias is ACTIVE*/
		UPDATE opt_student_criteria_grade osg
		SET osg.status_osg = 'ACTIVE'
		WHERE osg.serial_att = OLD.serial_att
		AND osg.serial_prd = OLD.serial_prd
		AND osg.serial_osb = OLD.serial_osb
		AND osg.serial_gct = OLD.serial_gct
		AND osg.serial_opa = OLD.serial_opa;
	/*change opt_student_criteria_grade set the other criterias,serial_gct != OLD.serial_gct,as incomplete*/
		UPDATE opt_student_criteria_grade osg
		SET osg.is_complete_osg = 'NO'
		WHERE osg.serial_att = OLD.serial_att
		AND osg.serial_prd = OLD.serial_prd
		AND osg.serial_osb = OLD.serial_osb
		AND osg.serial_gct != OLD.serial_gct
		AND osg.serial_opa = OLD.serial_opa;
	END IF;
END
//
DELIMITER ;

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `opt_activity_attachment`
--

CREATE TABLE IF NOT EXISTS `opt_activity_attachment` (
  `serial_oat` int(11) NOT NULL AUTO_INCREMENT,
  `serial_opa` int(11) DEFAULT NULL,
  `name_oat` varchar(256) NOT NULL,
  `filename_oat` varchar(256) NOT NULL,
  `status_oat` enum('ACTIVE','INACTIVE','DELETED') NOT NULL DEFAULT 'ACTIVE',
  PRIMARY KEY (`serial_oat`),
  KEY `fk_relationship_119` (`serial_opa`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

--
-- Volcar la base de datos para la tabla `opt_activity_attachment`
--


-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `opt_grade_change_log`
--

CREATE TABLE IF NOT EXISTS `opt_grade_change_log` (
  `serial_usr` int(11) DEFAULT NULL,
  `serial_chr` int(11) DEFAULT NULL,
  `serial_opa` int(11) DEFAULT NULL,
  `serial_gct` int(11) DEFAULT NULL,
  `serial_osb` int(11) DEFAULT NULL,
  `serial_att` int(11) DEFAULT NULL,
  `serial_prd` int(11) DEFAULT NULL,
  `serial_std` int(11) DEFAULT NULL,
  `opt_change_date_ogc` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `opt_last_grade_ogc` decimal(5,2) DEFAULT NULL,
  `opt_comment_ogc` text,
  KEY `fk_relationship_125` (`serial_opa`,`serial_gct`,`serial_osb`,`serial_att`,`serial_prd`,`serial_std`),
  KEY `fk_relationship_126` (`serial_usr`),
  KEY `fk_relationship_149` (`serial_chr`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Volcar la base de datos para la tabla `opt_grade_change_log`
--


-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `opt_student_activity_attachment`
--

CREATE TABLE IF NOT EXISTS `opt_student_activity_attachment` (
  `serial_oth` int(11) NOT NULL AUTO_INCREMENT,
  `serial_opa` int(11) DEFAULT NULL,
  `serial_std` int(11) DEFAULT NULL,
  `name_oth` varchar(256) NOT NULL,
  `filename_oth` varchar(256) NOT NULL,
  `status_oth` enum('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  PRIMARY KEY (`serial_oth`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

--
-- Volcar la base de datos para la tabla `opt_student_activity_attachment`
--


-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `opt_student_criteria_grade`
--

CREATE TABLE IF NOT EXISTS `opt_student_criteria_grade` (
  `serial_opa` int(11) NOT NULL,
  `serial_gct` int(11) NOT NULL,
  `serial_osb` int(11) NOT NULL,
  `serial_att` int(11) NOT NULL,
  `serial_prd` int(11) NOT NULL,
  `serial_std` int(11) NOT NULL,
  `grade_osg` decimal(5,2) DEFAULT NULL,
  `is_complete_osg` enum('YES','NO') NOT NULL DEFAULT 'NO',
  `activity_grade_part_osg` decimal(6,2) DEFAULT NULL,
  `status_osg` enum('ACTIVE','INACTIVE','NOT_APPLY') NOT NULL DEFAULT 'ACTIVE',
  PRIMARY KEY (`serial_opa`,`serial_gct`,`serial_osb`,`serial_att`,`serial_prd`,`serial_std`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Volcar la base de datos para la tabla `opt_student_criteria_grade`
--


-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `opt_subject_period_activity_type`
--

CREATE TABLE IF NOT EXISTS `opt_subject_period_activity_type` (
  `serial_att` int(11) NOT NULL,
  `serial_prd` int(11) NOT NULL,
  `serial_osb` int(11) NOT NULL,
  `weight_osa` decimal(5,2) NOT NULL,
  `due_min_days_osa` int(11) NOT NULL,
  `due_max_grade_osa` int(11) DEFAULT NULL,
  `status_osa` enum('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  PRIMARY KEY (`serial_att`,`serial_prd`,`serial_osb`),
  KEY `fk_relationship_112` (`serial_prd`,`serial_osb`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Volcar la base de datos para la tabla `opt_subject_period_activity_type`
--


-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `opt_subject_retest_change_log`
--

CREATE TABLE IF NOT EXISTS `opt_subject_retest_change_log` (
  `serial_usr` int(11) DEFAULT NULL,
  `serial_chr` int(11) DEFAULT NULL,
  `serial_std` int(11) DEFAULT NULL,
  `serial_osr` int(11) DEFAULT NULL,
  `change_date_orl` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `last_grade_orl` decimal(5,2) NOT NULL,
  `comment_orl` text,
  KEY `fk_relationship_215` (`serial_std`,`serial_osr`),
  KEY `fk_relationship_216` (`serial_usr`),
  KEY `fk_relationship_217` (`serial_chr`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Volcar la base de datos para la tabla `opt_subject_retest_change_log`
--


-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `opt_subject_year_activity_criteria_period`
--

CREATE TABLE IF NOT EXISTS `opt_subject_year_activity_criteria_period` (
  `serial_gct` int(11) NOT NULL,
  `serial_osb` int(11) NOT NULL,
  `serial_att` int(11) NOT NULL,
  `serial_prd` int(11) NOT NULL,
  `status_oac` enum('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  PRIMARY KEY (`serial_gct`,`serial_osb`,`serial_att`,`serial_prd`),
  KEY `fk_relationship_114` (`serial_att`,`serial_prd`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Volcar la base de datos para la tabla `opt_subject_year_activity_criteria_period`
--


-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `opt_subject_year_grade_criteria`
--

CREATE TABLE IF NOT EXISTS `opt_subject_year_grade_criteria` (
  `serial_gct` int(11) NOT NULL,
  `serial_osb` int(11) NOT NULL,
  `status_oyc` enum('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  PRIMARY KEY (`serial_gct`,`serial_osb`),
  KEY `fk_relationship_110` (`serial_osb`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Volcar la base de datos para la tabla `opt_subject_year_grade_criteria`
--


-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `payment_concept`
--

CREATE TABLE IF NOT EXISTS `payment_concept` (
  `serial_pct` int(11) NOT NULL AUTO_INCREMENT,
  `name_pct` varchar(200) NOT NULL,
  `status_pct` enum('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  PRIMARY KEY (`serial_pct`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

--
-- Volcar la base de datos para la tabla `payment_concept`
--


-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `payment_detail`
--

CREATE TABLE IF NOT EXISTS `payment_detail` (
  `serial_pym` int(11) DEFAULT NULL,
  `serial_ccd` int(11) DEFAULT NULL,
  `serial_fin` int(11) DEFAULT NULL,
  `serial_phy` int(11) DEFAULT NULL,
  `description_pdt` varchar(128) NOT NULL,
  `document_number_pdt` varchar(128) DEFAULT NULL,
  `amount_pdt` decimal(6,2) NOT NULL,
  `kinship_pdt` varchar(80) DEFAULT NULL,
  `owner_pdt` varchar(300) DEFAULT NULL,
  `status_pdt` enum('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  KEY `fk_relationship_172` (`serial_phy`),
  KEY `fk_relationship_173` (`serial_pym`),
  KEY `fk_relationship_191` (`serial_ccd`),
  KEY `fk_relationship_192` (`serial_fin`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Volcar la base de datos para la tabla `payment_detail`
--


-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `payment_method`
--

CREATE TABLE IF NOT EXISTS `payment_method` (
  `serial_pym` int(11) NOT NULL AUTO_INCREMENT,
  `name_pym` varchar(128) NOT NULL,
  `requires_owner_info_pym` enum('YES','NO') NOT NULL DEFAULT 'NO',
  `requires_financial_pym` enum('YES','NO') NOT NULL DEFAULT 'NO',
  `requires_credit_card_pym` enum('YES','NO') NOT NULL DEFAULT 'NO',
  `status_pym` enum('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  PRIMARY KEY (`serial_pym`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

--
-- Volcar la base de datos para la tabla `payment_method`
--

INSERT INTO `payment_method` (`serial_pym`, `name_pym`, `requires_owner_info_pym`, `requires_financial_pym`, `requires_credit_card_pym`, `status_pym`) VALUES
(1, 'Diferido Corriente', 'NO', 'NO', 'NO', 'ACTIVE');

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `period`
--

CREATE TABLE IF NOT EXISTS `period` (
  `serial_prd` int(11) NOT NULL AUTO_INCREMENT,
  `serial_pgy` int(11) DEFAULT NULL,
  `name_prd` varchar(255) NOT NULL,
  `start_date_prd` date NOT NULL,
  `end_date_prd` date NOT NULL,
  `max_grade_date_prd` date NOT NULL,
  `status_prd` enum('ACTIVE','INACTIVE','BLOCKED','ENDED') NOT NULL DEFAULT 'ACTIVE',
  `min_grade_prd` decimal(5,2) NOT NULL,
  `max_grade_prd` decimal(5,2) NOT NULL,
  `warning_grade_prd` decimal(5,2) NOT NULL,
  `weight_prd` decimal(5,2) NOT NULL,
  PRIMARY KEY (`serial_prd`),
  KEY `fk_periods_by_group` (`serial_pgy`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

--
-- Volcar la base de datos para la tabla `period`
--

INSERT INTO `period` (`serial_prd`, `serial_pgy`, `name_prd`, `start_date_prd`, `end_date_prd`, `max_grade_date_prd`, `status_prd`, `min_grade_prd`, `max_grade_prd`, `warning_grade_prd`, `weight_prd`) VALUES
(1, 1, 'Trimestre 1', '2011-02-08', '2011-05-02', '2011-05-01', 'ACTIVE', '2.00', '20.00', '12.00', '33.00'),
(2, 1, 'Trimestre 2', '2011-05-03', '2011-06-08', '2011-06-06', 'ACTIVE', '2.00', '20.00', '12.00', '33.00'),
(3, 1, 'Trimestre 3', '2011-06-09', '2011-11-08', '2011-11-05', 'ACTIVE', '2.00', '20.00', '12.00', '34.00');

--
-- (Evento) desencadenante `period`
--
DROP TRIGGER IF EXISTS `period_subject_course_period_trg`;
DELIMITER //
CREATE TRIGGER `period_subject_course_period_trg` BEFORE UPDATE ON `period`
 FOR EACH ROW BEGIN
	/*change subject_course_period to INACTIVE if period is INACTIVE*/
	IF NEW.status_prd LIKE '%INACTIVE%' THEN
		UPDATE subject_course_period scp
		SET scp.status_scp = 'INACTIVE',
			scp.weight_scp = 0
		WHERE scp.serial_prd = OLD.serial_prd;
	END IF;
END
//
DELIMITER ;

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `period_group_year`
--

CREATE TABLE IF NOT EXISTS `period_group_year` (
  `serial_pgy` int(11) NOT NULL AUTO_INCREMENT,
  `serial_scy` int(11) DEFAULT NULL,
  `serial_cpd` int(11) DEFAULT NULL,
  `name_pgy` varchar(255) DEFAULT NULL,
  `status_pgy` enum('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  PRIMARY KEY (`serial_pgy`),
  KEY `fk_periods_in_year` (`serial_scy`),
  KEY `fk_period_for_year` (`serial_cpd`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

--
-- Volcar la base de datos para la tabla `period_group_year`
--

INSERT INTO `period_group_year` (`serial_pgy`, `serial_scy`, `serial_cpd`, `name_pgy`, `status_pgy`) VALUES
(1, 1, 1, 'Año 2010', 'ACTIVE');

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `pre_registration`
--

CREATE TABLE IF NOT EXISTS `pre_registration` (
  `serial_prt` int(11) NOT NULL AUTO_INCREMENT,
  `serial_ste` int(11) DEFAULT NULL,
  `serial_nnt` int(11) DEFAULT NULL,
  `serial_usr` int(11) DEFAULT NULL,
  `serial_dgr` int(11) DEFAULT NULL,
  `id_prt` varchar(10) DEFAULT NULL,
  `serial_spt` int(11) DEFAULT NULL,
  `serial_lvl` int(11) DEFAULT NULL,
  `serial_scy` int(11) DEFAULT NULL,
  `serial_set` int(11) DEFAULT NULL,
  `first_name_prt` varchar(512) NOT NULL,
  `last_name_prt` varchar(512) NOT NULL,
  `birthdate_prt` date NOT NULL,
  `gender_prt` enum('MALE','FEMALE') NOT NULL,
  `address_prt` text NOT NULL,
  `phone_prt` varchar(30) NOT NULL,
  `first_name_father_prt` varchar(100) DEFAULT NULL,
  `last_name_father_prt` varchar(100) DEFAULT NULL,
  `first_name_mother_prt` varchar(100) DEFAULT NULL,
  `last_name_mother_prt` varchar(100) DEFAULT NULL,
  `city_birth_prt` varchar(100) NOT NULL,
  `blood_type_prt` varchar(10) NOT NULL,
  `allergies_prt` text,
  `lives_with_prt` enum('PARENT','SIBLING','GRANDPARENT','AUNT_UNCLE','OTHER') NOT NULL,
  `photo_prt` varchar(1024) DEFAULT NULL,
  `email_prt` varchar(256) DEFAULT NULL,
  `rep_first_name_prt` varchar(100) NOT NULL,
  `rep_last_name_prt` varchar(100) NOT NULL,
  `rep_relationship_prt` enum('PARENT','SIBLING','GRANDPARENT','AUNT_UNCLE','OTHER') NOT NULL,
  `rep_birthdate_prt` date NOT NULL,
  `rep_city_birth_prt` varchar(100) NOT NULL,
  `rep_marital_status_prt` enum('SINGLE','MARRIED','DIVORCED','WIDOWED','UNMARRIED PARTNERS') NOT NULL,
  `rep_occupation_prt` varchar(100) NOT NULL,
  `rep_address_prt` text NOT NULL,
  `rep_phone_prt` varchar(13) NOT NULL,
  `rep_mobile_prt` varchar(13) DEFAULT NULL,
  `rep_company_name_prt` varchar(80) DEFAULT NULL,
  `rep_company_activity_prt` text,
  `rep_company_phone_prt` varchar(13) DEFAULT NULL,
  `rep_photo_prt` varchar(1024) NOT NULL,
  `rep_gender_prt` enum('MALE','FEMALE') NOT NULL,
  `rep_id_prt` varchar(13) NOT NULL,
  `rep_email_prt` varchar(256) DEFAULT NULL,
  `submitted_docs_prt` enum('YES','NO') NOT NULL DEFAULT 'NO',
  `pre_approved_prt` enum('YES','NO','PENDING') NOT NULL DEFAULT 'PENDING',
  `pre_approved_comment_prt` text,
  `approved_prt` enum('YES','NO','PENDING') NOT NULL DEFAULT 'PENDING',
  `approved_comment_prt` text,
  PRIMARY KEY (`serial_prt`),
  KEY `fk_registered_nationality` (`serial_nnt`),
  KEY `fk_relationship_241` (`serial_ste`),
  KEY `fk_relationship_64` (`serial_spt`,`serial_lvl`,`serial_scy`,`serial_set`),
  KEY `fk_relationship_67` (`serial_dgr`),
  KEY `fk_user_register` (`serial_usr`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

--
-- Volcar la base de datos para la tabla `pre_registration`
--

INSERT INTO `pre_registration` (`serial_prt`, `serial_ste`, `serial_nnt`, `serial_usr`, `serial_dgr`, `id_prt`, `serial_spt`, `serial_lvl`, `serial_scy`, `serial_set`, `first_name_prt`, `last_name_prt`, `birthdate_prt`, `gender_prt`, `address_prt`, `phone_prt`, `first_name_father_prt`, `last_name_father_prt`, `first_name_mother_prt`, `last_name_mother_prt`, `city_birth_prt`, `blood_type_prt`, `allergies_prt`, `lives_with_prt`, `photo_prt`, `email_prt`, `rep_first_name_prt`, `rep_last_name_prt`, `rep_relationship_prt`, `rep_birthdate_prt`, `rep_city_birth_prt`, `rep_marital_status_prt`, `rep_occupation_prt`, `rep_address_prt`, `rep_phone_prt`, `rep_mobile_prt`, `rep_company_name_prt`, `rep_company_activity_prt`, `rep_company_phone_prt`, `rep_photo_prt`, `rep_gender_prt`, `rep_id_prt`, `rep_email_prt`, `submitted_docs_prt`, `pre_approved_prt`, `pre_approved_comment_prt`, `approved_prt`, `approved_comment_prt`) VALUES
(1, 19, 17, 2, 2, '', 1, 1, 1, 1, 'Paul', 'Ambrossi', '1991-02-07', 'MALE', 'adasdas d das d', '3243233', 'Nic', 'Fury', 'Noc', 'Sadness', 'Quito', 'O+', 'a ti', 'PARENT', '', 'pruebas@crifa.com', 'Nelson', 'Mandela', 'PARENT', '1986-02-12', 'Quito', 'MARRIED', 'Profe', 'casa', '222222222', '', '', '', '', '', 'MALE', '1715492656', '', 'NO', 'PENDING', '', 'PENDING', ''),
(2, 131, 10, 2, 8, '0103438180', 2, 3, 1, 2, 'Miguelito', 'Ponce', '1988-11-18', 'MALE', 'asd as das das dsa d', '2232323', 'Nelson', 'Mandela', 'Noc', 'Mandela', 'Quito', 'O+', 'nada', 'SIBLING', '', '', 'Manuel', 'Chicaiza', 'AUNT_UNCLE', '1981-02-11', 'Quito', 'SINGLE', 'Profe', 'tin tin dsfsf', '43242424', '', '', '', '', '462bf9.jpg', 'MALE', '0103438180', '', 'NO', 'PENDING', '', 'PENDING', '');

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `pre_registration_period`
--

CREATE TABLE IF NOT EXISTS `pre_registration_period` (
  `serial_scy` int(11) DEFAULT NULL,
  `start_date_prp` date NOT NULL,
  `end_date_prp` date NOT NULL,
  `status_prp` enum('ACTIVE','INACTIVE','CLOSED','OPEN') NOT NULL DEFAULT 'ACTIVE',
  KEY `fk_relationship_73` (`serial_scy`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Volcar la base de datos para la tabla `pre_registration_period`
--

INSERT INTO `pre_registration_period` (`serial_scy`, `start_date_prp`, `end_date_prp`, `status_prp`) VALUES
(1, '2011-02-07', '2011-02-09', 'OPEN');

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `pre_registration_required_documment`
--

CREATE TABLE IF NOT EXISTS `pre_registration_required_documment` (
  `serial_prt` int(11) DEFAULT NULL,
  `serial_ypd` int(11) DEFAULT NULL,
  `serial_wpr` int(11) DEFAULT NULL,
  `filename_pdc` varchar(500) DEFAULT NULL,
  `status_pdc` enum('PENDING','SUBMITTED') NOT NULL DEFAULT 'PENDING',
  KEY `fk_relationship_263` (`serial_ypd`),
  KEY `fk_relationship_264` (`serial_prt`),
  KEY `fk_relationship_265` (`serial_wpr`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Volcar la base de datos para la tabla `pre_registration_required_documment`
--


-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `process`
--

CREATE TABLE IF NOT EXISTS `process` (
  `serial_prc` int(11) NOT NULL AUTO_INCREMENT,
  `prc_serial_prc` int(11) DEFAULT NULL,
  `name_prc` varchar(150) NOT NULL,
  `link_prc` varchar(1024) NOT NULL,
  `weight_prc` decimal(5,1) NOT NULL,
  `status_prc` enum('LOCKABLE','NON-LOCKABLE') NOT NULL DEFAULT 'LOCKABLE',
  PRIMARY KEY (`serial_prc`),
  KEY `fk_parent_process` (`prc_serial_prc`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=255 ;

--
-- Volcar la base de datos para la tabla `process`
--

INSERT INTO `process` (`serial_prc`, `prc_serial_prc`, `name_prc`, `link_prc`, `weight_prc`, `status_prc`) VALUES
(1, NULL, 'Parámetros', '#', '1.0', 'LOCKABLE'),
(2, 1, 'Área Académica', '#', '48.5', 'LOCKABLE'),
(3, 2, 'Nueva Área Académica', 'modules/academic_area/fNewArea', '48.6', 'LOCKABLE'),
(4, 2, 'Actualizar Área Académica', 'modules/academic_area/fSearchArea', '48.7', 'LOCKABLE'),
(5, 1, 'Autoridades', '#', '5.0', 'LOCKABLE'),
(6, 5, 'Nueva Autoridad', 'modules/authority/fNewAuthority', '6.0', 'LOCKABLE'),
(7, 5, 'Actualizar Autoridad', 'modules/authority/fSearchAuthority', '7.0', 'LOCKABLE'),
(8, 1, 'Cargos de Autoridades', '#', '4.1', 'LOCKABLE'),
(9, 8, 'Nuevo Cargo', 'modules/authority_charges/fNewCharge', '4.2', 'LOCKABLE'),
(10, 8, 'Actualizar Cargo', 'modules/authority_charges/fSearchCharge', '4.3', 'LOCKABLE'),
(11, 1, 'Perfiles', '#', '51.5', 'LOCKABLE'),
(12, 11, 'Nuevo Perfil', 'modules/profile/fNewProfile', '51.6', 'LOCKABLE'),
(13, 11, 'Actualizar Perfil', 'modules/profile/fSearchProfile', '51.7', 'LOCKABLE'),
(14, 1, 'Paises', '#', '14.0', 'LOCKABLE'),
(15, 14, 'Nuevo País', 'modules/country/fNewCountry', '15.0', 'LOCKABLE'),
(16, 14, 'Actualizar País', 'modules/country/fSearchCountry', '16.0', 'LOCKABLE'),
(17, 1, 'Provincias/Estados', '#', '17.0', 'LOCKABLE'),
(18, 17, 'Nueva Provincia/Estado', 'modules/state/fNewState', '18.0', 'LOCKABLE'),
(19, 17, 'Actualizar Provincia/Estado', 'modules/state/fSearchState', '19.0', 'LOCKABLE'),
(20, 1, 'Secciones', '#', '20.0', 'LOCKABLE'),
(21, 20, 'Nueva Sección', 'modules/section/fNewSection', '21.0', 'LOCKABLE'),
(22, 20, 'Actualizar Sección', 'modules/section/fSearchSection', '22.0', 'LOCKABLE'),
(23, 1, 'Niveles', '#', '23.0', 'LOCKABLE'),
(24, 23, 'Nuevo Nivel', 'modules/level/fNewLevel', '24.0', 'LOCKABLE'),
(25, 23, 'Actualizar Nivel', 'modules/level/fSearchLevel', '25.0', 'LOCKABLE'),
(26, 1, 'Especialidades', '#', '22.5', 'LOCKABLE'),
(27, 26, 'Nueva Especialidad', 'modules/specialty/fNewSpecialty', '22.6', 'LOCKABLE'),
(28, 26, 'Actualizar Especialidad', 'modules/specialty/fSearchSpecialty', '22.7', 'LOCKABLE'),
(29, 1, 'Paralelos', '#', '29.0', 'LOCKABLE'),
(30, 29, 'Nuevo Paralelo', 'modules/class_group/fNewClassGroup', '30.0', 'LOCKABLE'),
(31, 29, 'Actualizar Paralelo', 'modules/class_group/fSearchClassGroup', '31.0', 'LOCKABLE'),
(32, 1, 'Horas Académicas', '#', '32.0', 'LOCKABLE'),
(33, 32, 'Grupos de Horas', '#', '33.0', 'LOCKABLE'),
(34, 33, 'Nuevo Grupo Horas', 'modules/group_hour/fNewGroupHour', '34.0', 'LOCKABLE'),
(35, 33, 'Actualizar Grupo Horas', 'modules/group_hour/fSearchGroupHour', '35.0', 'LOCKABLE'),
(36, 1, 'Categoría de Período', '#', '36.0', 'LOCKABLE'),
(37, 36, 'Nueva Categoría', 'modules/cat_period/fNewCatPeriod', '37.0', 'LOCKABLE'),
(38, 36, 'Actualizar Categoría', 'modules/cat_period/fSearchCatPeriod', '38.0', 'LOCKABLE'),
(39, NULL, 'Configuración', '#', '39.0', 'LOCKABLE'),
(40, 39, 'Año Lectivo', '#', '40.0', 'LOCKABLE'),
(41, 40, 'Iniciar/Continuar', 'modules/year_setup/index', '41.0', 'LOCKABLE'),
(42, 1, 'Año Lectivo', '#', '19.5', 'LOCKABLE'),
(43, 42, 'Nuevo Año Lectivo', 'modules/school_year/fNewYear', '19.6', 'LOCKABLE'),
(44, 42, 'Actualizar Año Lectivo', 'modules/school_year/fSearchYear', '19.7', 'LOCKABLE'),
(45, 199, 'Preinscripciones', '#', '45.0', 'LOCKABLE'),
(46, 45, 'Nueva Preinscripción', 'modules/pre_registration/fNewPreRegistration', '46.0', 'LOCKABLE'),
(47, 45, 'Actualizar Preinscripción', 'modules/pre_registration/fSelectPreRegisteredToUpdate', '47.0', 'LOCKABLE'),
(48, 45, 'Procesar Preinscripciones', 'modules/pre_registration/fSelectLevel', '48.0', 'LOCKABLE'),
(49, 1, 'Materias', '#', '49.0', 'LOCKABLE'),
(50, 49, 'Nueva Materia', 'modules/cat_subject/fNewCatSubject', '50.0', 'LOCKABLE'),
(51, 49, 'Actualizar Materia', 'modules/cat_subject/fSearchCatSubject', '51.0', 'LOCKABLE'),
(52, 1, 'Usuarios', '#', '52.0', 'LOCKABLE'),
(53, 52, 'Profesores', '#', '53.0', 'LOCKABLE'),
(54, 53, 'Nuevo Profesor', 'modules/employee/teacher/fNewTeacher', '54.0', 'LOCKABLE'),
(55, 53, 'Actualizar Profesor', 'modules/employee/teacher/fSearchTeacher', '55.0', 'LOCKABLE'),
(56, 52, 'Empleados', '#', '56.0', 'LOCKABLE'),
(57, 39, 'Materias', '#', '57.0', 'LOCKABLE'),
(58, 57, 'Iniciar/Continuar', 'modules/year_subject_setup/index', '58.0', 'LOCKABLE'),
(59, 39, 'Profesores', '#', '59.0', 'LOCKABLE'),
(60, 59, 'Horas disponibles por año', 'modules/teacher_hours_year/fSelectSchoolYear', '60.0', 'LOCKABLE'),
(61, 39, 'Estudiantes', '#', '61.0', 'LOCKABLE'),
(62, 61, 'Asignar Curso', 'modules/student/fSelectSchoolYear', '62.0', 'LOCKABLE'),
(63, 53, 'Asignar Área Académica', 'modules/employee/teacher/fAssignTeacherToAcademicArea', '63.0', 'LOCKABLE'),
(64, 56, 'Nuevo Empleado', 'modules/employee/general/fNewEmployee', '64.0', 'LOCKABLE'),
(65, 56, 'Actualizar Empleado', 'modules/employee/general/fSearchEmployee', '65.0', 'LOCKABLE'),
(66, 1, 'Aulas', '#', '51.1', 'LOCKABLE'),
(67, 66, 'Nueva Aula', 'modules/classroom/fNewClassroom', '67.0', 'LOCKABLE'),
(68, 66, 'Actualizar Aula', 'modules/classroom/fSearchClassroom', '68.0', 'LOCKABLE'),
(69, 52, 'Asignación de Perfiles', 'modules/user_profile/fSearchUser', '69.0', 'LOCKABLE'),
(70, 1, 'Procesos', '#', '70.0', 'LOCKABLE'),
(71, 70, 'Bloquear Procesos', 'modules/process/fSearchProfileProcess', '71.0', 'LOCKABLE'),
(72, 2, 'Asignar/Reasignar Director de Area', 'modules/academic_area/fChangeAcademicAreaDirector', '48.8', 'LOCKABLE'),
(73, 52, 'Representantes', '#', '56.1', 'LOCKABLE'),
(74, 73, 'Nuevo Representante', 'modules/guardian/fNewGuardian', '74.0', 'LOCKABLE'),
(75, 73, 'Actualizar Representante', 'modules/guardian/fSearchGuardian', '75.0', 'LOCKABLE'),
(76, 39, 'Preinscripciones', '#', '76.0', 'LOCKABLE'),
(77, 76, 'Periodos de Preinscripción', 'modules/school_year/fNewPreRegistrationPeriod', '77.0', 'LOCKABLE'),
(78, 1, 'Transportes', '#', '51.2', 'LOCKABLE'),
(79, 78, 'Conductores', '#', '79.0', 'LOCKABLE'),
(80, 79, 'Nuevo Conductor', 'modules/bus_driver/fNewBusDriver', '80.0', 'LOCKABLE'),
(81, 79, 'Actualizar Conductor', 'modules/bus_driver/fSearchBusDriver', '81.0', 'LOCKABLE'),
(82, 78, 'Buses', '#', '82.0', 'LOCKABLE'),
(83, 82, 'Nuevo Bus', 'modules/bus/fNewBus', '83.0', 'LOCKABLE'),
(84, 82, 'Actualizar Bus', 'modules/bus/fSearchBus', '84.0', 'LOCKABLE'),
(85, 78, 'Rutas', '#', '85.0', 'LOCKABLE'),
(86, 85, 'Nueva Ruta', 'modules/route/fNewRoute', '86.0', 'LOCKABLE'),
(87, 85, 'Actualizar Ruta', 'modules/route/fSearchRoute', '87.0', 'LOCKABLE'),
(88, 78, 'Turnos', '#', '88.0', 'LOCKABLE'),
(89, 88, 'Nuevo Turno', 'modules/shift/fNewShift', '89.0', 'LOCKABLE'),
(90, 88, 'Actualizar Turno', 'modules/shift/fSearchShift', '90.0', 'LOCKABLE'),
(91, 1, 'Tipos de Actividad', '#', '49.1', 'LOCKABLE'),
(92, 91, 'Nuevo Tipo', 'modules/activity_type/fNewActivityType', '92.0', 'LOCKABLE'),
(93, 91, 'Actualizar Tipo', 'modules/activity_type/fSearchActivityType', '93.0', 'LOCKABLE'),
(94, 1, 'Criterios de Evaluación', '#', '49.2', 'LOCKABLE'),
(95, 94, 'Nuevo Criterio', 'modules/grade_criteria/fNewGradeCriteria', '95.0', 'LOCKABLE'),
(96, 94, 'Actualizar Criterio', 'modules/grade_criteria/fSearchGradeCriteria', '96.0', 'LOCKABLE'),
(97, 39, 'Transportes', '#', '97.0', 'LOCKABLE'),
(98, 97, 'Iniciar/Continuar', 'modules/transportation_setup/fNewTransportationSetup', '98.0', 'LOCKABLE'),
(99, 52, 'Desactivar Usuario', 'modules/user/fSearchUser', '99.0', 'LOCKABLE'),
(100, NULL, 'Estudiantes', '#', '100.0', 'LOCKABLE'),
(101, 100, 'Documentación de Aprobados', 'modules/student_documents/fSelectStudent', '101.0', 'LOCKABLE'),
(102, 1, 'Artículos', '#', '51.3', 'LOCKABLE'),
(103, 102, 'Nuevo Artículo', 'modules/article/fNewArticle', '103.0', 'LOCKABLE'),
(104, 102, 'Actualizar Artículo', 'modules/article/fSearchArticle', '104.0', 'LOCKABLE'),
(105, 97, 'Asignar Usuarios', 'modules/transportation_setup/fSelectSchoolYear', '105.0', 'LOCKABLE'),
(106, 39, 'Criterios y Actividades', '#', '57.2', 'LOCKABLE'),
(107, 106, 'Materias Obligatorias', '#', '107.0', 'LOCKABLE'),
(108, 107, 'Iniciar/Continuar', 'modules/year_activity_criteria_setup/', '108.0', 'LOCKABLE'),
(109, 106, 'Materias Optativas', '#', '109.0', 'LOCKABLE'),
(110, 109, 'Iniciar/Continuar', 'modules/year_opt_activity_criteria_setup/', '110.0', 'LOCKABLE'),
(111, 1, 'Concepto de Pago', '#', '51.3', 'LOCKABLE'),
(112, 111, 'Nuevo Concepto de Pago', 'modules/payment_concept/fNewPaymentConcept', '1.0', 'LOCKABLE'),
(113, 111, 'Actualizar Concepto de Pago', 'modules/payment_concept/fSearchPaymentConcept', '2.0', 'LOCKABLE'),
(114, 1, 'Impuestos', '#', '51.4', 'LOCKABLE'),
(115, 114, 'Nuevo Impuesto', 'modules/tax/fNewTax', '1.0', 'LOCKABLE'),
(116, 114, 'Actualizar Impuesto', 'modules/tax/fSearchTax', '2.0', 'LOCKABLE'),
(117, 39, 'Rubros', '#', '114.0', 'LOCKABLE'),
(118, 117, 'Iniciar/Continuar', 'modules/year_level_specialty_payment_concept/index', '1.0', 'LOCKABLE'),
(119, 134, 'Actividades', '#', '116.0', 'LOCKABLE'),
(120, 119, 'Nueva Actividad (obsoleta)', '#', '1.0', 'LOCKABLE'),
(121, 119, 'Actualizar Actividad (obsoleta)', '#', '2.0', 'LOCKABLE'),
(122, 119, 'Calificar Actividad', '#', '3.0', 'LOCKABLE'),
(123, 119, 'Recalificar Actividad', '#', '4.0', 'LOCKABLE'),
(124, 120, 'Materia Obligatoria', 'modules/activity/fNewActivity', '1.0', 'LOCKABLE'),
(125, 120, 'Materia Optativa ', 'modules/opt_activity/fNewOptionalActivity', '1.0', 'LOCKABLE'),
(126, 122, 'Materia Obligatoria', 'modules/student_criteria_grade/fGradeActivityByCourse', '1.0', 'LOCKABLE'),
(127, 122, 'Materia Optativa', 'modules/opt_student_criteria_grade/fOptionalSubjectGradeActivityByCourse', '2.0', 'LOCKABLE'),
(128, 121, 'Materia Obligatoria', 'modules/activity/fUpdateActivity', '1.0', 'LOCKABLE'),
(129, 121, 'Materia Optativa', 'modules/opt_activity/fUpdateOptionalActivity', '2.0', 'LOCKABLE'),
(130, 119, 'Ver Actividades', '#', '3.0', 'LOCKABLE'),
(131, 130, 'Materias Obligatorias', 'modules/student_subject_activity/fSelectSchoolYear ', '1.0', 'LOCKABLE'),
(132, 130, 'Materias Optativas', 'modules/student_opt_subject_activity/fSelectSchoolYear', '2.0', 'LOCKABLE'),
(133, 61, 'Asignar Materia Optativa', 'modules/student_optional_subject/fSelectSchoolYear', '62.5', 'LOCKABLE'),
(134, 199, 'Materias', '#', '134.0', 'LOCKABLE'),
(135, 134, 'Plan de Estudios', 'modules/syllabus_files/fAssignSyllabusFiles', '1.0', 'LOCKABLE'),
(136, 134, 'Plan de Estudios Profesores', 'modules/syllabus_files_change_log/fChangeSyllabusFiles', '1.0', 'LOCKABLE'),
(137, 123, 'Materia Obligatoria', 'modules/grade_change_log/fGradeChangeLog', '1.0', 'LOCKABLE'),
(138, 123, 'Materia Optativa', 'modules/opt_grade_change_log/fOptGradeChangeLog', '1.1', 'LOCKABLE'),
(139, 134, 'Ver Planes de Estudio', 'modules/download_subject_syllabus/fDownloadSubjectSyllabus', '1.0', 'LOCKABLE'),
(140, 119, 'Calendario Actividades Alumno', 'modules/activity/fStudentCalendarActivity', '0.5', 'LOCKABLE'),
(141, 119, 'Calendario Representante', 'modules/activity/fGuardianCalendarActivity', '0.5', 'LOCKABLE'),
(142, 119, 'Calendario Actividades Profesor', '#', '0.5', 'LOCKABLE'),
(143, 199, 'Disciplina', '#', '143.0', 'LOCKABLE'),
(144, 143, 'Disciplina General', 'modules/discipline/fDisciplineGeneralGrade', '1.0', 'LOCKABLE'),
(145, 143, 'Disciplina x Materia', 'modules/student_criteria_grade/fGradeDisciplineByCourse', '1.0', 'LOCKABLE'),
(146, 143, 'Recalificar Disciplina x Materia', 'modules/student_criteria_grade/fGradeDisciplineByCourseLog', '1.0', 'LOCKABLE'),
(147, NULL, 'Mensajería', '#', '147.0', 'LOCKABLE'),
(148, 147, 'Bandeja', 'modules/message/fInbox', '1.0', 'LOCKABLE'),
(149, NULL, 'Reportes', '#', '149.0', 'LOCKABLE'),
(150, 149, 'Profesores', '#', '1.0', 'LOCKABLE'),
(151, 149, 'Estudiantes', '#', '2.0', 'LOCKABLE'),
(152, 150, 'Area Académica', 'modules/reports/employee/teacher/fViewTeachersByArea', '1.0', 'LOCKABLE'),
(153, 151, 'Curso u Optativa', 'modules/reports/student/fViewStudentsByCourseOrOptSubject', '1.0', 'LOCKABLE'),
(154, 1, 'Mensajería', '#', '69.0', 'LOCKABLE'),
(155, 154, 'Configurar Receptor', 'modules/message/fMessagesBetweenProfiles', '1.0', 'LOCKABLE'),
(156, NULL, 'Financiero', '#', '148.0', 'LOCKABLE'),
(157, 156, 'Realizar Cobro', 'modules/student_year_debts/fSearchStudentForPayingDebts', '2.0', 'LOCKABLE'),
(158, 149, 'Usuarios', '#', '3.0', 'LOCKABLE'),
(159, 158, 'Ingreso al Sistema', 'modules/reports/user/fUserAccessLog', '1.0', 'LOCKABLE'),
(160, 147, 'Comunicaciones', '#', '1.0', 'LOCKABLE'),
(161, 160, 'Enviar Comunicación', 'modules/message/fSendCommunication', '1.0', 'LOCKABLE'),
(162, 1, 'Razones de Cambio', '#', '49.3', 'LOCKABLE'),
(163, 162, 'Nueva Razón', 'modules/change_reason/fNewChangeReason', '1.0', 'LOCKABLE'),
(164, 162, 'Actualizar Razón', 'modules/change_reason/fSearchChangeReason', '2.0', 'LOCKABLE'),
(165, 39, 'Dirigentes de Curso', '#', '59.1', 'LOCKABLE'),
(166, 165, 'Iniciar/Continuar', 'modules/course_director/fNewCourseDirector', '1.0', 'LOCKABLE'),
(167, 39, 'Definición de Notas', '#', '59.1', 'LOCKABLE'),
(168, 167, 'Equivalencia de Cualitativas', 'modules/qualitative_equivalence/fNewQualitativeEquivalence', '1.0', 'LOCKABLE'),
(169, 167, 'Mínimo de Supletorios', 'modules/year_resit_exam_ranges/fNewYearResitExamRanges', '2.0', 'LOCKABLE'),
(170, 39, 'Asignación de aulas', '#', '59.2', 'LOCKABLE'),
(171, 170, 'Iniciar/Continuar', 'modules/year_subject_classroom/fAssignClassroomsToSubjects', '1.0', 'LOCKABLE'),
(172, 52, 'Estudiantes', '#', '56.2', 'LOCKABLE'),
(173, 172, 'Actualizar Datos', 'modules/student/fUpdateStudentInfo', '1.0', 'LOCKABLE'),
(174, 172, 'Actualizar Representantes', 'modules/student_guardian/fAssignGuardiansToStudent', '1.1', 'LOCKABLE'),
(175, 1, 'Mantenimiento', '#', '175.0', 'LOCKABLE'),
(176, 175, 'Categoría de Artículos', '#', '1.0', 'LOCKABLE'),
(177, 176, 'Nueva Categoría', 'modules/item_category/fNewItemCategory', '1.0', 'LOCKABLE'),
(178, 176, 'Actualizar Categoría', 'modules/item_category/fSearchItemCategory', '1.1', 'LOCKABLE'),
(179, 175, 'Proveedores', '#', '1.1', 'LOCKABLE'),
(180, 179, 'Nuevo Proveedor', 'modules/provider/fNewProvider', '1.0', 'LOCKABLE'),
(181, 179, 'Actualizar Proveedor', 'modules/provider/fSearchProvider', '1.1', 'LOCKABLE'),
(182, 39, 'Materias de Grado', '#', '182.0', 'LOCKABLE'),
(183, 182, 'Definir Materias Disponibles', 'modules/year_grade_subject/fSelectSchoolYear', '1.0', 'LOCKABLE'),
(184, 182, 'Selección Materia x Estudiante', 'modules/grade_subject_score/fSelectSchoolYear', '1.1', 'LOCKABLE'),
(185, 39, 'Calificaciones Especiales', '#', '185.0', 'LOCKABLE'),
(186, 185, 'Iniciar/Continuar', 'modules/year_special_grade_setup/index', '1.0', 'LOCKABLE'),
(187, 175, 'Destinos de Uso', '#', '3.0', 'LOCKABLE'),
(188, 187, 'Nuevo Destino de Uso', 'modules/use_destination/fNewUseDestination', '1.0', 'LOCKABLE'),
(189, 187, 'Actualizar Destino de Uso', 'modules/use_destination/fSearchUseDestination', '1.1', 'LOCKABLE'),
(190, 1, 'Instituciones Financieras', '#', '51.3', 'LOCKABLE'),
(191, 190, 'Nuevo Institución', 'modules/financial_institution/fNewFinancialInstitution', '1.0', 'LOCKABLE'),
(192, 190, 'Actualizar Institución', 'modules/financial_institution/fSearchFinancialInstitution', '1.1', 'LOCKABLE'),
(193, 1, 'Tarjetas de Crédito', '#', '51.3', 'LOCKABLE'),
(194, 193, 'Nueva Tarjeta', 'modules/credit_card/fNewCreditCard', '1.0', 'LOCKABLE'),
(195, 193, 'Actualizar Tarjeta', 'modules/credit_card/fSearchCreditCard', '1.1', 'LOCKABLE'),
(196, 175, 'Artículos de Mantenimiento', '#', '4.0', 'LOCKABLE'),
(197, 196, 'Nuevo Artículo', 'modules/maintenance_item/fNewMaintenanceItem', '1.0', 'LOCKABLE'),
(198, 196, 'Actualizar Artículo', 'modules/maintenance_item/fSearchMaintenanceItem', '1.1', 'LOCKABLE'),
(199, NULL, 'Año Lectivo', '#', '45.0', 'LOCKABLE'),
(200, 1, 'Materias del Ministerio', '#', '48.9', 'LOCKABLE'),
(201, 200, 'Nueva Materia', 'modules/ministry_subject/fNewMinistrySubject', '1.0', 'LOCKABLE'),
(202, 200, 'Actualizar Materia', 'modules/ministry_subject/fSearchMinistrySubject', '2.0', 'LOCKABLE'),
(203, 39, 'Materias del Ministerio', '#', '203.0', 'LOCKABLE'),
(204, 203, 'Definir Equivalencias', 'modules/subject_ministry_equivalence/fNewSubjectMinistryEquivalence', '1.0', 'LOCKABLE'),
(205, 199, 'Registro de Novedades', '#', '205.0', 'LOCKABLE'),
(206, 205, 'Registrar Novedad', 'modules/incident/fNewIncident', '1.0', 'LOCKABLE'),
(207, NULL, 'Centro Médico', '#', '207.0', 'LOCKABLE'),
(208, 207, 'Registrar Visita', 'modules/visits_medical_center/fNewVisit', '1.0', 'LOCKABLE'),
(209, 142, 'Actividades Obligatorias', 'modules/activity/fTeacherCalendarActivity', '1.0', 'LOCKABLE'),
(210, 142, 'Actividades Optativas', 'modules/opt_activity/fTeacherCalendarOptionalActivity', '1.0', 'LOCKABLE'),
(211, 39, 'Disciplina', '#', '211.0', 'LOCKABLE'),
(212, 211, 'Modo Calificación x Nivel', 'modules/level_section_year/fSearchYear', '1.0', 'LOCKABLE'),
(213, 160, 'Crear Act. General', 'modules/general_activity/fGeneralCalendarActivity', '1.0', 'LOCKABLE'),
(214, 149, 'Actividades', '#', '1.0', 'LOCKABLE'),
(215, 214, 'Actividades x Curso o Materia Optativa', 'modules/reports/activity/fViewActivitiesByCourseOrSubject', '1.0', 'LOCKABLE'),
(216, 156, 'Selección de Rubros para el Año', 'modules/student_year_debts/fSearchStudentdForSettingDebts', '1.0', 'LOCKABLE'),
(217, 156, 'Anular Factura', 'modules/student_year_debts/fSearchInvoiceToVoid', '3.0', 'LOCKABLE'),
(218, 134, 'Exámenes de Recuperación', '#', '218.0', 'LOCKABLE'),
(219, 218, 'Materias Obligatorias', '#', '1.0', 'LOCKABLE'),
(220, 219, 'Configurar Exámenes', 'modules/subject_course_retest/fNewSubjectCourseRetest', '1.0', 'LOCKABLE'),
(221, 219, 'Calififcar Exámenes', 'modules/subject_course_retest_student/fNewSubjectCourseRetestStudent', '2.0', 'LOCKABLE'),
(222, 218, 'Materias Optativas', '#', '2.0', 'LOCKABLE'),
(223, 222, 'Configurar Exámenes', 'modules/optional_subject_retest/fNewOptionalSubjectRetest', '1.0', 'LOCKABLE'),
(224, 222, 'Calififcar Exámenes', 'modules/optional_subject_retest_student/fNewOptionalSubjectRetestStudent', '2.0', 'LOCKABLE'),
(225, 199, 'Supletorios', '#', '225.0', 'LOCKABLE'),
(226, 225, 'Calificar Exámenes', 'modules/resit_student/fGradeResitExam', '1.0', 'LOCKABLE'),
(227, 199, 'Registro de Hermanos', '#', '227.0', 'LOCKABLE'),
(228, 227, 'Ingresar Solicitud', 'modules/relationship_application/fSelectStudent', '1.0', 'LOCKABLE'),
(229, 45, 'Aprobar Solicitud de Hermanos', 'modules/relationship_application/fRelationshipApproval', '229.0', 'LOCKABLE'),
(230, 1, 'Descuentos', '#', '51.3', 'LOCKABLE'),
(231, 230, 'Nuevo Descuento', 'modules/discount/fNewDiscount', '1.0', 'LOCKABLE'),
(232, 230, 'Actualizar Descuento', 'modules/discount/fSearchDiscount', '2.0', 'LOCKABLE'),
(233, 39, 'Descuentos', '#', '115.0', 'LOCKABLE'),
(234, 233, 'Iniciar/Continuar', 'modules/year_discount/fAssignYearDiscount', '1.0', 'LOCKABLE'),
(235, 1, 'Documentos Habilitantes', '#', '51.3', 'LOCKABLE'),
(236, 235, 'Nuevo Documento', 'modules/qualifying_document/fNewQualifyingDocument', '1.0', 'LOCKABLE'),
(237, 235, 'Actualizar Documento', 'modules/qualifying_document/fSearchQualifyingDocument', '2.0', 'LOCKABLE'),
(238, 39, 'Documentos Habilitantes', '#', '51.3', 'LOCKABLE'),
(239, 238, 'Iniciar/Continuar', 'modules/year_qualifying_document/fYearQualifyingDocument', '1.0', 'LOCKABLE'),
(240, 225, 'Determinar Supletorios', 'modules/resit_student/fSelectLevel', '0.1', 'LOCKABLE'),
(241, 199, 'Exámenes de Grado', '#', '225.1', 'LOCKABLE'),
(242, 241, 'Calificar Exámenes', 'modules/grade_subject_score/fSelectSchoolYearToSetScore', '1.0', 'LOCKABLE'),
(243, 199, 'Descuentos', '#', '45.1', 'LOCKABLE'),
(244, 243, 'Asignar a Estudiante', 'modules/student_year_discount_values/fSearchStudentForAssignDiscounts', '1.0', 'LOCKABLE'),
(245, 156, 'Mantenimiento', '#', '4.0', 'LOCKABLE'),
(246, 245, 'Ingreso de Stock', 'modules/income_stock/fNewIncomeStock', '1.0', 'LOCKABLE'),
(247, 245, 'Salida de Stock', 'modules/stock_request/fNewStockRequest', '1.0', 'LOCKABLE'),
(248, 199, 'Calendario Actvidades Empleados', 'modules/employee/general/fActivitiesForEmployee', '248.0', 'LOCKABLE'),
(249, 149, 'Ministerio', '#', '4.0', 'LOCKABLE'),
(250, 249, 'Contrato de Servicios', 'modules/reports/ministry/fServiceContract', '1.0', 'LOCKABLE'),
(251, 249, 'Estudiantes Matriculados', 'modules/reports/student_matriculated/fViewMatriculatedStudents', '2.0', 'LOCKABLE'),
(252, 249, 'Acta de Matrícula', 'modules/reports/ministry/fRegistrationCertificate', '3.0', 'LOCKABLE'),
(253, 249, 'Acta de Grado', 'modules/reports/school_certificate/fNewSchoolCertificate', '4.0', 'LOCKABLE'),
(254, 167, 'Mínimo de Supletorios Ministerio', 'modules/year_resit_exam_ranges_ministry/fNewYearResitExamRangesMinistry', '3.0', 'LOCKABLE');

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `profile`
--

CREATE TABLE IF NOT EXISTS `profile` (
  `serial_prf` int(11) NOT NULL AUTO_INCREMENT,
  `name_prf` varchar(150) NOT NULL,
  `type_prf` enum('INTERNAL','EXTERNAL') NOT NULL,
  `status_prf` enum('ACTIVE','INACTIVE') NOT NULL,
  `owner_prf` enum('SYSTEM','USER') NOT NULL DEFAULT 'USER',
  PRIMARY KEY (`serial_prf`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;

--
-- Volcar la base de datos para la tabla `profile`
--

INSERT INTO `profile` (`serial_prf`, `name_prf`, `type_prf`, `status_prf`, `owner_prf`) VALUES
(1, 'Administrador', 'INTERNAL', 'ACTIVE', 'SYSTEM'),
(2, 'Representante', 'EXTERNAL', 'ACTIVE', 'SYSTEM'),
(3, 'Estudiante', 'EXTERNAL', 'ACTIVE', 'SYSTEM'),
(4, 'Docente', 'INTERNAL', 'ACTIVE', 'SYSTEM'),
(5, 'Administrativo', 'INTERNAL', 'ACTIVE', 'SYSTEM');

--
-- (Evento) desencadenante `profile`
--
DROP TRIGGER IF EXISTS `profile_status_trg`;
DELIMITER //
CREATE TRIGGER `profile_status_trg` BEFORE UPDATE ON `profile`
 FOR EACH ROW BEGIN
	/*change user_profile to INACTIVE if profile is INACTIVE*/
	IF NEW.status_prf = 'INACTIVE' THEN
		UPDATE user_profile upr
		SET upr.status_upr = 'INACTIVE'
		WHERE upr.serial_prf = OLD.serial_prf;
	END IF;
END
//
DELIMITER ;

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `profile_process`
--

CREATE TABLE IF NOT EXISTS `profile_process` (
  `serial_prf` int(11) DEFAULT NULL,
  `serial_prc` int(11) DEFAULT NULL,
  `status_ppr` enum('ACTIVE','BLOCKED') DEFAULT 'ACTIVE',
  KEY `fk_has_process` (`serial_prf`),
  KEY `fk_in_profile` (`serial_prc`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Volcar la base de datos para la tabla `profile_process`
--

INSERT INTO `profile_process` (`serial_prf`, `serial_prc`, `status_ppr`) VALUES
(1, 1, 'ACTIVE'),
(1, 2, 'ACTIVE'),
(1, 3, 'ACTIVE'),
(1, 4, 'ACTIVE'),
(1, 5, 'ACTIVE'),
(1, 6, 'ACTIVE'),
(1, 7, 'ACTIVE'),
(1, 8, 'ACTIVE'),
(1, 9, 'ACTIVE'),
(1, 10, 'ACTIVE'),
(1, 11, 'ACTIVE'),
(1, 12, 'ACTIVE'),
(1, 13, 'ACTIVE'),
(1, 14, 'ACTIVE'),
(1, 15, 'ACTIVE'),
(1, 16, 'ACTIVE'),
(1, 17, 'ACTIVE'),
(1, 18, 'ACTIVE'),
(1, 19, 'ACTIVE'),
(1, 20, 'ACTIVE'),
(1, 21, 'ACTIVE'),
(1, 22, 'ACTIVE'),
(1, 23, 'ACTIVE'),
(1, 24, 'ACTIVE'),
(1, 25, 'ACTIVE'),
(1, 26, 'ACTIVE'),
(1, 27, 'ACTIVE'),
(1, 28, 'ACTIVE'),
(1, 29, 'ACTIVE'),
(1, 30, 'ACTIVE'),
(1, 31, 'ACTIVE'),
(1, 32, 'ACTIVE'),
(1, 33, 'ACTIVE'),
(1, 34, 'ACTIVE'),
(1, 35, 'ACTIVE'),
(1, 36, 'ACTIVE'),
(1, 37, 'ACTIVE'),
(1, 38, 'ACTIVE'),
(1, 39, 'ACTIVE'),
(1, 40, 'ACTIVE'),
(1, 41, 'ACTIVE'),
(1, 42, 'ACTIVE'),
(1, 43, 'ACTIVE'),
(1, 44, 'ACTIVE'),
(1, 45, 'ACTIVE'),
(1, 48, 'ACTIVE'),
(1, 49, 'ACTIVE'),
(1, 50, 'ACTIVE'),
(1, 51, 'ACTIVE'),
(1, 52, 'ACTIVE'),
(1, 54, 'ACTIVE'),
(1, 55, 'ACTIVE'),
(1, 56, 'ACTIVE'),
(1, 57, 'ACTIVE'),
(1, 58, 'ACTIVE'),
(1, 59, 'ACTIVE'),
(1, 60, 'ACTIVE'),
(1, 61, 'ACTIVE'),
(1, 62, 'ACTIVE'),
(1, 63, 'ACTIVE'),
(1, 64, 'ACTIVE'),
(1, 65, 'ACTIVE'),
(1, 66, 'ACTIVE'),
(1, 67, 'ACTIVE'),
(1, 68, 'ACTIVE'),
(1, 69, 'ACTIVE'),
(1, 70, 'ACTIVE'),
(1, 71, 'ACTIVE'),
(1, 72, 'ACTIVE'),
(1, 74, 'ACTIVE'),
(1, 75, 'ACTIVE'),
(1, 77, 'ACTIVE'),
(1, 80, 'ACTIVE'),
(1, 81, 'ACTIVE'),
(1, 83, 'ACTIVE'),
(1, 84, 'ACTIVE'),
(1, 86, 'ACTIVE'),
(1, 87, 'ACTIVE'),
(1, 89, 'ACTIVE'),
(1, 90, 'ACTIVE'),
(1, 92, 'ACTIVE'),
(1, 93, 'ACTIVE'),
(1, 95, 'ACTIVE'),
(1, 96, 'ACTIVE'),
(1, 98, 'ACTIVE'),
(1, 99, 'ACTIVE'),
(1, 103, 'ACTIVE'),
(1, 104, 'ACTIVE'),
(1, 105, 'ACTIVE'),
(1, 107, 'ACTIVE'),
(1, 108, 'ACTIVE'),
(1, 109, 'ACTIVE'),
(1, 110, 'ACTIVE'),
(1, 111, 'ACTIVE'),
(1, 112, 'ACTIVE'),
(1, 113, 'ACTIVE'),
(1, 114, 'ACTIVE'),
(1, 115, 'ACTIVE'),
(1, 116, 'ACTIVE'),
(1, 117, 'ACTIVE'),
(1, 118, 'ACTIVE'),
(1, 119, 'ACTIVE'),
(1, 133, 'ACTIVE'),
(1, 136, 'ACTIVE'),
(1, 137, 'ACTIVE'),
(1, 138, 'ACTIVE'),
(1, 144, 'ACTIVE'),
(1, 146, 'ACTIVE'),
(1, 148, 'ACTIVE'),
(1, 152, 'ACTIVE'),
(1, 153, 'ACTIVE'),
(1, 155, 'ACTIVE'),
(1, 157, 'ACTIVE'),
(1, 159, 'ACTIVE'),
(1, 161, 'ACTIVE'),
(1, 163, 'ACTIVE'),
(1, 164, 'ACTIVE'),
(1, 166, 'ACTIVE'),
(1, 168, 'ACTIVE'),
(1, 169, 'ACTIVE'),
(1, 171, 'ACTIVE'),
(1, 173, 'ACTIVE'),
(1, 174, 'ACTIVE'),
(1, 177, 'ACTIVE'),
(1, 178, 'ACTIVE'),
(1, 180, 'ACTIVE'),
(1, 181, 'ACTIVE'),
(1, 183, 'ACTIVE'),
(1, 184, 'ACTIVE'),
(1, 186, 'ACTIVE'),
(1, 188, 'ACTIVE'),
(1, 189, 'ACTIVE'),
(1, 191, 'ACTIVE'),
(1, 192, 'ACTIVE'),
(1, 194, 'ACTIVE'),
(1, 195, 'ACTIVE'),
(1, 197, 'ACTIVE'),
(1, 198, 'ACTIVE'),
(1, 201, 'ACTIVE'),
(1, 202, 'ACTIVE'),
(1, 204, 'ACTIVE'),
(1, 206, 'ACTIVE'),
(1, 208, 'ACTIVE'),
(1, 212, 'ACTIVE'),
(1, 213, 'ACTIVE'),
(1, 215, 'ACTIVE'),
(1, 217, 'ACTIVE'),
(1, 229, 'ACTIVE'),
(1, 231, 'ACTIVE'),
(1, 232, 'ACTIVE'),
(1, 234, 'ACTIVE'),
(1, 236, 'ACTIVE'),
(1, 237, 'ACTIVE'),
(1, 239, 'ACTIVE'),
(1, 240, 'ACTIVE'),
(1, 242, 'ACTIVE'),
(1, 244, 'ACTIVE'),
(1, 246, 'ACTIVE'),
(1, 247, 'ACTIVE'),
(1, 250, 'ACTIVE'),
(1, 251, 'ACTIVE'),
(1, 252, 'ACTIVE'),
(1, 253, 'ACTIVE'),
(1, 254, 'ACTIVE'),
(2, 46, 'ACTIVE'),
(2, 47, 'ACTIVE'),
(2, 101, 'ACTIVE'),
(2, 139, 'ACTIVE'),
(2, 141, 'ACTIVE'),
(2, 148, 'ACTIVE'),
(2, 216, 'ACTIVE'),
(2, 228, 'ACTIVE'),
(3, 131, 'ACTIVE'),
(3, 132, 'ACTIVE'),
(3, 139, 'ACTIVE'),
(3, 140, 'ACTIVE'),
(3, 148, 'ACTIVE'),
(4, 120, 'ACTIVE'),
(4, 120, 'ACTIVE'),
(4, 124, 'ACTIVE'),
(4, 125, 'ACTIVE'),
(4, 126, 'ACTIVE'),
(4, 127, 'ACTIVE'),
(4, 128, 'ACTIVE'),
(4, 129, 'ACTIVE'),
(4, 135, 'ACTIVE'),
(4, 145, 'ACTIVE'),
(4, 148, 'ACTIVE'),
(4, 209, 'ACTIVE'),
(4, 210, 'ACTIVE'),
(4, 220, 'ACTIVE'),
(4, 221, 'ACTIVE'),
(4, 223, 'ACTIVE'),
(4, 224, 'ACTIVE'),
(4, 226, 'ACTIVE'),
(5, 148, 'ACTIVE'),
(5, 248, 'ACTIVE');

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `provider`
--

CREATE TABLE IF NOT EXISTS `provider` (
  `serial_prv` int(11) NOT NULL AUTO_INCREMENT,
  `name_prv` varchar(160) NOT NULL,
  `id_prv` varchar(15) NOT NULL,
  `address_prv` text NOT NULL,
  `phone1_prv` varchar(30) NOT NULL,
  `phone2_prv` varchar(30) DEFAULT NULL,
  `contact_name_prv` varchar(255) NOT NULL,
  `status_prv` enum('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  PRIMARY KEY (`serial_prv`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

--
-- Volcar la base de datos para la tabla `provider`
--


-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `psychological_file`
--

CREATE TABLE IF NOT EXISTS `psychological_file` (
  `serial_pyf` int(11) NOT NULL AUTO_INCREMENT,
  `serial_std` int(11) DEFAULT NULL,
  `therapy_pyf` enum('PSYCHOLOGICAL','VERBAL','VISUAL','LEARNING','BEHAVIOUR','AUDITORY','FAMILY','OTHER') DEFAULT NULL,
  `discharged_pyf` enum('YES','NO') DEFAULT NULL,
  `diagnosis_pyf` text,
  `diagnosed_by_pyf` text,
  `therapy_time_pyf` text,
  `abandon_reason_pyf` text,
  `medication_pyf` text,
  `therapeutic_center_pyf` varchar(512) DEFAULT NULL,
  `registration_date_pyf` date NOT NULL,
  `status_pyf` enum('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  PRIMARY KEY (`serial_pyf`),
  KEY `fk_relationship_89` (`serial_std`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

--
-- Volcar la base de datos para la tabla `psychological_file`
--


-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `qualifying_document`
--

CREATE TABLE IF NOT EXISTS `qualifying_document` (
  `serial_qdc` int(11) NOT NULL AUTO_INCREMENT,
  `name_qdc` varchar(256) NOT NULL,
  `description_qdc` text NOT NULL,
  `status_qdc` enum('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  PRIMARY KEY (`serial_qdc`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

--
-- Volcar la base de datos para la tabla `qualifying_document`
--


-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `qualitative_equivalence`
--

CREATE TABLE IF NOT EXISTS `qualitative_equivalence` (
  `serial_qeq` int(11) NOT NULL AUTO_INCREMENT,
  `serial_scy` int(11) DEFAULT NULL,
  `lower_percentage_qeq` decimal(5,2) NOT NULL,
  `higher_percentage_qeq` decimal(5,2) NOT NULL,
  `name_qeq` varchar(50) NOT NULL,
  `code_qeq` varchar(15) NOT NULL,
  `status_qeq` enum('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  PRIMARY KEY (`serial_qeq`),
  KEY `fk_relationship_161` (`serial_scy`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

--
-- Volcar la base de datos para la tabla `qualitative_equivalence`
--


-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `related_student`
--

CREATE TABLE IF NOT EXISTS `related_student` (
  `serial_rap` int(11) NOT NULL,
  `serial_std` int(11) NOT NULL,
  `stu_serial_std` int(11) NOT NULL,
  `comment_rnt` varchar(128) DEFAULT NULL,
  `status_rnt` enum('PENDING','APROVED','DENIED','INACTIVE') NOT NULL DEFAULT 'PENDING',
  PRIMARY KEY (`serial_rap`,`serial_std`,`stu_serial_std`),
  KEY `fk_relationship_233` (`serial_std`),
  KEY `fk_relationship_234` (`stu_serial_std`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Volcar la base de datos para la tabla `related_student`
--


-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `relationship_application`
--

CREATE TABLE IF NOT EXISTS `relationship_application` (
  `serial_rap` int(11) NOT NULL AUTO_INCREMENT,
  `serial_grd` int(11) DEFAULT NULL,
  `date_rap` date NOT NULL,
  `comment_rap` varchar(256) DEFAULT NULL,
  `status_rap` enum('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  PRIMARY KEY (`serial_rap`),
  KEY `fk_relationship_232` (`serial_grd`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

--
-- Volcar la base de datos para la tabla `relationship_application`
--


-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `request_detail`
--

CREATE TABLE IF NOT EXISTS `request_detail` (
  `serial_ist` int(11) DEFAULT NULL,
  `serial_srq` int(11) DEFAULT NULL,
  `item_quantity_rdt` decimal(5,2) NOT NULL,
  `status_rdt` enum('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  KEY `fk_relationship_188` (`serial_ist`),
  KEY `fk_relationship_189` (`serial_srq`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Volcar la base de datos para la tabla `request_detail`
--


-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `resit_change_log`
--

CREATE TABLE IF NOT EXISTS `resit_change_log` (
  `serial_usr` int(11) DEFAULT NULL,
  `serial_chr` int(11) DEFAULT NULL,
  `remote_id_rst` int(11) DEFAULT NULL,
  `serial_std` int(11) DEFAULT NULL,
  `serial_spt` int(11) DEFAULT NULL,
  `serial_lvl` int(11) DEFAULT NULL,
  `serial_scy` int(11) DEFAULT NULL,
  `serial_set` int(11) DEFAULT NULL,
  `type_rst` enum('SBJ','OSB') DEFAULT 'SBJ',
  `change_date_rcl` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `last_grade_rcl` decimal(5,2) NOT NULL,
  `comment_rcl` text,
  KEY `fk_relationship_218` (`remote_id_rst`,`serial_std`,`serial_spt`,`serial_lvl`,`serial_scy`,`serial_set`,`type_rst`),
  KEY `fk_relationship_219` (`serial_usr`),
  KEY `fk_relationship_220` (`serial_chr`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Volcar la base de datos para la tabla `resit_change_log`
--


-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `resit_student`
--

CREATE TABLE IF NOT EXISTS `resit_student` (
  `serial_emp` int(11) NOT NULL,
  `remote_id_rst` int(11) NOT NULL,
  `serial_std` int(11) NOT NULL,
  `serial_spt` int(11) NOT NULL,
  `serial_lvl` int(11) NOT NULL,
  `serial_scy` int(11) NOT NULL,
  `serial_set` int(11) NOT NULL,
  `required_grade_rst` decimal(5,2) NOT NULL,
  `grade_rst` decimal(5,2) DEFAULT NULL,
  `type_rst` enum('CSB','LOP') NOT NULL DEFAULT 'CSB',
  `status_rst` enum('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  PRIMARY KEY (`remote_id_rst`,`serial_std`,`serial_spt`,`serial_lvl`,`serial_scy`,`serial_set`,`type_rst`),
  KEY `fk_relationship_209` (`serial_std`,`serial_spt`,`serial_lvl`,`serial_scy`,`serial_set`),
  KEY `fk_relationship_211` (`serial_emp`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Volcar la base de datos para la tabla `resit_student`
--


-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `route`
--

CREATE TABLE IF NOT EXISTS `route` (
  `serial_rot` int(11) NOT NULL AUTO_INCREMENT,
  `code_rot` varchar(10) NOT NULL,
  `description_rot` text NOT NULL,
  `status_rot` enum('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  PRIMARY KEY (`serial_rot`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

--
-- Volcar la base de datos para la tabla `route`
--


-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `school_setup`
--

CREATE TABLE IF NOT EXISTS `school_setup` (
  `serial_ste` int(11) DEFAULT NULL,
  `serial_crr` int(11) DEFAULT NULL,
  `name_sst` varchar(1024) NOT NULL,
  `city_sst` varchar(100) NOT NULL,
  `address_sst` text NOT NULL,
  `phone1_sst` varchar(30) NOT NULL,
  `phone2_sst` varchar(30) DEFAULT NULL,
  `phone3_sst` varchar(30) DEFAULT NULL,
  `contact_name_sst` varchar(250) NOT NULL,
  `contact_phone_sst` varchar(20) NOT NULL,
  `logo_sst` varchar(100) NOT NULL,
  `slogan_sst` varchar(300) DEFAULT NULL,
  `main_color_sst` varchar(7) NOT NULL,
  `secondary_color_sst` varchar(7) NOT NULL,
  KEY `fk_currency_for_school` (`serial_crr`),
  KEY `fk_school_state` (`serial_ste`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Volcar la base de datos para la tabla `school_setup`
--

INSERT INTO `school_setup` (`serial_ste`, `serial_crr`, `name_sst`, `city_sst`, `address_sst`, `phone1_sst`, `phone2_sst`, `phone3_sst`, `contact_name_sst`, `contact_phone_sst`, `logo_sst`, `slogan_sst`, `main_color_sst`, `secondary_color_sst`) VALUES
(19, 47, 'Colegio Guanajuato', 'Quito', 'sdf sd f f s', '444444', '', '', 'Pancho Cevallos', '2232323', '5de350.jpg', '', '0a040a', '3f615e');

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `school_year`
--

CREATE TABLE IF NOT EXISTS `school_year` (
  `serial_scy` int(11) NOT NULL AUTO_INCREMENT,
  `start_date_scy` date NOT NULL,
  `end_date_scy` date NOT NULL,
  `status_scy` set('ACTIVE','CURRENT','ENDED','NEW','INACTIVE') NOT NULL DEFAULT 'NEW',
  `name_scy` varchar(150) NOT NULL,
  `grade_precision_scy` int(11) NOT NULL,
  `invoice_due_max_day_scy` int(11) DEFAULT NULL,
  `ministry_max_grade_scy` decimal(6,2) NOT NULL,
  PRIMARY KEY (`serial_scy`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

--
-- Volcar la base de datos para la tabla `school_year`
--

INSERT INTO `school_year` (`serial_scy`, `start_date_scy`, `end_date_scy`, `status_scy`, `name_scy`, `grade_precision_scy`, `invoice_due_max_day_scy`, `ministry_max_grade_scy`) VALUES
(1, '2011-02-08', '2011-11-08', 'NEW', 'Sierra 2010-2011', 2, 15, '20.00');

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `section`
--

CREATE TABLE IF NOT EXISTS `section` (
  `serial_set` int(11) NOT NULL AUTO_INCREMENT,
  `name_set` varchar(150) NOT NULL,
  `description_set` text,
  `status_set` enum('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  `type_set` enum('DAY','EVENING','NIGHT') NOT NULL DEFAULT 'DAY',
  PRIMARY KEY (`serial_set`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

--
-- Volcar la base de datos para la tabla `section`
--

INSERT INTO `section` (`serial_set`, `name_set`, `description_set`, `status_set`, `type_set`) VALUES
(1, 'Primaria', 'dfs d fsd fsd f s', 'ACTIVE', 'DAY'),
(2, 'Secundaria', 'sdf s fsdf sdfsf  fd', 'ACTIVE', 'DAY');

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `section_by_year`
--

CREATE TABLE IF NOT EXISTS `section_by_year` (
  `serial_scy` int(11) NOT NULL,
  `serial_set` int(11) NOT NULL,
  `status_sby` enum('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  PRIMARY KEY (`serial_scy`,`serial_set`),
  KEY `fk_section_in_year` (`serial_set`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Volcar la base de datos para la tabla `section_by_year`
--

INSERT INTO `section_by_year` (`serial_scy`, `serial_set`, `status_sby`) VALUES
(1, 1, 'ACTIVE'),
(1, 2, 'ACTIVE');

--
-- (Evento) desencadenante `section_by_year`
--
DROP TRIGGER IF EXISTS `seccion_year_trg`;
DELIMITER //
CREATE TRIGGER `seccion_year_trg` BEFORE UPDATE ON `section_by_year`
 FOR EACH ROW BEGIN
	/*change level section year status to INACTIVE 
	when a section Year is changed to INACTIVE*/
	IF NEW.status_sby = 'INACTIVE' THEN
	UPDATE level_section_year lsy
	SET lsy.status_lsy = 'INACTIVE'
	WHERE lsy.serial_scy = OLD.serial_scy
	AND	lsy.serial_set = OLD.serial_set;
	END IF;
END
//
DELIMITER ;

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `shift`
--

CREATE TABLE IF NOT EXISTS `shift` (
  `serial_sft` int(11) NOT NULL AUTO_INCREMENT,
  `name_sft` varchar(120) NOT NULL,
  `description_sft` text NOT NULL,
  `status_sft` enum('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  PRIMARY KEY (`serial_sft`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

--
-- Volcar la base de datos para la tabla `shift`
--


-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `specialty`
--

CREATE TABLE IF NOT EXISTS `specialty` (
  `serial_spt` int(11) NOT NULL AUTO_INCREMENT,
  `name_spt` varchar(150) NOT NULL,
  `description_spt` text,
  `status_spt` enum('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  PRIMARY KEY (`serial_spt`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

--
-- Volcar la base de datos para la tabla `specialty`
--

INSERT INTO `specialty` (`serial_spt`, `name_spt`, `description_spt`, `status_spt`) VALUES
(1, '* Ninguna', 'Opción para todo nivel que no tiene una especialización', 'ACTIVE'),
(2, 'Físico Matemático', 'asd sa das  dsa', 'ACTIVE'),
(3, 'Ciencias Sociales', 'aas das d as d', 'ACTIVE'),
(4, 'Químico Biólogo', 'sd fsd fsd fs  s s f', 'ACTIVE');

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `special_grade_change_log`
--

CREATE TABLE IF NOT EXISTS `special_grade_change_log` (
  `serial_usr` int(11) DEFAULT NULL,
  `serial_chr` int(11) DEFAULT NULL,
  `serial_ysg` int(11) DEFAULT NULL,
  `serial_std` int(11) DEFAULT NULL,
  `serial_spt` int(11) DEFAULT NULL,
  `serial_lvl` int(11) DEFAULT NULL,
  `serial_scy` int(11) DEFAULT NULL,
  `serial_set` int(11) DEFAULT NULL,
  `change_date_sgl` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `last_grade_sgl` decimal(5,2) NOT NULL,
  `comment_sgl` text,
  KEY `fk_relationship_221` (`serial_ysg`,`serial_std`,`serial_spt`,`serial_lvl`,`serial_scy`,`serial_set`),
  KEY `fk_relationship_222` (`serial_chr`),
  KEY `fk_relationship_223` (`serial_usr`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Volcar la base de datos para la tabla `special_grade_change_log`
--


-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `state`
--

CREATE TABLE IF NOT EXISTS `state` (
  `serial_ste` int(11) NOT NULL AUTO_INCREMENT,
  `serial_cou` int(11) DEFAULT NULL,
  `name_ste` varchar(100) NOT NULL,
  `status_ste` enum('ACTIVE','INACTIVE') DEFAULT 'ACTIVE',
  PRIMARY KEY (`serial_ste`),
  KEY `fk_country_state` (`serial_cou`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=256 ;

--
-- Volcar la base de datos para la tabla `state`
--

INSERT INTO `state` (`serial_ste`, `serial_cou`, `name_ste`, `status_ste`) VALUES
(1, 61, 'Azuay', 'ACTIVE'),
(2, 61, 'Bolívar', 'ACTIVE'),
(3, 61, 'Cañar', 'ACTIVE'),
(4, 61, 'Carchi', 'ACTIVE'),
(5, 61, 'Chimborazo', 'ACTIVE'),
(6, 61, 'Cotopaxi', 'ACTIVE'),
(7, 61, 'El Oro', 'ACTIVE'),
(8, 61, 'Esmeraldas', 'ACTIVE'),
(9, 61, 'Galápagos', 'ACTIVE'),
(10, 61, 'Guayas', 'ACTIVE'),
(11, 61, 'Imbabura', 'ACTIVE'),
(12, 61, 'Loja', 'ACTIVE'),
(13, 61, 'Los Ríos', 'ACTIVE'),
(14, 61, 'Manabí', 'ACTIVE'),
(15, 61, 'Morona Santiago', 'ACTIVE'),
(16, 61, 'Napo', 'ACTIVE'),
(17, 61, 'Orellana', 'ACTIVE'),
(18, 61, 'Pastaza', 'ACTIVE'),
(19, 61, 'Pichincha', 'ACTIVE'),
(20, 61, 'Santa Elena', 'ACTIVE'),
(21, 61, 'Sto.Domingo de los Tsáchilas', 'ACTIVE'),
(22, 61, 'Sucumbíos', 'ACTIVE'),
(23, 61, 'Tungurahua', 'ACTIVE'),
(24, 61, 'Zamora Chinchipe', 'ACTIVE'),
(25, 1, 'Otra', 'ACTIVE'),
(26, 2, 'Otra', 'ACTIVE'),
(27, 3, 'Otra', 'ACTIVE'),
(28, 4, 'Otra', 'ACTIVE'),
(29, 5, 'Otra', 'ACTIVE'),
(30, 6, 'Otra', 'ACTIVE'),
(31, 7, 'Otra', 'ACTIVE'),
(32, 8, 'Otra', 'ACTIVE'),
(33, 9, 'Otra', 'ACTIVE'),
(34, 10, 'Otra', 'ACTIVE'),
(35, 11, 'Otra', 'ACTIVE'),
(36, 12, 'Otra', 'ACTIVE'),
(37, 13, 'Otra', 'ACTIVE'),
(38, 14, 'Otra', 'ACTIVE'),
(39, 15, 'Otra', 'ACTIVE'),
(40, 16, 'Otra', 'ACTIVE'),
(41, 17, 'Otra', 'ACTIVE'),
(42, 18, 'Otra', 'ACTIVE'),
(43, 19, 'Otra', 'ACTIVE'),
(44, 20, 'Otra', 'ACTIVE'),
(45, 21, 'Otra', 'ACTIVE'),
(46, 22, 'Otra', 'ACTIVE'),
(47, 23, 'Otra', 'ACTIVE'),
(48, 24, 'Otra', 'ACTIVE'),
(49, 25, 'Otra', 'ACTIVE'),
(50, 26, 'Otra', 'ACTIVE'),
(51, 27, 'Otra', 'ACTIVE'),
(52, 28, 'Otra', 'ACTIVE'),
(53, 29, 'Otra', 'ACTIVE'),
(54, 30, 'Otra', 'ACTIVE'),
(55, 31, 'Otra', 'ACTIVE'),
(56, 32, 'Otra', 'ACTIVE'),
(57, 33, 'Otra', 'ACTIVE'),
(58, 34, 'Otra', 'ACTIVE'),
(59, 35, 'Otra', 'ACTIVE'),
(60, 36, 'Otra', 'ACTIVE'),
(61, 37, 'Otra', 'ACTIVE'),
(62, 38, 'Otra', 'ACTIVE'),
(63, 39, 'Otra', 'ACTIVE'),
(64, 40, 'Otra', 'ACTIVE'),
(65, 41, 'Otra', 'ACTIVE'),
(66, 42, 'Otra', 'ACTIVE'),
(67, 43, 'Otra', 'ACTIVE'),
(68, 44, 'Otra', 'ACTIVE'),
(69, 45, 'Otra', 'ACTIVE'),
(70, 46, 'Otra', 'ACTIVE'),
(71, 47, 'Otra', 'ACTIVE'),
(72, 48, 'Otra', 'ACTIVE'),
(73, 49, 'Otra', 'ACTIVE'),
(74, 50, 'Otra', 'ACTIVE'),
(75, 51, 'Otra', 'ACTIVE'),
(76, 52, 'Otra', 'ACTIVE'),
(77, 53, 'Otra', 'ACTIVE'),
(78, 54, 'Otra', 'ACTIVE'),
(79, 55, 'Otra', 'ACTIVE'),
(80, 56, 'Otra', 'ACTIVE'),
(81, 57, 'Otra', 'ACTIVE'),
(82, 58, 'Otra', 'ACTIVE'),
(83, 59, 'Otra', 'ACTIVE'),
(84, 60, 'Otra', 'ACTIVE'),
(85, 62, 'Otra', 'ACTIVE'),
(86, 63, 'Otra', 'ACTIVE'),
(87, 64, 'Otra', 'ACTIVE'),
(88, 65, 'Otra', 'ACTIVE'),
(89, 66, 'Otra', 'ACTIVE'),
(90, 67, 'Otra', 'ACTIVE'),
(91, 68, 'Otra', 'ACTIVE'),
(92, 69, 'Otra', 'ACTIVE'),
(93, 70, 'Otra', 'ACTIVE'),
(94, 71, 'Otra', 'ACTIVE'),
(95, 72, 'Otra', 'ACTIVE'),
(96, 73, 'Otra', 'ACTIVE'),
(97, 74, 'Otra', 'ACTIVE'),
(98, 75, 'Otra', 'ACTIVE'),
(99, 76, 'Otra', 'ACTIVE'),
(100, 77, 'Otra', 'ACTIVE'),
(101, 78, 'Otra', 'ACTIVE'),
(102, 79, 'Otra', 'ACTIVE'),
(103, 80, 'Otra', 'ACTIVE'),
(104, 81, 'Otra', 'ACTIVE'),
(105, 82, 'Otra', 'ACTIVE'),
(106, 83, 'Otra', 'ACTIVE'),
(107, 84, 'Otra', 'ACTIVE'),
(108, 85, 'Otra', 'ACTIVE'),
(109, 86, 'Otra', 'ACTIVE'),
(110, 87, 'Otra', 'ACTIVE'),
(111, 88, 'Otra', 'ACTIVE'),
(112, 89, 'Otra', 'ACTIVE'),
(113, 90, 'Otra', 'ACTIVE'),
(114, 91, 'Otra', 'ACTIVE'),
(115, 92, 'Otra', 'ACTIVE'),
(116, 93, 'Otra', 'ACTIVE'),
(117, 94, 'Otra', 'ACTIVE'),
(118, 95, 'Otra', 'ACTIVE'),
(119, 96, 'Otra', 'ACTIVE'),
(120, 97, 'Otra', 'ACTIVE'),
(121, 98, 'Otra', 'ACTIVE'),
(122, 99, 'Otra', 'ACTIVE'),
(123, 100, 'Otra', 'ACTIVE'),
(124, 101, 'Otra', 'ACTIVE'),
(125, 102, 'Otra', 'ACTIVE'),
(126, 103, 'Otra', 'ACTIVE'),
(127, 104, 'Otra', 'ACTIVE'),
(128, 105, 'Otra', 'ACTIVE'),
(129, 106, 'Otra', 'ACTIVE'),
(130, 107, 'Otra', 'ACTIVE'),
(131, 108, 'Otra', 'ACTIVE'),
(132, 109, 'Otra', 'ACTIVE'),
(133, 110, 'Otra', 'ACTIVE'),
(134, 111, 'Otra', 'ACTIVE'),
(135, 112, 'Otra', 'ACTIVE'),
(136, 113, 'Otra', 'ACTIVE'),
(137, 114, 'Otra', 'ACTIVE'),
(138, 115, 'Otra', 'ACTIVE'),
(139, 116, 'Otra', 'ACTIVE'),
(140, 117, 'Otra', 'ACTIVE'),
(141, 118, 'Otra', 'ACTIVE'),
(142, 119, 'Otra', 'ACTIVE'),
(143, 120, 'Otra', 'ACTIVE'),
(144, 121, 'Otra', 'ACTIVE'),
(145, 122, 'Otra', 'ACTIVE'),
(146, 123, 'Otra', 'ACTIVE'),
(147, 124, 'Otra', 'ACTIVE'),
(148, 125, 'Otra', 'ACTIVE'),
(149, 126, 'Otra', 'ACTIVE'),
(150, 127, 'Otra', 'ACTIVE'),
(151, 128, 'Otra', 'ACTIVE'),
(152, 129, 'Otra', 'ACTIVE'),
(153, 130, 'Otra', 'ACTIVE'),
(154, 131, 'Otra', 'ACTIVE'),
(155, 132, 'Otra', 'ACTIVE'),
(156, 133, 'Otra', 'ACTIVE'),
(157, 134, 'Otra', 'ACTIVE'),
(158, 135, 'Otra', 'ACTIVE'),
(159, 136, 'Otra', 'ACTIVE'),
(160, 137, 'Otra', 'ACTIVE'),
(161, 138, 'Otra', 'ACTIVE'),
(162, 139, 'Otra', 'ACTIVE'),
(163, 140, 'Otra', 'ACTIVE'),
(164, 141, 'Otra', 'ACTIVE'),
(165, 142, 'Otra', 'ACTIVE'),
(166, 143, 'Otra', 'ACTIVE'),
(167, 144, 'Otra', 'ACTIVE'),
(168, 145, 'Otra', 'ACTIVE'),
(169, 146, 'Otra', 'ACTIVE'),
(170, 147, 'Otra', 'ACTIVE'),
(171, 148, 'Otra', 'ACTIVE'),
(172, 149, 'Otra', 'ACTIVE'),
(173, 150, 'Otra', 'ACTIVE'),
(174, 151, 'Otra', 'ACTIVE'),
(175, 152, 'Otra', 'ACTIVE'),
(176, 153, 'Otra', 'ACTIVE'),
(177, 154, 'Otra', 'ACTIVE'),
(178, 155, 'Otra', 'ACTIVE'),
(179, 156, 'Otra', 'ACTIVE'),
(180, 157, 'Otra', 'ACTIVE'),
(181, 158, 'Otra', 'ACTIVE'),
(182, 159, 'Otra', 'ACTIVE'),
(183, 160, 'Otra', 'ACTIVE'),
(184, 161, 'Otra', 'ACTIVE'),
(185, 162, 'Otra', 'ACTIVE'),
(186, 163, 'Otra', 'ACTIVE'),
(187, 164, 'Otra', 'ACTIVE'),
(188, 165, 'Otra', 'ACTIVE'),
(189, 166, 'Otra', 'ACTIVE'),
(190, 167, 'Otra', 'ACTIVE'),
(191, 168, 'Otra', 'ACTIVE'),
(192, 169, 'Otra', 'ACTIVE'),
(193, 170, 'Otra', 'ACTIVE'),
(194, 171, 'Otra', 'ACTIVE'),
(195, 172, 'Otra', 'ACTIVE'),
(196, 173, 'Otra', 'ACTIVE'),
(197, 174, 'Otra', 'ACTIVE'),
(198, 175, 'Otra', 'ACTIVE'),
(199, 176, 'Otra', 'ACTIVE'),
(200, 177, 'Otra', 'ACTIVE'),
(201, 178, 'Otra', 'ACTIVE'),
(202, 179, 'Otra', 'ACTIVE'),
(203, 180, 'Otra', 'ACTIVE'),
(204, 181, 'Otra', 'ACTIVE'),
(205, 182, 'Otra', 'ACTIVE'),
(206, 183, 'Otra', 'ACTIVE'),
(207, 184, 'Otra', 'ACTIVE'),
(208, 185, 'Otra', 'ACTIVE'),
(209, 186, 'Otra', 'ACTIVE'),
(210, 187, 'Otra', 'ACTIVE'),
(211, 188, 'Otra', 'ACTIVE'),
(212, 189, 'Otra', 'ACTIVE'),
(213, 190, 'Otra', 'ACTIVE'),
(214, 191, 'Otra', 'ACTIVE'),
(215, 192, 'Otra', 'ACTIVE'),
(216, 193, 'Otra', 'ACTIVE'),
(217, 194, 'Otra', 'ACTIVE'),
(218, 195, 'Otra', 'ACTIVE'),
(219, 196, 'Otra', 'ACTIVE'),
(220, 197, 'Otra', 'ACTIVE'),
(221, 198, 'Otra', 'ACTIVE'),
(222, 199, 'Otra', 'ACTIVE'),
(223, 200, 'Otra', 'ACTIVE'),
(224, 201, 'Otra', 'ACTIVE'),
(225, 202, 'Otra', 'ACTIVE'),
(226, 203, 'Otra', 'ACTIVE'),
(227, 204, 'Otra', 'ACTIVE'),
(228, 205, 'Otra', 'ACTIVE'),
(229, 206, 'Otra', 'ACTIVE'),
(230, 207, 'Otra', 'ACTIVE'),
(231, 208, 'Otra', 'ACTIVE'),
(232, 209, 'Otra', 'ACTIVE'),
(233, 210, 'Otra', 'ACTIVE'),
(234, 211, 'Otra', 'ACTIVE'),
(235, 212, 'Otra', 'ACTIVE'),
(236, 213, 'Otra', 'ACTIVE'),
(237, 214, 'Otra', 'ACTIVE'),
(238, 215, 'Otra', 'ACTIVE'),
(239, 216, 'Otra', 'ACTIVE'),
(240, 217, 'Otra', 'ACTIVE'),
(241, 218, 'Otra', 'ACTIVE'),
(242, 219, 'Otra', 'ACTIVE'),
(243, 220, 'Otra', 'ACTIVE'),
(244, 221, 'Otra', 'ACTIVE'),
(245, 222, 'Otra', 'ACTIVE'),
(246, 223, 'Otra', 'ACTIVE'),
(247, 224, 'Otra', 'ACTIVE'),
(248, 225, 'Otra', 'ACTIVE'),
(249, 226, 'Otra', 'ACTIVE'),
(250, 227, 'Otra', 'ACTIVE'),
(251, 228, 'Otra', 'ACTIVE'),
(252, 229, 'Otra', 'ACTIVE'),
(253, 230, 'Otra', 'ACTIVE'),
(254, 231, 'Otra', 'ACTIVE'),
(255, 232, 'Otra', 'ACTIVE');

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `stock_request`
--

CREATE TABLE IF NOT EXISTS `stock_request` (
  `serial_srq` int(11) NOT NULL AUTO_INCREMENT,
  `serial_udt` int(11) DEFAULT NULL,
  `serial_usr` int(11) DEFAULT NULL,
  `description_srq` text NOT NULL,
  `request_date_srq` date NOT NULL,
  `status_srq` enum('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  PRIMARY KEY (`serial_srq`),
  KEY `fk_relationship_187` (`serial_udt`),
  KEY `fk_relationship_190` (`serial_usr`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

--
-- Volcar la base de datos para la tabla `stock_request`
--


-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `student`
--

CREATE TABLE IF NOT EXISTS `student` (
  `serial_std` int(11) NOT NULL AUTO_INCREMENT,
  `serial_ste` int(11) DEFAULT NULL,
  `serial_usr` int(11) NOT NULL,
  `serial_nnt` int(11) DEFAULT NULL,
  `id_std` varchar(13) NOT NULL,
  `first_name_std` varchar(255) NOT NULL,
  `last_name_std` varchar(255) NOT NULL,
  `birthdate_std` date NOT NULL,
  `birthcity_std` varchar(80) DEFAULT NULL,
  `gender_std` enum('MALE','FEMALE') NOT NULL,
  `address_std` text,
  `phone_std` varchar(30) DEFAULT NULL,
  `type_std` enum('NEW','OLD') NOT NULL DEFAULT 'NEW',
  `email_std` varchar(256) DEFAULT NULL,
  `status_std` enum('ACTIVE','INACTIVE','BLOCKED') NOT NULL DEFAULT 'INACTIVE',
  `lives_with_std` enum('PARENT','SIBLING','GRANDPARENT','AUNT_UNCLE','OTHER') DEFAULT NULL,
  PRIMARY KEY (`serial_std`),
  KEY `fk_relationship_240` (`serial_ste`),
  KEY `fk_relationship_61` (`serial_nnt`),
  KEY `fk_student_user` (`serial_usr`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

--
-- Volcar la base de datos para la tabla `student`
--


-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `student_activity_attachment`
--

CREATE TABLE IF NOT EXISTS `student_activity_attachment` (
  `serial_saa` int(11) NOT NULL AUTO_INCREMENT,
  `name_saa` varchar(256) NOT NULL,
  `filename_saa` varchar(256) NOT NULL,
  `serial_std` int(11) DEFAULT NULL,
  `serial_act` int(11) DEFAULT NULL,
  `status_saa` enum('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  PRIMARY KEY (`serial_saa`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

--
-- Volcar la base de datos para la tabla `student_activity_attachment`
--


-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `student_by_course`
--

CREATE TABLE IF NOT EXISTS `student_by_course` (
  `serial_std` int(11) NOT NULL,
  `serial_crs` int(11) NOT NULL,
  `status_stc` enum('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  PRIMARY KEY (`serial_std`,`serial_crs`),
  KEY `fk_course_for_student` (`serial_crs`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Volcar la base de datos para la tabla `student_by_course`
--


-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `student_criteria_grade`
--

CREATE TABLE IF NOT EXISTS `student_criteria_grade` (
  `serial_sbc` int(11) NOT NULL,
  `serial_att` int(11) NOT NULL,
  `serial_prd` int(11) NOT NULL,
  `serial_gct` int(11) NOT NULL,
  `serial_act` int(11) NOT NULL,
  `serial_std` int(11) NOT NULL,
  `serial_crs` int(11) NOT NULL,
  `grade_scg` decimal(5,2) DEFAULT NULL,
  `is_complete_scg` enum('YES','NO') NOT NULL DEFAULT 'NO',
  `activity_grade_part_scg` decimal(6,2) DEFAULT NULL,
  `status_scg` enum('ACTIVE','INACTIVE','NOT_APPLY') NOT NULL DEFAULT 'ACTIVE',
  PRIMARY KEY (`serial_sbc`,`serial_att`,`serial_prd`,`serial_gct`,`serial_act`,`serial_std`,`serial_crs`),
  KEY `fk_relationship_100` (`serial_std`,`serial_crs`),
  KEY `fk_relationship_99` (`serial_att`,`serial_prd`,`serial_sbc`,`serial_gct`,`serial_act`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Volcar la base de datos para la tabla `student_criteria_grade`
--


-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `student_guardian`
--

CREATE TABLE IF NOT EXISTS `student_guardian` (
  `serial_std` int(11) NOT NULL,
  `serial_grd` int(11) NOT NULL,
  `relationship_stg` enum('PARENT','SIBLING','GRANDPARENT','AUNT_UNCLE','OTHER') NOT NULL,
  `legal_guardian_stg` enum('YES','NO') NOT NULL DEFAULT 'NO',
  `status_stg` enum('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  PRIMARY KEY (`serial_std`,`serial_grd`),
  KEY `fk_is_guardian` (`serial_grd`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Volcar la base de datos para la tabla `student_guardian`
--


-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `student_optional_subject`
--

CREATE TABLE IF NOT EXISTS `student_optional_subject` (
  `serial_osb` int(11) NOT NULL,
  `serial_std` int(11) NOT NULL,
  `serial_prd` int(11) NOT NULL,
  `serial_lop` int(11) NOT NULL,
  `status_sos` enum('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  PRIMARY KEY (`serial_osb`,`serial_std`,`serial_prd`,`serial_lop`),
  KEY `fk_relationship_109` (`serial_std`),
  KEY `fk_relationship_246` (`serial_prd`,`serial_lop`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Volcar la base de datos para la tabla `student_optional_subject`
--


-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `student_subject_period_average`
--

CREATE TABLE IF NOT EXISTS `student_subject_period_average` (
  `serial_ssa` int(11) NOT NULL AUTO_INCREMENT,
  `stu_serial_ssa` int(11) DEFAULT NULL,
  `serial_lop` int(11) DEFAULT NULL,
  `serial_sbc` int(11) DEFAULT NULL,
  `serial_prd` int(11) DEFAULT NULL,
  `serial_std` int(11) DEFAULT NULL,
  `serial_spt` int(11) DEFAULT NULL,
  `serial_lvl` int(11) DEFAULT NULL,
  `serial_scy` int(11) DEFAULT NULL,
  `serial_set` int(11) DEFAULT NULL,
  `average_ssa` decimal(5,2) DEFAULT NULL,
  PRIMARY KEY (`serial_ssa`),
  KEY `fk_relationship_248` (`stu_serial_ssa`),
  KEY `fk_relationship_250` (`serial_sbc`),
  KEY `fk_relationship_251` (`serial_lop`),
  KEY `fk_relationship_252` (`serial_std`,`serial_spt`,`serial_lvl`,`serial_scy`,`serial_set`),
  KEY `fk_relationship_253` (`serial_prd`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

--
-- Volcar la base de datos para la tabla `student_subject_period_average`
--


-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `student_year`
--

CREATE TABLE IF NOT EXISTS `student_year` (
  `folio_number_sty` varchar(20) DEFAULT NULL,
  `matricule_number_sty` varchar(20) DEFAULT NULL,
  `is_listener_sty` enum('YES','NO') NOT NULL DEFAULT 'NO',
  `serial_std` int(11) NOT NULL,
  `serial_spt` int(11) NOT NULL,
  `serial_lvl` int(11) NOT NULL,
  `serial_scy` int(11) NOT NULL,
  `serial_set` int(11) NOT NULL,
  `status_sty` enum('ACCEPTED','REGISTERED','MATRICULE','RETIRED') NOT NULL,
  PRIMARY KEY (`serial_std`,`serial_spt`,`serial_lvl`,`serial_scy`,`serial_set`),
  KEY `fk_relationship_75` (`serial_spt`,`serial_lvl`,`serial_scy`,`serial_set`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Volcar la base de datos para la tabla `student_year`
--


-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `student_year_debts`
--

CREATE TABLE IF NOT EXISTS `student_year_debts` (
  `serial_deb` int(11) NOT NULL AUTO_INCREMENT,
  `serial_std` int(11) DEFAULT NULL,
  `serial_pct` int(11) DEFAULT NULL,
  `serial_spt` int(11) DEFAULT NULL,
  `serial_lvl` int(11) DEFAULT NULL,
  `serial_scy` int(11) DEFAULT NULL,
  `serial_set` int(11) DEFAULT NULL,
  `total_deb` decimal(9,2) NOT NULL,
  `total_taxes_deb` decimal(9,2) NOT NULL,
  `article_quantity_deb` decimal(5,2) DEFAULT NULL,
  `start_date_deb` date NOT NULL,
  `status_deb` enum('PENDING','PAID','INACTIVE') NOT NULL DEFAULT 'PENDING',
  PRIMARY KEY (`serial_deb`),
  KEY `fk_relationship_102` (`serial_pct`,`serial_spt`,`serial_lvl`,`serial_scy`,`serial_set`),
  KEY `fk_relationship_103` (`serial_std`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

--
-- Volcar la base de datos para la tabla `student_year_debts`
--


-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `student_year_discount`
--

CREATE TABLE IF NOT EXISTS `student_year_discount` (
  `serial_std` int(11) NOT NULL,
  `serial_ydc` int(11) NOT NULL,
  `status_syn` enum('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  PRIMARY KEY (`serial_std`,`serial_ydc`),
  KEY `fk_relationship_228` (`serial_ydc`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Volcar la base de datos para la tabla `student_year_discount`
--


-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `student_year_discount_values`
--

CREATE TABLE IF NOT EXISTS `student_year_discount_values` (
  `serial_sva` int(11) NOT NULL AUTO_INCREMENT,
  `serial_pct` int(11) DEFAULT NULL,
  `serial_std` int(11) DEFAULT NULL,
  `serial_ydc` int(11) DEFAULT NULL,
  `discount_month_ydv` char(3) NOT NULL,
  `value_ydv` decimal(6,2) NOT NULL,
  `comment_ydv` varchar(128) DEFAULT NULL,
  `status_ydv` enum('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  PRIMARY KEY (`serial_sva`),
  KEY `fk_relationship_230` (`serial_std`,`serial_ydc`),
  KEY `fk_relationship_235` (`serial_pct`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

--
-- Volcar la base de datos para la tabla `student_year_discount_values`
--


-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `student_year_document`
--

CREATE TABLE IF NOT EXISTS `student_year_document` (
  `serial_yqd` int(11) NOT NULL,
  `serial_std` int(11) NOT NULL,
  `serial_spt` int(11) NOT NULL,
  `serial_lvl` int(11) NOT NULL,
  `serial_scy` int(11) NOT NULL,
  `serial_set` int(11) NOT NULL,
  `delivered_syd` enum('YES','NO') NOT NULL DEFAULT 'NO',
  `filename_syd` varchar(1024) DEFAULT NULL,
  `status_syd` enum('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  PRIMARY KEY (`serial_yqd`,`serial_std`,`serial_spt`,`serial_lvl`,`serial_scy`,`serial_set`),
  KEY `fk_relationship_159` (`serial_std`,`serial_spt`,`serial_lvl`,`serial_scy`,`serial_set`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Volcar la base de datos para la tabla `student_year_document`
--


-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `student_year_special_grade`
--

CREATE TABLE IF NOT EXISTS `student_year_special_grade` (
  `serial_ysg` int(11) NOT NULL,
  `serial_std` int(11) NOT NULL,
  `serial_spt` int(11) NOT NULL,
  `serial_lvl` int(11) NOT NULL,
  `serial_scy` int(11) NOT NULL,
  `serial_set` int(11) NOT NULL,
  `grade_sys` decimal(6,2) DEFAULT NULL,
  `status_sys` enum('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  PRIMARY KEY (`serial_ysg`,`serial_std`,`serial_spt`,`serial_lvl`,`serial_scy`,`serial_set`),
  KEY `fk_relationship_180` (`serial_std`,`serial_spt`,`serial_lvl`,`serial_scy`,`serial_set`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Volcar la base de datos para la tabla `student_year_special_grade`
--


-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `subject`
--

CREATE TABLE IF NOT EXISTS `subject` (
  `serial_sbj` int(11) NOT NULL AUTO_INCREMENT,
  `serial_csb` int(11) DEFAULT NULL,
  `serial_spt` int(11) DEFAULT NULL,
  `serial_lvl` int(11) DEFAULT NULL,
  `serial_scy` int(11) DEFAULT NULL,
  `serial_set` int(11) DEFAULT NULL,
  `status_sbj` enum('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  PRIMARY KEY (`serial_sbj`),
  KEY `fk_subject_fo__level` (`serial_spt`,`serial_lvl`,`serial_scy`,`serial_set`),
  KEY `fk_year_subject` (`serial_csb`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=16 ;

--
-- Volcar la base de datos para la tabla `subject`
--

INSERT INTO `subject` (`serial_sbj`, `serial_csb`, `serial_spt`, `serial_lvl`, `serial_scy`, `serial_set`, `status_sbj`) VALUES
(1, 3, 1, 1, 1, 1, 'ACTIVE'),
(2, 4, 1, 1, 1, 1, 'ACTIVE'),
(3, 5, 1, 1, 1, 1, 'ACTIVE'),
(4, 1, 1, 2, 1, 1, 'ACTIVE'),
(5, 5, 1, 2, 1, 1, 'ACTIVE'),
(6, 1, 2, 3, 1, 2, 'ACTIVE'),
(7, 3, 2, 3, 1, 2, 'ACTIVE'),
(8, 4, 2, 3, 1, 2, 'ACTIVE'),
(9, 4, 2, 4, 1, 2, 'ACTIVE'),
(10, 5, 2, 4, 1, 2, 'ACTIVE'),
(11, 1, 4, 3, 1, 2, 'ACTIVE'),
(12, 5, 4, 3, 1, 2, 'ACTIVE'),
(13, 1, 4, 4, 1, 2, 'ACTIVE'),
(14, 3, 4, 4, 1, 2, 'ACTIVE'),
(15, 4, 4, 4, 1, 2, 'ACTIVE');

--
-- (Evento) desencadenante `subject`
--
DROP TRIGGER IF EXISTS `subject_trg`;
DELIMITER //
CREATE TRIGGER `subject_trg` BEFORE UPDATE ON `subject`
 FOR EACH ROW BEGIN
	/*change dependent course_subject status to INACTIVE 
	when a subject is changed to INACTIVE*/
	IF NEW.status_sbj = 'INACTIVE' THEN
	UPDATE subject_course sbc
	SET sbc.status_sbc = 'INACTIVE'
	WHERE sbc.serial_sbj = OLD.serial_sbj;
	END IF;
END
//
DELIMITER ;

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `subject_course`
--

CREATE TABLE IF NOT EXISTS `subject_course` (
  `serial_sbc` int(11) NOT NULL AUTO_INCREMENT,
  `serial_sbj` int(11) DEFAULT NULL,
  `serial_crs` int(11) DEFAULT NULL,
  `serial_aca` int(11) DEFAULT NULL,
  `serial_emp` int(11) DEFAULT NULL,
  `status_sbc` enum('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  `grade_type_sbc` enum('QUALITATIVE','QUANTITATIVE') NOT NULL DEFAULT 'QUANTITATIVE',
  `hours_week_sbc` int(11) NOT NULL,
  `hours_distribution_sbc` varchar(512) DEFAULT NULL,
  PRIMARY KEY (`serial_sbc`),
  KEY `fk_course_with_subject` (`serial_crs`),
  KEY `fk_is_teacher` (`serial_aca`,`serial_emp`),
  KEY `fk_subject_for_course` (`serial_sbj`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=31 ;

--
-- Volcar la base de datos para la tabla `subject_course`
--

INSERT INTO `subject_course` (`serial_sbc`, `serial_sbj`, `serial_crs`, `serial_aca`, `serial_emp`, `status_sbc`, `grade_type_sbc`, `hours_week_sbc`, `hours_distribution_sbc`) VALUES
(1, 3, 1, 1, 1, 'ACTIVE', 'QUANTITATIVE', 3, '1,1,1'),
(2, 3, 2, 1, 3, 'ACTIVE', 'QUANTITATIVE', 3, '2,1'),
(3, 1, 1, 3, 1, 'ACTIVE', 'QUANTITATIVE', 1, '1'),
(4, 1, 2, 3, 1, 'ACTIVE', 'QUANTITATIVE', 1, '1'),
(5, 2, 1, 3, 2, 'ACTIVE', 'QUANTITATIVE', 2, '1,1'),
(6, 2, 2, 3, 2, 'ACTIVE', 'QUANTITATIVE', 2, '2'),
(7, 4, 3, 3, 2, 'ACTIVE', 'QUANTITATIVE', 2, '2'),
(8, 4, 4, 3, 1, 'ACTIVE', 'QUANTITATIVE', 2, '1,1'),
(9, 5, 3, 1, 3, 'ACTIVE', 'QUANTITATIVE', 2, '1,1'),
(10, 5, 4, 1, 3, 'ACTIVE', 'QUANTITATIVE', 2, '2'),
(11, 6, 5, 3, 1, 'ACTIVE', 'QUANTITATIVE', 1, '1'),
(12, 6, 6, 3, 1, 'ACTIVE', 'QUANTITATIVE', 1, '1'),
(13, 7, 5, 3, 2, 'ACTIVE', 'QUANTITATIVE', 1, '1'),
(14, 7, 6, 3, 1, 'ACTIVE', 'QUANTITATIVE', 1, '1'),
(15, 8, 5, 3, 1, 'ACTIVE', 'QUANTITATIVE', 2, '1,1'),
(16, 8, 6, 3, 2, 'ACTIVE', 'QUANTITATIVE', 2, '2'),
(17, 10, 9, 1, 1, 'ACTIVE', 'QUANTITATIVE', 2, '1,1'),
(18, 10, 10, 1, 1, 'ACTIVE', 'QUANTITATIVE', 2, '2'),
(19, 9, 9, 3, 2, 'ACTIVE', 'QUANTITATIVE', 1, '1,1'),
(20, 9, 10, 3, 2, 'ACTIVE', 'QUANTITATIVE', 1, '1'),
(21, 11, 7, 3, 1, 'ACTIVE', 'QUANTITATIVE', 1, '1'),
(22, 11, 8, 3, 1, 'ACTIVE', 'QUANTITATIVE', 1, '1'),
(23, 12, 7, 1, 3, 'ACTIVE', 'QUANTITATIVE', 1, '1'),
(24, 12, 8, 1, 1, 'ACTIVE', 'QUANTITATIVE', 1, '1'),
(25, 13, 11, 3, 1, 'ACTIVE', 'QUANTITATIVE', 1, '1'),
(26, 13, 12, 3, 2, 'ACTIVE', 'QUANTITATIVE', 1, '1'),
(27, 14, 11, 3, 2, 'ACTIVE', 'QUANTITATIVE', 2, '2'),
(28, 14, 12, 3, 1, 'ACTIVE', 'QUANTITATIVE', 2, '1,1'),
(29, 15, 11, 3, 1, 'ACTIVE', 'QUANTITATIVE', 1, '1'),
(30, 15, 12, 3, 2, 'ACTIVE', 'QUANTITATIVE', 1, '1');

--
-- (Evento) desencadenante `subject_course`
--
DROP TRIGGER IF EXISTS `subject_course_trg`;
DELIMITER //
CREATE TRIGGER `subject_course_trg` BEFORE UPDATE ON `subject_course`
 FOR EACH ROW BEGIN
	/*change subject_course_period to INACTIVE if subject_course is INACTIVE*/
	IF NEW.status_sbc = 'INACTIVE' THEN
		UPDATE subject_course_period scp
		SET scp.status_scp = 'INACTIVE',
			scp.weight_scp = 0
		WHERE scp.serial_sbc = OLD.serial_sbc;
	END IF;
END
//
DELIMITER ;

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `subject_course_period`
--

CREATE TABLE IF NOT EXISTS `subject_course_period` (
  `serial_prd` int(11) NOT NULL,
  `serial_sbc` int(11) NOT NULL,
  `status_scp` enum('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  `weight_scp` decimal(5,2) DEFAULT NULL,
  PRIMARY KEY (`serial_prd`,`serial_sbc`),
  KEY `fk_relationship_72` (`serial_sbc`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Volcar la base de datos para la tabla `subject_course_period`
--

INSERT INTO `subject_course_period` (`serial_prd`, `serial_sbc`, `status_scp`, `weight_scp`) VALUES
(1, 1, 'ACTIVE', '100.00'),
(1, 2, 'ACTIVE', '100.00'),
(1, 3, 'ACTIVE', '20.00'),
(1, 4, 'ACTIVE', '80.00'),
(1, 5, 'ACTIVE', '80.00'),
(1, 6, 'ACTIVE', '20.00'),
(1, 7, 'ACTIVE', '100.00'),
(1, 8, 'ACTIVE', '100.00'),
(1, 9, 'ACTIVE', '100.00'),
(1, 10, 'ACTIVE', '100.00'),
(1, 11, 'ACTIVE', '100.00'),
(1, 12, 'ACTIVE', '100.00'),
(1, 13, 'ACTIVE', '30.00'),
(1, 14, 'ACTIVE', '30.00'),
(1, 15, 'ACTIVE', '70.00'),
(1, 16, 'ACTIVE', '70.00'),
(1, 17, 'ACTIVE', '100.00'),
(1, 18, 'ACTIVE', '100.00'),
(1, 19, 'ACTIVE', '100.00'),
(1, 20, 'ACTIVE', '100.00'),
(1, 21, 'ACTIVE', '100.00'),
(1, 22, 'ACTIVE', '100.00'),
(1, 23, 'ACTIVE', '100.00'),
(1, 24, 'ACTIVE', '100.00'),
(1, 25, 'ACTIVE', '100.00'),
(1, 26, 'ACTIVE', '100.00'),
(1, 27, 'ACTIVE', '60.00'),
(1, 28, 'ACTIVE', '100.00'),
(1, 29, 'ACTIVE', '40.00'),
(1, 30, 'ACTIVE', '0.00'),
(2, 1, 'ACTIVE', '100.00'),
(2, 2, 'ACTIVE', '100.00'),
(2, 3, 'ACTIVE', '80.00'),
(2, 4, 'ACTIVE', '20.00'),
(2, 5, 'ACTIVE', '20.00'),
(2, 6, 'ACTIVE', '80.00'),
(2, 7, 'ACTIVE', '100.00'),
(2, 8, 'ACTIVE', '100.00'),
(2, 9, 'ACTIVE', '100.00'),
(2, 10, 'ACTIVE', '100.00'),
(2, 11, 'ACTIVE', '100.00'),
(2, 12, 'ACTIVE', '100.00'),
(2, 13, 'ACTIVE', '50.00'),
(2, 14, 'ACTIVE', '40.00'),
(2, 15, 'ACTIVE', '50.00'),
(2, 16, 'ACTIVE', '60.00'),
(2, 17, 'ACTIVE', '100.00'),
(2, 18, 'ACTIVE', '100.00'),
(2, 19, 'ACTIVE', '100.00'),
(2, 20, 'ACTIVE', '100.00'),
(2, 21, 'ACTIVE', '100.00'),
(2, 22, 'ACTIVE', '100.00'),
(2, 23, 'ACTIVE', '100.00'),
(2, 24, 'ACTIVE', '100.00'),
(2, 25, 'ACTIVE', '100.00'),
(2, 26, 'ACTIVE', '100.00'),
(2, 27, 'ACTIVE', '40.00'),
(2, 28, 'ACTIVE', '0.00'),
(2, 29, 'ACTIVE', '60.00'),
(2, 30, 'ACTIVE', '100.00'),
(3, 1, 'ACTIVE', '100.00'),
(3, 2, 'ACTIVE', '100.00'),
(3, 3, 'ACTIVE', '20.00'),
(3, 4, 'ACTIVE', '80.00'),
(3, 5, 'ACTIVE', '80.00'),
(3, 6, 'ACTIVE', '20.00'),
(3, 7, 'ACTIVE', '100.00'),
(3, 8, 'ACTIVE', '100.00'),
(3, 9, 'ACTIVE', '100.00'),
(3, 10, 'ACTIVE', '100.00'),
(3, 11, 'ACTIVE', '100.00'),
(3, 12, 'ACTIVE', '100.00'),
(3, 13, 'ACTIVE', '30.00'),
(3, 14, 'ACTIVE', '50.00'),
(3, 15, 'ACTIVE', '70.00'),
(3, 16, 'ACTIVE', '50.00'),
(3, 17, 'ACTIVE', '100.00'),
(3, 18, 'ACTIVE', '100.00'),
(3, 19, 'ACTIVE', '100.00'),
(3, 20, 'ACTIVE', '100.00'),
(3, 21, 'ACTIVE', '100.00'),
(3, 22, 'ACTIVE', '100.00'),
(3, 23, 'ACTIVE', '100.00'),
(3, 24, 'ACTIVE', '100.00'),
(3, 25, 'ACTIVE', '100.00'),
(3, 26, 'ACTIVE', '100.00'),
(3, 27, 'ACTIVE', '30.00'),
(3, 28, 'ACTIVE', '100.00'),
(3, 29, 'ACTIVE', '70.00'),
(3, 30, 'ACTIVE', '0.00');

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `subject_course_retest`
--

CREATE TABLE IF NOT EXISTS `subject_course_retest` (
  `serial_scr` int(11) NOT NULL AUTO_INCREMENT,
  `serial_prd` int(11) DEFAULT NULL,
  `serial_sbc` int(11) DEFAULT NULL,
  `description_scr` text NOT NULL,
  `sent_date_scr` date NOT NULL,
  `retest_date_scr` date NOT NULL,
  `weight_scr` decimal(5,2) NOT NULL,
  `status_scr` enum('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  PRIMARY KEY (`serial_scr`),
  KEY `fk_relationship_201` (`serial_prd`,`serial_sbc`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

--
-- Volcar la base de datos para la tabla `subject_course_retest`
--


-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `subject_course_retest_student`
--

CREATE TABLE IF NOT EXISTS `subject_course_retest_student` (
  `serial_std` int(11) NOT NULL,
  `serial_scr` int(11) NOT NULL,
  `grade_srg` decimal(5,2) NOT NULL,
  `status_srg` enum('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  PRIMARY KEY (`serial_std`,`serial_scr`),
  KEY `fk_relationship_202` (`serial_scr`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Volcar la base de datos para la tabla `subject_course_retest_student`
--


-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `subject_level_restriction`
--

CREATE TABLE IF NOT EXISTS `subject_level_restriction` (
  `serial_hbg` int(11) DEFAULT NULL,
  `serial_sbc` int(11) DEFAULT NULL,
  `serial_lop` int(11) DEFAULT NULL,
  `status_slr` enum('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  KEY `fk_relationship_266` (`serial_lop`),
  KEY `fk_relationship_267` (`serial_sbc`),
  KEY `fk_relationship_268` (`serial_hbg`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Volcar la base de datos para la tabla `subject_level_restriction`
--

INSERT INTO `subject_level_restriction` (`serial_hbg`, `serial_sbc`, `serial_lop`, `status_slr`) VALUES
(1, 1, NULL, 'ACTIVE'),
(1, NULL, 5, 'ACTIVE'),
(1, NULL, 7, 'ACTIVE'),
(3, 28, NULL, 'ACTIVE');

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `subject_ministry_equivalence`
--

CREATE TABLE IF NOT EXISTS `subject_ministry_equivalence` (
  `serial_lop` int(11) DEFAULT NULL,
  `serial_emp` int(11) DEFAULT NULL,
  `serial_msb` int(11) DEFAULT NULL,
  `serial_csb` int(11) DEFAULT NULL,
  `serial_spt` int(11) DEFAULT NULL,
  `serial_lvl` int(11) DEFAULT NULL,
  `serial_scy` int(11) DEFAULT NULL,
  `serial_set` int(11) DEFAULT NULL,
  `status_sme` enum('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  KEY `fk_relationship_166` (`serial_msb`),
  KEY `fk_relationship_169` (`serial_emp`),
  KEY `fk_relationship_193` (`serial_csb`),
  KEY `fk_relationship_204` (`serial_spt`,`serial_lvl`,`serial_scy`,`serial_set`),
  KEY `fk_relationship_247` (`serial_lop`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Volcar la base de datos para la tabla `subject_ministry_equivalence`
--


-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `subject_period_activity_type`
--

CREATE TABLE IF NOT EXISTS `subject_period_activity_type` (
  `serial_att` int(11) NOT NULL,
  `serial_prd` int(11) NOT NULL,
  `serial_sbc` int(11) NOT NULL,
  `weight_spa` decimal(5,2) DEFAULT NULL,
  `due_min_days_spa` int(11) NOT NULL,
  `due_max_grade_spa` int(11) NOT NULL,
  `status_spa` enum('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  PRIMARY KEY (`serial_att`,`serial_prd`,`serial_sbc`),
  KEY `fk_relationship_84` (`serial_prd`,`serial_sbc`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Volcar la base de datos para la tabla `subject_period_activity_type`
--


-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `subject_retest_change_log`
--

CREATE TABLE IF NOT EXISTS `subject_retest_change_log` (
  `serial_usr` int(11) DEFAULT NULL,
  `serial_chr` int(11) DEFAULT NULL,
  `serial_std` int(11) DEFAULT NULL,
  `serial_scr` int(11) DEFAULT NULL,
  `change_date_srl` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `last_grade_srl` decimal(5,2) NOT NULL,
  `comment_srl` text,
  KEY `fk_relationship_212` (`serial_std`,`serial_scr`),
  KEY `fk_relationship_213` (`serial_chr`),
  KEY `fk_relationship_214` (`serial_usr`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Volcar la base de datos para la tabla `subject_retest_change_log`
--


-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `subject_year_activity_criteria_period`
--

CREATE TABLE IF NOT EXISTS `subject_year_activity_criteria_period` (
  `serial_att` int(11) NOT NULL,
  `serial_prd` int(11) NOT NULL,
  `serial_sbc` int(11) NOT NULL,
  `serial_sbj` int(11) NOT NULL,
  `serial_gct` int(11) NOT NULL,
  `status_acp` enum('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  PRIMARY KEY (`serial_att`,`serial_prd`,`serial_sbc`,`serial_sbj`,`serial_gct`),
  KEY `fk_relationship_87` (`serial_sbj`,`serial_gct`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Volcar la base de datos para la tabla `subject_year_activity_criteria_period`
--


-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `subject_year_grade_criteria`
--

CREATE TABLE IF NOT EXISTS `subject_year_grade_criteria` (
  `serial_sbj` int(11) NOT NULL,
  `serial_gct` int(11) NOT NULL,
  `status_sgc` enum('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  PRIMARY KEY (`serial_sbj`,`serial_gct`),
  KEY `fk_relationship_83` (`serial_gct`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Volcar la base de datos para la tabla `subject_year_grade_criteria`
--


-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `syllabus_files`
--

CREATE TABLE IF NOT EXISTS `syllabus_files` (
  `serial_syf` int(11) NOT NULL AUTO_INCREMENT,
  `serial_sbc` int(11) DEFAULT NULL,
  `serial_osb` int(11) DEFAULT NULL,
  `name_syf` varchar(256) NOT NULL,
  `description_syf` text NOT NULL,
  `filename_syf` varchar(128) NOT NULL,
  `status_syf` enum('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  PRIMARY KEY (`serial_syf`),
  KEY `fk_relationship_128` (`serial_osb`),
  KEY `fk_relationship_129` (`serial_sbc`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

--
-- Volcar la base de datos para la tabla `syllabus_files`
--


-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `syllabus_files_change_log`
--

CREATE TABLE IF NOT EXISTS `syllabus_files_change_log` (
  `serial_syf` int(11) DEFAULT NULL,
  `serial_usr` int(11) DEFAULT NULL,
  `change_date_scl` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `old_data_scl` text NOT NULL,
  KEY `fk_relationship_132` (`serial_syf`),
  KEY `fk_relationship_133` (`serial_usr`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Volcar la base de datos para la tabla `syllabus_files_change_log`
--


-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `taxes`
--

CREATE TABLE IF NOT EXISTS `taxes` (
  `serial_tax` int(11) NOT NULL AUTO_INCREMENT,
  `name_tax` varchar(256) NOT NULL,
  `description_tax` text NOT NULL,
  `status_tax` enum('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  PRIMARY KEY (`serial_tax`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

--
-- Volcar la base de datos para la tabla `taxes`
--


-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `teacher_degree`
--

CREATE TABLE IF NOT EXISTS `teacher_degree` (
  `serial_tdg` int(11) NOT NULL AUTO_INCREMENT,
  `name_tdg` varchar(255) NOT NULL,
  `status_tdg` enum('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  PRIMARY KEY (`serial_tdg`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=12 ;

--
-- Volcar la base de datos para la tabla `teacher_degree`
--

INSERT INTO `teacher_degree` (`serial_tdg`, `name_tdg`, `status_tdg`) VALUES
(1, 'Licenciado en Ciencias de la Educación', 'ACTIVE'),
(2, 'Profesores de segunda enseñanza', 'ACTIVE'),
(3, 'Otros Títulos en CCEE con 4 o más años de estudio', 'ACTIVE'),
(4, 'Profesores de Primaria(Ins.Pedagógico)', 'ACTIVE'),
(5, 'Tecnológico Educativo', 'ACTIVE'),
(6, 'Auxiliar Pedagógico', 'ACTIVE'),
(7, 'Asistente Pedagógico', 'ACTIVE'),
(8, 'Facilitador Pedagógico', 'ACTIVE'),
(9, 'Bachiller en Ciencias de la Educación', 'ACTIVE'),
(10, 'Normalista rural', 'ACTIVE'),
(11, 'Doctorado en ciencias de la educación', 'ACTIVE');

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `teacher_hours_year`
--

CREATE TABLE IF NOT EXISTS `teacher_hours_year` (
  `serial_thy` int(11) NOT NULL AUTO_INCREMENT,
  `serial_emp` int(11) DEFAULT NULL,
  `serial_scy` int(11) DEFAULT NULL,
  `start_hour_thy` time NOT NULL,
  `end_hour_thy` time NOT NULL,
  `status_thy` enum('ACTIVE','INACTIVE','ASSIGNED') NOT NULL DEFAULT 'ACTIVE',
  `labor_day_thy` enum('MONDAY','TUESDAY','WEDNESDAY','THURSDAY','FRIDAY','SATURDAY','SUNDAY') NOT NULL,
  PRIMARY KEY (`serial_thy`),
  KEY `fk_relationship_69` (`serial_emp`),
  KEY `fk_relationship_70` (`serial_scy`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=24 ;

--
-- Volcar la base de datos para la tabla `teacher_hours_year`
--

INSERT INTO `teacher_hours_year` (`serial_thy`, `serial_emp`, `serial_scy`, `start_hour_thy`, `end_hour_thy`, `status_thy`, `labor_day_thy`) VALUES
(1, 1, 1, '07:00:00', '09:00:00', 'ACTIVE', 'MONDAY'),
(2, 1, 1, '11:00:00', '13:00:00', 'ACTIVE', 'MONDAY'),
(3, 1, 1, '10:00:00', '13:00:00', 'ACTIVE', 'TUESDAY'),
(4, 1, 1, '08:00:00', '12:00:00', 'ACTIVE', 'WEDNESDAY'),
(5, 1, 1, '13:00:00', '14:00:00', 'ACTIVE', 'WEDNESDAY'),
(6, 1, 1, '07:00:00', '13:00:00', 'ACTIVE', 'THURSDAY'),
(7, 1, 1, '08:00:00', '10:00:00', 'ACTIVE', 'FRIDAY'),
(8, 1, 1, '11:00:00', '12:00:00', 'ACTIVE', 'FRIDAY'),
(9, 2, 1, '07:00:00', '09:00:00', 'ACTIVE', 'MONDAY'),
(10, 2, 1, '10:00:00', '12:00:00', 'ACTIVE', 'MONDAY'),
(11, 2, 1, '07:00:00', '12:00:00', 'ACTIVE', 'TUESDAY'),
(12, 2, 1, '08:00:00', '10:00:00', 'ACTIVE', 'WEDNESDAY'),
(13, 2, 1, '12:00:00', '14:00:00', 'ACTIVE', 'WEDNESDAY'),
(14, 2, 1, '10:00:00', '13:00:00', 'ACTIVE', 'THURSDAY'),
(15, 2, 1, '08:00:00', '12:00:00', 'ACTIVE', 'FRIDAY'),
(16, 3, 1, '07:00:00', '09:00:00', 'ACTIVE', 'MONDAY'),
(17, 3, 1, '12:00:00', '13:00:00', 'ACTIVE', 'MONDAY'),
(18, 3, 1, '07:00:00', '09:00:00', 'ACTIVE', 'TUESDAY'),
(19, 3, 1, '11:00:00', '13:00:00', 'ACTIVE', 'TUESDAY'),
(20, 3, 1, '09:00:00', '13:00:00', 'ACTIVE', 'WEDNESDAY'),
(21, 3, 1, '10:00:00', '13:00:00', 'ACTIVE', 'THURSDAY'),
(22, 3, 1, '07:00:00', '09:00:00', 'ACTIVE', 'FRIDAY'),
(23, 3, 1, '11:00:00', '14:00:00', 'ACTIVE', 'FRIDAY');

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `teacher_master_degree`
--

CREATE TABLE IF NOT EXISTS `teacher_master_degree` (
  `serial_tmd` int(11) NOT NULL AUTO_INCREMENT,
  `name_tmd` varchar(255) NOT NULL,
  `status_tmd` enum('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  PRIMARY KEY (`serial_tmd`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

--
-- Volcar la base de datos para la tabla `teacher_master_degree`
--

INSERT INTO `teacher_master_degree` (`serial_tmd`, `name_tmd`, `status_tmd`) VALUES
(1, 'Máster y/o Phd en ciencias de la educación', 'ACTIVE');

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `teacher_specialty`
--

CREATE TABLE IF NOT EXISTS `teacher_specialty` (
  `serial_tst` int(11) NOT NULL AUTO_INCREMENT,
  `name_tst` varchar(255) NOT NULL,
  `status_tst` enum('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  PRIMARY KEY (`serial_tst`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=93 ;

--
-- Volcar la base de datos para la tabla `teacher_specialty`
--

INSERT INTO `teacher_specialty` (`serial_tst`, `name_tst`, `status_tst`) VALUES
(1, 'Administración educativa', 'ACTIVE'),
(2, 'Artes aplicadas', 'ACTIVE'),
(3, 'Auxiliar de laboratorio de ciencias naturales y Biología', 'ACTIVE'),
(4, 'Ciencias exactas', 'ACTIVE'),
(5, 'Ciencias sociales', 'ACTIVE'),
(6, 'Comercio ya dministración', 'ACTIVE'),
(7, 'Comunicación educativa', 'ACTIVE'),
(8, 'Contabilidad y administración', 'ACTIVE'),
(9, 'Diseño y dibujo', 'ACTIVE'),
(10, 'Docencia y desarrollo comunitario', 'ACTIVE'),
(11, 'Docencia y gestión en educación básica', 'ACTIVE'),
(12, 'Ecoturismo', 'ACTIVE'),
(13, 'Educación artística', 'ACTIVE'),
(14, 'Educación primaria', 'ACTIVE'),
(15, 'Educación especial', 'ACTIVE'),
(16, 'Educación inicial', 'ACTIVE'),
(17, 'Educación parvularia', 'ACTIVE'),
(18, 'Educación superior', 'ACTIVE'),
(19, 'Electricidad', 'ACTIVE'),
(20, 'Filosofía y pedagogía', 'ACTIVE'),
(21, 'Físico matemáticas', 'ACTIVE'),
(22, 'Gerencia educativa', 'ACTIVE'),
(23, 'Idiomas', 'ACTIVE'),
(24, 'Informática', 'ACTIVE'),
(25, 'Informática y programación', 'ACTIVE'),
(26, 'Inglés y plurilingue', 'ACTIVE'),
(27, 'Laboratorio de ciencias naturales', 'ACTIVE'),
(28, 'Liderazgo educativo', 'ACTIVE'),
(29, 'Literatura y castellano', 'ACTIVE'),
(30, 'Mecánica industrial', 'ACTIVE'),
(31, 'Monitor de actividad física', 'ACTIVE'),
(32, 'Pedagogía y gerencia educativa', 'ACTIVE'),
(33, 'Promoción y educación para la salud', 'ACTIVE'),
(34, 'Psicología educativa y orientación vocacional', 'ACTIVE'),
(35, 'Psicología infantil', 'ACTIVE'),
(36, 'Psicopedagogía infantil', 'ACTIVE'),
(37, 'Químico biológicas', 'ACTIVE'),
(38, 'Supervisión y administración educativa', 'ACTIVE'),
(39, 'Turismo histórico y cultural', 'ACTIVE'),
(40, 'Administación', 'ACTIVE'),
(41, 'Informática', 'ACTIVE'),
(42, 'Comunicación', 'ACTIVE'),
(43, 'Agronomía', 'ACTIVE'),
(44, 'Medicina', 'ACTIVE'),
(45, 'Civil/arquitectura', 'ACTIVE'),
(46, 'Teología', 'ACTIVE'),
(47, 'Administración y supervisión educativa', 'ACTIVE'),
(48, 'Asignaturas secretariales', 'ACTIVE'),
(49, 'Bellas artes', 'ACTIVE'),
(50, 'Ciencias naturales', 'ACTIVE'),
(51, 'Ciencias socioeconómicas y políticas', 'ACTIVE'),
(52, 'Computación', 'ACTIVE'),
(53, 'Contabilidad', 'ACTIVE'),
(54, 'Cultura fisica', 'ACTIVE'),
(55, 'Docencia primaria', 'ACTIVE'),
(56, 'Docencia y gestión en bachillerato', 'ACTIVE'),
(57, 'Docencia y gestión en educación parvularia', 'ACTIVE'),
(58, 'Educación ambiental', 'ACTIVE'),
(59, 'Educación básica', 'ACTIVE'),
(60, 'Educación de adultos', 'ACTIVE'),
(61, 'Educación física,deportes y recreación', 'ACTIVE'),
(62, 'Educación intercultural bilingüe', 'ACTIVE'),
(63, 'Educación preescolar', 'ACTIVE'),
(64, 'Educación y estudios interculturales', 'ACTIVE'),
(65, 'Filosofía y ciencias psicosociales', 'ACTIVE'),
(66, 'Filosofía,sociología y economía', 'ACTIVE'),
(67, 'Geografía y ecología', 'ACTIVE'),
(68, 'Historia y geografía', 'ACTIVE'),
(69, 'Idiomas y lingüística', 'ACTIVE'),
(70, 'Informática educativa', 'ACTIVE'),
(71, 'Inglés', 'ACTIVE'),
(72, 'Investigación y planificación educativa', 'ACTIVE'),
(73, 'Lengua y literatura', 'ACTIVE'),
(74, 'Lingüística(inglés-francés,inglés-italiano,inglés-alemán)', 'ACTIVE'),
(75, 'Mecánica automotriz', 'ACTIVE'),
(76, 'Mercadotecnia y publicidad', 'ACTIVE'),
(77, 'Opciones prácticas', 'ACTIVE'),
(78, 'Planificación y diseño curricular', 'ACTIVE'),
(79, 'Psicología del adolescente y orientación', 'ACTIVE'),
(80, 'Psicología gerontológico', 'ACTIVE'),
(81, 'Psicología y pedagogía', 'ACTIVE'),
(82, 'Psicorehabilitación y educación especial', 'ACTIVE'),
(83, 'Secretariado en español', 'ACTIVE'),
(84, 'Teatro y arte dramatico', 'ACTIVE'),
(85, 'Otras especializaciones de títulos docentes', 'ACTIVE'),
(86, 'Economía', 'ACTIVE'),
(87, 'Derecho', 'ACTIVE'),
(88, 'Medicina veterinaria', 'ACTIVE'),
(89, 'Geología', 'ACTIVE'),
(90, 'Antropología', 'ACTIVE'),
(91, 'Otras ingenierías', 'ACTIVE'),
(92, 'Artes', 'ACTIVE');

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `university`
--

CREATE TABLE IF NOT EXISTS `university` (
  `serial_unv` int(11) NOT NULL AUTO_INCREMENT,
  `name_unv` varchar(255) NOT NULL,
  `status_unv` enum('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  PRIMARY KEY (`serial_unv`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=19 ;

--
-- Volcar la base de datos para la tabla `university`
--

INSERT INTO `university` (`serial_unv`, `name_unv`, `status_unv`) VALUES
(1, 'Escuela Politécnica del Ejército', 'ACTIVE'),
(2, 'Escuela Politécnica Nacional', 'ACTIVE'),
(3, 'Universidad Central del Ecuador', 'ACTIVE'),
(4, 'Universidad Tecnológica Equinoccial', 'ACTIVE'),
(5, 'Escuela Politécnica Javeriana', 'ACTIVE'),
(6, 'Universidad Autónoma de Quito', 'ACTIVE'),
(7, 'Universidad Del Pacífico', 'ACTIVE'),
(8, 'Universidad Tecnológica America', 'ACTIVE'),
(9, 'Universidad Tecnológica Israel', 'ACTIVE'),
(10, 'Universidad De Especialidades Turisticas', 'ACTIVE'),
(11, 'Universidad de las Americas', 'ACTIVE'),
(12, 'Universidad Internacional del Ecuador', 'ACTIVE'),
(13, 'Universidad Internacional SEK', 'ACTIVE'),
(14, 'Universidad San Francisco de Quito', 'ACTIVE'),
(15, 'Universidad Politécnica Salesiana', 'ACTIVE'),
(16, 'Pontificia Universidad Catolica del Ecuador', 'ACTIVE'),
(17, 'Universidad Tecnológica Indoamérica', 'ACTIVE'),
(18, 'Otra', 'ACTIVE');

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `user`
--

CREATE TABLE IF NOT EXISTS `user` (
  `serial_usr` int(11) NOT NULL AUTO_INCREMENT,
  `serial_emp` int(11) DEFAULT NULL,
  `serial_grd` int(11) DEFAULT NULL,
  `serial_std` int(11) DEFAULT NULL,
  `username_usr` varchar(150) NOT NULL,
  `password_usr` varchar(300) DEFAULT NULL,
  `photo_usr` varchar(1024) DEFAULT NULL,
  `status_usr` enum('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  PRIMARY KEY (`serial_usr`),
  KEY `fk_employee_user2` (`serial_emp`),
  KEY `fk_guardian_user2` (`serial_grd`),
  KEY `fk_student_user2` (`serial_std`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

--
-- Volcar la base de datos para la tabla `user`
--

INSERT INTO `user` (`serial_usr`, `serial_emp`, `serial_grd`, `serial_std`, `username_usr`, `password_usr`, `photo_usr`, `status_usr`) VALUES
(1, NULL, NULL, NULL, 'admin', 'e10adc3949ba59abbe56e057f20f883e', NULL, 'ACTIVE'),
(2, 2, 1, NULL, 'padre', 'e10adc3949ba59abbe56e057f20f883e', NULL, 'ACTIVE'),
(3, 1, NULL, NULL, 'machicai', NULL, '462bf9.jpg', 'ACTIVE'),
(4, 3, NULL, NULL, 'maherdoi', NULL, NULL, 'ACTIVE');

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `user_profile`
--

CREATE TABLE IF NOT EXISTS `user_profile` (
  `serial_usr` int(11) NOT NULL,
  `serial_prf` int(11) NOT NULL,
  `status_upr` enum('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  PRIMARY KEY (`serial_usr`,`serial_prf`),
  KEY `fk_for_user` (`serial_prf`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Volcar la base de datos para la tabla `user_profile`
--

INSERT INTO `user_profile` (`serial_usr`, `serial_prf`, `status_upr`) VALUES
(1, 1, 'ACTIVE'),
(2, 2, 'ACTIVE'),
(2, 4, 'ACTIVE'),
(3, 4, 'ACTIVE'),
(4, 4, 'ACTIVE');

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `use_destination`
--

CREATE TABLE IF NOT EXISTS `use_destination` (
  `serial_udt` int(11) NOT NULL AUTO_INCREMENT,
  `name_udt` varchar(200) NOT NULL,
  `description_udt` text NOT NULL,
  `status_udt` enum('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  PRIMARY KEY (`serial_udt`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

--
-- Volcar la base de datos para la tabla `use_destination`
--



-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `visits_medical_center`
--

CREATE TABLE IF NOT EXISTS `visits_medical_center` (
  `serial_vmc` int(11) NOT NULL AUTO_INCREMENT,
  `serial_mdf` int(11) DEFAULT NULL,
  `date_vmc` date NOT NULL,
  `reason_vmc` text NOT NULL,
  `diagnosis_vmc` text NOT NULL,
  `filename_att_vmc` varchar(100) DEFAULT NULL,
  `attachment_vmc` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`serial_vmc`),
  KEY `fk_relationship_197` (`serial_mdf`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

--
-- Volcar la base de datos para la tabla `visits_medical_center`
--


-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `web_pre_registration`
--

CREATE TABLE IF NOT EXISTS `web_pre_registration` (
  `serial_wpr` int(11) NOT NULL AUTO_INCREMENT,
  `serial_wus` int(11) DEFAULT NULL,
  `serial_nnt` int(11) DEFAULT NULL,
  `serial_dgr` int(11) DEFAULT NULL,
  `serial_ste` int(11) DEFAULT NULL,
  `id_wpr` varchar(10) DEFAULT NULL,
  `serial_spt` int(11) DEFAULT NULL,
  `serial_lvl` int(11) DEFAULT NULL,
  `serial_scy` int(11) DEFAULT NULL,
  `serial_set` int(11) DEFAULT NULL,
  `first_name_wpr` varchar(512) NOT NULL,
  `last_name_wpr` varchar(512) NOT NULL,
  `birthdate_wpr` date NOT NULL,
  `gender_wpr` enum('MALE','FEMALE') NOT NULL,
  `address_wpr` text NOT NULL,
  `phone_wpr` varchar(30) NOT NULL,
  `first_name_father_wpr` varchar(100) DEFAULT NULL,
  `last_name_father_wpr` varchar(100) DEFAULT NULL,
  `first_name_mother_wpr` varchar(100) DEFAULT NULL,
  `last_name_mother_wpr` varchar(100) DEFAULT NULL,
  `city_birth_wpr` varchar(100) NOT NULL,
  `blood_type_wpr` varchar(10) NOT NULL,
  `allergies_wpr` text,
  `lives_with_wpr` enum('PARENT','SIBLING','GRANDPARENT','AUNT_UNCLE','OTHER') NOT NULL,
  `photo_wpr` varchar(1024) DEFAULT NULL,
  `email_wpr` varchar(256) DEFAULT NULL,
  `rep_first_name_wpr` varchar(100) NOT NULL,
  `rep_last_name_wpr` varchar(100) NOT NULL,
  `rep_relationship_wpr` enum('PARENT','SIBLING','GRANDPARENT','AUNT_UNCLE','OTHER') NOT NULL,
  `rep_birthdate_wpr` date NOT NULL,
  `rep_city_birth_wpr` varchar(100) NOT NULL,
  `rep_marital_status_wpr` enum('SINGLE','MARRIED','DIVORCED','WIDOWED','UNMARRIED PARTNERS') NOT NULL,
  `rep_occupation_wpr` varchar(100) NOT NULL,
  `rep_address_wpr` text NOT NULL,
  `rep_phone_wpr` varchar(13) NOT NULL,
  `rep_mobile_wpr` varchar(13) DEFAULT NULL,
  `rep_company_name_wpr` varchar(80) DEFAULT NULL,
  `rep_company_activity_wpr` text,
  `rep_company_phone_wpr` varchar(13) DEFAULT NULL,
  `rep_photo_wpr` varchar(1024) DEFAULT NULL,
  `rep_gender_wpr` enum('MALE','FEMALE') NOT NULL,
  `rep_id_wpr` varchar(13) NOT NULL,
  `rep_email_wpr` varchar(256) DEFAULT NULL,
  `submitted_docs_wpr` enum('YES','NO') NOT NULL DEFAULT 'NO',
  `pre_approved_wpr` enum('YES','NO','PENDING') NOT NULL DEFAULT 'PENDING',
  `pre_approved_comment_wpr` text,
  `approved_wpr` enum('YES','NO','PENDING') NOT NULL DEFAULT 'PENDING',
  `approved_comment_wpr` text,
  PRIMARY KEY (`serial_wpr`),
  KEY `fk_relationship_205` (`serial_dgr`),
  KEY `fk_relationship_206` (`serial_nnt`),
  KEY `fk_relationship_207` (`serial_spt`,`serial_lvl`,`serial_scy`,`serial_set`),
  KEY `fk_relationship_208` (`serial_wus`),
  KEY `fk_relationship_242` (`serial_ste`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

--
-- Volcar la base de datos para la tabla `web_pre_registration`
--


-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `web_user`
--

CREATE TABLE IF NOT EXISTS `web_user` (
  `serial_wus` int(11) NOT NULL AUTO_INCREMENT,
  `id_wus` varchar(13) NOT NULL,
  `first_name_wus` varchar(512) NOT NULL,
  `last_name_wus` varchar(512) NOT NULL,
  `birthdate_wus` date NOT NULL,
  `gender_wus` enum('MALE','FEMALE') NOT NULL,
  `address_wus` text NOT NULL,
  `phone_wus` varchar(30) NOT NULL,
  `status_wus` enum('ACTIVE','INACTIVE','PENDING','BLOCKED') NOT NULL DEFAULT 'PENDING',
  `occupation_wus` varchar(50) DEFAULT NULL,
  `company_name_wus` varchar(100) DEFAULT NULL,
  `company_activity_wus` varchar(80) DEFAULT NULL,
  `company_phone_wus` varchar(13) DEFAULT NULL,
  `email_wus` varchar(128) NOT NULL,
  `password_wus` varchar(256) NOT NULL,
  `authentication_key_wus` varchar(1024) NOT NULL,
  PRIMARY KEY (`serial_wus`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

--
-- Volcar la base de datos para la tabla `web_user`
--


-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `year_concept_taxes`
--

CREATE TABLE IF NOT EXISTS `year_concept_taxes` (
  `serial_tax` int(11) DEFAULT NULL,
  `value_yct` decimal(5,2) DEFAULT NULL,
  `status_yct` enum('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  `serial_pct` int(11) DEFAULT NULL,
  `serial_spt` int(11) DEFAULT NULL,
  `serial_lvl` int(11) DEFAULT NULL,
  `serial_scy` int(11) DEFAULT NULL,
  `serial_set` int(11) DEFAULT NULL,
  KEY `fk_relationship_93` (`serial_tax`),
  KEY `fk_relationship_94` (`serial_pct`,`serial_spt`,`serial_lvl`,`serial_scy`,`serial_set`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Volcar la base de datos para la tabla `year_concept_taxes`
--


-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `year_discount`
--

CREATE TABLE IF NOT EXISTS `year_discount` (
  `serial_ydc` int(11) NOT NULL AUTO_INCREMENT,
  `serial_dsc` int(11) DEFAULT NULL,
  `serial_scy` int(11) DEFAULT NULL,
  `type_ydc` enum('PERCENTAGE','FIXED') NOT NULL,
  `value_ydc` decimal(6,2) NOT NULL,
  `days_before_limit_ydc` int(11) DEFAULT NULL,
  `status_ydc` enum('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  PRIMARY KEY (`serial_ydc`),
  KEY `fk_relationship_224` (`serial_scy`),
  KEY `fk_relationship_225` (`serial_dsc`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

--
-- Volcar la base de datos para la tabla `year_discount`
--


-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `year_grade_subject`
--

CREATE TABLE IF NOT EXISTS `year_grade_subject` (
  `serial_ygs` int(11) NOT NULL AUTO_INCREMENT,
  `serial_scy` int(11) DEFAULT NULL,
  `serial_csb` int(11) DEFAULT NULL,
  `status_ygs` enum('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  PRIMARY KEY (`serial_ygs`),
  KEY `fk_relationship_174` (`serial_csb`),
  KEY `fk_relationship_175` (`serial_scy`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

--
-- Volcar la base de datos para la tabla `year_grade_subject`
--


--
-- (Evento) desencadenante `year_grade_subject`
--
DROP TRIGGER IF EXISTS `year_grade_subject_trg`;
DELIMITER //
CREATE TRIGGER `year_grade_subject_trg` BEFORE UPDATE ON `year_grade_subject`
 FOR EACH ROW BEGIN
	/*change grade_subject_score to INACTIVE if year_grade_subject is INACTIVE*/
	IF NEW.status_ygs LIKE '%INACTIVE%' THEN
		UPDATE grade_subject_score gss
		SET gss.status_gss = 'INACTIVE'
		WHERE gss.serial_ygs = OLD.serial_ygs;
	END IF;
END
//
DELIMITER ;

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `year_level_specialty_payment_concept`
--

CREATE TABLE IF NOT EXISTS `year_level_specialty_payment_concept` (
  `serial_art` int(11) DEFAULT NULL,
  `serial_pct` int(11) NOT NULL,
  `serial_spt` int(11) NOT NULL,
  `serial_lvl` int(11) NOT NULL,
  `serial_scy` int(11) NOT NULL,
  `serial_set` int(11) NOT NULL,
  `value_ypc` decimal(9,2) NOT NULL,
  `frequency_ypc` enum('ANNUAL','MONTHLY') NOT NULL,
  `repetitions_number_ypc` int(11) NOT NULL,
  `enabling_concept_ypc` enum('YES','NO') NOT NULL DEFAULT 'NO',
  `mandatory_ypc` enum('YES','NO') NOT NULL DEFAULT 'NO',
  `student_type_ypc` enum('NEW','OLD','BOTH') NOT NULL DEFAULT 'BOTH',
  PRIMARY KEY (`serial_pct`,`serial_spt`,`serial_lvl`,`serial_scy`,`serial_set`),
  KEY `fk_relationship_90` (`serial_spt`,`serial_lvl`,`serial_scy`,`serial_set`),
  KEY `fk_relationship_92` (`serial_art`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Volcar la base de datos para la tabla `year_level_specialty_payment_concept`
--


-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `year_pre_registration_document`
--

CREATE TABLE IF NOT EXISTS `year_pre_registration_document` (
  `serial_ypd` int(11) NOT NULL AUTO_INCREMENT,
  `serial_scy` int(11) DEFAULT NULL,
  `serial_qdc` int(11) DEFAULT NULL,
  `status_ypd` enum('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  PRIMARY KEY (`serial_ypd`),
  KEY `fk_relationship_261` (`serial_scy`),
  KEY `fk_relationship_262` (`serial_qdc`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

--
-- Volcar la base de datos para la tabla `year_pre_registration_document`
--


-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `year_qualifying_document`
--

CREATE TABLE IF NOT EXISTS `year_qualifying_document` (
  `serial_yqd` int(11) NOT NULL AUTO_INCREMENT,
  `serial_scy` int(11) DEFAULT NULL,
  `serial_qdc` int(11) DEFAULT NULL,
  `status_yqd` enum('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  PRIMARY KEY (`serial_yqd`),
  KEY `fk_relationship_157` (`serial_scy`),
  KEY `fk_relationship_158` (`serial_qdc`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

--
-- Volcar la base de datos para la tabla `year_qualifying_document`
--


-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `year_report_comment`
--

CREATE TABLE IF NOT EXISTS `year_report_comment` (
  `serial_yrc` int(11) NOT NULL AUTO_INCREMENT,
  `serial_scy` int(11) DEFAULT NULL,
  `serial_std` int(11) DEFAULT NULL,
  `report_type_yrc` enum('MIN_PERIOD_GRADE','MIN_FINAL_GRADE','MIN_GRADE_SCORE','MIN_RESIT_GRADE') NOT NULL,
  `comment_yrc` text NOT NULL,
  PRIMARY KEY (`serial_yrc`),
  KEY `fk_relationship_259` (`serial_std`),
  KEY `fk_relationship_260` (`serial_scy`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

--
-- Volcar la base de datos para la tabla `year_report_comment`
--


-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `year_resit_exam_ranges`
--

CREATE TABLE IF NOT EXISTS `year_resit_exam_ranges` (
  `serial_yre` int(11) NOT NULL AUTO_INCREMENT,
  `serial_scy` int(11) DEFAULT NULL,
  `lower_limit_yre` decimal(5,2) NOT NULL,
  `higher_limit_yre` decimal(5,2) NOT NULL,
  `required_grade_yre` decimal(5,2) NOT NULL,
  `type_yre` enum('SCHOOL','MINISTRY') NOT NULL DEFAULT 'SCHOOL',
  `status_yre` enum('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  PRIMARY KEY (`serial_yre`),
  KEY `fk_relationship_162` (`serial_scy`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

--
-- Volcar la base de datos para la tabla `year_resit_exam_ranges`
--


-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `year_special_grade`
--

CREATE TABLE IF NOT EXISTS `year_special_grade` (
  `serial_ysg` int(11) NOT NULL AUTO_INCREMENT,
  `serial_scy` int(11) DEFAULT NULL,
  `name_ysg` varchar(256) NOT NULL,
  `description_ysg` text NOT NULL,
  `status_ysg` enum('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  PRIMARY KEY (`serial_ysg`),
  KEY `fk_relationship_178` (`serial_scy`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

--
-- Volcar la base de datos para la tabla `year_special_grade`
--


--
-- (Evento) desencadenante `year_special_grade`
--
DROP TRIGGER IF EXISTS `year_special_grade_trg`;
DELIMITER //
CREATE TRIGGER `year_special_grade_trg` BEFORE UPDATE ON `year_special_grade`
 FOR EACH ROW BEGIN
	/*change student_year_special_grade to INACTIVE if year_special_grade is INACTIVE*/
	IF NEW.status_ysg LIKE '%INACTIVE%' THEN
		UPDATE student_year_special_grade sys
		SET sys.status_sys = 'INACTIVE'
		WHERE sys.serial_ysg = OLD.serial_ysg;
	END IF;
END
//
DELIMITER ;

-- --------------------------------------------------------

--
-- Estructura de tabla para la tabla `year_subject_classroom`
--

CREATE TABLE IF NOT EXISTS `year_subject_classroom` (
  `serial_ysc` int(11) NOT NULL AUTO_INCREMENT,
  `serial_sbc` int(11) DEFAULT NULL,
  `serial_clr` int(11) DEFAULT NULL,
  `serial_osb` int(11) DEFAULT NULL,
  `status_ysc` enum('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  PRIMARY KEY (`serial_ysc`),
  KEY `fk_relationship_163` (`serial_clr`),
  KEY `fk_relationship_164` (`serial_sbc`),
  KEY `fk_relationship_165` (`serial_osb`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

--
-- Volcar la base de datos para la tabla `year_subject_classroom`
--

--
-- Estructura de tabla para la tabla `credits_detail`
--

CREATE TABLE IF NOT EXISTS `credits_detail` (
  `serial_cdt` int(11) NOT NULL AUTO_INCREMENT,
  `serial_hrs` int(11) DEFAULT NULL,
  `start_at_cdt` time NOT NULL,
  `end_at_cdt` time NOT NULL,
  `status_cdt` enum('ACTIVE','INACTIVE') NOT NULL DEFAULT 'ACTIVE',
  PRIMARY KEY (`serial_cdt`),
  KEY `fk_relationship_269` (`serial_hrs`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;

--
-- Volcar la base de datos para la tabla `credits_detail`
--

INSERT INTO `credits_detail` (`serial_cdt`, `serial_hrs`, `start_at_cdt`, `end_at_cdt`, `status_cdt`) VALUES
(1, 1, '07:00:00', '08:00:00', 'ACTIVE'),
(2, 1, '08:00:00', '09:00:00', 'ACTIVE'),
(3, 3, '09:30:00', '10:45:00', 'ACTIVE'),
(4, 3, '10:45:00', '11:15:00', 'ACTIVE'),
(5, 3, '11:15:00', '12:00:00', 'ACTIVE');


--
-- Filtros para las tablas descargadas (dump)
--

--
-- Filtros para la tabla `credits_detail`
--
ALTER TABLE `credits_detail`
  ADD CONSTRAINT `fk_relationship_269` FOREIGN KEY (`serial_hrs`) REFERENCES `hour` (`serial_hrs`);

--
-- Filtros para la tabla `access_log`
--
ALTER TABLE `access_log`
  ADD CONSTRAINT `fk_relationship_143` FOREIGN KEY (`serial_usr`, `serial_prf`) REFERENCES `user_profile` (`serial_usr`, `serial_prf`);

--
-- Filtros para la tabla `activity_applied_criterias`
--
ALTER TABLE `activity_applied_criterias`
  ADD CONSTRAINT `fk_activity_header` FOREIGN KEY (`serial_act`) REFERENCES `activity` (`serial_act`),
  ADD CONSTRAINT `fk_relationship_95` FOREIGN KEY (`serial_att`, `serial_prd`, `serial_sbc`, `serial_sbj`, `serial_gct`) REFERENCES `subject_year_activity_criteria_period` (`serial_att`, `serial_prd`, `serial_sbc`, `serial_sbj`, `serial_gct`);

--
-- Filtros para la tabla `activity_attachment`
--
ALTER TABLE `activity_attachment`
  ADD CONSTRAINT `fk_relationship_96` FOREIGN KEY (`serial_act`) REFERENCES `activity` (`serial_act`);

--
-- Filtros para la tabla `activity_policy_violations`
--
ALTER TABLE `activity_policy_violations`
  ADD CONSTRAINT `fk_relationship_146` FOREIGN KEY (`serial_act`) REFERENCES `activity` (`serial_act`),
  ADD CONSTRAINT `fk_relationship_147` FOREIGN KEY (`serial_opa`) REFERENCES `opt_activity` (`serial_opa`),
  ADD CONSTRAINT `fk_relationship_148` FOREIGN KEY (`serial_emp`) REFERENCES `employee` (`serial_emp`);

--
-- Filtros para la tabla `allowed_pages_by_process`
--
ALTER TABLE `allowed_pages_by_process`
  ADD CONSTRAINT `fk_child_pages` FOREIGN KEY (`serial_prc`) REFERENCES `process` (`serial_prc`);

--
-- Filtros para la tabla `authority`
--
ALTER TABLE `authority`
  ADD CONSTRAINT `fk_has_charge` FOREIGN KEY (`serial_atc`) REFERENCES `authority_charge` (`serial_atc`);

--
-- Filtros para la tabla `bus_shift_year`
--
ALTER TABLE `bus_shift_year`
  ADD CONSTRAINT `fk_relationship_76` FOREIGN KEY (`serial_scy`) REFERENCES `school_year` (`serial_scy`),
  ADD CONSTRAINT `fk_relationship_77` FOREIGN KEY (`serial_bdr`) REFERENCES `bus_driver` (`serial_bdr`),
  ADD CONSTRAINT `fk_relationship_78` FOREIGN KEY (`serial_bus`) REFERENCES `bus` (`serial_bus`),
  ADD CONSTRAINT `fk_relationship_79` FOREIGN KEY (`serial_rot`) REFERENCES `route` (`serial_rot`),
  ADD CONSTRAINT `fk_relationship_80` FOREIGN KEY (`serial_sft`) REFERENCES `shift` (`serial_sft`),
  ADD CONSTRAINT `fk_relationship_81` FOREIGN KEY (`bus_serial_bdr`) REFERENCES `bus_driver` (`serial_bdr`);

--
-- Filtros para la tabla `bus_year_user`
--
ALTER TABLE `bus_year_user`
  ADD CONSTRAINT `fk_relationship_97` FOREIGN KEY (`serial_rot`, `serial_scy`, `serial_bus`, `serial_sft`) REFERENCES `bus_shift_year` (`serial_rot`, `serial_scy`, `serial_bus`, `serial_sft`),
  ADD CONSTRAINT `fk_relationship_98` FOREIGN KEY (`serial_usr`) REFERENCES `user` (`serial_usr`);

--
-- Filtros para la tabla `cat_subject`
--
ALTER TABLE `cat_subject`
  ADD CONSTRAINT `fk_child_parent` FOREIGN KEY (`cat_serial_csb`) REFERENCES `cat_subject` (`serial_csb`),
  ADD CONSTRAINT `fk_relationship_62` FOREIGN KEY (`serial_aca`) REFERENCES `academic_area` (`serial_aca`),
  ADD CONSTRAINT `fk_relationship_68` FOREIGN KEY (`serial_lng`) REFERENCES `language` (`serial_lng`);

--
-- Filtros para la tabla `course`
--
ALTER TABLE `course`
  ADD CONSTRAINT `fk_selected_class` FOREIGN KEY (`serial_clg`) REFERENCES `class_group` (`serial_clg`),
  ADD CONSTRAINT `fk_selected_level_speciality` FOREIGN KEY (`serial_spt`, `serial_lvl`, `serial_scy`, `serial_set`) REFERENCES `level_section_year_specialty` (`serial_spt`, `serial_lvl`, `serial_scy`, `serial_set`);

--
-- Filtros para la tabla `course_director`
--
ALTER TABLE `course_director`
  ADD CONSTRAINT `fk_relationship_144` FOREIGN KEY (`serial_emp`) REFERENCES `employee` (`serial_emp`),
  ADD CONSTRAINT `fk_relationship_145` FOREIGN KEY (`serial_crs`) REFERENCES `course` (`serial_crs`);

--
-- Filtros para la tabla `current_deferred`
--
ALTER TABLE `current_deferred`
  ADD CONSTRAINT `fk_relationship_170` FOREIGN KEY (`serial_deb`) REFERENCES `student_year_debts` (`serial_deb`);

--
-- Filtros para la tabla `discipline`
--
ALTER TABLE `discipline`
  ADD CONSTRAINT `fk_relationship_130` FOREIGN KEY (`serial_std`, `serial_spt`, `serial_lvl`, `serial_scy`, `serial_set`) REFERENCES `student_year` (`serial_std`, `serial_spt`, `serial_lvl`, `serial_scy`, `serial_set`),
  ADD CONSTRAINT `fk_relationship_131` FOREIGN KEY (`serial_prd`) REFERENCES `period` (`serial_prd`);

--
-- Filtros para la tabla `discount_payment_concept`
--
ALTER TABLE `discount_payment_concept`
  ADD CONSTRAINT `fk_relationship_226` FOREIGN KEY (`serial_ydc`) REFERENCES `year_discount` (`serial_ydc`),
  ADD CONSTRAINT `fk_relationship_227` FOREIGN KEY (`serial_pct`) REFERENCES `payment_concept` (`serial_pct`);

--
-- Filtros para la tabla `employee`
--
ALTER TABLE `employee`
  ADD CONSTRAINT `fk_employee_birth_country` FOREIGN KEY (`serial_cou`) REFERENCES `country` (`serial_cou`),
  ADD CONSTRAINT `fk_employee_birth_state` FOREIGN KEY (`serial_ste`) REFERENCES `state` (`serial_ste`),
  ADD CONSTRAINT `fk_employee_guardian2` FOREIGN KEY (`serial_grd`) REFERENCES `guardian` (`serial_grd`),
  ADD CONSTRAINT `fk_employee_user` FOREIGN KEY (`serial_usr`) REFERENCES `user` (`serial_usr`),
  ADD CONSTRAINT `fk_relationship_54` FOREIGN KEY (`serial_nnt`) REFERENCES `nationality` (`serial_nnt`),
  ADD CONSTRAINT `fk_relationship_55` FOREIGN KEY (`serial_tdg`) REFERENCES `teacher_degree` (`serial_tdg`),
  ADD CONSTRAINT `fk_relationship_56` FOREIGN KEY (`serial_tmd`) REFERENCES `teacher_master_degree` (`serial_tmd`),
  ADD CONSTRAINT `fk_relationship_57` FOREIGN KEY (`serial_tst`) REFERENCES `teacher_specialty` (`serial_tst`),
  ADD CONSTRAINT `fk_relationship_58` FOREIGN KEY (`serial_dgr`) REFERENCES `degree` (`serial_dgr`),
  ADD CONSTRAINT `fk_relationship_59` FOREIGN KEY (`serial_mdg`) REFERENCES `master_degree` (`serial_mdg`),
  ADD CONSTRAINT `fk_relationship_60` FOREIGN KEY (`serial_unv`) REFERENCES `university` (`serial_unv`);

--
-- Filtros para la tabla `employee_area`
--
ALTER TABLE `employee_area`
  ADD CONSTRAINT `fk_area_by_employee` FOREIGN KEY (`serial_aca`) REFERENCES `academic_area` (`serial_aca`),
  ADD CONSTRAINT `fk_employee_in_area` FOREIGN KEY (`serial_emp`) REFERENCES `employee` (`serial_emp`);

--
-- Filtros para la tabla `general_activity_attendees`
--
ALTER TABLE `general_activity_attendees`
  ADD CONSTRAINT `fk_relationship_139` FOREIGN KEY (`serial_scy`) REFERENCES `school_year` (`serial_scy`),
  ADD CONSTRAINT `fk_relationship_140` FOREIGN KEY (`serial_gac`) REFERENCES `general_activity` (`serial_gac`);

--
-- Filtros para la tabla `general_discipline_log`
--
ALTER TABLE `general_discipline_log`
  ADD CONSTRAINT `fk_relationship_151` FOREIGN KEY (`serial_usr`) REFERENCES `user` (`serial_usr`),
  ADD CONSTRAINT `fk_relationship_152` FOREIGN KEY (`serial_prd`, `serial_std`, `serial_spt`, `serial_lvl`, `serial_scy`, `serial_set`) REFERENCES `discipline` (`serial_prd`, `serial_std`, `serial_spt`, `serial_lvl`, `serial_scy`, `serial_set`),
  ADD CONSTRAINT `fk_relationship_153` FOREIGN KEY (`serial_chr`) REFERENCES `change_reason` (`serial_chr`);

--
-- Filtros para la tabla `grade_change_log`
--
ALTER TABLE `grade_change_log`
  ADD CONSTRAINT `fk_relationship_123` FOREIGN KEY (`serial_sbc`, `serial_att`, `serial_prd`, `serial_gct`, `serial_act`, `serial_std`, `serial_crs`) REFERENCES `student_criteria_grade` (`serial_sbc`, `serial_att`, `serial_prd`, `serial_gct`, `serial_act`, `serial_std`, `serial_crs`),
  ADD CONSTRAINT `fk_relationship_124` FOREIGN KEY (`serial_usr`) REFERENCES `user` (`serial_usr`),
  ADD CONSTRAINT `fk_relationship_150` FOREIGN KEY (`serial_chr`) REFERENCES `change_reason` (`serial_chr`);

--
-- Filtros para la tabla `grade_score_change_log`
--
ALTER TABLE `grade_score_change_log`
  ADD CONSTRAINT `fk_relationship_237` FOREIGN KEY (`serial_ygs`, `serial_std`) REFERENCES `grade_subject_score` (`serial_ygs`, `serial_std`),
  ADD CONSTRAINT `fk_relationship_238` FOREIGN KEY (`serial_chr`) REFERENCES `change_reason` (`serial_chr`),
  ADD CONSTRAINT `fk_relationship_239` FOREIGN KEY (`serial_usr`) REFERENCES `user` (`serial_usr`);

--
-- Filtros para la tabla `grade_subject_score`
--
ALTER TABLE `grade_subject_score`
  ADD CONSTRAINT `fk_relationship_176` FOREIGN KEY (`serial_ygs`) REFERENCES `year_grade_subject` (`serial_ygs`),
  ADD CONSTRAINT `fk_relationship_177` FOREIGN KEY (`serial_std`, `serial_spt`, `serial_lvl`, `serial_scy`, `serial_set`) REFERENCES `student_year` (`serial_std`, `serial_spt`, `serial_lvl`, `serial_scy`, `serial_set`);

--
-- Filtros para la tabla `group_hour_level`
--
ALTER TABLE `group_hour_level`
  ADD CONSTRAINT `fk_group_hour_for_level` FOREIGN KEY (`serial_gph`) REFERENCES `group_hour` (`serial_gph`),
  ADD CONSTRAINT `fk_levels_in_group_hour` FOREIGN KEY (`serial_lvl`, `serial_scy`, `serial_set`) REFERENCES `level_section_year` (`serial_lvl`, `serial_scy`, `serial_set`);

--
-- Filtros para la tabla `guardian`
--
ALTER TABLE `guardian`
  ADD CONSTRAINT `fk_employee_guardian` FOREIGN KEY (`serial_emp`) REFERENCES `employee` (`serial_emp`),
  ADD CONSTRAINT `fk_guardian_user` FOREIGN KEY (`serial_usr`) REFERENCES `user` (`serial_usr`);

--
-- Filtros para la tabla `hour_by_group`
--
ALTER TABLE `hour_by_group`
  ADD CONSTRAINT `fk_for_group` FOREIGN KEY (`serial_gph`) REFERENCES `group_hour` (`serial_gph`),
  ADD CONSTRAINT `fk_is_in_group` FOREIGN KEY (`serial_hrs`) REFERENCES `hour` (`serial_hrs`);

--
-- Filtros para la tabla `incident`
--
ALTER TABLE `incident`
  ADD CONSTRAINT `fk_relationship_194` FOREIGN KEY (`use_serial_usr`) REFERENCES `user` (`serial_usr`),
  ADD CONSTRAINT `fk_relationship_195` FOREIGN KEY (`serial_usr`) REFERENCES `user` (`serial_usr`);

--
-- Filtros para la tabla `income_stock`
--
ALTER TABLE `income_stock`
  ADD CONSTRAINT `fk_relationship_184` FOREIGN KEY (`serial_prv`) REFERENCES `provider` (`serial_prv`),
  ADD CONSTRAINT `fk_relationship_185` FOREIGN KEY (`serial_mti`) REFERENCES `maintenance_item` (`serial_mti`),
  ADD CONSTRAINT `fk_relationship_186` FOREIGN KEY (`serial_usr`) REFERENCES `user` (`serial_usr`);

--
-- Filtros para la tabla `invoice_detail`
--
ALTER TABLE `invoice_detail`
  ADD CONSTRAINT `fk_relationship_155` FOREIGN KEY (`serial_phy`) REFERENCES `invoice` (`serial_phy`),
  ADD CONSTRAINT `fk_relationship_156` FOREIGN KEY (`serial_deb`) REFERENCES `student_year_debts` (`serial_deb`),
  ADD CONSTRAINT `fk_relationship_171` FOREIGN KEY (`serial_cdf`) REFERENCES `current_deferred` (`serial_cdf`),
  ADD CONSTRAINT `fk_relationship_231` FOREIGN KEY (`serial_sva`) REFERENCES `student_year_discount_values` (`serial_sva`);

--
-- Filtros para la tabla `level_optional_group`
--
ALTER TABLE `level_optional_group`
  ADD CONSTRAINT `fk_relationship_243` FOREIGN KEY (`serial_spt`, `serial_lvl`, `serial_scy`, `serial_set`) REFERENCES `level_section_year_specialty` (`serial_spt`, `serial_lvl`, `serial_scy`, `serial_set`);

--
-- Filtros para la tabla `level_optional_group_detail`
--
ALTER TABLE `level_optional_group_detail`
  ADD CONSTRAINT `fk_relationship_244` FOREIGN KEY (`serial_lop`) REFERENCES `level_optional_group` (`serial_lop`),
  ADD CONSTRAINT `fk_relationship_245` FOREIGN KEY (`serial_prd`) REFERENCES `period` (`serial_prd`);

--
-- Filtros para la tabla `level_section_year`
--
ALTER TABLE `level_section_year`
  ADD CONSTRAINT `fk_level_in_section` FOREIGN KEY (`serial_lvl`) REFERENCES `level` (`serial_lvl`),
  ADD CONSTRAINT `fk_period_group_level` FOREIGN KEY (`serial_pgy`) REFERENCES `period_group_year` (`serial_pgy`),
  ADD CONSTRAINT `fk_section_for_level` FOREIGN KEY (`serial_scy`, `serial_set`) REFERENCES `section_by_year` (`serial_scy`, `serial_set`);

--
-- Filtros para la tabla `level_section_year_specialty`
--
ALTER TABLE `level_section_year_specialty`
  ADD CONSTRAINT `fk_selected_level` FOREIGN KEY (`serial_lvl`, `serial_scy`, `serial_set`) REFERENCES `level_section_year` (`serial_lvl`, `serial_scy`, `serial_set`),
  ADD CONSTRAINT `fk_selected_specialty` FOREIGN KEY (`serial_spt`) REFERENCES `specialty` (`serial_spt`);

--
-- Filtros para la tabla `maintenance_item`
--
ALTER TABLE `maintenance_item`
  ADD CONSTRAINT `fk_relationship_183` FOREIGN KEY (`serial_ict`) REFERENCES `item_category` (`serial_ict`);

--
-- Filtros para la tabla `medical_file`
--
ALTER TABLE `medical_file`
  ADD CONSTRAINT `fk_relationship_196` FOREIGN KEY (`serial_gst`) REFERENCES `guest` (`serial_gst`),
  ADD CONSTRAINT `fk_relationship_65` FOREIGN KEY (`serial_usr`) REFERENCES `user` (`serial_usr`);

--
-- Filtros para la tabla `message`
--
ALTER TABLE `message`
  ADD CONSTRAINT `fk_relationship_136` FOREIGN KEY (`serial_usr_send`) REFERENCES `user` (`serial_usr`),
  ADD CONSTRAINT `fk_relationship_137` FOREIGN KEY (`serial_usr_receive`) REFERENCES `user` (`serial_usr`),
  ADD CONSTRAINT `fk_relationship_154` FOREIGN KEY (`serial_prf`) REFERENCES `profile` (`serial_prf`);

--
-- Filtros para la tabla `message_between_profiles`
--
ALTER TABLE `message_between_profiles`
  ADD CONSTRAINT `fk_relationship_141` FOREIGN KEY (`serial_prf`) REFERENCES `profile` (`serial_prf`),
  ADD CONSTRAINT `fk_relationship_142` FOREIGN KEY (`pro_serial_prf`) REFERENCES `profile` (`serial_prf`);

--
-- Filtros para la tabla `message_thread`
--
ALTER TABLE `message_thread`
  ADD CONSTRAINT `fk_relationship_138` FOREIGN KEY (`serial_usr`) REFERENCES `user` (`serial_usr`);

--
-- Filtros para la tabla `message_thread_messages`
--
ALTER TABLE `message_thread_messages`
  ADD CONSTRAINT `fk_relationship_134` FOREIGN KEY (`serial_mth`) REFERENCES `message_thread` (`serial_mth`),
  ADD CONSTRAINT `fk_relationship_135` FOREIGN KEY (`serial_msg`) REFERENCES `message` (`serial_msg`);

--
-- Filtros para la tabla `ministry_resit_change_log`
--
ALTER TABLE `ministry_resit_change_log`
  ADD CONSTRAINT `fk_relationship_254` FOREIGN KEY (`serial_msb`, `serial_std`, `serial_spt`, `serial_lvl`, `serial_scy`, `serial_set`) REFERENCES `ministry_resit_student` (`serial_msb`, `serial_std`, `serial_spt`, `serial_lvl`, `serial_scy`, `serial_set`),
  ADD CONSTRAINT `fk_relationship_257` FOREIGN KEY (`serial_usr`) REFERENCES `user` (`serial_usr`),
  ADD CONSTRAINT `fk_relationship_258` FOREIGN KEY (`serial_chr`) REFERENCES `change_reason` (`serial_chr`);

--
-- Filtros para la tabla `ministry_resit_student`
--
ALTER TABLE `ministry_resit_student`
  ADD CONSTRAINT `fk_relationship_255` FOREIGN KEY (`serial_std`, `serial_spt`, `serial_lvl`, `serial_scy`, `serial_set`) REFERENCES `student_year` (`serial_std`, `serial_spt`, `serial_lvl`, `serial_scy`, `serial_set`),
  ADD CONSTRAINT `fk_relationship_256` FOREIGN KEY (`serial_msb`) REFERENCES `ministry_subject` (`serial_msb`);

--
-- Filtros para la tabla `optional_subject`
--
ALTER TABLE `optional_subject`
  ADD CONSTRAINT `fk_relationship_116` FOREIGN KEY (`per_serial_pgy`) REFERENCES `period_group_year` (`serial_pgy`),
  ADD CONSTRAINT `fk_relationship_127` FOREIGN KEY (`serial_aca`, `serial_emp`) REFERENCES `employee_area` (`serial_aca`, `serial_emp`),
  ADD CONSTRAINT `fk_relationship_249` FOREIGN KEY (`serial_pgy`) REFERENCES `period_group_year` (`serial_pgy`),
  ADD CONSTRAINT `fk_relationship_53` FOREIGN KEY (`serial_csb`) REFERENCES `cat_subject` (`serial_csb`);

--
-- Filtros para la tabla `optional_subject_level`
--
ALTER TABLE `optional_subject_level`
  ADD CONSTRAINT `fk_relationship_104` FOREIGN KEY (`serial_spt`, `serial_lvl`, `serial_scy`, `serial_set`) REFERENCES `level_section_year_specialty` (`serial_spt`, `serial_lvl`, `serial_scy`, `serial_set`),
  ADD CONSTRAINT `fk_relationship_105` FOREIGN KEY (`serial_osb`) REFERENCES `optional_subject` (`serial_osb`);

--
-- Filtros para la tabla `optional_subject_period`
--
ALTER TABLE `optional_subject_period`
  ADD CONSTRAINT `fk_relationship_106` FOREIGN KEY (`serial_osb`) REFERENCES `optional_subject` (`serial_osb`),
  ADD CONSTRAINT `fk_relationship_107` FOREIGN KEY (`serial_prd`) REFERENCES `period` (`serial_prd`);

--
-- Filtros para la tabla `optional_subject_retest`
--
ALTER TABLE `optional_subject_retest`
  ADD CONSTRAINT `fk_relationship_198` FOREIGN KEY (`serial_prd`, `serial_osb`) REFERENCES `optional_subject_period` (`serial_prd`, `serial_osb`);

--
-- Filtros para la tabla `optional_subject_retest_student`
--
ALTER TABLE `optional_subject_retest_student`
  ADD CONSTRAINT `fk_relationship_199` FOREIGN KEY (`serial_osr`) REFERENCES `optional_subject_retest` (`serial_osr`),
  ADD CONSTRAINT `fk_relationship_200` FOREIGN KEY (`serial_std`) REFERENCES `student` (`serial_std`);

--
-- Filtros para la tabla `opt_activity_applied_criterias`
--
ALTER TABLE `opt_activity_applied_criterias`
  ADD CONSTRAINT `fk_relationship_117` FOREIGN KEY (`serial_gct`, `serial_osb`, `serial_att`, `serial_prd`) REFERENCES `opt_subject_year_activity_criteria_period` (`serial_gct`, `serial_osb`, `serial_att`, `serial_prd`),
  ADD CONSTRAINT `fk_relationship_118` FOREIGN KEY (`serial_opa`) REFERENCES `opt_activity` (`serial_opa`);

--
-- Filtros para la tabla `opt_activity_attachment`
--
ALTER TABLE `opt_activity_attachment`
  ADD CONSTRAINT `fk_relationship_119` FOREIGN KEY (`serial_opa`) REFERENCES `opt_activity` (`serial_opa`);

--
-- Filtros para la tabla `opt_grade_change_log`
--
ALTER TABLE `opt_grade_change_log`
  ADD CONSTRAINT `fk_relationship_125` FOREIGN KEY (`serial_opa`, `serial_gct`, `serial_osb`, `serial_att`, `serial_prd`, `serial_std`) REFERENCES `opt_student_criteria_grade` (`serial_opa`, `serial_gct`, `serial_osb`, `serial_att`, `serial_prd`, `serial_std`),
  ADD CONSTRAINT `fk_relationship_126` FOREIGN KEY (`serial_usr`) REFERENCES `user` (`serial_usr`),
  ADD CONSTRAINT `fk_relationship_149` FOREIGN KEY (`serial_chr`) REFERENCES `change_reason` (`serial_chr`);

--
-- Filtros para la tabla `opt_student_criteria_grade`
--
ALTER TABLE `opt_student_criteria_grade`
  ADD CONSTRAINT `fk_relationship_121` FOREIGN KEY (`serial_opa`, `serial_gct`, `serial_osb`, `serial_att`, `serial_prd`) REFERENCES `opt_activity_applied_criterias` (`serial_opa`, `serial_gct`, `serial_osb`, `serial_att`, `serial_prd`);

--
-- Filtros para la tabla `opt_subject_period_activity_type`
--
ALTER TABLE `opt_subject_period_activity_type`
  ADD CONSTRAINT `fk_relationship_112` FOREIGN KEY (`serial_prd`, `serial_osb`) REFERENCES `optional_subject_period` (`serial_prd`, `serial_osb`),
  ADD CONSTRAINT `fk_relationship_113` FOREIGN KEY (`serial_att`) REFERENCES `activity_type` (`serial_att`);

--
-- Filtros para la tabla `opt_subject_retest_change_log`
--
ALTER TABLE `opt_subject_retest_change_log`
  ADD CONSTRAINT `fk_relationship_215` FOREIGN KEY (`serial_std`, `serial_osr`) REFERENCES `optional_subject_retest_student` (`serial_std`, `serial_osr`),
  ADD CONSTRAINT `fk_relationship_216` FOREIGN KEY (`serial_usr`) REFERENCES `user` (`serial_usr`),
  ADD CONSTRAINT `fk_relationship_217` FOREIGN KEY (`serial_chr`) REFERENCES `change_reason` (`serial_chr`);

--
-- Filtros para la tabla `opt_subject_year_activity_criteria_period`
--
ALTER TABLE `opt_subject_year_activity_criteria_period`
  ADD CONSTRAINT `fk_relationship_114` FOREIGN KEY (`serial_att`, `serial_prd`) REFERENCES `opt_subject_period_activity_type` (`serial_att`, `serial_prd`),
  ADD CONSTRAINT `fk_relationship_115` FOREIGN KEY (`serial_gct`, `serial_osb`) REFERENCES `opt_subject_year_grade_criteria` (`serial_gct`, `serial_osb`);

--
-- Filtros para la tabla `opt_subject_year_grade_criteria`
--
ALTER TABLE `opt_subject_year_grade_criteria`
  ADD CONSTRAINT `fk_relationship_110` FOREIGN KEY (`serial_osb`) REFERENCES `optional_subject` (`serial_osb`),
  ADD CONSTRAINT `fk_relationship_111` FOREIGN KEY (`serial_gct`) REFERENCES `grade_criteria` (`serial_gct`);

--
-- Filtros para la tabla `payment_detail`
--
ALTER TABLE `payment_detail`
  ADD CONSTRAINT `fk_relationship_172` FOREIGN KEY (`serial_phy`) REFERENCES `invoice` (`serial_phy`),
  ADD CONSTRAINT `fk_relationship_173` FOREIGN KEY (`serial_pym`) REFERENCES `payment_method` (`serial_pym`),
  ADD CONSTRAINT `fk_relationship_191` FOREIGN KEY (`serial_ccd`) REFERENCES `credit_card` (`serial_ccd`),
  ADD CONSTRAINT `fk_relationship_192` FOREIGN KEY (`serial_fin`) REFERENCES `financial_institution` (`serial_fin`);

--
-- Filtros para la tabla `period`
--
ALTER TABLE `period`
  ADD CONSTRAINT `fk_periods_by_group` FOREIGN KEY (`serial_pgy`) REFERENCES `period_group_year` (`serial_pgy`);

--
-- Filtros para la tabla `period_group_year`
--
ALTER TABLE `period_group_year`
  ADD CONSTRAINT `fk_periods_in_year` FOREIGN KEY (`serial_scy`) REFERENCES `school_year` (`serial_scy`),
  ADD CONSTRAINT `fk_period_for_year` FOREIGN KEY (`serial_cpd`) REFERENCES `cat_period` (`serial_cpd`);

--
-- Filtros para la tabla `pre_registration`
--
ALTER TABLE `pre_registration`
  ADD CONSTRAINT `fk_registered_nationality` FOREIGN KEY (`serial_nnt`) REFERENCES `nationality` (`serial_nnt`),
  ADD CONSTRAINT `fk_relationship_241` FOREIGN KEY (`serial_ste`) REFERENCES `state` (`serial_ste`),
  ADD CONSTRAINT `fk_relationship_64` FOREIGN KEY (`serial_spt`, `serial_lvl`, `serial_scy`, `serial_set`) REFERENCES `level_section_year_specialty` (`serial_spt`, `serial_lvl`, `serial_scy`, `serial_set`),
  ADD CONSTRAINT `fk_relationship_67` FOREIGN KEY (`serial_dgr`) REFERENCES `degree` (`serial_dgr`),
  ADD CONSTRAINT `fk_user_register` FOREIGN KEY (`serial_usr`) REFERENCES `user` (`serial_usr`);

--
-- Filtros para la tabla `pre_registration_period`
--
ALTER TABLE `pre_registration_period`
  ADD CONSTRAINT `fk_relationship_73` FOREIGN KEY (`serial_scy`) REFERENCES `school_year` (`serial_scy`);

--
-- Filtros para la tabla `pre_registration_required_documment`
--
ALTER TABLE `pre_registration_required_documment`
  ADD CONSTRAINT `fk_relationship_263` FOREIGN KEY (`serial_ypd`) REFERENCES `year_pre_registration_document` (`serial_ypd`),
  ADD CONSTRAINT `fk_relationship_264` FOREIGN KEY (`serial_prt`) REFERENCES `pre_registration` (`serial_prt`),
  ADD CONSTRAINT `fk_relationship_265` FOREIGN KEY (`serial_wpr`) REFERENCES `web_pre_registration` (`serial_wpr`);

--
-- Filtros para la tabla `process`
--
ALTER TABLE `process`
  ADD CONSTRAINT `fk_parent_process` FOREIGN KEY (`prc_serial_prc`) REFERENCES `process` (`serial_prc`);

--
-- Filtros para la tabla `profile_process`
--
ALTER TABLE `profile_process`
  ADD CONSTRAINT `fk_has_process` FOREIGN KEY (`serial_prf`) REFERENCES `profile` (`serial_prf`),
  ADD CONSTRAINT `fk_in_profile` FOREIGN KEY (`serial_prc`) REFERENCES `process` (`serial_prc`);

--
-- Filtros para la tabla `psychological_file`
--
ALTER TABLE `psychological_file`
  ADD CONSTRAINT `fk_relationship_89` FOREIGN KEY (`serial_std`) REFERENCES `student` (`serial_std`);

--
-- Filtros para la tabla `qualitative_equivalence`
--
ALTER TABLE `qualitative_equivalence`
  ADD CONSTRAINT `fk_relationship_161` FOREIGN KEY (`serial_scy`) REFERENCES `school_year` (`serial_scy`);

--
-- Filtros para la tabla `related_student`
--
ALTER TABLE `related_student`
  ADD CONSTRAINT `fk_relationship_233` FOREIGN KEY (`serial_std`) REFERENCES `student` (`serial_std`),
  ADD CONSTRAINT `fk_relationship_234` FOREIGN KEY (`stu_serial_std`) REFERENCES `student` (`serial_std`),
  ADD CONSTRAINT `fk_relationship_236` FOREIGN KEY (`serial_rap`) REFERENCES `relationship_application` (`serial_rap`);

--
-- Filtros para la tabla `relationship_application`
--
ALTER TABLE `relationship_application`
  ADD CONSTRAINT `fk_relationship_232` FOREIGN KEY (`serial_grd`) REFERENCES `guardian` (`serial_grd`);

--
-- Filtros para la tabla `request_detail`
--
ALTER TABLE `request_detail`
  ADD CONSTRAINT `fk_relationship_188` FOREIGN KEY (`serial_ist`) REFERENCES `income_stock` (`serial_ist`),
  ADD CONSTRAINT `fk_relationship_189` FOREIGN KEY (`serial_srq`) REFERENCES `stock_request` (`serial_srq`);

--
-- Filtros para la tabla `resit_change_log`
--
ALTER TABLE `resit_change_log`
  ADD CONSTRAINT `fk_relationship_218` FOREIGN KEY (`remote_id_rst`, `serial_std`, `serial_spt`, `serial_lvl`, `serial_scy`, `serial_set`, `type_rst`) REFERENCES `resit_student` (`remote_id_rst`, `serial_std`, `serial_spt`, `serial_lvl`, `serial_scy`, `serial_set`, `type_rst`),
  ADD CONSTRAINT `fk_relationship_219` FOREIGN KEY (`serial_usr`) REFERENCES `user` (`serial_usr`),
  ADD CONSTRAINT `fk_relationship_220` FOREIGN KEY (`serial_chr`) REFERENCES `change_reason` (`serial_chr`);

--
-- Filtros para la tabla `resit_student`
--
ALTER TABLE `resit_student`
  ADD CONSTRAINT `fk_relationship_209` FOREIGN KEY (`serial_std`, `serial_spt`, `serial_lvl`, `serial_scy`, `serial_set`) REFERENCES `student_year` (`serial_std`, `serial_spt`, `serial_lvl`, `serial_scy`, `serial_set`),
  ADD CONSTRAINT `fk_relationship_211` FOREIGN KEY (`serial_emp`) REFERENCES `employee` (`serial_emp`);

--
-- Filtros para la tabla `school_setup`
--
ALTER TABLE `school_setup`
  ADD CONSTRAINT `fk_currency_for_school` FOREIGN KEY (`serial_crr`) REFERENCES `currency` (`serial_crr`),
  ADD CONSTRAINT `fk_school_state` FOREIGN KEY (`serial_ste`) REFERENCES `state` (`serial_ste`);

--
-- Filtros para la tabla `section_by_year`
--
ALTER TABLE `section_by_year`
  ADD CONSTRAINT `fk_section_in_year` FOREIGN KEY (`serial_set`) REFERENCES `section` (`serial_set`),
  ADD CONSTRAINT `fk_year_sections` FOREIGN KEY (`serial_scy`) REFERENCES `school_year` (`serial_scy`);

--
-- Filtros para la tabla `special_grade_change_log`
--
ALTER TABLE `special_grade_change_log`
  ADD CONSTRAINT `fk_relationship_221` FOREIGN KEY (`serial_ysg`, `serial_std`, `serial_spt`, `serial_lvl`, `serial_scy`, `serial_set`) REFERENCES `student_year_special_grade` (`serial_ysg`, `serial_std`, `serial_spt`, `serial_lvl`, `serial_scy`, `serial_set`),
  ADD CONSTRAINT `fk_relationship_222` FOREIGN KEY (`serial_chr`) REFERENCES `change_reason` (`serial_chr`),
  ADD CONSTRAINT `fk_relationship_223` FOREIGN KEY (`serial_usr`) REFERENCES `user` (`serial_usr`);

--
-- Filtros para la tabla `state`
--
ALTER TABLE `state`
  ADD CONSTRAINT `fk_country_state` FOREIGN KEY (`serial_cou`) REFERENCES `country` (`serial_cou`);

--
-- Filtros para la tabla `stock_request`
--
ALTER TABLE `stock_request`
  ADD CONSTRAINT `fk_relationship_187` FOREIGN KEY (`serial_udt`) REFERENCES `use_destination` (`serial_udt`),
  ADD CONSTRAINT `fk_relationship_190` FOREIGN KEY (`serial_usr`) REFERENCES `user` (`serial_usr`);

--
-- Filtros para la tabla `student`
--
ALTER TABLE `student`
  ADD CONSTRAINT `fk_relationship_240` FOREIGN KEY (`serial_ste`) REFERENCES `state` (`serial_ste`),
  ADD CONSTRAINT `fk_relationship_61` FOREIGN KEY (`serial_nnt`) REFERENCES `nationality` (`serial_nnt`),
  ADD CONSTRAINT `fk_student_user` FOREIGN KEY (`serial_usr`) REFERENCES `user` (`serial_usr`);

--
-- Filtros para la tabla `student_by_course`
--
ALTER TABLE `student_by_course`
  ADD CONSTRAINT `fk_course_for_student` FOREIGN KEY (`serial_crs`) REFERENCES `course` (`serial_crs`),
  ADD CONSTRAINT `fk_student_in_course` FOREIGN KEY (`serial_std`) REFERENCES `student` (`serial_std`);

--
-- Filtros para la tabla `student_criteria_grade`
--
ALTER TABLE `student_criteria_grade`
  ADD CONSTRAINT `fk_relationship_100` FOREIGN KEY (`serial_std`, `serial_crs`) REFERENCES `student_by_course` (`serial_std`, `serial_crs`),
  ADD CONSTRAINT `fk_relationship_99` FOREIGN KEY (`serial_att`, `serial_prd`, `serial_sbc`, `serial_gct`, `serial_act`) REFERENCES `activity_applied_criterias` (`serial_att`, `serial_prd`, `serial_sbc`, `serial_gct`, `serial_act`);

--
-- Filtros para la tabla `student_guardian`
--
ALTER TABLE `student_guardian`
  ADD CONSTRAINT `fk_has_guardian` FOREIGN KEY (`serial_std`) REFERENCES `student` (`serial_std`),
  ADD CONSTRAINT `fk_is_guardian` FOREIGN KEY (`serial_grd`) REFERENCES `guardian` (`serial_grd`);

--
-- Filtros para la tabla `student_optional_subject`
--
ALTER TABLE `student_optional_subject`
  ADD CONSTRAINT `fk_relationship_108` FOREIGN KEY (`serial_osb`) REFERENCES `optional_subject` (`serial_osb`),
  ADD CONSTRAINT `fk_relationship_109` FOREIGN KEY (`serial_std`) REFERENCES `student` (`serial_std`),
  ADD CONSTRAINT `fk_relationship_246` FOREIGN KEY (`serial_prd`, `serial_lop`) REFERENCES `level_optional_group_detail` (`serial_prd`, `serial_lop`);

--
-- Filtros para la tabla `student_subject_period_average`
--
ALTER TABLE `student_subject_period_average`
  ADD CONSTRAINT `fk_relationship_248` FOREIGN KEY (`stu_serial_ssa`) REFERENCES `student_subject_period_average` (`serial_ssa`),
  ADD CONSTRAINT `fk_relationship_250` FOREIGN KEY (`serial_sbc`) REFERENCES `subject_course` (`serial_sbc`),
  ADD CONSTRAINT `fk_relationship_251` FOREIGN KEY (`serial_lop`) REFERENCES `level_optional_group` (`serial_lop`),
  ADD CONSTRAINT `fk_relationship_252` FOREIGN KEY (`serial_std`, `serial_spt`, `serial_lvl`, `serial_scy`, `serial_set`) REFERENCES `student_year` (`serial_std`, `serial_spt`, `serial_lvl`, `serial_scy`, `serial_set`),
  ADD CONSTRAINT `fk_relationship_253` FOREIGN KEY (`serial_prd`) REFERENCES `period` (`serial_prd`);

--
-- Filtros para la tabla `student_year`
--
ALTER TABLE `student_year`
  ADD CONSTRAINT `fk_relationship_74` FOREIGN KEY (`serial_std`) REFERENCES `student` (`serial_std`),
  ADD CONSTRAINT `fk_relationship_75` FOREIGN KEY (`serial_spt`, `serial_lvl`, `serial_scy`, `serial_set`) REFERENCES `level_section_year_specialty` (`serial_spt`, `serial_lvl`, `serial_scy`, `serial_set`);

--
-- Filtros para la tabla `student_year_debts`
--
ALTER TABLE `student_year_debts`
  ADD CONSTRAINT `fk_relationship_102` FOREIGN KEY (`serial_pct`, `serial_spt`, `serial_lvl`, `serial_scy`, `serial_set`) REFERENCES `year_level_specialty_payment_concept` (`serial_pct`, `serial_spt`, `serial_lvl`, `serial_scy`, `serial_set`),
  ADD CONSTRAINT `fk_relationship_103` FOREIGN KEY (`serial_std`) REFERENCES `student` (`serial_std`);

--
-- Filtros para la tabla `student_year_discount`
--
ALTER TABLE `student_year_discount`
  ADD CONSTRAINT `fk_relationship_228` FOREIGN KEY (`serial_ydc`) REFERENCES `year_discount` (`serial_ydc`),
  ADD CONSTRAINT `fk_relationship_229` FOREIGN KEY (`serial_std`) REFERENCES `student` (`serial_std`);

--
-- Filtros para la tabla `student_year_discount_values`
--
ALTER TABLE `student_year_discount_values`
  ADD CONSTRAINT `fk_relationship_230` FOREIGN KEY (`serial_std`, `serial_ydc`) REFERENCES `student_year_discount` (`serial_std`, `serial_ydc`),
  ADD CONSTRAINT `fk_relationship_235` FOREIGN KEY (`serial_pct`) REFERENCES `payment_concept` (`serial_pct`);

--
-- Filtros para la tabla `student_year_document`
--
ALTER TABLE `student_year_document`
  ADD CONSTRAINT `fk_relationship_159` FOREIGN KEY (`serial_std`, `serial_spt`, `serial_lvl`, `serial_scy`, `serial_set`) REFERENCES `student_year` (`serial_std`, `serial_spt`, `serial_lvl`, `serial_scy`, `serial_set`),
  ADD CONSTRAINT `fk_relationship_160` FOREIGN KEY (`serial_yqd`) REFERENCES `year_qualifying_document` (`serial_yqd`);

--
-- Filtros para la tabla `student_year_special_grade`
--
ALTER TABLE `student_year_special_grade`
  ADD CONSTRAINT `fk_relationship_179` FOREIGN KEY (`serial_ysg`) REFERENCES `year_special_grade` (`serial_ysg`),
  ADD CONSTRAINT `fk_relationship_180` FOREIGN KEY (`serial_std`, `serial_spt`, `serial_lvl`, `serial_scy`, `serial_set`) REFERENCES `student_year` (`serial_std`, `serial_spt`, `serial_lvl`, `serial_scy`, `serial_set`);

--
-- Filtros para la tabla `subject`
--
ALTER TABLE `subject`
  ADD CONSTRAINT `fk_subject_fo__level` FOREIGN KEY (`serial_spt`, `serial_lvl`, `serial_scy`, `serial_set`) REFERENCES `level_section_year_specialty` (`serial_spt`, `serial_lvl`, `serial_scy`, `serial_set`),
  ADD CONSTRAINT `fk_year_subject` FOREIGN KEY (`serial_csb`) REFERENCES `cat_subject` (`serial_csb`);

--
-- Filtros para la tabla `subject_course`
--
ALTER TABLE `subject_course`
  ADD CONSTRAINT `fk_course_with_subject` FOREIGN KEY (`serial_crs`) REFERENCES `course` (`serial_crs`),
  ADD CONSTRAINT `fk_is_teacher` FOREIGN KEY (`serial_aca`, `serial_emp`) REFERENCES `employee_area` (`serial_aca`, `serial_emp`),
  ADD CONSTRAINT `fk_subject_for_course` FOREIGN KEY (`serial_sbj`) REFERENCES `subject` (`serial_sbj`);

--
-- Filtros para la tabla `subject_course_period`
--
ALTER TABLE `subject_course_period`
  ADD CONSTRAINT `fk_relationship_71` FOREIGN KEY (`serial_prd`) REFERENCES `period` (`serial_prd`),
  ADD CONSTRAINT `fk_relationship_72` FOREIGN KEY (`serial_sbc`) REFERENCES `subject_course` (`serial_sbc`);

--
-- Filtros para la tabla `subject_course_retest`
--
ALTER TABLE `subject_course_retest`
  ADD CONSTRAINT `fk_relationship_201` FOREIGN KEY (`serial_prd`, `serial_sbc`) REFERENCES `subject_course_period` (`serial_prd`, `serial_sbc`);

--
-- Filtros para la tabla `subject_course_retest_student`
--
ALTER TABLE `subject_course_retest_student`
  ADD CONSTRAINT `fk_relationship_202` FOREIGN KEY (`serial_scr`) REFERENCES `subject_course_retest` (`serial_scr`),
  ADD CONSTRAINT `fk_relationship_203` FOREIGN KEY (`serial_std`) REFERENCES `student` (`serial_std`);

--
-- Filtros para la tabla `subject_level_restriction`
--
ALTER TABLE `subject_level_restriction`
  ADD CONSTRAINT `fk_relationship_266` FOREIGN KEY (`serial_lop`) REFERENCES `level_optional_group` (`serial_lop`),
  ADD CONSTRAINT `fk_relationship_267` FOREIGN KEY (`serial_sbc`) REFERENCES `subject_course` (`serial_sbc`),
  ADD CONSTRAINT `fk_relationship_268` FOREIGN KEY (`serial_hbg`) REFERENCES `hour_by_group` (`serial_hbg`);

--
-- Filtros para la tabla `subject_ministry_equivalence`
--
ALTER TABLE `subject_ministry_equivalence`
  ADD CONSTRAINT `fk_relationship_166` FOREIGN KEY (`serial_msb`) REFERENCES `ministry_subject` (`serial_msb`),
  ADD CONSTRAINT `fk_relationship_169` FOREIGN KEY (`serial_emp`) REFERENCES `employee` (`serial_emp`),
  ADD CONSTRAINT `fk_relationship_193` FOREIGN KEY (`serial_csb`) REFERENCES `cat_subject` (`serial_csb`),
  ADD CONSTRAINT `fk_relationship_204` FOREIGN KEY (`serial_spt`, `serial_lvl`, `serial_scy`, `serial_set`) REFERENCES `level_section_year_specialty` (`serial_spt`, `serial_lvl`, `serial_scy`, `serial_set`),
  ADD CONSTRAINT `fk_relationship_247` FOREIGN KEY (`serial_lop`) REFERENCES `level_optional_group` (`serial_lop`);

--
-- Filtros para la tabla `subject_period_activity_type`
--
ALTER TABLE `subject_period_activity_type`
  ADD CONSTRAINT `fk_relationship_84` FOREIGN KEY (`serial_prd`, `serial_sbc`) REFERENCES `subject_course_period` (`serial_prd`, `serial_sbc`),
  ADD CONSTRAINT `fk_relationship_85` FOREIGN KEY (`serial_att`) REFERENCES `activity_type` (`serial_att`);

--
-- Filtros para la tabla `subject_retest_change_log`
--
ALTER TABLE `subject_retest_change_log`
  ADD CONSTRAINT `fk_relationship_212` FOREIGN KEY (`serial_std`, `serial_scr`) REFERENCES `subject_course_retest_student` (`serial_std`, `serial_scr`),
  ADD CONSTRAINT `fk_relationship_213` FOREIGN KEY (`serial_chr`) REFERENCES `change_reason` (`serial_chr`),
  ADD CONSTRAINT `fk_relationship_214` FOREIGN KEY (`serial_usr`) REFERENCES `user` (`serial_usr`);

--
-- Filtros para la tabla `subject_year_activity_criteria_period`
--
ALTER TABLE `subject_year_activity_criteria_period`
  ADD CONSTRAINT `fk_relationship_86` FOREIGN KEY (`serial_att`, `serial_prd`, `serial_sbc`) REFERENCES `subject_period_activity_type` (`serial_att`, `serial_prd`, `serial_sbc`),
  ADD CONSTRAINT `fk_relationship_87` FOREIGN KEY (`serial_sbj`, `serial_gct`) REFERENCES `subject_year_grade_criteria` (`serial_sbj`, `serial_gct`);

--
-- Filtros para la tabla `subject_year_grade_criteria`
--
ALTER TABLE `subject_year_grade_criteria`
  ADD CONSTRAINT `fk_relationship_82` FOREIGN KEY (`serial_sbj`) REFERENCES `subject` (`serial_sbj`),
  ADD CONSTRAINT `fk_relationship_83` FOREIGN KEY (`serial_gct`) REFERENCES `grade_criteria` (`serial_gct`);

--
-- Filtros para la tabla `syllabus_files`
--
ALTER TABLE `syllabus_files`
  ADD CONSTRAINT `fk_relationship_128` FOREIGN KEY (`serial_osb`) REFERENCES `optional_subject` (`serial_osb`),
  ADD CONSTRAINT `fk_relationship_129` FOREIGN KEY (`serial_sbc`) REFERENCES `subject_course` (`serial_sbc`);

--
-- Filtros para la tabla `syllabus_files_change_log`
--
ALTER TABLE `syllabus_files_change_log`
  ADD CONSTRAINT `fk_relationship_132` FOREIGN KEY (`serial_syf`) REFERENCES `syllabus_files` (`serial_syf`),
  ADD CONSTRAINT `fk_relationship_133` FOREIGN KEY (`serial_usr`) REFERENCES `user` (`serial_usr`);

--
-- Filtros para la tabla `teacher_hours_year`
--
ALTER TABLE `teacher_hours_year`
  ADD CONSTRAINT `fk_relationship_69` FOREIGN KEY (`serial_emp`) REFERENCES `employee` (`serial_emp`),
  ADD CONSTRAINT `fk_relationship_70` FOREIGN KEY (`serial_scy`) REFERENCES `school_year` (`serial_scy`);

--
-- Filtros para la tabla `user`
--
ALTER TABLE `user`
  ADD CONSTRAINT `fk_employee_user2` FOREIGN KEY (`serial_emp`) REFERENCES `employee` (`serial_emp`),
  ADD CONSTRAINT `fk_guardian_user2` FOREIGN KEY (`serial_grd`) REFERENCES `guardian` (`serial_grd`),
  ADD CONSTRAINT `fk_student_user2` FOREIGN KEY (`serial_std`) REFERENCES `student` (`serial_std`);

--
-- Filtros para la tabla `user_profile`
--
ALTER TABLE `user_profile`
  ADD CONSTRAINT `fk_for_user` FOREIGN KEY (`serial_prf`) REFERENCES `profile` (`serial_prf`),
  ADD CONSTRAINT `fk_has_profile` FOREIGN KEY (`serial_usr`) REFERENCES `user` (`serial_usr`);

--
-- Filtros para la tabla `visits_medical_center`
--
ALTER TABLE `visits_medical_center`
  ADD CONSTRAINT `fk_relationship_197` FOREIGN KEY (`serial_mdf`) REFERENCES `medical_file` (`serial_mdf`);

--
-- Filtros para la tabla `web_pre_registration`
--
ALTER TABLE `web_pre_registration`
  ADD CONSTRAINT `fk_relationship_205` FOREIGN KEY (`serial_dgr`) REFERENCES `degree` (`serial_dgr`),
  ADD CONSTRAINT `fk_relationship_206` FOREIGN KEY (`serial_nnt`) REFERENCES `nationality` (`serial_nnt`),
  ADD CONSTRAINT `fk_relationship_207` FOREIGN KEY (`serial_spt`, `serial_lvl`, `serial_scy`, `serial_set`) REFERENCES `level_section_year_specialty` (`serial_spt`, `serial_lvl`, `serial_scy`, `serial_set`),
  ADD CONSTRAINT `fk_relationship_208` FOREIGN KEY (`serial_wus`) REFERENCES `web_user` (`serial_wus`),
  ADD CONSTRAINT `fk_relationship_242` FOREIGN KEY (`serial_ste`) REFERENCES `state` (`serial_ste`);

--
-- Filtros para la tabla `year_concept_taxes`
--
ALTER TABLE `year_concept_taxes`
  ADD CONSTRAINT `fk_relationship_93` FOREIGN KEY (`serial_tax`) REFERENCES `taxes` (`serial_tax`),
  ADD CONSTRAINT `fk_relationship_94` FOREIGN KEY (`serial_pct`, `serial_spt`, `serial_lvl`, `serial_scy`, `serial_set`) REFERENCES `year_level_specialty_payment_concept` (`serial_pct`, `serial_spt`, `serial_lvl`, `serial_scy`, `serial_set`);

--
-- Filtros para la tabla `year_discount`
--
ALTER TABLE `year_discount`
  ADD CONSTRAINT `fk_relationship_224` FOREIGN KEY (`serial_scy`) REFERENCES `school_year` (`serial_scy`),
  ADD CONSTRAINT `fk_relationship_225` FOREIGN KEY (`serial_dsc`) REFERENCES `discount` (`serial_dsc`);

--
-- Filtros para la tabla `year_grade_subject`
--
ALTER TABLE `year_grade_subject`
  ADD CONSTRAINT `fk_relationship_174` FOREIGN KEY (`serial_csb`) REFERENCES `cat_subject` (`serial_csb`),
  ADD CONSTRAINT `fk_relationship_175` FOREIGN KEY (`serial_scy`) REFERENCES `school_year` (`serial_scy`);

--
-- Filtros para la tabla `year_level_specialty_payment_concept`
--
ALTER TABLE `year_level_specialty_payment_concept`
  ADD CONSTRAINT `fk_relationship_90` FOREIGN KEY (`serial_spt`, `serial_lvl`, `serial_scy`, `serial_set`) REFERENCES `level_section_year_specialty` (`serial_spt`, `serial_lvl`, `serial_scy`, `serial_set`),
  ADD CONSTRAINT `fk_relationship_91` FOREIGN KEY (`serial_pct`) REFERENCES `payment_concept` (`serial_pct`),
  ADD CONSTRAINT `fk_relationship_92` FOREIGN KEY (`serial_art`) REFERENCES `article` (`serial_art`);

--
-- Filtros para la tabla `year_pre_registration_document`
--
ALTER TABLE `year_pre_registration_document`
  ADD CONSTRAINT `fk_relationship_261` FOREIGN KEY (`serial_scy`) REFERENCES `school_year` (`serial_scy`),
  ADD CONSTRAINT `fk_relationship_262` FOREIGN KEY (`serial_qdc`) REFERENCES `qualifying_document` (`serial_qdc`);

--
-- Filtros para la tabla `year_qualifying_document`
--
ALTER TABLE `year_qualifying_document`
  ADD CONSTRAINT `fk_relationship_157` FOREIGN KEY (`serial_scy`) REFERENCES `school_year` (`serial_scy`),
  ADD CONSTRAINT `fk_relationship_158` FOREIGN KEY (`serial_qdc`) REFERENCES `qualifying_document` (`serial_qdc`);

--
-- Filtros para la tabla `year_report_comment`
--
ALTER TABLE `year_report_comment`
  ADD CONSTRAINT `fk_relationship_259` FOREIGN KEY (`serial_std`) REFERENCES `student` (`serial_std`),
  ADD CONSTRAINT `fk_relationship_260` FOREIGN KEY (`serial_scy`) REFERENCES `school_year` (`serial_scy`);

--
-- Filtros para la tabla `year_resit_exam_ranges`
--
ALTER TABLE `year_resit_exam_ranges`
  ADD CONSTRAINT `fk_relationship_162` FOREIGN KEY (`serial_scy`) REFERENCES `school_year` (`serial_scy`);

--
-- Filtros para la tabla `year_special_grade`
--
ALTER TABLE `year_special_grade`
  ADD CONSTRAINT `fk_relationship_178` FOREIGN KEY (`serial_scy`) REFERENCES `school_year` (`serial_scy`);

--
-- Filtros para la tabla `year_subject_classroom`
--
ALTER TABLE `year_subject_classroom`
  ADD CONSTRAINT `fk_relationship_163` FOREIGN KEY (`serial_clr`) REFERENCES `classroom` (`serial_clr`),
  ADD CONSTRAINT `fk_relationship_164` FOREIGN KEY (`serial_sbc`) REFERENCES `subject_course` (`serial_sbc`),
  ADD CONSTRAINT `fk_relationship_165` FOREIGN KEY (`serial_osb`) REFERENCES `optional_subject` (`serial_osb`);
