-- ----------------------------------------------------------------------
-- MySQL Migration Toolkit
-- SQL Create Script
-- ----------------------------------------------------------------------

SET FOREIGN_KEY_CHECKS = 0;

CREATE DATABASE IF NOT EXISTS `opas`
  CHARACTER SET latin1 COLLATE latin1_swedish_ci;
USE `opas`;
-- -------------------------------------
-- Tables

DROP TABLE IF EXISTS `opas`.`contacts`;
CREATE TABLE `opas`.`contacts` (
  `orgid` BIGINT(20) NOT NULL DEFAULT '0',
  `orgaddr` VARCHAR(100) NULL,
  `orgcontact` VARCHAR(100) NULL,
  `orgdoes` VARCHAR(255) NULL,
  `orgemail` VARCHAR(75) NULL,
  `orgname` VARCHAR(100) NULL,
  `orgpostcode` VARCHAR(10) NULL,
  `orgtel` VARCHAR(50) NULL,
  `orgusername` VARCHAR(50) NULL,
  `orgpassword` VARCHAR(50) NULL,
  PRIMARY KEY (`orgid`)
)
ENGINE = INNODB;

DROP TABLE IF EXISTS `opas`.`final_project`;
CREATE TABLE `opas`.`final_project` (
  `projectId` BIGINT(20) NOT NULL DEFAULT '0',
  `projectTitle` VARCHAR(255) NULL,
  `projectSubmittedDate` DATETIME NULL,
  `lateHandIn` VARCHAR(255) NULL,
  `unitExamBoardDecision` DATETIME NULL,
  `projectMark` BIGINT(20) NULL,
  `projectComments` VARCHAR(255) NULL,
  PRIMARY KEY (`projectId`)
)
ENGINE = INNODB;

DROP TABLE IF EXISTS `opas`.`ideas2`;
CREATE TABLE `opas`.`ideas2` (
  `projid` BIGINT(20) NOT NULL DEFAULT '0',
  `projaims` TEXT NULL,
  `projapproved` VARCHAR(10) NULL,
  `projdate` DATE NULL,
  `projdeliver` TEXT NULL,
  `projquest` TEXT NULL,
  `projstudent` VARCHAR(100) NULL,
  `projtitle` VARCHAR(150) NULL,
  `orgid` BIGINT(20) NOT NULL DEFAULT '0',
  PRIMARY KEY (`projid`),
  INDEX `FK80705F76CE88E69C` (`orgid`)
)
ENGINE = INNODB;

DROP TABLE IF EXISTS `opas`.`marker_capacity`;
CREATE TABLE `opas`.`marker_capacity` (
  `markerCapacityId` BIGINT(20) NOT NULL DEFAULT '0',
  `allocatedDate` DATETIME NULL,
  `unallocatedDate` DATETIME NULL,
  `PERSON_FK` BIGINT(20) NOT NULL DEFAULT '0',
  `MARKERTYPE_FK` BIGINT(20) NOT NULL DEFAULT '0',
  PRIMARY KEY (`markerCapacityId`),
  INDEX `FK987D4F1FC160730E` (`PERSON_FK`),
  INDEX `FK987D4F1F40E629CE` (`MARKERTYPE_FK`)
)
ENGINE = INNODB;

DROP TABLE IF EXISTS `opas`.`marker_type`;
CREATE TABLE `opas`.`marker_type` (
  `markerTypeId` BIGINT(20) NOT NULL DEFAULT '0',
  `markerDescription` VARCHAR(255) NULL,
  PRIMARY KEY (`markerTypeId`)
)
ENGINE = INNODB;

DROP TABLE IF EXISTS `opas`.`person`;
CREATE TABLE `opas`.`person` (
  `personId` BIGINT(20) NOT NULL DEFAULT '0',
  `userName` VARCHAR(255) NULL,
  `pwd` VARCHAR(255) NULL,
  `firstName` VARCHAR(255) NULL,
  `surname` VARCHAR(255) NULL,
  `inactive` VARCHAR(255) NULL,
  PRIMARY KEY (`personId`)
)
ENGINE = INNODB;

DROP TABLE IF EXISTS `opas`.`preferred_students`;
CREATE TABLE `opas`.`preferred_students` (
  `preferredId` BIGINT(20) NOT NULL DEFAULT '0',
  `preferenceOrder` INT(11) NULL,
  `STUDENT_FK` BIGINT(20) NOT NULL DEFAULT '0',
  `MARKERCAPACITY_FK` BIGINT(20) NOT NULL DEFAULT '0',
  PRIMARY KEY (`preferredId`),
  INDEX `FK8F927B6BD1A7EFA` (`STUDENT_FK`),
  INDEX `FK8F927B63F44D4CE` (`MARKERCAPACITY_FK`)
)
ENGINE = INNODB;

DROP TABLE IF EXISTS `opas`.`project_choice`;
CREATE TABLE `opas`.`project_choice` (
  `choiceId` BIGINT(20) NOT NULL DEFAULT '0',
  `choiceDate` DATETIME NULL,
  `STUDENT_FK` BIGINT(20) NOT NULL DEFAULT '0',
  PRIMARY KEY (`choiceId`),
  INDEX `FK38AC5DA7BD1A7EFA` (`STUDENT_FK`)
)
ENGINE = INNODB;

DROP TABLE IF EXISTS `opas`.`project_choice_options`;
CREATE TABLE `opas`.`project_choice_options` (
  `pcoId` BIGINT(20) NOT NULL DEFAULT '0',
  `preferenceOrder` INT(11) NULL,
  `PROJECTCHOICE_FK` BIGINT(20) NOT NULL DEFAULT '0',
  `IDEAS2_FK` BIGINT(20) NOT NULL DEFAULT '0',
  PRIMARY KEY (`pcoId`),
  INDEX `FKCE857FE6997AEE3A` (`PROJECTCHOICE_FK`),
  INDEX `FKCE857FE6714EE76E` (`IDEAS2_FK`)
)
ENGINE = INNODB;

DROP TABLE IF EXISTS `opas`.`student`;
CREATE TABLE `opas`.`student` (
  `studentId` BIGINT(20) NOT NULL DEFAULT '0',
  `hemisNo` BIGINT(20) NULL,
  `degreeStream` VARCHAR(255) NULL,
  `finalYear` VARCHAR(255) NULL,
  PRIMARY KEY (`studentId`)
)
ENGINE = INNODB;



SET FOREIGN_KEY_CHECKS = 1;

-- ----------------------------------------------------------------------
-- EOF

