CREATE TABLE `degree` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`userId` INT(11) NOT NULL,
`title` VARCHAR(255) NOT NULL COLLATE utf8_general_ci,
`university` VARCHAR(255) NOT NULL COLLATE utf8_general_ci,
`degreeDate` DATE,
`degreeLevelId` INT(11) NOT NULL,
PRIMARY KEY (`id`, `userId`),
FOREIGN KEY (`userId`) REFERENCES jos_users(`id`),
FOREIGN KEY (`degreeLevelId`) REFERENCES degreeLevel(`id`)
)TYPE=MyISAM;



CREATE TABLE `degreeLevel` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`level` VARCHAR(255) NOT NULL COLLATE utf8_general_ci,
PRIMARY KEY (`id`)
)TYPE=MyISAM;


CREATE TABLE `language` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`userId` INT(11) NOT NULL,
`language` VARCHAR(255) NOT NULL COLLATE utf8_general_ci,
`titleCertificate` VARCHAR(255) NOT NULL COLLATE utf8_general_ci,
`snCertificate` VARCHAR(255) NOT NULL COLLATE utf8_general_ci,
`teachability` ENUM('Yes', 'No') COLLATE utf8_general_ci,
PRIMARY KEY (`id`, `userId`),
FOREIGN KEY (`userId`) REFERENCES jos_users(`id`)
)TYPE=MyISAM;

CREATE TABLE `available_hours` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`userId` INT(11) NOT NULL,
`day` VARCHAR(255) NOT NULL COLLATE utf8_general_ci,
`fromHour` VARCHAR(255) NOT NULL COLLATE utf8_general_ci,
`toHour` VARCHAR(255) NOT NULL COLLATE utf8_general_ci,
`comment` TEXT COLLATE utf8_general_ci,
PRIMARY KEY (`id`, `userId`),
FOREIGN KEY (`userId`) REFERENCES jos_users(`id`)
)TYPE=MyISAM;

CREATE TABLE `pro_experience` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`userId` INT(11) NOT NULL,
`scope` VARCHAR(255) NOT NULL COLLATE utf8_general_ci,
`status` VARCHAR(255) NOT NULL COLLATE utf8_general_ci,
`docNumber` VARCHAR(255) NOT NULL COLLATE utf8_general_ci,
`since` DATE,
`until` DATE,
PRIMARY KEY(`id`, `userId`),
FOREIGN KEY (`userId`) REFERENCES jos_users(`id`)
)TYPE=MyISAM;

CREATE TABLE `educ_experience` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`userId` INT(11) NOT NULL,
`lesson` VARCHAR(255) NOT NULL COLLATE utf8_general_ci,
`educInstitution` VARCHAR(255) NOT NULL COLLATE utf8_general_ci,
`since` DATE, 
`until` DATE,
`docNumner` VARCHAR(255) NOT NULL COLLATE utf8_general_ci,
PRIMARY KEY (`id`, `userId`),
FOREIGN KEY (`userId`) REFERENCES jos_users(`id`)
)TYPE=MyISAM;

CREATE TABLE `scientific_activity` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`userId` INT(11) NOT NULL,
`author` VARCHAR(255) NOT NULL COLLATE utf8_general_ci,
`title` VARCHAR(255) NOT NULL COLLATE utf8_general_ci,
`magazineCouncil` VARCHAR(255) NOT NULL COLLATE utf8_general_ci,
`tome` VARCHAR(255) NOT NULL COLLATE utf8_general_ci,
`page` VARCHAR(255) NOT NULL COLLATE utf8_general_ci,
PRIMARY KEY (`id`, `userId`),
FOREIGN KEY (`userId`) REFERENCES jos_users(`id`)
)TYPE=MyISAM;

CREATE TABLE `supervisor` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(255) NOT NULL COLLATE utf8_general_ci,
`surname` VARCHAR(255) NOT NULL COLLATE utf8_general_ci,
`sectionId` INT(11) NOT NULL,
PRIMARY KEY (`id`),
FOREIGN KEY (`sectionId`) REFERENCES section(`id`)
)TYPE=MyISAM;

CREATE TABLE `section` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`sectionTitle` VARCHAR(255) NOT NULL COLLATE utf8_general_ci,
`supervisorId` INT(11) NOT NULL,
PRIMARY KEY (`id`),
FOREIGN KEY (`supervisorId`) REFERENCES supervisor(`id`)
)TYPE=MyISAM;

CREATE TABLE `department` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`departmentName` VARCHAR(255) NOT NULL COLLATE utf8_general_ci,
PRIMARY KEY (`id`)
)TYPE=MyISAM


CREATE TABLE `lesson` (
`id` VARCHAR(255) NOT NULL COLLATE utf8_general_ci,
`sectionId` INT(11) NOT NULL,
`lessonTitle` VARCHAR(255) NOT NULL COLLATE utf8_general_ci,
`semester` VARCHAR(255) NOT NULL COLLATE utf8_general_ci,
PRIMARY KEY (`id`),
FOREIGN KEY (`sectionId`) REFERENCES section(`id`)
)TYPE=MyISAM;

CREATE TABLE IF NOT EXISTS `applicationform` (
  `userId` int(11) NOT NULL,
  `lessonId` varchar(255) NOT NULL,
  `departmentId` int(11) NOT NULL,
  `sectionId` int(11) NOT NULL,
  `academicYear` year(4) NOT NULL DEFAULT '0000',
  `submissionDate` date DEFAULT NULL,
  `educatorNotes` enum('Yes','No') DEFAULT NULL,
  `semesterTeaching` int(11) DEFAULT NULL,
  `submited` ENUM('0', '1') COLLATE utf8_general_ci, 	
  PRIMARY KEY (`userId`,`lessonId`,`departmentId`,`sectionId`,`academicYear`),
  KEY `lessonId` (`lessonId`),
  KEY `departmentId` (`departmentId`),
  KEY `sectionId` (`sectionId`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;