Audiobook Chapter Management

Objective

Create a system for managing audiobooks with chapters organized sequentially. This enables building audiobook players with proper navigation and progress tracking.

Step 1: Create Audiobooks Table

Create a table for audiobook metadata.

CREATE TABLE audiobooks (
    book_id INTEGER PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    author VARCHAR(255) NOT NULL,
    narrator VARCHAR(255),
    genre VARCHAR(50),
    description TEXT,
    cover_image IMAGE(JPEG),
    total_chapters INTEGER DEFAULT 0,
    total_duration_seconds INTEGER DEFAULT 0,
    publication_year INTEGER,
    isbn VARCHAR(20),
    language VARCHAR(10) DEFAULT 'en',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Step 2: Create Chapters Table

Create a table for individual chapters.

CREATE TABLE audiobook_chapters (
    chapter_id INTEGER PRIMARY KEY,
    book_id INTEGER NOT NULL,
    chapter_number INTEGER NOT NULL,
    title VARCHAR(255) NOT NULL,
    audio_file AUDIO(MP3),
    duration_seconds INTEGER,
    file_size BIGINT,
    start_position INTEGER DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (book_id) REFERENCES audiobooks(book_id)
);

Step 3: Insert Sample Audiobooks

Add sample audiobook records.

INSERT INTO audiobooks (book_id, title, author, narrator, genre, description, publication_year, isbn) VALUES
    (1, 'The Art of Programming', 'Jane Developer', 'Mike Voice', 'Technology', 'A comprehensive guide to software development best practices', 2023, '978-1234567890'),
    (2, 'Business Leadership', 'John Executive', 'Sarah Speaker', 'Business', 'Leadership strategies for the modern workplace', 2022, '978-0987654321'),
    (3, 'Mystery at Midnight', 'Alice Writer', 'Tom Narrator', 'Fiction', 'A thrilling mystery novel set in Victorian London', 2024, '978-1122334455'),
    (4, 'Healthy Living Guide', 'Dr. Health Expert', 'Lisa Voice', 'Health', 'Practical tips for improving your daily wellness', 2023, '978-5544332211');

Step 4: Insert Chapters

Add chapters for each audiobook.

INSERT INTO audiobook_chapters (chapter_id, book_id, chapter_number, title, duration_seconds, file_size) VALUES
    -- The Art of Programming chapters
    (1, 1, 1, 'Introduction to Programming', 1200, 19200000),
    (2, 1, 2, 'Variables and Data Types', 1800, 28800000),
    (3, 1, 3, 'Control Flow', 1500, 24000000),
    (4, 1, 4, 'Functions and Methods', 2100, 33600000),
    (5, 1, 5, 'Object-Oriented Programming', 2400, 38400000),
    (6, 1, 6, 'Best Practices', 1650, 26400000),

    -- Business Leadership chapters
    (7, 2, 1, 'What Makes a Leader', 900, 14400000),
    (8, 2, 2, 'Building Your Team', 1200, 19200000),
    (9, 2, 3, 'Communication Skills', 1350, 21600000),
    (10, 2, 4, 'Decision Making', 1100, 17600000),
    (11, 2, 5, 'Leading Through Change', 1500, 24000000),

    -- Mystery at Midnight chapters
    (12, 3, 1, 'A Dark Beginning', 1800, 28800000),
    (13, 3, 2, 'The First Clue', 2100, 33600000),
    (14, 3, 3, 'Suspects Emerge', 1950, 31200000),
    (15, 3, 4, 'The Plot Thickens', 2250, 36000000),
    (16, 3, 5, 'The Revelation', 2400, 38400000),

    -- Healthy Living Guide chapters
    (17, 4, 1, 'Nutrition Basics', 1000, 16000000),
    (18, 4, 2, 'Exercise Fundamentals', 1200, 19200000),
    (19, 4, 3, 'Sleep and Recovery', 900, 14400000),
    (20, 4, 4, 'Mental Wellness', 1100, 17600000);

Step 5: Calculate Start Positions

Update cumulative start positions for navigation.

UPDATE audiobook_chapters ac
SET start_position = (
    SELECT COALESCE(SUM(duration_seconds), 0)
    FROM audiobook_chapters prev
    WHERE prev.book_id = ac.book_id
      AND prev.chapter_number < ac.chapter_number
);

SELECT
    chapter_number,
    title,
    duration_seconds,
    start_position,
    start_position + duration_seconds as end_position
FROM audiobook_chapters
WHERE book_id = 1
ORDER BY chapter_number;

Step 6: Update Audiobook Totals

Calculate and update book totals.

UPDATE audiobooks
SET total_chapters = (
    SELECT COUNT(*) FROM audiobook_chapters WHERE audiobook_chapters.book_id = audiobooks.book_id
),
total_duration_seconds = (
    SELECT COALESCE(SUM(duration_seconds), 0) FROM audiobook_chapters WHERE audiobook_chapters.book_id = audiobooks.book_id
);

SELECT
    title,
    author,
    total_chapters,
    total_duration_seconds / 3600 as hours,
    (total_duration_seconds % 3600) / 60 as minutes
FROM audiobooks;

Step 7: Get Full Book Contents

Retrieve complete chapter listing for a book.

SELECT
    a.title as book_title,
    a.author,
    c.chapter_number,
    c.title as chapter_title,
    c.duration_seconds / 60 as duration_minutes
FROM audiobooks a
INNER JOIN audiobook_chapters c ON a.book_id = c.book_id
WHERE a.book_id = 1
ORDER BY c.chapter_number;

Step 8: Find Chapter by Position

Locate chapter for a given playback position.

SELECT
    chapter_number,
    title,
    start_position,
    start_position + duration_seconds as end_position
FROM audiobook_chapters
WHERE book_id = 1
  AND start_position <= 5000
  AND (start_position + duration_seconds) > 5000;

Step 9: Library Statistics

Generate library-wide statistics.

SELECT
    genre,
    COUNT(*) as book_count,
    SUM(total_chapters) as total_chapters,
    SUM(total_duration_seconds) / 3600 as total_hours
FROM audiobooks
GROUP BY genre
ORDER BY total_hours DESC;

Step 10: Longest and Shortest Books

Find books by duration.

SELECT
    title,
    author,
    total_chapters,
    total_duration_seconds / 3600 as hours,
    CASE
        WHEN total_duration_seconds > 36000 THEN 'Long (10+ hours)'
        WHEN total_duration_seconds > 18000 THEN 'Medium (5-10 hours)'
        ELSE 'Short (< 5 hours)'
    END as length_category
FROM audiobooks
ORDER BY total_duration_seconds DESC;

Step 11: Chapter Length Analysis

Analyze chapter durations across books.

SELECT
    a.title as book,
    AVG(c.duration_seconds) / 60 as avg_chapter_minutes,
    MIN(c.duration_seconds) / 60 as shortest_chapter,
    MAX(c.duration_seconds) / 60 as longest_chapter
FROM audiobooks a
INNER JOIN audiobook_chapters c ON a.book_id = c.book_id
GROUP BY a.book_id, a.title
ORDER BY avg_chapter_minutes DESC;

Step 12: Navigation Query

Build chapter navigation data.

SELECT
    chapter_number,
    title,
    duration_seconds / 60 as duration_minutes,
    start_position / 60 as start_minute,
    (start_position + duration_seconds) / 60 as end_minute,
    CASE
        WHEN chapter_number = 1 THEN NULL
        ELSE chapter_number - 1
    END as prev_chapter,
    CASE
        WHEN chapter_number = (SELECT MAX(chapter_number) FROM audiobook_chapters WHERE book_id = 1) THEN NULL
        ELSE chapter_number + 1
    END as next_chapter
FROM audiobook_chapters
WHERE book_id = 1
ORDER BY chapter_number;

Cleanup (Optional)

DROP TABLE IF EXISTS audiobook_chapters;
DROP TABLE IF EXISTS audiobooks;

Expected Outcomes

• Audiobooks with sequential chapters
• Start positions enable navigation
• Total durations calculated
• Chapter lookup by position works
• Library statistics available

Key Concepts Learned

• Sequential chapter ordering
• Cumulative position calculations
• Book-chapter relationships
• Position-based navigation
• Library analytics queries