Proposed Tables
Table 0: users
Field Data type Validation Example
user_id VARCHAR Cannot be null johndoe@gmail.com
Must be unique
At least 8 characters
password VARCHAR Cannot be null Hamilton%07
At least 10 characters
Must have a number, symbol, and
capital letter
verification_answer VARCHAR Cannot be null Baobao
Table 1:
student_registration
Field Data type Validation Example
student_id (PK) INT Cannot be null 523
Auto increment
Must be unique
student_first_name VARCHAR Cannot be null John
student_last_name VARCHAR Optional Doe
email_id VARCHAR Cannot be null johndoe@gmail.com
Must contain "@" and
".com"
Must be unique
mobile_number VARCHAR Cannot be null 1234567890
Must contain 10 digits
Table 2: courses
Field Data type Validation Example
course_id (PK) VARCHAR Cannot be null M-101
Must be unique
course_name VARCHAR Cannot be null Fundamentals of Mathematics
Table 3:
student_courses
Field Data type Validation Example
student_id (FK) INT Must exist in students 523
table
course_id (FK) VARCHAR Must exist in courses M-101
table
PRIMARY KEY student_id + Must be unique 532 +
course_id 5101
Table 4:
attendance
Field Data type Validation Example
student_id (FK) INT Cannot be null 523
Must exist in students
table
course_id (FK) VARCHAR Cannot be null M-101
Must exist in courses
table
lesson_date DATE Cannot be null "2025-04-
29"
status ENUM("present", "absent", Cannot be null "present"
"excused")
Table 5: due_fees
Field Data type Validation Example
fees_id (PK) INT Cannot be null 412
Auto increment
Must be unique
student_id (FK) INT Cannot be null 523
Must exist in students table
course_id (FK) INT Must exist in courses table M-101
amount INT Cannot be negative 200
due_month ENUM Cannot be null "April"
Table 6: payments
Field Data type Validation Example
fees_id (FK) INT Cannot be null Dec2025
Must exist in due_fees table
date_paid DATE Cannot be null "2025-04-29"
Table 7:
lessons
Field Data type Validation Example
lesson_id (PK) INT Cannot be null 31
Auto
increment
Must be
unique
course_id (FK) INT Cannot be null M-101
Must exist in
courses table
lesson_date DATE Cannot be null "2025-04-30"
duration TIME Cannot be null "1:30:00"
objective TEXT Cannot be null "Understand mixed fractions and perform
involving basic operations."
introduction TEXT Optional
procedure TEXT Optional
assesment_file VARCHAR Optional "/assesments/mixed_fractions.docx"
reflection TEXT Optional
Table 8:
material_files
Field Data type Validation Example
file_id (PK) INT Cannot be 128
null
Auto
increment
Must be
unique
lesson_id (FK) VARCHAR Cannot be 31
null
Table 8:
material_files
Must exist in
lessons table
file_path VARCHAR Cannot be "/materials/worksheet_A1.pdf"
null
file_type ENUM("PDF", "DOCX", Cannot be PDF
"XLSX", "PPTX", "TXT", null
"MP4", "MP3", "JPG",
"PNG")`
description TEXT Optional Math worksheet for lesson 1
date_uploaded DATE Cannot be "2025-04-20"
null
Table 9:
homework
Field Data type Validation Example
homework_id INT Cannot be null 45
(PK) Auto increment
Must be unique
lesson_id (FK) INT Cannot be null 31
Must exist in
lessons table
file_path VARCHAR Optional "/homework/mixed_fractions"
description VARCHAR Optional "Complete problems 1-10 with step-by-
step solution."
due_date DATE Cannot be null "2025-04-20"
Table 10:
progress_notes
Field Data type Validation Example
note_id (PK) INT Cannot be null 317
Auto increment
Must be unique
student_id (FK) INT Cannot be null 523
Must exist in
students table
Table 10:
progress_notes
course_id (FK) VARCHAR Optional M-101
Must exist in
courses table
content TEXT Cannot be null "Needs help with English reading
comprehension to be able to solve
word problems."
date_created DATETIME Cannot be null "2025-04-20 10:30:30"
last_edited TIMESTAMP Optional "2025-05-03 05:10:45"
Entity-relationship diagram (ERD)
Product Design (GUI)
[add descriptions of elements on the screen]
[make them using writing pad at home]
Figure 1: User registration
Figure 2: Login
Figure 3: Reset Password
Figure 4: Dashboard
Figure 5: Course list
Figure 6: Course page
Figure 7: Attendance sheet
Figure 8: Fee and payment page
Figure 9: Student List
Figure 10: Student profile
Figure 11: Progress notes
Figure 12: Add course
Figure 13: Add student
Figure 14: Edit student profile
Figure 15: Add course
UML Diagram
Data-flow diagram
Main classes and methods
