CREATE TABLE TiberDM.ExamDim (
    id                  MEDIUMINT       NOT NULL AUTO_INCREMENT,
    name                Varchar(150)    NOT NULL,
    examDate            Date            NOT NULL,
    effStatus           Char(1)         NOT NULL,
    academicYearDimId   MEDIUMINT       NOT NULL,
    dateUploaded        DateTime        NOT NULL,
    userUploaded        MEDIUMINT       NOT NULL,
    courseDimId         MEDIUMINT       NOT NULL,
    assessmentDimId     MEDIUMINT       NOT NULL,
    importMessage       Varchar(2048)   NOT NULL,
    pevStatus           Char(1)         NOT NULL DEFAULT('N'),
    pevMessage          Varchar(2048)   NULL,
    PRIMARY KEY (id)
);

CREATE Unique INDEX ExamDim_UIDX01
ON TiberDM.ExamDim (name, academicYearDimId);

CREATE INDEX ExamDim_CourseIdx
ON TiberDM.ExamDim (courseDimId, name, academicYearDimId);

CREATE INDEX ExamDim_AssessmentIdx
ON TiberDM.ExamDim(assessmentDimId, name, academicYearDimId);
