Understanding Relational Databases

Table of Contents

1. What is a Relational Database?
2. History and Evolution
3. Core Concepts of Relational Databases
   • Tables
   • Rows and Columns
   • Primary Keys
   • Foreign Keys
4. Relationships Between Tables
5. Advantages of Relational Databases
6. Limitations of Relational Databases
7. Example Schema: E-Commerce System
8. Summary

1. What is a Relational Database?

A Relational Database is a type of database that stores data in structured formats using rows and columns. Each piece of data is stored in a table, and these tables can relate to one another through keys, which is where the term “relational” comes from.

Relational databases are the most widely used type of database, particularly when consistency, accuracy, and the ability to structure data clearly are required.

2. History and Evolution

Relational databases are based on the Relational Model proposed by E. F. Codd in 1970 while working at IBM. His research paper “A Relational Model of Data for Large Shared Data Banks” laid the theoretical foundation for what we now know as relational databases.

The first commercial relational database was Oracle, released in 1979. Since then, many popular RDBMS (Relational Database Management Systems) have emerged, such as:

• MySQL
• PostgreSQL
• Microsoft SQL Server
• SQLite
• Oracle Database

3. Core Concepts of Relational Databases

Tables

A table (also called a relation) is a collection of related data entries and consists of columns and rows.

CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    Name VARCHAR(100),
    Email VARCHAR(100),
    City VARCHAR(50)
);

Rows and Columns

• Columns define the attributes or fields (e.g., Name, Email).
• Rows represent individual records (e.g., a customer named Alice).

Primary Keys

A Primary Key uniquely identifies each record in a table. It must be unique and non-null.

CustomerID INT PRIMARY KEY

Foreign Keys

A Foreign Key is a field (or collection of fields) in one table that refers to the Primary Key in another table.

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    OrderDate DATE,
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

4. Relationships Between Tables

There are three main types of relationships in relational databases:

• One-to-One: One record in a table is linked to one record in another table.
• One-to-Many: One record in a table can be linked to many records in another table.
• Many-to-Many: Many records in a table can be linked to many in another, often handled through junction tables.

Example: A customer can place multiple orders — this is a one-to-many relationship.

5. Advantages of Relational Databases

• Data Integrity: Relationships and constraints prevent invalid data.
• Flexibility: Tables can be added or modified with minimal disruption.
• SQL Standardization: Use of SQL makes RDBMS systems broadly compatible.
• Transactional Support: ACID compliance ensures reliable and consistent data operations.
• Security: Role-based access controls and encryption mechanisms.

6. Limitations of Relational Databases

• Scaling Issues: Not as horizontally scalable as NoSQL databases.
• Complexity: Joins and normalization can increase design complexity.
• Fixed Schema: Changing schema requires migration or downtime.
• Performance: For large-scale unstructured data (e.g., video, IoT), relational databases may underperform.

7. Example Schema: E-Commerce System

Let’s look at a simple example with three related tables:

-- Customers table
CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    Name VARCHAR(100),
    Email VARCHAR(100)
);

-- Products table
CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    ProductName VARCHAR(100),
    Price DECIMAL(10,2)
);

-- Orders table with foreign keys
CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    ProductID INT,
    OrderDate DATE,
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID),
    FOREIGN KEY (ProductID) REFERENCES Products(ProductID)
);

This design enables us to answer questions like:

• What did a customer order?
• When did they order it?
• What was the price of the product?

8. Summary

Relational databases are powerful tools that structure data efficiently and reliably through the use of tables, keys, and relationships. Understanding the relational model is crucial before diving into writing SQL queries, creating normalized schemas, or designing enterprise-level applications.

In the next module, we’ll explore SQL Basics: Syntax and Structure, where you’ll write your first SQL queries and understand how to interact with relational databases using SQL.