Entity Relationship Diagrams (ERD)

Introduction

• ERD stands for Entity Relationship Diagram.
• Used by database developers to design new data models for databases.
• ERD represents tables (entities) and their relationships.

Key Concepts

• Entities: These are tables in the database (e.g., Customers, Orders, Items).
• Relationships: The lines between entities, showing how they are related.

Example 1: Customers, Orders, and Items

• Customers Entity:
  • Contains customer information only (e.g., Customer ID, Customer Name, Customer Address).
• Orders Entity:
  • Contains order information.
  • Related to Customers with a one-to-many relationship.
• Items Entity:
  • Contains item information.
  • Related to Orders with a one-to-many relationship.
• Symbols:
  • Crow's Foot Notation: Represents the 'many' side of a relationship.
  • Pipe Symbol: Represents the 'one' side of a relationship.

Primary Keys and Foreign Keys

• Primary Key: Unique identifier for each record in a table.
• Foreign Key: Used to link two tables together.
  • Located on the 'many' side of a relationship.

Example 2: Students, Courses, and Instructors

• Students Entity:
  • Contains student information (e.g., Student ID).
• Courses Entity:
  • Contains course information (e.g., Course ID).
• Instructors Entity:
  • Contains instructor information.
• Relationships:
  • Students and Courses: Many-to-many relationship.
  • Courses and Instructors: One-to-many relationship.

Many-to-Many Relationship

• Requires an Intermediary Table to manage relationships (e.g., Enrollments Table).
• Enrollments Table:
  • Contains Student ID and Course ID as foreign keys.

Practical Application

• ERD helps understand how data is related before saving it in tables.
• Important for designing data models in projects or applications.

Real-World Implementation

• Intermediary Table: Used to resolve many-to-many relationships by linking IDs from different entities.
• Example of Enrollments Table data:
  • Each record links a student ID with a course ID.

Conclusion

• ERD diagrams are useful for understanding entity relationships in database design.
• Essential for practical database development but less detailed in certification exams.
• Encouraged to practice creating data models for real-world application.

• Note: For Oracle SQL Certified Associate Exam, focus on basic concepts as there are usually few questions on ERDs.