Transcript of Records Processing System Database Model

Transcript of Records Processing System Database Model

The project entitled Transcript of Records Processing System is actually a sub component of the enrolment or school management system. In this article, we will show you the list of tables and their relationships.

The said system has the following tables:

• tblcourse
• tblfinalgrade
• tblsemester
• tblstudentinfo
• tblstudentsubject
• tblsubject
• tbluser
• tblyearlevel

Database Schema

tblstudentinfo (student_info_id, student_id_no, first_name, last_name, middle_name, address, gender, contact, course_id, yearlevel_id, semester_id, user_id)

Table Description: tblstudentinfo is the table that stores the personal information of the students. It has 12 fields or columns; (1) student_info_id is the primary key, (2) student_id_no is the unique number provided the institution, (3) first_name stores the first name of the student, (4) last_name for the last name of the student, (5) middle_name column for the middle name of the student, (6) address stores the complete address of the student, (7) gender, (8) contact for the contact number and information of the student, (9) course_id is a foreign key that links to the tblcourse table, (10) yearlevel_id is another foreign key that links to the tblyearlevel, (11) semester_id also is a foreign key that connects to the tblsemester, (12) user_id represents the user who encodes the information of the student. The table below is the sql that will create the tblstudentinfo in your database.

Create SQL Statement:

