E-LEARNING DATABASE DESIGN

This article will discuss the step by step process on how to prepare the entity relationship diagram or ERD of the project entitled ELearning.

Before we process the tutorial and explanation on how to create ERD for the ELearning system, let us explain and describe the purpose of the ELearning system.

                       Brief overview of the user story

E-Learning represents an innovative shift in the field of learning, providing rapid access to specific knowledge and information. It offers online instruction that can be delivered anytime and anywhere through a wide range of electronic learning solutions such as web-based courseware, online discussion groups, live virtual classes, video and audio streaming, web chat, online simulations, and virtual mentoring.

                    We can start now!

The first step in the development of the ELearning system is to prepare that will serve as the basis later on in the creation of the actual database.

We will create and explain the process of making the entity relationship diagram of the ELearning système.

 Let’s start from the symbols used in the ER Diagram.

📎Entity is represented by the rectangle shape. The entity will be our database table of the ELearning system later on.

📎Attribute is represented by diamond shape. This will determine the relationships among entities. This is usually in a form of primary key to foreign key connection.

    We will follow the 3 basic rules in creating the ER diagram. 

🔎Identify all the entities.
🔎Identify the relationship between entities.
🔎Add meaningful attributes to our entities.

STEP 1. In the ELearning system we have the following entities

📌 Student
📌 Result
📌Lesson
📌Lesson Category
📌Lesson Video
📌Quiz
📌Lesson PDF
Our design of ELearning system consists of 7 entities; the specified entities will be our database tables in the design and implementation of eLearning database schema.

We will now draw the entities of the ELearning system specified above and it will be the ELearning system.

  STEP 2 

After we have specified our entities, it is time now to connect or establish a relationship among the entities.
✔ Students view their scores for every lesson (1 to many relationship).

✔ A lesson belongs to a specific lesson category (1 to 1 relationship).

✔ A lesson contains multiple videos (1 to many relationship)

✔ A lesson contains multiple pdf files ( 1 to many relationship)

✔ Every lesson has an assessment in a form of quiz (1 to 1 relationship).

        STEP 3 

The last part of the ERD process is to add attributes to our entities.
Student Entity has the following attributes:
🔑ID – primary key represented with underline
🗝 Student ID No
🗝 First name
🗝 Last name

Result Entity has the following attributes:
🔑ID – primary key represented with underline
🔑Student ID – foreign key
🔑Lesson ID – foreign key
🗝Score
🗝Total Item
🗝Date Recorded

Lesson Entity has the following attributes:
🔑ID – primary key represented with underline
🗝Name
🗝Contents
🗝Category ID

Lesson Category Entity has the following attributes:
🔑ID – primary key represented with underline
🗝Name
🗝Description

Lesson Video Entity has the following attributes:
🔑ID – primary key represented with underline
🔑Lesson ID – foreign key
🗝Video File

Quiz Entity has the following attributes:
🔑ID – primary key represented with underline
🗝Lesson ID
🗝Question
🗝Correct Answer
🗝Answer 1
🗝Answer 2
🗝Answer 3
🗝Answer 4

Lesson PDF Entity has the following attributes:
🔑ID – primary key represented with underline
🔑Lesson ID – foreign key
🗝PDF File

The next step is to convert the plan designed on ER Diagram into
the actual database

        Database schema with description of table

This section will discuss the different tables of the E-learning system

📝 tbl student (id, studentidno, last name, firstname) table stores information of students such as student id number, firstname and lastname

📝tbl lesson category (id, categoryName, description) is the table that stores the category name and description

📝tbl lesson (id, lessonName, content, categoryid) table stores information about the lessons or topics, it includes the name of the lesson, the contents and the category where it belongs.

📝tbl lesson pdf (id, lessonID, pdfFile) The system can accept lessons in pdf format and the table to store that record is the tbl lesson pdf. The table is connected to the lesson and the table also stores the location of the pdf file.

📝tbl lesson video (id, lessonID, videoFile) Aside from lessons in pdf format, you can also upload a video presentation for your lessons, tbl lesson video is the table for storing the video files or the location of the videos.

📝tbl quiz (id, question, ans1, ans2, ans3, ans4, correctAnswer, lessonID) 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 tbl quiz, it stores information about the questions and the choices.

📝tbl result (id, student id, lessonID, score, totalItem, date) is the table for storing the scores of the student, it also includes the lesson and the date.

   Relationship of tables

The relationship is used to cross reference information between tables.

              MySql workbench
                     ⇓