Database Fundamentals

What is a Database?

A database is an organized collection of structured data stored electronically. A Relational Database Management System (RDBMS) manages relational databases.

💡 Think of a database as a highly organized filing system. Each "file cabinet" is a database, each "folder" is a table, and each "document" is a row.

Core Concepts

Tables

A table is a collection of related data organized in rows and columns.

-- Creating a simple table
CREATE TABLE students (
    student_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    enrollment_date DATE
);

Columns (Attributes)

Each column defines a specific attribute with a data type:

Rows (Records)

Each row represents a single record — an instance of the entity described by the table.

-- Inserting rows
INSERT INTO students VALUES (1, 'Alice', 'Johnson', '2024-01-15');
INSERT INTO students VALUES (2, 'Bob', 'Smith', '2024-01-16');

Keys

Primary Key

A primary key uniquely identifies each row in a table. It must be unique and cannot be NULL.

CREATE TABLE orders (
    order_id INT PRIMARY KEY,  -- Primary key
    customer_id INT,
    order_date DATE,
    total_amount DECIMAL(10,2)
);

Foreign Key

A foreign key creates a link between two tables by referencing the primary key of another table.

CREATE TABLE order_items (
    item_id INT PRIMARY KEY,
    order_id INT,              -- Foreign key referencing orders
    product_id INT,            -- Foreign key referencing products
    quantity INT,
    FOREIGN KEY (order_id) REFERENCES orders(order_id)
);

Relationships

One-to-One (1:1)

Each record in Table A relates to exactly one record in Table B.

users (1) ─────────── (1) user_profiles

One-to-Many (1:N)

Each record in Table A can relate to many records in Table B.

customers (1) ─────────── (N) orders

Many-to-Many (M:N)

Records in Table A can relate to many records in Table B, and vice versa. Requires a junction table.

students (M) ──── enrollment (junction) ──── (N) courses

-- Junction table for many-to-many
CREATE TABLE enrollments (
    student_id INT,
    course_id INT,
    enrollment_date DATE,
    PRIMARY KEY (student_id, course_id),
    FOREIGN KEY (student_id) REFERENCES students(student_id),
    FOREIGN KEY (course_id) REFERENCES courses(course_id)
);

Schema

A schema is the blueprint of your database — it defines the structure, relationships, and constraints.

Database: university
├── Schema: students
│   ├── Table: students
│   ├── Table: enrollments
│   └── Table: grades
├── Schema: courses
│   ├── Table: courses
│   └── Table: instructors
└── Schema: finance
    ├── Table: payments
    └── Table: scholarships

Normalization

Normalization organizes tables to reduce data redundancy and improve data integrity.

⚠️ While normalization reduces redundancy, over-normalizing can hurt performance due to excessive JOINs. Find the right balance for your use case.

✏️ Exercise: Create a table called 'courses' with columns: course_id (PRIMARY KEY), course_name, credits, department.

See Solution

CREATE TABLE courses (
    course_id INT PRIMARY KEY,
    course_name VARCHAR(100),
    credits INT,
    department VARCHAR(50)
);

✅ Key Takeaways

1. Tables store data in rows and columns with defined data types
2. Primary keys uniquely identify each row; foreign keys create table relationships
3. Three relationship types exist: one-to-one, one-to-many, and many-to-many
4. Schema defines the database structure and constraints
5. Normalization reduces redundancy but balance it with performance needs