CREATE Statements

CREATE TABLE School_Info (
            SchName VARCHAR2(30) NOT NULL,
            SchPostCode NUMBER(5) NOT NULL,
            SchAddr VARCHAR2(30) NOT NULL,
            SchCity VARCHAR2(20) NOT NULL,
            SchState VARCHAR2(20) NOT NULL,
            SchType VARCHAR2(6) NOT NULL,
            CONSTRAINT PK_SchoolInfo PRIMARY KEY (SchName, SchPostCode)
);

CREATE TABLE School (
            SchCode NUMBER(10) PRIMARY KEY NOT NULL,
            SchName VARCHAR2(30) NOT NULL,
            SchPostCode NUMBER(5) NOT NULL,
            SchPassword VARCHAR2(12) NOT NULL,
            CONSTRAINT FK_SchName_School FOREIGN KEY (SchName, SchPostCode) REFERENCES School_Info (SchName, SchPostCode)
);

CREATE TABLE School_Email (
            SchEmail VARCHAR2(30) PRIMARY KEY NOT NULL,
            SchCode NUMBER(10) NOT NULL,
            CONSTRAINT FK_SchCode_SchoolEmail FOREIGN KEY (SchCode) REFERENCES School (SchCode)
);

CREATE TABLE School_Phone (
            SchPhoneNo NUMBER(12) PRIMARY KEY NOT NULL,
            SchCode NUMBER(10) NOT NULL,
            CONSTRAINT FK_SchCode_SchoolPhone FOREIGN KEY (SchCode) REFERENCES School (SchCode)
);

CREATE TABLE Organizer_Info  (
            OrgICNo NUMBER(12) PRIMARY KEY NOT NULL,
            OrgName VARCHAR2(30) NOT NULL,
            OrgPosition VARCHAR(20)
);

CREATE TABLE Organizer  (
            OrgID NUMBER(10) GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1) PRIMARY KEY NOT NULL,
            OrgICNo NUMBER(12) NOT NULL,
            OrgPassword VARCHAR2(12) NOT NULL,
            CONSTRAINT FK_OrgICNo_Organizer FOREIGN KEY (OrgICNo) REFERENCES Organizer_Info (OrgICNo)
);

CREATE TABLE Organizer_Email (
            OrgEmail VARCHAR2(30) PRIMARY KEY NOT NULL,
            OrgID NUMBER(10) NOT NULL,
            CONSTRAINT FK_OrgID_OrganizerEmail FOREIGN KEY (OrgID) REFERENCES Organizer (OrgID)
);

CREATE TABLE Organizer_Phone (
            OrgPhoneNo NUMBER(12) PRIMARY KEY NOT NULL,
            OrgID NUMBER(10) NOT NULL,
            CONSTRAINT FK_OrgID_OrganizerPhone FOREIGN KEY (OrgID) REFERENCES Organizer (OrgID)
);

CREATE TABLE Jury_Team (
            JTeamID NUMBER(10) GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1) PRIMARY KEY NOT NULL,
            OrgID NUMBER(10) NOT NULL,
            CONSTRAINT FK_OrgID_Jury_Team FOREIGN KEY (OrgID) REFERENCES Organizer (OrgID)
);

CREATE TABLE Judge_Info (
            JudgeICNo NUMBER(12) PRIMARY KEY NOT NULL,
            JudgeName VARCHAR2(30) NOT NULL
);

CREATE TABLE Judge (
            JudgeID NUMBER(10) GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1) PRIMARY KEY NOT NULL,
            JudgeICNo NUMBER(12) NOT NULL,
            JudgePassword VARCHAR2(12) NOT NULL,
            JTeamID NUMBER(10),
            CONSTRAINT FK_JTeamID_Judge FOREIGN KEY (JTeamID) REFERENCES Jury_Team (JTeamID),
            CONSTRAINT FK_JudgeICNo_Judge FOREIGN KEY (JudgeICNo) REFERENCES Judge_Info (JudgeICNo)
);

CREATE TABLE Judge_Email (
            JudgeEmail VARCHAR2(30) PRIMARY KEY NOT NULL,
            JudgeID NUMBER(10) NOT NULL,
            CONSTRAINT FK_JudgeID_JudgeEmail FOREIGN KEY (JudgeID) REFERENCES Judge (JudgeID)
);

CREATE TABLE Judge_Phone (
            JudgePhoneNo NUMBER(12) PRIMARY KEY NOT NULL,
            JudgeID NUMBER(10) NOT NULL,
            CONSTRAINT FK_JudgeID_JudgePhone FOREIGN KEY (JudgeID) REFERENCES Judge (JudgeID)
);

CREATE TABLE Supervision (
            JudgeID NUMBER(10) NOT NULL,
            CONSTRAINT FK_JudgeID_Supervision FOREIGN KEY (JudgeID) REFERENCES Judge (JudgeID)
);

CREATE TABLE Material_Submission (
            SubmissionTime TIMESTAMP PRIMARY KEY NOT NULL,
            EposterID NUMBER(10) NOT NULL,
            VideoLink VARCHAR2(255) NOT NULL,
            FbLink VARCHAR2(255) NOT NULL
);

CREATE TABLE Material (
            MatID NUMBER(10) GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1) PRIMARY KEY NOT NULL,
            SubmissionTime TIMESTAMP NOT NULL,
            MaterialComment VARCHAR2(255),
            SchCode NUMBER(10) NOT NULL,
            JTeamID NUMBER(10),
            CONSTRAINT FK_SchCode_Material FOREIGN KEY (SchCode) REFERENCES School(SchCode),
            CONSTRAINT FK_JTeamID_Material FOREIGN KEY (JTeamID) REFERENCES Jury_Team (JTeamID),
            CONSTRAINT FK_SubmissionTime_Material FOREIGN KEY (SubmissionTime) REFERENCES Material_Submission (SubmissionTime)
);


CREATE TABLE Judge_Remark (
            JudgingDate DATE NOT NULL,
            JudgeComment VARCHAR2(255),
            FinalMarkVerifyTime TIMESTAMP NOT NULL,
            TotalMark NUMBER(10) NOT NULL,
            MatID NUMBER(10) NOT NULL,
            JudgeID NUMBER(10) NOT NULL,
            CONSTRAINT FK_MatID_JudgeRemark FOREIGN KEY (MatID) REFERENCES Material (MatID),
            CONSTRAINT FK_JudgeID_JudgeRemark FOREIGN KEY (JudgeID) REFERENCES Judge (JudgeID)
);

CREATE TABLE Final_Rank (
            Ranking VARCHAR2(15) NOT NULL,
            MatID NUMBER(10) NOT NULL,
            AnnounceRankDate DATE,
            CONSTRAINT PK_Rank PRIMARY KEY (Ranking, MatID),
            CONSTRAINT FK_MatID_Rank FOREIGN KEY (MatID) REFERENCES Material (MatID)
);

CREATE TABLE Amendment (
            AmmID NUMBER(10) GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1) PRIMARY KEY NOT NULL,
            AmmReason VARCHAR2(255) NOT NULL,
            AmmEvidence VARCHAR2(255),
            MatID NUMBER(10) NOT NULL,
            OrgID NUMBER(10),
            JudgeID NUMBER(10),
            CONSTRAINT FK_MatID_Amendment FOREIGN KEY (MatID) REFERENCES Material (MatID),
            CONSTRAINT FK_OrgID_Ammendment FOREIGN KEY (OrgID) REFERENCES Organizer (OrgID),
            CONSTRAINT FK_JudgeID_Ammendment FOREIGN KEY (JudgeID) REFERENCES Judge(JudgeID)
);