1
Data Management Group Assignment: SkillTrack E-Learning Database
Group 3
Anusha Bikkasani
Deepak Pulamolu
Krishna Sri Rangisetty
Manideep Chinnamsetti
Pavani Kurra
Pooja Tadwai
College of Technology, Wilmington University
IST7000: Data Management
Bert Gibbons
March 30, 2025
2
SkillTrack E-Learning Database
1. Business Problem
With the surge in demand for online education, educational institutions and educators are facing
numerous challenges in managing their courses, student enrollments, and content accessibility.
Traditional learning management systems (LMS) often lack the flexibility to handle real-time
course registrations, track student progress accurately, and manage instructor-led sessions
seamlessly. As a result, these inefficiencies contribute to various issues, including scheduling
conflicts, data inconsistencies, and heavy administrative workloads. Educators are also burdened
with manual tasks, such as tracking enrollments and monitoring student engagement, which
detracts from their ability to focus on teaching.
These operational challenges hinder the ability to deliver smooth and efficient online learning
experience, thereby affecting the quality of education and limiting the scalability of online
educational programs. As institutions expand their digital offerings, the need for a robust system to
handle these complexities becomes increasingly evident. The Skill Track E-Learning Database
aims to resolve these pain points by providing an organized, streamlined solution for managing
courses, student enrollments, content accessibility, and progress tracking.
2. Business Objectives
The primary goal of the SkillTrack E-Learning Database is to enhance the online learning
experience by providing a centralized, scalable system for managing digital classrooms. Key
objectives of this project include:
● Structured Course Management: The system will allow instructors to easily create,
3
update, and manage courses. It will provide tools for course organization, including
modules, lessons, assignments, and exams, ensuring that the learning material is accessible
and up-to-date for students.
● Automated Enrollment and Progress Tracking: To reduce the administrative burden on
educators and institutions, the database will automate student enrollment processes and
monitor student progress throughout their learning journey. This will improve the
efficiency of administrative tasks and ensure real-time updates on students’ progress.
● Real-Time Access Control: The database will ensure that students, instructors, and
administrators have real-time access to the relevant data, including course content, progress
reports, and communications. This will help maintain smooth interactions between
stakeholders, enhancing the overall learning experience.
● Performance Reports for Stakeholders: The system will generate performance reports
for both students and educators. These reports will provide detailed insights into student
engagement, progress, and completion, allowing instructors to adjust their teaching
methods and students to track their learning outcomes.
● Secure Data Storage: The SkillTrack system will prioritize secure data storage for
sensitive educational materials, student records, and assignments. By utilizing encryption
and backup protocols, the database will ensure that all data remains secure and easily
accessible when needed.
3. Scope
3.1 In-Scope Elements
Item# Name Description
1 User Manage profiles for students, instructors, and administrators, with
Management role-based permissions and access control.
4
2 Course Allow instructors to create, update, manage courses, add materials,
Management and track student performance.
3 Enrollment Enable automated student registration for courses and track
System participation.
4 Content Support storage and organization of course content like video
Management lectures, reading materials, and assignments.
5 Progress Monitor and record student engagement, assignment completion, and
Tracking course progress in real time.
3.2 Out-of-Scope Elements
Item# Name Description
1 Live Video Streaming The system will not support real-time video classes or
Integration webinars; only pre-recorded content is supported.
2 AI-Powered Learning No AI-based or personalized content suggestions for
Recommendations students will be included.
3 Payment Processing Financial transactions or online payment management will
not be handled within this system.
By focusing on these in-scope elements and excluding certain out-of-scope features, the Skill
Track E-Learning Database aims to provide a robust and efficient platform for managing online
courses while simplifying administrative tasks for educators and institutions.
4. Business Requirements/User Stories
REQ# User Stories
1 As a user, I want to register for an account so I can access the e-learning platform.
As an administrator, I must be able to manage user accounts to ensure platform security and
2
user access control.
3 As a user, I want to browse available courses so I can choose what I want to learn.
5
4 As a user, I want to enroll in a course and track my progress to stay motivated.
As an instructor, I want to upload and update course content, so learners have access to the
5
latest materials.
6 As a user, I want to take quizzes and receive instant feedback to evaluate my understanding.
As an administrator, I must be able to generate reports on user activity and course completion
7
for performance analysis.
As a user, I want to earn certificates upon course completion to showcase my learning
8
achievements.
5. Project Constraints
Item# Constraint Description
1 Budget The total budget for the project is limited to $25,000.
2 Time The system must be delivered within a 4-month timeline.
3 Resources The project will utilize a team of 4 developers and 1 project manager.
4 Platform The system must be accessible via both web and mobile platforms.
Technology The solution must use MySQL for the database and Python for backend
5
Stack development.
The platform must comply with GDPR for data privacy and user information
6 Security
protection.
7 Hosting The application must be deployed on Microsoft Azure Cloud Services.
The system must support integration with third-party payment gateways (e.g.,
8 Integration
PayPal, Stripe).
6
6. Conceptual Data Model
Entities and Attributes:
● User (UserID, FirstName, LastName, Email, Password, Role [Student/Instructor/Admin])
● Course (CourseID, Title, Description, InstructorID, Category)
● Enrollment (EnrollmentID, UserID, CourseID, EnrollmentDate, Progress,
CompletionStatus)
● Quiz (QuizID, CourseID, Title)
● Question (QuestionID, QuizID, Content, CorrectAnswer)
● Attempt (AttemptID, UserID, QuizID, AttemptDate, Score)
● Certificate (CertificateID, UserID, CourseID, IssueDate)
● Report (ReportID, AdminID, ReportType, GeneratedDate)
Relationships:
● A User can enroll in multiple Courses (many-to-many via Enrollment).
● A Course is created by one Instructor (one-to-many).
● A Course can have multiple Quizzes (one-to-many).
● A Quiz contains multiple Questions (one-to-many).
● A User can attempt multiple Quizzes (many-to-many via Attempt).
● Upon completion of a Course, a Certificate is issued to the User (one-to-one).
● Administrators can generate Reports (one-to-many).
Logical/Physical Data Model
7
Identified Entities, Attributes, and Relationships
Entities & Attributes
1. User
o PK: UserID
o FirstName
o LastName
o Email
o Password
o Role (Student / Instructor / Admin)
2. Course
o PK: CourseID
o Title
8
o Description
o Category
o FK: InstructorID → User(UserID)
3. Enrollment
o PK: EnrollmentID
o FK: UserID → User(UserID)
o FK: CourseID → Course(CourseID)
o EnrollmentDate
o Progress
o CompletionStatus
4. Quiz
o PK: QuizID
o FK: CourseID → Course(CourseID)
o Title
5. Question
o PK: QuestionID
o FK: QuizID → Quiz(QuizID)
o Content
o CorrectAnswer
6. Attempt
o PK: AttemptID
o FK: UserID → User(UserID)
o FK: QuizID → Quiz(QuizID)
o AttemptDate
9
o Score
7. Certificate
o PK: CertificateID
o FK: UserID → User(UserID)
o FK: CourseID → Course(CourseID)
o IssueDate
8. Report
o PK: ReportID
o FK: AdminID → User(UserID)
o ReportType
o GeneratedDate
ERD in 3rd Normal Form (3NF)
● All attributes are atomic (1NF)
● All non-key attributes are fully functionally dependent on the PK (2NF)
● There are no transitive dependencies (3NF)
● Proper PK–FK constraints are applied
● Relationships:
o A User can enroll in multiple Courses (M:N via Enrollment)
o A Course is created by one Instructor (1:N)
o A Course can have many Quizzes (1:N)
o A Quiz contains multiple Questions (1:N)
o A User can attempt many Quizzes (M:N via Attempt)
o A Certificate is issued to a User for completing a Course (1:1)
o Reports are generated by Admins (Users with role = Admin)
Data Dictionary
10
Entity Attribute Data Type Description Key
User UserID INT Unique ID for each user PK
FirstName VARCHAR(50) User’s first name
LastName VARCHAR(50) User’s last name
Email VARCHAR(100) User’s email address
Password VARCHAR(100) Encrypted password
User role:
Role VARCHAR(20)
Student/Instructor/Admin
Course CourseID INT Unique ID for each course PK
Title VARCHAR(100) Course title
Description TEXT Course description
Course category (e.g., Math,
Category VARCHAR(50)
Science)
FK →
InstructorID INT Linked instructor (User)
User
Enrollment EnrollmentID INT Unique enrollment ID PK
FK →
UserID INT Linked student
User
FK →
CourseID INT Linked course
Course
EnrollmentDate DATE When enrollment occurred
Progress DECIMAL(5,2) Progress %
CompletionStat VARCHAR(20) Completed / In Progress
11
Entity Attribute Data Type Description Key
us
Quiz QuizID INT Unique ID for each quiz PK
FK →
CourseID INT Linked course
Course
Title VARCHAR(100) Quiz title
Question QuestionID INT Unique ID for each question PK
FK →
QuizID INT Linked quiz
Quiz
Content TEXT Question content
CorrectAnswer VARCHAR(100) Correct answer for grading
Attempt AttemptID INT Unique attempt ID PK
FK →
UserID INT Linked user
User
FK →
QuizID INT Linked quiz
Quiz
AttemptDate DATE Date of quiz attempt
Score DECIMAL(5,2) Score obtained
Certificate CertificateID INT Unique certificate ID PK
FK →
UserID INT Linked user
User
CourseID INT Linked course FK →
12
Entity Attribute Data Type Description Key
Course
IssueDate DATE Date of certificate issuance
Report ReportID INT Unique report ID PK
FK →
AdminID INT Linked admin user
User
Type of report (e.g., usage,
ReportType VARCHAR(50)
performance)
GeneratedDate DATE Report generation date
Data Management – SQL
1. INSERT Statements
-- Inserting data into the 'Customers' table
INSERT INTO Customers (CustomerID, FirstName, LastName, Email, Phone)
VALUES (1, 'Anusha', 'Bikkasani', '[email protected]', '123-456-7890');
-- Inserting data into the 'Orders' table
INSERT INTO Orders (OrderID, CustomerID, OrderDate, TotalAmount)
VALUES (101, 1, '2025-04-01', 250.75);
-- Inserting data into the 'Products' table
INSERT INTO Products (ProductID, ProductName, Price, Category)
VALUES (501, 'Wireless Mouse', 25.99, 'Electronics');
13
2. SELECT Statements
- JOIN Example 1:
SELECT Customers.FirstName, Customers.LastName, Orders.TotalAmount
FROM Customers
JOIN Orders ON Customers.CustomerID = Orders.CustomerID
WHERE Orders.OrderDate >= '2025-01-01' AND Customers.FirstName = 'Anusha';
- JOIN Example 2:
SELECT Products.ProductName, Categories.CategoryName
FROM Products
JOIN Categories ON Products.CategoryID = Categories.CategoryID
WHERE Categories.CategoryName = 'Electronics' AND Products.ProductName = 'Wireless
Mouse';
- AGGREGATE and GROUP BY:
SELECT CustomerID, COUNT(OrderID) AS TotalOrders, AVG(TotalAmount) AS
AverageOrderAmount
FROM Orders
GROUP BY CustomerID
HAVING COUNT(OrderID) > 2 AND CustomerID = 1;
3. DELETE Statements
14
-- Deleting a specific order from the 'Orders' table
DELETE FROM Orders
WHERE OrderID = 101 AND CustomerID = 1;
-- Deleting a specific product from the 'Products' table
DELETE FROM Products
WHERE ProductID = 501 AND ProductName = 'Wireless Mouse';
-- Deleting a customer from the 'Customers' table
DELETE FROM Customers
WHERE CustomerID = 1 AND FirstName = 'Anusha';
4. UPDATE Statements
-- Updating the price of a product in the 'Products' table
UPDATE Products
SET Price = 29.99
WHERE ProductID = 501 AND ProductName = 'Wireless Mouse';
-- Updating the total amount for a specific order in the 'Orders' table
UPDATE Orders
SET TotalAmount = 275.50
WHERE OrderID = 101 AND CustomerID = 1;
-- Updating customer contact information in the 'Customers' table
UPDATE Customers
15
SET Phone = '987-654-3210'
WHERE CustomerID = 1 AND FirstName = 'Anusha';
10. Business Intelligence (BI) Report:
This report presents a Business Intelligence (BI) analysis of learner performance data sourced from
the SkillTrack E-Learning platform. The BI visualization was created using Power BI and
represents a bar chart of average student progress across two representative courses—Python
Basics and Data Analytics. This visual representation assists stakeholders such as instructors,
program managers, and educational analysts in understanding student achievement trends and
making strategic decisions for instructional improvement.
Data Sources and Structure
The dataset used for the visualization comprises three relational tables that represent key entities
within the SkillTrack system. Each table plays a vital role in modeling the interactions between
users, courses, and learning outcomes.
10.1. User Table
The User table captures information about individuals registered on the platform. Each user is
assigned a unique identifier (UserID), alongside their first name and system role. Roles can include
students, who consume learning content, and instructors, who manage and deliver the courses.
● UserID- A unique numerical identifier automatically assigned to each user. This serves as a
primary key, ensuring each record in the User table is distinct and traceable.
● FirstName- The first name of the user. This is primarily used for identification and
personalization in platform interfaces.
● Role - Indicates whether a user is a student or an instructor. This role classification is
critical for determining relationships with other tables such as Enrollment and Course.
16
The User table allows segmentation of data by role. For instance, when filtering reports to show
student progress, the system will only be considered entries where the role is “Student.”
Sample structure:
UserID FirstName Role
1 Emily Student
2 James Instructor
3 Sophia Student
4 Michael Instructor
5 Olivia Student
10.2. Course Table
The Course table records all courses offered on the platform. Each course is uniquely identified
using a CourseID. The table also includes the course title and the ID of the instructor responsible
for teaching it. Catalogs the various courses offered on the SkillTrack platform and links each
course to a specific instructor.
● CourseID - A unique identifier for each course. This acts as the primary key of the Course
table and is used in foreign key relationships with the Enrollment table.
● Title - Describes the name of the course (e.g., Python Basics, Data Analytics). It is used in
visualizations like charts and dashboards.
● InstructorID - A foreign key linking to the UserID in the User table, indicating which
instructor is responsible for the course.
Course titles are used as the X-axis in the Power BI chart to compare average student progress by
course. Additionally, course data helps identify which instructors are associated with
higher-performing students.
17
Sample structure:
CourseID Title InstructorID
101 Python 2
Basics
102 Data 4
Analytics
10.3. Enrollment Table
This table captures student enrollment data, linking users (students) with specific courses. It
includes a unique EnrollmentID, references to the user and course involved, and a numeric
progress indicator representing the percentage of course completion. Acts as a junction table
capturing which students are enrolled in which courses, along with their progress.
● EnrollmentID - A unique identifier for each enrollment record. This is the primary key of
the Enrollment table.
● UserID - A foreign key from the User table, indicating which student is enrolled in a
course.
● CourseID - A foreign key from the Course table, representing the course in which the
student is enrolled.
● Progress - (%) Reflects how much of the course content a student has completed, expressed
as a percentage.
This is the most critical table for analyzing student progress. The Progress (%) column is used to
calculate averages and drive the Y-axis in Power BI visualization.
Sample structure:
EnrollmentID UserID CourseID Progress (%)
201 1 101 88.0
202 3 101 67.5
203 5 102 91.2
18
10.4. Entity Relationships in Analytical Context
The relationships between these tables enable the generation of meaningful reports. Here’s how:
Each student (User) can be linked to multiple courses (Enrollment), and each course can have
multiple enrolled students. The Enrollment table serves as the bridge between Users and Courses,
and the recorded progress data becomes the foundation for performance analysis. With Power BI,
we can filter, aggregate, and visualize data based on these relationships to uncover trends like:
Which course has the highest student progress on average?
Which instructor’s course shows consistent improvement?
Do certain types of courses (e.g., technical vs. theoretical) show varied completion rates?
The clarity and structure of these entities enable Business Intelligence tools to provide actionable
insights:
- Administrators can detect which courses require enhancement.
- Instructors can be informed about student engagement levels.
- Students may receive personalized feedback based on performance metrics derived from
these relationships.
10.5. BI Chart – Average Student Progress by Course
The following chart was generated using Power BI to show the average progress for each course:
19
Figure 1. Power BI Chart Average Student Progress
10.6. Key Insights and Analysis
The bar chart reveals a discrepancy in student performance across the two courses. Learners in the
Data Analytics course achieved an average progress of 91.2%, whereas students in the Python
Basics course achieved a lower average of 77.75%. Several factors may contribute to this
variation:
- The Data Analytics course might have more structured content or better instructor support.
- Python Basics could be more challenging for beginners, requiring additional foundational
support.
- Variations in learner background knowledge and engagement levels may impact outcomes.
These insights can inform course redesign, targeted interventions, and performance monitoring
strategies.
10.7. Business and Educational Relevance
This BI analysis enables data-driven decision-making within the e-learning ecosystem. By
identifying which courses present more significant learning barriers, the organization can:
20
- Introduce adaptive learning technologies.
- Allocate instructional resources more effectively.
- Provide supplementary materials for more challenging topics.
- Empower instructors through targeted training.
Overall, such visualizations enhance educational equity and learning outcomes by focusing on
measurable learner progress. This report demonstrates the importance of integrating BI tools with
educational data systems. By analyzing progress metrics at the course level, instructors and
administrators gain valuable insight into learner behavior and course effectiveness. Continued
analysis and expansion of such datasets will further support curriculum improvement and learner
success.
11. Data Backup and Recovery Plan
Type Plan Description/Strategy
Backup Full Backup: A full database copy will happen once a week, on Sunday nights to
avoid complicating peak-usage times. This includes all Data from SkillTrack
E-Learning such as User, Enrollments, Courses, Progress and QuizAttempts. Full
backing up files should be kept offsite and onsite, and backups must be tested to
ensure they are working.
Differential Spread: Daily (Monday–Saturday only) to capture changes that were
made since the last Full Spread. This method mitigates the storage size vs. restore
time.
Transaction Log Backup: Every 15 minutes to allow point-in-time recovery, which is
21
Type Plan Description/Strategy
very important during times of high user activity (enrollments and progress updates).
This gives a Recovery Point Objective of around up to 15 minutes.
Storage Sites: Backups will be copied to site for rapid restores and offsite for DR.
On-prem backup will be for 2 weeks, and cloud archive will be for 90 days. The
cloud storage available on Azure can also be a cheaper piece of the backup plan.
Security: Backups will be AES-256 encrypted at rest, and TLS/SSL encrypted during
transmission. Access is going to be restricted via Azure Active Directory roles, and
that means it be limited to DBAs and DevOps and might want to add a feature to
logically isolate (separate) data of each consumer to prevent a potential security
issue.
Monthly Testing of the backup and restore capability will be performed to test
RTO and RPO target attainment and backup integrity. Testing automation with the
backup tools of Azure, or built-in features of MySQL, can prevent human mistakes.
We are going to do some automated monitoring and alerting to notice your backups
are failing.
22
Type Plan Description/Strategy
Recovery Single Transaction Recovery: Small errors, such as a single quiz score update failure,
can be addressed with transaction log backups to roll forward or roll back an
individual transaction while leaving the rest of the database as it is.
Full Database Recovery When major data loss, e.g. Server crash or database corrupts
happens we need to:
Recovering the latest full backup.
Performing the most recent differential backup.
Replaying every transaction log that was still available at the time of failure.
This guarantees the lowest possible data loss (RPO = 15 minutes), and targets the
availability of the database in 2-4 hours.
GDPR Compliance: Recovery will use technology to meet GDPR standards, with data
protection, encryption, and controlled access during recovery. This means that Data is
to be stored safely, it should be encrypted with restricted access in accordance with
GDPR rules.
