/*
 * < À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.
 */

--
-- Current Database: `akura`
--


USE `akura`;

--
-- Table structure for table `SECURITY_QUESTIONS`
--

CREATE TABLE `SECURITY_QUESTIONS` (
  `SECURITY_QUESTIONS_ID` int(11) NOT NULL AUTO_INCREMENT,
  `SECURITY_QUESTION` varchar(100) NOT NULL,
  `MODIFIED_DATE` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`SECURITY_QUESTIONS_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `SECURITY_QUESTIONS`
--




--
-- Table structure for table `USER_SECURITY_QUESTIONS`
--

CREATE TABLE `USER_SECURITY_QUESTIONS` (
  `USER_SECURITY_QUESTIONS_ID` int(11) NOT NULL AUTO_INCREMENT,
  `USER_LOGIN_ID` int(11) NOT NULL,
  `SECURITY_QUESTION_ID` int(11) NOT NULL,
  `ANSWER` varchar(100) NOT NULL,
  `MODIFIED_DATE` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`USER_SECURITY_QUESTIONS_ID`),
  UNIQUE KEY `UC_USER_SECURITY_QUESTIONS` (`SECURITY_QUESTION_ID`,`USER_LOGIN_ID`),
  KEY `fk_USER_SECURITY_QUESTIONS_USER_LOGIN` (`USER_LOGIN_ID`),
  KEY `fk_USER_SECURITY_QUESTIONS_SECURITY_QUESTIONS` (`SECURITY_QUESTION_ID`),
  CONSTRAINT `fk_USER_SECURITY_QUESTIONS_USER_LOGIN` FOREIGN KEY (`USER_LOGIN_ID`) REFERENCES `USER_LOGIN` (`USER_LOGIN_ID`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_USER_SECURITY_QUESTIONS_SECURITY_QUESTIONS` FOREIGN KEY (`SECURITY_QUESTION_ID`) REFERENCES `SECURITY_QUESTIONS` (`SECURITY_QUESTIONS_ID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `USER_SECURITY_QUESTIONS`
--


--
-- Table structure for table `STAFF_LEAVE`
--

CREATE TABLE `STAFF_LEAVE` (
  `STAFF_LEAVE_ID` int(11) NOT NULL,
  `STAFF_ID` int(11) NOT NULL,
  `FROM_DATE` date NOT NULL,
  `TO_DATE` date NOT NULL,
  `REASON` varchar(400) DEFAULT NULL,
  `NO_OF_DAYS` int NOT NULL,
  `MODIFIED_TIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`STAFF_LEAVE_ID`),
  KEY `fk_STAFF_LEAVE_STAFF1` (`STAFF_ID`),
  CONSTRAINT `fk_STAFF_LEAVE_STAFF1` FOREIGN KEY (`STAFF_ID`) REFERENCES `STAFF` (`STAFF_ID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `STAFF_LEAVE`
--


--
-- Table structure for table `FAITH_LIFE_CATEGORY`
--

CREATE TABLE `FAITH_LIFE_CATEGORY` (
  `FAITH_LIFE_CATEGORY_ID` int(11) NOT NULL,
  `DESCRIPTION` varchar(400) NOT NULL,
  `MODIFIED_TIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`FAITH_LIFE_CATEGORY_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `FAITH_LIFE_CATEGORY`
--


--
-- Table structure for table `FAITH_LIFE_COMMENT`
--

CREATE TABLE `FAITH_LIFE_COMMENT` (
  `FAITH_LIFE_COMMENT_ID` int(11) NOT NULL,
  `DESCRIPTION` varchar(400) NOT NULL,
  `FAITH_LIFE_CATEGORY_ID` int(11) NOT NULL,
  `MODIFIED_TIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`FAITH_LIFE_COMMENT_ID`),
  KEY `fk_FAITH_LIFE_COMMENT_CATEGORY1` (`FAITH_LIFE_CATEGORY_ID`),
  CONSTRAINT `fk_FAITH_LIFE_COMMENT_CATEGORY1` FOREIGN KEY (`FAITH_LIFE_CATEGORY_ID`) REFERENCES `FAITH_LIFE_CATEGORY` (`FAITH_LIFE_CATEGORY_ID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `FAITH_LIFE_COMMENT`
--


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

ALTER TABLE `HOLIDAY` DROP COLUMN `DATE`, 
ADD COLUMN `START_DATE` date NOT NULL, 
ADD COLUMN `END_DATE` date NOT NULL;

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

ALTER TABLE `FAITH_LIFE_RATING` 
DROP COLUMN `FAITH_LIFE_VALUE`, 
DROP COLUMN `CATEGORY`;

ALTER TABLE `FAITH_LIFE_RATING` 
MODIFY COLUMN `FAITH_LIFE_DESCRIPTION` varchar(45) DEFAULT NULL, 
ADD COLUMN `FAITH_LIFE_COMMENT_ID` int(11) DEFAULT NULL, 
ADD COLUMN `GRADING_ID` int(11) NOT NULL,
ADD CONSTRAINT `fk_FAITH_LIFE_RATING_COMMENT1` FOREIGN KEY (`FAITH_LIFE_COMMENT_ID`) REFERENCES `FAITH_LIFE_COMMENT` (`FAITH_LIFE_COMMENT_ID`) ON UPDATE CASCADE ON DELETE RESTRICT, 
ADD CONSTRAINT `fk_FAITH_LIFE_RATING_GRADING1` FOREIGN KEY (`GRADING_ID`) REFERENCES `GRADING` (`GRADING_ID`) ON UPDATE CASCADE ON DELETE RESTRICT;


--
-- Table structure for table `GRADE_SUBJECT`
--
ALTER TABLE `GRADE_SUBJECT`
ADD COLUMN `IS_OPTIONAL_SUBJECT` tinyint(4) NOT NULL;

--
-- Table structure for table `PUBLICATION_TYPE`
--

CREATE TABLE `PUBLICATION_TYPE` (
  `PUBLICATION_TYPE_ID` int(11) NOT NULL,
  `TYPE` varchar(45) NOT NULL,
  `MODIFIED_TIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`PUBLICATION_TYPE_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `PUBLICATION_TYPE`
--


--
-- Table structure for table `publication`
--

CREATE TABLE `PUBLICATION` (
  `PUBLICATION_ID` int(11) NOT NULL,
  `HEADER` varchar(45) NOT NULL,
  `MESSAGE` varchar(45) NOT NULL,
  `EXPIRY_DATE` date NOT NULL,
  `IMAGE` longblob,
  `PUBLICATION_TYPE_ID` int(11) NOT NULL,
  `MODIFIED_TIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`PUBLICATION_ID`),
  UNIQUE KEY `HEADER_UNIQUE` (`HEADER`),
  KEY `fk_PUBLICATION_TYPE1` (`PUBLICATION_TYPE_ID`),
  CONSTRAINT `fk_PUBLICATION_TYPE1` FOREIGN KEY (`PUBLICATION_TYPE_ID`) REFERENCES `PUBLICATION_TYPE` (`PUBLICATION_TYPE_ID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `PUBLICATION`
--


--
-- Table structure for table `SEMINAR`
--
CREATE TABLE `SEMINAR` (
  `SEMINAR_ID` int(11) NOT NULL,
  `DESCRIPTION` varchar(200) NOT NULL,
  `MODIFIED_TIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`SEMINAR_ID`),
  UNIQUE KEY `description_UNIQUE` (`DESCRIPTION`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Table structure for table `STAFF_CLUB_SOCIETY`
--
CREATE TABLE `STAFF_CLUB_SOCIETY` (
  `STAFF_CLUB_SOCIETY_ID` int(11) NOT NULL,
  `STAFF_ID` int(11) NOT NULL,
  `CLUB_SOCIETY_ID` int(11) NOT NULL,
  `POSITION_ID` int(11) NOT NULL,
  `YEAR` date NOT NULL,
  `STATUS` tinyint(4) DEFAULT NULL,
  `MODIFIED_TIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`STAFF_CLUB_SOCIETY_ID`),
  KEY `fk_STAFF_CLUB_SOCIETY_STAFF1` (`STAFF_ID`),
  KEY `fk_STAFF_CLUB_SOCIETY_CLUB_SOCIETY1` (`CLUB_SOCIETY_ID`),
  KEY `fk_STAFF_CLUB_SOCIETY_POSITION1` (`POSITION_ID`),
  CONSTRAINT `fk_STAFF_CLUB_SOCIETY_CLUB_SOCIETY1` FOREIGN KEY (`CLUB_SOCIETY_ID`) REFERENCES `CLUB_SOCIETY` (`CLUB_SOCIETY_ID`) ON UPDATE CASCADE ON DELETE RESTRICT,
  CONSTRAINT `fk_STAFF_CLUB_SOCIETY_POSITION1` FOREIGN KEY (`POSITION_ID`) REFERENCES `POSITION` (`POSITION_ID`) ON UPDATE CASCADE ON DELETE RESTRICT,
  CONSTRAINT `fk_STAFF_CLUB_SOCIETY_STAFF1` FOREIGN KEY (`STAFF_ID`) REFERENCES `STAFF` (`STAFF_ID`) ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Table structure for table `STAFF_EXTERNAL_ACTIVITY`
--

CREATE TABLE `STAFF_EXTERNAL_ACTIVITY` (
  `STAFF_EXTERNAL_ACTIVITY_ID` int(11) NOT NULL,
  `STAFF_ID` int(11) NOT NULL,
  `EXTERNAL_ACTIVITY` varchar(250) NOT NULL,
  `ACHIEVEMENT` varchar(250) NOT NULL,
  `YEAR` date NOT NULL,
  `STATUS` tinyint(4) DEFAULT NULL,
  `MODIFIED_TIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`STAFF_EXTERNAL_ACTIVITY_ID`),
  KEY `FK_STAFF_EXTERNAL_ACTIVITY_STAFF1` (`STAFF_ID`),
  CONSTRAINT `FK_STAFF_EXTERNAL_ACTIVITY_STAFF1` FOREIGN KEY (`STAFF_ID`) REFERENCES `STAFF` (`STAFF_ID`) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Table structure for table `STAFF_SEMINAR`
--

CREATE TABLE `STAFF_SEMINAR` (
  `STAFF_SEMINAR_ID` int(11) NOT NULL,
  `STAFF_ID` int(11) NOT NULL,
  `SEMINAR_ID` int(11) NOT NULL,
  `DESCRIPTION` varchar(200) DEFAULT NULL,
  `YEAR` date NOT NULL,
  `STATUS` tinyint(4) DEFAULT NULL,
  `MODIFIED_TIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`STAFF_SEMINAR_ID`),
  KEY `fk_STAFF_SEMINAR_STAFF1` (`STAFF_ID`),
  KEY `fk_STAFF_SEMINAR_SEMINAR1` (`SEMINAR_ID`),
  CONSTRAINT `fk_STAFF_SEMINAR_STAFF1` FOREIGN KEY (`STAFF_ID`) REFERENCES `STAFF` (`STAFF_ID`) ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT `fk_STAFF_SEMINAR_SEMINAR1` FOREIGN KEY (`SEMINAR_ID`) REFERENCES `SEMINAR` (`SEMINAR_ID`) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Table structure for table `STAFF_SPORT`
--

CREATE TABLE `STAFF_SPORT` (
  `STAFF_SPORT_ID` int(11) NOT NULL,
  `STAFF_ID` int(11) NOT NULL,
  `SPORT_CATEGORY_ID` int(11) NOT NULL,
  `POSITION_ID` int(11) NOT NULL,
  `DESCRIPTION` varchar(200) DEFAULT NULL,
  `YEAR` date NOT NULL,
  `STATUS` tinyint(4) DEFAULT NULL,
  `MODIFIED_TIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`STAFF_SPORT_ID`),
  KEY `fk_STAFF_SPORT_STAFF1` (`STAFF_ID`),
  KEY `fk_STAFF_SPORT_POSTION1` (`POSITION_ID`),
  KEY `fk_STAFF_SPORT_SPORT_CATEGORY1` (`SPORT_CATEGORY_ID`),
  CONSTRAINT `fk_STAFF_SPORT_SPORT_CATEGORY1` FOREIGN KEY (`SPORT_CATEGORY_ID`) REFERENCES `SPORT_CATEGORY` (`SPORT_CATEGORY_ID`) ON UPDATE CASCADE ON DELETE RESTRICT,
  CONSTRAINT `fk_STAFF_SPORT_POSTION1` FOREIGN KEY (`POSITION_ID`) REFERENCES `POSITION` (`POSITION_ID`) ON UPDATE CASCADE ON DELETE RESTRICT,
  CONSTRAINT `fk_STAFF_SPORT_STAFF1` FOREIGN KEY (`STAFF_ID`) REFERENCES `STAFF` (`STAFF_ID`) ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Table structure for table `USER_LOGIN`
--
ALTER TABLE `USER_LOGIN` 
ADD COLUMN `GENERATED_PASSWORD` SMALLINT(6) DEFAULT NULL DEFAULT '0';

--
-- Table structure for table `STUDENT_DISCIPLINE`
--
ALTER TABLE `STUDENT_DISCIPLINE` 
DROP FOREIGN KEY `fk_STUDENT_DESCIPLINE_STAFF1`;

ALTER TABLE `STUDENT_DISCIPLINE`
CHANGE COLUMN `STAFF_ID` `USER_LOGIN_ID` int(11) DEFAULT NULL, 
ADD CONSTRAINT `fk_STUDENT_DESCIPLINE_USERLOGIN1` FOREIGN KEY (`USER_LOGIN_ID`) REFERENCES `USER_LOGIN` (`USER_LOGIN_ID`) ON UPDATE CASCADE ON DELETE RESTRICT ;

--
-- Table structure for table `STUDY_MEDIUM`
--

CREATE TABLE `STUDY_MEDIUM` (
  `STUDY_MEDIUM_ID` int(11) NOT NULL,
  `STUDY_MEDIUM_NAME` varchar(45) NOT NULL,
  `MODIFIED_TIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`STUDY_MEDIUM_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;



--
-- Table structure for table `STAFF`
--
ALTER TABLE `STAFF` 
ADD COLUMN `SUBJECT_ID` int(11) DEFAULT NULL, 
ADD COLUMN `STUDY_MEDIUM_ID` int(11) DEFAULT NULL, 
ADD CONSTRAINT `fk_STAFF_SUBJECT1` FOREIGN KEY (`SUBJECT_ID`) REFERENCES `SUBJECT` (`SUBJECT_ID`) ON UPDATE CASCADE ON DELETE RESTRICT,
ADD CONSTRAINT `fk_STAFF_STUDYMEDIUM1` FOREIGN KEY (`STUDY_MEDIUM_ID`) REFERENCES `STUDY_MEDIUM` (`STUDY_MEDIUM_ID`) ON UPDATE CASCADE ON DELETE RESTRICT ;


--
-- Table structure for table `DAILY_STUDENT_ATTENDANCE`
--
CREATE  TABLE `DAILY_STUDENT_ATTENDANCE` (
  `DAILY_STUDENT_ATTENDANCE_ID` INT NOT NULL ,
  `STUDENT_ID` INT NOT NULL ,
  `DATE` DATE NOT NULL ,
  `TIME_IN` TIME NOT NULL ,
  `TIME_OUT` TIME NOT NULL ,
  `MODIFIED_TIME` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`DAILY_STUDENT_ATTENDANCE_ID`) ,
  CONSTRAINT `fk_STUDENT_DAILY_ATTENDANCE` FOREIGN KEY (`STUDENT_ID` ) REFERENCES `STUDENT` (`STUDENT_ID` ) 
ON DELETE CASCADE ON UPDATE CASCADE
);

--
-- Table structure for table `DAILY_TEACHER_ATTENDANCE`
--
CREATE  TABLE `DAILY_TEACHER_ATTENDANCE` (
  `DAILY_TEACHER_ATTENDANCE_ID` INT(11) NOT NULL ,
  `STAFF_ID` INT(11) NOT NULL ,
  `TIME_IN` TIME NOT NULL ,
  `TIME_OUT` TIME NOT NULL ,
  `DATE` DATE NOT NULL ,
  `MODIFIED_TIME` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`DAILY_TEACHER_ATTENDANCE_ID`) ,
  CONSTRAINT `fk_STAFF_DAILY_ATTENDENCE` FOREIGN KEY (`STAFF_ID` ) REFERENCES `STAFF` (`STAFF_ID`) 
ON DELETE CASCADE ON UPDATE CASCADE
);

--
-- Table structure for table `STUDENT_LEAVE`
--
ALTER TABLE `STUDENT_LEAVE` 
ADD COLUMN `CERTIFICATE_GIVEN` TINYINT(4)  NOT NULL, 
ADD COLUMN `NO_OF_DAYS` int NOT NULL;

--
-- Table structure for table `STUDENT`
--
ALTER TABLE `STUDENT` 
ADD COLUMN `TRAVEL_ID2` INT(11) DEFAULT '0',
ADD COLUMN `TRAVEL_ID3` INT(11) DEFAULT '0';

/*UPDATE `STUDENT` SET TRAVEL_ID2='0', TRAVEL_ID3='0';*/

--
-- Table structure for table `STUDENT_SEMINAR`
--
CREATE  TABLE `STUDENT_SEMINAR` (

  `STUDENT_SEMINAR_ID` INT(11) NOT NULL ,

  `STUDENT_ID` INT(11) NOT NULL ,

  `SEMINAR_ID` INT(11) NOT NULL ,

  `DESCRIPTION` VARCHAR(200) NULL DEFAULT NULL ,

  `YEAR` DATE NOT NULL ,

  `STATUS` TINYINT(4) NULL DEFAULT NULL ,

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

  PRIMARY KEY (`STUDENT_SEMINAR_ID`) ,

  CONSTRAINT `fk_STUDENT_SEMINAR_STUDENT1` FOREIGN KEY (`STUDENT_ID` ) REFERENCES `STUDENT` (`STUDENT_ID` ) ON DELETE RESTRICT ON UPDATE CASCADE,

  CONSTRAINT `fk_STUDENT_SEMINAR_SEMINAR1` FOREIGN KEY (`SEMINAR_ID` ) REFERENCES `SEMINAR` (`SEMINAR_ID` ) ON DELETE RESTRICT ON UPDATE CASCADE
);

--
-- Table structure for table `PARTICIPANT_CATEGORY`
--
CREATE  TABLE `PARTICIPANT_CATEGORY` (

  `PARTICIPANT_CATEGORY_ID` INT(11) NOT NULL ,

  `DESCRIPTION` VARCHAR(100) NOT NULL ,

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

  PRIMARY KEY (`PARTICIPANT_CATEGORY_ID`) 
);

--
-- Table structure for table `SPECIAL_EVENTS`
--
CREATE  TABLE `SPECIAL_EVENTS` (

  `SPECIAL_EVENTS_ID` INT(11) NOT NULL ,

  `NAME` VARCHAR(200) NOT NULL ,

  `DESCRIPTION` VARCHAR(400) NOT NULL ,

  `DATE` DATE NOT NULL ,

  `PARTICIPANT_CATEGORY_ID` INT(11) NOT NULL ,

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

  PRIMARY KEY (`SPECIAL_EVENTS_ID`) ,

  CONSTRAINT `fk_SPECIAL_EVENTS_PARTICIPANT_CATEGORY1` FOREIGN KEY (`PARTICIPANT_CATEGORY_ID`) REFERENCES `PARTICIPANT_CATEGORY` (`PARTICIPANT_CATEGORY_ID`) ON DELETE RESTRICT ON UPDATE CASCADE
);

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

  `SPECIAL_EVENT_PARTICIPATION_ID` INT(11) NOT NULL ,

  `SPECIAL_EVENT_ID` INT(11) NOT NULL ,

  `CLASS_GRADE_ID` INT(11) NULL DEFAULT NULL ,

  `SPORT_CATEGORY_ID` INT(11) NULL DEFAULT NULL ,

  `CLUB_SOCIETY_ID` INT(11) NULL DEFAULT NULL ,

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

  PRIMARY KEY (`SPECIAL_EVENT_PARTICIPATION_ID`) ,

  CONSTRAINT `fk_SPECIALEVENT_SPECIALEVENT1`

    FOREIGN KEY (`SPECIAL_EVENT_ID` )

    REFERENCES `SPECIAL_EVENTS` (`SPECIAL_EVENTS_ID` )

    ON DELETE RESTRICT

    ON UPDATE CASCADE,

  CONSTRAINT `fk_SPECIALEVENT_CLASSGRADE1`

    FOREIGN KEY (`CLASS_GRADE_ID` )

    REFERENCES `CLASS_GRADE` (`CLASS_GRADE_ID` )

    ON DELETE RESTRICT

    ON UPDATE CASCADE,

  CONSTRAINT `fk_SPECIALEVENT_SPORTCATEGORY1`

    FOREIGN KEY (`SPORT_CATEGORY_ID` )

    REFERENCES `SPORT_CATEGORY` (`SPORT_CATEGORY_ID` )

    ON DELETE RESTRICT

    ON UPDATE CASCADE,

  CONSTRAINT `fk_SPECIALEVENT_CLUBSOCIETY1`

    FOREIGN KEY (`CLUB_SOCIETY_ID` )

    REFERENCES `CLUB_SOCIETY` (`CLUB_SOCIETY_ID` )

    ON DELETE RESTRICT

    ON UPDATE CASCADE
);


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

  `SPECIAL_EVENT_ATTENDANCE_ID` INT(11) NOT NULL ,

  `SPECIAL_EVENT_PARTICIPATION_ID` INT(11) NOT NULL ,

  `STUDENT_ID` INT(11) NOT NULL ,

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

  PRIMARY KEY (`SPECIAL_EVENT_ATTENDANCE_ID`) ,

  CONSTRAINT `fk_SPECIALEVENT_PARTICIPATION1` FOREIGN KEY (`SPECIAL_EVENT_PARTICIPATION_ID` ) REFERENCES `SPECIAL_EVENT_PARTICIPATION` (`SPECIAL_EVENT_PARTICIPATION_ID` ) ON DELETE RESTRICT ON UPDATE CASCADE,

  CONSTRAINT `fk_SPECIALEVENT_STUDENT1` FOREIGN KEY (`STUDENT_ID` ) REFERENCES `STUDENT` (`STUDENT_ID` ) ON DELETE RESTRICT ON UPDATE CASCADE
);

