Use an entity-relationship (E-R) diagram to document a database design

Resources | Subject Notes | Computer Science | Lesson Plan

Database Concepts - E-R Diagrams

Database Concepts - 8.1 Use an entity-relationship (E-R) diagram to document a database design

This section explores the fundamental concepts of database design and how Entity-Relationship (E-R) diagrams are used to visually represent and document these designs. E-R diagrams are crucial for planning and creating efficient and well-structured databases.

What is a Database?

A database is an organized collection of data, typically stored and accessed electronically. Databases are used to store information persistently and allow for efficient retrieval, modification, and management of that information. Examples include customer databases, product databases, and library databases.

Key Database Concepts

Entities

An entity is a real-world object, person, place, thing, or concept that we want to store information about in a database. Entities are represented by rectangles in an E-R diagram.

  • Examples: Customer, Product, Order, Employee.

Attributes

An attribute is a characteristic or property of an entity. Attributes describe the entity. They are represented by ovals connected to the entity rectangle.

  • Examples:
    • Customer: CustomerID, Name, Address, Email, Phone Number
    • Product: ProductID, ProductName, Price, Description
    • Order: OrderID, OrderDate, TotalAmount

Relationships

A relationship represents an association between two or more entities. Relationships are represented by diamonds in an E-R diagram. Relationships have a cardinality that defines how many instances of one entity can be related to instances of another entity.

  • Examples:
    • A Customer places an Order.
    • An Employee works on a Project.

Cardinality

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

  • One-to-one (1:1): One instance of entity A is related to at most one instance of entity B, and vice versa.
  • One-to-many (1:N): One instance of entity A is related to many instances of entity B, but each instance of entity B is related to only one instance of entity A.
  • Many-to-many (M:N): Many instances of entity A can be related to many instances of entity B.

E-R Diagrams

An E-R diagram is a visual representation of the entities, attributes, and relationships in a database. It provides a high-level overview of the database structure.

E-R diagrams use specific symbols to represent different components:

  • Entities: Rectangles
  • Attributes: Ovals
  • Relationships: Diamonds
  • Cardinality: Notation on the relationship lines (e.g., 1, N, M)

Creating an E-R Diagram

The process of creating an E-R diagram involves identifying the entities, their attributes, and the relationships between them. This is often done by analyzing the requirements of the database.

  1. Identify the entities: Determine the key objects or concepts that need to be represented in the database.
  2. Define the attributes: List the characteristics of each entity.
  3. Identify the relationships: Determine how the entities are related to each other.
  4. Specify the cardinality: Define the number of instances of each entity that can participate in each relationship.
  5. Draw the diagram: Use the appropriate symbols to represent the entities, attributes, and relationships.

Example E-R Diagram: Library Database

Suggested diagram: A simple E-R diagram for a library database showing Entities: Book, Author, Member. Relationships: Book is written by Author, Member borrows Book.

Consider a library database. The entities might include Book, Author, and Member. A Book has attributes like BookID, Title, and ISBN. An Author has attributes like AuthorID, Name, and Nationality. A Member has attributes like MemberID, Name, and Address. Relationships would show that a Book is written by an Author (one-to-many: one author can write many books) and a Member borrows a Book (many-to-many: one member can borrow many books, and one book can be borrowed by many members).

From E-R Diagram to Database Schema

Once an E-R diagram is created, it can be used to design the database schema. This involves translating the entities into tables, the attributes into columns, and the relationships into foreign keys. The E-R diagram provides a clear blueprint for the database structure.