Entity-Relationship (ER) Diagrams | Visual Database Planning

# CHAPTER 6

ER Diagrams Explained #

1. Introduction #

2. Learning Objectives #

• Understand the purpose of an ER Diagram.

• Identify the standard symbols (Chen Notation).

• Understand modern "Crow's Foot" Notation used in software tools.

• Map Cardinality visually.

• Draw a complete ER Diagram from business requirements.

3. The Classic Notation (Chen Notation) #

• Rectangle: Represents an Entity (Table).

• Double Rectangle: Represents a Weak Entity.

• Oval: Represents an Attribute (Column).

• Underlined Oval: Represents a Primary Key Attribute.

• Diamond: Represents the Relationship (Verb) between Entities.

*Visualizing a Rule:* [Customer Rectangle] ---<Places Diamond>--- [Order Rectangle].

4. Modern Software Notation (Crow's Foot Notation) #

The Lines determine the Cardinality:

• One (1): A straight line with a single perpendicular dash (|).

• Many (N): A line that splits into three prongs at the end (looks like a crow's foot |<).

• Zero or One: A line with an O and a |.

*Visualizing a 1:N Rule:* [Users Box] ----|----------|< [Orders Box] *(Read as: Exactly ONE user can have MANY orders).*

5. Why ERDs are Mandatory #

1. 1. Developer Blueprint: The backend engineers look at the ERD to know exactly what SQL JOIN queries they need to write.

1. 2. Stakeholder Verification: You can show an ERD to a non-technical CEO to verify that the Business Rules (Chapter 4) are correct before writing code.

1. 3. Documentation: When a new developer joins the company, the ERD is the map they use to understand the massive 500-table database.

6. Mini Project: Draw a Social Media ER Diagram #

Entities identified: User, Tweet. Attributes identified:

• User: userid (PK), handle, email

• Tweet: tweetid (PK), content, timestamp

The Whiteboard Sketch (Crow's Foot):

+----------------+          +----------------+
|      USER      |          |     TWEET      |
+----------------+          +----------------+
| PK: user_id    |---|----|<| PK: tweet_id   |
|     handle     |          | FK: user_id    |
|     email      |          |     content    |
+----------------+          +----------------+

*(Notice how clearly the userid Foreign Key in the Tweet table connects back to the User!)*

• Overcomplicating the Conceptual ERD: A database might have 50 specific columns (like updatedat, is_deleted). Do not put all 50 columns on a high-level conceptual ER diagram; it becomes an unreadable mess. Only include the Primary Keys, Foreign Keys, and the 2 or 3 most important descriptive attributes. Save the exhaustive column list for the Data Dictionary.

8. Best Practices #

• Left-to-Right Hierarchy: When drawing an ERD, place the most important "Strong" Entities (like Users or Companies) on the left side, and let the lines flow rightwards toward the "Weak" Entities (like Orders, Logs, or Comments). This makes the diagram naturally readable.

9. Exercises #

1. 1. In modern Crow's Foot notation, what does a line ending in three diverging prongs symbolize?

1. 2. Why is drawing an ER Diagram considered a mandatory step before writing SQL CREATE TABLE statements?

10. Database Design Challenges #

11. MCQ Quiz with Answers #

12. Interview Questions #

• Q: Explain the transition from a Conceptual ER Diagram (using Chen Notation with Diamonds) to a Logical/Physical ER Diagram (using Crow's Foot Notation). Why do software tools prefer the latter?

• Q: You are handed an ER Diagram where the Orders table has a Crow's Foot pointing towards it from the Customers table. Explain exactly what this line means for the underlying Foreign Key SQL architecture.

13. FAQs #

14. Summary #

15. Next Chapter Recommendation #