Introduction to Entity-Relationship Diagrams (ERDs)
Imagine you are tasked with building a digital system for a library. You have thousands of books, hundreds of members, and daily loans to track. If you just started typing data into a spreadsheet without a plan, you would quickly end up with a messy "data soup."
This is where Entity-Relationship Diagrams (ERDs) come in. An ERD is a visual map or blueprint of a database. It helps us visualize how different "things" (like Books and Members) are connected before we even touch a computer. In the "Data and Information" section of your syllabus, mastering ERDs is the secret to designing databases that are organized, efficient, and free of errors.
1. The Building Blocks of an ERD
Before we draw, we need to understand the three core components of any ERD:
A. Entities
An Entity is an object, person, place, or event about which data is stored. Think of an entity as a noun. In a school system, entities would be Student, Teacher, and Subject.
Pro-Tip: When drawing an ERD, entities are usually represented by rectangular boxes. Always name them using a singular noun (e.g., "Student," not "Students").
B. Attributes
An Attribute is a specific piece of information about an entity. Think of these as adjectives or details. For a Student entity, attributes might include StudentID, Name, and DateOfBirth.
Note: For more details on how these become "fields" in a table, refer to the chapter on Relational Databases, Keys, and Normalisation.
C. Relationships
A Relationship describes how two entities interact. Think of this as a verb. For example, a Student enrols in a Course. In a diagram, relationships are shown as lines connecting the entity boxes.
Key Takeaway: Entities are the "things," Attributes are the "details," and Relationships are the "connections."
2. Understanding Cardinality (The "How Many" Rule)
In your H2 Computing exams, the most important part of an ERD is showing the cardinality. This is just a fancy word for "how many" of one entity can relate to "how many" of another.
There are three main types of relationships you need to know:
One-to-One \( (1:1) \)
Each instance of Entity A relates to exactly one instance of Entity B, and vice versa.
Example: In a traditional system, a Citizen has exactly one Passport, and a Passport belongs to exactly one Citizen.
One-to-Many \( (1:M) \) or \( (1:N) \)
This is the most common relationship. One instance of Entity A can relate to many instances of Entity B, but each instance of Entity B relates to only one of Entity A.
Example: One Mother can have many Children, but each Child has only one biological Mother.
Many-to-Many \( (M:N) \)
Many instances of Entity A can relate to many instances of Entity B.
Example: One Student can take many Subjects, and one Subject can be taken by many Students.
Quick Review Box:
- \( 1:1 \): Rare, like a "soulmate" connection.
- \( 1:M \): The "Parent-Child" connection.
- \( M:N \): The "Group" connection.
3. Handling Many-to-Many (M:N) Relationships
Don't worry if this seems tricky at first! This is a common stumbling block for students. In a relational database, we cannot directly implement a Many-to-Many relationship between two tables. It causes massive data redundancy (repeated info).
To fix this, we break the \( M:N \) relationship into two \( 1:M \) relationships using a Junction Entity (also known as a Link Entity or Composite Entity).
Example:
Instead of: Student \( \infty \) --- \( \infty \) Subject
We create: Student \( 1 \) --- \( \infty \) Enrolment \( \infty \) --- \( 1 \) Subject
The Enrolment table "links" the two. It would contain the Primary Keys from both tables as Foreign Keys.
4. How to Draw an ERD: Step-by-Step
When you are given a case study in Paper 1 or Paper 2, follow these steps to draw your diagram:
- Identify the Entities: Look for the main "nouns" in the description.
- Identify the Relationships: Look for the "verbs" (e.g., "borrows," "assigned to," "contains").
- Determine Cardinality: For each relationship, ask two questions:
- "Can one A have many Bs?"
- "Can one B have many As?"
- Draw the Boxes and Lines: Use the standard notation (usually Crow's Foot notation in H2 Computing, where a "three-pronged foot" symbol represents the "Many" side).
- Check for \( M:N \): If you find any Many-to-Many relationships, create a junction entity to resolve them.
5. Common Mistakes to Avoid
1. Using Plural Names: Don't name an entity "Books." Name it "Book." The box represents the template for one record.
2. Putting Attributes in the Wrong Place: If a "Grade" depends on both a Student and a Subject, it belongs in the Junction Entity (Enrolment), not in the Student or Subject entity.
3. Misplacing the "Many" side: Always double-check which side is the "One" and which is the "Many." In a \( 1:M \) relationship between Department and Employee, the "Many" side is the Employee (one department has many employees).
Did you know?
ERDs are not just for computers! Large companies use these diagrams to map out their business logic and processes, ensuring that everyone from the CEO to the programmer understands how the business works.
6. Summary Checklist
Before you move on to the next chapter on SQL, make sure you can:
[ ] Define Entity, Attribute, and Relationship.
[ ] Identify and explain \( 1:1 \), \( 1:M \), and \( M:N \) relationships.
[ ] Draw an ERD based on a written scenario.
[ ] Resolve a Many-to-Many relationship using a junction entity.
Note: For more practice on how these diagrams are converted into actual tables, see the chapter on "Relational Databases, Keys and Normalisation."