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

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

  `STAFF_LEAVE_TYPE_ID` INT(11) NOT NULL AUTO_INCREMENT ,

  `DESCRIPTION` VARCHAR(45) NOT NULL ,

  `MAX_NO_OF_LEAVES` INT(11) NULL DEFAULT NULL ,

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

  PRIMARY KEY (`STAFF_LEAVE_TYPE_ID`),
  UNIQUE INDEX `DESCRIPTION_UNIQUE` (`DESCRIPTION` ASC) )

ENGINE = InnoDB

DEFAULT CHARACTER SET = latin1

COLLATE = latin1_swedish_ci;

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

ADD UNIQUE INDEX `COUNTRY_NAME_UNIQUE` (`COUNTRY_NAME` ASC) ;

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

ADD UNIQUE INDEX `DESCRIPTION_UNIQUE` (`DESCRIPTION` ASC) ;

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

  `FAITH_LIFE_GRADING_ID` INT(11) NOT NULL AUTO_INCREMENT ,

  `DESCRIPTION` CHAR(1) NOT NULL ,

  `VALUE` INT(11) NOT NULL ,

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

  PRIMARY KEY (`FAITH_LIFE_GRADING_ID`) )

ENGINE = InnoDB

DEFAULT CHARACTER SET = latin1

COLLATE = latin1_swedish_ci;

--
-- Table structure for table `STAFF_PAST_SERVICE`
--
CREATE TABLE `STAFF_PAST_SERVICE` (
  `PAST_SERVICE_ID` int(11) NOT NULL AUTO_INCREMENT,
  `STAFF_ID` int(11) NOT NULL,
  `JOINED_DATE` date NOT NULL,
  `DEPATURE_DATE` date NOT NULL,
  `REASON` varchar(250) DEFAULT NULL,
  `COMPLETE_CLEARANCE` tinyint(4) NOT NULL,
  `MODIFIED_TIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`PAST_SERVICE_ID`), 
  CONSTRAINT `fk_STAFF_PAST_SERVICE_STAFF1` 
   FOREIGN KEY (`STAFF_ID`) 
   REFERENCES `STAFF` (`STAFF_ID`) 
   ON UPDATE CASCADE 
   ON DELETE RESTRICT
) ENGINE = InnoDB

DEFAULT CHARACTER SET = latin1

COLLATE = latin1_swedish_ci;

--
-- Table structure for table `STAFF_LEAVE`
--
ALTER TABLE `STAFF_LEAVE` 
ADD COLUMN `DELETED` TINYINT(4) DEFAULT 0, 
ADD COLUMN `STAFF_LEAVE_TYPE_ID` INT(11) NULL,  

ADD CONSTRAINT `fk_STAFF_LEAVE_STAFF_LEAVE_TYPE1`

  FOREIGN KEY (`STAFF_LEAVE_TYPE_ID` )

  REFERENCES `STAFF_LEAVE_TYPE` (`STAFF_LEAVE_TYPE_ID` )

  ON DELETE RESTRICT

  ON UPDATE CASCADE ;

--
-- Table structure for table `CLASS_TEACHER`
--
ALTER TABLE `CLASS_TEACHER` 
ADD COLUMN `DELETED` TINYINT(4) DEFAULT 0 ;

--
-- Table structure for table `SUBJECT_TEACHER`
--
ALTER TABLE `SUBJECT_TEACHER` 
ADD COLUMN `DELETED` TINYINT(4) DEFAULT 0 ;

--
-- Table structure for table `USER_LOGIN`
--
ALTER TABLE `USER_LOGIN` 
ADD COLUMN `DELETED` TINYINT(4) DEFAULT 0 ;

--
-- Table structure for table `EXAM_SUBJECT`
--
ALTER TABLE `EXAM_SUBJECT` 
DROP FOREIGN KEY `fk_EXAM_SUBJECT_EXAM1` ;


ALTER TABLE `EXAM_SUBJECT` 

  ADD CONSTRAINT `fk_EXAM_SUBJECT_EXAM1`

  FOREIGN KEY (`EXAM_ID` )

  REFERENCES `EXAM` (`EXAM_ID` )

  ON DELETE RESTRICT

  ON UPDATE CASCADE;

--
-- Table structure for table `STAFF_PAST_SERVICE`
--
ALTER TABLE `STAFF_PAST_SERVICE` 
ADD COLUMN `REGISTRATION_NO` VARCHAR(45) NULL;






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