Converting ER diagrams to relational schema in DBMS Theory - Full Explanation

Introduction

When designing a database, we often start with a picture showing how things relate to each other. But computers need a clear table format to store data. Converting these pictures into tables is the key step to make the design usable.

Explanation

Entities to Tables

Each entity in the diagram becomes a table in the database. The attributes of the entity turn into columns of that table. One attribute is chosen as the primary key to uniquely identify each row.

Entities become tables with attributes as columns and a primary key.

Relationships to Foreign Keys

Relationships between entities are represented by adding foreign keys. A foreign key is a column in one table that points to the primary key of another table. This shows how rows in one table connect to rows in another.

Relationships become foreign keys linking tables.

Handling One-to-One Relationships

For one-to-one relationships, you can add a foreign key to either table. This key references the other table's primary key, ensuring each row matches one row in the related table.

One-to-one relationships use a foreign key in one table referencing the other.

Handling One-to-Many Relationships

In one-to-many relationships, the table on the 'many' side gets a foreign key column. This column points to the primary key of the 'one' side table, linking many rows back to one row.

The 'many' side table holds the foreign key to the 'one' side.

Handling Many-to-Many Relationships

Many-to-many relationships need a new table called a junction or associative table. This table has foreign keys pointing to the primary keys of both related tables. It breaks down the many-to-many link into two one-to-many links.

Many-to-many relationships become a new table with foreign keys to both tables.

Mapping Weak Entities

Weak entities depend on another entity for identification. Their table includes a foreign key to the owner entity's primary key, combined with their own partial key to form a composite primary key.

Weak entities use composite keys combining their own and owner’s keys.

Real World Analogy

Imagine organizing a library. Books are entities, each with details like title and author. Relationships show which books belong to which categories. To keep track, you create shelves (tables) for books and categories, and labels (foreign keys) to link them.

Entities to Tables → Books placed on shelves representing tables

Relationships to Foreign Keys → Labels on shelves showing which books belong to which categories

Handling One-to-One Relationships → A special shelf with one book linked directly to one category label

Handling One-to-Many Relationships → One category label linked to many books on the shelf

Handling Many-to-Many Relationships → A separate shelf holding cards that link books and categories together

Mapping Weak Entities → Books that need a special tag combining their own ID and the category’s ID

Diagram

Diagram

┌─────────────┐      ┌─────────────┐      ┌───────────────┐
│   Entity A  │      │   Entity B  │      │ Associative   │
│ (Table A)   │      │ (Table B)   │      │ Table (for    │
│ PK: A_id    │      │ PK: B_id    │      │ many-to-many) │
└─────┬───────┘      └─────┬───────┘      │ FK: A_id      │
      │                     │              │ FK: B_id      │
      │ One-to-many          │              └───────────────┘
      │ relationship         │
      ▼                     ▼
┌─────────────┐      ┌─────────────┐
│ Table B     │      │ Table A     │
│ FK: A_id    │◄─────┤ PK: A_id    │
└─────────────┘      └─────────────┘

Diagram showing entities as tables and how one-to-many and many-to-many relationships convert to foreign keys and associative tables.

Key Facts

Entity → A real-world object or concept represented as a table in a database.

Primary Key → A unique identifier for each row in a table.

Foreign Key → A column that links one table to the primary key of another table.

One-to-Many Relationship → A relationship where one row in a table relates to many rows in another.

Many-to-Many Relationship → A relationship where many rows in one table relate to many rows in another, requiring a junction table.

Weak Entity → An entity that depends on another entity for its identification.

Code Example

DBMS Theory

CREATE TABLE Department (
  DeptID INT PRIMARY KEY,
  DeptName VARCHAR(100)
);

CREATE TABLE Employee (
  EmpID INT PRIMARY KEY,
  EmpName VARCHAR(100),
  DeptID INT,
  FOREIGN KEY (DeptID) REFERENCES Department(DeptID)
);

CREATE TABLE Project (
  ProjectID INT PRIMARY KEY,
  ProjectName VARCHAR(100)
);

CREATE TABLE EmployeeProject (
  EmpID INT,
  ProjectID INT,
  PRIMARY KEY (EmpID, ProjectID),
  FOREIGN KEY (EmpID) REFERENCES Employee(EmpID),
  FOREIGN KEY (ProjectID) REFERENCES Project(ProjectID)
);

Common Confusions

Thinking many-to-many relationships can be represented by just foreign keys in the two tables.

Thinking many-to-many relationships can be represented by just foreign keys in the two tables. Many-to-many relationships require a separate junction table with foreign keys to both related tables to properly represent the connections.

Believing that the primary key of an entity can be any attribute without uniqueness.

Believing that the primary key of an entity can be any attribute without uniqueness. The primary key must uniquely identify each row; attributes that can repeat cannot serve as primary keys.

Assuming weak entities can have independent primary keys.

Assuming weak entities can have independent primary keys. Weak entities use a composite key combining their partial key with the owner entity’s primary key to ensure uniqueness.

Summary

Entities in ER diagrams become tables with attributes as columns and a primary key.

Relationships are converted into foreign keys or new junction tables depending on their type.

Weak entities require composite keys combining their own and owner entity keys.