UseCase003 - Browse releases

Initiating actors:

• Unregistered Guest

• Logged-Out User

• Logged-In Consumer

• Logged-In Admin

The goal of this use case is to allow any visitor or authenticated user to explore the store's catalog. The system provides a comprehensive view of available music titles, artists, and specific release details, serving as the primary way for users to discover physical media before making a purchase.

Scenario

1. User navigates to the store's catalog or search page to view available music.

2. System retrieves the list of Releases from the database, including metadata such as title, genre, and record_label.

SELECT release_id, title, genre, record_label, release_date, cover_photo 
FROM RELEASES;

3. System matches each Release with its corresponding Artist.

SELECT 
    r.release_id,
    r.title, 
    STRING_AGG(a.artist_name, ', ' ORDER BY ra.release_ordinal) AS artist_names
FROM RELEASES r
JOIN RELEASE_ARTISTS ra ON r.release_id = ra.release_id
JOIN ARTISTS a ON ra.artist_id = a.artist_id
GROUP BY r.release_id, r.title;

4. System identifies the specific physical Product/s available for each release (such as Vinyl, CD, or Cassette) along with their current price and stock levels.

SELECT product_id, format, price, stock, product_description 
FROM PRODUCTS 
WHERE release_id = :current_release_id;

5. System displays the results to the user, formatted by the release type (Album or Single) and featuring the cover_photo.

SELECT release_id, 'Album' AS type FROM ALBUMS
UNION
SELECT release_id, 'Single' AS type FROM SINGLE_RELEASES;