Introduction to ERDs

An Entity-Relationship Diagram (ERD) is a visual representation of the entities, attributes, and relationships within a database.

ERDs are used to model the logical structure of databases and help in designing and understanding the database .

Key Terms

Entities

An entity is a real-world object or concept that can have data stored about it.

• Entities are represented as rectangles in ERDs.

• Examples include Customer, Order, and Product.

Attributes

Attributes are properties or characteristics of an entity.

• They are represented as ovals connected to their respective entities.

• Examples include CustomerName, OrderDate, and ProductPrice.

Relationships

Relationships describe how entities interact with each other.

• They are represented as diamonds or lines connecting entities.

• Relationships can be one-to-one, one-to-many, or many-to-many.

Types of Relationships

Relationships

One-to-One (1:1)

In a one-to-one relationship, each instance of Entity A is associated with one instance of Entity B, and vice versa.

For example, each Person has one Passport, and each Passport is assigned to one Person.

Person | -- | Passport

One-to-Many (1:M)

In a one-to-many relationship, each instance of Entity A can be associated with multiple instances of Entity B, but each instance of Entity B is associated with only one instance of Entity A.

For example, one Customer can place many Orders, but each Order is placed by one Customer.

Customer | -- < Orders

Many-to-Many (M:N)

In a many-to-many relationship, each instance of Entity A can be associated with multiple instances of Entity B, and vice versa.

For example, Students can enroll in many Courses, and each Course can have many Students.

Students > -- < Courses

Three Levels of Schema / Types of ERD

1. Conceptual (High Level, General Purpose)

2. Logical (Focuses more on the structure of data)

3. Physical (Most detail often the schematics to an actual database)

Conceptual (Business)

• 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.

Logical (Designers)

• Used to translate the conceptual model into a relational database schema.

• Specifies attributes for each entity, defines cardinality/relationships (one to one, one to many) and participation(optional vs required) attributes.

• Provides a detailed view of the database schema, including primary and foreign keys

Physical (Developer)

• 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.