/*
 * < �KURA, This application manages the daily activities of a Teacher and a Student of a School>
 *
 * Copyright (C) 2012 Virtusa Corporation.
 * This program is free software: you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation, either version 3 of the License, or
 * (at your option) any later version.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of 
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 */

CREATE DATABASE  IF NOT EXISTS `akura` /*!40100 DEFAULT CHARACTER SET latin1 */;
USE `akura`;


--
-- Table structure for table `PROVINCE`
--

CREATE TABLE `PROVINCE` (
  `PROVINCE_ID` int(11) NOT NULL AUTO_INCREMENT,
  `DESCRIPTION` varchar(45) NOT NULL,
  `MODIFIED_TIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`PROVINCE_ID`),
  UNIQUE KEY `DESCRIPTION_UNIQUE` (`DESCRIPTION`)
) ENGINE=InnoDB AUTO_INCREMENT=73 DEFAULT CHARSET=latin1;

--
-- Table structure for table `STREAM`
--

CREATE TABLE `STREAM` (
  `STREAM_ID` int(11) NOT NULL AUTO_INCREMENT,
  `DESCRIPTION` varchar(45) NOT NULL,
  `MODIFIED_TIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`STREAM_ID`),
  UNIQUE KEY `DESCRIPTION_UNIQUE` (`DESCRIPTION`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=latin1;

--
-- Table structure for table `GRADING`
--

CREATE TABLE `GRADING` (
  `GRADING_ID` int(11) NOT NULL AUTO_INCREMENT,
  `DESCRIPTION` varchar(45) NOT NULL,
  `GRADE_ACRONYM` char(1) NOT NULL,
  `MODIFIED_TIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`GRADING_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;

--
-- Table structure for table `MERIT_AWARD`
--

CREATE TABLE `MERIT_AWARD` (
  `MERIT_AWARDS_ID` int(11) NOT NULL AUTO_INCREMENT,
  `AWARD_DESCRIPTION` varchar(100) NOT NULL,
  `MODIFIED_TIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`MERIT_AWARDS_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Table structure for table `STUDENT_PREFECT`
--

CREATE TABLE `STUDENT_PREFECT` (
  `STUDENT_PREFECT_ID` int(11) NOT NULL AUTO_INCREMENT,
  `STUDENT_ID` int(11) NOT NULL,
  `PREFECT_TYPE_ID` int(11) NOT NULL,
  `YEAR` date NOT NULL,
  `MODIFIED_TIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`STUDENT_PREFECT_ID`),
  UNIQUE KEY `UC_STUDENT_PREFECT` (`STUDENT_ID`,`PREFECT_TYPE_ID`,`YEAR`),
  KEY `fk_STUDENT_PREFECT_TYPE_PREFECT_TYPE1` (`PREFECT_TYPE_ID`),
  KEY `fk_STUDENT_PREFECT_TYPE_STUDENT1` (`STUDENT_ID`),
  CONSTRAINT `fk_STUDENT_PREFECT_TYPE_STUDENT1` FOREIGN KEY (`STUDENT_ID`) REFERENCES `STUDENT` (`STUDENT_ID`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_STUDENT_PREFECT_TYPE_PREFECT_TYPE1` FOREIGN KEY (`PREFECT_TYPE_ID`) REFERENCES `PREFECT_TYPE` (`PREFECT_TYPE_ID`) ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Table structure for table `HOLIDAY`
--

CREATE TABLE `HOLIDAY` (
  `HOLIDAY_ID` int(11) NOT NULL AUTO_INCREMENT,
  `DESCRIPTION` varchar(50) NOT NULL,
  `DATE` date DEFAULT NULL,
  `MODIFIED_TIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`HOLIDAY_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Table structure for table `GRADE_SUBJECT`
--

CREATE TABLE `GRADE_SUBJECT` (
  `GRADE_SUBJECT_ID` int(11) NOT NULL AUTO_INCREMENT,
  `GRADE_ID` int(11) NOT NULL,
  `SUBJECT_ID` int(11) NOT NULL,
  `SUBJECT_CODE` varchar(45) NOT NULL,
  `MODIFIED_TIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`GRADE_SUBJECT_ID`),
  UNIQUE KEY `GRADE_SUBJECT_ID_UNIQUE` (`GRADE_SUBJECT_ID`),
  UNIQUE KEY `UC_GRADE_SUBJECT` (`GRADE_ID`,`SUBJECT_ID`),
  KEY `fk_GRADE_SUBJECT_SUBJECT1` (`SUBJECT_ID`),
  KEY `fk_GRADE_SUBJECT_GRADE1` (`GRADE_ID`),
  CONSTRAINT `fk_GRADE_SUBJECT_GRADE1` FOREIGN KEY (`GRADE_ID`) REFERENCES `GRADE` (`GRADE_ID`) ON UPDATE CASCADE ON DELETE RESTRICT,
  CONSTRAINT `fk_GRADE_SUBJECT_SUBJECT1` FOREIGN KEY (`SUBJECT_ID`) REFERENCES `SUBJECT` (`SUBJECT_ID`) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Table structure for table `PREFECT_TYPE`
--

CREATE TABLE `PREFECT_TYPE` (
  `PREFECT_TYPE_ID` int(11) NOT NULL,
  `DESCRIPTION` varchar(45) NOT NULL,
  `MODIFIED_TIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`PREFECT_TYPE_ID`),
  UNIQUE KEY `DESCRIPTION_UNIQUE` (`DESCRIPTION`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Table structure for table `SPORT_SUB`
--

CREATE TABLE `SPORT_SUB` (
  `SPORT_SUB_ID` int(11) NOT NULL AUTO_INCREMENT,
  `DESCRIPTION` varchar(45) NOT NULL,
  `MODIFIED_TIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`SPORT_SUB_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Table structure for table `USER_LOGIN`
--

CREATE TABLE `USER_LOGIN` (
  `USER_LOGIN_ID` int(11) NOT NULL AUTO_INCREMENT,
  `USER_ROLE_ID` int(11) NOT NULL,
  `FIRST_NAME` varchar(45) NOT NULL,
  `LAST_NAME` varchar(45) NOT NULL,
  `EMAIL` varchar(45) NOT NULL,
  `USERNAME` varchar(45) NOT NULL,
  `PASSWORD` varchar(100) NOT NULL,
  `STATUS` smallint(6) NOT NULL,
  `LOGIN_ATTEMPTS` int(1) DEFAULT '0',
  `USER_IDENTIFICATION_NO` varchar(45) DEFAULT NULL,
  `MODIFIED_TIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`USER_LOGIN_ID`),
  KEY `fk_USER_LOGIN_USER_ROLE1` (`USER_ROLE_ID`),
  CONSTRAINT `fk_USER_LOGIN_USER_ROLE1` FOREIGN KEY (`USER_ROLE_ID`) REFERENCES `USER_ROLE` (`USER_ROLE_ID`) ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;

--
-- Table structure for table `JOB_TYPE`
--

CREATE TABLE `JOB_TYPE` (
  `JOB_TYPE_ID` int(11) NOT NULL AUTO_INCREMENT,
  `TYPE_NAME` varchar(45) NOT NULL,
  `MODIFIED_TIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`JOB_TYPE_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Table structure for table `SPORT_CATEGORY`
--

CREATE TABLE `SPORT_CATEGORY` (
  `SPORT_CATEGORY_ID` int(11) NOT NULL AUTO_INCREMENT,
  `SPORT_ID` int(11) NOT NULL,
  `SPORT_SUB_ID` int(11) NOT NULL,
  `MODIFIED_TIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`SPORT_CATEGORY_ID`),
  UNIQUE KEY `UC_SPORT_SPORTSUB` (`SPORT_ID`,`SPORT_SUB_ID`),
  KEY `fk_SPORT_has_SPORT_SUB_SPORT_SUB1` (`SPORT_SUB_ID`),
  KEY `fk_SPORT_has_SPORT_SUB_SPORT1` (`SPORT_ID`),
  CONSTRAINT `fk_SPORT_has_SPORT_SUB_SPORT1` FOREIGN KEY (`SPORT_ID`) REFERENCES `SPORT` (`SPORT_ID`) ON UPDATE CASCADE ON DELETE RESTRICT,
  CONSTRAINT `fk_SPORT_has_SPORT_SUB_SPORT_SUB1` FOREIGN KEY (`SPORT_SUB_ID`) REFERENCES `SPORT_SUB` (`SPORT_SUB_ID`) ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Table structure for table `RELIGION`
--

CREATE TABLE `RELIGION` (
  `RELIGION_ID` int(11) NOT NULL AUTO_INCREMENT,
  `DESCRIPTION` varchar(45) NOT NULL,
  `MODIFIED_TIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`RELIGION_ID`),
  UNIQUE KEY `DESCRIPTION_UNIQUE` (`DESCRIPTION`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

--
-- Table structure for table `JOB`
--

CREATE TABLE `JOB` (
  `JOB_ID` int(11) NOT NULL AUTO_INCREMENT,
  `NAME` varchar(45) NOT NULL,
  `START_DATE` date DEFAULT NULL,
  `END_DATE` date DEFAULT NULL,
  `TRIGGER_NAME` varchar(45) DEFAULT NULL,
  `PARAMETER_1` int(11) DEFAULT NULL,
  `PARAMETER_2` int(11) DEFAULT NULL,
  `PARAMETER_3` int(11) DEFAULT NULL,
  `JOB_TYPE_ID` int(11) DEFAULT NULL,
  `MODIFIED_TIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`JOB_ID`),
  KEY `fk_JOBS_JOB_TYPE1` (`JOB_TYPE_ID`),
  CONSTRAINT `fk_JOBS_JOB_TYPE1` FOREIGN KEY (`JOB_TYPE_ID`) REFERENCES `JOB_TYPE` (`JOB_TYPE_ID`) ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Table structure for table `DONATION_TYPE`
--

CREATE TABLE `DONATION_TYPE` (
  `DONATION_TYPE_ID` int(11) NOT NULL AUTO_INCREMENT,
  `DESCRIPTION` varchar(45) NOT NULL,
  `MODIFIED_TIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`DONATION_TYPE_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;

--
-- Table structure for table `NATIONALITY`
--

CREATE TABLE `NATIONALITY` (
  `NATIONALITY_ID` int(11) NOT NULL AUTO_INCREMENT,
  `DESCRIPTION` varchar(45) NOT NULL,
  `MODIFIED_TIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`NATIONALITY_ID`),
  UNIQUE KEY `DESCRIPTION_UNIQUE` (`DESCRIPTION`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=latin1;

--
-- Table structure for table `EDUCATIONAL_QUALIFICATION`
--

CREATE TABLE `EDUCATIONAL_QUALIFICATION` (
  `EDUCATIONAL_QUAL_ID` int(11) NOT NULL AUTO_INCREMENT,
  `DESCRIPTION` varchar(45) NOT NULL,
  `MODIFIED_TIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`EDUCATIONAL_QUAL_ID`),
  UNIQUE KEY `DESCRIPTION_UNIQUE` (`DESCRIPTION`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;

--
-- Table structure for table `METHOD_OF_TRAVEL`
--

CREATE TABLE `METHOD_OF_TRAVEL` (
  `TRAVEL_ID` int(11) NOT NULL AUTO_INCREMENT,
  `TRAVEL_METHOD` varchar(45) NOT NULL,
  `MODIFIED_TIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`TRAVEL_ID`),
  UNIQUE KEY `TRAVEL_METHOD_UNIQUE` (`TRAVEL_METHOD`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

--
-- Table structure for table `PROFESSIONAL_QUALIFICATION`
--

CREATE TABLE `PROFESSIONAL_QUALIFICATION` (
  `PROFESSIONAL_QUAL_ID` int(11) NOT NULL AUTO_INCREMENT,
  `DESCRIPTION` varchar(45) NOT NULL,
  `MODIFIED_TIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`PROFESSIONAL_QUAL_ID`),
  UNIQUE KEY `DESCRIPTION_UNIQUE` (`DESCRIPTION`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;

--
-- Table structure for table `FAITH_LIFE_RATING`
--

CREATE TABLE `FAITH_LIFE_RATING` (
  `FAITH_LIFE_RATING_ID` int(11) NOT NULL AUTO_INCREMENT,
  `STUDENT_ID` int(11) NOT NULL,
  `FAITH_LIFE_DESCRIPTION` varchar(45) NOT NULL,
  `FAITH_LIFE_VALUE` varchar(45) DEFAULT NULL,
  `CATEGORY` int(11) DEFAULT NULL,
  `MODIFIED_TIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `YEAR` date NOT NULL,
  PRIMARY KEY (`FAITH_LIFE_RATING_ID`),
  KEY `fk_FAITH_LIFE_RATING_STUDENT1` (`STUDENT_ID`),
  CONSTRAINT `fk_FAITH_LIFE_RATING_STUDENT1` FOREIGN KEY (`STUDENT_ID`) REFERENCES `STUDENT` (`STUDENT_ID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Table structure for table `BLOOD_GROUP`
--

CREATE TABLE `BLOOD_GROUP` (
  `BLOOD_GROUP_ID` int(11) NOT NULL AUTO_INCREMENT,
  `DESCRIPTION` varchar(45) NOT NULL,
  `MODIFIED_TIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`BLOOD_GROUP_ID`),
  UNIQUE KEY `DESCRIPTION_UNIQUE` (`DESCRIPTION`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;

--
-- Table structure for table `STUDENT_DISABILITY`
--

CREATE TABLE `STUDENT_DISABILITY` (
  `STUDENT_ID` int(11) NOT NULL,
  `ALLERGIES_MEDICAL_NOTES` varchar(300) DEFAULT NULL,
  `DISABILITY_INFO` varchar(300) DEFAULT NULL,
  `HEARING_IMPAIRMENT` varchar(300) DEFAULT NULL,
  `VISUAL_IMPAIRMENT` varchar(300) DEFAULT NULL,
  `SPEECH_DIFFICULTIES` varchar(300) DEFAULT NULL,
  `DYSLEXIA` varchar(300) DEFAULT NULL,
  `PHYSICAL_DISABILITIES` varchar(300) DEFAULT NULL,
  `FITS` varchar(300) DEFAULT NULL,
  `BEHAVIOUR_DIFFICULTIES` varchar(300) DEFAULT NULL,
  `OTHER_INFO` varchar(400) DEFAULT NULL,
  `MODIFIED_TIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`STUDENT_ID`),
  KEY `fk_STUDENT_DISABILITY_TYPE_STUDENT1` (`STUDENT_ID`),
  CONSTRAINT `fk_STUDENT_DISABILITY_TYPE_STUDENT1` FOREIGN KEY (`STUDENT_ID`) REFERENCES `STUDENT` (`STUDENT_ID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Table structure for table `DONATION`
--

CREATE TABLE `DONATION` (
  `DONATION_ID` int(11) NOT NULL AUTO_INCREMENT,
  `PURPOSE` varchar(45) NOT NULL,
  `AMOUNT` float DEFAULT NULL,
  `DATE` date DEFAULT NULL,
  `DONATION_TYPE_ID` int(11) NOT NULL,
  `MODIFIED_TIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`DONATION_ID`),
  KEY `fk_DONATION_DONATION_TYPE1` (`DONATION_TYPE_ID`),
  CONSTRAINT `fk_DONATION_DONATION_TYPE1` FOREIGN KEY (`DONATION_TYPE_ID`) REFERENCES `DONATION_TYPE` (`DONATION_TYPE_ID`) ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;

--
-- Table structure for table `STAFF_CATEGORY`
--

CREATE TABLE `STAFF_CATEGORY` (
  `STAFF_CATEGORY_ID` int(11) NOT NULL AUTO_INCREMENT,
  `DESCRIPTION` varchar(45) NOT NULL,
  `IS_ACADEMIC` tinyint(1) NOT NULL DEFAULT '1',
  `MODIFIED_TIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`STAFF_CATEGORY_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Table structure for table `MASTER_IN_CHARGE`
--

CREATE TABLE `MASTER_IN_CHARGE` (
  `MASTER_IN_CHARGE_ID` int(11) NOT NULL AUTO_INCREMENT,
  `STAFF_ID` int(11) NOT NULL,
  `CLUB_SOCIETY_ID` int(11) DEFAULT NULL,
  `SPORT_CATEGORY_ID` int(11) DEFAULT NULL,
  `HOUSE_ID` int(11) DEFAULT NULL,
  `MIC_FROM_DATE` date NOT NULL,
  `MIC_TO_DATE` date DEFAULT NULL,
  `NOTE` varchar(45) DEFAULT NULL,
  `MODIFIED_TIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`MASTER_IN_CHARGE_ID`),
  UNIQUE KEY `UC_MASTER_IN_CHARGE` (`STAFF_ID`,`CLUB_SOCIETY_ID`,`SPORT_CATEGORY_ID`,`HOUSE_ID`),
  KEY `fk_master_in_charge_staff1` (`STAFF_ID`),
  KEY `fk_master_in_charge_club_society1` (`CLUB_SOCIETY_ID`),
  KEY `fk_MASTER_IN_CHARGE_SPORT_CATEGORY1` (`SPORT_CATEGORY_ID`),
  KEY `fk_MASTER_IN_CHARGE_HOUSE1` (`HOUSE_ID`),
  CONSTRAINT `fk_master_in_charge_club_society1` FOREIGN KEY (`CLUB_SOCIETY_ID`) REFERENCES `CLUB_SOCIETY` (`CLUB_SOCIETY_ID`) ON UPDATE CASCADE ON DELETE RESTRICT,
  CONSTRAINT `fk_MASTER_IN_CHARGE_HOUSE1` FOREIGN KEY (`HOUSE_ID`) REFERENCES `HOUSE` (`HOUSE_ID`) ON UPDATE CASCADE ON DELETE RESTRICT,
  CONSTRAINT `fk_MASTER_IN_CHARGE_SPORT_CATEGORY1` FOREIGN KEY (`SPORT_CATEGORY_ID`) REFERENCES `SPORT_CATEGORY` (`SPORT_CATEGORY_ID`) ON UPDATE CASCADE ON DELETE RESTRICT,
  CONSTRAINT `fk_master_in_charge_staff1` FOREIGN KEY (`STAFF_ID`) REFERENCES `STAFF` (`STAFF_ID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Table structure for table `SCHOOL`
--

CREATE TABLE `SCHOOL` (
  `SCHOOL_ID` int(11) NOT NULL AUTO_INCREMENT,
  `NAME` varchar(45) NOT NULL,
  `PRINCIPAL` int(11) NOT NULL,
  `VICE_PRINCIPAL` int(11) NOT NULL,
  `ADDRESS` varchar(45) DEFAULT NULL,
  `CONTACT_NO` varchar(45) DEFAULT NULL,
  `FAX_NO` varchar(45) DEFAULT NULL,
  `WEB_SITE` varchar(45) DEFAULT NULL,
  `STARTED_DATE` date DEFAULT NULL,
  `DISTRICT_ID` int(11) NOT NULL,
  `PROVINCE_ID` int(11) NOT NULL,
  `NUMBER_OF_STAFF` int(11) NOT NULL,
  `NUMBER_OF_STUDENTS` int(11) DEFAULT NULL,
  `PRIMARY_AND_SECONDARY_SCHOOL_INFO` varchar(45) DEFAULT NULL,
  `FACILITIES` varchar(45) DEFAULT NULL,
  `EMAIL_ADDRESS` varchar(45) DEFAULT NULL,
  `MODIFIED_TIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`SCHOOL_ID`),
  KEY `fk_SCHOOL_DISTRICT1` (`DISTRICT_ID`),
  KEY `fk_SCHOOL_PROVINCE1` (`PROVINCE_ID`),
  KEY `fk_PRINCIPAL` (`PRINCIPAL`),
  KEY `fk_VICE_PRINCIPAL` (`VICE_PRINCIPAL`),
  CONSTRAINT `fk_PRINCIPAL` FOREIGN KEY (`PRINCIPAL`) REFERENCES `STAFF` (`STAFF_ID`) ON UPDATE CASCADE ON DELETE RESTRICT,
  CONSTRAINT `fk_SCHOOL_DISTRICT1` FOREIGN KEY (`DISTRICT_ID`) REFERENCES `DISTRICT` (`DISTRICT_ID`) ON UPDATE CASCADE ON DELETE RESTRICT,
  CONSTRAINT `fk_SCHOOL_PROVINCE1` FOREIGN KEY (`PROVINCE_ID`) REFERENCES `PROVINCE` (`PROVINCE_ID`) ON UPDATE CASCADE ON DELETE RESTRICT,
  CONSTRAINT `fk_VICE_PRINCIPAL` FOREIGN KEY (`VICE_PRINCIPAL`) REFERENCES `STAFF` (`STAFF_ID`) ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;

--
-- Table structure for table `STUDENT_TERM_MARK`
--

CREATE TABLE `STUDENT_TERM_MARK` (
  `STUDENT_TERM_MARKS_ID` int(11) NOT NULL AUTO_INCREMENT,
  `STUDENT_CLASS_INFO_ID` int(11) NOT NULL,
  `GRADE_SUBJECT_ID` int(11) NOT NULL,
  `TERM_ID` int(11) NOT NULL,
  `MARKS` float DEFAULT NULL,
  `COMMENTS` varchar(45) DEFAULT NULL,
  `MERIT_AWARDS_ID` int(11) DEFAULT NULL,
  `IS_ABSENT` tinyint(4) DEFAULT '0',
  `MODIFIED_TIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`STUDENT_TERM_MARKS_ID`),
  UNIQUE KEY `STUDENT_TERM_MARKS_ID_UNIQUE` (`STUDENT_TERM_MARKS_ID`),
  UNIQUE KEY `UC_STCLSINFO_GRDSUB_TERM` (`STUDENT_CLASS_INFO_ID`,`GRADE_SUBJECT_ID`,`TERM_ID`),
  KEY `fk_STUDENT_CLASS_INFO_GRADE_SUBJECT_GRADE_SUBJECT1` (`GRADE_SUBJECT_ID`),
  KEY `fk_STUDENT_CLASS_INFO_GRADE_SUBJECT_STUDENT_CLASS_INFO1` (`STUDENT_CLASS_INFO_ID`),
  KEY `fk_STUDENT_TERM_MARKS_TERM1` (`TERM_ID`),
  KEY `fk_STUDENT_TERM_MARKS_MERIT_AWARDS1` (`MERIT_AWARDS_ID`),
  CONSTRAINT `fk_STUDENT_CLASS_INFO_GRADE_SUBJECT_GRADE_SUBJECT1` FOREIGN KEY (`GRADE_SUBJECT_ID`) REFERENCES `GRADE_SUBJECT` (`GRADE_SUBJECT_ID`) ON UPDATE CASCADE ON DELETE RESTRICT,
  CONSTRAINT `fk_STUDENT_CLASS_INFO_GRADE_SUBJECT_STUDENT_CLASS_INFO1` FOREIGN KEY (`STUDENT_CLASS_INFO_ID`) REFERENCES `STUDENT_CLASS_INFO` (`STUDENT_CLASS_INFO_ID`) ON UPDATE CASCADE ON DELETE RESTRICT,
  CONSTRAINT `fk_STUDENT_TERM_MARKS_MERIT_AWARDS1` FOREIGN KEY (`MERIT_AWARDS_ID`) REFERENCES `MERIT_AWARD` (`MERIT_AWARDS_ID`) ON UPDATE CASCADE ON DELETE RESTRICT,
  CONSTRAINT `fk_STUDENT_TERM_MARKS_TERM1` FOREIGN KEY (`TERM_ID`) REFERENCES `TERM` (`TERM_ID`) ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE=InnoDB AUTO_INCREMENT=52 DEFAULT CHARSET=latin1;

--
-- Table structure for table `HOUSE`
--

CREATE TABLE `HOUSE` (
  `HOUSE_ID` int(11) NOT NULL AUTO_INCREMENT,
  `NAME` varchar(45) NOT NULL,
  `COLOUR` varchar(45) DEFAULT NULL,
  `DESCRIPTION` varchar(45) DEFAULT NULL,
  `CAPTAIN` int(11) DEFAULT NULL,
  `VICE_CAPTAIN` int(11) DEFAULT NULL,
  `MODIFIED_TIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`HOUSE_ID`),
  UNIQUE KEY `NAME_UNIQUE` (`NAME`),
  KEY `fk_HOUSE_STUDENT1` (`CAPTAIN`),
  KEY `fk_HOUSE_STUDENT2` (`VICE_CAPTAIN`),
  CONSTRAINT `fk_HOUSE_STUDENT1` FOREIGN KEY (`CAPTAIN`) REFERENCES `STUDENT` (`STUDENT_ID`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `fk_HOUSE_STUDENT2` FOREIGN KEY (`VICE_CAPTAIN`) REFERENCES `STUDENT` (`STUDENT_ID`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Table structure for table `PARENT_DONATION`
--

CREATE TABLE `PARENT_DONATION` (
  `PARENT_DONATION_ID` int(11) NOT NULL AUTO_INCREMENT,
  `PARENT_ID` int(11) NOT NULL,
  `DONATION_ID` int(11) NOT NULL,
  `MODIFIED_TIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`PARENT_DONATION_ID`),
  UNIQUE KEY `UC_PARENT_DONATION` (`PARENT_ID`,`DONATION_ID`),
  KEY `fk_PARENT_DONATION_DONATION1` (`DONATION_ID`),
  KEY `fk_PARENT_DONATION_PARENT1` (`PARENT_ID`),
  CONSTRAINT `fk_PARENT_DONATION_DONATION1` FOREIGN KEY (`DONATION_ID`) REFERENCES `DONATION` (`DONATION_ID`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_PARENT_DONATION_PARENT1` FOREIGN KEY (`PARENT_ID`) REFERENCES `PARENT` (`PARENT_ID`) ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Table structure for table `STUDENT`
--

CREATE TABLE `STUDENT` (
  `STUDENT_ID` int(11) NOT NULL AUTO_INCREMENT,
  `ADMISSION_NO` varchar(45) NOT NULL,
  `BLOOD_GROUP_ID` int(11) NOT NULL,
  `NATIONALITY_ID` int(11) NOT NULL,
  `RELIGION_ID` int(11) NOT NULL,
  `HOUSE_ID` int(11) NOT NULL,
  `TRAVEL_ID` int(11) NOT NULL,
  `LANGUAGE_ID` int(11) NOT NULL,
  `CITY_ID` int(11) NOT NULL,
  `ADMISSION_DATE` date NOT NULL,
  `FIRST_SCHOOL_DAY` date NOT NULL,
  `PHOTO` longblob,
  `FULL_NAME` varchar(150) DEFAULT NULL,
  `NAME_WT_INITIALS` varchar(45) NOT NULL,
  `LAST_NAME` varchar(45) NOT NULL,
  `NATIONAL_ID_NO` varchar(45) DEFAULT NULL,
  `DATE_OF_BIRTH` date NOT NULL,
  `ADDRESS` varchar(400) DEFAULT NULL,
  `TEMPORARY_ADDRESS` varchar(400) DEFAULT NULL,
  `TEMPORARY_CITY_ID` int(11) DEFAULT NULL,
  `MOBILE_NO` varchar(45) DEFAULT NULL,
  `RESIDENCE_NO` varchar(45) DEFAULT NULL,
  `EMAIL` varchar(45) DEFAULT NULL,
  `GENDER` char(1) NOT NULL,
  `IS_OLD_BOY` tinyint(4) NOT NULL,
  `PREVIOUS_SCHOOL_NAME` varchar(45) DEFAULT NULL,
  `PREVIOUS_SCHOOL_PASSED_GRADE` varchar(45) DEFAULT NULL,
  `PREVIOUS_SCHOOL_STUDIED_GRADE` varchar(45) DEFAULT NULL,
  `PREVIOUS_SCHOOL_STUDY_MEDIUM_ID` int(11) DEFAULT NULL,
  `PREVIOUS_SCHOOL_STUDIED_FROM_DATE` date DEFAULT NULL,
  `PREVIOUS_SCHOOL_STUDIED_TO_DATE` date DEFAULT NULL,
  `PREVIOUS_SCHOOL_REASON_FOR_LEAVE` varchar(45) DEFAULT NULL,
  `EMERGENCY_CONTACT_FULL_NAME` varchar(45) DEFAULT NULL,
  `EMERGENCY_CONTACT_NAME_WITH_INITIALS` varchar(45) DEFAULT NULL,
  `EMERGENCY_CONTACT_LAST_NAME` varchar(45) DEFAULT NULL,
  `EMERGENCY_CONTACT_RELATIONSHIP` varchar(45) DEFAULT NULL,
  `EMERGENCY_CONTACT_RESIDENCE_NO` varchar(45) DEFAULT NULL,
  `EMERGENCY_CONTACT_MOBILE_NO` varchar(45) DEFAULT NULL,
  `EMERGENCY_CONTACT_OFFICE_NO` varchar(45) DEFAULT NULL,
  `EMERGENCY_CONTACT_EMAIL_ADDRESS` varchar(45) DEFAULT NULL,
  `MODIFIED_TIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`STUDENT_ID`),
  UNIQUE KEY `ADMISSION_NO_UNIQUE` (`ADMISSION_NO`),
  KEY `fk_STUDENT_BLOOD_GROUP1` (`BLOOD_GROUP_ID`),
  KEY `fk_STUDENT_NATIONALITY1` (`NATIONALITY_ID`),
  KEY `fk_STUDENT_RELIGION1` (`RELIGION_ID`),
  KEY `fk_STUDENT_HOUSE1` (`HOUSE_ID`),
  KEY `fk_STUDENT_CITY1` (`CITY_ID`),
  KEY `fk_STUDENT_METHOD_OF_TRAVEL1` (`TRAVEL_ID`),
  KEY `fk_STUDENT_LANGUAGE1` (`LANGUAGE_ID`),
  KEY `fk_TEMPORARY_CITY_ID` (`TEMPORARY_CITY_ID`),
  KEY `fk_PREVIOUS_SCHOOL_STUDY_MEDIUM_ID` (`PREVIOUS_SCHOOL_STUDY_MEDIUM_ID`),
  CONSTRAINT `fk_PREVIOUS_SCHOOL_STUDY_MEDIUM_ID` FOREIGN KEY (`PREVIOUS_SCHOOL_STUDY_MEDIUM_ID`) REFERENCES `LANGUAGE` (`LANGUAGE_ID`) ON UPDATE CASCADE ON DELETE RESTRICT,
  CONSTRAINT `fk_STUDENT_BLOOD_GROUP1` FOREIGN KEY (`BLOOD_GROUP_ID`) REFERENCES `BLOOD_GROUP` (`BLOOD_GROUP_ID`) ON UPDATE CASCADE ON DELETE RESTRICT,
  CONSTRAINT `fk_STUDENT_CITY1` FOREIGN KEY (`CITY_ID`) REFERENCES `CITY` (`CITY_ID`) ON UPDATE CASCADE ON DELETE RESTRICT,
  CONSTRAINT `fk_STUDENT_HOUSE1` FOREIGN KEY (`HOUSE_ID`) REFERENCES `HOUSE` (`HOUSE_ID`) ON UPDATE CASCADE ON DELETE RESTRICT,
  CONSTRAINT `fk_STUDENT_LANGUAGE1` FOREIGN KEY (`LANGUAGE_ID`) REFERENCES `LANGUAGE` (`LANGUAGE_ID`) ON UPDATE CASCADE ON DELETE RESTRICT,
  CONSTRAINT `fk_STUDENT_METHOD_OF_TRAVEL1` FOREIGN KEY (`TRAVEL_ID`) REFERENCES `METHOD_OF_TRAVEL` (`TRAVEL_ID`) ON UPDATE CASCADE ON DELETE RESTRICT,
  CONSTRAINT `fk_STUDENT_NATIONALITY1` FOREIGN KEY (`NATIONALITY_ID`) REFERENCES `NATIONALITY` (`NATIONALITY_ID`) ON UPDATE CASCADE ON DELETE RESTRICT,
  CONSTRAINT `fk_STUDENT_RELIGION1` FOREIGN KEY (`RELIGION_ID`) REFERENCES `RELIGION` (`RELIGION_ID`) ON UPDATE CASCADE ON DELETE RESTRICT,
  CONSTRAINT `fk_TEMPORARY_CITY_ID` FOREIGN KEY (`TEMPORARY_CITY_ID`) REFERENCES `CITY` (`CITY_ID`) ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Table structure for table `STUDENT_SPORT`
--

CREATE TABLE `STUDENT_SPORT` (
  `STUDENT_SPORT_ID` int(11) NOT NULL AUTO_INCREMENT,
  `STUDENT_ID` int(11) NOT NULL,
  `SPORT_CATEGORY_ID` int(11) NOT NULL,
  `YEAR` date NOT NULL,
  `POSITION_ID` int(11) NOT NULL,
  `STATUS` tinyint(4) DEFAULT NULL,
  `IS_SCHOOL_COLOUR` tinyint(1) DEFAULT NULL,
  `MODIFIED_TIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`STUDENT_SPORT_ID`),
  UNIQUE KEY `UC_STUDENT_SPORT` (`STUDENT_ID`,`SPORT_CATEGORY_ID`,`YEAR`),
  KEY `fk_STUDENT_SPORT_STUDENT1` (`STUDENT_ID`),
  KEY `fk_STUDENT_SPORT_POSITION1` (`POSITION_ID`),
  KEY `fk_STUDENT_SPORT_SPORT_CATEGORY1` (`SPORT_CATEGORY_ID`),
  CONSTRAINT `fk_STUDENT_SPORT_POSITION1` FOREIGN KEY (`POSITION_ID`) REFERENCES `POSITION` (`POSITION_ID`) ON UPDATE CASCADE ON DELETE RESTRICT,
  CONSTRAINT `fk_STUDENT_SPORT_SPORT_CATEGORY1` FOREIGN KEY (`SPORT_CATEGORY_ID`) REFERENCES `SPORT_CATEGORY` (`SPORT_CATEGORY_ID`) ON UPDATE CASCADE ON DELETE RESTRICT,
  CONSTRAINT `fk_STUDENT_SPORT_STUDENT1` FOREIGN KEY (`STUDENT_ID`) REFERENCES `STUDENT` (`STUDENT_ID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Table structure for table `CHAPTER`
--

CREATE TABLE `CHAPTER` (
  `CHAPTER_ID` int(11) NOT NULL AUTO_INCREMENT,
  `GRADE_SUBJECT_ID` int(11) NOT NULL,
  `TERM_ID` int(11) NOT NULL,
  `CHAPTER_NO` int(11) NOT NULL,
  `MODIFIED_TIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`CHAPTER_ID`),
  KEY `fk_CHAPTER_GRADE_SUBJECT1` (`GRADE_SUBJECT_ID`),
  KEY `fk_CHAPTER_TERM1` (`TERM_ID`),
  CONSTRAINT `fk_CHAPTER_GRADE_SUBJECT1` FOREIGN KEY (`GRADE_SUBJECT_ID`) REFERENCES `GRADE_SUBJECT` (`GRADE_SUBJECT_ID`) ON UPDATE CASCADE ON DELETE RESTRICT,
  CONSTRAINT `fk_CHAPTER_TERM1` FOREIGN KEY (`TERM_ID`) REFERENCES `TERM` (`TERM_ID`) ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Table structure for table `STAFF_PROFESSIONAL`
--

CREATE TABLE `STAFF_PROFESSIONAL` (
  `STAFF_PROFESSIONAL_ID` int(11) NOT NULL AUTO_INCREMENT,
  `STAFF_ID` int(11) NOT NULL,
  `PROFESSIONAL_QUAL_ID` int(11) NOT NULL,
  `DESCRIPTION` varchar(45) DEFAULT NULL,
  `FROM_YEAR` date DEFAULT NULL,
  `TO_YEAR` date DEFAULT NULL,
  `EXPERIENCE` varchar(45) DEFAULT NULL,
  `RESPONSIBILITIES` varchar(45) DEFAULT NULL,
  `MODIFIED_TIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`STAFF_PROFESSIONAL_ID`),
  UNIQUE KEY `UC_STAFF_PROFESSIONAL` (`STAFF_ID`,`PROFESSIONAL_QUAL_ID`),
  KEY `fk_STAFF_PROFESSIONAL_QUALIFICATION_PROFESSIONAL_QUALIFICATION1` (`PROFESSIONAL_QUAL_ID`),
  KEY `fk_STAFF_PROFESSIONAL_QUALIFICATION_STAFF1` (`STAFF_ID`),
  CONSTRAINT `fk_STAFF_PROFESSIONAL_QUALIFICATION_PROFESSIONAL_QUALIFICATION1` FOREIGN KEY (`PROFESSIONAL_QUAL_ID`) REFERENCES `PROFESSIONAL_QUALIFICATION` (`PROFESSIONAL_QUAL_ID`) ON UPDATE CASCADE ON DELETE RESTRICT,
  CONSTRAINT `fk_STAFF_PROFESSIONAL_QUALIFICATION_STAFF1` FOREIGN KEY (`STAFF_ID`) REFERENCES `STAFF` (`STAFF_ID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Table structure for table `CLASS_TEACHER`
--

CREATE TABLE `CLASS_TEACHER` (
  `CLASS_TEACHER_ID` int(11) NOT NULL AUTO_INCREMENT,
  `CLASS_GRADE_ID` int(11) NOT NULL,
  `STAFF_ID` int(11) NOT NULL,
  `YEAR` date NOT NULL,
  `MODIFIED_TIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`CLASS_TEACHER_ID`),
  UNIQUE KEY `UC_CLASS_TEACHER` (`CLASS_GRADE_ID`,`STAFF_ID`,`YEAR`),
  KEY `fk_CLASS_GRADE_STAFF_STAFF1` (`STAFF_ID`),
  KEY `fk_CLASS_GRADE_STAFF_CLASS_GRADE1` (`CLASS_GRADE_ID`),
  CONSTRAINT `fk_CLASS_GRADE_STAFF_CLASS_GRADE1` FOREIGN KEY (`CLASS_GRADE_ID`) REFERENCES `CLASS_GRADE` (`CLASS_GRADE_ID`) ON UPDATE CASCADE ON DELETE RESTRICT,
  CONSTRAINT `fk_CLASS_GRADE_STAFF_STAFF1` FOREIGN KEY (`STAFF_ID`) REFERENCES `STAFF` (`STAFF_ID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Table structure for table `STUDENT_LEAVE`
--

CREATE TABLE `STUDENT_LEAVE` (
  `STUDENT_LEAVE_ID` int(11) NOT NULL,
  `STUDENT_ID` int(11) NOT NULL,
  `FROM_DATE` date NOT NULL,
  `TO_DATE` date NOT NULL,
  `REASON` varchar(45) DEFAULT NULL,
  `MODIFIED_TIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`STUDENT_LEAVE_ID`),
  KEY `fk_STUDENT_LEAVE_STUDENT1` (`STUDENT_ID`),
  CONSTRAINT `fk_STUDENT_LEAVE_STUDENT1` FOREIGN KEY (`STUDENT_ID`) REFERENCES `STUDENT` (`STUDENT_ID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Table structure for table `WARNING_LEVEL`
--

CREATE TABLE `WARNING_LEVEL` (
  `WARNING_LEVEL_ID` int(11) NOT NULL AUTO_INCREMENT,
  `DESCRIPTION` varchar(45) NOT NULL,
  `COLOUR` varchar(45) NOT NULL,
  `MODIFIED_TIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`WARNING_LEVEL_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Table structure for table `SUB_TERM`
--

CREATE TABLE `SUB_TERM` (
  `SUB_TERM_ID` int(11) NOT NULL AUTO_INCREMENT,
  `TERM_ID` int(11) NOT NULL,
  `DESCRIPTION` varchar(45) NOT NULL,
  `FROM_MONTH` date NOT NULL,
  `TO_MONTH` date NOT NULL,
  `MODIFIED_TIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`SUB_TERM_ID`),
  KEY `fk_SUB_TERM_TERM` (`TERM_ID`),
  CONSTRAINT `fk_SUB_TERM_TERM` FOREIGN KEY (`TERM_ID`) REFERENCES `TERM` (`TERM_ID`) ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Table structure for table `TERM`
--

CREATE TABLE `TERM` (
  `TERM_ID` int(11) NOT NULL AUTO_INCREMENT,
  `DESCRIPTION` varchar(45) NOT NULL,
  `FROM_MONTH` date NOT NULL,
  `TO_MONTH` date NOT NULL,
  `MODIFIED_TIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`TERM_ID`),
  UNIQUE KEY `DESCRIPTION_UNIQUE` (`DESCRIPTION`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

--
-- Table structure for table `LANGUAGE`
--

CREATE TABLE `LANGUAGE` (
  `LANGUAGE_ID` int(11) NOT NULL AUTO_INCREMENT,
  `LANGUAGE` varchar(45) NOT NULL,
  `MODIFIED_TIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`LANGUAGE_ID`),
  UNIQUE KEY `LANGUAGE_UNIQUE` (`LANGUAGE`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;

--
-- Table structure for table `STUDENT_PARENT`
--

CREATE TABLE `STUDENT_PARENT` (
  `STUDENT_PARENT_ID` int(11) NOT NULL AUTO_INCREMENT,
  `STUDENT_ID` int(11) NOT NULL,
  `PARENT_ID` int(11) NOT NULL,
  `RELATIONSHIP` char(1) NOT NULL COMMENT 'F- FATHER\nM-MOTHER\nG-GUARDIAN',
  `MODIFIED_TIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`STUDENT_PARENT_ID`),
  UNIQUE KEY `UC_STUDENT_PARENT` (`STUDENT_ID`,`PARENT_ID`),
  KEY `fk_STUDENT_PARENT_PARENT1` (`PARENT_ID`),
  KEY `fk_STUDENT_PARENT_STUDENT1` (`STUDENT_ID`),
  CONSTRAINT `fk_STUDENT_PARENT_PARENT1` FOREIGN KEY (`PARENT_ID`) REFERENCES `PARENT` (`PARENT_ID`) ON UPDATE CASCADE ON DELETE RESTRICT,
  CONSTRAINT `fk_STUDENT_PARENT_STUDENT1` FOREIGN KEY (`STUDENT_ID`) REFERENCES `STUDENT` (`STUDENT_ID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Table structure for table `STUDENT_SUB_TERM_MARK`
--

CREATE TABLE `STUDENT_SUB_TERM_MARK` (
  `STUDENT_SUB_TERM_MARK_ID` int(11) NOT NULL AUTO_INCREMENT,
  `STUDENT_TERM_MARKS_ID` int(11) NOT NULL,
  `SUB_TERM_ID` int(11) NOT NULL,
  `GRADING_ID` int(11) DEFAULT NULL,
  `IS_ABSENT` tinyint(4) DEFAULT NULL,
  `MODIFIED_TIME` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`STUDENT_SUB_TERM_MARK_ID`),
  UNIQUE KEY `UC_STUDENT_SUB_TERM_MARK` (`STUDENT_TERM_MARKS_ID`,`SUB_TERM_ID`,`GRADING_ID`),
  KEY `fk_STUDENT_SUB_TERM_MARK_STUDENT_TERM_MARK1` (`STUDENT_TERM_MARKS_ID`),
  KEY `fk_STUDENT_SUB_TERM_MARK_SUB_TERM1` (`SUB_TERM_ID`),
  KEY `fk_STUDENT_SUB_TERM_MARK_GRADING1` (`GRADING_ID`),
  CONSTRAINT `fk_STUDENT_SUB_TERM_MARK_GRADING1` FOREIGN KEY (`GRADING_ID`) REFERENCES `GRADING` (`GRADING_ID`) ON UPDATE CASCADE ON DELETE RESTRICT,
  CONSTRAINT `fk_STUDENT_SUB_TERM_MARK_STUDENT_TERM_MARK1` FOREIGN KEY (`STUDENT_TERM_MARKS_ID`) REFERENCES `STUDENT_TERM_MARK` (`STUDENT_TERM_MARKS_ID`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_STUDENT_SUB_TERM_MARK_SUB_TERM1` FOREIGN KEY (`SUB_TERM_ID`) REFERENCES `SUB_TERM` (`SUB_TERM_ID`) ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Table structure for table `POSITION`
--

CREATE TABLE `POSITION` (
  `POSITION_ID` int(11) NOT NULL AUTO_INCREMENT,
  `DESCRIPTION` varchar(45) NOT NULL,
  `MODIFIED_TIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`POSITION_ID`),
  UNIQUE KEY `DESCRIPTION_UNIQUE` (`DESCRIPTION`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;

--
-- Table structure for table `SPORT`
--

CREATE TABLE `SPORT` (
  `SPORT_ID` int(11) NOT NULL AUTO_INCREMENT,
  `DESCRIPTION` varchar(45) NOT NULL,
  `MODIFIED_TIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`SPORT_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Table structure for table `CLASS`
--

CREATE TABLE `CLASS` (
  `CLASS_ID` int(11) NOT NULL AUTO_INCREMENT,
  `DESCRIPTION` varchar(45) NOT NULL,
  `MODIFIED_TIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`CLASS_ID`),
  UNIQUE KEY `DESCRIPTION_UNIQUE` (`DESCRIPTION`)
) ENGINE=InnoDB AUTO_INCREMENT=40 DEFAULT CHARSET=latin1;

--
-- Table structure for table `ACHIEVEMENT`
--

CREATE TABLE `ACHIEVEMENT` (
  `ACHIEVEMENT_ID` int(11) NOT NULL AUTO_INCREMENT,
  `DESCRIPTION` varchar(400) NOT NULL,
  `STUDENT_ID` int(11) NOT NULL,
  `ACTIVITY` varchar(45) DEFAULT NULL,
  `YEAR` date DEFAULT NULL,
  `CLUB_SOCIETY_ID` int(11) DEFAULT NULL,
  `IS_ACADEMIC` tinyint(1) NOT NULL,
  `MODIFIED_TIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`ACHIEVEMENT_ID`),
  KEY `fk_ACHIEVEMENTS_STUDENT1` (`STUDENT_ID`),
  KEY `fk_ACHIEVEMENT_CLUB_SOCIETY1` (`CLUB_SOCIETY_ID`),
  CONSTRAINT `fk_ACHIEVEMENTS_STUDENT1` FOREIGN KEY (`STUDENT_ID`) REFERENCES `STUDENT` (`STUDENT_ID`) ON UPDATE CASCADE ON DELETE RESTRICT,
  CONSTRAINT `fk_ACHIEVEMENT_CLUB_SOCIETY1` FOREIGN KEY (`CLUB_SOCIETY_ID`) REFERENCES `CLUB_SOCIETY` (`CLUB_SOCIETY_ID`) ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Table structure for table `SECTION_HEAD`
--

CREATE TABLE `SECTION_HEAD` (
  `SECTION_HEAD_ID` int(11) NOT NULL AUTO_INCREMENT,
  `STAFF_ID` int(11) NOT NULL,
  `START_DATE` date NOT NULL,
  `GRADE_ID` int(11) DEFAULT NULL,
  `END_DATE` date DEFAULT NULL,
  `NOTES` varchar(45) DEFAULT NULL,
  `SECTION` varchar(45) DEFAULT NULL,
  `MODIFIED_TIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`SECTION_HEAD_ID`),
  UNIQUE KEY `UC_SECTION_HEAD` (`STAFF_ID`,`START_DATE`),
  KEY `fk_SECTION_HEAD_STAFF1` (`STAFF_ID`),
  CONSTRAINT `fk_SECTION_HEAD_STAFF1` FOREIGN KEY (`STAFF_ID`) REFERENCES `STAFF` (`STAFF_ID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=latin1;

--
-- Table structure for table `PARENT`
--

CREATE TABLE `PARENT` (
  `PARENT_ID` int(11) NOT NULL AUTO_INCREMENT,
  `IS_TEACHER` tinyint(1) NOT NULL DEFAULT '0',
  `IS_PAST_PUPIL` tinyint(1) NOT NULL DEFAULT '0',
  `FULL_NAME` varchar(150) DEFAULT NULL,
  `NAME_WT_INITIALS` varchar(45) NOT NULL,
  `LAST_NAME` varchar(45) NOT NULL,
  `NATIONAL_ID_NO` varchar(45) NOT NULL,
  `ADDRESS` varchar(400) DEFAULT NULL,
  `MOBILE_NO` varchar(45) DEFAULT NULL,
  `RESIDENCE_NO` varchar(45) DEFAULT NULL,
  `OFFICE_NO` varchar(45) DEFAULT NULL,
  `EMAIL` varchar(45) DEFAULT NULL,
  `RELATIONSHIP` varchar(45) NOT NULL,
  `CITY_ID` int(11) DEFAULT NULL,
  `TEACHER_REG_NO` varchar(45) DEFAULT NULL,
  `MODIFIED_TIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `RELIGION_ID` int(11) NOT NULL,
  `WORKING_SEGMENT_ID` int(11) DEFAULT NULL,
  `ALUMINI_NO` int(11) DEFAULT NULL,
  `INCOME_LEVEL` varchar(45) DEFAULT NULL,
  `TEMP_ADDRESS` varchar(400) DEFAULT NULL,
  `TEMP_CITY_ID` int(11) DEFAULT NULL,
  `OFFICE_NAME` varchar(45) DEFAULT NULL,
  `OFFICE_ADDRESS` varchar(400) DEFAULT NULL,
  `OFFICE_CITY_ID` int(11) DEFAULT NULL,
  `OFFICE_FAX_NO` varchar(45) DEFAULT NULL,
  `OFFICE_EMAIL_ADDRESS` varchar(45) DEFAULT NULL,
  `DESIGNATION` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`PARENT_ID`),
  UNIQUE KEY `NATIONAL_ID_NO_UNIQUE` (`NATIONAL_ID_NO`),
  KEY `fk_PARENT_CITY1` (`CITY_ID`),
  KEY `fk_PARENT_STAFF1` (`TEACHER_REG_NO`),
  KEY `fk_PARENT_RELIGION1` (`RELIGION_ID`),
  KEY `fk_TEMP_CITY_ID` (`TEMP_CITY_ID`),
  KEY `fk_OFFICE_CITY_ID` (`OFFICE_CITY_ID`),
  KEY `fk_PARENT_WORKING_SEGMENT1` (`WORKING_SEGMENT_ID`),
  CONSTRAINT `fk_OFFICE_CITY_ID` FOREIGN KEY (`OFFICE_CITY_ID`) REFERENCES `CITY` (`CITY_ID`) ON UPDATE CASCADE ON DELETE RESTRICT,
  CONSTRAINT `fk_PARENT_CITY1` FOREIGN KEY (`CITY_ID`) REFERENCES `CITY` (`CITY_ID`) ON UPDATE CASCADE ON DELETE RESTRICT,
  CONSTRAINT `fk_PARENT_RELIGION1` FOREIGN KEY (`RELIGION_ID`) REFERENCES `RELIGION` (`RELIGION_ID`) ON UPDATE CASCADE ON DELETE RESTRICT,
  CONSTRAINT `fk_PARENT_STAFF1` FOREIGN KEY (`TEACHER_REG_NO`) REFERENCES `STAFF` (`REGISTRATION_NO`) ON UPDATE CASCADE ON DELETE RESTRICT,
  CONSTRAINT `fk_PARENT_WORKING_SEGMENT1` FOREIGN KEY (`WORKING_SEGMENT_ID`) REFERENCES `WORKING_SEGMENT` (`WORKING_SEGMENT_ID`) ON UPDATE CASCADE ON DELETE RESTRICT,
  CONSTRAINT `fk_TEMP_CITY_ID` FOREIGN KEY (`TEMP_CITY_ID`) REFERENCES `CITY` (`CITY_ID`) ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

--
-- Table structure for table `SUBJECT_TEACHER`
--

CREATE TABLE `SUBJECT_TEACHER` (
  `SUBJECT_TEACHER_ID` int(11) NOT NULL AUTO_INCREMENT,
  `STAFF_ID` int(11) NOT NULL,
  `GRADE_SUBJECT_ID` int(11) NOT NULL,
  `CLASS_ID` int(11) NOT NULL,
  `MODIFIED_TIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`SUBJECT_TEACHER_ID`),
  UNIQUE KEY `UC_SUBJECT_TEACHER` (`STAFF_ID`,`GRADE_SUBJECT_ID`,`CLASS_ID`),
  KEY `fk_STAFF_GRADE_SUBJECT_GRADE_SUBJECT1` (`GRADE_SUBJECT_ID`),
  KEY `fk_STAFF_GRADE_SUBJECT_STAFF1` (`STAFF_ID`),
  KEY `fk_SUBJECT_TEACHER_CLASS1` (`CLASS_ID`),
  CONSTRAINT `fk_STAFF_GRADE_SUBJECT_STAFF1` FOREIGN KEY (`STAFF_ID`) REFERENCES `STAFF` (`STAFF_ID`) ON UPDATE CASCADE ON DELETE RESTRICT,
  CONSTRAINT `fk_STAFF_GRADE_SUBJECT_GRADE_SUBJECT1` FOREIGN KEY (`GRADE_SUBJECT_ID`) REFERENCES `GRADE_SUBJECT` (`GRADE_SUBJECT_ID`) ON UPDATE CASCADE ON DELETE RESTRICT,
  CONSTRAINT `fk_SUBJECT_TEACHER_CLASS1` FOREIGN KEY (`CLASS_ID`) REFERENCES `CLASS` (`CLASS_ID`) ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Table structure for table `STUDENT_SCHOLARSHIP`
--

CREATE TABLE `STUDENT_SCHOLARSHIP` (
  `STUDENT_SCHOLARSHIP_ID` int(11) NOT NULL AUTO_INCREMENT,
  `STUDENT_ID` int(11) NOT NULL,
  `SCHOLARSHIP_ID` int(11) NOT NULL,
  `YEAR` date NOT NULL,
  `COMMENTS` varchar(150) DEFAULT NULL,
  `MODIFIED_TIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`STUDENT_SCHOLARSHIP_ID`),
  UNIQUE KEY `UC_STUDENT_SCHOLARSHIP` (`STUDENT_ID`,`SCHOLARSHIP_ID`,`YEAR`),
  KEY `fk_STUDENT_SCHOLARSHIP_SCHOLARSHIP1` (`SCHOLARSHIP_ID`),
  KEY `fk_STUDENT_SCHOLARSHIP_STUDENT1` (`STUDENT_ID`),
  CONSTRAINT `fk_STUDENT_SCHOLARSHIP_STUDENT1` FOREIGN KEY (`STUDENT_ID`) REFERENCES `STUDENT` (`STUDENT_ID`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_STUDENT_SCHOLARSHIP_SCHOLARSHIP1` FOREIGN KEY (`SCHOLARSHIP_ID`) REFERENCES `SCHOLARSHIP` (`SCHOLARSHIP_ID`) ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Table structure for table `USER_ROLE`
--

CREATE TABLE `USER_ROLE` (
  `USER_ROLE_ID` int(11) NOT NULL AUTO_INCREMENT,
  `ROLE` varchar(45) NOT NULL,
  `DESCRIPTION` varchar(100) DEFAULT NULL,
  `MODIFIED_TIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`USER_ROLE_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Table structure for table `SCHOLARSHIP`
--

CREATE TABLE `SCHOLARSHIP` (
  `SCHOLARSHIP_ID` int(11) NOT NULL AUTO_INCREMENT,
  `NAME` varchar(45) NOT NULL,
  `CONDITIONS` varchar(250) DEFAULT NULL,
  `NO_OF_STUDENTS` int(11) DEFAULT NULL,
  `MODIFIED_TIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`SCHOLARSHIP_ID`),
  UNIQUE KEY `NAME_UNIQUE` (`NAME`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;

--
-- Table structure for table `GENERAL_LOG`
--

CREATE TABLE `GENERAL_LOG` (
  `event_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `user_host` mediumtext NOT NULL,
  `thread_id` int(11) NOT NULL,
  `server_id` int(10) unsigned NOT NULL,
  `command_type` varchar(64) NOT NULL,
  `argument` mediumtext NOT NULL
) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='General log';

--
-- Table structure for table `CITY`
--

CREATE TABLE `CITY` (
  `CITY_ID` int(11) NOT NULL AUTO_INCREMENT,
  `DESCRIPTION` varchar(45) NOT NULL,
  `DISTRICT_ID` int(11) NOT NULL,
  `MODIFIED_TIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`CITY_ID`),
  UNIQUE KEY `DESCRIPTION_UNIQUE` (`DESCRIPTION`),
  KEY `fk_CITY_DISTRICT1` (`DISTRICT_ID`),
  CONSTRAINT `fk_CITY_DISTRICT1` FOREIGN KEY (`DISTRICT_ID`) REFERENCES `DISTRICT` (`DISTRICT_ID`) ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;

--
-- Table structure for table `DISTRICT`
--

CREATE TABLE `DISTRICT` (
  `DISTRICT_ID` int(11) NOT NULL AUTO_INCREMENT,
  `DESCRIPTION` varchar(45) NOT NULL,
  `PROVINCE_ID` int(11) NOT NULL,
  `MODIFIED_TIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`DISTRICT_ID`),
  UNIQUE KEY `DESCRIPTION_UNIQUE` (`DESCRIPTION`),
  KEY `fk_DISTRICT_PROVINCE1` (`PROVINCE_ID`),
  CONSTRAINT `fk_DISTRICT_PROVINCE1` FOREIGN KEY (`PROVINCE_ID`) REFERENCES `PROVINCE` (`PROVINCE_ID`) ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;

--
-- Table structure for table `CLUB_SOCIETY`
--

CREATE TABLE `CLUB_SOCIETY` (
  `CLUB_SOCIETY_ID` int(11) NOT NULL AUTO_INCREMENT,
  `NAME` varchar(45) NOT NULL,
  `DESCRIPTION` varchar(45) DEFAULT NULL,
  `MODIFIED_TIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`CLUB_SOCIETY_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Table structure for table `GRADE`
--

CREATE TABLE `GRADE` (
  `GRADE_ID` int(11) NOT NULL AUTO_INCREMENT,
  `DESCRIPTION` varchar(45) NOT NULL,
  `MODIFIED_TIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`GRADE_ID`),
  UNIQUE KEY `DESCRIPTION` (`DESCRIPTION`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Table structure for table `STAFF_EDUCATIONAL`
--

CREATE TABLE `STAFF_EDUCATIONAL` (
  `STAFF_EDUCATIONAL_ID` int(11) NOT NULL AUTO_INCREMENT,
  `STAFF_ID` int(11) NOT NULL,
  `EDUCATIONAL_QUAL_ID` int(11) NOT NULL,
  `DESCRIPTION` varchar(45) DEFAULT NULL,
  `YEAR` date DEFAULT NULL,
  `STATUS` tinyint(4) DEFAULT NULL,
  `EXPERIENCE` varchar(45) DEFAULT NULL,
  `MODIFIED_TIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`STAFF_EDUCATIONAL_ID`),
  UNIQUE KEY `UC_STAFF_EDUCATIONAL` (`STAFF_ID`,`EDUCATIONAL_QUAL_ID`),
  KEY `fk_STAFF_EDUCATIONAL_QUALIFICATION_EDUCATIONAL_QUALIFICATION1` (`EDUCATIONAL_QUAL_ID`),
  KEY `fk_STAFF_EDUCATIONAL_QUALIFICATION_STAFF1` (`STAFF_ID`),
  CONSTRAINT `fk_STAFF_EDUCATIONAL_QUALIFICATION_EDUCATIONAL_QUALIFICATION1` FOREIGN KEY (`EDUCATIONAL_QUAL_ID`) REFERENCES `EDUCATIONAL_QUALIFICATION` (`EDUCATIONAL_QUAL_ID`) ON UPDATE CASCADE ON DELETE RESTRICT,
  CONSTRAINT `fk_STAFF_EDUCATIONAL_QUALIFICATION_STAFF1` FOREIGN KEY (`STAFF_ID`) REFERENCES `STAFF` (`STAFF_ID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Table structure for table `WORKING_SEGMENT`
--

CREATE TABLE `WORKING_SEGMENT` (
  `WORKING_SEGMENT_ID` int(11) NOT NULL AUTO_INCREMENT,
  `DESCRIPTION` varchar(45) NOT NULL,
  `MODIFIED_TIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`WORKING_SEGMENT_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Table structure for table `STUDENT_CLUB_SOCIETY`
--

CREATE TABLE `STUDENT_CLUB_SOCIETY` (
  `STUDENT_CLUB_SOCIETY_ID` int(11) NOT NULL AUTO_INCREMENT,
  `STUDENT_ID` int(11) NOT NULL,
  `CLUB_SOCIETY_ID` int(11) NOT NULL,
  `YEAR` date NOT NULL,
  `POSITION_ID` int(11) NOT NULL,
  `MEMBERSHIP_NO` varchar(45) DEFAULT NULL,
  `STATUS` tinyint(4) DEFAULT NULL,
  `MODIFIED_TIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`STUDENT_CLUB_SOCIETY_ID`),
  UNIQUE KEY `UC_STUDENT_CLUB_SOCIETY` (`STUDENT_ID`,`CLUB_SOCIETY_ID`,`YEAR`),
  KEY `fk_STUDENT_CLUB_SOCIETIES_STUDENT1` (`STUDENT_ID`),
  KEY `fk_STUDENT_CLUB_SOCIETIES_POSITION1` (`POSITION_ID`),
  KEY `fk_STUDENT_CLUB_SOCIETIES_CLUB_SOCIETIES1` (`CLUB_SOCIETY_ID`),
  CONSTRAINT `fk_STUDENT_CLUB_SOCIETIES_STUDENT1` FOREIGN KEY (`STUDENT_ID`) REFERENCES `STUDENT` (`STUDENT_ID`) ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT `fk_STUDENT_CLUB_SOCIETIES_CLUB_SOCIETIES1` FOREIGN KEY (`CLUB_SOCIETY_ID`) REFERENCES `CLUB_SOCIETY` (`CLUB_SOCIETY_ID`) ON UPDATE CASCADE ON DELETE RESTRICT,
  CONSTRAINT `fk_STUDENT_CLUB_SOCIETIES_POSITION1` FOREIGN KEY (`POSITION_ID`) REFERENCES `POSITION` (`POSITION_ID`) ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Table structure for table `STUDENT_DISCIPLINE`
--

CREATE TABLE `STUDENT_DISCIPLINE` (
  `STUDENT_DISCIPLINE_ID` int(11) NOT NULL AUTO_INCREMENT,
  `STUDENT_ID` int(11) NOT NULL,
  `WARNING_LEVEL_ID` int(11) NOT NULL,
  `STAFF_ID` int(11) DEFAULT NULL,
  `COMMENT` varchar(45) DEFAULT NULL,
  `IS_INFORMED_TO_PARENT` tinyint(1) NOT NULL DEFAULT '0',
  `DATE` date NOT NULL,
  `MODIFIED_TIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`STUDENT_DISCIPLINE_ID`),
  KEY `fk_STUDENT_DESCIPLINE_STUDENT1` (`STUDENT_ID`),
  KEY `fk_STUDENT_DESCIPLINE_WARNING_LEVEL1` (`WARNING_LEVEL_ID`),
  KEY `fk_STUDENT_DESCIPLINE_STAFF1` (`STAFF_ID`),
  CONSTRAINT `fk_STUDENT_DESCIPLINE_STAFF1` FOREIGN KEY (`STAFF_ID`) REFERENCES `STAFF` (`STAFF_ID`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `fk_STUDENT_DESCIPLINE_STUDENT1` FOREIGN KEY (`STUDENT_ID`) REFERENCES `STUDENT` (`STUDENT_ID`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_STUDENT_DESCIPLINE_WARNING_LEVEL1` FOREIGN KEY (`WARNING_LEVEL_ID`) REFERENCES `WARNING_LEVEL` (`WARNING_LEVEL_ID`) ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Table structure for table `CLASS_GRADE`
--

CREATE TABLE `CLASS_GRADE` (
  `CLASS_GRADE_ID` int(11) NOT NULL AUTO_INCREMENT,
  `CLASS_ID` int(11) NOT NULL,
  `GRADE_ID` int(11) NOT NULL,
  `DESCRIPTION` varchar(45) DEFAULT NULL,
  `MODIFIED_TIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`CLASS_GRADE_ID`),
  UNIQUE KEY `CLASS_GRADE_ID_UNIQUE` (`CLASS_GRADE_ID`),
  UNIQUE KEY `UC_CLASS_GRADE` (`CLASS_ID`,`GRADE_ID`),
  UNIQUE KEY `DESCRIPTION_UNIQUE` (`DESCRIPTION`),
  KEY `fk_CLASS_GRADE_GRADE1` (`GRADE_ID`),
  KEY `fk_CLASS_GRADE_CLASS` (`CLASS_ID`),
  CONSTRAINT `fk_CLASS_GRADE_CLASS` FOREIGN KEY (`CLASS_ID`) REFERENCES `CLASS` (`CLASS_ID`) ON UPDATE CASCADE ON DELETE RESTRICT,
  CONSTRAINT `fk_CLASS_GRADE_GRADE1` FOREIGN KEY (`GRADE_ID`) REFERENCES `GRADE` (`GRADE_ID`) ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Table structure for table `STUDENT_CLASS_INFO`
--

CREATE TABLE `STUDENT_CLASS_INFO` (
  `STUDENT_CLASS_INFO_ID` int(11) NOT NULL AUTO_INCREMENT,
  `STUDENT_ID` int(11) NOT NULL,
  `CLASS_GRADE_ID` int(11) NOT NULL,
  `YEAR` date NOT NULL,
  `IS_MONITOR` tinyint(1) NOT NULL DEFAULT '0',
  `MODIFIED_TIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`STUDENT_CLASS_INFO_ID`),
  UNIQUE KEY `STUDENT_CLASS_INFO_ID_UNIQUE` (`STUDENT_CLASS_INFO_ID`),
  UNIQUE KEY `UC_STUDENT_CLSGRD_YEAR` (`STUDENT_ID`,`CLASS_GRADE_ID`,`YEAR`),
  KEY `fk_STUDENT_CLASS_GRADE_CLASS_GRADE1` (`CLASS_GRADE_ID`),
  KEY `fk_STUDENT_CLASS_GRADE_STUDENT1` (`STUDENT_ID`),
  CONSTRAINT `fk_STUDENT_CLASS_GRADE_CLASS_GRADE1` FOREIGN KEY (`CLASS_GRADE_ID`) REFERENCES `CLASS_GRADE` (`CLASS_GRADE_ID`) ON DELETE RESTRICT,
  CONSTRAINT `fk_STUDENT_CLASS_GRADE_STUDENT1` FOREIGN KEY (`STUDENT_ID`) REFERENCES `STUDENT` (`STUDENT_ID`) ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Table structure for table `STAFF`
--

CREATE TABLE `STAFF` (
  `STAFF_ID` int(11) NOT NULL AUTO_INCREMENT,
  `STAFF_CATEGORY_ID` int(11) NOT NULL,
  `REGISTRATION_NO` varchar(45) NOT NULL,
  `DATE_OF_HIRE` date NOT NULL,
  `DATE_OF_DEPARTURE` date DEFAULT NULL,
  `BASIC_SALARY` float DEFAULT NULL,
  `SALARY_SCALE` varchar(45) DEFAULT NULL,
  `HISTORY` varchar(45) DEFAULT NULL,
  `OTHER_INFO` varchar(45) DEFAULT NULL,
  `FULL_NAME` varchar(150) NOT NULL,
  `NAME_WT_INITIALS` varchar(45) DEFAULT NULL,
  `LAST_NAME` varchar(45) NOT NULL,
  `NATIONAL_ID_NO` varchar(45) NOT NULL,
  `DOB` date NOT NULL,
  `ADDRESS` varchar(400) NOT NULL,
  `MOBILE_NO` varchar(45) DEFAULT NULL,
  `PHOTO` longblob,
  `RESIDENCE_NO` varchar(45) DEFAULT NULL,
  `OFFICE_NO` varchar(45) DEFAULT NULL,
  `EMAIL` varchar(45) DEFAULT NULL,
  `GENDER` char(1) NOT NULL,
  `CITY_ID` int(11) NOT NULL,
  `RELIGION_ID` int(11) DEFAULT NULL,
  `TEMP_CITY_ID` int(11) DEFAULT NULL,
  `TEMP_ADDRESS` varchar(400) DEFAULT NULL,
  `MODIFIED_TIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`STAFF_ID`),
  UNIQUE KEY `REGISTRATION_NO_UNIQUE` (`REGISTRATION_NO`),
  UNIQUE KEY `NATIONAL_ID_NO_UNIQUE` (`NATIONAL_ID_NO`),
  KEY `fk_STAFF_STAFF_CATEGORY1` (`STAFF_CATEGORY_ID`),
  KEY `fk_STAFF_CITY1` (`CITY_ID`),
  KEY `fk_STAFF_RELIGION1` (`RELIGION_ID`),
  KEY `fk_STAFF_TEMP_CITY_ID` (`TEMP_CITY_ID`),
  CONSTRAINT `fk_STAFF_CITY1` FOREIGN KEY (`CITY_ID`) REFERENCES `CITY` (`CITY_ID`) ON UPDATE CASCADE ON DELETE RESTRICT,
  CONSTRAINT `fk_STAFF_RELIGION1` FOREIGN KEY (`RELIGION_ID`) REFERENCES `RELIGION` (`RELIGION_ID`) ON UPDATE CASCADE ON DELETE RESTRICT,
  CONSTRAINT `fk_STAFF_STAFF_CATEGORY1` FOREIGN KEY (`STAFF_CATEGORY_ID`) REFERENCES `STAFF_CATEGORY` (`STAFF_CATEGORY_ID`) ON UPDATE CASCADE ON DELETE RESTRICT,
  CONSTRAINT `fk_STAFF_TEMP_CITY_ID` FOREIGN KEY (`TEMP_CITY_ID`) REFERENCES `CITY` (`CITY_ID`) ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE=InnoDB AUTO_INCREMENT=28 DEFAULT CHARSET=latin1;

--
-- Table structure for table `SUBJECT`
--

CREATE TABLE `SUBJECT` (
  `SUBJECT_ID` int(11) NOT NULL AUTO_INCREMENT,
  `DESCRIPTION` varchar(45) NOT NULL,
  `MODIFIED_TIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `STREAM_ID` int(11) DEFAULT NULL,
  PRIMARY KEY (`SUBJECT_ID`),
  UNIQUE KEY `DESCRIPTION_UNIQUE` (`DESCRIPTION`),
  KEY `fk_SUBJECT_STREAM1` (`STREAM_ID`),
  CONSTRAINT `fk_SUBJECT_STREAM1` FOREIGN KEY (`STREAM_ID`) REFERENCES `STREAM` (`STREAM_ID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

