use `akura`;
/*!40014 SET UNIQUE_CHECKS=0 */;
/*!40014 SET FOREIGN_KEY_CHECKS=0 */;

--
-- Table structure for table `BUSINESS_AUDIT`
--
CREATE  TABLE IF NOT EXISTS `BUSINESS_AUDIT` (

  `BUSINESS_AUDIT_ID` INT(11) NOT NULL AUTO_INCREMENT,

  `USER_LOGIN_ID` INT(11) NOT NULL ,

  `DATE` DATETIME NOT NULL ,

  `BUSINESS_FUNCTION` VARCHAR(200) NOT NULL ,

  `MODULE` VARCHAR(200) NOT NULL ,

  `EVENT_TYPE` VARCHAR(200) NOT NULL ,

  `MODIFIED_TIME` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ,

  PRIMARY KEY (`BUSINESS_AUDIT_ID`) ,

  CONSTRAINT `fk_BUSSINESS_AUDIT_USER_LOGIN1`

    FOREIGN KEY (`USER_LOGIN_ID` )

    REFERENCES `USER_LOGIN` (`USER_LOGIN_ID` )

    ON DELETE CASCADE

    ON UPDATE CASCADE)

ENGINE = InnoDB

DEFAULT CHARACTER SET = latin1

COLLATE = latin1_swedish_ci;

--
-- Table structure for table `BUSINESS_AUDIT_DETAILS`
--

CREATE  TABLE IF NOT EXISTS `BUSINESS_AUDIT_DETAILS` (

  `BUSINESS_AUDIT_DETAILS_ID` INT(11) NOT NULL AUTO_INCREMENT,

  `BUSINESS_AUDIT_ID` INT(11) NOT NULL ,

  `ATTRIBUTE_NAME` VARCHAR(200) NOT NULL ,

  `AUDIT_DESCRIPTION` VARCHAR(1000) NOT NULL ,

  `MODIFIED_TIME` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ,

  PRIMARY KEY (`BUSINESS_AUDIT_DETAILS_ID`) ,

  CONSTRAINT `fk_BUSSINESS_AUDIT_DETAILS_BUSSINESS_AUDIT1`

    FOREIGN KEY (`BUSINESS_AUDIT_ID` )

    REFERENCES `BUSINESS_AUDIT` (`BUSINESS_AUDIT_ID` )

    ON DELETE CASCADE

    ON UPDATE CASCADE)

ENGINE = InnoDB

DEFAULT CHARACTER SET = latin1

COLLATE = latin1_swedish_ci;

--
-- Table structure for table `SUBJECT_TEACHER`
--
ALTER TABLE `SUBJECT_TEACHER` 
ADD COLUMN `YEAR` VARCHAR(45) NOT NULL ,
DROP INDEX `UC_SUBJECT_TEACHER` , 
ADD UNIQUE INDEX `UC_SUBJECT_TEACHER` (`STAFF_ID` ASC, `GRADE_SUBJECT_ID` ASC, `CLASS_ID` ASC, `YEAR` ASC) ;

--
-- Table structure for table `EXAM`
--
CREATE  TABLE IF NOT EXISTS `EXAM` (

  `EXAM_ID` INT(11) NOT NULL AUTO_INCREMENT,

  `DESCRIPTION` VARCHAR(45) NOT NULL ,

  `MARK_TYPE` TINYINT(4) NOT NULL ,
  `GRADE_ID` INT(11) NOT NULL ,

  `MODIFIED_TIME` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ,

  PRIMARY KEY (`EXAM_ID`) ,
  UNIQUE INDEX `DESCRIPTION_UNIQUE` (`DESCRIPTION` ASC) ,
  CONSTRAINT `fk_EXAM_GRADE1`

    FOREIGN KEY (`GRADE_ID` )

    REFERENCES `GRADE` (`GRADE_ID` )

    ON DELETE RESTRICT

    ON UPDATE CASCADE) 

ENGINE = InnoDB

DEFAULT CHARACTER SET = latin1

COLLATE = latin1_swedish_ci;

