Store Database

Only available on StudyMode
  • Topic: Rye bread, Bread, Rye
  • Pages : 11 (1582 words )
  • Download(s) : 28
  • Published : October 24, 2012
Open Document
Text Preview
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 of our knowledge in SQL as well as allow shoppers to determine things like which items are offered within a particular chain, how much inventory in a store near them is available for purchase, and also the best coupon deals by store.

Queries & Results

1. Multiple Table Join (with an Order By)
/*List the products that are carried by the Publix-Decatur store in order of product description*/ SELECT Store.storeid, storename, Store_Inventory.pUPCcode, pdescription
FROM Store, Store_Inventory, Product
WHERE Store.storeid = Store_Inventory.storeid
AND Store_Inventory.pUPCcode = Product.pUPCcode
AND storename = 'Publix-Decatur'
ORDER BY pdescription

+ ------------ + -------------- + ------------- + ----------------- + | storeid | storename | pUPCcode | pdescription | + ------------ + -------------- + ------------- + ----------------- + | 1 | Publix-Decatur | 2013 | Apple | | 1 | Publix-Decatur | 2000 | Chick Breast | | 1 | Publix-Decatur | 2017 | Cod | | 1 | Publix-Decatur | 2005 | Rye Loaf of Bread | | 1 | Publix-Decatur | 2011 | Tomato | | 1 | Publix-Decatur | 2009 | Water Melon | | 1 | Publix-Decatur | 2003 | Whole Chicken | | 1 | Publix-Decatur | 2015 | Whole Live Lobster | | 1 | Publix-Decatur | 2002 | Whole Turkey | | 1 | Publix-Decatur | 2007 | Whole Wheat Hot Dog Buns | + ------------ + -------------- + ------------- + ----------------- + 10 rows

2. 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 |
+ -------------- +
| Walmart-New York |
+ -------------- +
1 rows

3. Correlated Sub-query
/*Find those stores (including city) that have products where the quantity on the shelf is greater than the average of all product inventory*/ SELECT storename, storecity, Product.pUPCcode, pdescription, quantity FROM Store, Product, Shelf_Inventory

WHERE Product.pUPCcode = Shelf_Inventory.pUPCcode
AND quantity >
(SELECT AVG(quantity) FROM Shelf_Inventory
WHERE Product.pUPCcode = Shelf_Inventory.pUPCcode)

+ -------------- + -------------- + ------------- + ----------------- + ------------- + | storename | storecity | pUPCcode | pdescription | quantity | + -------------- + -------------- + ------------- + ----------------- + ------------- + | Publix-Decatur | Decatur | 2002 | Whole Turkey | 13 | | Kroger-St. Louis | St. Louis | 2002 | Whole Turkey | 13 | | Walmart-New York | New York | 2002 | Whole Turkey | 13 | | Ingles-Baton Rouge | Baton Rouge | 2002 | Whole Turkey | 13 | | Target-San Diego | San Diego | 2002 | Whole Turkey | 13 | | Publix-Decatur | Decatur | 2003 | Whole Chicken | 33 | |...
tracking img