Music Track Catalog

Objective

Create a relational music library with artists, albums, and tracks. This pattern enables building a complete music catalog system with proper relationships.

Step 1: Create Artists Table

Create a table for music artists.

CREATE TABLE artists (
    artist_id INTEGER PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    genre VARCHAR(50),
    country VARCHAR(50),
    bio TEXT,
    website VARCHAR(255),
    formed_year INTEGER,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Step 2: Create Albums Table

Create a table for albums linked to artists.

CREATE TABLE albums (
    album_id INTEGER PRIMARY KEY,
    artist_id INTEGER NOT NULL,
    title VARCHAR(255) NOT NULL,
    release_date DATE,
    genre VARCHAR(50),
    label VARCHAR(100),
    cover_art IMAGE(JPEG),
    total_tracks INTEGER DEFAULT 0,
    total_duration_seconds INTEGER DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (artist_id) REFERENCES artists(artist_id)
);

Step 3: Create Tracks Table

Create a table for individual tracks.

CREATE TABLE tracks (
    track_id INTEGER PRIMARY KEY,
    album_id INTEGER NOT NULL,
    track_number INTEGER NOT NULL,
    title VARCHAR(255) NOT NULL,
    audio_file AUDIO(MP3),
    duration_seconds INTEGER,
    file_size BIGINT,
    play_count INTEGER DEFAULT 0,
    is_explicit BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (album_id) REFERENCES albums(album_id)
);

Step 4: Insert Sample Artists

Add sample artists to the catalog.

INSERT INTO artists (artist_id, name, genre, country, formed_year, is_active) VALUES
    (1, 'The Rock Band', 'Rock', 'USA', 2010, TRUE),
    (2, 'Electronic Dreams', 'Electronic', 'UK', 2015, TRUE),
    (3, 'Jazz Ensemble', 'Jazz', 'USA', 2005, TRUE),
    (4, 'Classical Masters', 'Classical', 'Germany', 1998, TRUE),
    (5, 'Pop Stars', 'Pop', 'USA', 2018, TRUE);

Step 5: Insert Sample Albums

Add albums for each artist.

INSERT INTO albums (album_id, artist_id, title, release_date, genre, label) VALUES
    -- The Rock Band albums
    (1, 1, 'First Impact', '2012-03-15', 'Rock', 'Rock Records'),
    (2, 1, 'Electric Thunder', '2015-07-20', 'Rock', 'Rock Records'),
    (3, 1, 'Acoustic Sessions', '2020-11-01', 'Rock', 'Rock Records'),

    -- Electronic Dreams albums
    (4, 2, 'Digital Horizons', '2016-05-10', 'Electronic', 'Synth Label'),
    (5, 2, 'Night Circuits', '2019-09-25', 'Electronic', 'Synth Label'),

    -- Jazz Ensemble albums
    (6, 3, 'Smooth Nights', '2008-02-14', 'Jazz', 'Jazz House'),
    (7, 3, 'Live at Blue Note', '2012-08-30', 'Jazz', 'Jazz House'),

    -- Classical Masters albums
    (8, 4, 'Symphony Collection', '2005-12-01', 'Classical', 'Classic Arts'),

    -- Pop Stars albums
    (9, 5, 'Summer Hits', '2020-06-15', 'Pop', 'Pop Central'),
    (10, 5, 'Midnight Dance', '2023-01-20', 'Pop', 'Pop Central');

Step 6: Insert Sample Tracks

Add tracks to the albums.

INSERT INTO tracks (track_id, album_id, track_number, title, duration_seconds, play_count, is_explicit) VALUES
    -- First Impact tracks
    (1, 1, 1, 'Opening Riff', 245, 15000, FALSE),
    (2, 1, 2, 'Highway Drive', 312, 28000, FALSE),
    (3, 1, 3, 'Night Rebel', 278, 22000, TRUE),
    (4, 1, 4, 'Final Stand', 356, 18000, FALSE),

    -- Electric Thunder tracks
    (5, 2, 1, 'Storm Coming', 198, 35000, FALSE),
    (6, 2, 2, 'Lightning Strike', 267, 42000, FALSE),
    (7, 2, 3, 'Thunder Road', 301, 38000, FALSE),

    -- Digital Horizons tracks
    (8, 4, 1, 'Synth Wave', 320, 25000, FALSE),
    (9, 4, 2, 'Digital Dream', 285, 31000, FALSE),
    (10, 4, 3, 'Neon Lights', 256, 29000, FALSE),

    -- Summer Hits tracks
    (11, 9, 1, 'Beach Party', 198, 55000, FALSE),
    (12, 9, 2, 'Sunshine', 212, 62000, FALSE),
    (13, 9, 3, 'Pool Side', 225, 48000, FALSE);

Step 7: Update Album Statistics

Calculate and update album totals.

UPDATE albums
SET total_tracks = (
    SELECT COUNT(*) FROM tracks WHERE tracks.album_id = albums.album_id
),
total_duration_seconds = (
    SELECT COALESCE(SUM(duration_seconds), 0) FROM tracks WHERE tracks.album_id = albums.album_id
);

SELECT album_id, title, total_tracks, total_duration_seconds / 60 as duration_minutes
FROM albums;

Step 8: Full Catalog Query

Get complete catalog with artist, album, and track info.

SELECT
    ar.name as artist,
    al.title as album,
    t.track_number,
    t.title as track_title,
    t.duration_seconds,
    t.play_count
FROM artists ar
INNER JOIN albums al ON ar.artist_id = al.artist_id
INNER JOIN tracks t ON al.album_id = t.album_id
ORDER BY ar.name, al.release_date, t.track_number;

Step 9: Most Played Tracks

Find the most popular tracks.

SELECT
    ar.name as artist,
    al.title as album,
    t.title as track,
    t.play_count
FROM tracks t
INNER JOIN albums al ON t.album_id = al.album_id
INNER JOIN artists ar ON al.artist_id = ar.artist_id
ORDER BY t.play_count DESC
LIMIT 10;

Step 10: Artist Statistics

Get statistics per artist.

SELECT
    ar.name,
    ar.genre,
    COUNT(DISTINCT al.album_id) as album_count,
    COUNT(t.track_id) as track_count,
    SUM(t.play_count) as total_plays,
    SUM(t.duration_seconds) / 60 as total_minutes
FROM artists ar
LEFT JOIN albums al ON ar.artist_id = al.artist_id
LEFT JOIN tracks t ON al.album_id = t.album_id
GROUP BY ar.artist_id, ar.name, ar.genre
ORDER BY total_plays DESC;

Step 11: Albums by Genre

Group albums by genre.

SELECT
    genre,
    COUNT(*) as album_count,
    COUNT(DISTINCT artist_id) as artist_count
FROM albums
GROUP BY genre
ORDER BY album_count DESC;

Step 12: Search Tracks

Search for tracks by title.

SELECT
    ar.name as artist,
    al.title as album,
    t.title as track,
    t.duration_seconds
FROM tracks t
INNER JOIN albums al ON t.album_id = al.album_id
INNER JOIN artists ar ON al.artist_id = ar.artist_id
WHERE t.title LIKE '%Night%'
   OR t.title LIKE '%Dream%'
ORDER BY t.play_count DESC;

Cleanup (Optional)

DROP TABLE IF EXISTS tracks;
DROP TABLE IF EXISTS albums;
DROP TABLE IF EXISTS artists;

Expected Outcomes

• Three-level hierarchy created
• Foreign keys link tables correctly
• Play counts tracked per track
• Album totals calculated
• Full catalog queries work

Key Concepts Learned

• Multi-table relationships
• Three-tier hierarchy (artist > album > track)
• Aggregate calculations across relations
• Updating computed columns
• Complex JOINs for catalog queries