--
-- Table structure for table `ASSIGNMENT`
--
CREATE  TABLE IF NOT EXISTS `ASSIGNMENT` (

  `ASSIGNMENT_ID` INT(11) NOT NULL AUTO_INCREMENT,

  `GRADE_SUBJECT_ID` INT(11) NOT NULL ,

  `NAME` VARCHAR(45) NOT NULL ,

  `DATE` DATE NOT NULL ,

  `DESCRIPTION` VARCHAR(45) NULL DEFAULT NULL ,
  `IS_MARKS` tinyint(4) NOT NULL DEFAULT '0' ,

  `MODIFIED_TIME` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ,

  PRIMARY KEY (`ASSIGNMENT_ID`) ,

  CONSTRAINT `fk_ASSIGNMENT_GRADE_SUBJECT`

    FOREIGN KEY (`GRADE_SUBJECT_ID` )

    REFERENCES `GRADE_SUBJECT` (`GRADE_SUBJECT_ID` )

    ON DELETE CASCADE

    ON UPDATE CASCADE)

ENGINE = InnoDB

DEFAULT CHARACTER SET = latin1

COLLATE = latin1_swedish_ci;

--
-- Table structure for table `EXAM_SUBJECT`
--
CREATE  TABLE IF NOT EXISTS `EXAM_SUBJECT` (

  `EXAM_SUBJECT_ID` INT(11) NOT NULL AUTO_INCREMENT,

  `EXAM_ID` INT(11) NOT NULL ,

  `SUBJECT_ID` INT(11) NOT NULL ,

  `IS_OPTIONAL_SUBJECT` TINYINT(4) NOT NULL ,

  `MODIFIED_TIME` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ,

  PRIMARY KEY (`EXAM_SUBJECT_ID`) ,

  CONSTRAINT `fk_EXAM_SUBJECT_EXAM1`

    FOREIGN KEY (`EXAM_ID` )

    REFERENCES `EXAM` (`EXAM_ID` )

    ON DELETE CASCADE

    ON UPDATE CASCADE,

  CONSTRAINT `fk_EXAM_SUBJECT_SUBJECT1`

    FOREIGN KEY (`SUBJECT_ID` )

    REFERENCES `SUBJECT` (`SUBJECT_ID` )

    ON DELETE CASCADE

    ON UPDATE CASCADE)

ENGINE = InnoDB

DEFAULT CHARACTER SET = latin1

COLLATE = latin1_swedish_ci;

--
-- Table structure for table `EXAM_MARKS`
--
CREATE  TABLE IF NOT EXISTS `EXAM_MARKS` (

  `EXAM_MARKS_ID` INT(11) NOT NULL AUTO_INCREMENT,

  `EXAM_SUBJECT_ID` INT(11) NOT NULL ,

  `YEAR` DATE NOT NULL ,

  `STUDENT_ID` INT(11) NOT NULL ,

  `MARKS` FLOAT(11) NULL DEFAULT NULL ,

  `GRADING_ID` INT(11) NULL DEFAULT NULL ,
  `STATUS` INT(11) NOT NULL ,
  `IS_OPTIONAL` TINYINT(4) NOT NULL ,
  `IS_ABSENT` TINYINT(4) NOT NULL ,

  `MODIFIED_TIME` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ,

  PRIMARY KEY (`EXAM_MARKS_ID`) ,

  CONSTRAINT `fk_EXAM_MARKS_EXAM_SUBJECT1`

    FOREIGN KEY (`EXAM_SUBJECT_ID` )

    REFERENCES `EXAM_SUBJECT` (`EXAM_SUBJECT_ID` )

    ON DELETE CASCADE

    ON UPDATE CASCADE,

  CONSTRAINT `fk_EXAM_MARKS_STUDENT1`

    FOREIGN KEY (`STUDENT_ID` )

    REFERENCES `STUDENT` (`STUDENT_ID` )

    ON DELETE CASCADE

    ON UPDATE CASCADE,

  CONSTRAINT `fk_EXAM_MARKS_GRADING1`

    FOREIGN KEY (`GRADING_ID` )

    REFERENCES `GRADING` (`GRADING_ID` )

    ON DELETE CASCADE

    ON UPDATE CASCADE)

