CREATE SEQUENCE your_sequence
  START WITH 1
  INCREMENT BY 1;
  
CREATE TABLE "REGION" 
   ("REG_CODE" number PRIMARY KEY, 
	"REGION" VARCHAR2(20 BYTE) ) ;
   
CREATE TABLE  "DISTRICT" 
   (	"DIS_CODE" number  ,"REG_CODE" NUMBER, 
	"DISTRICT" VARCHAR2(100 BYTE), 
	 PRIMARY KEY ("DIS_CODE"), 
	 FOREIGN KEY ("REG_CODE")
	  REFERENCES  "REGION" ("REG_CODE")
   );
  CREATE TABLE  "SCHOOL" 
   ("SCHOOL" VARCHAR2(100 BYTE), 
	"ADDRESS" VARCHAR2(100 BYTE) , 
	"REG_CODE" NUMBER, 
	"DIS_CODE" NUMBER, 
	"PHONE_ONE" NUMBER, 
	"PHONE_TWO" NUMBER, 
	"EMAIL" VARCHAR2(100 BYTE), 
	"LOGO" BLOB, 
	"STATUS" VARCHAR2(10 BYTE), 
	"S_ID" number , 
	"PASSWORD" VARCHAR2(100 BYTE), 
	"USERNAME" VARCHAR2(20 BYTE), 
	 PRIMARY KEY ("S_ID"), 
	 FOREIGN KEY ("REG_CODE")
	  REFERENCES  "REGION" ("REG_CODE"), 
	 FOREIGN KEY ("DIS_CODE")
	  REFERENCES  "DISTRICT" ("DIS_CODE")) ;
      

  CREATE TABLE "SCHOOL_USERS" ("S_ID" NUMBER, 
	"FIRSTNAME" VARCHAR2(30 BYTE), 
	"MIDDLENAME" VARCHAR2(30 BYTE), 
	"LASTNAME" VARCHAR2(30 BYTE), 
	"DOB" VARCHAR2(10 BYTE), 
	"GENDER" VARCHAR2(10 BYTE), 
	"ADDRESS" VARCHAR2(30 BYTE), 
	"MOBILE" NUMBER, 
	"WORK" NUMBER, 
	"EMAIL" VARCHAR2(30 BYTE), 
	"USERNAME" VARCHAR2(30 BYTE) PRIMARY KEY, 
	"PASSWORD" VARCHAR2(100 BYTE), 
	"RIGHTS" VARCHAR2(30 BYTE), 
	"CREATE_DT" DATE, 
	"STATUS" VARCHAR2(10 BYTE), 
	 FOREIGN KEY ("S_ID")
	  REFERENCES "SCHOOL" ("S_ID"));
      
CREATE TABLE CLASS("S_ID" NUMBER, FOREIGN KEY ("S_ID")
	  REFERENCES "SCHOOL" ("S_ID"),CLASS NUMBER PRIMARY KEY,TITLE varchar2(20));

ALTER TABLE CLASS ADD CLASS_TITLE varchar2(30);

CREATE TABLE SUB_CLASS(S_ID NUMBER, FOREIGN KEY ("S_ID")
	  REFERENCES "SCHOOL" ("S_ID"), CLASS NUMBER,FOREIGN KEY ("CLASS")
	  REFERENCES "CLASS" ("CLASS"),SUB_CODE number   PRIMARY KEY,SUB_TITLE varchar2(20));
      
CREATE TABLE DEPARTMENT (DEPT_ID NUMBER PRIMARY KEY,DEPT VARCHAR2(30));

CREATE TABLE GEN_ID(ID NUMBER);

ALTER TABLE GEN_ID ADD GEN NUMBER;

CREATE TABLE EMPLOYEE(S_ID NUMBER, FOREIGN KEY ("S_ID")
	  REFERENCES "SCHOOL" ("S_ID"),FIRSTNAME VARCHAR2(50),MIDDLENAME VARCHAR2(50),LASTNAME VARCHAR2(50),DOB VARCHAR2(10),GENDER VARCHAR2(10),ADDRESS VARCHAR2(50),NATIONALITY VARCHAR2(50), ID VARCHAR2(50),ID_ISSUE VARCHAR2(10),ID_EXPIRY VARCHAR2(50),ID_DOC BLOB, MOBILE NUMBER ,WORK NUMBER , EMAIL VARCHAR2(50), MARITAL_STATUS VARCHAR2(10),EDU_DOC BLOB ,APPOINT_DT VARCHAR2(20),DEPARTMENT VARCHAR2(20),EMP_ID VARCHAR2(20) PRIMARY KEY);
      
