Conceptual Design – ER Diagram and Data Requirements

ER Diagram

Data Requirements

Entities

• User: Entity representing all users who access the platform (students / learners).
  • User_ID – INT (PRIMARY KEY)
  • First_Name – VARCHAR
  • Last_Name – VARCHAR
  • Email – VARCHAR(unique)
  • Password – VARCHAR

• Administrator: Entity representing instructors who create and manage educational content.
  • Admin_ID – INT (PRIMARY KEY)
  • Admin_Level – VARCHAR

• Instructor: Entity representing instructors who upload and manage course materials.
  • Instructor_ID – INT (PRIMARY KEY)
  • BIO – VARCHAR
  • Ekspertise – VARCHAR

• Course: Entity representing the courses offered on the platform.
  • Course_id – INT (PRIMARY KEY)
  • Name – VARCHAR
  • Price – Decimal
  • status – VARCHAR

• Enrollment: Entity representing the enrollment of a user in a course.
  • Enrollment_id – INT (PRIMARY KEY)
  • Enroll_date – DATE
  • Completion_Status – VARCHAR
  • Progress_percentage – INT

• Payment: Entity representing financial transactions.
  • Payment_ID – PRIMARY KEY
  • Amount – DECIMAL

• SubscriptionPlan: Entity representing available subscription plans.
  • Plan_ID – INT (PRIMARY KEY)
  • Name – VARCHAR
  • Price – DECIMAL
  • Duration_months – INT
  • Description – VARCHAR
  • Access_type – VARCHAR

• Certificate: Entity representing certificates issued after course completion.
  • Certificate_id – INT (PRIMARY KEY)
  • Issue_date – DATE
  • Certificate_code – VARCHAR
  • Status – VARCHAR

• UserSubscription: Entity representing active subscriptions of users.
  • Subscription_ID – INT (PRIMARY KEY)
  • Start_date – DATE
  • End_date – DATE
  • Status – VARCHAR

• SupportTicket: Entity representing user support requests.
  • Ticket_ID – INT (PRIMARY KEY)
  • Subject – VARCHAR
  • Description – VARCHAR
  • Status – VARCHAR
  • Created_at – DATE

• Category: Entity representing course classification.
  • Category_ID – INT (PRIMARY KEY)
  • Name – VARCHAR
  • Description – VARCHAR

• Module: Entity representing logical subdivisions of a course.
  • Мodule_id – INT (PRIMARY KEY)
  • Тitle – VARCHAR
  • Description – VARCHAR

• Lesson: Entity representing individual learning units.
  • Lesson_ID – INT (PRIMARY KEY)
  • Тitle – VARCHAR
  • Material – VARCHAR

• Quiz: Entity representing assessments associated with lessons.
  • Quiz_ID – INT (PRIMARY KEY)
  • Total_points – INT
  • Passing_score – INT

• QuizAttempt: Entity representing individual quiz attempts made by users.
  • Attempt_ID – INT PRIMARY KEY
  • Score – INT
  • Attempt_date– DATE

Relationships

• Pays - 1:N between User and Payment. One user can make multiple payments, Each payment is associated with exactly one user.

• Owns - 1:N relationship between User and UserSubscription. Each user subscription belongs to exactly one user.

• Is_based_on - N:1 relationship between UserSubscription and SubscriptionPlan. Each user subscription is based on exactly one subscription plan, One subscription plan can be associated with many user subscriptions.

• Subscription - 1:N relationship between UserSubscription and Payment. One user subscription can be paid through one or multiple payments, Each payment is associated with exactly one user subscription.

• Submits - 1:N relationship between User and SupportTicket. One user can create multiple support tickets, Each support ticket is created by exactly one user.

• Resolves - 1:N relationship between Administrator and SupportTicket. One administrator can manage multiple support tickets, Each support ticket is managed by exactly one administrator.

• Manages - 1:N relationship between Administrator and Course. One administrator can manage multiple courses, Each course is managed by exactly one administrator.

• Uploads and manages - 1:N relationship between Instructor and Course. One instructor can upload and manage multiple courses, Each course is uploaded and managed by exactly one instructor.

• Enrolls_in - 1:N relationship between User and Enrollment. One user can have multiple enrollments, Each enrollment is associated with exactly one user.

• Refers_to - N:1 relationship between Enrollment and Course. Each enrollment is associated with exactly one course, One course can have multiple enrollments.

• Results_in - 1:1 relationship between Enrollment and Certificate. Each enrollment can lead to exactly one certificate, Each certificate is issued for exactly one enrollment.

• Consists_of - 1:N relationship between Course and Module. One course can create and contain multiple modules, Each module belongs to exactly one course.

• BelongsTo - N:M relationship between Course and Category. Each course belongs to exactly one category, One category can include multiple courses.

• Contains - 1:N relationship between Module and Lesson. One module can contain multiple lessons, Each lesson belongs to exactly one module.

• is_assessed_by - 1:1 relationship between Lesson and Quiz. Each lesson has exactly one quiz, Each quiz is associated with exactly one lesson.

• Is_for - 1:N relationship between Quiz and QuizAttempt. One quiz can have multiple quiz attempts, Each quiz attempt is associated with exactly one quiz.

• Submits - 1:N relationship between User and QuizAttempt. One user can submit multiple quiz attempts, Each quiz attempt is submitted by exactly one user.