DEMO S.Q.L : PROJECT THREE.

Building a Complete Shopping System with SQL | Full Project Walkthrough PostgreSQL | pgAdmin 4 | DDL | Cart Operations | Checkout | | Functions WELCOME TO PROJECT THREE how does an e-commerce platform manages its products, shopping carts, and orders behind the scenes, this is exactly the kind of database that powers it. Every time you add something to your cart on an online store, update the quantity, remove an item, or check out, there is a database doing exactly that WHAT THIS PROJECT COVERS The project is divided into five clearly defined parts. We start by designing the database schema based on a UML class diagram, which is a critical skill because in the real world you will almost always receive a diagram and be expected to translate it into working SQL. The schema consists of five tables: Products, Users, Cart, OrderHeader, and OrderDetails. Before we can test anything, we insert two products, Coke and Chips, and two users, Arnold and Sheryl. Keeping the seed data simple lets us focus on the logic rather than getting lost in large datasets. This is where things get interesting because we simulate a real shopping cart by writing conditional SQL logic. CART OPERATIONS The add-to-cart logic follows a simple decision tree. First we check whether the product already exists in the Cart. If yes, we UPDATE the existing row and increment qty by one. If no, we INSERT a new row with qty set to one. This pattern is sometimes called anUPSERT, a combination of UPDATE and INSERT. The Cart table uses product_id as its primary key, which enforces the rule that each product can only appear once, and we manage quantity through the qty column rather than inserting duplicate rows. A CHECK constraint ensures the quantity is always greater than zero. The remove-from-cart logic is slightly more nuanced. We check the current qty of the product. If qty is greater than one we UPDATE and decrement qty by one. If qty equals one we DELETE the entire row from the Cart. The reason we delete the row entirely when qty reaches one is that the CHECK constraint requires qty to be above zero. If we simply decremented to zero, the constraint would reject the update. More importantly, it makes no sense to keep a cart row for a product with zero quantity. CHECKOUT PROCESS The checkout process is where the cart becomes a permanent order. This is one of the most important concepts in e-commerce database design, the separation between a temporary cart and a permanent order record. First we reset the cart using TRUNCATE and re-insert a clean set of items. Then we INSERT a new order header record for the user who is checking out. The database auto-generates the order_id and we retrieve it using currval on the sequence. Next we use an INSERT SELECT statement to copy every row from the Cart table into OrderDetails. This is an elegant pattern because instead of inserting rows one by one, we select all cart rows and insert them in a single operation. Once the cart contents have been safely copied into OrderDetails, we DELETE all rows from the Cart. We repeat the entire process for a second user to demonstrate that the system correctly handles multiple users and multiple orders. REPORTING WITH INNER JOINS With orders in the database, we write four reporting queries using INNER JOINs across three tables. The first report retrieves every order line across all orders and joins four tables: OrderHeader, Users, OrderDetails, and Products. The join path flows from OrderHeader to Users via user_id, from OrderHeader to OrderDetails via order_id, and from OrderDetails to Products via prod_id. The second report adds a WHERE clause to filter results to a single order, useful for generating a receipt. The third report filters by date rather than order ID, retrieving all orders placed on a given day. The fourth report uses GROUP BY and SUM to produce a summary of the total value of each order, collapsing each order into one row showing the order ID, username, date, and total order value. STORED FUNCTIONS The bonus section elevates this project closer to a production system. We convert our cart logic into two reusable PostgreSQL stored functions: add_to_cart and remove_from_cart. Stored functions are powerful because they encapsulate logic inside the database, meaning any application that connects can use the same logic without duplicating code. They also reduce the number of round trips between the application and the database. TOOLS USED PostgreSQL, pgAdmin 4, SQL covering DDL and DML, PL/pgSQL for stored functions, and UML class diagrams for schema design. CHAPTERS GitHub : https://github.com/kabeloxabendlini Portfolio : https://kabeloxabendlini.github.io/Kabelo_Xabendlini_Repository_Portfolio_For_My_Online_Profile/ Project 1 : Employee Database with PostgreSQL Project 2 : Dating Database with Many to Many Tables #PostgreSQL #SQL #ShoppingCart #DatabaseDesign #SQLProject #pgAdmin #StoredFunctions #PLpgSQL #TechPortfolio #Developer