drop table performance;
drop table attend;
drop table assign;
drop table classes;
drop table subjects;
drop table guardians;
drop table students;
drop table employees;
drop table people;
drop table residence;

create table residence(
RID int(8) NOT NULL AUTO_INCREMENT,
RC varchar(15),
AD varchar(100),
PRIMARY KEY (RID)
);

create table people(
PID int(8) NOT NULL AUTO_INCREMENT,
RID int(8),
FN varchar(20) NOT NULL,
GN varchar(20) NOT NULL,
PC varchar(15),
PC2 varchar(15),
PRIMARY KEY(PID),
CONSTRAINT pk_RID_people FOREIGN KEY (RID) REFERENCES residence(RID)
ON DELETE SET NULL ON UPDATE CASCADE
);

create table students(
PID int(8) NOT NULL,
YR date,
Active varchar(1) NOT NULL,
CM varchar(50),
PRIMARY KEY (PID),
Constraint pk_PID_student FOREIGN KEY (PID) REFERENCES people(PID)
ON DELETE CASCADE ON UPDATE CASCADE
);
create table guardians(
PID int(8) NOT NULL,
CM varchar(50),
PRIMARY KEY(PID),
Constraint pk_PID_guardians FOREIGN KEY (PID) REFERENCES people(PID)
ON DELETE CASCADE ON UPDATE CASCADE

);
create table employees(
PID int(8) NOT NULL,
JOB varchar(10) NOT NULL,
SR decimal(6,2),
SM decimal(7,2),
Active varchar(1) NOT NULL,
PRIMARY KEY (PID),
Constraint pk_PID_emp FOREIGN KEY (PID) REFERENCES people(PID)
ON DELETE CASCADE ON UPDATE CASCADE
);
create table subjects(
SID VARCHAR(6) NOT NULL,
SUB VARCHAR(30) NOT NULL,
PRIMARY KEY(SID)
);
create table classes(
CID int(8) NOT NULL AUTO_INCREMENT,
SID VARCHAR(6) NOT NULL,
PID int(8),
TIME time,
HR decimal(3,1),
CL varchar(2),
DAY varchar(3),
Active varchar(1),
PRIMARY KEY (CID),
Constraint pk_PID_class FOREIGN KEY (PID) REFERENCES people(PID)
ON DELETE SET NULL ON UPDATE CASCADE,
Constraint pk_SID_class FOREIGN KEY (SID) REFERENCES subjects(SID)
ON DELETE CASCADE ON UPDATE CASCADE
);
create table assign(
PID int(8) NOT NULL,
CID int(9) NOT NULL,
Trans varchar(1),
PRIMARY KEY (PID),
Constraint pk_PID_assign FOREIGN KEY (PID) REFERENCES people(PID)
ON DELETE CASCADE ON UPDATE CASCADE,
Constraint pk_CID_assign FOREIGN KEY (CID) REFERENCES classes(CID)
ON DELETE CASCADE ON UPDATE CASCADE
);
create table attend(
PID int(8) NOT NULL,
CID int(9) NOT NULL,
YR date,
ATT varchar(1),
RS varchar(50),
Constraint pk_PID_attend FOREIGN KEY (PID) REFERENCES people(PID)
ON DELETE CASCADE ON UPDATE CASCADE,
Constraint pk_CID_attend FOREIGN KEY (CID) REFERENCES assign(CID)
ON DELETE CASCADE ON UPDATE CASCADE
);
create table performance(
PID int(8) NOT NULL,
DT date,
SID VARCHAR(6),
SCH char(20),
MARK decimal(4,1),
CM char(50),
PRIMARY KEY(PID),
Constraint pk_PID_perf FOREIGN KEY (PID) REFERENCES people(PID)
ON DELETE CASCADE ON UPDATE CASCADE,
Constraint pk_CID_perf FOREIGN KEY (SID) REFERENCES subjects(SID)
ON DELETE CASCADE ON UPDATE CASCADE
);
###########################################
################TEST VALUES################
###########################################

INSERT INTO residence (AD) VALUE ('129, Jalan Kinrara, Taman Kinrara, Jalan Puchong, 58200 KL');
INSERT INTO residence (AD) VALUE ('99, 2/6 Jalan Tan Yew Lai, Taman OUG');
INSERT INTO residence (AD) VALUE ('78, 10 Jalan Abu Bakar, Taman Desa');
INSERT INTO residence (AD) VALUE ('16, varcharlie Avenue, Rosevalley Hills');
INSERT INTO residence (AD) VALUE ('Kong Chun Wai\'s House');
INSERT INTO residence (AD) VALUE ('Palm Terrace');