ALTER TABLE EMPLOYEE ADD STATUS VARCHAR2(10);      

ALTER TABLE EMPLOYEE ADD PASS_PHOTO BLOB;

ALTER TABLE EMPLOYEE ADD CREATE_DT DATE;


ALTER TABLE EMPLOYEE ADD (DEPT_ID NUMBER,FOREIGN KEY (DEPT_ID) REFERENCES DEPARTMENT(DEPT_ID));

ALTER TABLE EMPLOYEE DROP COLUMN DEPARTMENT;

ALTER TABLE DEPARTMENT ADD (S_ID NUMBER,FOREIGN KEY (S_ID) REFERENCES SCHOOL(S_ID));

ALTER TABLE EMPLOYEE ADD (QUALIF VARCHAR2(50));

CREATE TABLE STUDENT (STUD_ID VARCHAR2(30) PRIMARY KEY,NAME VARCHAR2(100),CREATE_DT DATE,STATUS VARCHAR2(10),S_ID NUMBER,FOREIGN KEY (S_ID) REFERENCES SCHOOL(S_ID));

CREATE TABLE STUDENT_PERSONAL(STUD_ID VARCHAR2(30),FOREIGN KEY (STUD_ID) REFERENCES STUDENT(STUD_ID),S_ID NUMBER,FOREIGN KEY (S_ID) REFERENCES SCHOOL(S_ID),FIRSTNAME VARCHAR2(30),MIDDLENAME VARCHAR2(30),LASTNAME VARCHAR2(30),GENDER VARCHAR2(10),NATION VARCHAR2(50),TRIBE VARCHAR2(10));

CREATE TABLE STUDENT_CONTACT(STUD_ID VARCHAR2(30),FOREIGN KEY (STUD_ID) REFERENCES STUDENT(STUD_ID),S_ID NUMBER,FOREIGN KEY (S_ID) REFERENCES SCHOOL(S_ID),HOME_ADD VARCHAR2(30),EMAIL VARCHAR2(30),PHONE NUMBER,EMERGENCY NUMBER);

CREATE TABLE STUDENT_AUTHOURITY(STUD_ID VARCHAR2(30),FOREIGN KEY (STUD_ID) REFERENCES STUDENT(STUD_ID),S_ID NUMBER,FOREIGN KEY (S_ID) REFERENCES SCHOOL(S_ID),FIRSTNAME VARCHAR2(20),MIDDLENAME VARCHAR2(20),LASTNAME VARCHAR2(20),ADDRESS VARCHAR2(30),PHONE NUMBER,EMAIL VARCHAR2(20),OCCUPATION VARCHAR2(30),RELATION  VARCHAR2(10));

CREATE TABLE STUDENT_MEDICAL(STUD_ID VARCHAR2(30),FOREIGN KEY (STUD_ID) REFERENCES STUDENT(STUD_ID),S_ID NUMBER,FOREIGN KEY (S_ID) REFERENCES SCHOOL(S_ID),ALLERGY VARCHAR2(30),CONDITION VARCHAR2(30),MEDICATIONS VARCHAR2(30));

CREATE TABLE STUDENT_ACADEMIC(STUD_ID VARCHAR2(30),FOREIGN KEY (STUD_ID) REFERENCES STUDENT(STUD_ID),S_ID NUMBER,FOREIGN KEY (S_ID) REFERENCES SCHOOL(S_ID),CLASS NUMBER,FOREIGN KEY ("CLASS") REFERENCES "CLASS" ("CLASS"),PREV_SCHOOL VARCHAR2(30));

