CREATE TABLE UserPage (
UnixID VARCHAR2(9) NOT NULL,
LastPageVisited VARCHAR2(100),
PRIMARY KEY (UnixID)
);

CREATE TABLE UserRightsAndStuff (
UserRightsID INTEGER NOT NULL,
UnixID VARCHAR2(9) NOT NULL,
Abi CHAR(1) CHECK (Abi IN (0,1)) NOT NULL,
Thesis CHAR(1) CHECK (Thesis IN (0,1)) NOT NULL,
StudyGroup CHAR(1) CHECK (StudyGroup IN (0,1)) NOT NULL,
Fraternity CHAR(1) CHECK (Fraternity IN (0,1)) NOT NULL,
Foreign CHAR(1) CHECK (Foreign IN (0,1)) NOT NULL,
PRIMARY KEY (UserRightsID, UnixID)
);


CREATE TABLE ViewAbi (
ViewAbiID INTEGER NOT NULL,
UnixID VARCHAR2(9) NOT NULL,
Attendees CHAR(1) CHECK (Attendees IN (0,1)) NOT NULL,
Places CHAR(1) CHECK (Places IN (0,1)) NOT NULL,
ReportGotten CHAR(1) CHECK (ReportGotten IN (0,1)) NOT NULL,
OtherGrants CHAR(1) CHECK (OtherGrants IN (0,1)) NOT NULL,
AppliedPerAttendee CHAR(1) CHECK (AppliedPerAttendee IN (0,1)) NOT NULL,
PRIMARY KEY (ViewAbiID, UnixID)
);


CREATE TABLE ViewThesis (
ViewThesisID INTEGER NOT NULL,
UnixID VARCHAR2(9) NOT NULL,
ThesisTimeDone CHAR(1) CHECK (ThesisTimeDone IN (0,1)) NOT NULL,
ThesisTimeLeft CHAR(1) CHECK (ThesisTimeLeft IN (0,1)) NOT NULL,
Stations CHAR(1) CHECK (Stations IN (0,1)) NOT NULL,
WishedWeeks CHAR(1) CHECK (WishedWeeks IN (0,1)) NOT NULL,
PRIMARY KEY (ViewThesisID, UnixID)
);


CREATE TABLE ViewStudyGroup (
ViewStudyGroupID INTEGER NOT NULL,
UnixID VARCHAR2(9) NOT NULL,
FieldOfScience CHAR(1) CHECK (FieldOfScience IN (0,1)) NOT NULL,
EstimatedStudentCount CHAR(1) CHECK (EstimatedStudentCount IN (0,1)) NOT NULL,
ReportGotten CHAR(1) CHECK (ReportGotten IN (0,1)) NOT NULL,
HoursTotal CHAR(1) CHECK (HoursTotal IN (0,1)) NOT NULL,
TeachingType CHAR(1) CHECK (TeachingType IN (0,1)) NOT NULL,
SalaryTotal CHAR(1) CHECK (SalaryTotal IN (0,1)) NOT NULL,
PRIMARY KEY (ViewStudyGroupID, UnixID)
);


CREATE TABLE ViewFraternity (
ViewFraternityID INTEGER NOT NULL,
UnixID VARCHAR2(9) NOT NULL,
OrgName CHAR(1) CHECK (OrgName IN (0,1)) NOT NULL,
OrgFieldOfScience CHAR(1) CHECK (OrgFieldOfScience IN (0,1)) NOT NULL,
RelationToStudies CHAR(1) CHECK (RelationToStudies IN (0,1)) NOT NULL,
Turnout CHAR(1) CHECK (Turnout IN (0,1)) NOT NULL,
OtherGrants CHAR(1) CHECK (OtherGrants IN (0,1)) NOT NULL,
AppliedPerAttendee CHAR(1) CHECK (AppliedPerAttendee IN (0,1)) NOT NULL,
EstimatedExpense CHAR(1) CHECK (EstimatedExpense IN (0,1)) NOT NULL,
AppliedAmount CHAR(1) CHECK (AppliedAmount IN (0,1)) NOT NULL,
PRIMARY KEY (ViewFraternityID, UnixID)
);



