CREATE TABLE ACADEMIX."REGION" 
   ("REG_CODE" number  generated always as IDENTITY(start with 1 INCREMENT by  1) PRIMARY key, 
	"REGION" VARCHAR2(20 BYTE) ) ;
CREATE TABLE  ACADEMIX."DISTRICT" 
   (	"DIS_CODE" number  generated always as IDENTITY(start with 1 INCREMENT by  1),"REG_CODE" NUMBER, 
	"DISTRICT" VARCHAR2(100 BYTE), 
	 PRIMARY KEY ("DIS_CODE"), 
	 FOREIGN KEY ("REG_CODE")
	  REFERENCES  ACADEMIX."REGION" ("REG_CODE")
   );
  CREATE TABLE  ACADEMIX."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  generated always as IDENTITY(start with 1 INCREMENT by  1), 
	"PASSWORD" VARCHAR2(100 BYTE), 
	"USERNAME" VARCHAR2(20 BYTE), 
	 PRIMARY KEY ("S_ID"), 
	 FOREIGN KEY ("REG_CODE")
	  REFERENCES  ACADEMIX."REGION" ("REG_CODE"), 
	 FOREIGN KEY ("DIS_CODE")
	  REFERENCES  ACADEMIX."DISTRICT" ("DIS_CODE")) ;
      

  CREATE TABLE "ACADEMIX"."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 "ACADEMIX"."SCHOOL" ("S_ID"));
      
CREATE TABLE ACADEMIX.CLASS("S_ID" NUMBER, FOREIGN KEY ("S_ID")
	  REFERENCES "ACADEMIX"."SCHOOL" ("S_ID"),CLASS NUMBER PRIMARY KEY,TITLE varchar2(20));

ALTER TABLE CLASS ADD CLASS_TITLE varchar2(30);

CREATE TABLE ACADEMIX.SUB_CLASS(S_ID NUMBER, FOREIGN KEY ("S_ID")
	  REFERENCES "ACADEMIX"."SCHOOL" ("S_ID"), CLASS NUMBER,FOREIGN KEY ("CLASS")
	  REFERENCES "ACADEMIX"."CLASS" ("CLASS"),SUB_CODE number  generated always as IDENTITY(start with 1 INCREMENT by  1) PRIMARY KEY,SUB_TITLE varchar2(20));
      
CREATE TABLE ACADEMIX.DEPARTMENT (DEPT_ID NUMBER   generated always as IDENTITY(start with 1 INCREMENT by  1) PRIMARY KEY,DEPT VARCHAR2(30));

CREATE TABLE ACADEMIX.GEN_ID(ID NUMBER GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1 ));

ALTER TABLE ACADEMIX.GEN_ID ADD GEN NUMBER;

