Entity-Relationship Diagrams (ERDs)

Entity-Relationship Diagrams (ERDs)

Entity-Relationship Diagrams (ERDs) are a crucial tool in structured systems analysis and design. They help in visualising the data requirements of a system by illustrating the entities involved and the relationships between them. ERDs provide a clear picture of how data is connected, making it easier to design databases that meet user requirements.

Understanding Entities and Attributes

In ERDs, an entity represents a real-world object or concept that is significant to the system being designed. Each entity has specific characteristics known as attributes. For example, in a library system, the entities might include Book, Member, and Loan.

Example of Entities and Attributes

Consider the following entities for a library system:

• Book: Attributes can include ISBN, Title, Author, and Publication Year.
• Member: Attributes can include Member ID, Name, Email, and Phone Number.
• Loan: Attributes can include Loan ID, Loan Date, and Return Date.

Defining Relationships

Relationships in ERDs describe how entities interact with one another. There are three main types of relationships:

• One-to-One (1:1): Each instance of an entity relates to one instance of another entity. For example, each Member may have one Membership Card.
• One-to-Many (1:N): One instance of an entity relates to multiple instances of another entity. For example, a Member can borrow many Books, but each Book can be borrowed by only one Member at a time.
• Many-to-Many (M:N): Multiple instances of one entity relate to multiple instances of another entity. For example, a Book can be written by multiple Authors, and an Author can write multiple Books.

Example of Relationships

Using the library system example:

• Member to Loan: One-to-Many (1:N) - A member can have multiple loans.
• Book to Loan: One-to-Many (1:N) - A book can be loaned out multiple times.
• Book to Author: Many-to-Many (M:N) - A book can have multiple authors, and an author can write multiple books.

Creating an ERD

To create an ERD, follow these steps:

1. Identify the entities relevant to the system.
2. Determine the attributes for each entity.
3. Define the relationships between the entities.
4. Draw the diagram using appropriate symbols.

Example of Creating an ERD

Let’s create an ERD for the library system:

1. Identify Entities: Book, Member, Loan, Author.
2. Identify Attributes:

Book: ISBN, Title, Author, Publication Year
Member: Member ID, Name, Email, Phone Number
Loan: Loan ID, Loan Date, Return Date
Author: Author ID, Name

3. Define Relationships:

Member (1) --- (N) Loan
Book (1) --- (N) Loan
Book (M) --- (N) Author

4. Draw the Diagram: Use rectangles for entities, ovals for attributes, and lines for relationships.

The final ERD might look like this:

+----------------+       +----------------+       +----------------+
|     Member     |       |      Loan      |       |      Book      |
|----------------|       |----------------|       |----------------|
| Member ID      |       | Loan ID        |       | ISBN           |
| Name           |       | Loan Date      |       | Title          |
| Email          |       | Return Date    |       | Author         |
| Phone Number   |       +----------------+       | Publication Year|
+----------------+                               +----------------+
       | (1)                                      | (1)
       |                                           |
       |                                           |
       | (N)                                       | (N)
       |                                           |
+----------------+                               +----------------+
|     Author     |                               |     Loan      |
|----------------|                               |----------------|
| Author ID      |                               | Loan ID        |
| Name           |                               | Loan Date      |
+----------------+                               | Return Date    |
                                                   +----------------+

Cardinality and Participation Constraints

Cardinality defines the number of instances of one entity that can or must be associated with instances of another entity. Participation constraints indicate whether all or only some instances of an entity are involved in a relationship.

• Mandatory Participation: Every instance of an entity must be associated with at least one instance of another entity. For example, every Loan must be associated with a Member.
• Optional Participation: An instance of an entity may or may not be associated with an instance of another entity. For example, a Book may or may not be currently loaned out.

Example of Cardinality and Participation

In the library system:

• Member to Loan: Mandatory (1:N) - Each loan must have a member.
• Loan to Book: Mandatory (1:N) - Each loan must have a book.
• Book to Author: Optional (M:N) - A book may have no authors.

Normalisation and ERDs

Normalisation is the process of organising data to reduce redundancy and improve data integrity. It is often used alongside ERDs to ensure that the database design is efficient.

There are several normal forms, but the first three are the most commonly used:

• First Normal Form (1NF): Each table must have a primary key and contain atomic values (indivisible).
• Second Normal Form (2NF): Each non-key attribute must be fully functionally dependent on the primary key.
• Third Normal Form (3NF): No transitive dependencies should exist between non-key attributes.

Example of Normalisation

Consider a table that includes the following:

Loan ID | Member ID | Book ID | Member Name | Book Title
-------------------------------------------------------
1       | 101      | 1001    | John Smith   | C++ Programming
2       | 102      | 1002    | Jane Doe     | Data Structures

This table is not in 1NF because it contains non-atomic values (Member Name and Book Title). To normalise it:

1. Separate the data into two tables: Member and Loan.

Member Table:
Member ID | Member Name
------------------------
101       | John Smith
102       | Jane Doe

Loan Table:
Loan ID | Member ID | Book ID
---------------------------
1       | 101      | 1001
2       | 102      | 1002

Summary

• ERDs represent entities, attributes, and relationships.
• Use rectangles for entities and ovals for attributes.
• Define relationships as one-to-one, one-to-many, or many-to-many.
• Cardinality and participation constraints are important for accurate ERDs.
• Normalisation helps reduce redundancy in database design.

Check your understanding

1. What is an entity in an ERD?
2. Describe the difference between one-to-many and many-to-many relationships.
3. What does mandatory participation mean in the context of ERDs?
4. Explain the purpose of normalisation in database design.