Conceptual Design – ER Diagram and Data Requirements

ER Diagram

Data Requirements

Entities

• Users: Entity representing all users who access the platform (students / learners).
  • user_id – Primary key
  • first_name – Varchar
  • last_name – Varchar
  • email – Varchar(unique)
  • password – Varchar

• Administrators: Entity representing instructors who create and manage educational content.
  • Admin_ID – Primary key
  • Admin_Level – varchar

• Payment: Entity representing financial transactions.
  • Payment_ID – Primary key
  • Amount – Decimal

• Course: Entity representing the courses offered on the platform.
  • Course_id – Primary key
  • Certificate – Varchar
  • Name – Varchar
  • Price – Decimal
  • status – Varchar

• Enrollment: Entity representing the enrollment of a user in a course.
  • Enrollment_id – 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 – Primary key
  • Name – Varchar
  • Price – Decimal
  • Duration_months – Int
  • Description – Varchar
  • Access_type – Varchar

• Certificate: Entity representing certificates issued after course completion.
  • Certificate_id – Primary key
  • Issue_date – Date
  • Certificate_code – Varchar
  • Status – Varchar

• UserSubscription: Entity representing active subscriptions of users.
  • Subscription_ID – Primary key
  • Start_date – Date
  • End_date – Date
  • Status – Varchar

• SupportTicket: Entity representing user support requests.
  • Ticket_ID – Primary key
  • Subject – Varchar
  • Description – Varchar
  • Status – Varchar
  • Created_at – Date

• Category: Entity representing course classification.
  • Category_ID – Primary key
  • Name – Varchar
  • Description – Varchar

• Module: Entity representing logical subdivisions of a course.
  • Мodule_id – Primary key
  • Тitle – Varchar
  • Description – Varchar

• Lesson: Entity representing individual learning units.
  • Lesson_ID – Primary key
  • Тitle – Varchar
  • Material – Varchar

• Quiz: Entity representing assessments associated with lessons.
  • Quiz_ID – Primary key
  • Total_points – Int
  • Passing_score – Int

Relationships

• A User can make multiple Payments (1:N)
• A User can enroll in multiple Courses through Enrollment (1:N)
• An Instructor can upload and manage multiple Courses (1:N)
• A Course can have multiple Enrollments (1:N)
• A SubscriptionPlan can be used by multiple Users (1:N)
• A Certificate is issued for a completed Enrollment (1:1)