ENGINE = InnoDB

DEFAULT CHARACTER SET = latin1

COLLATE = latin1_swedish_ci;

--
-- Table structure for table `COUNTRY`
--
CREATE  TABLE IF NOT EXISTS `COUNTRY` (

  `COUNTRY_ID` INT(11) NOT NULL AUTO_INCREMENT ,

  `COUNTRY_NAME` VARCHAR(45) NOT NULL ,

  `MODIFIED_TIME` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ,

  PRIMARY KEY (`COUNTRY_ID`) )

ENGINE = InnoDB

DEFAULT CHARACTER SET = latin1

COLLATE = latin1_swedish_ci;

--
-- Table structure for table `RACE`
--

CREATE TABLE `RACE` (
`RACE_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 (`RACE_ID`) 
)
ENGINE = InnoDB

DEFAULT CHARACTER SET = latin1

COLLATE = latin1_swedish_ci;

--
-- Table structure for table `STUDENT`
--
ALTER TABLE `STUDENT` 
CHANGE COLUMN `BLOOD_GROUP_ID` `BLOOD_GROUP_ID` INT(11) NULL ,
CHANGE COLUMN `NATIONALITY_ID` `NATIONALITY_ID` INT(11) NULL  , 
CHANGE COLUMN `RELIGION_ID` `RELIGION_ID` INT(11) NULL  , 
CHANGE COLUMN `HOUSE_ID` `HOUSE_ID` INT(11) NULL  , 
CHANGE COLUMN `TRAVEL_ID` `TRAVEL_ID` INT(11) NULL  , 
CHANGE COLUMN `LANGUAGE_ID` `LANGUAGE_ID` INT(11) NOT NULL  , 
CHANGE COLUMN `CITY_ID` `CITY_ID` INT(11) NULL  ,
CHANGE COLUMN `ADMISSION_DATE` `ADMISSION_DATE` DATE NULL  , 
CHANGE COLUMN `DATE_OF_BIRTH` `DATE_OF_BIRTH` DATE NULL  ,
CHANGE COLUMN `IS_OLD_BOY` `IS_OLD_BOY` TINYINT(4) DEFAULT '0' , 
CHANGE COLUMN `PREVIOUS_SCHOOL_NAME` `PREVIOUS_SCHOOL_NAME` VARCHAR(250) NULL DEFAULT NULL ,
ADD COLUMN `COUNTRY_ID` INT(11) DEFAULT NULL, 
ADD COLUMN `RACE_ID` INT(11) DEFAULT NULL, 
ADD COLUMN `PARISH_NAME` VARCHAR(45) DEFAULT NULL, 
ADD COLUMN `IS_SIBLINGS` TINYINT(4) DEFAULT '0', 
ADD COLUMN `BRO_ADMISSION_NO` VARCHAR(45) DEFAULT NULL,
ADD CONSTRAINT `fk_STUDENT_COUNTRY1`

  FOREIGN KEY (`COUNTRY_ID` )

  REFERENCES `COUNTRY` (`COUNTRY_ID` )

  ON DELETE NO ACTION

  ON UPDATE CASCADE,  
ADD CONSTRAINT `fk_STUDENT_RACE1`

  FOREIGN KEY (`RACE_ID` )

  REFERENCES `RACE` (`RACE_ID` )

  ON DELETE RESTRICT

  ON UPDATE CASCADE;

--
-- Table structure for table `SECTION`
--
CREATE TABLE `SECTION` (
`SECTION_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 (`SECTION_ID`)
)
ENGINE = InnoDB

DEFAULT CHARACTER SET = latin1

COLLATE = latin1_swedish_ci;

--
-- Table structure for table `STAFF_SERVICE_CATEGORY`
--