CREATE TABLE ViewForeignBasic (
ViewForeignBasicID INTEGER NOT NULL,
UnixID VARCHAR2(9) NOT NULL,
Attendee CHAR(1) CHECK (Attendee IN (0,1)) NOT NULL,
Nationality CHAR(1) CHECK (Nationality IN (0,1)) NOT NULL,
CreditUnitsLastYear CHAR(1) CHECK (CreditUnitsLastYear IN (0,1)) NOT NULL,
CreditUnitsEstimated CHAR(1) CHECK (CreditUnitsEstimated IN (0,1)) NOT NULL,
EstimatedGraduationTime CHAR(1) CHECK (EstimatedGraduationTime IN (0,1)) NOT NULL,
ThesisState CHAR(1) CHECK (ThesisState IN (0,1)) NOT NULL,
ThesisTimeLeft CHAR(1) CHECK (ThesisTimeLeft IN (0,1)) NOT NULL,
Recommendation1 CHAR(1) CHECK (Recommendation1 IN (0,1)) NOT NULL,
Recommendation2 CHAR(1) CHECK (Recommendation2 IN (0,1)) NOT NULL,
PRIMARY KEY (ViewForeignBasicID, UnixID)
);


CREATE TABLE ViewForeignPost (
ViewForeignPostID INTEGER NOT NULL,
UnixID VARCHAR2(9) NOT NULL,
Attendee CHAR(1) CHECK (Attendee IN (0,1)) NOT NULL,
Nationality CHAR(1) CHECK (Nationality IN (0,1)) NOT NULL,
CreditUnitsLastYear CHAR(1) CHECK (CreditUnitsLastYear IN (0,1)) NOT NULL,
TakesPostGraduateDegree CHAR(1) CHECK (TakesPostGraduateDegree IN (0,1)) NOT NULL,
DegreeLeft CHAR(1) CHECK (DegreeLeft IN (0,1)) NOT NULL,
ResearchDone CHAR(1) CHECK (ResearchDone IN (0,1)) NOT NULL,
Recommendation1 CHAR(1) CHECK (Recommendation1 IN (0,1)) NOT NULL,
Recommendation2 CHAR(1) CHECK (Recommendation2 IN (0,1)) NOT NULL,
PRIMARY KEY (ViewForeignPostID, UnixID)
);



CREATE TABLE Scholarship (
ScholarshipID INTEGER NOT NULL,
Name VARCHAR2(100) NOT NULL,
ForBasicStudent CHAR(1) CHECK (ForBasicStudent IN (0,1)) NOT NULL,
ForPostStudent CHAR(1) CHECK (ForPostStudent IN (0,1)) NOT NULL,
CanApplyManyAtOneTime CHAR(1) CHECK (CanApplyManyAtOneTime IN (0,1)) NOT NULL,
CanGetMany CHAR(1) CHECK (CanGetMany IN (0,1)) NOT NULL,
HasRecommendations CHAR(1) CHECK (HasRecommendations IN (0,1)) NOT NULL,
PRIMARY KEY (ScholarshipID)
);

INSERT INTO Scholarship (ScholarshipID, Name, ForBasicStudent, ForPostStudent, CanApplyManyAtOneTime, CanGetMany, HasRecommendations)
VALUES (1, 'Abi', '1', '1', '1', '1', '0');

INSERT INTO Scholarship (ScholarshipID, Name, ForBasicStudent, ForPostStudent, CanApplyManyAtOneTime, CanGetMany, HasRecommendations)
VALUES (2, 'Thesis', '1', '0', '0', '0', '1');

INSERT INTO Scholarship (ScholarshipID, Name, ForBasicStudent, ForPostStudent, CanApplyManyAtOneTime, CanGetMany, HasRecommendations)
VALUES (3, 'StudyGroup', '1', '1', '1', '1', '0');

INSERT INTO Scholarship (ScholarshipID, Name, ForBasicStudent, ForPostStudent, CanApplyManyAtOneTime, CanGetMany, HasRecommendations)
VALUES (4, 'Fraternity', '1', '1', '1', '1', '0');


CREATE TABLE ApplyingPeriod (
ApplyingPeriodID INTEGER NOT NULL,
ScholarshipID INTEGER NOT NULL,
BeginDate DATE NOT NULL,
EndDate DATE NOT NULL,
HasRecommendationsOn CHAR(1) CHECK (HasRecommendationsOn IN (0,1)) NOT NULL,
PRIMARY KEY (ApplyingPeriodID),
FOREIGN KEY (ScholarshipID)
 REFERENCES Scholarship
);

insert into applyingperiod (ApplyingPeriodID, ScholarshipID, BeginDate, EndDate, HasRecommendationsOn)
 values (0, 1, '01-05-2006', '01-06-2006','1');
 
 
 
