A3.2.2 Constructing Entity Relationship Diagrams (ERDs) Notes

Constructing Entity Relationship Diagrams (ERDs)

The Significance of ERDs in Database Design

Entity Relationship Diagrams (ERDs) are visual representations of the structure of a database. They play a crucial role in:

1. Organizing Data: By clearly defining entities, attributes, and relationships.
2. Improving Communication: Providing a universal language for developers, designers, and stakeholders.
3. Preventing Errors: Allowing designers to identify and resolve issues before implementation.

Core Components of ERDs

1. Entities
   1. Definition: Objects or concepts that store data.
   2. Representation: Rectangles.
   3. Examples: Student, Course, Product.
2. Attributes
   1. Definition: Properties or characteristics of an entity.
   2. Representation: Ovals connected to their entity.
   3. Examples: A Student entity might have attributes like Name, ID, and Email.
3. Relationships
   1. Definition: Connections between entities.
   2. Representation: Diamonds or lines between entities.
   3. Examples: A Student "enrolls in" a Course.
4. Cardinality
   1. Definition: Describes the number of instances of one entity related to another.
   2. Types:
      1. One-to-One (1:1): Each instance of Entity A is related to one instance of Entity B.
      2. One-to-Many (1:N): Each instance of Entity A is related to multiple instances of Entity B.
      3. Many-to-Many (M:N): Multiple instances of Entity A are related to multiple instances of Entity B.
5. Modality (Optionality)
   1. Definition: Indicates whether a relationship is mandatory or optional.
   2. Examples:
      1. Mandatory: A Course must have at least one Student enrolled.
      2. Optional: A Student may or may not have a Scholarship.

Steps to Construct an ERD

1. Identify the Level of ERD
   1. Conceptual: Focuses on entities and relationships without attributes.
   2. Logical: Includes attributes, primary keys, and foreign keys.
   3. Physical: Adds data types and storage details.
2. Identify Entities and Attributes
   1. List all entities relevant to the system.
   2. Determine the attributes for each entity.
3. Identify Relationships
   1. Use verbs to describe how entities interact (e.g., Student "enrolls in" Course).
4. Determine Cardinality and Modality
   1. Specify the nature of each relationship (e.g., one-to-many, mandatory).
5. Draw the ERD
   1. Use standard symbols to represent entities, attributes, and relationships.

Example

Physical Schema Level ERD for a School Database

Relationships, Cardinality, and Modality

1. Relationships should be clearly defined with appropriate cardinality and modality.
2. Cardinality ensures that the database structure aligns with real-world constraints.

Practical Applications of ERDs

1. Database Design
   1. ERDs serve as a blueprint for creating relational databases.
2. Communication
   1. They provide a clear, visual representation of the database structure, facilitating communication among team members.
3. Error Prevention
   1. By visualizing relationships and constraints, ERDs help identify potential issues before implementation.