INSERT INTO people (FN,GN,PC,PC2,RID) VALUE ('Chang','Alan, Jing Jie','014-6289313','012-3415232',1);
INSERT INTO people (FN,GN,RID)VALUE ('Chang','Alvin, Wen Jun',1);
INSERT INTO people (FN,GN,RID)VALUE ('Mohmand','Abdul Ali bin',3);
INSERT INTO people (FN,GN,RID)VALUE ('Sasilan','Sathaesan A/L',3);
INSERT INTO people (FN,GN,RID)VALUE ('Cena','John',4);
INSERT INTO people (FN,GN,RID)VALUE ('Go','Joachim, Yap Chim',2);
INSERT INTO people (FN,GN,RID)VALUE ('Kong','Chun Wai',5);
INSERT INTO people (FN,GN,RID)VALUE ('Murakami','Suzuki',4);
INSERT INTO people (FN,GN,RID)VALUE ('Rossi','Valentino',4);
INSERT INTO people (FN,GN,RID)VALUE ('Chan','Yuin Ching',6);
INSERT INTO people (FN,GN,RID)VALUE ('Chan','Shao Hong',6);
INSERT INTO people (FN,GN)VALUE ('Teoh','Poh Lin');
INSERT INTO people (FN,GN)VALUE ('Ng','Poh Kin');

INSERT INTO students(PID,YR,active)VALUE(2,'2002-10-28','N');
INSERT INTO students(PID,YR,active)VALUE(3,'2002-10-31','Y');
INSERT INTO students(PID,YR,active)VALUE(4,'2002-11-03','Y');
INSERT INTO students(PID,YR,active)VALUE(5,'2002-11-03','Y');
INSERT INTO students(PID,YR,active)VALUE(6,'2002-11-06','N');
INSERT INTO students(PID,YR,active)VALUE(7,'2002-11-15','N');
INSERT INTO students(PID,YR,active)VALUE(8,'2002-11-25','Y');
INSERT INTO students(PID,YR,active)VALUE(11,'2002-11-30','Y');

INSERT INTO employees(PID,Job,active)VALUE(10,'TUITOR','Y');
INSERT INTO employees(PID,Job,active)VALUE(12,'TUITOR','Y');
INSERT INTO employees(PID,Job,active)VALUE(13,'CLERK','Y');

INSERT INTO guardians(PID)VALUE(1);
INSERT INTO guardians(PID)VALUE(9);
INSERT INTO guardians(PID)VALUE(10);

INSERT INTO subjects VALUE ('ENGS01','ENGLISH STANDARD 1');
INSERT INTO subjects VALUE ('ENGS02','ENGLISH STANDARD 2');
INSERT INTO subjects VALUE ('ENGS03','ENGLISH STANDARD 3');
INSERT INTO subjects VALUE ('ENGS04','ENGLISH STANDARD 4');
INSERT INTO subjects VALUE ('ENGS05','ENGLISH STANDARD 5');
INSERT INTO subjects VALUE ('ENGS06','ENGLISH STANDARD 6');
INSERT INTO subjects VALUE ('ENGF01','ENGLISH FORM 1');
INSERT INTO subjects VALUE ('ENGF02','ENGLISH FORM 2');
INSERT INTO subjects VALUE ('ENGF03','ENGLISH FORM 3');
INSERT INTO subjects VALUE ('ENGF04','ENGLISH FORM 4');
INSERT INTO subjects VALUE ('ENGF05','ENGLISH FORM 5');
INSERT INTO subjects VALUE ('ENGF06','ENGLISH FORM 6');

INSERT INTO subjects VALUE ('MALS01','BAHASA MALAYSIA STANDARD 1');
INSERT INTO subjects VALUE ('MALS02','BAHASA MALAYSIA STANDARD 2');
INSERT INTO subjects VALUE ('MALS03','BAHASA MALAYSIA STANDARD 3');
INSERT INTO subjects VALUE ('MALS04','BAHASA MALAYSIA STANDARD 4');
INSERT INTO subjects VALUE ('MALS05','BAHASA MALAYSIA STANDARD 5');
INSERT INTO subjects VALUE ('MALS06','BAHASA MALAYSIA STANDARD 6');
INSERT INTO subjects VALUE ('MALF01','BAHASA MALAYSIA FORM 1');
INSERT INTO subjects VALUE ('MALF02','BAHASA MALAYSIA FORM 2');
INSERT INTO subjects VALUE ('MALF03','BAHASA MALAYSIA FORM 3');
INSERT INTO subjects VALUE ('MALF04','BAHASA MALAYSIA FORM 4');
INSERT INTO subjects VALUE ('MALF05','BAHASA MALAYSIA FORM 5');
INSERT INTO subjects VALUE ('MALF06','BAHASA MALAYSIA FORM 6');

