Objective

Create a relational structure for organizing photos into albums. This pattern enables hierarchical organization with albums containing multiple photos.

Step 1: Create Albums Table

Create a table to store album information.

CREATE TABLE albums (
    album_id INTEGER PRIMARY KEY,
    album_name VARCHAR(255) NOT NULL,
    description TEXT,
    cover_photo_id INTEGER,
    is_public BOOLEAN DEFAULT FALSE,
    created_by VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Step 2: Create Photos Table

Create a table for photos with album relationship.

CREATE TABLE photos (
    photo_id INTEGER PRIMARY KEY,
    album_id INTEGER,
    title VARCHAR(255),
    photo IMAGE(JPEG),
    description TEXT,
    taken_date DATE,
    sort_order INTEGER DEFAULT 0,
    is_favorite BOOLEAN DEFAULT FALSE,
    view_count INTEGER DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (album_id) REFERENCES albums(album_id)
);

Step 3: Insert Sample Albums

Create sample albums for organization.

INSERT INTO albums (album_id, album_name, description, is_public, created_by) VALUES
    (1, 'Summer Vacation 2024', 'Family trip to the beach', TRUE, 'john_doe'),
    (2, 'Wedding Photography', 'Sarah and Mike wedding ceremony', FALSE, 'jane_photographer'),
    (3, 'Nature Collection', 'Landscape and wildlife photography', TRUE, 'nature_lover'),
    (4, 'City Architecture', 'Urban photography from around the world', TRUE, 'urban_explorer'),
    (5, 'Personal Memories', 'Private family moments', FALSE, 'john_doe');

Step 4: Insert Photos into Albums

Add photos to the albums.

INSERT INTO photos (photo_id, album_id, title, description, taken_date, sort_order, is_favorite) VALUES
    -- Summer Vacation album
    (1, 1, 'Beach Sunset', 'Golden hour at the beach', '2024-07-15', 1, TRUE),
    (2, 1, 'Family Picnic', 'Lunch by the shore', '2024-07-15', 2, FALSE),
    (3, 1, 'Kids Playing', 'Building sandcastles', '2024-07-16', 3, TRUE),
    (4, 1, 'Ocean View', 'Morning view from hotel', '2024-07-17', 4, FALSE),

    -- Wedding Photography album
    (5, 2, 'First Dance', 'Bride and groom first dance', '2024-06-20', 1, TRUE),
    (6, 2, 'Ceremony', 'Exchange of vows', '2024-06-20', 2, TRUE),
    (7, 2, 'Group Photo', 'All guests together', '2024-06-20', 3, FALSE),

    -- Nature Collection album
    (8, 3, 'Mountain Peak', 'Summit view at sunrise', '2024-03-10', 1, TRUE),
    (9, 3, 'Deer in Forest', 'Wildlife encounter', '2024-04-05', 2, TRUE),
    (10, 3, 'Waterfall', 'Cascading falls', '2024-05-12', 3, FALSE);

Step 5: Update Album Cover Photos

Set cover photos for albums.

UPDATE albums SET cover_photo_id = 1 WHERE album_id = 1;
UPDATE albums SET cover_photo_id = 5 WHERE album_id = 2;
UPDATE albums SET cover_photo_id = 8 WHERE album_id = 3;

Step 6: Query Albums with Photo Counts

Get albums with their photo statistics.

SELECT
    a.album_name,
    a.description,
    a.is_public,
    COUNT(p.photo_id) as photo_count,
    SUM(CASE WHEN p.is_favorite THEN 1 ELSE 0 END) as favorite_count
FROM albums a
LEFT JOIN photos p ON a.album_id = p.album_id
GROUP BY a.album_id, a.album_name, a.description, a.is_public
ORDER BY photo_count DESC;

Step 7: Get Album Contents

Retrieve all photos in a specific album.

SELECT
    p.title,
    p.description,
    p.taken_date,
    p.is_favorite,
    p.view_count
FROM photos p
WHERE p.album_id = 1
ORDER BY p.sort_order;

Step 8: Find Favorite Photos Across Albums

Query favorite photos from all albums.

SELECT
    a.album_name,
    p.title,
    p.taken_date
FROM photos p
INNER JOIN albums a ON p.album_id = a.album_id
WHERE p.is_favorite = TRUE
ORDER BY p.taken_date DESC;

Step 9: Public Albums Gallery

Get public albums for a gallery view.

SELECT
    a.album_id,
    a.album_name,
    a.description,
    a.created_by,
    COUNT(p.photo_id) as photo_count,
    a.created_at
FROM albums a
LEFT JOIN photos p ON a.album_id = p.album_id
WHERE a.is_public = TRUE
GROUP BY a.album_id, a.album_name, a.description, a.created_by, a.created_at
ORDER BY a.created_at DESC;

Step 10: Photos Without Albums

Find orphaned photos not in any album.

INSERT INTO photos (photo_id, album_id, title, taken_date) VALUES
    (11, NULL, 'Unorganized Photo 1', '2024-08-01'),
    (12, NULL, 'Unorganized Photo 2', '2024-08-02');

SELECT photo_id, title, taken_date
FROM photos
WHERE album_id IS NULL;

Step 11: Album Statistics

Generate statistics for photo organization.

SELECT
    COUNT(DISTINCT a.album_id) as total_albums,
    COUNT(DISTINCT CASE WHEN a.is_public THEN a.album_id END) as public_albums,
    COUNT(p.photo_id) as total_photos,
    COUNT(CASE WHEN p.album_id IS NULL THEN 1 END) as unorganized_photos,
    SUM(p.view_count) as total_views
FROM albums a
FULL JOIN photos p ON a.album_id = p.album_id;

Cleanup (Optional)

DROP TABLE IF EXISTS photos;
DROP TABLE IF EXISTS albums;

Expected Outcomes

Albums organize related photos
Photos linked to albums via foreign key
Cover photos enhance album display
Statistics aggregate across relationships
Public/private visibility controls access

Key Concepts Learned

Parent-child table relationships
Foreign key organization
Aggregate queries with JOINs
Cover photo pattern
Sorting within albums

Photo Album Organization