CREATE TABLE STUDENT_DOCUMENT(STUD_ID VARCHAR2(30),FOREIGN KEY (STUD_ID) REFERENCES STUDENT(STUD_ID),S_ID NUMBER,FOREIGN KEY (S_ID) REFERENCES SCHOOL(S_ID),BIRTH BLOB,PASSPORT BLOB,PASS_PHOTO BLOB);

CREATE TABLE SGEN_ID(ID NUMBER );

ALTER TABLE SGEN_ID ADD GEN NUMBER;

ALTER TABLE STUDENT_PERSONAL ADD DOB VARCHAR2(10);

ALTER TABLE STUDENT_AUTHOURITY MODIFY EMAIL VARCHAR2(30);

CREATE TABLE WAEC_SUBJECT(SUB_CODE NUMBER PRIMARY KEY,SUBJECT VARCHAR2(50)); 

CREATE TABLE SUBJECT(SUB_CODE NUMBER,FOREIGN KEY (SUB_CODE) REFERENCES WAEC_SUBJECT(SUB_CODE),SUBJECT varchar2(100),SUB_TYPE varchar2(100),CLASS NUMBER , FOREIGN KEY (CLASS) REFERENCES CLASS(CLASS),S_ID NUMBER, FOREIGN KEY ("S_ID")
	  REFERENCES "SCHOOL" ("S_ID"));
     
ALTER TABLE SUBJECT ADD (DEPT_ID NUMBER,FOREIGN KEY (DEPT_ID) REFERENCES DEPARTMENT(DEPT_ID));     
     
ALTER TABLE SUB_CLASS ADD CLASS_NAME VARCHAR2(30);

CREATE TABLE CLASS_STUDENT(STUD_ID VARCHAR2(30),FOREIGN KEY (STUD_ID) REFERENCES STUDENT(STUD_ID),S_ID NUMBER,FOREIGN KEY (S_ID) REFERENCES SCHOOL(S_ID),CLASS NUMBER,FOREIGN KEY ("CLASS")
	  REFERENCES "CLASS" ("CLASS"),SUB_CODE NUMBER,FOREIGN KEY ("SUB_CODE")
	  REFERENCES "SUB_CLASS" ("SUB_CODE"));

CREATE TABLE STUDENT_SUBJECT(STUD_ID VARCHAR2(30),FOREIGN KEY (STUD_ID) REFERENCES STUDENT(STUD_ID),S_ID NUMBER,FOREIGN KEY (S_ID) REFERENCES SCHOOL(S_ID),CLASS NUMBER,FOREIGN KEY ("CLASS")
	  REFERENCES "CLASS" ("CLASS"),SUB_CODE NUMBER,FOREIGN KEY (SUB_CODE) REFERENCES WAEC_SUBJECT(SUB_CODE));



ALTER TABLE SCHOOL ADD TYPE VARCHAR2(20);

ALTER TABLE WAEC_SUBJECT ADD SUB_GROUP VARCHAR2(30);
 
ALTER TABLE WAEC_SUBJECT DROP COLUMN SUB_GROUP;

ALTER TABLE WAEC_SUBJECT ADD EXAM VARCHAR2(10);



ALTER TABLE SUBJECT ADD (SUBS NUMBER ,FOREIGN KEY (SUBS) REFERENCES SUB_CLASS(SUB_CODE));

ALTER TABLE STUDENT_ACADEMIC ADD SCORE VARCHAR2(10);

ALTER TABLE STUDENT_ACADEMIC ADD (SUB_CODE NUMBER ,FOREIGN KEY (SUB_CODE) REFERENCES SUB_CLASS(SUB_CODE)); 

ALTER TABLE TUITION ADD (SUB_CODE NUMBER , FOREIGN KEY (SUB_CODE) REFERENCES SUB_CLASS(SUB_CODE));



ALTER TABLE STUDENT MODIFY STATUS VARCHAR2(20);

CREATE TABLE PROGRAMME (PROG VARCHAR2(30),PROG_ID NUMBER PRIMARY KEY);

CREATE TABLE SUB_INCRE(CODE NUMBER, SUB VARCHAR2(30));

CREATE TABLE ACADEMIC_CALENDAR(ACADEMIC_YEAR VARCHAR2(30) PRIMARY KEY,START_DT VARCHAR2(10),END_DT VARCHAR2(10));

