Stretch

Online course platform: ERD case study

An online learning platform needs to track instructors, courses, lessons and learner progress.

The case study

  1. Instructors create courses. Each instructor has an instructor ID, a name and a bio. Every course is created by exactly one instructor; an instructor may have created many courses, or none yet.
  2. A course has a course code, a title and a price, and is made up of one or more lessons.
  3. Each lesson belongs to exactly one course and has a lesson number, a title and a duration. Lesson numbers restart at 1 in every course.
  4. Learners sign up with a learner ID, a name and an email. A learner can enrol in many courses, and a course can have many learners. A new course may have no learners yet, and a learner who has just signed up may not be enrolled in anything.
  5. For every enrolment we record the enrolment date and the learner's progress percentage.

Your task

Draw an Entity Relationship Diagram in Crow's Foot notation. Show every entity with its attributes, mark each primary key (PK) and foreign key (FK), and set the minimum and maximum cardinality at both ends of every relationship. Resolve any many-to-many relationship with an associative entity.

Watch out: not every noun in the case study is an entity. Some only describe something else.

Draw it in the ERD Lab and get feedback on each entity, relationship and key.

Open the ERD Lab

Model answer

Show the model answer
Crow's Foot ERD for Online course platformcreatesis made up ofhashasInstructorPK Instructor IDNameBioCoursePK Course codeTitlePriceFK Instructor IDLessonPK FK Course codePK Lesson numberTitleDurationEnrolmentPK FK Learner IDPK FK Course codeEnrolment dateProgress percentageLearnerPK Learner IDNameEmail

Entities and keys

  • Instructor: Instructor ID (PK), Name, Bio
  • Course: Course code (PK), Title, Price, Instructor ID (FK)
  • Lesson: Course code (PK) (FK), Lesson number (PK), Title, Duration
    Lesson number restarts in every course (each course has a Lesson 1), so it can't identify a lesson alone. Use Course code + Lesson number together.
  • Enrolment: Learner ID (PK) (FK), Course code (PK) (FK), Enrolment date, Progress percentage
    One learner enrols in a course once, so the pair Learner ID + Course code identifies each enrolment.
  • Learner: Learner ID (PK), Name, Email

Relationships, read both ways

  • Each instructor creates zero or more courses. Each course is created by exactly one instructor.
  • Each course is made up of one or more lessons. Each lesson belongs to exactly one course.
  • Each learner has zero or more enrolments. Each enrolment belongs to exactly one learner.
  • Each course has zero or more enrolments. Each enrolment is for exactly one course.

Nouns that are not entities

  • Progress percentage: Progress belongs to one learner in one course. It's an attribute of Enrolment.
  • Duration: Duration describes a lesson. It's an attribute of Lesson.
  • Platform: The platform is the system itself, so it isn't modelled.
  • Bio: Bio describes an instructor. It's an attribute of Instructor.