CREATE TABLE `STAFF_SERVICE_CATEGORY` (
`STAFF_SERVICE_CATEGORY_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 (`STAFF_SERVICE_CATEGORY_ID`)
)
ENGINE = InnoDB

DEFAULT CHARACTER SET = latin1

COLLATE = latin1_swedish_ci;

--
-- Table structure for table `APPOINTMENT_CLASSIFICATION`
--

CREATE TABLE `APPOINTMENT_CLASSIFICATION` (
`APPOINTMENT_CLASSIFICATION_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 (`APPOINTMENT_CLASSIFICATION_ID`)
)
ENGINE = InnoDB

DEFAULT CHARACTER SET = latin1

COLLATE = latin1_swedish_ci;


--
-- Table structure for table `APPOINTMENT_NATURE`
--
CREATE TABLE `APPOINTMENT_NATURE` (
`APPOINTMENT_NATURE_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 (`APPOINTMENT_NATURE_ID`)
)
ENGINE = InnoDB

DEFAULT CHARACTER SET = latin1

COLLATE = latin1_swedish_ci;

--
-- Table structure for table `CIVIL_STATUS`
--
CREATE TABLE `CIVIL_STATUS` (
`CIVIL_STATUS_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 (`CIVIL_STATUS_ID`) 
)
ENGINE = InnoDB

DEFAULT CHARACTER SET = latin1

COLLATE = latin1_swedish_ci;


--
-- Table structure for table `STAFF`
--
ALTER TABLE `STAFF` 
CHANGE COLUMN `CITY_ID` `CITY_ID` INT(11) NULL, 
CHANGE COLUMN `DOB` `DOB` DATE NULL  , 
CHANGE COLUMN `ADDRESS` `ADDRESS` VARCHAR(400) NULL  , 
CHANGE COLUMN `GENDER` `GENDER` CHAR(1) NULL ,
ADD COLUMN `SECTION_ID` INT(11) DEFAULT NULL, 
ADD COLUMN `STAFF_SERVICE_ID` INT(11) DEFAULT NULL,
ADD COLUMN `CLASSIFICATION_ID` INT(11) DEFAULT NULL,
ADD COLUMN `NATURE_ID` INT(11) DEFAULT NULL,
ADD COLUMN `CIVIL_STATUS_ID` INT(11) DEFAULT NULL,
ADD COLUMN `RACE_ID` INT(11) DEFAULT NULL,
ADD COLUMN `DATE_FIRST_APPOINTMENT` DATE DEFAULT NULL,
ADD COLUMN `STAFF_GRADE` varchar(45) DEFAULT NULL,
ADD COLUMN `STAFF_CLASS` varchar(45) DEFAULT NULL, 
ADD COLUMN `COUNTRY_ID` INT(11) DEFAULT NULL,   
ADD CONSTRAINT `fk_STAFF_SECTION1`

  FOREIGN KEY (`SECTION_ID` )

  REFERENCES `SECTION` (`SECTION_ID` )

  ON DELETE RESTRICT

  ON UPDATE CASCADE, 

ADD CONSTRAINT `fk_STAFF_SERVICE_CATEGORY1`

  FOREIGN KEY (`STAFF_SERVICE_ID` )

  REFERENCES `STAFF_SERVICE_CATEGORY` (`STAFF_SERVICE_CATEGORY_ID` )

  ON DELETE RESTRICT

  ON UPDATE CASCADE,

ADD CONSTRAINT `fk_STAFF_APPOINTMENT_CLASSIFICATION1`

  FOREIGN KEY (`CLASSIFICATION_ID` )

  REFERENCES `APPOINTMENT_CLASSIFICATION` (`APPOINTMENT_CLASSIFICATION_ID` )

  ON DELETE RESTRICT

  ON UPDATE CASCADE, 

ADD CONSTRAINT `fk_STAFF_APPOINTMENT_NATURE1`

  FOREIGN KEY (`NATURE_ID` )

  REFERENCES `APPOINTMENT_NATURE` (`APPOINTMENT_NATURE_ID` )

  ON DELETE RESTRICT

  ON UPDATE CASCADE, 

ADD CONSTRAINT `fk_STAFF_CIVIL_STATUS1`

  FOREIGN KEY (`CIVIL_STATUS_ID` )

  REFERENCES `CIVIL_STATUS` (`CIVIL_STATUS_ID` )

  ON DELETE RESTRICT

  ON UPDATE CASCADE,   