CREATE TABLE TERM_CALENDAR(TERM VARCHAR2(50) PRIMARY KEY,START_DT VARCHAR2(10),END_DT VARCHAR2(10));

ALTER TABLE ACADEMIC_CALENDAR ADD (S_ID NUMBER,FOREIGN KEY (S_ID) REFERENCES SCHOOL(S_ID));

ALTER TABLE TERM_CALENDAR ADD (S_ID NUMBER,FOREIGN KEY (S_ID) REFERENCES SCHOOL(S_ID));

ALTER TABLE ACADEMIC_CALENDAR ADD STATUS VARCHAR2(10);

ALTER TABLE TERM_CALENDAR ADD STATUS VARCHAR2(10);

ALTER TABLE STUDENT_ACADEMIC ADD ( ACADEMIC_YEAR VARCHAR2(30),FOREIGN KEY (ACADEMIC_YEAR) REFERENCES ACADEMIC_CALENDAR(ACADEMIC_YEAR));

ALTER TABLE TERM_CALENDAR ADD ( ACADEMIC_YEAR VARCHAR2(30),FOREIGN KEY (ACADEMIC_YEAR) REFERENCES ACADEMIC_CALENDAR(ACADEMIC_YEAR));

ALTER TABLE EMPLOYEE ADD (NEXT_KIN VARCHAR2(30));

ALTER TABLE EMPLOYEE ADD (KIN_CONTACT NUMBER);

ALTER TABLE STUDENT_AUTHOURITY ADD (FATHER VARCHAR2(100),MOTHER VARCHAR2(100));

ALTER TABLE STUDENT_ACADEMIC MODIFY (PREV_SCHOOL VARCHAR2(100));

CREATE TABLE TEACHER_SUBJECT(EMP_ID VARCHAR2(30),FOREIGN KEY (EMP_ID) REFERENCES EMPLOYEE(EMP_ID),SUB_CODE NUMBER,FOREIGN KEY (SUB_CODE) REFERENCES WAEC_SUBJECT(SUB_CODE));

ALTER TABLE TEACHER_SUBJECT ADD (S_ID NUMBER,FOREIGN KEY (S_ID) REFERENCES SCHOOL(S_ID));

CREATE TABLE STUDENT_EVALUATION (
    S_ID NUMBER,
    FOREIGN KEY (S_ID) REFERENCES SCHOOL(S_ID),
    SUB_CODE NUMBER,
    FOREIGN KEY (SUB_CODE) REFERENCES WAEC_SUBJECT(SUB_CODE),
    STUD_ID VARCHAR2(30),
    FOREIGN KEY (STUD_ID) REFERENCES STUDENT(STUD_ID),
    EMP_ID VARCHAR2(30),
    FOREIGN KEY (EMP_ID) REFERENCES EMPLOYEE(EMP_ID),
    ACADEMIC_YEAR VARCHAR2(30),
    FOREIGN KEY (ACADEMIC_YEAR) REFERENCES ACADEMIC_CALENDAR(ACADEMIC_YEAR),
    CONST_ASS NUMERIC,
    EXAM NUMERIC,
    ENTRY_DT DATE,
    CLASS_CODE NUMBER,
    FOREIGN KEY (CLASS_CODE) REFERENCES SUB_CLASS(SUB_CODE));

ALTER TABLE EMPLOYEE ADD (USERNAME VARCHAR2(30),FOREIGN KEY (USERNAME) REFERENCES SCHOOL_USERS(USERNAME));

CREATE TABLE TERM (ACADEMIC_YEAR VARCHAR2(30),
    FOREIGN KEY (ACADEMIC_YEAR) REFERENCES ACADEMIC_CALENDAR(ACADEMIC_YEAR),TERM VARCHAR2(50),FOREIGN KEY (TERM) REFERENCES TERM_CALENDAR(TERM),DESCRIPTION VARCHAR2(100),DT VARCHAR2(20));


