Full-Stack Development Fundamentals

Chapter 11: Database Basics

11.1 Database Concepts

Databases are organized collections of data stored electronically. They provide efficient storage, retrieval, and management of information.

Database Components:

• Tables: Collections of related data organized in rows and columns
• Rows (Records): Individual entries in a table
• Columns (Fields): Attributes that define the type of data stored
• Primary Key: Unique identifier for each row
• Foreign Key: Reference to another table's primary key

Relational Database Structure:

users table:
+----+----------+------------------+
| id | name     | email            |
+----+----------+------------------+
| 1  | John     | john@example.com |
| 2  | Jane     | jane@example.com |
+----+----------+------------------+

orders table:
+----+---------+--------+-------+
| id | user_id | product| price |
+----+---------+--------+-------+
| 1  | 1       | Laptop | 999   |
| 2  | 2       | Phone  | 699   |
+----+---------+--------+-------+

Database Relationships:

• One-to-One: Each record in one table relates to one record in another
• One-to-Many: One record relates to multiple records (user has many orders)
• Many-to-Many: Multiple records relate to multiple records (students and courses)

Database Types:

• Relational (SQL): MySQL, PostgreSQL, SQL Server - structured data with relationships
• NoSQL: MongoDB, Redis, Cassandra - flexible schemas, document-based

11.2 SQL Basics

SQL (Structured Query Language) is used to interact with relational databases.

SELECT - Retrieve Data:

-- Select all columns
SELECT * FROM users;

-- Select specific columns
SELECT name, email FROM users;

-- Select with condition
SELECT * FROM users WHERE age > 18;

-- Select with sorting
SELECT * FROM users ORDER BY name ASC;

-- Select with limit
SELECT * FROM users LIMIT 10;

INSERT - Add Data:

-- Insert single record
INSERT INTO users (name, email, age) VALUES ('John', 'john@example.com', 30);

-- Insert multiple records
INSERT INTO users (name, email, age) VALUES 
    ('Jane', 'jane@example.com', 25),
    ('Bob', 'bob@example.com', 35);

UPDATE - Modify Data:

-- Update single record
UPDATE users SET name = 'Jane' WHERE id = 1;

-- Update multiple columns
UPDATE users SET name = 'John Doe', age = 31 WHERE id = 1;

-- Update multiple records
UPDATE users SET status = 'active' WHERE age > 18;

DELETE - Remove Data:

-- Delete specific record
DELETE FROM users WHERE id = 1;

-- Delete with condition
DELETE FROM users WHERE age < 18;

-- Delete all records (be careful!)
DELETE FROM users;

11.3 Advanced SQL

Advanced SQL queries help you work with complex data relationships.

JOINs - Combine Tables:

-- Inner join (matching records only)
SELECT users.name, orders.product 
FROM users 
INNER JOIN orders ON users.id = orders.user_id;

-- Left join (all users, matching orders)
SELECT users.name, orders.product 
FROM users 
LEFT JOIN orders ON users.id = orders.user_id;

-- Right join (all orders, matching users)
SELECT users.name, orders.product 
FROM users 
RIGHT JOIN orders ON users.id = orders.user_id;

Aggregate Functions:

-- Count records
SELECT COUNT(*) FROM users;

-- Sum values
SELECT SUM(price) FROM orders;

-- Average
SELECT AVG(age) FROM users;

-- Maximum
SELECT MAX(price) FROM orders;

-- Minimum
SELECT MIN(age) FROM users;

-- Group by
SELECT user_id, COUNT(*) as order_count 
FROM orders 
GROUP BY user_id;

11.4 Database Design

Good database design ensures data integrity and performance.

Normalization:

• First Normal Form (1NF): Each column contains atomic values
• Second Normal Form (2NF): No partial dependencies
• Third Normal Form (3NF): No transitive dependencies

Indexing:

• Create indexes on frequently queried columns
• Improves query performance
• Trade-off: Slower inserts/updates

Data Types:

• INT: Whole numbers
• VARCHAR: Variable-length strings
• TEXT: Long text content
• DATE/DATETIME: Date and time values
• BOOLEAN: True/false values