ADD CONSTRAINT `fk_STAFF_RACE1`

  FOREIGN KEY (`RACE_ID` )

  REFERENCES `RACE` (`RACE_ID` )

  ON DELETE RESTRICT

  ON UPDATE CASCADE,
ADD CONSTRAINT `fk_STAFF_COUNTRY1`

  FOREIGN KEY (`COUNTRY_ID` )

  REFERENCES `COUNTRY` (`COUNTRY_ID` )

  ON DELETE NO ACTION

  ON UPDATE CASCADE;


--
-- Table structure for table `CITY`
--
ALTER TABLE `CITY` 
CHANGE COLUMN `DISTRICT_ID` `DISTRICT_ID` INT(11) NULL ;

--
-- Table structure for table `DISTRICT`
--
ALTER TABLE `DISTRICT` 
CHANGE COLUMN `PROVINCE_ID` `PROVINCE_ID` INT(11) NULL ;

--
-- Table structure for table `MODULE`
--
CREATE  TABLE IF NOT EXISTS `MODULE` (

  `MODULE_ID` INT(11) NOT NULL AUTO_INCREMENT,

  `NAME` VARCHAR(45) NOT NULL ,

  `MODIFIED_TIME` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ,

  PRIMARY KEY (`MODULE_ID`) )

ENGINE = InnoDB

DEFAULT CHARACTER SET = latin1

COLLATE = latin1_swedish_ci;

--
-- Table structure for table `TAB`
--
CREATE  TABLE IF NOT EXISTS `TAB` (

  `TAB_ID` INT(11) NOT NULL AUTO_INCREMENT,

  `NAME` VARCHAR(45) NOT NULL ,

  `PARENT_TAB_ID` INT(11) NULL DEFAULT NULL ,

  `MODULE_ID` INT(11) NOT NULL ,
  `INDEX_NO` INT(11) NOT NULL ,

  `MODIFIED_TIME` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ,

  PRIMARY KEY (`TAB_ID`) ,

  CONSTRAINT `fk_TAB_MODULE1`

    FOREIGN KEY (`MODULE_ID` )

    REFERENCES `MODULE` (`MODULE_ID` )

    ON DELETE RESTRICT

    ON UPDATE CASCADE)

ENGINE = InnoDB

DEFAULT CHARACTER SET = latin1

COLLATE = latin1_swedish_ci;

--
-- Table structure for table `PAGE`
--
CREATE  TABLE IF NOT EXISTS `PAGE` (

  `PAGE_ID` INT(11) NOT NULL AUTO_INCREMENT,

  `NAME` VARCHAR(45) NOT NULL ,

  `URL` VARCHAR(45) NOT NULL ,

  `TAB_ID` INT(11) NULL DEFAULT NULL ,
  `INDEX_NO` INT(11) NOT NULL ,

  `MODIFIED_TIME` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ,

  PRIMARY KEY (`PAGE_ID`) ,

  CONSTRAINT `fk_PAGE_TAB1`

    FOREIGN KEY (`TAB_ID` )

    REFERENCES `TAB` (`TAB_ID` )

    ON DELETE RESTRICT

    ON UPDATE CASCADE)

ENGINE = InnoDB

DEFAULT CHARACTER SET = latin1

COLLATE = latin1_swedish_ci;

--
-- Table structure for table `PRIVILEGE`
--
CREATE  TABLE IF NOT EXISTS `PRIVILEGE` (

  `PRIVILEGE_ID` INT(11) NOT NULL AUTO_INCREMENT,

  `NAME` VARCHAR(150) NOT NULL ,

  `PAGE_ID` INT(11) NOT NULL ,

  `MODIFIED_TIME` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ,

  PRIMARY KEY (`PRIVILEGE_ID`) ,

  CONSTRAINT `fk_PRIVILEGE_PAGE1`

    FOREIGN KEY (`PAGE_ID` )

    REFERENCES `PAGE` (`PAGE_ID` )

    ON DELETE RESTRICT

    ON UPDATE CASCADE)