ALTER TABLE TEACHER_SUBJECT ADD (S_CODE NUMBER , FOREIGN KEY (S_CODE) REFERENCES SUB_CLASS(SUB_CODE));

 CREATE TABLE "PROG_CLASS" 
   (	"PROG_ID" NUMBER, 
	"CLASS" VARCHAR2(30 BYTE), 
	"SUB_CODE" NUMBER, 
	"S_ID" NUMBER, 
	 FOREIGN KEY ("PROG_ID")
	  REFERENCES "PROGRAMME" ("PROG_ID") , 
	 FOREIGN KEY ("SUB_CODE")
	  REFERENCES "SUB_CLASS" ("SUB_CODE") , 
	 FOREIGN KEY ("S_ID")
	  REFERENCES "SCHOOL" ("S_ID") 
   ) ;

ALTER TABLE STUDENT_EVALUATION ADD  (TERM VARCHAR2(50),FOREIGN KEY (TERM) REFERENCES TERM_CALENDAR(TERM));

ALTER TABLE STUDENT_EVALUATION MODIFY (CONST_ASS FLOAT, EXAM FLOAT);

CREATE TABLE GRADE (G_CODE NUMBER,GRADE VARCHAR2(10),S_ID NUMBER,FOREIGN KEY (S_ID) REFERENCES SCHOOL(S_ID));

ALTER TABLE GRADE MODIFY (G_CODE PRIMARY KEY);

CREATE TABLE GRADE_SETTING(G_CODE NUMBER,FOREIGN KEY (G_CODE) REFERENCES GRADE(G_CODE),START_GRADE_RANGE FLOAT,END_GRADE_RANGE FLOAT,S_ID NUMBER,FOREIGN KEY (S_ID) REFERENCES SCHOOL(S_ID));

ALTER TABLE STUDENT_PERSONAL ADD RELIGION VARCHAR2(20);

ALTER TABLE STUDENT_AUTHOURITY ADD (FATHER_OCCUP VARCHAR2(30),MOTHER_OCCUP VARCHAR2(30));

ALTER TABLE STUDENT_EVALUATION ADD MARK_STATUS VARCHAR2(10);

CREATE TABLE GPA (G_CODE NUMBER,FOREIGN KEY (G_CODE) REFERENCES GRADE(G_CODE),GPA FLOAT);

CREATE TABLE SUBJECT_CREDIT_HRS(S_ID NUMBER,
    FOREIGN KEY (S_ID) REFERENCES SCHOOL(S_ID),
    SUB_CODE NUMBER,
    FOREIGN KEY (SUB_CODE) REFERENCES WAEC_SUBJECT(SUB_CODE),CREDIT_HRS NUMBER);
    
ALTER TABLE STUDENT_AUTHOURITY MODIFY (FIRSTNAME VARCHAR2(100),MIDDLENAME VARCHAR2(100),LASTNAME VARCHAR2(100),OCCUPATION VARCHAR2(100),MOTHER_OCCUP VARCHAR2(100),FATHER_OCCUP VARCHAR2(100));

CREATE TABLE CLASS_TEACHER (S_ID NUMBER,
    FOREIGN KEY (S_ID) REFERENCES SCHOOL(S_ID),EMP_ID VARCHAR2(30),
    FOREIGN KEY (EMP_ID) REFERENCES EMPLOYEE(EMP_ID),SUB_CODE NUMBER,FOREIGN KEY (SUB_CODE) REFERENCES SUB_CLASS(SUB_CODE));
    
ALTER TABLE EMPLOYEE ADD NAME VARCHAR2(100);

CREATE TABLE STUDENT_CUMULATIVE (S_ID NUMBER,
    FOREIGN KEY (S_ID) REFERENCES SCHOOL(S_ID),SUB_CODE NUMBER,FOREIGN KEY (SUB_CODE) REFERENCES SUB_CLASS(SUB_CODE),G_CODE NUMBER,FOREIGN KEY (G_CODE) REFERENCES GRADE(G_CODE),SUBJ_CODE NUMBER,FOREIGN KEY (SUBJ_CODE) REFERENCES WAEC_SUBJECT(SUB_CODE),ACADEMIC_YEAR VARCHAR2(30),
    FOREIGN KEY (ACADEMIC_YEAR) REFERENCES ACADEMIC_CALENDAR(ACADEMIC_YEAR),TERM VARCHAR2(50),FOREIGN KEY (TERM) REFERENCES TERM_CALENDAR(TERM),STUD_ID VARCHAR2(30),
    FOREIGN KEY (STUD_ID) REFERENCES STUDENT(STUD_ID),MARK FLOAT,GPA FLOAT,ENTRY_DT VARCHAR2(20),CREDIT_HRS NUMBER,TOTAL_GPA_CREDIT FLOAT);
    
