Title: Elearning System Database Design
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.
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;
- The administrator will login to the system using the valid username and password
- The administrator will encode the list of student(lastname, firstname, middlename, username and password)
- Then the administrator will encode the list of Yunits
- The administrator can now encode the list of topics per yunit
- The administrator can now upload pdf files and videos
- The administrator can now add quiz per yunit
- The students can now login using their username and password
- The students can now view the different Yunits and aralin
- The students has the option to view the videos and pdf files
- The students will then take the quiz and after that results will be provided
- 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