CREATE TABLE Classes (
    ClassID INT(10) NOT NULL AUTO_INCREMENT,
    ClassName VARCHAR (20) NOT NULL,
    SchoolID INT(10) NULL,
    PRIMARY KEY(ClassID)
);


INSERT INTO Classes VALUES ('1', '6TH', '1');
INSERT INTO Classes VALUES ('2', '7TH', '1');
INSERT INTO Classes VALUES ('3', '8TH', '1');


CREATE TABLE ClassProperties (
    ClassPropertiesID INT (10) NOT NULL AUTO_INCREMENT,
    LecturerDescription VARCHAR (20) NULL,
    SubjectID INT (10) NULL,
    ClassID INT (10) NULL,
    TeacherID INT (10) NULL,
    PRIMARY KEY(ClassPropertiesID)
);

INSERT INTO ClassProperties VALUES ('1', 'Prose', '2', '3', '1');
INSERT INTO ClassProperties VALUES ('2', 'Prose', '2', '2', '6');
INSERT INTO ClassProperties VALUES ('3', 'Prose', '2', '2', '6');
INSERT INTO ClassProperties VALUES ('4', 'Botany', '1', '1', '8');
INSERT INTO ClassProperties VALUES ('5', 'Zoology', '1', '2', '8');
INSERT INTO ClassProperties VALUES ('6', 'Zoology', '1', '3', '8');
INSERT INTO ClassProperties VALUES ('7', 'Factors', '3', '1', '4');
INSERT INTO ClassProperties VALUES ('8', 'Geomectry', '3', '2', '4');
INSERT INTO ClassProperties VALUES ('9', 'Cubes', '3', '3', '4');
INSERT INTO ClassProperties VALUES ('10', 'Physics', '4', '3', '3');
INSERT INTO ClassProperties VALUES ('11', 'Chemistry', '4', '2', '3');
INSERT INTO ClassProperties VALUES ('12', 'Physics', '4', '3', '3');
INSERT INTO ClassProperties VALUES ('13', 'Physics', '4', '1', '3');
INSERT INTO ClassProperties VALUES ('14', 'Geography', '5', '1', '7');
INSERT INTO ClassProperties VALUES ('15', 'Geography', '5', '2', '7');
INSERT INTO ClassProperties VALUES ('16', 'Geography', '5', '3', '7');
INSERT INTO ClassProperties VALUES ('17', 'History', '5', '3', '7');
INSERT INTO ClassProperties VALUES ('18', 'History', '5', '2', '7');
INSERT INTO ClassProperties VALUES ('19', 'History', '5', '1', '7');
INSERT INTO ClassProperties VALUES ('20', 'fundatmentals', '6', '3', '5');
INSERT INTO ClassProperties VALUES ('21', 'memory', '6', '2', '5');
INSERT INTO ClassProperties VALUES ('22', 'BASIC', '6', '1', '5');


CREATE TABLE Exam (
    ExamID INT (10) NOT NULL AUTO_INCREMENT,
    ExamName VARCHAR (20) NULL,
    MaximumMarks INT (10) NULL,
    PassingMarks INT (10) NULL,
    ParentExamID INT (10) NULL,
    ParentMarksCarryForward INT (10) NULL,
    PRIMARY KEY(ExamID)
);


INSERT INTO Exam VALUES ('1', 'final year', '600', '250', '0', '0');
INSERT INTO Exam VALUES ('2', 'half yearly', '600', '250', '1', '25');
INSERT INTO Exam VALUES ('3', 'session1', '600', '250', '2', '20');
INSERT INTO Exam VALUES ('4', 'session2', '600', '250', '2', '20');

CREATE TABLE MarksRecord (
    Marks INT (10) NULL,
    StudentID INT (10) NULL,
    SubjectID INT (10) NULL,
    ExamID INT (10) NULL,
);

