Elearning System Database Design

Title: Elearning System Database Design

Description: the elearning system will be developed using PHP and MySQL, for the user interface the researcher will use Bootstrap. There are two types of users for this system, the administrator or the teacher account and the student account. The teacher/administrator will be the one to encode the student information, lessons, categories and quizzes. The student account can then login to the system using the username and password provided by the teachers, they can now view the lessons in text format, pdf version or even in video presentation, after that they can now take a quiz and view the results.

Database Schema with description of table:

This section will discuss the different tables of the elearning system.

tbluser stores basic information of the persons who are using the system such as username, password, firstname and lastname. User level field is set by the administrator specifying the role of the user. The id field is auto-generated by the table/system.

tbluser (id, username, password, firstname, lastname, userlevel)

tblstudent table stores information of students such as student id number, firstname and lastname

tblstudent (id, studentidno, lastname, firstname)

tbllessoncategory is the table that stores the category name and description

tbllessoncategory (id, categoryName, description)

tbllesson table stores information about the lessons or topics, it includes the name of the lesson, the contents and the category where it belongs.

tbllesson (id, lessonName, content, categoryid)

The system can accept lessons in pdf format and the table to store that record is the tbllessonpdf. The table is connected to the lesson and the table also stores the location of the pdf file.

tbllessonpdf (id, lessonID, pdfFile)

Aside from lessons in pdf format, you can also upload a video presentation for your lessons, tbllessonvideo is the table for storing the video files or the location of the videos.

tbllessonvideo (id, lessonID, videoFile)

The way to evaluate if the students had gain knowledge is to give them a test. Elearning system is a way to learn things using your electronic devices such as your computers or mobile devices, it is also a platform to test your students as well. The table for storing the questions is tblquiz, it stores information about the questions and the choices.

tblquiz (id, question, ans1, ans2, ans3, ans4, correctAnswer, lessonID)

tblresult is the table for storing the scores of the student, it also includes the lesson and the date.

tblresult (id, studentid, lessonID, score, totalItem, date)

 Relationship of tables

The relationship is used to cross reference information between tables.

Sample Queries:

The query below will display the list of students together with their scores:

SELECT tblstudent.studentidno, tblstudent.lastname, tblstudent.firstname, tbllesson.lessonname, tbllessoncategory.categoryname, tblresult.score, tblresult.totalitem, tblresult.daterecorded
FROM tblstudent INNER JOIN ((tbllessoncategory INNER JOIN tbllesson ON tbllessoncategory.ID = tbllesson.categoryid) INNER JOIN tblresult ON tbllesson.ID = tblresult.lessonid) ON tblstudent.ID = tblresult.studentid
GROUP BY tblstudent.studentidno, tblstudent.lastname, tblstudent.firstname, tbllesson.lessonname, tbllessoncategory.categoryname, tblresult.score, tblresult.totalitem, tblresult.daterecorded;

System Flow:

1. The administrator will login to the system using the valid username and password
2. The administrator will encode the list of student(lastname, firstname, middlename, username and password)
3. Then the administrator will encode the list of Yunits
4. The administrator can now encode the list of topics per yunit
5. The administrator can now upload pdf files and videos
6. The administrator can now add quiz per yunit
   1. The students can now login using their username and password
   2. The students can now view the different Yunits and aralin
   3. The students has the option to view the videos and pdf files
   4. The students will then take the quiz and after that results will be provided
7. Lastly the administrator can view the result of the student’s quiz

Database and the user interface of the project will be uploaded soon, please visit the download section for the updates.

For comments, suggestions and questions, you can contact us via our facebook page

https://www.inettutor.com/