Grading System Database Model and Design

Grading System Database Model and Design

This article will give you an idea and example on the different tables involved in the development of a grading system.

General Objectives

This study aims to improve the manual submission of grades to the Registrar’s office and as well as to allow the students to view their grades. The proposed Grading System will be secured and reliable in keeping the records. It will also minimize the workloads will be fast and convenient.

Specific Objectives

The main purpose of the study is to:

1. Provide a computerized record keeping of grades of students
2. Provide a feature that could easily search and locate the college students’ grades
3. Provide data security.
4. Provide an automated system that will let the student easily access their grades by using their school I.D. via Barcode.

Database Model and Design

tblclass (class_id, yearlevel_id, classname)

Table Description: class table will store information of the different class sections which includes the year level and the classname.

SQL Create Statement:

CREATE TABLE `tblclass` (
  `class_id` int(11) NOT NULL AUTO_INCREMENT,
  `yearlevel_id` int(11) NOT NULL,
  `classname` varchar(255) NOT NULL,
  PRIMARY KEY (`class_id`),
  UNIQUE KEY `yearlevel_id_2` (`yearlevel_id`,`classname`),
  KEY `yearLevel_id` (`yearlevel_id`),
  CONSTRAINT `tbl_class_year_fk` FOREIGN KEY (`yearlevel_id`) REFERENCES `tblyearlevel` (`yearlevel_id`)
) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=latin1;

tblcourse (course_id, coursename, description)

Table Description: tblcourse is the table that will store the records of different courses offered in the institution.

SQL Create Statement:

