Conversation
Added Mermaid source (.mmd) and PNG image files for the Bookstore ER diagram as part of Cohort 8 Assignment 2. The diagram models entities such as Customer, Employee, Order, Book, Supplier, and related relationships.
Renamed and updated the ER diagram to include EMPLOYEE, SHIFT, and EMPLOYEE_SHIFT entities, along with their relationships. Removed the old diagram files and added updated versions reflecting the new structure.
Provided SQL schema examples for customer address storage using type 1 (overwrite) and type 2 (history) slowly changing dimension approaches. This clarifies the architectural options for tracking customer addresses.
…pulation Implemented queries for window functions including visit numbering, most recent visit filtering, and counting product purchases per customer. Added string manipulation queries to extract product descriptions and filter product sizes containing numbers.
…n to use customer count
| only the customer’s most recent visit. */ | ||
|
|
||
|
|
||
| CREATE TEMP TABLE temp_customer_visits AS |
There was a problem hiding this comment.
Good attempt. The question requires reversing numbering based on the distinct visit list from above. Here used the raw customer_purchases may cause duplicate rows and incorrect visit numbering. consider this:
WITH visits AS (
SELECT
customer_id,
market_date,
ROW_NUMBER() OVER (
PARTITION BY customer_id
ORDER BY market_date DESC
) AS visit_number
FROM (
SELECT DISTINCT customer_id, market_date
FROM customer_purchases
) x
)
SELECT customer_id, market_date
FROM visits
WHERE visit_number = 1;
|
|
||
| SELECT | ||
| product_name, | ||
| TRIM(SUBSTR(product_name, NULLIF(INSTR(product_name, '-'), 0) + 1)) AS description |
There was a problem hiding this comment.
If the product name has no hyphen, student formula incorrectly extracts the entire product_name instead of returning NULL, producing wrong descriptions.
CASE WHEN INSTR(product_name, '-') > 0 THEN
TRIM(SUBSTR(product_name, INSTR(product_name, '-') + 1))
ELSE NULL END
| SELECT | ||
| v.vendor_name, | ||
| p.product_name, | ||
| customer_count * 5 * vi.original_price AS total_revenue |
There was a problem hiding this comment.
SUM(5 * vi.original_price) AS total_revenue
What changes are you trying to make? (e.g. Adding or removing code, refactoring existing code, adding reports)
I added a comprehensive Entity-Relationship (ER) diagram for a full bookstore database system, including customers, employees, orders, books, suppliers, inventory transactions, and employee shifts. I also added two SQL schema designs for the CUSTOMER_ADDRESS table, demonstrating both Type 1 (overwrite) and Type 2 (historical) Slowly Changing Dimensions. These changes improve the documentation, clarify the data model, and provide alternative architectures for handling customer addresses.
What did you learn from the changes you have made?
Data Modeling & Design (Section 1)
Advanced SQL Techniques (Sections 2 & 3)
String Manipulation:
INSTR(),SUBSTR(), andTRIM()together to dynamically extract product descriptions after hyphensREGEXPfor pattern matching with numbersWindow Functions:
DENSE_RANK()andROW_NUMBER()for numbering customer visitsCOUNT() OVER (PARTITION BY ...)to calculate purchase frequency per customer-product combinationSet Operations:
RANK()window functions to identify best/worst performing daysUNIONto display highest and lowest sales datesData Manipulation:
CROSS JOINto create all possible vendor-product-customer combinationsINSERT,UPDATE,DELETEoperations with proper WHERE clausesALTER TABLEto add columns and update values using subqueries withCOALESCE()Key Takeaway: Learned to combine multiple SQL functions (window functions, CTEs, joins) to solve complex analytical problems without hardcoding values.
Was there another approach you were thinking about making? If so, what approach(es) were you thinking of?
I considered adding a separate CUSTOMER_ADDRESS_HISTORY table, but the Type 2 model with validity ranges was more normalized and matched common data-warehouse practices.
Were there any challenges? If so, what issue(s) did you face? How did you overcome it?
One challenge was deciding how to handle inventory updates without risking inconsistencies. Initially, I considered storing stock_on_hand directly in the BOOK table, but that approach does not track history and is error-prone. I resolved this by introducing an INVENTORY_TRANSACTION table to log each stock movement.
How were these changes tested?
I validated the ER diagram by checking entity relationships for cardinality correctness and ensuring no circular or unnormalized dependencies.
A reference to a related issue in your repository (if applicable)
Checklist