Data Coding for pgadmin$4$

Part $2$ HG Database $($File $2)$

The second client is a local coffee shop called Holy Grounds. Complete the following steps for this client:

Note: All database names, attribute names, and attribute names must appear exactly as given below.

Create a database called holy$\_$grounds

Create the following tables based on the client$$s requirements:

coffee$\_$inventory: This table will store information about all items in inventory.

sku: An alphanumeric SKU

name: The brand and name of the coffee

roast$\_$type: The type of roast of each coffee $($Dark$,$Medium$,$ or $$Light$)$

lbs$\_$on$\_$hand: Total pounds of coffee on hand $($can be fractional$)$

organic: Only $2$ values, either organic or not

price$\_$per$\_$lb: price per pound of each coffee as a fractional value

sales$\_$transactions: This table contains records of each purchase made

receipt$\_$id: A number automatically assigned when a transaction is made.

While this is a smaller company, they do have multiple locations and online sales. They complete a total of approximately $250$ million transactions a year between these different revenue streams. This client likes to keep receipt records for multiple years.

sale$\_$amount: Amount of each purchase as a fractional value

sale$\_$type: The type of purchase made $($Online$$ or $$In$-$Store$)$

transaction$\_$date: The date and time a transaction occurs

Stores: This table hold information about all company stores where coffee is sold

store$\_$id: a numeric identifier automatically assigned when each store is

opened

store$\_$manager: The first and last name of each store manager.

store$\_$phone: Each store$$s phone number using format: $($xxx$)$ xxx$-$xxxx

store$\_$address: The store$$s physical address including street name and

number

city: city in which the store is located

zip: The store$$s $5-$digit zip code

state: two letter state abbreviation