Changes between Version 5 and Version 6 of AdvancedDatabaseDevelopment
- Timestamp:
- 06/05/26 14:56:46 (7 days ago)
Legend:
- Unmodified
- Added
- Removed
- Modified
-
AdvancedDatabaseDevelopment
v5 v6 6 6 7 7 {{{#!div style="text-align: justify; width: 100%;" 8 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. 9 }}} 8 * **Description:** 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. 9 }}} 10 * **Implementation:** 10 11 11 12 ==== Address and Phone Number Verifier 12 13 13 14 {{{#!div style="text-align: justify; width: 100%;" 14 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. 15 }}} 15 * **Description:** 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. 16 }}} 17 * **Implementation:** 16 18 17 19 ==== "HOT" Item Flagging 18 20 19 21 {{{#!div style="text-align: justify; width: 100%;" 20 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. 21 }}} 22 * **Description:** 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. 23 }}} 24 * **Implementation:** 22 25 23 26 == Triggers … … 26 29 27 30 {{{#!div style="text-align: justify; width: 100%;" 28 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. 29 }}} 31 * **Description:** 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. 32 }}} 33 * **Implementation:** 30 34 31 35 ==== Price Drop Alert 32 36 33 37 {{{#!div style="text-align: justify; width: 100%;" 34 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. 38 * **Description:** 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. 39 }}} 40 * **Implementation:** 41 42 First, we need to create a table that will be used to store information about users who should be notified when a product's price decreases. 43 44 {{{ 45 DROP TABLE IF EXISTS project.price_drop_notifications; 46 47 CREATE TABLE project.price_drop_notifications ( 48 notification_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, 49 user_id BIGINT NOT NULL REFERENCES project.users(user_id) ON DELETE CASCADE, 50 user_email VARCHAR(255) NOT NULL, 51 product_id BIGINT NOT NULL REFERENCES project.products(product_id) ON DELETE CASCADE, 52 old_price NUMERIC NOT NULL, 53 new_price NUMERIC NOT NULL, 54 message TEXT NOT NULL, 55 created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, 56 is_read BOOLEAN NOT NULL DEFAULT FALSE, 57 is_email_sent BOOLEAN NOT NULL DEFAULT FALSE 58 ); 59 }}} 60 61 Then, we need to create a trigger function that defines the logic that automatically identifies interested users and creates notification records whenever a product becomes cheaper. 62 63 {{{ 64 CREATE OR REPLACE FUNCTION project.create_price_drop_notifications() 65 RETURNS TRIGGER AS $$ 66 BEGIN 67 INSERT INTO project.price_drop_notifications 68 ( 69 user_id, 70 user_email, 71 product_id, 72 old_price, 73 new_price, 74 message 75 ) 76 SELECT 77 u.user_id, 78 u.email, 79 NEW.product_id, 80 OLD.price, 81 NEW.price, 82 'Price dropped for product: ' || NEW.product_description || 83 '. Old price: ' || OLD.price || 84 ', new price: ' || NEW.price || '.' 85 FROM project.wishlists w 86 JOIN project.users u 87 ON u.user_id = w.user_id 88 JOIN project.wishlist_products wp 89 ON wp.wishlist_id = w.wishlist_id 90 WHERE wp.product_id = NEW.product_id; 91 92 RETURN NEW; 93 END; 94 $$ LANGUAGE plpgsql; 95 }}} 96 97 Finally, the trigger attaches the notification logic to the products table so it executes automatically after a price reduction occurs. 98 99 {{{ 100 DROP TRIGGER IF EXISTS trg_price_drop_alert ON project.products; 101 102 CREATE TRIGGER trg_price_drop_alert 103 AFTER UPDATE OF price ON project.products 104 FOR EACH ROW 105 WHEN (NEW.price < OLD.price) 106 EXECUTE FUNCTION project.create_price_drop_notifications(); 35 107 }}} 36 108 … … 40 112 41 113 {{{#!div style="text-align: justify; width: 100%;" 42 **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. 43 }}} 44 **Implementation: 114 * **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. 115 }}} 116 * **Implementation: 117 45 118 {{{ 46 119 DROP VIEW IF EXISTS project.detailed_release_view; … … 149 222 150 223 {{{#!div style="text-align: justify; width: 100%;" 151 **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. 152 }}} 153 **Implementation:** 224 * **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. 225 }}} 226 * **Implementation:** 227 154 228 {{{ 155 229 DROP VIEW IF EXISTS project.top_10_highest_selling_products;
