UseCase003 Implementation - 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 browse the music catalog. The system retrieves release information together with associated artists, available physical products, release-specific information, and available genres so that users can explore the store's catalog and filter the displayed releases.

Scenario

1. User navigates to the store's catalog page to browse the available music releases.

2. System retrieves the releases from the database together with their general metadata. For every release, the system also checks whether it has a corresponding Album or Single Release record, retrieves the physical products associated with it, and retrieves the artists connected to the release. LEFT JOIN operations are used so that the release can still be retrieved even when some related information is not present.

SELECT
r.release_id,
r.cover_photo,
r.genre,
r.record_label,
r.release_date,
r.title,
a.release_id,
s.release_id,
p.product_id,
p.format,
p.price,
p.product_description,
p.release_id,
p.stock,
t.release_id,
t.artist_id,
t.release_ordinal,
t.type,
t.artist_id0,
t.artist_description,
t.artist_name,
t.artist_photo,
s.duration
FROM project.releases AS r
LEFT JOIN project.albums AS a
ON r.release_id = a.release_id
LEFT JOIN project.single_releases AS s
ON r.release_id = s.release_id
LEFT JOIN project.products AS p
ON r.release_id = p.release_id
LEFT JOIN (
SELECT
r0.release_id,
r0.artist_id,
r0.release_ordinal,
r0.type,
a0.artist_id AS artist_id0,
a0.artist_description,
a0.artist_name,
a0.artist_photo
FROM project.release_artists AS r0
INNER JOIN project.artists AS a0
ON r0.artist_id = a0.artist_id
) AS t
ON r.release_id = t.release_id
ORDER BY
r.title,
r.release_id,
a.release_id,
s.release_id,
p.product_id,
t.release_id,
t.artist_id;

3. As part of the same retrieval operation, the system obtains the physical products associated with each release, including their format, price, description, and current stock. It also obtains the artists associated with each release through the Release_Artists relationship, including the artist name and the ordinal used to determine the artist ordering.

4. System retrieves the distinct genres currently present in the Release table. These values can be used by the catalog interface to present genre-based filtering options to the user.

SELECT t.genre
FROM (
SELECT DISTINCT r.genre
FROM project.releases AS r
) AS t
ORDER BY t.genre;

5. System displays the retrieved releases to the user together with their cover images, associated artists, available product formats, prices, stock information, and relevant release information. The catalog can also use the retrieved genre values to allow the user to filter the displayed releases.