Section 3: Entity Relationship Diagrams

§1 What is an Entity Relationship Diagram (ERD)? #

An Entity Relationship Diagram (ERD) is a flowchart that illustrates how “entities” such as people, objects, or concepts relate to each other within a system. In software engineering, ERDs are a fundamental tool used to model and design relational databases. They provide a visual blueprint of the database’s structure, showing its tables, the columns within those tables, and the relationships connecting them.

Example Bookstore ERD:

erDiagram AUTHORS { int author_id PK string first_name string last_name string bio datetime created_at } PUBLISHERS { int publisher_id PK string name string address string phone datetime created_at } BOOKS { int book_id PK string isbn UK string title decimal price int stock_qty int publisher_id FK datetime published_date datetime created_at } BOOK_AUTHORS { int book_id FK int author_id FK } CUSTOMERS { int customer_id PK string first_name string last_name string email UK string phone string shipping_address datetime created_at } ORDERS { int order_id PK int customer_id FK datetime order_date decimal total_amount string status } ORDER_ITEMS { int order_item_id PK int order_id FK int book_id FK int quantity decimal unit_price } %% Relationships AUTHORS ||--o{ BOOK_AUTHORS : "writes" BOOKS ||--o{ BOOK_AUTHORS : "authored_by" PUBLISHERS ||--o{ BOOKS : "publishes" CUSTOMERS ||--o{ ORDERS : "places" ORDERS ||--o{ ORDER_ITEMS : "contains" BOOKS ||--o{ ORDER_ITEMS : "listed_in"

§2 Creating Entity Relationship Diagrams #

ERD’s can be created in a variety of ways, the diagrams on this page are created using mermaid.js, though several tools could be used:

§3 Core Components of an ERD #

Every ERD is built from three core components: entities, attributes, and relationships.

§3.1 Entities #

An entity represents a real-world object or concept that can be distinctly identified. In database terms, an entity corresponds to a table. For example, in a university database, Student, Course, and Professor would all be entities.

§3.2 Attributes #

An attribute is a property or characteristic of an entity. In a database, attributes are the columns of a table. For each attribute, we define a data type (e.g., integer, text, date).

Key attributes are special:

Primary Key (PK): An attribute that uniquely identifies each record in an entity. A StudentID would be a primary key for the Student entity.
Foreign Key (FK): An attribute in one entity that refers to the primary key of another entity. It’s used to link tables together.

Example: A Users Entity Here’s a simple Student entity (or table) with a primary key and a few other attributes.

erDiagram STUDENT { int student_id PK string name string last_name }

§3.3 Relationships #

A relationship defines how two or more entities are connected. For example, a Student “enrolls in” a Course. Relationships are crucial for showing how the database structure works as a whole. The “cardinality” of a relationship defines the number of instances of one entity that can be associated with instances of another.

§4 Cardinality: Defining Relationships #

Cardinality specifies the nature of the relationship between two entities. The most common types are one-to-one, one-to-many, and many-to-many.

§4.1 One-to-One (1:1) #

In a one-to-one relationship, one record in an entity is associated with exactly one record in another entity. For example, a User might have exactly one UserProfile.

The ||--|| notation between the two entities signifies that the relationship is “one and only one” on both sides.

erDiagram User { int UserID PK varchar Email } UserProfile { int UserProfileID PK int UserID FK "Links to User.UserID" varchar FullName date DateOfBirth } User ||--|| UserProfile : "has one"

§4.2 One-to-Many (1:N) #

In a one-to-many relationship, one record in an entity can be associated with multiple records in another entity. This is the most common relationship type. For example, one Author can write many Books.

The Author ||--|{ Book notation indicates that an Author has “one or many” Books, and a Book is written by “one and only one” Author.

|| means “one and only one”.
o{ means “zero or many”.
|{ means “one or many”.

erDiagram Author { int AuthorID PK varchar Name } Book { int BookID PK varchar Title int AuthorID FK "Links to Author.AuthorID" } Author ||--|{ Book : "writes"

§4.3 Many-to-Many (N:M) #

In a many-to-many relationship, multiple records in one entity can be associated with multiple records in another. For example, a Student can enroll in many Courses, and a Course can have many Students.

This type of relationship cannot be implemented directly in a relational database. It requires a third “junction” or “linking” table that breaks the many-to-many relationship down into two one-to-many relationships.

erDiagram Student { int StudentID PK varchar Name } Course { int CourseID PK varchar Title } Enrollment { int StudentID FK "Links to Student.StudentID" int CourseID FK "Links to Course.CourseID" varchar Semester } Student ||--o{ Enrollment : "enrolls in" Course ||--o{ Enrollment : "is taken by"

§5 Complete Example: University Database #

Let’s combine these concepts into a more complete diagram for a university database, showing how departments, professors, courses, and students are all interconnected.

erDiagram DEPARTMENT { varchar(10) DeptCode PK varchar(100) DeptName } PROFESSOR { int ProfessorID PK varchar(100) Name varchar(10) DeptCode FK } COURSE { int CourseID PK varchar(100) Title varchar(10) DeptCode FK int ProfessorID FK } STUDENT { int StudentID PK varchar(100) Name varchar(10) DeptCode FK } ENROLLMENT { int StudentID FK int CourseID FK varchar(10) Semester varchar(2) Grade } DEPARTMENT ||--o{ PROFESSOR : "employs" DEPARTMENT ||--o{ COURSE : "offers" DEPARTMENT ||--o{ STUDENT : "has major" PROFESSOR ||--o{ COURSE : "teaches" STUDENT ||--o{ ENROLLMENT : "enrolls in" COURSE ||--o{ ENROLLMENT : "is taken by"

§6 More Resources #

If you would like to see more examples on ERD creation, here are some additional resources:

§7 Questions #

Always provide at least a brief explanations for your answers.
Please number each response to directly correspond with the question. Provide clear, concise answers that fully address each question without unnecessary elaboration or essay-style writing.

What is the purpose of an Entity Relationship Diagram (ERD)?

What are the three core components of an ERD?

In the bookstore ERD, identify all foreign keys and explain which primary keys they reference.

§7.1 Diagram Creation #

Create a simple ERD for a Customer and Order relationship, where a customer can have many orders, but each order belongs to only one customer. (you do not need to specify table columns, just the overall table relationships)

A music streaming app needs to store playlists, songs, and artists. Each playlist can contain many songs, and each song can appear in multiple playlists. Additionally, each song is performed by one or more artists, and each artist can perform many songs. Design a normalized ERD for this scenario. (you do not need to specify table columns, just the overall table relationships)

Create an ERD for a Movie information database inspired by imdb.com containing the following tables: Movies, Shows, People, Studios, Genres, Ratings. Mention if your ERD is normalized or not. (you do not need to specify table columns, just the overall table relationships)
This will have several overlapping relationships, dont worry too much about the layout.