INSERT INTO subjects VALUE ('MATS01','MATHEMATICS STANDARD 1');
INSERT INTO subjects VALUE ('MATS02','MATHEMATICS STANDARD 2');
INSERT INTO subjects VALUE ('MATS03','MATHEMATICS STANDARD 3');
INSERT INTO subjects VALUE ('MATS04','MATHEMATICS STANDARD 4');
INSERT INTO subjects VALUE ('MATS05','MATHEMATICS STANDARD 5');
INSERT INTO subjects VALUE ('MATS06','MATHEMATICS STANDARD 6');
INSERT INTO subjects VALUE ('MATF01','MATHEMATICS FORM 1');
INSERT INTO subjects VALUE ('MATF02','MATHEMATICS FORM 2');
INSERT INTO subjects VALUE ('MATF03','MATHEMATICS FORM 3');
INSERT INTO subjects VALUE ('MATF04','MATHEMATICS FORM 4');
INSERT INTO subjects VALUE ('MATF05','MATHEMATICS FORM 5');
INSERT INTO subjects VALUE ('MATF06','MATHEMATICS FORM 6');

INSERT INTO subjects VALUE ('SCIS01','SCIENCE STANDARD 1');
INSERT INTO subjects VALUE ('SCIS02','SCIENCE STANDARD 2');
INSERT INTO subjects VALUE ('SCIS03','SCIENCE STANDARD 3');
INSERT INTO subjects VALUE ('SCIS04','SCIENCE STANDARD 4');
INSERT INTO subjects VALUE ('SCIS05','SCIENCE STANDARD 5');
INSERT INTO subjects VALUE ('SCIS06','SCIENCE STANDARD 6');
INSERT INTO subjects VALUE ('SCIF01','SCIENCE FORM 1');
INSERT INTO subjects VALUE ('SCIF02','SCIENCE FORM 2');
INSERT INTO subjects VALUE ('SCIF03','SCIENCE FORM 3');
INSERT INTO subjects VALUE ('SCIF04','SCIENCE FORM 4');
INSERT INTO subjects VALUE ('SCIF05','SCIENCE FORM 5');
INSERT INTO subjects VALUE ('SCIF06','SCIENCE FORM 6');

INSERT INTO subjects VALUE ('SEJS01','SEJARAH STANDARD 1');
INSERT INTO subjects VALUE ('SEJS02','SEJARAH STANDARD 2');
INSERT INTO subjects VALUE ('SEJS03','SEJARAH STANDARD 3');
INSERT INTO subjects VALUE ('SEJS04','SEJARAH STANDARD 4');
INSERT INTO subjects VALUE ('SEJS05','SEJARAH STANDARD 5');
INSERT INTO subjects VALUE ('SEJS06','SEJARAH STANDARD 6');
INSERT INTO subjects VALUE ('SEJF01','SEJARAH FORM 1');
INSERT INTO subjects VALUE ('SEJF02','SEJARAH FORM 2');
INSERT INTO subjects VALUE ('SEJF03','SEJARAH FORM 3');
INSERT INTO subjects VALUE ('SEJF04','SEJARAH FORM 4');
INSERT INTO subjects VALUE ('SEJF05','SEJARAH FORM 5');
INSERT INTO subjects VALUE ('SEJF06','SEJARAH FORM 6');

INSERT INTO subjects VALUE ('GEOS01','GEOGRAPHY STANDARD 1');
INSERT INTO subjects VALUE ('GEOS02','GEOGRAPHY STANDARD 2');
INSERT INTO subjects VALUE ('GEOS03','GEOGRAPHY STANDARD 3');
INSERT INTO subjects VALUE ('GEOS04','GEOGRAPHY STANDARD 4');
INSERT INTO subjects VALUE ('GEOS05','GEOGRAPHY STANDARD 5');
INSERT INTO subjects VALUE ('GEOS06','GEOGRAPHY STANDARD 6');


INSERT INTO subjects VALUE ('ADDF04','ADDITIONAL MATHS FORM 4');
INSERT INTO subjects VALUE ('ADDF05','ADDITIONAL MATHS FORM 5');
INSERT INTO subjects VALUE ('ADDF06','ADDITIONAL MATHS FORM 6');


INSERT INTO subjects VALUE ('PHYF04','PHYSICS FORM 4');
INSERT INTO subjects VALUE ('PHYF05','PHYSICS FORM 5');
INSERT INTO subjects VALUE ('PHYF06','PHYSICS FORM 6');


INSERT INTO subjects VALUE ('CHEF04','CHEMICAL FORM 4');
INSERT INTO subjects VALUE ('CHEF05','CHEMICAL FORM 5');
INSERT INTO subjects VALUE ('CHEF06','CHEMICAL FORM 6');


INSERT INTO subjects VALUE ('BIOF04','BIOLOGY FORM 4');
INSERT INTO subjects VALUE ('BIOF05','BIOLOGY FORM 5');
INSERT INTO subjects VALUE ('BIOF06','BIOLOGY FORM 6');

INSERT INTO subjects VALUE ('ACCF04','ACCOUNTING FORM 4');
INSERT INTO subjects VALUE ('ACCF05','ACCOUNTING FORM 5');
INSERT INTO subjects VALUE ('ACCF06','ACCOUNTING FORM 6');

CREATE FUNCTION test()
  RETURNS INT
   DETERMINISTIC
    BEGIN;
     SET avg = SELECT FN,LN FROM STUDENTS;
     RETURN avg;
END