# File name: C:\sums20051128.sql
# Creation date: 11/28/2005
# Created by Oracle to MySQL 3.3 
# --------------------------------------------------
# More conversion tools at http://www.convert-in.com

#
# Table structure for table 'AUDIT_TRAIL'
#

DROP TABLE IF EXISTS `AUDIT_TRAIL`;
CREATE TABLE `AUDIT_TRAIL` (
  `AUDIT_ID` BIGINT NOT NULL,
  `PERSON_MAKING_CHANGE_ID` BIGINT NOT NULL,
  `PROJECT_BEING_CHANGED_ID` BIGINT NOT NULL,
  `REASON` LONGTEXT,
  `DATE_ALTERED` DATETIME,
  `ITEM_CHANGED` VARCHAR(128) NOT NULL,
  `OLD_VALUE_OF_ITEM` LONGTEXT,
  `NEW_VALUE_OF_ITEM` LONGTEXT,
  `TIME_ALTERED` DATETIME
) TYPE=InnoDB;

#
# Dumping data for table 'AUDIT_TRAIL'
#

LOCK TABLES `AUDIT_TRAIL` WRITE;
UNLOCK TABLES;

#
# Table structure for table 'CATEGORY_MARKS'
#

DROP TABLE IF EXISTS `CATEGORY_MARKS`;
CREATE TABLE `CATEGORY_MARKS` (
  `CAT_MARK_ID` BIGINT NOT NULL,
  `MARK_ID` BIGINT NOT NULL,
  `CAT_ID` BIGINT NOT NULL,
  `MARK` BIGINT NOT NULL,
  `CAT_COMMENT` LONGTEXT,
  PRIMARY KEY (`CAT_MARK_ID`)
) TYPE=InnoDB;

#
# Dumping data for table 'CATEGORY_MARKS'
#

LOCK TABLES `CATEGORY_MARKS` WRITE;
UNLOCK TABLES;

#
# Table structure for table 'CATEGORY_OPTION_CRITERIA'
#

DROP TABLE IF EXISTS `CATEGORY_OPTION_CRITERIA`;
CREATE TABLE `CATEGORY_OPTION_CRITERIA` (
  `OPT_CRITERIA_ID` BIGINT NOT NULL,
  `MARK_CATEGORY` BIGINT NOT NULL,
  `POSITIVE_CRITERIA` LONGTEXT,
  `NEGATIVE_CRITERIA` LONGTEXT,
  `CAT_OPTION` BIGINT NOT NULL,
  PRIMARY KEY (`OPT_CRITERIA_ID`)
) TYPE=InnoDB;

#
# Dumping data for table 'CATEGORY_OPTION_CRITERIA'
#

LOCK TABLES `CATEGORY_OPTION_CRITERIA` WRITE;
UNLOCK TABLES;

#
# Table structure for table 'CATEGORY_OPTION_GROUPS'
#

DROP TABLE IF EXISTS `CATEGORY_OPTION_GROUPS`;
CREATE TABLE `CATEGORY_OPTION_GROUPS` (
  `OPT_GROUP_ID` BIGINT NOT NULL,
  `OPT_GROUP_NAME` VARCHAR(64),
  PRIMARY KEY (`OPT_GROUP_ID`)
) TYPE=InnoDB;

#
# Dumping data for table 'CATEGORY_OPTION_GROUPS'
#

LOCK TABLES `CATEGORY_OPTION_GROUPS` WRITE;
UNLOCK TABLES;

#
# Table structure for table 'CATEGORY_OPTIONS'
#

DROP TABLE IF EXISTS `CATEGORY_OPTIONS`;
CREATE TABLE `CATEGORY_OPTIONS` (
  `OPT_ID` BIGINT NOT NULL,
  `OPT_LABEL` VARCHAR(30) NOT NULL,
  `OPT_SELECTED_VALUE` BIGINT NOT NULL,
  `OPT_GROUP` BIGINT NOT NULL,
  `OPT_INDEX` BIGINT NOT NULL,
  PRIMARY KEY (`OPT_ID`)
) TYPE=InnoDB;

#
# Dumping data for table 'CATEGORY_OPTIONS'
#

LOCK TABLES `CATEGORY_OPTIONS` WRITE;
UNLOCK TABLES;

#
# Table structure for table 'COHORT'
#