ENGINE = InnoDB

DEFAULT CHARACTER SET = latin1

COLLATE = latin1_swedish_ci;

--
-- Table structure for table `ROLE_TAB`
--
CREATE  TABLE IF NOT EXISTS `ROLE_TAB` (

  `ROLE_TAB_ID` INT(11) NOT NULL AUTO_INCREMENT,

  `TAB_ID` INT(11) NOT NULL ,

  `USER_ROLE_ID` INT(11) NOT NULL ,

  `MODIFIED_TIME` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ,

  PRIMARY KEY (`ROLE_TAB_ID`) ,

  CONSTRAINT `fk_ROLE_TAB_TAB`

    FOREIGN KEY (`TAB_ID` )

    REFERENCES `TAB` (`TAB_ID` )

    ON DELETE RESTRICT

    ON UPDATE CASCADE,

  CONSTRAINT `fk_ROLE_TAB_USER_ROLE1`

    FOREIGN KEY (`USER_ROLE_ID` )

    REFERENCES `USER_ROLE` (`USER_ROLE_ID` )

    ON DELETE RESTRICT

    ON UPDATE CASCADE)

ENGINE = InnoDB

DEFAULT CHARACTER SET = latin1

COLLATE = latin1_swedish_ci;

--
-- Table structure for table `ROLE_PIVILEGE`
--
CREATE  TABLE IF NOT EXISTS `ROLE_PRIVILEGE` (

  `ROLE_PRIVILEGE_ID` INT(11) NOT NULL AUTO_INCREMENT,

  `USER_ROLE_ID` INT(11) NOT NULL ,

  `PRIVILEGE_ID` INT(11) NOT NULL ,

  `MODIFIED_TIME` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ,

  PRIMARY KEY (`ROLE_PRIVILEGE_ID`) ,

  CONSTRAINT `fk_ROLE_PRIVILEGE_USER_ROLE1`

    FOREIGN KEY (`USER_ROLE_ID` )

    REFERENCES `USER_ROLE` (`USER_ROLE_ID` )

    ON DELETE RESTRICT

    ON UPDATE CASCADE,

  CONSTRAINT `fk_ROLE_PRIVILEGE_PRIVILEGE1`

    FOREIGN KEY (`PRIVILEGE_ID` )

    REFERENCES `PRIVILEGE` (`PRIVILEGE_ID` )

    ON DELETE RESTRICT

    ON UPDATE CASCADE)

ENGINE = InnoDB

DEFAULT CHARACTER SET = latin1

COLLATE = latin1_swedish_ci;

--
-- Table structure for table `STUDENT_TERM_MARK`
--
ALTER TABLE `STUDENT_TERM_MARK` 
ADD COLUMN `EDIT_MARK_STATE` TINYINT(4) DEFAULT '0'  ;

--
-- Table structure for table `STUDENT_SUB_TERM_MARK`
--
ALTER TABLE `STUDENT_SUB_TERM_MARK` 
ADD COLUMN `EDIT_MARK_STATE` TINYINT(4) DEFAULT '0'  ;

--
-- Table structure for table `EXAM_ADMISSIONS`
--
CREATE  TABLE IF NOT EXISTS `EXAM_ADMISSIONS` (

  `EXAM_ADMISSION_ID` INT(11) NOT NULL AUTO_INCREMENT,

  `STUDENT_ID` INT(11) NOT NULL ,

  `ADMISSION_NO` VARCHAR(45) NOT NULL ,

  `EXAM_ID` INT(11) NOT NULL ,
  `YEAR` DATE NOT NULL,

  `MODIFIED_TIME` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ,

  PRIMARY KEY (`EXAM_ADMISSION_ID`) ,

  CONSTRAINT `fk_EXAM_ADMISSION_STUDENT1`

    FOREIGN KEY (`STUDENT_ID` )

    REFERENCES `STUDENT` (`STUDENT_ID` )

    ON DELETE NO ACTION

    ON UPDATE NO ACTION,

  CONSTRAINT `fk_EXAM_ADMISSION_EXAM1`

    FOREIGN KEY (`EXAM_ID` )

    REFERENCES `EXAM` (`EXAM_ID` )

    ON DELETE NO ACTION

    ON UPDATE NO ACTION)

