Construct ERDs

• An ERD (Entity Relationship Diagram) shows:
  • entities
  • attributes
  • relationships
  • cardinality
  • modality
• ERDs are used to design a relational database before building the tables.
• Steps to construct an ERD:
  1. Identify the level required:
     • conceptual
     • logical
     • physical
  2. Identify the entities
  3. Identify the attributes for each entity
  4. Identify the relationships between entities
  5. Identify the cardinality:
     • one-to-one
     • one-to-many
     • many-to-many
  6. Identify the modality:
     • optional
     • mandatory
• Example:
  • Student enrolls in Class
  • one student can enroll in many classes
  • one class can contain many students
  • this is a many-to-many relationship
  • in a relational database, this usually needs a linking table such as Enrollment

Worked ERD example

• Scenario:
  • a school stores students and classes
  • each student can join many classes
  • each class can contain many students
• Entities:
  • Student
    • StudentID
    • Name
  • Class
    • ClassID
    • ClassName
  • Enrollment
    • StudentID
    • ClassID
• Relationships:
  • one Student can appear in many Enrollment records
  • one Class can appear in many Enrollment records
  • Enrollment resolves the many-to-many relationship between Student and Class
• Cardinality:
  • Student 1 : many Enrollment
  • Class 1 : many Enrollment
• Modality:
  • a student may exist before enrolling in a class, so participation in Enrollment can be optional
  • a class may exist before any student joins it, so participation can also be optional

A visual trace of the relationships described in this objective.

• Exam-style text description of the ERD:
  • Student(StudentID, Name)
  • Class(ClassID, ClassName)
  • Enrollment(StudentID, ClassID)
  • one Student can appear in many Enrollment records
  • one Class can appear in many Enrollment records
  • therefore Student and Class have a many-to-many relationship resolved by Enrollment