Members: Ben Kim, Jackson Avey, Christian Hertzig, Success Onichabor, Drew Vajda
- Second rendition of the SQL project to include: more complex queries as well as visualizations
The task at hand is to model and build a relational database for the general operations of a network of bike stores. The central entity in the model is the Store entity, representing each individual location where bikes and related products are sold. Each store manages its stock of products, including details such as quantity, category, and brand. The system also tracks orders placed by customers, consisting of multiple order items tied to specific products. Additionally, employees are associated with each store to manage sales and inventory. The product detail entity provides descriptive information about each item, while the categories and brands entities classify and group products effectively. Our goal is to accurately model these relationships, populate the database with sample data, and perform queries that yield useful insights into sales performance, inventory management, and customer purchasing trends across all stores.
Explanation: The Bike Store data model captures how products, customers, employees, and orders interact across multiple store locations. It is built around a set of well-defined relationships that mirror the real-world structure of a retail business. At the heart of the design is the Product table, which connects to several entities. Each product belongs to exactly one Brand and one Category, forming two one-to-many relationships from Product to Brand and Category. This means a single brand or category can be associated with many products, but each product can only have one brand and one category. The Product_Detail table is linked to Product through a one-to-one relationship because each product has a unique set of detailed attributes such as model year and list price that apply to that specific item only. Sales transactions are modeled through the Orders and Order_Item tables. The Orders table connects to Customer in a one-to-many relationship since a single customer can place many orders, but each order belongs to only one customer. Each Order can also have multiple items, leading to a one-to-many relationship between Orders and Order_Item. On the other side, each Order_Item references a single Product, but a product can appear in many order items over time, forming another one-to-many relationship from Order_Item to Product. The Order_Item table serves as an associative entity, enabling a many-to-many conceptual link between Orders and Product while storing key transactional attributes like quantity, discount, and price at the item level. Inventory and store management are supported by the Stores, Stock, and Employee tables. The Stock table connects Stores and Product in a many-to-many relationship implemented through Stock as a bridge entity. Each store carries many products, and each product can be stocked in multiple stores, with quantity levels stored in Stock. The Employee table links to Stores in a one-to-many relationship, showing that multiple employees work in one store. It also has a self-referencing relationship where an employee can manage other employees, modeling a managerial hierarchy within the same table. Finally, Orders connect back to Stores indirectly through Employees and Customers, ensuring that every transaction can be traced to both a customer and a physical location. Overall, this model efficiently supports business operations such as tracking sales, managing inventory, and organizing staff responsibilities. The chosen relationship types ensure data consistency — for example, every product detail ties to exactly one product, while inventory and sales can scale across multiple stores and customers. This balance of one-to-one, one-to-many, and many-to-many structures provides both normalization and flexibility for future business growth.
SELECT
product.product_id,
product.product_name,
stock.quantity,
MAX(orders.order_date) AS last_order_date
FROM stock
JOIN product
ON stock.product_id = product.product_id
LEFT JOIN order_item
ON product.product_id = order_item.product_id
LEFT JOIN orders
ON order_item.orders_id = orders.order_id
WHERE
stock.store_id = 1
AND stock.quantity > 0
GROUP BY
product.product_id,
product.product_name,
stock.quantity
ORDER BY
last_order_date DESC;CREATE VIEW ProductPrices AS
SELECT
product.product_id,
product.product_name,
brand.brand_name,
product_details.list_price
FROM product
JOIN brand
ON product.brand_id = brand.brand_id
JOIN product_details
ON product_details.product_id = product.product_id;
SELECT *
FROM product_details
WHERE list_price > 500
ORDER BY list_price DESC;SELECT
customer.customer_id,
customer.first_name,
customer.last_name,
COUNT(orders.order_id) AS TotalOrders
FROM customer
JOIN orders
ON orders.customer_id = customer.customer_id
GROUP BY
customer.customer_id,
customer.first_name,
customer.last_name
HAVING COUNT(orders.order_id) >= 2
ORDER BY TotalOrders DESC;SELECT
stores.stores_id AS storeID,
stores.store_name,
COUNT(DISTINCT stock.product_id) AS number_of_products_in_stock
FROM stores
JOIN stock
ON stores.stores_id = stock.store_id
GROUP BY
stores.stores_id,
stores.store_name
HAVING COUNT(DISTINCT stock.product_id) > 20
ORDER BY number_of_products_in_stock DESC;SELECT
product.product_id,
product.product_name,
SUM(order_item.quantity) AS total_units_sold,
SUM(order_item.quantity * product_details.list_price) AS total_revenue
FROM product
JOIN product_details
ON product.product_id = product_details.product_id
JOIN order_item
ON product.product_id = order_item.product_id
WHERE
order_item.quantity > 0
GROUP BY
product.product_id,
product.product_name
ORDER BY total_revenue DESC;| Database Information | Query 1 | Query 2 | Query 3 | Query 4 | Query 5 |
|---|---|---|---|---|---|
| Multiple table join | x | x | x | x | |
| Create view | x | ||||
| Group By, Aggregation | x | x | x | x | |
| Join | x | x | x | x | x |
The query starts by joining stock and product so we can see exactly which items a specific store carries and the quantity currently available. It then connects order_item and orders to each product, which lets us pull the most recent order date for every item that has ever been purchased. The query links stock with product to show which items the store has and how many are available. It also joins order_item and orders so we can see the most recent time each product was ordered. This helps the business track what’s selling, what’s sitting, and when to reorder.
This query creates a view that pulls together product IDs, names, brands, and list prices from the product, brand, and product_details tables. After defining the view, I run a query to show only the products priced over 500 so I can quickly see the higher-priced items. This helps the business identify premium products and compare pricing across different brands.
This query joins customer and orders to find customers who have placed at least two orders. By grouping by each customer and counting their orders, it returns the customer ID, first name, last name, and total number of orders for the most active shoppers. This helps the business quickly spot loyal customers for rewards or targeted marketing.
This query joins stores and stock to count how many distinct products each store has in stock. It filters to only show stores with more than 20 different products and orders them from the most stocked to the least. This helps the business compare store inventory levels and see which locations carry the widest product range.
This query joins product, product_details, and order_item to calculate total units sold and total revenue for each product. It only includes rows where quantity sold is positive and then sorts products from highest to lowest revenue. This lets the business easily see top-earning products and make smarter decisions about pricing, promotions, and inventory.
Description:
- Chart showcases the total sales revenue generated by each bike type. Helps managers understand which bike types contribute the most to overall sales revenue.
Relevance:
- By understanding the distribution of revenue amongst bike types, it can help upper management identify which product lines generate the most sales. This can later go on to inform data-driven decisions such as: inventory prioritization, marketing tactics, etc.
Description:
- Chart shows the total amount of inventory held for each bike type.
Relevance:
- Upper management, as well as store owners, can make informed decisions by evaluating the total stock of each bike type with important metrics such as revenue and # of orders --> customer demand. For example, a bike type with higher stock but lower revenue generated can help store owners optimize their stock.
Description:
- Chart shows the total number of orders placed by customers for each bike type.
Relevance:
- This chart is especially important in highlighting purchase trends across the different bike offerings. Volume of orders can help provide insight into market/customer trends. Furthermore, by comparing total # of orders to metrics such as revenue and inventory, store owners can identify top-selling types and match the current market better.
Which product categories generate the most total revenue?
Which stores carry the highest number of unique products, and which carry the least?
How many customers place multiple orders compared to those who only order once?
The Name of the Database is: cs_bck81809