DROP TABLE IF EXISTS `COHORT`;
CREATE TABLE `COHORT` (
  `COHORT_ID` BIGINT NOT NULL,
  `COHORT_DESCRIPTION` VARCHAR(128) NOT NULL,
  `PROJECT_START_DATE` DATETIME,
  `PROJECT_END_DATE` DATETIME,
  `COHORT_CLOSED` VARCHAR(1),
  PRIMARY KEY (`COHORT_ID`)
) TYPE=InnoDB;

#
# Dumping data for table 'COHORT'
#

LOCK TABLES `COHORT` WRITE;
UNLOCK TABLES;

#
# Table structure for table 'COHORT_COORDINATORS'
#

DROP TABLE IF EXISTS `COHORT_COORDINATORS`;
CREATE TABLE `COHORT_COORDINATORS` (
  `CO_CORD_ID` BIGINT NOT NULL,
  `COHORT_ID` BIGINT NOT NULL,
  `PERSON_ID` BIGINT NOT NULL,
  PRIMARY KEY (`CO_CORD_ID`)
) TYPE=InnoDB;

#
# Dumping data for table 'COHORT_COORDINATORS'
#

LOCK TABLES `COHORT_COORDINATORS` WRITE;
UNLOCK TABLES;

#
# Table structure for table 'CONTACTS'
#

DROP TABLE IF EXISTS `CONTACTS`;
CREATE TABLE `CONTACTS` (
  `ORGADDR` VARCHAR(100),
  `ORGCONTACT` VARCHAR(50),
  `ORGDOES` VARCHAR(50),
  `ORGEMAIL` VARCHAR(50),
  `ORGID` BIGINT NOT NULL,
  `ORGNAME` VARCHAR(50),
  `ORGPOSTCODE` VARCHAR(10),
  `ORGTEL` VARCHAR(20),
  PRIMARY KEY (`ORGID`)
) TYPE=InnoDB;

#
# Dumping data for table 'CONTACTS'
#

LOCK TABLES `CONTACTS` WRITE;
UNLOCK TABLES;

#
# Table structure for table 'EMAILMESSAGES'
#

DROP TABLE IF EXISTS `EMAILMESSAGES`;
CREATE TABLE `EMAILMESSAGES` (
  `MAILKEY` VARCHAR(30) NOT NULL,
  `MAILVALUE` LONGTEXT,
  `MAILSTRING` TEXT
) TYPE=InnoDB;

#
# Dumping data for table 'EMAILMESSAGES'
#

LOCK TABLES `EMAILMESSAGES` WRITE;
UNLOCK TABLES;

#
# Table structure for table 'EMAILS'
#

DROP TABLE IF EXISTS `EMAILS`;
CREATE TABLE `EMAILS` (
  `EMAIL` VARCHAR(100) NOT NULL,
  `USERID` LONGTEXT
) TYPE=InnoDB;

#
# Dumping data for table 'EMAILS'
#

LOCK TABLES `EMAILS` WRITE;
UNLOCK TABLES;

#
# Table structure for table 'FINAL_PROJECT'
#

DROP TABLE IF EXISTS `FINAL_PROJECT`;
CREATE TABLE `FINAL_PROJECT` (
  `PROJECT_ID` BIGINT NOT NULL,
  `STUDENT_ID` BIGINT NOT NULL,
  `COHORT_ID` BIGINT NOT NULL,
  `UNIT_ID` BIGINT NOT NULL,
  `PROJECT_TITLE` VARCHAR(50) NOT NULL,
  `PROJECT_SUBMITTED_DATE` DATETIME,
  `LATE_HAND_IN` VARCHAR(1),
  `UNIT_EXAM_BOARD_DECISION` DATETIME,
  `PROJECT_STATUS` VARCHAR(3),
  `PROJECT_MARK` BIGINT,
  `PROJECT_COMMENTS` LONGTEXT,
  PRIMARY KEY (`PROJECT_ID`)
) TYPE=InnoDB;

#
# Dumping data for table 'FINAL_PROJECT'
#

LOCK TABLES `FINAL_PROJECT` WRITE;
UNLOCK TABLES;

#
# Table structure for table 'IDEAS2'
#

DROP TABLE IF EXISTS `IDEAS2`;
CREATE TABLE `IDEAS2` (
  `PROJAIMS` LONGTEXT,
  `PROJAPPROVED` VARCHAR(1),
  `PROJDATE` DATETIME,
  `PROJDELIVER` LONGTEXT,
  `PROJID` BIGINT NOT NULL,
  `PROJORG` BIGINT,
  `PROJQUEST` LONGTEXT,
  `PROJSTUDENT` VARCHAR(100),
  `PROJTITLE` VARCHAR(100),
  PRIMARY KEY (`PROJID`)
) TYPE=InnoDB;