INSERT INTO MarksRecord VALUES ('98', '1', '1', '1');
INSERT INTO MarksRecord VALUES ('87', '1', '2', '1');
INSERT INTO MarksRecord VALUES ('99', '1', '3', '1');
INSERT INTO MarksRecord VALUES ('78', '1', '4', '1');
INSERT INTO MarksRecord VALUES ('52', '1', '5', '1');
INSERT INTO MarksRecord VALUES ('77', '1', '6', '1');
INSERT INTO MarksRecord VALUES ('88', '2', '1', '1');
INSERT INTO MarksRecord VALUES ('91', '2', '2', '1');
INSERT INTO MarksRecord VALUES ('80', '2', '3', '1');
INSERT INTO MarksRecord VALUES ('89', '2', '4', '1');
INSERT INTO MarksRecord VALUES ('70', '2', '5', '1');
INSERT INTO MarksRecord VALUES ('75', '2', '6', '1');
INSERT INTO MarksRecord VALUES ('79', '3', '1', '1');
INSERT INTO MarksRecord VALUES ('71', '3', '2', '1');
INSERT INTO MarksRecord VALUES ('79', '3', '3', '1');
INSERT INTO MarksRecord VALUES ('78', '3', '4', '1');
INSERT INTO MarksRecord VALUES ('73', '3', '5', '1');
INSERT INTO MarksRecord VALUES ('89', '3', '6', '1');
INSERT INTO MarksRecord VALUES ('99', '4', '1', '1');
INSERT INTO MarksRecord VALUES ('81', '4', '2', '1');
INSERT INTO MarksRecord VALUES ('59', '4', '3', '1');
INSERT INTO MarksRecord VALUES ('89', '4', '4', '1');
INSERT INTO MarksRecord VALUES ('71', '4', '5', '1');
INSERT INTO MarksRecord VALUES ('79', '4', '6', '1');
INSERT INTO MarksRecord VALUES ('99', '5', '1', '1');
INSERT INTO MarksRecord VALUES ('81', '5', '2', '1');
INSERT INTO MarksRecord VALUES ('59', '5', '3', '1');
INSERT INTO MarksRecord VALUES ('89', '5', '4', '1');
INSERT INTO MarksRecord VALUES ('71', '5', '5', '1');
INSERT INTO MarksRecord VALUES ('79', '5', '6', '1');
INSERT INTO MarksRecord VALUES ('98', '6', '1', '1');
INSERT INTO MarksRecord VALUES ('87', '6', '2', '1');
INSERT INTO MarksRecord VALUES ('89', '6', '3', '1');
INSERT INTO MarksRecord VALUES ('78', '6', '4', '1');
INSERT INTO MarksRecord VALUES ('72', '6', '5', '1');
INSERT INTO MarksRecord VALUES ('67', '6', '6', '1');
INSERT INTO MarksRecord VALUES ('79', '7', '1', '1');
INSERT INTO MarksRecord VALUES ('71', '7', '2', '1');
INSERT INTO MarksRecord VALUES ('69', '7', '3', '1');
INSERT INTO MarksRecord VALUES ('78', '7', '4', '1');
INSERT INTO MarksRecord VALUES ('73', '7', '5', '1');
INSERT INTO MarksRecord VALUES ('89', '7', '6', '1');
INSERT INTO MarksRecord VALUES ('78', '8', '1', '1');
INSERT INTO MarksRecord VALUES ('80', '8', '2', '1');
INSERT INTO MarksRecord VALUES ('88', '8', '3', '1');
INSERT INTO MarksRecord VALUES ('89', '8', '4', '1');
INSERT INTO MarksRecord VALUES ('70', '8', '5', '1');
INSERT INTO MarksRecord VALUES ('75', '8', '6', '1');
INSERT INTO MarksRecord VALUES ('99', '9', '1', '1');
INSERT INTO MarksRecord VALUES ('81', '9', '2', '1');
INSERT INTO MarksRecord VALUES ('59', '9', '3', '1');
INSERT INTO MarksRecord VALUES ('89', '9', '4', '1');
INSERT INTO MarksRecord VALUES ('71', '9', '5', '1');
INSERT INTO MarksRecord VALUES ('79', '9', '6', '1');
INSERT INTO MarksRecord VALUES ('99', '10', '1', '1');
INSERT INTO MarksRecord VALUES ('81', '10', '2', '1');
INSERT INTO MarksRecord VALUES ('59', '10', '3', '1');
INSERT INTO MarksRecord VALUES ('89', '10', '4', '1');
INSERT INTO MarksRecord VALUES ('71', '10', '5', '1');
INSERT INTO MarksRecord VALUES ('79', '10', '6', '1');


CREATE TABLE Post (
    PostID INT (10) NOT NULL,
    PostName VARCHAR (20) NOT NULL,
    PostRank VARCHAR (20) NULL,
    PRIMARY KEY(PostID)
);

INSERT INTO Post VALUES ('1', 'Principal', '1');
INSERT INTO Post VALUES ('2', 'Vice Principal', '2');
INSERT INTO Post VALUES ('3', 'Teacher', '3');

CREATE TABLE School (
    SchoolID INT (10) NOT NULL AUTO_INCREMENT,
    SchoolName VARCHAR (20) NULL,
    SchoolAddress VARCHAR (80) NULL,
    PhoneNumber VARCHAR (20) NULL,
    EMailAddress VARCHAR (80) NULL,
    PRIMARY KEY(SchoolID)
);

INSERT INTO School VALUES ('1', 'Arthur Morgan School', '1901,Hannah Branch RoadBurnsville,NC', '1-828-675-4262', 'info@arthurmorganschool.org');