CREATE TABLE ApplicationState (
ApplicationStateID INTEGER NOT NULL,
Name VARCHAR2(40) NOT NULL,
PRIMARY KEY (ApplicationStateID)
);


CREATE TABLE BiggestApplicationID (
SingletonID INTEGER NOT NULL,
BiggestID INTEGER NOT NULL
 CHECK (BiggestID > -1),
PRIMARY KEY (SingletonID)
);


CREATE TABLE Filler (
ApplicationID INTEGER NOT NULL,		/*FillerID:itä on monta samaa, mutta ApplicationID:itä vain yksi samaa.*/
FillerID INTEGER NOT NULL,
FillerUnixID VARCHAR2(9) NOT NULL,
FillerStudentID VARCHAR2(20) NOT NULL,
FillerPostGraduate CHAR(1) CHECK (FillerPostGraduate IN (0,1)) NOT NULL,
FillerName VARCHAR2(100) NOT NULL,
FillerEmail VARCHAR2(100),
FillerPhoneNo VARCHAR2(30),
FillerStreetAddress VARCHAR2(100),
FillerPostNo VARCHAR2(10),
FillerPostOffice VARCHAR2(30),
FillerNationality VARCHAR2(50),
PRIMARY KEY (ApplicationID)
);



CREATE TABLE ApplicationAbi (
ApplicationAbiID INTEGER NOT NULL,
ScholarshipID INTEGER NOT NULL,
ApplyingPeriodID INTEGER NOT NULL,
ApplicationStateID INTEGER NOT NULL,
Signed CHAR(1) CHECK (Signed IN (0,1)) NOT NULL,
SignedDate DATE,
DeletedBy VARCHAR2(9),
DeletedDate DATE,
HolderAmount INTEGER,		/*tästä alkaa abiin liittyvät tiedot*/
FieldsOfStudy VARCHAR2(250),
ReasonsForAbiAmount VARCHAR2(1000),
EventDescription VARCHAR2(1000),
OtherGrants INTEGER,
ApplicantName VARCHAR2(100),
ApplicantAccountNo VARCHAR2(30),
CONSTRAINT ApplicationAbi_pkey
 PRIMARY KEY (ApplicationAbiID),
CONSTRAINT ApplicationAbi_f1
 FOREIGN KEY (ScholarshipID)
  REFERENCES Scholarship,
CONSTRAINT ApplicationAbi_f2
 FOREIGN KEY (ApplyingPeriodID)
  REFERENCES ApplyingPeriod,
CONSTRAINT ApplicationAbi_f3
 FOREIGN KEY (ApplicationStateID)
  REFERENCES ApplicationState
);



CREATE TABLE Abi_info (
Abi_infoID INTEGER NOT NULL,
ApplicationAbiID INTEGER NOT NULL,
Municipality VARCHAR2(50) NOT NULL,
Event VARCHAR2(100),
AbiAmount INTEGER,
PRIMARY KEY (Abi_infoID),
FOREIGN KEY (ApplicationAbiID)
 REFERENCES ApplicationAbi
);



CREATE TABLE RealizedAbi_info (
RealizedAbi_infoID INTEGER NOT NULL,
ApplicationAbiID INTEGER NOT NULL,
Municipality VARCHAR2(50) NOT NULL,
Event VARCHAR2(100),
AbiAmount INTEGER,
PRIMARY KEY (RealizedAbi_infoID),
FOREIGN KEY (ApplicationAbiID)
 REFERENCES ApplicationAbi
);


CREATE TABLE Expenses (
ExpensesID INTEGER NOT NULL,
ApplicationID INTEGER NOT NULL,
Transport INTEGER
 CHECK (Transport > -1),
TransportDescription VARCHAR2(100),
Accommodation INTEGER
 CHECK (Accommodation > -1),
AccommodationDescription VARCHAR2(100),
Eating INTEGER
 CHECK (Eating > -1),
EatingDescription VARCHAR2(100),
Other INTEGER
 CHECK (Other > -1),
OtherDescription VARCHAR2(100),
PRIMARY KEY (ExpensesID),
FOREIGN KEY (ApplicationID)
 REFERENCES ApplicationAbi,
FOREIGN KEY (ApplicationID)				/*ID CAN REFER TO 2 TABLES*/
 REFERENCES ApplicationFraternity
);


