database-schema.sql

CREATE DATABASE 'D:\Furqan.fdb' USER 'SYSDBA' PASSWORD 'masterkey' PAGE_SIZE 8192 DEFAULT CHARACTER SET UTF8;

CONNECT 'D:\Furqan.fdb';

CREATE TABLE CLASS (
  ID 	BIGINT NOT NULL PRIMARY KEY,
  NAME 	VARCHAR(40) NOT NULL UNIQUE,
  LEVEL CHAR DEFAULT 0 CHECK (LEVEL IN (0, 1, 2, 3))
);
 
CREATE TABLE COURSE (
  ID	   BIGINT NOT NULL PRIMARY KEY,
  NAME	   VARCHAR(40) NOT NULL UNIQUE,
  SEMESTER CHAR DEFAULT 0 CHECK (SEMESTER IN (0, 1, 2))
);

CREATE TABLE STUDENT (
  ID		  	BIGINT NOT NULL PRIMARY KEY,
  FULL_NAME		VARCHAR(40) NOT NULL,
  FATHER_NAME 	VARCHAR(20),
  GENDER 	  	CHAR DEFAULT 0 CHECK (GENDER IN (0, 1, 2)),
  BIRTH_PLACE 	VARCHAR(20),
  BIRTH_DATE  	DATE,
  RESIDENCY   	VARCHAR(20),
  NATIONALITY 	VARCHAR(20),
  NATIONAL_ID 	VARCHAR(20),
  MOBILE 		VARCHAR(20),
  FATHER_JOB 	VARCHAR(20),
  LEVEL 		CHAR DEFAULT 0 CHECK (LEVEL IN (0, 1, 2, 3)),
  CLASS			BIGINT NOT NULL,
  CONSTRAINT student_class_fk FOREIGN KEY (CLASS) REFERENCES CLASS ON DELETE CASCADE ON UPDATE CASCADE, 
  CONSTRAINT student_name_uk UNIQUE (FULL_NAME, FATHER_NAME)
);

CREATE TABLE TEACHER (
  ID		  	BIGINT NOT NULL PRIMARY KEY,
  FULL_NAME		VARCHAR(40) NOT NULL,
  FATHER_NAME 	VARCHAR(20),
  GENDER 	  	CHAR DEFAULT 0 CHECK (GENDER IN (0, 1, 2)),
  BIRTH_PLACE 	VARCHAR(20),
  BIRTH_DATE  	DATE,
  RESIDENCY   	VARCHAR(20),
  NATIONALITY 	VARCHAR(20),
  NATIONAL_ID 	VARCHAR(20),
  MOBILE 		VARCHAR(20),
  QUALIFICATION	VARCHAR(40),
  EXPERIENCE	VARCHAR(40),
  CONSTRAINT teacher_name_uk UNIQUE (FULL_NAME, FATHER_NAME)
);

CREATE TABLE JOB (
  ID	BIGINT NOT NULL PRIMARY KEY,
  NAME 	VARCHAR(40) NOT NULL UNIQUE
);

CREATE TABLE EMPLOYEE (
  ID		  	BIGINT NOT NULL PRIMARY KEY,
  FULL_NAME		VARCHAR(40) NOT NULL,
  FATHER_NAME 	VARCHAR(20),
  GENDER 	  	CHAR DEFAULT 0 CHECK (GENDER IN (0, 1, 2)),
  BIRTH_PLACE 	VARCHAR(20),
  BIRTH_DATE  	DATE,
  RESIDENCY   	VARCHAR(20),
  NATIONALITY 	VARCHAR(20),
  NATIONAL_ID 	VARCHAR(20),
  MOBILE 		VARCHAR(20),
  JOB 			BIGINT NOT NULL,
  OTHER_JOB 	VARCHAR(20),
  CONSTRAINT employee_job_fk FOREIGN KEY (JOB) REFERENCES JOB ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT employee_name_uk UNIQUE (FULL_NAME, FATHER_NAME)
);

CREATE TABLE TEACHING (
  ID		 BIGINT NOT NULL PRIMARY KEY,
  TEACHER	 BIGINT NOT NULL,
  CLASS 	 BIGINT NOT NULL,
  COURSE	 BIGINT NOT NULL,
  CONSTRAINT teaching_class_fk FOREIGN KEY (CLASS) REFERENCES CLASS ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT teaching_teacher_fk FOREIGN KEY (TEACHER) REFERENCES TEACHER ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT teaching_course_fk FOREIGN KEY (COURSE) REFERENCES COURSE ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT teacher_class_course_uk UNIQUE (TEACHER, CLASS, COURSE)
);