CREATE TABLE Student (
    StudentID INT (10) NOT NULL AUTO_INCREMENT,
    StudentName VARCHAR (20) NULL,
    RollNumber INT (10) NULL,
    Gender CHAR (1) NULL,
    StudentAddress VARCHAR (80) NULL,
    PhoneNumber VARCHAR (20) NULL,
    ClassID INT (10) NULL,
    PRIMARY KEY(StudentID)
);


INSERT INTO Student VALUES ('1', 'Catherine Guzman', '1001', 'F', '1500 Warburton Avenue, Santa Clara, CA 95050', '(408)615-2250', '1');
INSERT INTO Student VALUES ('2', 'John K.', '1002', 'M', 'Oroville City Palace 1735 Montgomery Street Oroville, California 95965', '(408) 615 - 7297', '1');
INSERT INTO Student VALUES ('3', 'Cathe', '1003', 'F', 'Raananah Park, Highland Mills, NY 10930-0000', '(845) 928 - 6395', '1');
INSERT INTO Student VALUES ('4', 'John QQQ', '1004', 'M', '3000 N Sheridan Rd, Chicago, IL 60657-5553', '(773) 477 - 6034', '1');
INSERT INTO Student VALUES ('5', 'Woll B.', '1005', 'F', '11200 Arbor Creek Dr, Richmond, VA 23233-020', '(804) 934 - 9178', '1');
INSERT INTO Student VALUES ('6', 'Valine', '1006', 'F', '1419 Madison Pl, Pompton Lakes, NJ 07442-1517', '(973) 835 - 9107', '2');
INSERT INTO Student VALUES ('7', 'Liebig', '1007', 'F', '1590 N Airport Rd, Columbia City, IN 46725-8679', '(219) 248 - 8261', '2');
INSERT INTO Student VALUES ('8', 'Williams', '1008', 'M', '2700 Park Ave, Minneapolis, MN 55407-1017', '(612) 872 - 1014', '2');
INSERT INTO Student VALUES ('9', 'Tovera', '1009', 'M', '3048 Brighton 13th St, Brooklyn, NY 11235-5638', '(718) 646 - 3603', '2');
INSERT INTO Student VALUES ('10', 'WinkField', '1010', 'F', '19325 Royalton Rd, Strongsville, OH 44149-4946', '(440) 238 - 7297', '2');


CREATE TABLE Subject (
    SubjectID INT (10) NOT NULL AUTO_INCREMENT,
    SubjectName VARCHAR (20) NOT NULL,
    PRIMARY KEY(SubjectID)
);

INSERT INTO Subject VALUES ('1', 'Biology');
INSERT INTO Subject VALUES ('2', 'English');
INSERT INTO Subject VALUES ('3', 'Mathematics');
INSERT INTO Subject VALUES ('4', 'Science');
INSERT INTO Subject VALUES ('5', 'Social Studies');
INSERT INTO Subject VALUES ('6', 'Computers');


CREATE TABLE Teacher (
    EmployeeID INT (10) NOT NULL AUTO_INCREMENT,
    TeacherName VARCHAR (20) NULL,
    DateOfJoining DATE NULL,
    DateOfBirth DATE NULL,
    Salary INT (10) NULL,
    DepartName VARCHAR (20) NULL,
    PostID INT (10) NULL,
    SchoolID INT (10) NULL,
    PRIMARY KEY(EmployeeID)
);

INSERT INTO Teacher VALUES ('1', 'Mr. Agregado', '1996-04-16', '1965-04-09', '10000', 'English', '1', '1');
INSERT INTO Teacher VALUES ('2', 'Mr. Brumfield', '1997-06-30', '1966-11-26', '8520', 'Science', '2', '1');
INSERT INTO Teacher VALUES ('3', 'Ms. McKelvey', '1998-09-24', '1968-01-06', '6000', 'Science', '3', '1');
INSERT INTO Teacher VALUES ('4', 'Mr. Everett', '1998-10-24', '1968-01-16', '7200', 'Math', '3', '1');
INSERT INTO Teacher VALUES ('5', 'Mr. Verstrepen', '1998-08-28', '1969-05-17', '6000', 'Computer', '3', '1');
INSERT INTO Teacher VALUES ('6', 'Mr. Haight', '1998-09-14', '1968-02-21', '6001', 'English', '3', '1');
INSERT INTO Teacher VALUES ('7', 'Ms. Hartenfeld', '1998-09-14', '1970-01-05', '6002', 'Social Studies', '3', '1');
INSERT INTO Teacher VALUES ('8', 'Mr. Henry', '1998-10-24', '1969-03-22', '6065', 'Biology', '3', '1');