IT 9626 - Database and File Concepts

10. Entity Relationship Diagrams (ERD)

This section details the creation of Entity Relationship Diagrams (ERDs), a fundamental tool for database design. ERDs visually represent the entities, attributes, and relationships within a system. We will cover both conceptual and logical ERDs.

1. Conceptual ERD

A conceptual ERD provides a high-level overview of the data requirements of a system. It focuses on the key entities and their relationships without specifying details like data types or cardinalities.

1.1 Entities

Entities are real-world objects or concepts that we want to store information about. They are typically represented by rectangles.

• Examples: Customer, Product, Order

1.2 Attributes

Attributes are properties or characteristics of an entity. They are represented by ovals and are connected to the entity by lines.

• Examples (for Customer): CustomerID, Name, Address, Email

1.3 Relationships

Relationships represent how entities are related to each other. They are represented by diamonds and are connected to the entities involved.

• Examples: places, contains, is_a

1.4 Cardinality

Cardinality specifies the number of instances of one entity that can relate to another entity. Common cardinalities include:

• One-to-One (1:1)
• One-to-Many (1:N)
• Many-to-Many (M:N)

2. Logical ERD

A logical ERD is a more detailed representation of the data requirements. It specifies the data types of attributes and defines primary and foreign keys.

2.1 Entities and Attributes (as in Conceptual ERD)

Entities are still represented by rectangles, and attributes by ovals. However, logical ERDs include details about the attributes.

2.2 Relationships (with Cardinality and Participation)**

Relationships in a logical ERD are defined with specific cardinalities and participation constraints. Participation constraints indicate whether an entity instance must participate in a relationship.

• Total Participation: Every instance of the entity must participate in the relationship.
• Partial Participation: An instance of the entity may or may not participate in the relationship.

2.3 Primary and Foreign Keys

Primary keys uniquely identify instances of an entity. Foreign keys are attributes in one entity that reference the primary key of another entity, establishing a link between them.

3. Example: Online Shopping System

Consider an online shopping system. Here's an example of a conceptual ERD:

This conceptual diagram shows that a Customer places one or many Orders, and each Order contains one or many Products.

Here's a possible logical ERD for the same system:

This logical diagram specifies attributes like CustomerID (PK) for Customer, ProductID (PK) for Product, and a composite primary key of OrderID (PK) for Order. It also defines the relationships with cardinality (e.g., 1:N between Customer and Order).

4. Creating ERDs

The process of creating ERDs typically involves the following steps:

1. Identify the entities: Determine the key objects or concepts in the system.
2. Define the attributes: List the properties of each entity.
3. Identify the relationships: Determine how the entities are related to each other.
4. Specify cardinality and participation: Define the number of instances and whether participation is mandatory.
5. Identify primary and foreign keys: Determine the keys that uniquely identify entities and link them together.
6. Draw the diagram: Use a standard notation (like Chen's notation) to visually represent the ERD.

5. ERD Notation (Chen's Notation)

Chen's notation is a common standard for drawing ERDs. It uses rectangles for entities, ovals for attributes, and diamonds for relationships.

Symbol	Meaning
Rectangle	Entity
Oval	Attribute
Diamond	Relationship