Databases Beginner Course

A database is an organized collection of data managed by a DBMS (Database Management System). Unlike flat files, databases guarantee data integrity, concurrent access, fast queries, and durability. Every app you use — banking, social media, e-commerce — stores its data in a database. Understanding databases means understanding where and how the world's information lives.

# File (fragile) vs Database (structured)
# File: no constraints, no concurrency, slow search
# Database: schema, ACID, indexes, multi-user
print('Databases organize, protect, and query data')

Banking, social media, e-commerce, healthcare, gaming.

Relational databases (MySQL, PostgreSQL) store data in tables with rigid schemas and relationships — perfect for structured, transactional data. NoSQL databases (MongoDB, Redis) store documents, key-values, or graphs — flexible, horizontally scalable, ideal for unstructured or rapidly-changing data. The choice is a design decision: consistency and relationships (SQL) vs flexibility and scale (NoSQL).

# Relational: tables, rows, joins
# NoSQL: documents, key-value, graphs
comparison = {
    'SQL': 'structured, ACID, joins, vertical scaling',
    'NoSQL': 'flexible, horizontal scaling, eventual consistency'
}
print(comparison)

NoSQL — horizontal scaling for massive unstructured data.

A relational database is made of tables — each table has columns (fields) and rows (records). A users table has columns like id, name, email; each user is a row. The PRIMARY KEY uniquely identifies each row. This tabular structure is the foundation of all SQL. Everything else — queries, joins, indexes — operates on this simple grid.

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT UNIQUE,
    age INTEGER
);

An auto-incrementing integer.

SELECT is the most important SQL command — it retrieves data. SELECT column FROM table fetches values; WHERE filters rows by condition. The query flow: FROM (pick table) → WHERE (filter rows) → SELECT (pick columns). Master SELECT + WHERE and you can answer most basic data questions. It's the 'print' of databases.

-- Get names of users over 18
SELECT name, email
FROM users
WHERE age > 18;

SELECT * FROM products;

CRUD — Create, Read, Update, Delete — is the full data lifecycle. INSERT adds rows; SELECT reads; UPDATE modifies; DELETE removes. Together they're the four operations every app performs on data. The critical safety rule: always use a WHERE clause with UPDATE and DELETE — without it, you modify or delete EVERY row.

-- CREATE
INSERT INTO users (name, email) VALUES ('Ali', '[email protected]');
-- UPDATE
UPDATE users SET age = 22 WHERE name = 'Ali';
-- DELETE
DELETE FROM users WHERE age < 18;

Without WHERE, DELETE removes all rows — catastrophic data loss.

Aggregate functions collapse many rows into one summary value. COUNT counts rows, SUM totals a column, AVG averages, MIN/MAX find extremes. GROUP BY groups rows by a column before aggregating — total sales per region, average age per department. This turns raw data into insights: one line of SQL replaces loops of Python code.

SELECT department, COUNT(*) AS employee_count, AVG(salary) AS avg_salary
FROM employees
GROUP BY department;

SELECT COUNT(*) FROM table_name;

Joins combine rows from multiple tables based on a related column — the power that makes relational databases relational. INNER JOIN keeps only matching rows; LEFT JOIN keeps all left rows plus matches; RIGHT JOIN the opposite. Example: join orders with customers to show who ordered what. Joins connect the dots across your schema.

SELECT users.name, orders.total
FROM users
INNER JOIN orders ON users.id = orders.user_id;

LEFT JOIN.

Normalization organizes data to eliminate redundancy and prevent anomalies — the same data stored once, referenced everywhere. 1NF: atomic values, no repeating groups. 2NF: no partial dependencies. 3NF: no transitive dependencies. The goal: every fact stored exactly once. Normalized schemas are cleaner, consistent, and the standard for transactional systems.

# Unnormalized: orders has customer_name repeated
# Normalized: customers table + orders references customer_id
-- customers: (id, name, email)
-- orders: (id, customer_id, total)  ← no name duplication

Eliminate data redundancy and anomalies.

A primary key uniquely identifies a row in its table. A foreign key in another table references that primary key, creating a relationship and enforcing referential integrity — you can't have an order for a non-existent customer. This constraint is the backbone of relational design: related data stays consistent automatically, because the database rejects orphaned references.

CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name TEXT
);
CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER,
    FOREIGN KEY (customer_id) REFERENCES customers(id)
);

Rows referencing non-existent parent rows.

Indexes are like a book's index — they let the database find rows without scanning the entire table. An index on a column enables O(log n) lookups instead of O(n) scans. The trade-off: indexes speed SELECT but slow INSERT/UPDATE/DELETE (must maintain the index), and consume storage. Index the columns you search and join on — not everything.

CREATE INDEX idx_users_email ON users(email);

-- This query now uses the index
SELECT * FROM users WHERE email = '[email protected]';

Slower writes — INSERT/UPDATE must maintain the index.

