Database Project # 1: Grocery Store Database

Database Project #1: Grocery Store Database
DatabaseName: GroupStoreProject

Project Description
Today, there are many grocery store and grocery list apps on the market. Our task was to exercise our creativity and create a relational database to support a grocery list website for a corresponding application. First, we created the data model containing entities named Category, Chain, List, List_Item, Product, Section, Shelf, Shelf_Inventory, Shopper, Shopper_List, Store, and Store_Inventory. These entities, their associated attributes, and the relationships between these entities allowed up to create 10 queries and use cases from our database. Our queries listed below, with their results, shall show the uniqueness of our design and depth
Sub-query
/*List the stores that are part of the Walmart chain/*
SELECT storename FROM Store WHERE chainid IN (SELECT chainid FROM Chain WHERE chainname = 'Walmart')

+ -------------- +
| storename
IN
/*List the product details including UPC code, manufacturer, MSRP, product description, and category id for Chick Breast and Whole Chicken*/
Which products include the product description Chick Breast or Whole Chicken?
SELECT * FROM Product WHERE pdescription IN ('Chick Breast', 'Whole Chicken')

+ ------------- + ------------------ + ---------- + ----------------- + --------------- +
| pUPCcode | pmanufacturer | pMSRP | pdescription | categoryid |
+ ------------- + ------------------ + ---------- + ----------------- + --------------- +
| 2000 | Perdue Chicken | 4.99 | Chick Breast | 5001 |
| 2003 | Perdue | 8.99 | Whole Chicken | 5001