The public schema is a snowflake schema. The following graphic illustrates the public schema and its relationships with tables in the online_sales and store schemas.
The subsequent subsections describe database tables.
This is the multi-page printable view of this section. Click here to print.
Public schema map
The public schema is a snowflake schema.
- 1: inventory_fact
- 2: customer_dimension
- 3: date_dimension
- 4: employee_dimension
- 5: product_dimension
- 6: promotion_dimension
- 7: shipping_dimension
- 8: vendor_dimension
- 9: warehouse_dimension
1 - inventory_fact
This table contains information about each product in inventory.
This table contains information about each product in inventory.
| Column Name | Data Type | NULLs |
date_key |
INTEGER | No |
product_key |
INTEGER | No |
product_version |
INTEGER | No |
warehouse_key |
INTEGER | No |
qty_in_stock |
INTEGER | No |
2 - customer_dimension
This table contains information about all the retail chain’s customers.
This table contains information about all the retail chain’s customers.
| Column Name | Data Type | NULLs |
customer_key |
INTEGER | No |
customer_type |
VARCHAR(16) | Yes |
customer_name |
VARCHAR(256) | Yes |
customer_gender |
VARCHAR(8) | Yes |
title |
VARCHAR(8) | Yes |
household_id |
INTEGER | Yes |
customer_address |
VARCHAR(256) | Yes |
customer_city |
VARCHAR(64) | Yes |
customer_state |
CHAR(2) | Yes |
customer_region |
VARCHAR(64) | Yes |
marital_status |
VARCHAR(32) | Yes |
customer_age |
INTEGER | Yes |
number_of_children |
INTEGER | Yes |
annual_income |
INTEGER | Yes |
occupation |
VARCHAR(64) | Yes |
largest_bill_amount |
INTEGER | Yes |
store_membership_card |
INTEGER | Yes |
customer_since |
DATE | Yes |
deal_stage |
VARCHAR(32) | Yes |
deal_size |
INTEGER | Yes |
last_deal_update |
DATE | Yes |
3 - date_dimension
This table contains information about dates.
This table contains information about dates. It is generated from a file containing correct date/time data.
| Column Name | Data Type | NULLs |
date_key |
INTEGER | No |
date |
DATE | Yes |
full_date_description |
VARCHAR(18) | Yes |
day_of_week |
VARCHAR(9) | Yes |
day_number_in_calendar_month |
INTEGER | Yes |
day_number_in_calendar_year |
INTEGER | Yes |
day_number_in_fiscal_month |
INTEGER | Yes |
day_number_in_fiscal_year |
INTEGER | Yes |
last_day_in_week_indicator |
INTEGER | Yes |
last_day_in_month_indicator |
INTEGER | Yes |
calendar_week_number_in_year |
INTEGER | Yes |
calendar_month_name |
VARCHAR(9) | Yes |
calendar_month_number_in_year |
INTEGER | Yes |
calendar_year_month |
CHAR(7) | Yes |
calendar_quarter |
INTEGER | Yes |
calendar_year_quarter |
CHAR(7) | Yes |
calendar_half_year |
INTEGER | Yes |
calendar_year |
INTEGER | Yes |
holiday_indicator |
VARCHAR(10) | Yes |
weekday_indicator |
CHAR(7) | Yes |
selling_season |
VARCHAR(32) | Yes |
4 - employee_dimension
This table contains information about all the people who work for the retail chain.
This table contains information about all the people who work for the retail chain.
| Column Name | Data Type | NULLs |
employee_key |
INTEGER | No |
employee_gender |
VARCHAR(8) | Yes |
courtesy_title |
VARCHAR(8) | Yes |
employee_first_name |
VARCHAR(64) | Yes |
employee_middle_initial |
VARCHAR(8) | Yes |
employee_last_name |
VARCHAR(64) | Yes |
employee_age |
INTEGER | Yes |
hire_date |
DATE | Yes |
employee_street_address |
VARCHAR(256) | Yes |
employee_city |
VARCHAR(64) | Yes |
employee_state |
CHAR(2) | Yes |
employee_region |
CHAR(32) | Yes |
job_title |
VARCHAR(64) | Yes |
reports_to |
INTEGER | Yes |
salaried_flag |
INTEGER | Yes |
annual_salary |
INTEGER | Yes |
hourly_rate |
FLOAT | Yes |
vacation_days |
INTEGER | Yes |
5 - product_dimension
This table describes all products sold by the department store chain.
This table describes all products sold by the department store chain.
| Column Name | Data Type | NULLs |
product_key |
INTEGER | No |
product_version |
INTEGER | No |
product_description |
VARCHAR(128) | Yes |
sku_number |
CHAR(32) | Yes |
category_description |
CHAR(32) | Yes |
department_description |
CHAR(32) | Yes |
package_type_description |
CHAR(32) | Yes |
package_size |
CHAR(32) | Yes |
fat_content |
INTEGER | Yes |
diet_type |
CHAR(32) | Yes |
weight |
INTEGER | Yes |
weight_units_of_measure |
CHAR(32) | Yes |
shelf_width |
INTEGER | Yes |
shelf_height |
INTEGER | Yes |
shelf_depth |
INTEGER | Yes |
product_price |
INTEGER | Yes |
product_cost |
INTEGER | Yes |
lowest_competitor_price |
INTEGER | Yes |
highest_competitor_price |
INTEGER | Yes |
average_competitor_price |
INTEGER | Yes |
discontinued_flag |
INTEGER | Yes |
6 - promotion_dimension
This table describes every promotion ever done by the retail chain.
This table describes every promotion ever done by the retail chain.
| Column Name | Data Type | NULLs |
promotion_key |
INTEGER | No |
promotion_name |
VARCHAR(128) | Yes |
price_reduction_type |
VARCHAR(32) | Yes |
promotion_media_type |
VARCHAR(32) | Yes |
ad_type |
VARCHAR(32) | Yes |
display_type |
VARCHAR(32) | Yes |
coupon_type |
VARCHAR(32) | Yes |
ad_media_name |
VARCHAR(32) | Yes |
display_provider |
VARCHAR(128) | Yes |
promotion_cost |
INTEGER | Yes |
promotion_begin_date |
DATE | Yes |
promotion_end_date |
DATE | Yes |
7 - shipping_dimension
This table contains information about shipping companies that the retail chain uses.
This table contains information about shipping companies that the retail chain uses.
| Column Name | Data Type | NULLs |
shipping_key |
INTEGER | No |
ship_type |
CHAR(30) | Yes |
ship_mode |
CHAR(10) | Yes |
ship_carrier |
CHAR(20) | Yes |
8 - vendor_dimension
This table contains information about each vendor that provides products sold through the retail chain.
This table contains information about each vendor that provides products sold through the retail chain.
| Column Name | Data Type | NULLs |
vendor_key |
INTEGER | No |
vendor_name |
VARCHAR(64) | Yes |
vendor_address |
VARCHAR(64) | Yes |
vendor_city |
VARCHAR(64) | Yes |
vendor_state |
CHAR(2) | Yes |
vendor_region |
VARCHAR(32) | Yes |
deal_size |
INTEGER | Yes |
last_deal_update |
DATE | Yes |
9 - warehouse_dimension
This table provides information about each of the chain’s warehouses.
This table provides information about each of the chain’s warehouses.
| Column Name | Data Type | NULLs |
warehouse_key |
INTEGER | No |
warehouse_name |
VARCHAR(20) | Yes |
warehouse_address |
VARCHAR(256) | Yes |
warehouse_city |
VARCHAR(60) | Yes |
warehouse_state |
CHAR(2) | Yes |
warehouse_region |
VARCHAR(32) | Yes |