CREATE TABLE `tblcourse` (
  `course_id` int(11) NOT NULL AUTO_INCREMENT,
  `coursename` varchar(255) NOT NULL,
  `description` varchar(255) NOT NULL,
  PRIMARY KEY (`course_id`)
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=latin1;

tblfaculty (faculty_id, facultyno, fname, mname, lname, course_id, username, password, faculty_level, created)

Table Description: information of the faculty members such as the faculty id number, fullname, username and password used to access the system will be stored in the tblfaculty table.

SQL Create Statement:

CREATE TABLE `tblfaculty` (
  `faculty_id` int(11) NOT NULL AUTO_INCREMENT,
  `facultyno ` varchar(15) NOT NULL,
  `fname` varchar(255) NOT NULL,
  `mname` varchar(255) NOT NULL,
  `lname` varchar(255) NOT NULL,
  `course_id` int(11) DEFAULT NULL,
  `username` varchar(255) NOT NULL,
  `password` varchar(255) NOT NULL,
  `faculty_level` int(1) NOT NULL,
  `created` date NOT NULL,
  PRIMARY KEY (`faculty_id`),
  UNIQUE KEY `facNo` (`facNo`),
  KEY `course_id` (`course_id`),
  CONSTRAINT `tbl_fac_course_fk` FOREIGN KEY (`course_id`) REFERENCES `tblcourse` (`course_id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;

tblfacultysubject (faculty_subject_id, faculty_id, subject_id)

Table Description: loading of faculty subjects will be stored in the tblfacultysubject. The table has two foreign keys; the faculty_id which is connected to the tblfaculty table and the subject_id that is connected to the tblsubject table.

SQL Create Statement:

CREATE TABLE `tblfacultysubject` (
  `faculty_subject_id` int(11) NOT NULL AUTO_INCREMENT,
  `faculty_id` int(11) NOT NULL,
  `subject_id` int(11) NOT NULL,
  PRIMARY KEY (`faculty_subject_id`),
  UNIQUE KEY `faculty_id_2` (`faculty_id`,`subject_id`) USING BTREE,
  KEY `faculty_id` (`faculty_id`),
  KEY `subject_id` (`subject_id`),
  CONSTRAINT `tbl_fac_fk` FOREIGN KEY (`faculty_id`) REFERENCES `tblfaculty` (`faculty_id`),
  CONSTRAINT `tbl_sub_fk` FOREIGN KEY (`subject_id`) REFERENCES `tblsubject` (`subject_id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=latin1;

tblschoolyear (schoolyear_id, schoolyear, semester)

Table Description: every academic year and semester must be encoded also in this system and this will be stored in the tblschoolyear table.

SQL Create Statement:

CREATE TABLE `tblschoolyear` (
  `schoolyear_id` int(11) NOT NULL AUTO_INCREMENT,
  `schoolyear` varchar(30) NOT NULL,
  `semester` varchar(30) NOT NULL,
  PRIMARY KEY (`schoolyear_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;

tblstudent (student_id, studidno, student_fname, student_mname, student_lname, course_id, yearlevel_id, class_id, username, password, created)

Table Description: student information such as the id number of each student, fullname, course, year level, username and password to access the system and view grades are the information that will be stored in the tblstudent table.

SQL Create Statement:

CREATE TABLE `tblstudent` (
  `student_id` int(11) NOT NULL AUTO_INCREMENT,
  `studentidno` varchar(255) NOT NULL,
  `student_fname` varchar(255) NOT NULL,
  `student_mname` varchar(255) NOT NULL,
  `student_lname` varchar(255) NOT NULL,
  `course_id` int(11) NOT NULL,
  `yearlevel_id` int(11) NOT NULL,
  `class_id` int(11) NOT NULL,
  `username` varchar(255) NOT NULL,
  `password` varchar(255) NOT NULL,
  `created` date NOT NULL,
  PRIMARY KEY (`student_id`),
  UNIQUE KEY `studentIdNo` (`studentIdNo`),
  KEY `course_id` (`course_id`,`yearlevel_id`),
  KEY `class_id` (`class_id`),
  KEY `tbl_year_fk` (`yearlevel_id`),
  CONSTRAINT `tbl_class_fk` FOREIGN KEY (`class_id`) REFERENCES `tblclass` (`class_id`),
  CONSTRAINT `tbl_course_fk` FOREIGN KEY (`course_id`) REFERENCES `tblcourse` (`course_id`),
  CONSTRAINT `tbl_year_fk` FOREIGN KEY (`yearlevel_id`) REFERENCES `tblyearlevel` (`yearlevel_id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;

tblstudentgrade (studentgrade_id, student_id,  subject_id, faculty_id, course_id, schoolyear_id,  prelim, midterm, final, finalgrade  remarks)

Table Description: grades of the students will be stored in the tblstudentgrade.

SQL Create Statement:

CREATE TABLE `tblstudentgrade` (
  `studentgrade_id` int(11) NOT NULL AUTO_INCREMENT,
  `student_id` int(12) NOT NULL,
  `subject_id` int(11) NOT NULL,
  `faculty_id` int(11) NOT NULL,
  `course_id` int(11) NOT NULL,
  `schoolyear_id` int(11) NOT NULL,
  `prelim` double NOT NULL,
  `midterm` double NOT NULL,
  `final` double NOT NULL,
  `finalgrade` double NOT NULL,
  `remarks` varchar(15) NOT NULL,
  PRIMARY KEY (`studentgrade_id`),
  UNIQUE KEY `student_id_2` (`student_id`,`subject_id`),
  KEY `tbl_gr_fac_fk` (`faculty_id`),
  KEY `tbl_gr_crse_fk` (`course_id`),
  KEY `tbl_gr_sub_fk` (`subject_id`),
  KEY `tbl_gr_sch_fk` (`schoolyear_id`),
  KEY `student_id` (`student_id`),
  CONSTRAINT `tbl_gr_crse_fk` FOREIGN KEY (`course_id`) REFERENCES `tblcourse` (`course_id`),
  CONSTRAINT `tbl_gr_fac_fk` FOREIGN KEY (`faculty_id`) REFERENCES `tblfaculty` (`faculty_id`),
  CONSTRAINT `tbl_gr_sch_fk` FOREIGN KEY (`schoolyear_id`) REFERENCES `tblschoolyear` (`schoolyear_id`),
  CONSTRAINT `tbl_gr_stud_id` FOREIGN KEY (`student_id`) REFERENCES `tblstudent` (`student_id`),
  CONSTRAINT `tbl_gr_sub_fk` FOREIGN KEY (`subject_id`) REFERENCES `tblsubject` (`subject_id`)
) ENGINE=InnoDB AUTO_INCREMENT=76 DEFAULT CHARSET=latin1;

tblstudentsubject (studentsubject_id, student_id, subject_id, faculty_id)

Table Description: tblstudentsubject table stores information on the different subjects taken by the students.

SQL Create Statement:

CREATE TABLE `tblstudentsubject` (
  `studentsubject_id` int(11) NOT NULL AUTO_INCREMENT,
  `student_id` int(11) NOT NULL,
  `subject_id` int(11) NOT NULL,
  `faculty_id` int(11) NOT NULL,
  PRIMARY KEY (`studentsubject_id`),
  KEY `student_id` (`student_id`,`subject_id`,`faculty_id`),
  KEY `subject_id` (`subject_id`,`faculty_id`),
  KEY `faculty_id` (`faculty_id`),
  CONSTRAINT `tbl_sub_fac_fk` FOREIGN KEY (`faculty_id`) REFERENCES `tblfaculty` (`faculty_id`),
  CONSTRAINT `tbl_sub_stud_fk` FOREIGN KEY (`student_id`) REFERENCES `tblstudent` (`student_id`),
  CONSTRAINT `tbl_sub_sub_fk` FOREIGN KEY (`subject_id`) REFERENCES `tblsubject` (`subject_id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=latin1;

tblsubject (subject_id, subjectCode, subjectName, unit, yearlevel_id, schoolyear_id)

Table Description: list of subjects offered by the institution will be stored in the tblsubject table.

SQL Create Statement:

CREATE TABLE `tblsubject` (
  `subject_id` int(11) NOT NULL AUTO_INCREMENT,
  `subjectCode` varchar(255) NOT NULL,
  `subjectName` varchar(255) NOT NULL,
  `unit` int(11) NOT NULL,
  `yearlevel_id` int(11) NOT NULL,
  `schoolyear_id` int(11) NOT NULL,
  PRIMARY KEY (`subject_id`),
  UNIQUE KEY `subjectCode` (`subjectCode`),
  UNIQUE KEY `subjectName` (`subjectName`,`unit`,`yearlevel_id`),
  KEY `yearLevel_id` (`yearlevel_id`,`schoolyear_id`),
  KEY `yearLevel_id_2` (`yearlevel_id`,`schoolyear_id`),
  KEY `tbl_sch_fk` (`schoolyear_id`),
  CONSTRAINT `tbl_sch_fk` FOREIGN KEY (`schoolyear_id`) REFERENCES `tblschoolyear` (`schoolyear_id`),
  CONSTRAINT `tbl_subj_fk` FOREIGN KEY (`subject_id`) REFERENCES `tblyearlevel` (`yearlevel_id`),
  CONSTRAINT `tbl_yr_fk` FOREIGN KEY (`yearlevel_id`) REFERENCES `tblyearlevel` (`yearlevel_id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;

tblyearlevel  (yearlevel_id, yearLevel)

Table Description: year level information will be saved in the tblyearlevel

SQL Create Statement:

CREATE TABLE `tblyearlevel` (
  `yearlevel_id` int(11) NOT NULL AUTO_INCREMENT,
  `yearLevel` varchar(11) NOT NULL,
  PRIMARY KEY (`yearlevel_id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;

You may visit our Facebook page for more information, inquiries, and comments. Please subscribe also to our YouTube Channel to receive free capstone projects resources and computer programming tutorials.

Hire our team to do the project.