CREATE TABLE RESULT (
  ID		 BIGINT NOT NULL PRIMARY KEY,
  COURSE	 BIGINT NOT NULL,
  STUDENT 	 BIGINT NOT NULL,
  RESULT	 DECIMAL(2,2) CHECK (RESULT BETWEEN 0 AND 100),
  CONSTRAINT result_course_fk FOREIGN KEY (COURSE) REFERENCES COURSE ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT result_student_fk FOREIGN KEY (STUDENT) REFERENCES STUDENT ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT course_student_uk UNIQUE (COURSE, STUDENT)
);

CREATE TABLE QURAN (
  ID		 	BIGINT NOT NULL PRIMARY KEY,
  STUDENT 	 	BIGINT NOT NULL,
  RECITAL_TYPE 	CHAR DEFAULT 0 CHECK (RECITAL_TYPE IN (0, 1, 2, 3)),
  FROM_PAGE  	SMALLINT CHECK (FROM_PAGE BETWEEN 0 AND 604),
  TO_PAGE 	 	SMALLINT CHECK (TO_PAGE BETWEEN 0 AND 604),
  "DATE"	 	DATE,
  CONSTRAINT quran_student_fk FOREIGN KEY (STUDENT) REFERENCES STUDENT ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE COMPETITION (
  ID	 BIGINT NOT NULL PRIMARY KEY,
  NAME 	 VARCHAR(40) NOT NULL UNIQUE,
  TYPE	 CHAR DEFAULT 0 CHECK (TYPE IN (0, 1, 2, 3)),
  PARTS  VARCHAR(40),
  "DATE" DATE
);

CREATE TABLE COMPETITOR (
  ID		  BIGINT NOT NULL PRIMARY KEY,
  COMPETITION BIGINT NOT NULL,
  STUDENT	  BIGINT NOT NULL,
  RANK		  BIGINT,
  CONSTRAINT competitor_competition_fk FOREIGN KEY (COMPETITION) REFERENCES COMPETITION ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT competitor_student_fk FOREIGN KEY (STUDENT) REFERENCES STUDENT ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT competition_student_uk UNIQUE (COMPETITION, STUDENT)
);

SET TERM ^ ;

CREATE TRIGGER STUDENT_TRIGGER FOR STUDENT
ACTIVE AFTER INSERT
POSITION 0
AS
DECLARE VARIABLE NEW_RESULT_ID BIGINT;
DECLARE VARIABLE CURRENT_COURSE_ID BIGINT;
BEGIN
	SELECT MAX(ID) FROM RESULT INTO :NEW_RESULT_ID;
            
    IF (NEW_RESULT_ID IS NULL) THEN
    	NEW_RESULT_ID = 0;
                
	FOR SELECT ID FROM COURSE INTO :CURRENT_COURSE_ID
	DO
		BEGIN
			NEW_RESULT_ID = NEW_RESULT_ID + 1;
			INSERT INTO RESULT VALUES(:NEW_RESULT_ID, :CURRENT_COURSE_ID, NEW.ID, NULL);
		END
END^

CREATE TRIGGER COURSE_TRIGGER FOR COURSE
ACTIVE AFTER INSERT
POSITION 0
AS
DECLARE VARIABLE NEW_RESULT_ID BIGINT;
DECLARE VARIABLE CURRENT_STUDENT_ID BIGINT;
BEGIN
	SELECT MAX(ID) FROM RESULT INTO :NEW_RESULT_ID;
            
    IF (NEW_RESULT_ID IS NULL) THEN
    	NEW_RESULT_ID = 0;
        
	FOR SELECT ID FROM STUDENT INTO :CURRENT_STUDENT_ID
	DO
		BEGIN
            NEW_RESULT_ID = NEW_RESULT_ID + 1;
			INSERT INTO RESULT VALUES(:NEW_RESULT_ID, NEW.ID, :CURRENT_STUDENT_ID, NULL);
		END
END^

SET TERM ; ^

COMMIT;