ENGINE = InnoDB

DEFAULT CHARACTER SET = latin1

COLLATE = latin1_swedish_ci;

--
-- Table structure for table `STUDENT_ASSIGNMENT_MARK`
--
DROP TABLE IF EXISTS `STUDENT_ASSIGNMENT_MARK`;
CREATE TABLE `STUDENT_ASSIGNMENT_MARK` (
  `STUDENT_ASSIGNMENT_MARKS_ID` int(11) NOT NULL AUTO_INCREMENT,
  `STUDENT_CLASS_INFO_ID` int(11) NOT NULL,
  `GRADE_SUBJECT_ID` int(11) NOT NULL,
  `ASSIGNMENT_ID` int(11) NOT NULL,
  `YEAR` DATE NOT NULL ,
  `GRADING_ID` int(11) DEFAULT NULL,
  `MARKS` float DEFAULT NULL,
  `IS_ABSENT` tinyint(4) DEFAULT '0',
  `MODIFIED_TIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`STUDENT_ASSIGNMENT_MARKS_ID`),
  CONSTRAINT `fk_STUDENT_ASSIGNMENT_MARK_STUDENT_GRADING1` FOREIGN KEY (`GRADING_ID`) REFERENCES `GRADING` (`GRADING_ID`) ON UPDATE CASCADE ON DELETE RESTRICT,
  CONSTRAINT `fk_STUDENT_ASSIGNMENT_MARK_ASSIGNMENT1` FOREIGN KEY (`ASSIGNMENT_ID`) REFERENCES `ASSIGNMENT` (`ASSIGNMENT_ID`) ON UPDATE CASCADE ON DELETE RESTRICT,
  CONSTRAINT `fk_STUDENT_ASSIGNMENT_MARK_GRADE_SUBJECT1` FOREIGN KEY (`GRADE_SUBJECT_ID`) REFERENCES `GRADE_SUBJECT` (`GRADE_SUBJECT_ID`) ON UPDATE CASCADE ON DELETE RESTRICT,
  CONSTRAINT `fk_STUDENT_ASSIGNMENT_MARK_STUDENT_CLASS_INFO1` FOREIGN KEY (`STUDENT_CLASS_INFO_ID`) REFERENCES `STUDENT_CLASS_INFO` (`STUDENT_CLASS_INFO_ID`) ON UPDATE CASCADE ON DELETE RESTRICT
)
ENGINE = InnoDB

DEFAULT CHARACTER SET = latin1

COLLATE = latin1_swedish_ci;

--
-- Table structure for table `EMPLOYMENT_STATUS`
--
CREATE  TABLE IF NOT EXISTS `EMPLOYMENT_STATUS` (

  `EMPLOYMENT_STATUS_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 (`EMPLOYMENT_STATUS_ID`) )
ENGINE = InnoDB

DEFAULT CHARACTER SET = latin1

COLLATE = latin1_swedish_ci;

--
-- Table structure for table `PARENT`
--
ALTER TABLE `PARENT`  
CHANGE COLUMN `RELIGION_ID` `RELIGION_ID` INT(11) NULL , 
CHANGE COLUMN `ALUMINI_NO` `ALUMINI_NO` VARCHAR(45) NULL , 
CHANGE COLUMN `EMAIL` `EMAIL` VARCHAR(100) NULL , 
ADD COLUMN `EMPLOYMENT_STATUS_ID` INT(11) DEFAULT NULL, 
ADD CONSTRAINT `fk_PARENT_EMPLOYMENT_STATUS1`

  FOREIGN KEY (`EMPLOYMENT_STATUS_ID` )

  REFERENCES `EMPLOYMENT_STATUS` (`EMPLOYMENT_STATUS_ID` )

  ON DELETE RESTRICT

  ON UPDATE CASCADE ;
 
