This is the multi-page printable view of this section. Click here to print.
The store schema is a snowflake schema that contains information about the retail chain’s bricks-and-mortar stores. The following graphic illustrates the store schema and its relationship with tables in the public schema.
The subsequent subsections describe database tables.
This table contains information about all orders made at the company’s brick-and-mortar stores.
| Column Name | Data Type | NULLs |
product_key |
INTEGER | No |
product_version |
INTEGER | No |
store_key |
INTEGER | No |
vendor_key |
INTEGER | No |
employee_key |
INTEGER | No |
order_number |
INTEGER | No |
date_ordered |
DATE | Yes |
date_shipped |
DATE | Yes |
expected_delivery_date |
DATE | Yes |
date_delivered |
DATE | Yes |
quantity_ordered |
INTEGER | Yes |
quantity_delivered |
INTEGER | Yes |
shipper_name |
VARCHAR(32) | Yes |
unit_price |
INTEGER | Yes |
shipping_cost |
INTEGER | Yes |
total_order_cost |
INTEGER | Yes |
quantity_in_stock |
INTEGER | Yes |
reorder_level |
INTEGER | Yes |
overstock_ceiling |
INTEGER | Yes |
This table contains information about all sales made at the company’s brick-and-mortar stores.
| Column Name | Data Type | NULLs |
date_key |
INTEGER | No |
product_key |
INTEGER | No |
product_version |
INTEGER | No |
store_key |
INTEGER | No |
promotion_key |
INTEGER | No |
customer_key |
INTEGER | No |
employee_key |
INTEGER | No |
pos_transaction_number |
INTEGER | No |
sales_quantity |
INTEGER | Yes |
sales_dollar_amount |
INTEGER | Yes |
cost_dollar_amount |
INTEGER | Yes |
gross_profit_dollar_amount |
INTEGER | Yes |
transaction_type |
VARCHAR(16) | Yes |
transaction_time |
TIME | Yes |
tender_type |
VARCHAR(8) | Yes |
This table contains information about each brick-and-mortar store within the retail chain.
| Column Name | Data Type | NULLs |
store_key |
INTEGER | No |
store_name |
VARCHAR(64) | Yes |
store_number |
INTEGER | Yes |
store_address |
VARCHAR(256) | Yes |
store_city |
VARCHAR(64) | Yes |
store_state |
CHAR(2) | Yes |
store_region |
VARCHAR(64) | Yes |
floor_plan_type |
VARCHAR(32) | Yes |
photo_processing_type |
VARCHAR(32) | Yes |
financial_service_type |
VARCHAR(32) | Yes |
selling_square_footage |
INTEGER | Yes |
total_square_footage |
INTEGER | Yes |
first_open_date |
DATE | Yes |
last_remodel_date |
DATE | Yes |
number_of_employees |
INTEGER | Yes |
annual_shrinkage |
INTEGER | Yes |
foot_traffic |
INTEGER | Yes |
monthly_rent_cost |
INTEGER | Yes |