CREATE TABLE ACADEMIX.EMPLOYEE(S_ID NUMBER, FOREIGN KEY ("S_ID")
	  REFERENCES "ACADEMIX"."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 ACADEMIX.EMPLOYEE ADD STATUS VARCHAR2(10);      

ALTER TABLE ACADEMIX.EMPLOYEE ADD PASS_PHOTO BLOB;

ALTER TABLE ACADEMIX.EMPLOYEE ADD CREATE_DT DATE;

ALTER TABLE ACADEMIX.SUBJECT ADD (DEPT_ID NUMBER,FOREIGN KEY (DEPT_ID) REFERENCES DEPARTMENT(DEPT_ID));

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

ALTER TABLE ACADEMIX.EMPLOYEE DROP COLUMN DEPARTMENT;

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

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

CREATE TABLE ACADEMIX.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 ACADEMIX.STUDENT_PERSONAL(STUD_ID VARCHAR2(30),FOREIGN KEY (STUD_ID) REFERENCES ACADEMIX.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 ACADEMIX.STUDENT_CONTACT(STUD_ID VARCHAR2(30),FOREIGN KEY (STUD_ID) REFERENCES ACADEMIX.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 ACADEMIX.STUDENT_AUTHOURITY(STUD_ID VARCHAR2(30),FOREIGN KEY (STUD_ID) REFERENCES ACADEMIX.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 ACADEMIX.STUDENT_MEDICAL(STUD_ID VARCHAR2(30),FOREIGN KEY (STUD_ID) REFERENCES ACADEMIX.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 ACADEMIX.STUDENT_ACADEMIC(STUD_ID VARCHAR2(30),FOREIGN KEY (STUD_ID) REFERENCES ACADEMIX.STUDENT(STUD_ID),S_ID NUMBER,FOREIGN KEY (S_ID) REFERENCES SCHOOL(S_ID),CLASS NUMBER,FOREIGN KEY ("CLASS") REFERENCES "ACADEMIX"."CLASS" ("CLASS"),PREV_SCHOOL VARCHAR2(30));

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

CREATE TABLE ACADEMIX.SGEN_ID(ID NUMBER GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1 ));

ALTER TABLE ACADEMIX.SGEN_ID ADD GEN NUMBER;

ALTER TABLE ACADEMIX.STUDENT_PERSONAL ADD DOB VARCHAR2(10);

ALTER TABLE ACADEMIX.STUDENT_AUTHOURITY MODIFY EMAIL VARCHAR2(30);

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

CREATE TABLE ACADEMIX.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 "ACADEMIX"."SCHOOL" ("S_ID"));
     
INSERT INTO WAEC_SUBJECT (SUB_CODE,SUBJECT) VALUES (111,'ENGLISH LANGUAGE');
INSERT INTO WAEC_SUBJECT (SUB_CODE,SUBJECT) VALUES (112,'MATHEMATICS');
INSERT INTO WAEC_SUBJECT (SUB_CODE,SUBJECT) VALUES (113,'SCIENCE');
INSERT INTO WAEC_SUBJECT (SUB_CODE,SUBJECT) VALUES (114,'SOCIAL AND ENVIRONMENTAL STUDIES');
INSERT INTO WAEC_SUBJECT (SUB_CODE,SUBJECT) VALUES (115,'ARABIC');
INSERT INTO WAEC_SUBJECT (SUB_CODE,SUBJECT) VALUES (125,'FRENCH');
INSERT INTO WAEC_SUBJECT (SUB_CODE,SUBJECT) VALUES (116,'ISLAMIC STUDIES');
INSERT INTO WAEC_SUBJECT (SUB_CODE,SUBJECT) VALUES (117,'AGRICULTURAL SCIENCE');
INSERT INTO WAEC_SUBJECT (SUB_CODE,SUBJECT) VALUES (118,'PHYSICAL EDUCATION');
INSERT INTO WAEC_SUBJECT (SUB_CODE,SUBJECT) VALUES (119,'LITERATURE-IN-ENGLISH');
INSERT INTO WAEC_SUBJECT (SUB_CODE,SUBJECT) VALUES (120,'HOME ECONOMICS');
INSERT INTO WAEC_SUBJECT (SUB_CODE,SUBJECT) VALUES (121,'ART AND CRAFT');
INSERT INTO WAEC_SUBJECT (SUB_CODE,SUBJECT) VALUES (122,'TECHNICAL DRAWING');
INSERT INTO WAEC_SUBJECT (SUB_CODE,SUBJECT) VALUES (123,'METALWORK');
INSERT INTO WAEC_SUBJECT (SUB_CODE,SUBJECT) VALUES (124,'WOODWORK');

ALTER TABLE SUB_CLASS ADD CLASS_NAME VARCHAR2(30);

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

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

CREATE TABLE TUITION(S_ID NUMBER,FOREIGN KEY (S_ID) REFERENCES SCHOOL(S_ID),CLASS NUMBER,FOREIGN KEY ("CLASS")
	  REFERENCES "ACADEMIX"."CLASS" ("CLASS"),DESCRIPTION VARCHAR2(50),COST NUMBER);

CREATE TABLE STUDENT_FINANCE(STUD_ID VARCHAR2(30),FOREIGN KEY (STUD_ID) REFERENCES ACADEMIX.STUDENT(STUD_ID),S_ID NUMBER,FOREIGN KEY (S_ID) REFERENCES SCHOOL(S_ID),DESCRIPTION VARCHAR2(50),FEE NUMBER,POST_DT DATE,BALANCE NUMBER,CLASS NUMBER,FOREIGN KEY ("CLASS")
	  REFERENCES "ACADEMIX"."CLASS" ("CLASS"));      

ALTER TABLE STUDENT_FINANCE ADD(PROOF BLOB);

ALTER TABLE STUDENT_FINANCE ADD AMT NUMBER;

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);

INSERT INTO WAEC_SUBJECT (SUB_CODE,SUBJECT,EXAM) VALUES (126,'GENERAL MATHEMATICS','WASSCE');
INSERT INTO WAEC_SUBJECT (SUB_CODE,SUBJECT,EXAM) VALUES (127,'ENGLISH LANGUAGE','WASSCE');
INSERT INTO WAEC_SUBJECT (SUB_CODE,SUBJECT,EXAM) VALUES (128,'BIOLOGY','WASSCE');
INSERT INTO WAEC_SUBJECT (SUB_CODE,SUBJECT,EXAM) VALUES (129,'CHEMISTRY','WASSCE');
INSERT INTO WAEC_SUBJECT (SUB_CODE,SUBJECT,EXAM) VALUES (130,'PHYSICS','WASSCE');
INSERT INTO WAEC_SUBJECT (SUB_CODE,SUBJECT,EXAM) VALUES (131,'FURTHERMATHEMATICS','WASSCE');
INSERT INTO WAEC_SUBJECT (SUB_CODE,SUBJECT,EXAM) VALUES (132,'FRENCH','WASSCE');
INSERT INTO WAEC_SUBJECT (SUB_CODE,SUBJECT,EXAM) VALUES (133,'AGRICULTURAL SCIENCE','WASSCE');
INSERT INTO WAEC_SUBJECT (SUB_CODE,SUBJECT,EXAM) VALUES (134,'PHYSICAL EDUCATION','WASSCE');
INSERT INTO WAEC_SUBJECT (SUB_CODE,SUBJECT,EXAM) VALUES (135,'LITERATURE-IN-ENGLISH','WASSCE');
INSERT INTO WAEC_SUBJECT (SUB_CODE,SUBJECT,EXAM) VALUES (136,'HEALTH SCIENCE','WASSCE');
INSERT INTO WAEC_SUBJECT (SUB_CODE,SUBJECT,EXAM) VALUES (137,'SCIENCE','WASSCE');
INSERT INTO WAEC_SUBJECT (SUB_CODE,SUBJECT,EXAM) VALUES (138,'BUSINESS MANAGEMENT','WASSCE');
INSERT INTO WAEC_SUBJECT (SUB_CODE,SUBJECT,EXAM) VALUES (139,'COMMERCE','WASSCE');
INSERT INTO WAEC_SUBJECT (SUB_CODE,SUBJECT,EXAM) VALUES (140,'FINANCIAL ACCOUNTING','WASSCE');
INSERT INTO WAEC_SUBJECT (SUB_CODE,SUBJECT,EXAM) VALUES (141,'PRINCIPLES OF COST ACCOUNTING','WASSCE');
INSERT INTO WAEC_SUBJECT (SUB_CODE,SUBJECT,EXAM) VALUES (142,'CHRISTIAN RELIGIOUS STUDIES','WASSCE');
INSERT INTO WAEC_SUBJECT (SUB_CODE,SUBJECT,EXAM) VALUES (143,'ECONOMICS','WASSCE');
INSERT INTO WAEC_SUBJECT (SUB_CODE,SUBJECT,EXAM) VALUES (144,'GEOGRAPHY','WASSCE');
INSERT INTO WAEC_SUBJECT (SUB_CODE,SUBJECT,EXAM) VALUES (145,'HISTORY','WASSCE');
INSERT INTO WAEC_SUBJECT (SUB_CODE,SUBJECT,EXAM) VALUES (146,'ISLAMIC STUDIES','WASSCE');
INSERT INTO WAEC_SUBJECT (SUB_CODE,SUBJECT,EXAM) VALUES (147,'ARABIC','WASSCE');
INSERT INTO WAEC_SUBJECT (SUB_CODE,SUBJECT,EXAM) VALUES (148,'APPLIED ELECTRICITY','WASSCE');
INSERT INTO WAEC_SUBJECT (SUB_CODE,SUBJECT,EXAM) VALUES (149,'AUTO MECHANICS','WASSCE');
INSERT INTO WAEC_SUBJECT (SUB_CODE,SUBJECT,EXAM) VALUES (150,'METALWORK','WASSCE');
INSERT INTO WAEC_SUBJECT (SUB_CODE,SUBJECT,EXAM) VALUES (151,'TECHNICAL DRAWING','WASSCE');
INSERT INTO WAEC_SUBJECT (SUB_CODE,SUBJECT,EXAM) VALUES (157,'CLOTHING AND TEXTILES','WASSCE');
INSERT INTO WAEC_SUBJECT (SUB_CODE,SUBJECT,EXAM) VALUES (152,'FOODS AND NUTRITION','WASSCE');
INSERT INTO WAEC_SUBJECT (SUB_CODE,SUBJECT,EXAM) VALUES (153,'HOME MANAGEMENT','WASSCE');
INSERT INTO WAEC_SUBJECT (SUB_CODE,SUBJECT,EXAM) VALUES (154,'VISUAL ART','WASSCE');
INSERT INTO WAEC_SUBJECT (SUB_CODE,SUBJECT,EXAM) VALUES (155,'CIVIC EDUCATION','WASSCE');
INSERT INTO WAEC_SUBJECT (SUB_CODE,SUBJECT,EXAM) VALUES (156,'GOVERNMENT','WASSCE');

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_FINANCE ADD (SUB_CODE NUMBER,FOREIGN KEY (SUB_CODE) REFERENCES SUB_CLASS(SUB_CODE));

ALTER TABLE STUDENT MODIFY STATUS VARCHAR2(20);

CREATE TABLE STUDENT_ACTIVITIES (S_ID NUMBER, FOREIGN KEY ("S_ID")REFERENCES "SCHOOL" ("S_ID"),STUD_ID VARCHAR2(100), FOREIGN KEY (STUD_ID) REFERENCES STUDENT (STUD_ID), ACT VARCHAR2(250)); 

CREATE TABLE STUDENT_RESPON(S_ID NUMBER, FOREIGN KEY ("S_ID")REFERENCES "SCHOOL" ("S_ID"),STUD_ID VARCHAR2(100), FOREIGN KEY (STUD_ID) REFERENCES STUDENT (STUD_ID),RESPON VARCHAR2(250), CLASS NUMBER, FOREIGN KEY (CLASS) REFERENCES CLASS(CLASS));