CREATE TABLE RealizedExpenses (
RealizedExpensesID INTEGER NOT NULL,
ApplicationID INTEGER NOT NULL,
Transport INTEGER
 CHECK (Transport > -1),
TransportDescription VARCHAR2(100),
Accommodation INTEGER
 CHECK (Accommodation > -1),
AccommodationDescription VARCHAR2(100),
Eating INTEGER
 CHECK (Eating > -1),
EatingDescription VARCHAR2(100),
Other INTEGER
 CHECK (Other > -1),
OtherDescription VARCHAR2(100),
PRIMARY KEY (RealizedExpensesID),
FOREIGN KEY (ApplicationID)
 REFERENCES ApplicationAbi,
FOREIGN KEY (ApplicationID)				/*ID CAN REFER TO 2 TABLES*/
 REFERENCES ApplicationFraternity
);



CREATE TABLE ApplicationThesis (
ApplicationThesisID INTEGER NOT NULL,
ScholarshipID INTEGER NOT NULL,
ApplyingPeriodID INTEGER NOT NULL,
ApplicationStateID INTEGER NOT NULL,
Signed CHAR(1) CHECK (Signed IN (0,1)) NOT NULL,
SignedDate DATE,
DeletedBy VARCHAR2(9),
DeletedDate DATE,
Subject VARCHAR2(200),	/*tästä alkaa thesis-tyypin omat tiedot*/
BeginDate DATE,
EndDate DATE,
ShortDescription VARCHAR2(1000),
ReasonsForGrant VARCHAR2(1000),
WishedDate DATE,
WishedLength INT,
ReasonsForStations VARCHAR2(1000),
Station1 INTEGER,
Station2 INTEGER,
Station3 INTEGER,
Station4 INTEGER,
CONSTRAINT ApplicationThesis_pkey
 PRIMARY KEY (ApplicationThesisID),
CONSTRAINT ApplicationThesis_f1
 FOREIGN KEY (ScholarshipID)
  REFERENCES Scholarship,
CONSTRAINT ApplicationThesis_f2
 FOREIGN KEY (ApplyingPeriodID)
  REFERENCES ApplyingPeriod,
CONSTRAINT ApplicationThesis_f3
 FOREIGN KEY (ApplicationStateID)
  REFERENCES ApplicationState
);


CREATE TABLE Stations (
StationsID INTEGER NOT NULL,
Name VARCHAR2(100) NOT NULL,
PRIMARY KEY (StationsID)
);


CREATE TABLE RecommendationThesis (
RecommendationID VARCHAR2(100) NOT NULL,
IsValid CHAR(1) CHECK (IsValid IN (0,1)),
ApplicationThesisID INTEGER NOT NULL,
RecommenderName VARCHAR2(100),
ApplierGoodForGrant CHAR(1) CHECK (ApplierGoodForGrant IN (0,1)),
ReasonsForYes VARCHAR2(500),
RecommendationSigned CHAR(1) CHECK (RecommendationSigned IN (0,1)),
PRIMARY KEY (RecommendationID),
FOREIGN KEY (ApplicationThesisID)
 REFERENCES ApplicationThesis
);


CREATE TABLE ReportThesis (
ReportThesisID INTEGER NOT NULL,
ApplicationThesisID INTEGER NOT NULL,
GrantUsedDate DATE,
DescriptionOfWork VARCHAR2(1000),
PeriodSignificanceForThesis VARCHAR2(1000),
SuggestionsForDevelopment VARCHAR2(1000),
PRIMARY KEY (ReportThesisID),
FOREIGN KEY (ApplicationThesisID)
 REFERENCES ApplicationThesis
);



CREATE TABLE ApplicationStudyGroup (
ApplicationStudyGroupID INTEGER NOT NULL,
ScholarshipID INTEGER NOT NULL,
ApplyingPeriodID INTEGER NOT NULL,
ApplicationStateID INTEGER NOT NULL,
Signed CHAR(1) CHECK (Signed IN (0,1)) NOT NULL,
SignedDate DATE,
DeletedBy VARCHAR2(9),
DeletedDate DATE,
ApplicantName VARCHAR2(100),		/*tästä alkaa StudyGroup-tyypin omat tiedot*/
ApplicantAccountNo VARCHAR2(50),
Subject VARCHAR2(100),
FieldOfScience VARCHAR2(100),
ReasonsForStudyGroup VARCHAR2(1000),
AttendeeAmount INTEGER,
ReasonsForAmount VARCHAR2(200),
FreeText VARCHAR2 (500),
CONSTRAINT ApplicationStudyGroup_pkey
 PRIMARY KEY (ApplicationStudyGroupID),
CONSTRAINT ApplicationStudyGroup_f1
 FOREIGN KEY (ScholarshipID)
  REFERENCES Scholarship,
CONSTRAINT ApplicationStudyGroup_f2
 FOREIGN KEY (ApplyingPeriodID)
  REFERENCES ApplyingPeriod,
CONSTRAINT ApplicationStudyGroup_f3
 FOREIGN KEY (ApplicationStateID)
  REFERENCES ApplicationState
);