ALTER TABLE SUBJECT ADD SUBJECT_CREDIT_HRS NUMBER;    

CREATE TABLE STUDENT_STANDINGS (S_ID NUMBER,
    FOREIGN KEY (S_ID) REFERENCES SCHOOL(S_ID),GPA FLOAT ,CLASS_CODE NUMBER,FOREIGN KEY (CLASS_CODE) REFERENCES SUB_CLASS(SUB_CODE),ACADEMIC_YEAR VARCHAR2(30),
    FOREIGN KEY (ACADEMIC_YEAR) REFERENCES ACADEMIC_CALENDAR(ACADEMIC_YEAR),TERM VARCHAR2(50),FOREIGN KEY (TERM) REFERENCES TERM_CALENDAR(TERM),STUD_ID VARCHAR2(30),
    FOREIGN KEY (STUD_ID) REFERENCES STUDENT(STUD_ID));
    
ALTER TABLE STUDENT_STANDINGS ADD STATUS VARCHAR2(30);

CREATE TABLE DELETE_MARK_LOG(username varchar2(50), foreign key (username) references school_users(username),CLASS_CODE NUMBER ,FOREIGN KEY (CLASS_CODE) REFERENCES SUB_CLASS(SUB_CODE),SUB_CODE NUMBER ,FOREIGN KEY (SUB_CODE) REFERENCES WAEC_SUBJECT(SUB_CODE),ENTRY_DT DATE,TERM VARCHAR2(50),FOREIGN KEY (TERM) REFERENCES TERM_CALENDAR(TERM));

ALTER TABLE STUDENT ADD STDNUMBER VARCHAR2 (30);

