Databases

SQL & Relational Databases

CSCI 4513

Week 13, Lecture 22

Today's Learning Objectives

🎯 Understand what databases are and why we use them
🎯 Learn relational database concepts
🎯 Write SQL queries (SELECT, INSERT, UPDATE, DELETE)
🎯 Design database schemas with relationships
🎯 Practice with interactive SQL tutorials

Norse Mythology Connection

Mimir's Well - Repository of All Knowledge

Mimir's Well holds all knowledge of the cosmos, organized and connected, just as databases store and structure all your application's data.

The Tale:

At one of the roots of Yggdrasil lies Mimir's Well (Mímisbrunnr), a spring of crystalline water that holds all the knowledge and memories of the cosmos. Every event that has ever occurred, every fact that exists, every piece of wisdom accumulated since the beginning of time flows into this well and is preserved there perfectly.

Mimir himself dwells by the well, maintaining its waters and ensuring its knowledge remains pure and accessible. The well doesn't just passively store knowledge—it organizes and connects information. A single drop contains entire stories, relationships between events, and the implications of decisions. Querying the well requires understanding how to ask the right questions. Odin couldn't simply demand "tell me everything"—he had to know what knowledge he sought and how to properly request it. The well responds to structured queries, returning exactly what is asked for, organized in meaningful ways.

Querying the Well

// Mimir's Well - the database
const pool = mysql.createPool({
    host: 'localhost',
    database: 'mimirs_well'
});

// Structured knowledge - tables
CREATE TABLE gods (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    realm VARCHAR(50),
    power_level INT
);

// Properly querying the well
const getGodByName = async (name) => {
    const query = 'SELECT * FROM gods WHERE name = ?';
    const [results] = await pool.execute(query, [name]);
    return results[0];
};

// Relationships - cosmic connections
CREATE TABLE alliances (
    god_id INT,
    ally_id INT,
    FOREIGN KEY (god_id) REFERENCES gods(id),
    FOREIGN KEY (ally_id) REFERENCES gods(id)
);

Reflection Questions

❓ Mimir's Well organizes knowledge with clear structure and relationships. How do you decide how to structure tables and relationships in your database?
❓ Odin had to ask specific, well-formed questions to get useful answers. What makes a database query efficient versus inefficient?
❓ The well preserves knowledge eternally, but only if it's valid when added. What validation should happen before data enters your database?

What is a Database?

📚 Database: An organized collection of structured data stored electronically
🔧 DBMS: Database Management System - software to manage databases
💾 Examples: MySQL, PostgreSQL, MongoDB, SQLite, Oracle

Why use databases?

Persistent data storage that survives program restarts and can be accessed by multiple users simultaneously

Why Not Just Use Files?

❌ Plain Files

No structure enforcement
Hard to query efficiently
Difficult to update safely
No concurrent access control
No data validation

✅ Databases

Enforced data structure
Powerful query language
Safe concurrent updates
Multi-user support
Built-in validation

Relational Databases

📊 Data organized into tables (relations)
🔢 Each table has rows (records) and columns (fields)
🔗 Tables can be related to each other
🔑 Each row has a unique identifier (primary key)

users table:
+----+----------+------------------+
| id | username | email            |
+----+----------+------------------+
| 1  | alice    | alice@email.com  |
| 2  | bob      | bob@email.com    |
+----+----------+------------------+

SQL - Structured Query Language

📝 Standard language for relational databases
🗣️ Declarative - you say what you want, not how to get it
📂 Four main operation types (CRUD):

CREATE - INSERT
READ - SELECT

UPDATE - UPDATE
DELETE - DELETE

Creating Tables