CREATE TABLE TeachingHours (
TeachingHoursID INTEGER NOT NULL,
ApplicationStudyGroupID INTEGER NOT NULL,
Pro INTEGER,
Dos INTEGER,
Toh INTEGER,
Lis INTEGER,
HighDegree INTEGER,
LowDegree INTEGER,
PRIMARY KEY (TeachingHoursID),
FOREIGN KEY (ApplicationStudyGroupID)
 REFERENCES ApplicationStudyGroup
);


CREATE TABLE TeachingSubjects (
TeachingSubjectsID INTEGER NOT NULL,
ApplicationStudyGroupID INTEGER NOT NULL,
Subject VARCHAR2(100),
Hours INTEGER,
PRIMARY KEY (TeachingSubjectsID),
FOREIGN KEY (ApplicationStudyGroupID)
 REFERENCES ApplicationStudyGroup
);


CREATE TABLE RealizedTeachingHours (
RealizedTeachingHoursID INTEGER NOT NULL,
ApplicationStudyGroupID INTEGER NOT NULL,
Pro INTEGER,
Dos INTEGER,
Toh INTEGER,
Lis INTEGER,
HighDegree INTEGER,
LowDegree INTEGER,
PRIMARY KEY (RealizedTeachingHoursID),
FOREIGN KEY (ApplicationStudyGroupID)
 REFERENCES ApplicationStudyGroup
);


CREATE TABLE ReportStudyGroup (
ReportStudyGroupID INTEGER NOT NULL,
ApplicationStudyGroupID INTEGER NOT NULL,
BasicDegreeAmount INTEGER,
PostDegreeAmount INTEGER,
FieldOfScience VARCHAR2(100),
DescriptionOfStudying VARCHAR2(100),
StudyGroupSignificance VARCHAR2(1000),
TeachingFacilitys VARCHAR2(1000),
SuggestionsForDevelopment VARCHAR2(1000),
PRIMARY KEY (ReportStudyGroupID),
FOREIGN KEY (ApplicationStudyGroupID)
 REFERENCES ApplicationStudyGroup
);



CREATE TABLE ApplicationFraternity (
ApplicationFraternityID INTEGER NOT NULL,
ScholarshipID INTEGER NOT NULL,
ApplyingPeriodID INTEGER NOT NULL,
ApplicationStateID INTEGER NOT NULL,
Signed CHAR(1) CHECK (Signed IN (0,1)) NOT NULL,
SignedDate DATE,
DeletedBy VARCHAR2(9),
DeletedDate DATE,
ProjectName VARCHAR2(100),		/*tästä alkaa Fraternity-tyypin omat tiedot*/
RelationToStudies VARCHAR2(1000),
ProjectGoals VARCHAR2(1000),
Schedule VARCHAR2(1000),
AttendeeAmount INTEGER,
OtherGrants INTEGER,
CONSTRAINT ApplicationFraternity_pkey
 PRIMARY KEY (ApplicationFraternityID),
CONSTRAINT ApplicationFraternity_f1
 FOREIGN KEY (ScholarshipID)
  REFERENCES Scholarship,
CONSTRAINT ApplicationFraternity_f2
 FOREIGN KEY (ApplyingPeriodID)
  REFERENCES ApplyingPeriod,
CONSTRAINT ApplicationFraternity_f3
 FOREIGN KEY (ApplicationStateID)
  REFERENCES ApplicationState
);
















/*Ei toteuteta tästä alaspäin----------------------------------------------------------------------------------------------------*/





