Relational Databases

A relational database is a type of database that stores data in a structured format using rows and columns, often organized into tables.

These databases rely on the relational model proposed by Edgar F. Codd in 1970, where the data is logically stored in relations (tables) and accessed using a standardized query language, most commonly SQL (Structured Query Language).

 A table consists of rows (records) and columns (attributes).

Each table should have a unique name within the database.

Example Table

Each row in a table represents a unique record.

For example, in the employee table, each row represents a single employee.

Columns represent data categories. Each column in a table holds values of a specific type (e.g., integers, strings, dates).

A primary key is a column (or a set of columns) that uniquely identifies each row in a table. No two rows can have the same primary key value.

Example: In the employee table, "Employee_ID" could be the primary key, as each employee has a unique ID.

A foreign key is a column (or a set of columns) in one table that refers to the primary key of another table. It establishes a relationship between the two tables.

Projects Table Example

In this case, the Employee_ID in the Projects table will act as a foreign key that references the Employee_ID in the Employees table. 

One-to-One

One row in a table is related to one row in another table.

One-to-Many

One row in a table is related to multiple rows in another table.

Many-to-Many

Multiple rows in one table are related to multiple rows in another table, usually implemented with a junction table.

A One-to-One relationship occurs when each record in one table is linked to exactly one record in another table, and vice versa. This means:

• No record in the first table can link to more than one record in the second table.
• No record in the second table can link to more than one record in the first table.

Key Features

Unique pairing: Every row in Table A corresponds to one and only one row in Table B.

Primary keys: Usually, the primary key of one table is used as a foreign key in the other table to enforce this relationship.

One-to-One relationships are less common than One-to-Many relationships, but they are useful when:

• Splitting a table for security reasons (e.g., separating sensitive information like passwords).
• Storing optional information (e.g., a table of employees with an optional table of company cars).

A One-to-Many relationship occurs when a single record in one table can be associated with multiple records in another table, but each record in the second table is linked to only one record in the first table.

Key Features

Single source, multiple targets: One row in Table A can link to many rows in Table B.

Foreign key: The primary key of the “one” table is usually added as a foreign key in the “many” table to maintain the relationship.

Common in practice: One-to-Many relationships are extremely common in databases because they represent hierarchical or grouped data.

A Many-to-Many relationship occurs when multiple records in one table can be associated with multiple records in another table, and vice versa.

Key Features

Multiple connections: Each row in Table A can link to many rows in Table B, and each row in Table B can link to many rows in Table A.

Junction (or bridge) table: Most database systems cannot directly implement a Many-to-Many relationship, so a third table is created to store the relationships. This table usually contains foreign keys referencing the primary keys of the two main tables.

Common in practice: Many-to-Many relationships are common for things like students enrolled in courses, products in orders, or authors writing books.

A junction table (also known as a bridge table or associative entity) is a database table used to implement many-to-many relationships between two other tables.

Since relational databases don't directly support many-to-many relationships, the junction table helps by storing pairs of foreign keys referencing the primary keys of the related tables.

The process of organizing data to reduce redundancy and dependency by splitting large tables into smaller, related tables.

The aim is to eliminate data anomalies (like insertion, update, and deletion anomalies) and make the database more efficient.

The process is normally split into First, Second and Third normal form (1NF, 2NF, 3NF).

Data Integrity

Constraints like primary keys, foreign keys, and unique constraints ensure the consistency and integrity of the data.

Flexibility

Relational databases are flexible and can handle a wide variety of data types.

Scalability

They can efficiently manage large volumes of data, especially when the schema is well-designed.

Standardization

SQL is a standardized language, making it easier to transfer knowledge and interact with different database systems (e.g., MySQL, PostgreSQL, Oracle, SQL Server).

ACID Properties

Relational databases follow the ACID properties (Atomicity, Consistency, Isolation, Durability) to ensure reliable transactions.

Complexity in Handling Large Amounts of Unstructured Data

Relational databases are not designed for handling unstructured data (e.g., large text files, multimedia content), which is better suited for NoSQL databases.

Scalability in Distributed Environments

While relational databases can handle large data sets, they may face challenges in highly distributed, cloud-based, or big data environments due to their reliance on a central server or clustered setup.

Performance Issues with Large Scale Operations

Complex queries on large data sets, especially those involving multiple joins, can lead to performance bottlenecks.