#
# Dumping data for table 'IDEAS2'
#

LOCK TABLES `IDEAS2` WRITE;
UNLOCK TABLES;

#
# Table structure for table 'MARK_CATEGORIES'
#

DROP TABLE IF EXISTS `MARK_CATEGORIES`;
CREATE TABLE `MARK_CATEGORIES` (
  `CATBEGIN` DATETIME NOT NULL,
  `CATDESCRIPTION` LONGTEXT,
  `CATEND` DATETIME,
  `CATID` BIGINT NOT NULL,
  `CATLONGNAME` VARCHAR(100) NOT NULL,
  `CATNAME` VARCHAR(30) NOT NULL,
  `COG_ID` BIGINT NOT NULL,
  `OPTION_TYPE` VARCHAR(1) NOT NULL,
  PRIMARY KEY (`CATID`)
) TYPE=InnoDB;

#
# Dumping data for table 'MARK_CATEGORIES'
#

LOCK TABLES `MARK_CATEGORIES` WRITE;
UNLOCK TABLES;

#
# Table structure for table 'MARK_FORM_CATEGORIES'
#

DROP TABLE IF EXISTS `MARK_FORM_CATEGORIES`;
CREATE TABLE `MARK_FORM_CATEGORIES` (
  `CATID` BIGINT NOT NULL,
  `CATINDEX` BIGINT,
  `CATWEIGHT` BIGINT,
  `FORMID` BIGINT NOT NULL,
  `MFCBEGIN` DATETIME,
  `MFCEND` DATETIME,
  `MFCID` BIGINT NOT NULL,
  `OPTIONAL` VARCHAR(1),
  PRIMARY KEY (`MFCID`)
) TYPE=InnoDB;

#
# Dumping data for table 'MARK_FORM_CATEGORIES'
#

LOCK TABLES `MARK_FORM_CATEGORIES` WRITE;
UNLOCK TABLES;

#
# Table structure for table 'MARK_FORMS'
#

DROP TABLE IF EXISTS `MARK_FORMS`;
CREATE TABLE `MARK_FORMS` (
  `FORMBEGIN` DATETIME,
  `FORMEND` DATETIME,
  `FORMID` BIGINT NOT NULL,
  `FORMNAME` VARCHAR(255) NOT NULL,
  PRIMARY KEY (`FORMID`)
) TYPE=InnoDB;

#
# Dumping data for table 'MARK_FORMS'
#

LOCK TABLES `MARK_FORMS` WRITE;
UNLOCK TABLES;

#
# Table structure for table 'MARKER_CAPACITY'
#

DROP TABLE IF EXISTS `MARKER_CAPACITY`;
CREATE TABLE `MARKER_CAPACITY` (
  `MARKER_CAPACITY_ID` BIGINT NOT NULL,
  `PROJECT_ID` BIGINT NOT NULL,
  `MARKER_TYPE_ID` BIGINT NOT NULL,
  `PERSON_ID` BIGINT NOT NULL,
  `ALLOCATED_DATE` DATETIME,
  `UNALLOCATED_DATE` DATETIME,
  PRIMARY KEY (`MARKER_CAPACITY_ID`)
) TYPE=InnoDB;

#
# Dumping data for table 'MARKER_CAPACITY'
#

LOCK TABLES `MARKER_CAPACITY` WRITE;
UNLOCK TABLES;

#
# Table structure for table 'MARKER_TYPE'
#

DROP TABLE IF EXISTS `MARKER_TYPE`;
CREATE TABLE `MARKER_TYPE` (
  `MARKER_TYPE_ID` BIGINT NOT NULL,
  `MARKER_DESCRIPTION` VARCHAR(64) NOT NULL,
  PRIMARY KEY (`MARKER_TYPE_ID`)
) TYPE=InnoDB;

#
# Dumping data for table 'MARKER_TYPE'
#

LOCK TABLES `MARKER_TYPE` WRITE;
UNLOCK TABLES;

#
# Table structure for table 'MARKS'
#

