Basics of Databases

A comprehensive guide to understanding SQL and NoSQL databases.

---

What is a Database?

A database is a structured way to store data so you can:

• Save it
• Search it
• Update it
• Delete it
• Reliably manage it, even when millions of users are accessing it simultaneously

Two Main Categories

1. SQL (Relational databases)
2. NoSQL (Non-relational databases)

---

SQL Databases (Relational)

Examples: MySQL, PostgreSQL, SQL Server, Oracle, SQLite

How Data is Stored

Data is organized in tables (similar to Excel spreadsheets):

Users Table
------------------------------------
id  | name    | email           | age
------------------------------------
1   | Misbah  | m@example.com   | 22
2   | John    | j@example.com   | 25

Tables are connected using relationships (foreign keys).

Key Features

1. Fixed Schema

You define the structure first:

CREATE TABLE users (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  age INT
);

Data must obey it. No chaos allowed.

2. SQL Language

SELECT * FROM users WHERE age > 18;

Same language everywhere. Old, boring, powerful.

3. ACID Transactions

• Atomicity
• Consistency
• Isolation
• Durability

Meaning: Your money won't disappear mid-transfer. Usually.

4. Great for Structured Data

Ideal for banking, school systems, ecommerce, inventory, etc.

When SQL is Best

• ✅ Data is structured
• ✅ Relationships matter (users → orders → payments)
• ✅ You need strong consistency
• ✅ Complex queries & joins

---

NoSQL Databases (Non-relational)

Examples: MongoDB, Redis, Cassandra, Firebase, DynamoDB

Created because SQL databases started crying when the internet became huge.

Types of NoSQL

1. Document-based (MongoDB)

{
  "name": "Misbah",
  "skills": ["Python", "ESP32", "AI"],
  "age": 22
}

Flexible structure. Each record can be different.

2. Key-Value (Redis)

"user123" → "Misbah"

Fast. Simple. Memory-loving.

3. Column-based (Cassandra)

Used for massive scale data.

4. Graph (Neo4j)

Nodes + relationships (social networks).

Key Features

1. Flexible Schema

Add new fields anytime. No permission slips.

2. Horizontally Scalable

Easy to spread across many servers.

3. High Performance

Built for speed & large traffic.

4. BASE instead of ACID

• Basically Available
• Soft state
• Eventually consistent

Translation: Data might be slightly wrong for a moment, but fixes itself later.

When NoSQL is Best

• ✅ Huge data volume
• ✅ Rapid development
• ✅ Flexible data structure
• ✅ Real-time apps
• ✅ IoT, chat apps, logs, AI systems

Your ESP32 projects would feel at home here.

---

SQL vs NoSQL Comparison

Feature	SQL	NoSQL
Schema	Fixed	Flexible
Data Model	Tables	JSON / Key-Value / Graph
Scaling	Vertical	Horizontal
Consistency	Strong	Eventual (usually)
Query Language	SQL	Varies
Best For	Structured data	Big & messy data

---

Real-World Use Cases

Use SQL When:

• 📊 Student management system
• 💰 Bank system
• 🛒 Online store
• 🏢 ERP software

Use NoSQL When:

• 💬 Chat application
• 🌡️ IoT sensor data
• 🎮 Game backend
• 🤖 AI logs
• 📱 Social media feed

---

Additional Resources

• MySQL Documentation
• PostgreSQL Documentation
• MongoDB Documentation