Entity Relationship Diagrams
Introduction to Entity Relationship Diagrams.
Entity-Relationship Diagrams (ERDs) provide a visual representation of the database structure.
They facilitate communication among stakeholders, guide the database design process, and serve as documentation for the database schema.
Types of Entity Relationship Diagrams
There are three types of Entity Relationship Diagrams - Conceptual, Logical & Physical.
As the project progresses and requirements became more defined the ERDs needed will move from Conceptual through to Physical.
Which ERD type is closest to the actual database implementation?
Conceptual ERD (CERD)
Focuses on high-level relationships between entities.
Used in the initial stages of database design to capture essential entities and their relationships.
Helps stakeholders understand the scope and requirements of the database system.
Used by: Business analysts, stakeholders, project managers
Key Questions
- What entities are going to be in the database?
- How do they relate to each other?
Example Conceptual Entity Relationship Diagram
Logical Entity Relationship Diagrams
Used to translate the conceptual model into a relational database schema.
Specifies attributes for each entity, defines cardinality (one to one, one to many) and participation(optional vs required) constraints.
Provides a detailed view of the database schema, including primary and foreign keys.
Used by: Database designers, data architects, developers
Key Questions
- What attributes do we need to store about each entity?
- Which of these attributes interact with the other entities?
What is an attribute in an Entity Relationship Diagram?
Example Logical Entity Relationship Diagram
Physical Entity Relationship Diagrams
Represents the actual implementation of the database on a specific DBMS in order to to build, optimize, and maintain the physical database schema.
Includes details such as data types, indexes, storage options, and constraints tailored to the chosen DBMS.
Used by: database administrators, system administrators, developers.
Key questions
- What hardware/software are we going to build this?
- What data needs optimising?
- What constraints?
- Is the referential integrity enforced correctly?
Example Physical Entity Relationship Diagram
What is cardinality in an Entity Relationship Diagram?
Types of Entity Relationship Diagram
- Introduction to Entity Relationship Diagrams.
- Types of Entity Relationship Diagrams
- Conceptual ERD (CERD)
- Example Conceptual Entity Relationship Diagram
- Logical Entity Relationship Diagrams
- Example Logical Entity Relationship Diagram
- Physical Entity Relationship Diagrams
- Example Physical Entity Relationship Diagram