CREATE TABLE IF NOT EXISTS `tblstudentinfo` (
`student_info_id` int(11) NOT NULL AUTO_INCREMENT,
`student_id_no` varchar(15) NOT NULL,
`first_name` varchar(30) NOT NULL,
`last_name` varchar(30) NOT NULL,
`middle_name` varchar(30) NOT NULL,
`address` varchar(100) NOT NULL,
`gender` int(1) NOT NULL,
`contact` varchar(11) NOT NULL,
`course_id` int(11) NOT NULL,
`yearlevel_id` int(11) NOT NULL,
`semester_id` int(11) NOT NULL,
`user_id` int(11) NOT NULL,
PRIMARY KEY (`student_info_id`),
KEY `course_id` (`course_id`,`yearlevel_id`,`semester_id`,`user_id`),
KEY `user_id` (`user_id`),
KEY `yearlevel_id` (`yearlevel_id`),
KEY `semester_id` (`semester_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

tblcourse (course_id, course_code, description)

Table Description: tblcourse table is the table that stores the list of courses or programs offered by the institution. It has 3 columns; (1) course_id is the primary key that serves as the unique key for identification of records, (2) course_code represents the code assigned to the program, (3) and the description for the complete name of the course.

Create SQL Statement:

CREATE TABLE IF NOT EXISTS `tblcourse` (
`course_id` int(11) NOT NULL AUTO_INCREMENT,
`course_code` varchar(15) NOT NULL,
`description` varchar(100) NOT NULL,
PRIMARY KEY (`course_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

tblsemester (semester_id, semester, schoolyear)

Table Description: information of semester will be stored in the tblsemester. The table contains 3 columns; (1) semester_id is the primary key of the table, (2) semester is the name of the semester (1^st sem, 2^nd sem, 3^rd sem, summer), (3) schoolyear represents the academic year.

Create SQL Statement:

CREATE TABLE IF NOT EXISTS `tblsemester` (
`semester_id` int(11) NOT NULL AUTO_INCREMENT,
`semester` varchar(15) NOT NULL,
`schoolyear` varchar(15) NOT NULL,
PRIMARY KEY (`semester_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

tblyearlevel (year_level_id, year_level, description)

Table Description: tblyearlevel is the table that stores the information of the different year levels such as 1^st year, 2^nd year, 3^rd year, 4^th year and 5^th year if applicable.

Create SQL Statement:

CREATE TABLE IF NOT EXISTS `tblyearlevel` (
`year_level_id` int(11) NOT NULL AUTO_INCREMENT,
`year_level` varchar(10) NOT NULL,
`description` varchar(100) NOT NULL,
PRIMARY KEY (`year_level_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

tblsubject (subject_id, subject_code, subject_name, year_level_id, semester_id, course_id, units)

Table Description: tblsubject is the table that stores the list of subjects per semester, year level and course. The table has 7 columns; (1) subject_id serves as the unique key or primary key of the table, (2) subject_code is the code given to the subject, (3) subject_name is the complete name of the subject, (4) year_level_id is a foreign key that links to the tblyearlevel, (5) semester_id is the foreign key that connects to the tblsemester, this means that the subject is only offered for that semester but the students can request of apply for a special class, (6) course_id is another foreign key that links to the tblcourse, it means that the subject is for a specific program or course, (7) units column represents the number of units that will be credited to the students.

Create SQL Statement:

CREATE TABLE IF NOT EXISTS `tblsubject` (
`subject_id` int(11) NOT NULL,
`subject_code` varchar(15) NOT NULL,
`subject_name` int(50) NOT NULL,
`year_level_id` int(11) NOT NULL,
`semester_id` int(11) NOT NULL,
`course_id` int(11) NOT NULL,
`units` int(1) NOT NULL,
PRIMARY KEY (`subject_id`),
KEY `year_level_id` (`year_level_id`,`semester_id`,`course_id`),
KEY `semester_id` (`semester_id`),
KEY `course_id` (`course_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

tblstudentsubject (student_subject_id, student_info_id, semester_id, subject_id)

Table Description: tblstudentsubject is the table that stores the lists of subjects enrolled by the student. The table has 4 fields or columns; (1) student_subject_id as the primary key, (2) student_info_id this is a foreign key that connects to the tblstudentinfo table, (3) semester_id represents the semester in which the student has enrolled, (4) subject_id links to the tblsubject, this is where the name of the subject taken by the student is stored.

Create SQL Statement:

CREATE TABLE IF NOT EXISTS `tblstudentsubject` (
`student_subject_id` int(11) NOT NULL AUTO_INCREMENT,
`student_info_id` int(11) NOT NULL,
`semester_id` int(11) NOT NULL,
`subject_id` int(11) NOT NULL,
PRIMARY KEY (`student_subject_id`),
KEY `student_info_id` (`student_info_id`,`semester_id`,`subject_id`),
KEY `subject_id` (`subject_id`),
KEY `semester_id` (`semester_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

tblfinalgrade (grade_id, student_info_id, student_subject_id, final_grade, units, remarks, date_recorded)

Table Description: tblfinalgrade is the database table that stores the final grade of the student. The said table has 7 columns. This table is where most of the queries are fetch because the main purpose of the project is to generate a transcript of records.

Create SQL Statement:

CREATE TABLE IF NOT EXISTS `tblfinalgrade` (
`grade_id` int(11) NOT NULL AUTO_INCREMENT,
`student_info_id` int(11) NOT NULL,
`student_subject_id` int(11) NOT NULL,
`final_grade` int(3) NOT NULL,
`units` int(1) NOT NULL,
`remarks` varchar(50) NOT NULL,
`date_recorded` date NOT NULL,
PRIMARY KEY (`grade_id`),
KEY `student_info_id` (`student_info_id`,`student_subject_id`),
KEY `student_subject_id` (`student_subject_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

tbluser (user_id, complete_name, username, password)

Table Description: the system can only be accessed by an authorized user (registrar, registrar staff and administrators).  The table consists of 4 attributes; (1) user_id as the primary key, (2) complete_name refers to the name of the user, (3) username is the desired username of the user, and (4) password the username and password combo is used to gain access in the system.

Create SQL Statement:

CREATE TABLE IF NOT EXISTS `tbluser` (
`user_id` int(11) NOT NULL AUTO_INCREMENT,
`complete_name` varchar(50) NOT NULL,
`username` varchar(30) NOT NULL,
`password` varchar(30) NOT NULL,
PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ; 

Constraints for dumped tables

—
— Constraints for table `tblfinalgrade`
—
ALTER TABLE `tblfinalgrade`
ADD CONSTRAINT `tblfinalgrade_ibfk_2` FOREIGN KEY (`student_subject_id`) REFERENCES `tblstudentsubject` (`student_subject_id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `tblfinalgrade_ibfk_1` FOREIGN KEY (`student_info_id`) REFERENCES `tblstudentinfo` (`student_info_id`) ON DELETE CASCADE ON UPDATE CASCADE;

—
— Constraints for table `tblstudentinfo`
—
ALTER TABLE `tblstudentinfo`
ADD CONSTRAINT `tblstudentinfo_ibfk_4` FOREIGN KEY (`semester_id`) REFERENCES `tblsemester` (`semester_id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `tblstudentinfo_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `tbluser` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `tblstudentinfo_ibfk_2` FOREIGN KEY (`course_id`) REFERENCES `tblcourse` (`course_id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `tblstudentinfo_ibfk_3` FOREIGN KEY (`yearlevel_id`) REFERENCES `tblyearlevel` (`year_level_id`) ON DELETE CASCADE ON UPDATE CASCADE;

—
— Constraints for table `tblstudentsubject`
—
ALTER TABLE `tblstudentsubject`
ADD CONSTRAINT `tblstudentsubject_ibfk_2` FOREIGN KEY (`semester_id`) REFERENCES `tblsemester` (`semester_id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `tblstudentsubject_ibfk_1` FOREIGN KEY (`subject_id`) REFERENCES `tblsubject` (`subject_id`) ON DELETE CASCADE ON UPDATE CASCADE;

—
— Constraints for table `tblsubject`
—
ALTER TABLE `tblsubject`
ADD CONSTRAINT `tblsubject_ibfk_3` FOREIGN KEY (`course_id`) REFERENCES `tblcourse` (`course_id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `tblsubject_ibfk_1` FOREIGN KEY (`semester_id`) REFERENCES `tblsemester` (`semester_id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `tblsubject_ibfk_2` FOREIGN KEY (`year_level_id`) REFERENCES `tblyearlevel` (`year_level_id`) ON DELETE CASCADE ON UPDATE CASCADE;

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.