CREATE TABLE ApplicationForeignBasic (
ApplicationForeignBasicID INTEGER NOT NULL,
ScholarshipID INTEGER NOT NULL,
ApplyingPeriodID INTEGER NOT NULL,
ApplicationStateID INTEGER NOT NULL,
Signed CHAR(1) CHECK (Signed IN (0,1)) NOT NULL,
SignedDate DATE,
DeletedBy VARCHAR2(9),
DeletedDate DATE,
PlannedCreditUnits INTEGER,		/*tästä alkaa ForeignBasic-tyypin omat tiedot*/
CraduationDate DATE,
ThesisState INTEGER,
RecommendationId1 VARCHAR2(50),
RecommendationId2 VARCHAR2(50),
ThesisName VARCHAR2(200),
ThesisBeginDate DATE,
ThesisEndDate DATE,
CONSTRAINT ApplicationForeignBasic_pkey
 PRIMARY KEY (ApplicationForeignBasicID),
CONSTRAINT ApplicationForeignBasic_f1
 FOREIGN KEY (ScholarshipID)
  REFERENCES Scholarship,
CONSTRAINT ApplicationForeignBasic_f2
 FOREIGN KEY (ApplyingPeriodID)
  REFERENCES ApplyingPeriod,
CONSTRAINT ApplicationForeignBasic_f3
 FOREIGN KEY (ApplicationStateID)
  REFERENCES ApplicationState
);


CREATE TABLE RecommenderForeignBasic (
RecommenderForeignBasicID INTEGER NOT NULL,
ApplicationForeignBasicID INTEGER NOT NULL,
Name VARCHAR2(100),
Appointment VARCHAR2(100),
HowKnowsApplicant VARCHAR2(1000),
HYEmployee CHAR(1) CHECK (HYEmployee IN (0,1)),
UnixId VARCHAR2(9),
PRIMARY KEY (RecommenderForeignBasicID),
FOREIGN KEY (ApplicationForeignBasicID)
 REFERENCES ApplicationForeignBasic
);


CREATE TABLE ValuationForeignBasic (
ValuationForeignBasicID INTEGER NOT NULL,
ApplicationForeignBasicID INTEGER NOT NULL,
HasOtherGrants INTEGER,
HasSalary INTEGER,
StudyingSuccess INTEGER,
StudyingFacility INTEGER,
Activity INTEGER,
Motivation INTEGER,
WorkPlanning INTEGER,
ResearchSignificance INTEGER,
PRIMARY KEY (ValuationForeignBasicID),
FOREIGN KEY (ApplicationForeignBasicID)
 REFERENCES ApplicationForeignBasic
);



CREATE TABLE ApplicationForeignPost (
ApplicationForeignPostID INTEGER NOT NULL,
ScholarshipID INTEGER NOT NULL,
ApplyingPeriodID INTEGER NOT NULL,
ApplicationStateID INTEGER NOT NULL,
Signed CHAR(1) CHECK (Signed IN (0,1)) NOT NULL,
SignedDate DATE,
DeletedBy VARCHAR2(9),
DeletedDate DATE,
PostGraduateDegree CHAR(1) CHECK (PostGraduateDegree IN (0,1)),		/*tästä alkaa ForeignPost-tyypin omat tiedot*/
ResearchSubject VARCHAR2(200),
ResearchBeginDate DATE,
ResearchEndDate DATE,
CONSTRAINT ApplicationForeignPost_pkey
 PRIMARY KEY (ApplicationForeignPostID),
CONSTRAINT ApplicationForeignPost_f1
 FOREIGN KEY (ScholarshipID)
  REFERENCES Scholarship,
CONSTRAINT ApplicationForeignPost_f2
 FOREIGN KEY (ApplyingPeriodID)
  REFERENCES ApplyingPeriod,
CONSTRAINT ApplicationForeignPost_f3
 FOREIGN KEY (ApplicationStateID)
  REFERENCES ApplicationState
);


CREATE TABLE RecommenderForeignPost (
RecommenderForeignPostID INTEGER NOT NULL,
ApplicationForeignPostID INTEGER NOT NULL,
Name VARCHAR2(100),
Appointment VARCHAR2(100),
HowKnowsApplicant VARCHAR2(1000),
HYEmployee CHAR(1) CHECK (HYEmployee IN (0,1)),
UnixId VARCHAR2(9),
Company VARCHAR2(100),
PhoneNo VARCHAR2(30),
Email VARCHAR2(100),
PRIMARY KEY (RecommenderForeignPostID),
FOREIGN KEY (ApplicationForeignPostID)
 REFERENCES ApplicationForeignPost
);


CREATE TABLE ValuationForeignPost (
ValuationForeignPostID INTEGER NOT NULL,
ApplicationForeignPostID INTEGER NOT NULL,
Phase VARCHAR2(1000),
WorkSignificance VARCHAR2(1000),
Publications CHAR(1) CHECK (Publications IN (0,1)),
PublicationsSignificance VARCHAR2(1000),
PRIMARY KEY (ValuationForeignPostID),
FOREIGN KEY (ApplicationForeignPostID)
 REFERENCES ApplicationForeignPost
);