Courses IB Computer Science (Standard) Databases & SQL Entity Relationship Diagrams

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.

{"keyword": "Entity Relationship Diagram (ERD)", "definition": "A visual representation of the logical structure of a database, showing the relationships between entities"}

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?

{"answers": {"A": "Logical ERD", "B": "Conceptual ERD", "C": "Relational ERD", "D": "Physical ERD"}, "question_text": "Which ERD type is closest to the actual database implementation?", "correct_answer": "D"}

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

An entity in an ER diagram is represented by a [[rectangle]].

{"cloze_text": "An entity in an ER diagram is represented by a [[rectangle]]."}

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?

{"answers": {"A": "A property or characteristic of an entity", "B": "A database table", "C": "A line connecting two entities", "D": "A type of relationship"}, "question_text": "What is an attribute in an Entity Relationship Diagram?", "correct_answer": "A"}

Example Logical Entity Relationship Diagram

A Logical ERD focuses on the relationships between entities using [[foreign keys]] to connect them.

{"cloze_text": "A Logical ERD focuses on the relationships between entities using [[foreign keys]] to connect them."}

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?

In a Physical ERD, the actual data types and lengths of attributes are specified according to the chosen [[database management system]].

{"cloze_text": "In a Physical ERD, the actual data types and lengths of attributes are specified according to the chosen [[database management system]]."}

Example Physical Entity Relationship Diagram

What is cardinality in an Entity Relationship Diagram?

{"answers": {"A": "The number of occurrences of one entity that are associated with the occurrence of another entity", "B": "The type of relationship between entities", "C": "The color used to draw the diagram", "D": "The font size of the entities"}, "question_text": "What is cardinality in an Entity Relationship Diagram?", "correct_answer": "A"}