Entity Relationship Diagrams

Overview

An Entity Relationship Diagram (ERD) is a visual tool used to model the structure of a relational database. ERDs illustrate how entities (tables) are connected, using different types of relationships to represent how data relates within a system. ERDs also help with database design by clearly defining primary keys, foreign keys, and secondary keys in each table, making it easier to manage data and establish meaningful connections between tables.

This note covers key terminology, relationship types, and how to construct ERDs, as well as how to identify linkages between tables in a database.

Keys in Databases

Primary Key:

• A unique identifier for each record in a table.
• Ensures that no two records in the table are identical.
• Typically a single field (e.g., Student_ID) but can sometimes be a composite key (a combination of fields).
• Example: In a Students table, Student_ID could serve as the primary key.

Foreign Key:

• A field in one table that creates a link to the primary key of another table, establishes a relationship between tables.
• Allows data from one table to relate to data in another, enabling complex data structures.
• Example: In an Enrollments table, Student_ID could be a foreign key linking to the Student_ID in the Students table.

Secondary Key:

• A field used for data retrieval purposes to allow quick access to records based on commonly searched attributes.
• Unlike primary keys, secondary keys are not necessarily unique.
• Example: In a Books table, Author could be a secondary key if the system frequently searches for books by author.

Types of Relationships in ERDs

One-to-One (1:1****):

• Each record in one table is linked to only one record in another table, and vice versa.
• Common in cases where data is split for security or organisational reasons.
• Example: A Person table with personal details and a Passport table where each person has only one passport.

One-to-Many (1

****):

• A single record in one table can be linked to multiple records in another table.
• The most common relationship type in relational databases.
• Example: A Teacher can teach multiple Classes, but each Class has only one Teacher.

Many-to-Many (M

****):

• Multiple records in one table can link to multiple records in another table.
• Implemented by creating a junction table (or associative table) with foreign keys linking back to both tables.
• Example: Students and Courses tables have an M
  relationship, as students can enrol in multiple courses, and courses can have multiple students. An Enrollments table would serve as the junction table.

Constructing an ERD

Identify Entities:

• Entities represent tables in the database (e.g., Student, Course, Teacher).
• Each entity should have a primary key to uniquely identify records.

Define Relationships:

• Determine how entities relate to each other (1:1, 1
  , or M
  ).
• Use foreign keys to establish links between tables for 1:1 and 1
  relationships.
• For M
  relationships, create a junction table with foreign keys pointing back to each entity.

Draw the ERD:

• Use rectangles to represent entities.
• Use lines to connect related entities, labelling each line with the relationship type (1:1, 1
  , M
  ).
• Indicate primary and foreign keys within each entity.

Example ERD

Examples of Relationships in ERDs

One-to-One (1:1) Example:

• Person and Passport tables where each person has only one passport.
• Relationship in ERD: A line connecting Person and Passport with a 1:1 label.

One-to-Many (1

) Example:

• Teacher and Classes tables where each teacher teaches multiple classes.
• Relationship in ERD: A line connecting Teacher to Classes with a 1
  label.

Many-to-Many (M

) Example:

• Students and Courses tables where students enrol in multiple courses.
• Implemented with an Enrollments table, where each enrollment links a student to a course through foreign keys.

Note Summary