DROP TABLE IF EXISTS `MARKS`;
CREATE TABLE `MARKS` (
  `MARK_ID` BIGINT NOT NULL,
  `PROJECT_ID` BIGINT NOT NULL,
  `MARKER_CAPACITY_ID` BIGINT NOT NULL,
  `MARK` BIGINT NOT NULL,
  `MARK_DATE` DATETIME,
  `GENERAL_COMMENTS` LONGTEXT,
  `COMMENTS_FOR_EXAMINERS` LONGTEXT,
  `GENERAL_COMMENTS_EXAMBOARD` LONGTEXT,
  `PLAGIARISM_UNFAIR_ACT` VARCHAR(1),
  `PLAGIARISM_COMMENTS` LONGTEXT,
  `UNFAIR_ACT_NOTES` LONGTEXT,
  `ADJUSTMENT_APPLIED` BIGINT,
  `PLAGIARISM_SUSPECT` VARCHAR(1),
  PRIMARY KEY (`MARK_ID`)
) TYPE=InnoDB;

#
# Dumping data for table 'MARKS'
#

LOCK TABLES `MARKS` WRITE;
UNLOCK TABLES;

#
# Table structure for table 'OPTION_MARKS'
#

DROP TABLE IF EXISTS `OPTION_MARKS`;
CREATE TABLE `OPTION_MARKS` (
  `OPT_MARK_ID` BIGINT NOT NULL,
  `MARK_ID` BIGINT NOT NULL,
  `CAT_ID` BIGINT NOT NULL,
  `CO_ID` BIGINT NOT NULL,
  PRIMARY KEY (`OPT_MARK_ID`)
) TYPE=InnoDB;

#
# Dumping data for table 'OPTION_MARKS'
#

LOCK TABLES `OPTION_MARKS` WRITE;
UNLOCK TABLES;

#
# Table structure for table 'PERSON'
#

DROP TABLE IF EXISTS `PERSON`;
CREATE TABLE `PERSON` (
  `PERSON_ID` BIGINT NOT NULL,
  `USER_NAME` VARCHAR(16),
  `PWD` VARCHAR(16),
  `FIRST_NAME` VARCHAR(64) NOT NULL,
  `SURNAME` VARCHAR(64) NOT NULL,
  `INACTIVE` VARCHAR(1),
  PRIMARY KEY (`PERSON_ID`)
) TYPE=InnoDB;

#
# Dumping data for table 'PERSON'
#

LOCK TABLES `PERSON` WRITE;
UNLOCK TABLES;

#
# Table structure for table 'PERSON_EMAIL'
#

DROP TABLE IF EXISTS `PERSON_EMAIL`;
CREATE TABLE `PERSON_EMAIL` (
  `E_MAIL_ID` BIGINT NOT NULL,
  `PERSON_ID` BIGINT NOT NULL,
  `E_MAIL_ADD` VARCHAR(128) NOT NULL,
  `E_MAIL_SUSPENDED` VARCHAR(1),
  PRIMARY KEY (`E_MAIL_ID`)
) TYPE=InnoDB;

#
# Dumping data for table 'PERSON_EMAIL'
#

LOCK TABLES `PERSON_EMAIL` WRITE;
UNLOCK TABLES;

#
# Table structure for table 'PERSON_ROLES'
#

DROP TABLE IF EXISTS `PERSON_ROLES`;
CREATE TABLE `PERSON_ROLES` (
  `ROLE` BIGINT NOT NULL,
  `PERSON_ID` BIGINT NOT NULL
) TYPE=InnoDB;

#
# Dumping data for table 'PERSON_ROLES'
#

LOCK TABLES `PERSON_ROLES` WRITE;
UNLOCK TABLES;

#
# Table structure for table 'PRIZE_CATEGORIES'
#

DROP TABLE IF EXISTS `PRIZE_CATEGORIES`;
CREATE TABLE `PRIZE_CATEGORIES` (
  `PRIZE_CAT_ID` BIGINT NOT NULL,
  `PRIZE_CAT_NAME` VARCHAR(16) NOT NULL,
  `PRIZE_VALUE` BIGINT,
  `PRIZE_CAT_CRITERIA` LONGTEXT,
  PRIMARY KEY (`PRIZE_CAT_ID`)
) TYPE=InnoDB;

#
# Dumping data for table 'PRIZE_CATEGORIES'
#

LOCK TABLES `PRIZE_CATEGORIES` WRITE;
UNLOCK TABLES;

#
# Table structure for table 'PRIZE_NOMINATIONS'
#

DROP TABLE IF EXISTS `PRIZE_NOMINATIONS`;
CREATE TABLE `PRIZE_NOMINATIONS` (
  `PRIZE_NOM_ID` BIGINT NOT NULL,
  `PRIZE_CAT_ID` BIGINT NOT NULL,
  `MARK_ID` BIGINT NOT NULL,
  `SUPPORTING_COMMENTS` LONGTEXT,
  PRIMARY KEY (`PRIZE_NOM_ID`)
) TYPE=InnoDB;

