Relational database concepts

Learning Focus

Use this lesson to understand Relational database concepts with practical syntax and examples.

What is a Relational Database?

Definition:

• A database that organizes data into tables (relations) with rows and columns
• Based on relational model proposed by Edgar F. Codd (1970)
• Data relationships are maintained through keys

Key Characteristics:

• Data is stored in structured format
• Relationships between data elements are explicitly defined
• Uses SQL (Structured Query Language) for data manipulation
• Maintains data integrity through constraints

Core Components

a) Tables (Relations)

• Structure:
  • Columns: Represent attributes (e.g., "customer_name", "order_date")
  • Rows: Represent individual records (tuples)

Example: Customers Table

| customer_id | name     | email               |
|-------------|----------|---------------------|
| 1           | Alice    | alice@example.com   |
| 2           | Bob      | bob@company.org     |

b) Keys

• Primary Key:
  • Unique identifier for each record (e.g., customer_id)
  • Cannot contain NULL values
• Foreign Key:
  • Field that links to another table's primary key
  • Maintains referential integrity

c) Relationships

1. One-to-Many (Most common)
   • e.g., One customer can have many orders
2. Many-to-Many
   • e.g., Students and Courses (requires junction table)
3. One-to-One
   • e.g., User and UserProfile tables

Database Normalization

Process of structuring data to:

• Eliminate data redundancy
• Ensure data dependencies make sense
• Improve data integrity

Common Normal Forms:

1. 1NF: Atomic values, no repeating groups
2. 2NF: No partial dependencies
3. 3NF: No transitive dependencies

Example of Normalization:

Before 1NF (Denormalized):
| order_id | items                     |
|----------|---------------------------|
| 1001     | Shirt, Jeans, Hat         |

After 1NF:
| order_id | item    |
|----------|---------|
| 1001     | Shirt   |
| 1001     | Jeans   |
| 1001     | Hat     |

ACID Properties

Guarantees database transactions are processed reliably:

• Atomicity: All-or-nothing transactions
• Consistency: Valid data state after transactions
• Isolation: Concurrent transactions don't interfere
• Durability: Committed transactions persist

SQL Overview

• DDL (Data Definition Language): CREATE, ALTER, DROP
• DML (Data Manipulation Language): SELECT, INSERT, UPDATE, DELETE
• DCL (Data Control Language): GRANT, REVOKE
• TCL (Transaction Control Language): COMMIT, ROLLBACK

Advantages of Relational Databases

• Data integrity through constraints
• Flexible query capabilities
• Mature technology with strong standards
• ACID compliance for reliable transactions
• Fine-grained access control

Common RDBMS Systems

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

Relational vs. NoSQL Databases

Feature	Relational	NoSQL
Data Model	Table-based	Document/Graph/etc.
Schema	Fixed	Flexible
Scalability	Vertical	Horizontal
Transactions	ACID compliant	BASE model
Best For	Complex queries	Big data, flexibility

Visual Learning Aids

1. Table Relationships Diagram:

   Diagram

2. Normalization Process Flow:

   Raw Data -> 1NF (Eliminate duplicates) -> 2NF (Remove partial dependencies) -> 3NF (Remove transitive dependencies)

Learning Check

Questions:

1. Why is a primary key important in a relational database?
2. What problem does normalization solve?
3. How does a foreign key differ from a primary key?
4. When would you choose a relational database over NoSQL?

Exercises:

1. Identify primary/foreign keys in a sample database schema
2. Normalize a denormalized dataset to 3NF
3. Draw an ER diagram for a simple library system

Real-World Examples

1. Banking systems (account transactions)
2. E-commerce platforms (orders/inventory)
3. Hospital management systems (patient records)

Key Takeaways

• Relational databases organize data in tables with defined relationships
• Keys (primary/foreign) maintain data integrity and relationships
• Normalization reduces redundancy and improves data structure
• ACID properties ensure reliable transactions
• SQL is the standard language for interaction

This foundation will help you understand why MySQL works the way it does before learning how to use it. Ready to move to the next module (MySQL Installation and Setup) when you are!

Concept Map

flowchart LR
    A[Schema Context] --> B[Relational database concepts]
    B --> C[Query Pattern]
    C --> D[Validation]
    D --> E[Production Use]

Common Pitfalls

Pitfall	Consequence	Prevention
Executing queries without validating sample rows	Logic errors reach production data or reports	Start with SELECT ... LIMIT 10 and inspect edge cases
Ignoring NULL and duplicate behavior	Aggregations and filters return misleading results	Test with NULL, duplicates, and empty sets explicitly
Using advanced syntax before checking schema	Queries fail due to missing columns/indexes	Verify structure with DESCRIBE table_name; and adapt query design

Quick Reference

SHOW DATABASES;
USE your_database;
SHOW TABLES;
SELECT * FROM your_table LIMIT 10;

What's Next

• Next: RDMS Concepts - Continue to the next concept with incremental complexity.
• Module Overview - Return to this module index and choose another related lesson.