ALTER TABLE SCHOOL ADD RIGHTS VARCHAR2(50);
ALTER TABLE REGION MODIFY REG_CODE DEFAULT your_sequence.NEXTVAL ;
ALTER TABLE DISTRICT MODIFY DIS_CODE DEFAULT your_sequence.NEXTVAL;
ALTER TABLE SCHOOL MODIFY S_ID DEFAULT your_sequence.NEXTVAL;
ALTER TABLE SUB_CLASS MODIFY SUB_CODE DEFAULT your_sequence.NEXTVAL ;
ALTER TABLE GEN_ID MODIFY ID DEFAULT your_sequence.NEXTVAL ;
ALTER TABLE DEPARTMENT MODIFY DEPT_ID DEFAULT your_sequence.NEXTVAL;
ALTER TABLE PROGRAMME MODIFY PROG_ID DEFAULT your_sequence.NEXTVAL ;
ALTER TABLE SUB_INCRE MODIFY CODE DEFAULT your_sequence.NEXTVAL ;
ALTER TABLE GRADE  MODIFY G_CODE DEFAULT your_sequence.NEXTVAL ;
ALTER TABLE SGEN_ID MODIFY ID DEFAULT your_sequence.NEXTVAL ;

 CREATE TABLE "PROG_SUBJECT" 
   (	"PROG_ID" NUMBER, 
	"SUB_CODE" NUMBER, 
	"PROG_SUB_TYPE" VARCHAR2(10 BYTE), 
	 FOREIGN KEY ("PROG_ID")
	  REFERENCES "PROGRAMME" ("PROG_ID") ENABLE, 
	 FOREIGN KEY ("SUB_CODE")
	  REFERENCES "WAEC_SUBJECT" ("SUB_CODE") ENABLE
   );
   
   CREATE TABLE FEE_PARAMETER (FEE_ID NUMBER DEFAULT your_sequence.NEXTVAL PRIMARY KEY,COST NUMBER, FEE_TYPE VARCHAR2(30), DESCRIPTION VARCHAR2(100));
   
  
    
    ALTER TABLE STUDENT_AUTHOURITY ADD PHOTO BLOB;
   
  
    
   ALTER TABLE FEE_PARAMETER ADD (S_ID NUMBER , FOREIGN KEY ("S_ID")
	  REFERENCES "SCHOOL" ("S_ID"));
      
   ALTER TABLE PROGRAMME ADD (S_ID NUMBER, FOREIGN KEY ("S_ID")
	  REFERENCES "SCHOOL" ("S_ID")) ;
      
    CREATE TABLE STUDENT_TRANSACT_HISTORY (STUD_ID VARCHAR2(30),
    FOREIGN KEY (STUD_ID) REFERENCES STUDENT(STUD_ID),DESCRIPTION VARCHAR2(100),ENTRY_DT DATE,FEE_ID NUMBER, FOREIGN KEY (FEE_ID) REFERENCES FEE_PARAMETER(FEE_ID),AMOUNT NUMBER,ACADEMIC_YEAR VARCHAR2(30),
    FOREIGN KEY (ACADEMIC_YEAR) REFERENCES ACADEMIC_CALENDAR(ACADEMIC_YEAR),TERM VARCHAR2(50),FOREIGN KEY (TERM) REFERENCES TERM_CALENDAR(TERM),EMP_ID VARCHAR2(30),
    FOREIGN KEY (EMP_ID) REFERENCES EMPLOYEE(EMP_ID));
    
    ALTER TABLE STUDENT_TRANSACT_HISTORY ADD RECEIPT BLOB;
    
    ALTER TABLE STUDENT_TRANSACT_HISTORY DROP COLUMN EMP_ID;
    
    ALTER TABLE STUDENT_TRANSACT_HISTORY ADD (USERNAME VARCHAR2(30 BYTE),FOREIGN KEY (USERNAME) REFERENCES SCHOOL_USERS(USERNAME));
    
    CREATE TABLE CALENDAR (ACADEMIC_YEAR VARCHAR2(30),
    FOREIGN KEY (ACADEMIC_YEAR) REFERENCES ACADEMIC_CALENDAR(ACADEMIC_YEAR),TERM VARCHAR2(50),FOREIGN KEY (TERM) REFERENCES TERM_CALENDAR(TERM),ACT VARCHAR2(100),START_DT VARCHAR2(15),END_DT VARCHAR2(15));
    
    CREATE TABLE STUDENT_BILLING (INVOICE_NO VARCHAR2(50) PRIMARY KEY ,ACADEMIC_YEAR VARCHAR2(30),TERM VARCHAR2(50),FOREIGN KEY (TERM) REFERENCES TERM_CALENDAR(TERM),CLASS_CODE NUMBER, FOREIGN KEY (CLASS_CODE) REFERENCES SUB_CLASS(SUB_CODE),DESCRIPTION VARCHAR2(100),AMOUNT NUMBER,BALANCE NUMBER,BILLING_DT DATE
    ,FOREIGN KEY (ACADEMIC_YEAR) REFERENCES ACADEMIC_CALENDAR(ACADEMIC_YEAR),USERNAME VARCHAR2(30 BYTE),FOREIGN KEY (USERNAME) REFERENCES SCHOOL_USERS(USERNAME),STUD_ID VARCHAR2(30),
    FOREIGN KEY (STUD_ID) REFERENCES STUDENT(STUD_ID));
    
    DROP TABLE STUDENT_TRANSACT_HISTORY;
    
    CREATE TABLE STUDENT_TRANSACT_HISTORY (INVOICE_NO VARCHAR2(50),FOREIGN KEY (INVOICE_NO) REFERENCES STUDENT_BILLING(INVOICE_NO),REF_NO VARCHAR2(50), PROOF BLOB,AMT NUMBER,POSTED_DT DATE,POSTEE VARCHAR2(30),FOREIGN KEY (POSTEE) REFERENCES SCHOOL_USERS(USERNAME));
   
    ALTER TABLE STUDENT_CONTACT ADD CONTACT_NAME VARCHAR2(100);
      ALTER TABLE SCHOOL_USERS MODIFY STATUS VARCHAR2(50);
   GRANT UNLIMITED TABLESPACE TO kotu;
  
COMMIT;