Database Concepts

What is a Database?

A database is an organized collection of structured data. A relational database stores data in tables that are connected through relationships.

💡 Think of a database as a digital filing system. Each table is like a folder, each row is a document, and each column is a field on that document.

Tables: The Foundation

A table organizes data into rows and columns:

Table: employees
+----+-------------+-----------+--------+-------------+
| id | first_name  | last_name | salary | department  |
+----+-------------+-----------+--------+-------------+
| 1  | Alice       | Johnson   | 75000  | Engineering |
| 2  | Bob         | Smith     | 62000  | Marketing   |
| 3  | Carol       | Williams  | 85000  | Engineering |
+----+-------------+-----------+--------+-------------+

Primary Keys

A primary key uniquely identifies each row in a table.

CREATE TABLE customers (
    id INTEGER PRIMARY KEY,  -- Unique identifier
    name TEXT NOT NULL,
    email TEXT UNIQUE NOT NULL
);

Rules for Primary Keys:

• Must be unique for every row
• Cannot be NULL
• Should never change
• Should be simple (integer or UUID)

Foreign Keys

A foreign key links tables together by referencing the primary key of another table.

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER,       -- Foreign key
    order_date DATE,
    total DECIMAL(10,2),
    FOREIGN KEY (customer_id) REFERENCES customers(id)
);

Foreign Key Actions

⚠️ Choose foreign key actions carefully. CASCADE can delete large amounts of data unintentionally.

✅ Key Takeaways

1. Databases store data in tables with rows and columns
2. Primary keys uniquely identify each row
3. Foreign keys create relationships between tables
4. Constraints enforce data integrity rules
5. Good database design prevents data redundancy