#
# Dumping data for table 'PRIZE_NOMINATIONS'
#

LOCK TABLES `PRIZE_NOMINATIONS` WRITE;
UNLOCK TABLES;

#
# Table structure for table 'ROLES'
#

DROP TABLE IF EXISTS `ROLES`;
CREATE TABLE `ROLES` (
  `ROLE_ID` BIGINT NOT NULL,
  `ROLE_DESCRIPTION` VARCHAR(32) NOT NULL,
  PRIMARY KEY (`ROLE_ID`)
) TYPE=InnoDB;

#
# Dumping data for table 'ROLES'
#

LOCK TABLES `ROLES` WRITE;
UNLOCK TABLES;

#
# Table structure for table 'STAFF'
#

DROP TABLE IF EXISTS `STAFF`;
CREATE TABLE `STAFF` (
  `STAFF_NO` VARCHAR(16) NOT NULL,
  `PERSON_ID` BIGINT NOT NULL,
  PRIMARY KEY (`STAFF_NO`)
) TYPE=InnoDB;

#
# Dumping data for table 'STAFF'
#

LOCK TABLES `STAFF` WRITE;
UNLOCK TABLES;

#
# Table structure for table 'STATUSES'
#

DROP TABLE IF EXISTS `STATUSES`;
CREATE TABLE `STATUSES` (
  `STATUS_CODE` VARCHAR(3),
  `STATUS` VARCHAR(100)
) TYPE=InnoDB;

#
# Dumping data for table 'STATUSES'
#

LOCK TABLES `STATUSES` WRITE;
UNLOCK TABLES;

#
# Table structure for table 'STUDENT'
#

DROP TABLE IF EXISTS `STUDENT`;
CREATE TABLE `STUDENT` (
  `STUDENT_ID` BIGINT NOT NULL,
  `HEMIS_NO` BIGINT NOT NULL,
  `PERSON_ID` BIGINT NOT NULL,
  `DEGREE_STREAM` VARCHAR(16) NOT NULL,
  `FINAL_YEAR` VARCHAR(16) NOT NULL,
  PRIMARY KEY (`STUDENT_ID`)
) TYPE=InnoDB;

#
# Dumping data for table 'STUDENT'
#

LOCK TABLES `STUDENT` WRITE;
UNLOCK TABLES;

#
# Table structure for table 'TEL_NUMBER'
#

DROP TABLE IF EXISTS `TEL_NUMBER`;
CREATE TABLE `TEL_NUMBER` (
  `TELEPHONE_NUMBER_ID` BIGINT NOT NULL,
  `PERSON_ID` BIGINT NOT NULL,
  `TEL_NUMBER` VARCHAR(20) NOT NULL,
  PRIMARY KEY (`TELEPHONE_NUMBER_ID`)
) TYPE=InnoDB;

#
# Dumping data for table 'TEL_NUMBER'
#

LOCK TABLES `TEL_NUMBER` WRITE;
UNLOCK TABLES;

#
# Table structure for table 'UNIT'
#

DROP TABLE IF EXISTS `UNIT`;
CREATE TABLE `UNIT` (
  `UNIT_ID` BIGINT NOT NULL,
  `UNIT_CODE` VARCHAR(16) NOT NULL,
  `UNIT_NAME` VARCHAR(50) NOT NULL,
  `ACADEMIC_YR_FIRST_RUN` DATETIME,
  `ACADEMIC_YR_LAST_RUN` DATETIME,
  PRIMARY KEY (`UNIT_ID`)
) TYPE=InnoDB;

#
# Dumping data for table 'UNIT'
#

LOCK TABLES `UNIT` WRITE;
UNLOCK TABLES;

#
# Table structure for table 'UNIT_MARK_FORMS'
#

DROP TABLE IF EXISTS `UNIT_MARK_FORMS`;
CREATE TABLE `UNIT_MARK_FORMS` (
  `UMFID` BIGINT NOT NULL,
  `UNIT_ID` BIGINT NOT NULL,
  `FORM_ID` BIGINT NOT NULL,
  `UMFBEGIN` DATETIME NOT NULL,
  `UMFEND` DATETIME,
  PRIMARY KEY (`UMFID`)
) TYPE=InnoDB;

#
# Dumping data for table 'UNIT_MARK_FORMS'
#

LOCK TABLES `UNIT_MARK_FORMS` WRITE;
UNLOCK TABLES;

