Cardinality in Entity-Relationship Diagrams (ERDs)

ERD Notation

image from: Entity-Relationship Diagram Symbols and Notation | Lucidchart

In database design, an Entity-Relationship Diagram (ERD) is a tool that helps visualize how entities (tables) relate to each other. One important aspect of ERDs is the concept of cardinality, which specifies how many instances of an entity can be associated with instances of another entity.

Here’s a breakdown of the various cardinality notations used in ERDs, as shown in your image:

One-to-One (1:1)
- Notation: A straight line with a tick mark (|) at each end.
- Meaning: One instance of an entity is associated with one and only one instance of another entity.
- Example: Imagine a scenario where a person and a passport are entities. Each person can only have one passport, and each passport belongs to only one person. This is a strict one-to-one relationship.

One-to-Many (1)
- Notation: A straight line with a tick mark (|) at one end and a crow’s foot at the other.
- Meaning: One instance of an entity can be associated with many instances of another entity.
- Example: In a company, a department might have many employees, but each employee belongs to only one department. This is a common scenario in most database designs, known as a one-to-many relationship.

Another Example

Relationship between department and student:
- One department can have many students.
- The foreign key departmentId in the student table links students to departments.
- Notation: A tick mark | on the department side and a crow’s foot (fork) on the student side.

One-to-One (Mandatory)
- Notation: A straight line with two tick marks (||) at one end.
- Meaning: One instance of an entity must be related to exactly one instance of another entity.
- Example: Consider the relationship between a person and their national ID. Every person must have one national ID, and each national ID belongs to exactly one person. There is no optional relationship here; it's required.

Example:

In the example shown in the diagram, we have four entities: professors, admin, student, and authentication. The relationships depicted are One-to-One (Mandatory) relationships, meaning each entity (professor, admin, or student) must be associated with exactly one authentication record, and vice versa.

Professors: Each professor must have exactly one authentication entry, which includes information such as their email, role, username, and password. This ensures that every professor has an authentication account for login purposes.
Admin: Similar to professors, each admin has one authentication entry tied to their account, which is mandatory for system access and managing tasks.
Student: Every student must have exactly one authentication entry, ensuring that they can securely log in and access the system.

In this case, the use of the || notation (two tick marks) indicates that the relationship between these entities and the authentication entity is required. Thus, no professor, admin, or student can exist in the system without having an associated authentication record.

This is an example of enforcing strict identity and access management where every user (professor, admin, or student) is required to have an authentication record.

Zero or One (Optional 1:1)
- Notation: A line with a circle (O) at one end and a tick mark (|) at the other.
- Meaning: One instance of an entity can either have no related instance or one instance of another entity.
- Example: A driver's license and a person. Not every person has a driver's license, but if they do, it’s only one license. This is known as an optional one-to-one relationship.

Example

Each student may have a corresponding requirement record, but it's not mandatory (indicated by the circle).
The requirement entity tracks documents like the student's last school name and birth certificate.
The foreign key studentId in the requirement table links back to the student entity.

This design allows for the possibility that not all students will have a requirement record.

One or Many (1 or More)

Notation: A straight line with a tick mark (|) at one end and a crow’s foot with a horizontal line across it at the other.
- Meaning: One instance of an entity is related to at least one instance, but possibly many, of another entity.
- Example: A company and its employees. A company must have at least one employee, but can employ many. This type of relationship ensures that there is never a situation with zero related entities.
- ANOTHER EXAMPLE
- One enrollment must be associated with at least one or many students enrolled. This could be represented as a One-to-One or Many relationship because each enrollment (for a specific academic year and semester) needs at least one student to be valid, but it could also have many students.

Zero or Many (Optional Many 0)
- Notation: A line with a circle (O) at one end and a crow’s foot at the other.
- Meaning: An entity can have zero or many instances of another entity.
- Example: A customer and their orders. A customer might not place any orders, or they could place several. This is a very flexible cardinality often used to model real-world relationships where interactions may or may not occur.
- Example 2

The relationship between student and attendance is represented as Zero or Many.

The line with a circle ○ on the attendance side indicates that a student may or may not have any attendance records.
The crow's foot (fork) symbol on the attendance side indicates that a student can have many attendance records.

SUMMARY

One-to-One (1:1): A one-to-one relationship where each instance of Entity A is related to one instance of Entity B. Example: A person has one passport.
One-to-Many (1): A one-to-many relationship where each instance of Entity A is related to multiple instances of Entity B. Example: A department has many employees.
Zero or One (0 or 1): An optional one-to-one relationship. Example: A person may or may not have a driver’s license.
One or Many (1 or More): A mandatory relationship where at least one instance of Entity B must exist. Example: A company must have at least one employee.
Zero or Many (0): An optional many relationship. Example: A customer can place zero or many orders.

References

Database Star - Guide to ER Diagrams(Database Star).

Gleek.io - ER Model Cardinality Examples(Gleek).

Lucidchart - Entity Relationship Diagram Symbols(Lucidchart).

Wikipedia - Cardinality in Data Modeling(Wikipedia).

Cardinality in Entity-Relationship Diagrams (ERDs)

Comments

MySql Database

Mysql Database User Role

More from this blog

Set Up Python fast API

Set Up React Typescript with Vite | Thirdy Gayares

Angular API

Yarn vs Npm

How to Create MYSQL Table with Foreign Key Constraint and add sample data

SUMMARY

References

Command Palette

Comments

MySql Database

Mysql Database User Role

More from this blog

SUMMARY

References