Advanced Database Development

Procedures

New User Free Points Gift

This welcomes new shoppers. When a person makes a new account, this procedure checks that they are not making a fake double profile. Then, it automatically sets up their rewards page and gives them 50 free starter points to help them buy their first record.

Address and Phone Number Verifier

This procedure checks shipping info before orders are processed. It automatically scans user profiles and makes sure that the shipping addresses and phone numbers are not blank or broken. If an address is missing, it stops the order and flags it for a manual fix so the package does not get lost.

"HOT" Item Flagging

This procedure watches how fast items sell. If a certain vinyl or CD suddenly sells a lot of copies (for example, more than 50 in a single week), it automatically flags it as a "HOT" item. It creates a note for the admins so they can put it on the front page of the website.

Triggers

Double Promo Blocker

This keeps customers from using multiple promotions in a single order. If someone tries to buy a product that is already on sale, this trigger stops them from using their store loyalty points on that same order. It forces the system to only allow one discount type at a time so the store doesn't lose money.

Price Drop Alert

When a product manager drops the price of a product, this trigger would notify users that have that same product in their wishlists. It scans the wishlists of all users to see who has been eyeing that specific item. For every enthusiast found, it automatically writes a notification record into the system tables so the application knows exactly who to alert or email about the price cut.

Views

Detailed View of a Release

Description: Combines the entire metadata of a music release into one detailed overview. For any given release, it aggregates its main artists, features, tracks, total track duration, and provides a real-time summary breakdown of available warehouse stock and pricing for all physical formats simultaneously.

Implementation:

DROP VIEW IF EXISTS project.detailed_release_view;

CREATE VIEW project.detailed_release_view AS
WITH main_artists AS (
    SELECT
        ra.release_id,
        string_agg(a.artist_name, ', ' ORDER BY ra.release_ordinal) AS main_artists
    FROM project.release_artists ra
    JOIN project.artists a ON a.artist_id = ra.artist_id
    WHERE ra.type = 'MAIN'
    GROUP BY ra.release_id
),
album_tracks AS (
    SELECT
        al.release_id,
        jsonb_agg(
            jsonb_build_object(
                'song_name', s.song_name,
                'duration', s.song_duration,
                'features', COALESCE(sf.features, ' ')
            )
            ORDER BY s.song_id
        ) AS tracks,
        SUM(
            split_part(s.song_duration, ':', 1)::INT * INTERVAL '1 minute'
            + split_part(s.song_duration, ':', 2)::INT * INTERVAL '1 second'
        ) AS total_duration
    FROM project.albums al
    JOIN project.album_songs als ON als.album_id = al.release_id
    JOIN project.songs s ON s.song_id = als.song_id
    LEFT JOIN (
        SELECT
            sa.song_id,
            string_agg(a.artist_name, ', ' ORDER BY sa.song_ordinal) AS features
        FROM project.song_artists sa
        JOIN project.artists a ON a.artist_id = sa.artist_id
        WHERE sa.song_ordinal > 1
        GROUP BY sa.song_id
    ) sf ON sf.song_id = s.song_id
    GROUP BY al.release_id
),
single_tracks AS (
    SELECT
        sr.release_id,
        jsonb_build_array(
            jsonb_build_object(
                'song_name', r.title,
                'duration', sr.duration,
                'features', COALESCE(sf.features, 'No features')
            )
        ) AS tracks,
        split_part(sr.duration, ':', 1)::INT * INTERVAL '1 minute'
        + split_part(sr.duration, ':', 2)::INT * INTERVAL '1 second' AS total_duration
    FROM project.single_releases sr
    JOIN project.releases r ON r.release_id = sr.release_id
    LEFT JOIN (
        SELECT
            ra.release_id,
            string_agg(a.artist_name, ', ' ORDER BY ra.release_ordinal) AS features
        FROM project.release_artists ra
        JOIN project.artists a ON a.artist_id = ra.artist_id
        WHERE ra.type = 'FEATURE'
        GROUP BY ra.release_id
    ) sf ON sf.release_id = sr.release_id
),
product_summary AS (
    SELECT
        p.release_id,
        jsonb_agg(
            jsonb_build_object(
                'format', p.format,
                'price', p.price,
                'stock', p.stock,
                'description', p.product_description
            )
            ORDER BY p.format
        ) AS physical_formats
    FROM project.products p
    GROUP BY p.release_id
)
SELECT
    r.title,
    r.record_label,
    r.genre,
    r.release_date,
    r.cover_photo,
    CASE
        WHEN al.release_id IS NOT NULL THEN 'ALBUM'
        ELSE 'SINGLE'
    END AS release_type,
    ma.main_artists,
    COALESCE(at.tracks, st.tracks) AS tracks,
    COALESCE(at.total_duration, st.total_duration) AS total_track_duration,
    ps.physical_formats
FROM project.releases r
LEFT JOIN project.albums al ON al.release_id = r.release_id
LEFT JOIN main_artists ma ON ma.release_id = r.release_id
LEFT JOIN album_tracks at ON at.release_id = r.release_id
LEFT JOIN single_tracks st ON st.release_id = r.release_id
LEFT JOIN product_summary ps ON ps.release_id = r.release_id;

Top 10 Highest Selling Products

Description: A detailed analytical view that uses a the previous 7-day period to show the Top 10 best-selling products. It combines data from multiple tables, calculates the total quantity sold, excludes cancelled orders, and sorts the products from highest to lowest selling.

Implementation:

DROP VIEW IF EXISTS project.top_10_highest_selling_products;

CREATE VIEW project.top_10_highest_selling_products AS
SELECT
    p.format,
    p.product_description,
    r.title AS release_title,
    r.genre,
    r.record_label,
    r.release_date,
    SUM(op.quantity) AS total_quantity_sold,
    SUM(op.quantity * op.price_at_purchase) AS total_revenue
FROM project.order_products op
JOIN project.orders o ON o.order_id = op.order_id
JOIN project.products p ON p.product_id = op.product_id
JOIN project.releases r ON r.release_id = p.release_id
WHERE o.purchase_date >= CURRENT_DATE - INTERVAL '7 days'
  AND o.purchase_date < CURRENT_DATE
  AND o.status <> 'CANCELLED'
GROUP BY
    p.product_id,
    p.format,
    p.product_description,
    r.title,
    r.genre,
    r.record_label,
    r.release_date
ORDER BY total_quantity_sold DESC, total_revenue DESC
LIMIT 10;