A transaction is a group of operations that succeed or fail TOGETHER — like transferring money (deduct from A, add to B: both or neither). ACID guarantees: Atomicity (all or nothing), Consistency (valid state), Isolation (concurrent transactions don't interfere), Durability (committed data survives crashes). Transactions are why banks don't lose money to race conditions.

BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;  -- both applied, or ROLLBACK undoes both

A transaction either fully completes or fully rolls back — no partial state.

A view is a saved SELECT query that acts like a virtual table. It simplifies complex joins, encapsulates business logic, and can hide sensitive columns. Instead of writing a 10-line join every time, you create a view once and SELECT from it. Views are queries stored in the database — the ultimate DRY (Don't Repeat Yourself) tool for SQL.

CREATE VIEW active_users AS
SELECT id, name, email
FROM users
WHERE is_active = 1;

SELECT * FROM active_users;

A function — saved logic you can call repeatedly.

SQLite is a file-based database built into Python — zero setup, perfect for learning and small apps. The sqlite3 module connects Python and SQL: create a connection, get a cursor, execute SQL, commit, close. Parameterized queries (?) prevent SQL injection — never string-format user input into SQL. This is how apps talk to databases.

import sqlite3

conn = sqlite3.connect('app.db')
cursor = conn.cursor()
cursor.execute('CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)')
cursor.execute('INSERT INTO users (name) VALUES (?)', ('Ali',))
conn.commit()
cursor.execute('SELECT * FROM users')
print(cursor.fetchall())
conn.close()

To prevent SQL injection by safely escaping user input.

SQL injection is the #1 database vulnerability: attacker input like '; DROP TABLE users; --' gets executed as SQL when you concatenate strings. Prevention: parameterized queries (placeholders), ORMs, input validation, and least-privilege database accounts. Understanding this attack is essential — it's how millions of records get leaked every year.

# VULNERABLE (never do this)
# query = f"SELECT * FROM users WHERE name = '{user_input}'"

# SAFE
cursor.execute('SELECT * FROM users WHERE name = ?', (user_input,))

Parameterized queries (prepared statements).

An ORM (Object-Relational Mapping) lets you work with databases using Python objects instead of raw SQL — a User class maps to a users table, attributes to columns. SQLAlchemy is Python's standard ORM. Benefits: type safety, cleaner code, database-agnostic (switch DBs without rewriting). Trade-off: some performance loss and hidden complexity. ORMs power most web frameworks.

from sqlalchemy import Column, Integer, String, create_engine
from sqlalchemy.orm import declarative_base

Base = declarative_base()
class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    email = Column(String)

Python classes/objects to database tables/rows.

Backups are your last line of defense — full backups (everything), incremental (changes since last backup), and continuous (transaction logs). The 3-2-1 rule: 3 copies, 2 media types, 1 offsite. Recovery means restoring to a point in time. No backup = data loss is permanent. This is operational database skill every developer needs.

# PostgreSQL backup
# pg_dump mydb > backup.sql
# restore
# psql mydb < backup.sql

# SQLite backup
import sqlite3
src = sqlite3.connect('app.db'); dst = sqlite3.connect('backup.db')
src.backup(dst)

3 copies, 2 media types, 1 offsite.

An Entity-Relationship Diagram (ERD) visualizes your data model: entities (tables), attributes (columns), and relationships (one-to-one, one-to-many, many-to-many). Before writing CREATE TABLE, you sketch the ERD. One-to-many (customer→orders) is the most common; many-to-many needs a junction table. Good design upfront prevents painful migrations later.

# Entities: Customer (1) --- (many) Orders
# Many-to-many: Students <-> Courses needs enrollment table
customers(id, name)
orders(id, customer_id, total)
enrollments(student_id, course_id)

A junction table with two foreign keys.

Constraints are rules the database enforces: NOT NULL (required), UNIQUE (no duplicates), CHECK (value range), DEFAULT (fallback), FOREIGN KEY (relationship). They make the database self-protecting — invalid data can't even enter. Better to reject bad data at write time than clean it up later. Constraints are your schema's immune system.

CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    price DECIMAL CHECK (price >= 0),
    stock INTEGER DEFAULT 0,
    UNIQUE(name)
);

NOT NULL.

PostgreSQL and MySQL are the two dominant open-source relational databases. PostgreSQL: feature-rich, standards-compliant, advanced types (JSON, arrays), the choice for complex applications. MySQL: fast, simple, widely used in web apps (WordPress), great for read-heavy workloads. Both are excellent — the choice depends on complexity and ecosystem. PostgreSQL is the modern default for new projects.

# PostgreSQL strengths: JSONB, full-text, complex queries, extensions
# MySQL strengths: simplicity, replication, huge hosting ecosystem
print('PostgreSQL = features; MySQL = simplicity + hosting')

PostgreSQL.

Your capstone: build a library database. Design tables (books, authors, borrowers, loans), define relationships, add constraints, then write queries — find books by author, active loans, overdue books. This applies everything: schema design, keys, joins, aggregates, constraints. It's the complete, portfolio-ready database project.

CREATE TABLE authors (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE books (id INTEGER PRIMARY KEY, title TEXT, author_id INTEGER,
    FOREIGN KEY (author_id) REFERENCES authors(id));
CREATE TABLE loans (id INTEGER PRIMARY KEY, book_id INTEGER,
    due_date DATE,
    FOREIGN KEY (book_id) REFERENCES books(id));

-- Query: books by author
SELECT books.title FROM books
JOIN authors ON books.author_id = authors.id
WHERE authors.name = 'JK Rowling';

SELECT * FROM loans WHERE due_date < CURRENT_DATE;