💾 What is a Database?

📖 Definition

A Database (DB) is a systematically structured collection of data. A DBMS (Database Management System) is software that creates, manages, and accesses databases. Databases enable efficient data storage and retrieval, allowing multiple users to access safely and simultaneously.

🎯 Understanding with Analogy

Library System

Think of a database as a library:

• Database: The entire library
• Table: Each section (fiction, science, history, etc.)
• Row: Individual book
• Column: Book attributes (title, author, publication year, etc.)
• Primary Key: Unique book number
• DBMS: Librarian and management system

⚙️ How It Works

Database Structure

Database
└─ Table
   ├─ Row (Record) - Individual data item
   │  └─ Example: One user's information
   └─ Column (Field) - Data attributes
      └─ Example: Name, email, age

Relational Database Example

-- Users table
users
┌────┬────────┬──────────────────┬─────┐
│ id │  name  │      email       │ age │
├────┼────────┼──────────────────┼─────┤
│ 1  │ John   │ john@mail.com    │ 25  │
│ 2  │ Alice  │ alice@mail.com   │ 30  │
│ 3  │ Bob    │ bob@mail.com     │ 28  │
└────┴────────┴──────────────────┴─────┘

-- Orders table
orders
┌────┬─────────┬─────────────┬───────┐
│ id │ user_id │   product   │ price │
├────┼─────────┼─────────────┼───────┤
│ 1  │   1     │  Laptop     │ 1500  │
│ 2  │   1     │  Mouse      │  30   │
│ 3  │   2     │  Keyboard   │  80   │
└────┴─────────┴─────────────┴───────┘
     ↑
     └─ Foreign key: References id in users table

CRUD Operations

CRUD are the 4 basic database operations:

C - Create (Insert): Add new data
R - Read   (Select): Query data
U - Update (Modify): Change existing data
D - Delete (Remove): Remove data

💡 Real Examples

CRUD with SQL

-- CREATE: Add data
INSERT INTO users (name, email, age)
VALUES ('John', 'john@mail.com', 25);

-- READ: Query data
SELECT * FROM users;
SELECT name, email FROM users WHERE age >= 28;

-- UPDATE: Modify data
UPDATE users SET age = 26 WHERE name = 'John';

-- DELETE: Remove data
DELETE FROM users WHERE id = 3;

Node.js Database Usage

const mysql = require('mysql2/promise');

// Database connection
const pool = mysql.createPool({
  host: 'localhost',
  user: 'root',
  password: 'password',
  database: 'myapp'
});

// CREATE - Add user
async function createUser(name, email, age) {
  const [result] = await pool.execute(
    'INSERT INTO users (name, email, age) VALUES (?, ?, ?)',
    [name, email, age]
  );
  return result.insertId;
}

// READ - Get all users
async function getAllUsers() {
  const [rows] = await pool.execute('SELECT * FROM users');
  return rows;
}

// UPDATE - Modify user
async function updateUser(id, name, email, age) {
  const [result] = await pool.execute(
    'UPDATE users SET name = ?, email = ?, age = ? WHERE id = ?',
    [name, email, age, id]
  );
  return result.affectedRows;
}

// DELETE - Remove user
async function deleteUser(id) {
  const [result] = await pool.execute(
    'DELETE FROM users WHERE id = ?',
    [id]
  );
  return result.affectedRows;
}

🤔 FAQ

Q1. Why use databases?

A: Many advantages over file systems:

❌ File System (e.g., users.txt)
├─ Data duplication possible
├─ Concurrent access issues
├─ Difficult backup/recovery
├─ Security vulnerabilities
└─ Slow searches

✅ Database
├─ Data integrity guaranteed
├─ Concurrency control (multiple users)
├─ Automated backup/recovery
├─ Access permission management
├─ Fast searches (indexes)
└─ Transaction support

Q2. Which database to choose?

A: Depends on project requirements:

// Relational Databases (SQL)
MySQL / PostgreSQL / SQLite
├─ Pros: Precise data structure, transactions
├─ Cons: Low flexibility, limited scalability
└─ Suitable: Finance, e-commerce, user management

// Non-relational Databases (NoSQL)
MongoDB / Redis / Cassandra
├─ Pros: Flexible schema, horizontal scaling
├─ Cons: Difficult complex relationship handling
└─ Suitable: Social media, real-time analytics, caching

Q3. What is an Index?

A: A data structure that speeds up data retrieval:

-- Without index: Check all 1M rows (slow)
SELECT * FROM users WHERE email = 'test@mail.com';

-- Create index
CREATE INDEX idx_email ON users(email);

-- With index: Find directly via index (fast!)
SELECT * FROM users WHERE email = 'test@mail.com';

-- Downside: Slower writes (index must be updated)

Q4. What is a Transaction?

A: Bundling multiple operations as a single unit:

// Bank transfer example
async function transferMoney(fromId, toId, amount) {
  const connection = await pool.getConnection();

  try {
    await connection.beginTransaction();

    // 1. Decrease sender's balance
    await connection.execute(
      'UPDATE accounts SET balance = balance - ? WHERE id = ?',
      [amount, fromId]
    );

    // 2. Increase receiver's balance
    await connection.execute(
      'UPDATE accounts SET balance = balance + ? WHERE id = ?',
      [amount, toId]
    );

    // All success → Commit
    await connection.commit();
  } catch (error) {
    // Error → Rollback all
    await connection.rollback();
    throw error;
  } finally {
    connection.release();
  }
}

🎬 Summary

Databases are the core of all applications:

• Database: Structured data collection
• CRUD: Create, Read, Update, Delete - basic operations
• Table: Organizes data in rows and columns
• Relationship: Connections between tables (JOIN)
• Transaction: Safe data processing

Understanding databases well enables building stable and scalable applications! 💾✨