--
-- Table structure for table `MARKING_FLAG`
--
CREATE  TABLE IF NOT EXISTS `MARKING_FLAG` (

  `MARKING_FLAG_ID` INT(11) NOT NULL AUTO_INCREMENT,

  `CLASS_GRADE_ID` INT(11) NOT NULL,

  `TERM_ID` INT(11) NOT NULL ,

  `YEAR` date NOT NULL,
  
  `MARKING_COMPLETED` TINYINT(4) NOT NULL ,
  `GRADE_ID` INT(11) NOT NULL ,
  `MODIFIED_TIME` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ,

  PRIMARY KEY (`MARKING_FLAG_ID`) ,
  CONSTRAINT `fk_MARKING_FLAG_CLASS_GRADE1` 
  FOREIGN KEY (`CLASS_GRADE_ID` )

  REFERENCES `CLASS_GRADE` (`CLASS_GRADE_ID` )

  ON DELETE NO ACTION

  ON UPDATE CASCADE, 

  CONSTRAINT `fk_MARKING_FLAG_TERM1`

  FOREIGN KEY (`TERM_ID` )

  REFERENCES `TERM` (`TERM_ID` )

  ON DELETE NO ACTION

  ON UPDATE CASCADE,
  CONSTRAINT `fk_MARKING_FLAG_GRADE`

  FOREIGN KEY (`GRADE_ID` )

  REFERENCES `GRADE` (`GRADE_ID` )

  ON DELETE NO ACTION

  ON UPDATE CASCADE )
ENGINE = InnoDB

DEFAULT CHARACTER SET = latin1

COLLATE = latin1_swedish_ci;

--
-- Table structure for table `SCHOOL`
--
ALTER TABLE `SCHOOL` 
ADD COLUMN `COUNTRY_ID` INT(11) DEFAULT NULL ,
ADD CONSTRAINT `fk_SCHOOL_COUNTRY1`

  FOREIGN KEY (`COUNTRY_ID` )

  REFERENCES `COUNTRY` (`COUNTRY_ID` )

  ON DELETE NO ACTION

  ON UPDATE CASCADE;

--
-- Table structure for table `USER_ROLE`
--
ALTER TABLE `USER_ROLE` 

ADD UNIQUE INDEX `ROLE_UNIQUE` (`ROLE` ASC) ;

ALTER TABLE `USER_ROLE` ADD COLUMN `IS_SYSTEM_ROLE` TINYINT(4) NOT NULL DEFAULT 1  ;

--
-- Table structure for table `PUBLICATION`
--
ALTER TABLE `PUBLICATION`  
CHANGE COLUMN `MESSAGE` `MESSAGE` VARCHAR(450) NOT NULL , 
DROP INDEX `HEADER_UNIQUE` ;

ALTER TABLE `PUBLICATION`  
ADD UNIQUE INDEX `HEADER_UNIQUE` (`HEADER` ASC, `EXPIRY_DATE` ASC) ;

--
-- Table structure for table `CLUB_SOCIETY`
--
ALTER TABLE `CLUB_SOCIETY` 
CHANGE COLUMN `DESCRIPTION` `DESCRIPTION` varchar(250) DEFAULT NULL ;


--
-- Table structure for table `STAFF`
--
ALTER TABLE `STAFF` 
CHANGE COLUMN `HISTORY` `HISTORY` varchar(250) DEFAULT NULL, 
CHANGE COLUMN `OTHER_INFO` `OTHER_INFO` varchar(250) DEFAULT NULL ;

--
-- Table structure for table `STAFF_EDUCATIONAL`
--
ALTER TABLE `STAFF_EDUCATIONAL` 
CHANGE COLUMN `DESCRIPTION` `DESCRIPTION` varchar(250) DEFAULT NULL ;

--
-- Table structure for table `STAFF_PROFESSIONAL`
--
ALTER TABLE `STAFF_PROFESSIONAL` 
CHANGE COLUMN `DESCRIPTION` `DESCRIPTION` varchar(250) DEFAULT NULL ;

/*!40014 SET FOREIGN_KEY_CHECKS=1 */;
/*!40014 SET UNIQUE_CHECKS=1 */;