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

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.

Top 10 Highest Selling Products

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.