CREATE TABLE users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    username TEXT NOT NULL UNIQUE,
    email TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE posts (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id INTEGER NOT NULL,
    title TEXT NOT NULL,
    content TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

🔑 PRIMARY KEY: Unique identifier for each row

🔗 FOREIGN KEY: Reference to another table's primary key

INSERT - Adding Data

-- Insert a single user
INSERT INTO users (username, email)
VALUES ('alice', 'alice@email.com');

-- Insert multiple users
INSERT INTO users (username, email)
VALUES
    ('bob', 'bob@email.com'),
    ('charlie', 'charlie@email.com'),
    ('diana', 'diana@email.com');

-- Insert a post
INSERT INTO posts (user_id, title, content)
VALUES (1, 'My First Post', 'Hello, world!');

💡 Note: id and created_at are auto-generated

SELECT - Reading Data

-- Select all columns from all rows
SELECT * FROM users;

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

-- Select with a condition (WHERE clause)
SELECT * FROM users WHERE username = 'alice';

-- Multiple conditions
SELECT * FROM posts
WHERE user_id = 1 AND created_at > '2024-01-01';

-- Pattern matching
SELECT * FROM users WHERE email LIKE '%@gmail.com';

SELECT - Sorting and Limiting

-- Order results
SELECT * FROM posts ORDER BY created_at DESC;

-- Limit number of results
SELECT * FROM posts LIMIT 10;

-- Skip and limit (pagination)
SELECT * FROM posts
ORDER BY created_at DESC
LIMIT 10 OFFSET 20;  -- Skip first 20, get next 10

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

-- Unique values
SELECT DISTINCT user_id FROM posts;

UPDATE - Modifying Data

-- Update a single user
UPDATE users
SET email = 'newalice@email.com'
WHERE username = 'alice';

-- Update multiple columns
UPDATE users
SET username = 'alice2024',
    email = 'alice2024@email.com'
WHERE id = 1;

-- Update multiple rows
UPDATE posts
SET content = UPPER(content)
WHERE user_id = 1;

⚠️ Always use WHERE! Without it, ALL rows will be updated!

DELETE - Removing Data

-- Delete a specific post
DELETE FROM posts WHERE id = 5;

-- Delete all posts by a user
DELETE FROM posts WHERE user_id = 1;

-- Delete old posts
DELETE FROM posts
WHERE created_at < '2023-01-01';

⚠️ Always use WHERE! Without it, ALL rows will be deleted!

💡 There's no undo for DELETE - it's permanent!

Database Relationships

🔗 One-to-One: One record in Table A relates to one in Table B
Example: User → Profile
🔗 One-to-Many: One record in Table A relates to many in Table B
Example: User → Posts
🔗 Many-to-Many: Multiple records in both tables can relate
Example: Students ↔ Classes (via enrollments)

JOIN - Combining Tables

-- Get all posts with their author information
SELECT
    posts.title,
    posts.content,
    users.username,
    users.email
FROM posts
INNER JOIN users ON posts.user_id = users.id;

-- Alternative syntax
SELECT p.title, p.content, u.username
FROM posts p
JOIN users u ON p.user_id = u.id;

🔗 INNER JOIN returns only rows that have matches in both tables

Types of JOINs

-- INNER JOIN (only matching rows)
SELECT * FROM posts
INNER JOIN users ON posts.user_id = users.id;

-- LEFT JOIN (all posts, even if user doesn't exist)
SELECT * FROM posts
LEFT JOIN users ON posts.user_id = users.id;

-- RIGHT JOIN (all users, even if they have no posts)
SELECT * FROM users
RIGHT JOIN posts ON users.id = posts.user_id;

-- FULL OUTER JOIN (all rows from both tables)
SELECT * FROM users
FULL OUTER JOIN posts ON users.id = posts.user_id;

Aggregate Functions

-- Count posts per user
SELECT
    users.username,
    COUNT(posts.id) as post_count
FROM users
LEFT JOIN posts ON users.id = posts.user_id
GROUP BY users.id, users.username;

-- Other aggregate functions
SELECT
    user_id,
    COUNT(*) as total_posts,
    MAX(created_at) as latest_post,
    MIN(created_at) as first_post
FROM posts
GROUP BY user_id;

HAVING - Filtering Grouped Results

-- Find users with more than 5 posts
SELECT
    users.username,
    COUNT(posts.id) as post_count
FROM users
JOIN posts ON users.id = posts.user_id
GROUP BY users.id, users.username
HAVING COUNT(posts.id) > 5;

-- WHERE vs HAVING:
-- WHERE filters rows BEFORE grouping
-- HAVING filters groups AFTER grouping

SELECT user_id, COUNT(*) as count
FROM posts
WHERE created_at > '2024-01-01'  -- Filter individual posts
GROUP BY user_id
HAVING COUNT(*) > 3;              -- Filter grouped results

Database Schema Design

🎨 Schema: The structure and organization of your database
📋 Define tables, columns, data types, and relationships
🎯 Good design prevents data redundancy and inconsistencies

Key principle: Normalization - organize data to reduce redundancy

Example: Blog Schema

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

CREATE TABLE posts (
    id INTEGER PRIMARY KEY,
    user_id INTEGER NOT NULL,
    title TEXT NOT NULL,
    content TEXT,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE TABLE comments (
    id INTEGER PRIMARY KEY,
    post_id INTEGER NOT NULL,
    user_id INTEGER NOT NULL,
    content TEXT NOT NULL,
    FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

Many-to-Many Relationships

Use a join table (also called junction/bridge table)

-- Students and Classes have many-to-many relationship
CREATE TABLE students (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE classes (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL
);

-- Join table connects them
CREATE TABLE enrollments (
    student_id INTEGER,
    class_id INTEGER,
    enrolled_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (student_id, class_id),
    FOREIGN KEY (student_id) REFERENCES students(id),
    FOREIGN KEY (class_id) REFERENCES classes(id)
);

Data Types

Common Types

INTEGER - Whole numbers
REAL - Decimal numbers
TEXT - Strings
BLOB - Binary data
BOOLEAN - True/false
DATE - Date only
TIMESTAMP - Date + time

Constraints

PRIMARY KEY
FOREIGN KEY
NOT NULL
UNIQUE
DEFAULT
CHECK
AUTOINCREMENT

ON DELETE Actions

CREATE TABLE posts (
    id INTEGER PRIMARY KEY,
    user_id INTEGER NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(id)
        ON DELETE CASCADE
);

-- CASCADE: Delete related posts when user is deleted
-- RESTRICT: Prevent deletion if related posts exist
-- SET NULL: Set user_id to NULL when user deleted
-- SET DEFAULT: Set to default value
-- NO ACTION: Do nothing (might violate integrity)

💡 Choose based on your business logic!

Practice: SQL Zoo

Interactive SQL tutorials with immediate feedback

🔗 sqlzoo.net

Start with SELECT basics and work through:

SELECT basics
SELECT from World
SELECT from Nobel
SELECT within SELECT
SUM and COUNT
JOIN operations

Practice: GalaXQL

Interactive SQL tutorial with a space theme!

🚀 solhsa.com/g3/

Learn SQL by exploring a fictional galaxy:

🌟 Query star systems and planets
🛸 Practice JOINs with spaceships
👽 Filter alien species data
📊 Aggregate galactic statistics

Fun, gamified approach to learning SQL!

SQL Best Practices

✅ Always use WHERE with UPDATE and DELETE
✅ Use prepared statements to prevent SQL injection
✅ Index frequently queried columns for performance
✅ Use meaningful table and column names
✅ Normalize your schema to reduce redundancy
✅ Use transactions for operations that must complete together
✅ Back up your database regularly

SQL Injection Attack

// ❌ DANGEROUS - Never do this!
const username = req.body.username;
const query = `SELECT * FROM users WHERE username = '${username}'`;
// If username is: ' OR '1'='1
// Query becomes: SELECT * FROM users WHERE username = '' OR '1'='1'
// Returns ALL users!

// ✅ SAFE - Use parameterized queries
const query = 'SELECT * FROM users WHERE username = ?';
db.execute(query, [username]);

// ✅ SAFE - Use ORM (Object-Relational Mapping)
const user = await User.findOne({
    where: { username: username }
});

Transactions

-- Transfer money between accounts
-- Both operations must succeed or both must fail

BEGIN TRANSACTION;

UPDATE accounts
SET balance = balance - 100
WHERE id = 1;

UPDATE accounts
SET balance = balance + 100
WHERE id = 2;

-- If everything worked:
COMMIT;

-- If something went wrong:
ROLLBACK;

💡 Transactions ensure data consistency

Review: CRUD Operations

-- CREATE
INSERT INTO users (username, email)
VALUES ('alice', 'alice@email.com');

-- READ
SELECT * FROM users WHERE username = 'alice';

-- UPDATE
UPDATE users SET email = 'newemail@email.com'
WHERE username = 'alice';

-- DELETE
DELETE FROM users WHERE username = 'alice';

Review: JOINs

-- Get posts with author information
SELECT
    p.title,
    p.content,
    u.username,
    u.email
FROM posts p
INNER JOIN users u ON p.user_id = u.id
WHERE u.username = 'alice'
ORDER BY p.created_at DESC
LIMIT 10;

Common SQL Commands Summary

Data Definition (DDL)

CREATE TABLE
ALTER TABLE
DROP TABLE

Data Manipulation (DML)

SELECT
INSERT
UPDATE
DELETE

Clauses & Keywords

WHERE
ORDER BY
LIMIT
GROUP BY
HAVING
JOIN
LIKE
IN

Today's Takeaways

You Can Now:

✅ Understand what databases are and why we use them
✅ Create tables with proper data types and constraints
✅ Write SQL queries to CREATE, READ, UPDATE, and DELETE data
✅ Design schemas with relationships and foreign keys
✅ Use JOINs to combine data from multiple tables
✅ Practice SQL interactively with SQL Zoo and GalaXQL

Next Steps

📅 Next Class: Node.js - Building a server

📝 Practice:

Complete SQL Zoo tutorials (SELECT basics through JOINs)
Try GalaXQL for interactive practice
Design a schema for a project idea

🎯 Coming Soon: Connecting databases to Node.js applications