This is the multi-page printable view of this section. Click here to print.

Return to the regular view of this page.

Appendix: VMart example database schema, tables, and scripts

This appendix provides detailed information about the VMart example database’s schema, tables, and scripts.

This appendix provides detailed information about the VMart example database’s schema, tables, and scripts.

The VMart example database contains three different schemas:

  • public

  • store

  • online_sales

The term “schema” has several related meanings in Vertica:

  • In SQL statements, a schema refers to named namespace for a logical schema.

  • Logical schema refers to a set of tables and constraints.

  • Physical schema refers to a set of projections.

Tables identifies the three schemas and all the data tables in the VMart database. Each schema contains tables that are created and loaded during database installation. See the schema maps for a list of tables and their contents:

Sample scripts describes the sample scripts that contain SQL commands that represent queries that might be used in a real business using a VMart-like database. Once you’re comfortable running the example queries, you might want to write your own.

1 - Tables

The three schemas in the VMart database include the following tables:.

The three schemas in the VMart database include the following tables:

public Schema store Schema online_sales Schema
inventory_fact store_orders_fact online_sales_fact
customer_dimension store_sales_fact call_center_dimension
date_dimension store_dimension online_page_dimension
employee_dimension
product_dimension
promotion_dimension
shipping_dimension
vendor_dimension
warehouse_dimension

2 - Public schema map

The public schema is a snowflake schema.

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.

2.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.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

2.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

2.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

2.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

2.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

2.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

2.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

2.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

3 - Store schema map

The store schema is a snowflake schema that contains information about the retail chain’s bricks-and-mortar stores.

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.

3.1 - store_orders_fact

This table contains information about all orders made at the company’s brick-and-mortar stores.

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

3.2 - store_sales_fact

This table contains information about all sales made at the company’s brick-and-mortar stores.

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

3.3 - store_dimension

This table contains information about each brick-and-mortar store within the retail chain.

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

4 - online_sales schema map

The online_sales schema is a snowflake schema that contains information about the retail chains.

The online_sales schema is a snowflake schema that contains information about the retail chains. The following graphic illustrates the online_sales schema and its relationship with tables in the public schema.

The subsequent subsections describe database tables.

4.1 - online_sales_fact

This table describes all the items purchased through the online store front.

This table describes all the items purchased through the online store front.

Column Name Data Type NULLs
sale_date_key INTEGER No
ship_date_key INTEGER No
product_key INTEGER No
product_version INTEGER No
customer_key INTEGER No
call_center_key INTEGER No
online_page_key INTEGER No
shipping_key INTEGER No
warehouse_key INTEGER No
promotion_key INTEGER No
pos_transaction_number INTEGER No
sales_quantity INTEGER Yes
sales_dollar_amount FLOAT Yes
ship_dollar_amount FLOAT Yes
net_dollar_amount FLOAT Yes
cost_dollar_amount FLOAT Yes
gross_profit_dollar_amount FLOAT Yes
transaction_type VARCHAR(16) Yes

4.2 - call_center_dimension

This table describes all the chain’s call centers.

This table describes all the chain’s call centers.

Column Name Data Type NULLs
call_center_key INTEGER No
cc_closed_date DATE Yes
cc_open_date DATE Yes
cc_date VARCHAR(50) Yes
cc_class VARCHAR(50) Yes
cc_employees INTEGER Yes
cc_hours CHAR(20) Yes
cc_manager VARCHAR(40) Yes
cc_address VARCHAR(256) Yes
cc_city VARCHAR(64) Yes
cc_state CHAR(2) Yes
cc_region VARCHAR(64) Yes

4.3 - online_page_dimension

This table describes all the pages in the online store front.

This table describes all the pages in the online store front.

Column Name Data Type NULLs
online_page_key INTEGER No
start_date DATE Yes
end_date DATE Yes
page_number INTEGER Yes
page_description VARCHAR(100) Yes
page_type VARCHAR(100) Yes

5 - Sample scripts

You can create your own queries, but the VMart example directory includes sample query script files to help you get started quickly.

You can create your own queries, but the VMart example directory includes sample query script files to help you get started quickly.

You can find the following sample scripts at this path /opt/vertica/examples/VMart_Schema.

To run any of the scripts, enter

=>  \i <script_name>

Alternatively, type the commands from the script file manually.

5.1 - vmart_query_01.sql

Output.
-- vmart_query_01.sql
-- FROM clause subquery
-- Return the values for five products with the
-- lowest-fat content in the Dairy department
SELECT fat_content
FROM (
  SELECT DISTINCT fat_content
  FROM product_dimension
  WHERE department_description
  IN ('Dairy') ) AS food
  ORDER BY fat_content
  LIMIT 5;

Output

 fat_content
-------------
          80
          81
          82
          83
          84
(5 rows)

5.2 - vmart_query_02.sql

Output.
-- vmart_query_02.sql
-- WHERE clause subquery
-- Asks for all orders placed by stores located in Massachusetts
-- and by vendors located elsewhere before March 1, 2003:
SELECT order_number, date_ordered
FROM store.store_orders_fact orders
WHERE orders.store_key IN (
  SELECT store_key
  FROM store.store_dimension
  WHERE store_state = 'MA')
    AND orders.vendor_key NOT IN (
  SELECT vendor_key
  FROM public.vendor_dimension
  WHERE vendor_state = 'MA')
    AND date_ordered < '2012-03-01';

Output

order_number | date_ordered
-------------+--------------
       53019 | 2012-02-10
      222168 | 2012-02-05
      160801 | 2012-01-08
      106922 | 2012-02-07
      246465 | 2012-02-10
      234218 | 2012-02-03
      263119 | 2012-01-04
       73015 | 2012-01-01
      233618 | 2012-02-10
       85784 | 2012-02-07
      146607 | 2012-02-07
      296193 | 2012-02-05
       55052 | 2012-01-05
      144574 | 2012-01-05
      117412 | 2012-02-08
      276288 | 2012-02-08
      185103 | 2012-01-03
      282274 | 2012-01-01
      245300 | 2012-02-06
      143526 | 2012-01-04
       59564 | 2012-02-06
...

5.3 - vmart_query_03.sql

Output.
-- vmart_query_03.sql
-- noncorrelated subquery
-- Requests female and male customers with the maximum
-- annual income from customers
SELECT customer_name, annual_income
FROM public.customer_dimension
WHERE (customer_gender, annual_income) IN (
  SELECT customer_gender, MAX(annual_income)
  FROM public.customer_dimension
  GROUP BY customer_gender);

Output

  customer_name   | annual_income
------------------+---------------
 James M. McNulty |        999979
 Emily G. Vogel   |        999998
(2 rows)

5.4 - vmart_query_04.sql

Output.
-- vmart_query_04.sql
-- IN predicate
-- Find all products supplied by stores in MA
SELECT DISTINCT s.product_key, p.product_description
FROM store.store_sales_fact s, public.product_dimension p
WHERE s.product_key = p.product_key
AND s.product_version = p.product_version AND s.store_key IN (
  SELECT store_key
  FROM store.store_dimension
  WHERE store_state = 'MA')
ORDER BY s.product_key;

Output

 product_key |          product_description
-------------+----------------------------------------
1 | Brand #1 butter
1 | Brand #2 bagels
2 | Brand #3 lamb
2 | Brand #4 brandy
2 | Brand #5 golf clubs
2 | Brand #6 chicken noodle soup
3 | Brand #10 ground beef
3 | Brand #11 vanilla ice cream
3 | Brand #7 canned chicken broth
3 | Brand #8 halibut
3 | Brand #9 camera case
4 | Brand #12 rash ointment
4 | Brand #13 low fat milk
4 | Brand #14 chocolate chip cookies
4 | Brand #15 silver polishing cream
5 | Brand #16 cod
5 | Brand #17 band aids
6 | Brand #18 bananas
6 | Brand #19 starch
6 | Brand #20 vegetable soup
6 | Brand #21 bourbon
...

5.5 - vmart_query_05.sql

Output.
-- vmart_query_05.sql
-- EXISTS predicate
-- Get a list of all the orders placed by all stores on
-- January 2, 2003 for the vendors with records in the
-- vendor_dimension table
SELECT store_key, order_number, date_ordered
FROM store.store_orders_fact
WHERE EXISTS (
   SELECT 1
   FROM public.vendor_dimension
   WHERE public.vendor_dimension.vendor_key = store.store_orders_fact.vendor_key)
   AND date_ordered = '2012-01-02';

Output

 store_key | order_number | date_ordered
-----------+--------------+--------------
        98 |       151837 | 2012-01-02
       123 |       238372 | 2012-01-02
       242 |       263973 | 2012-01-02
       150 |       226047 | 2012-01-02
       247 |       232273 | 2012-01-02
       203 |       171649 | 2012-01-02
       129 |        98723 | 2012-01-02
        80 |       265660 | 2012-01-02
       231 |       271085 | 2012-01-02
       149 |        12169 | 2012-01-02
       141 |       201153 | 2012-01-02
         1 |        23715 | 2012-01-02
       156 |        98182 | 2012-01-02
        44 |       229465 | 2012-01-02
       178 |       141869 | 2012-01-02
       134 |        44410 | 2012-01-02
       141 |       129839 | 2012-01-02
       205 |        54138 | 2012-01-02
       113 |        63358 | 2012-01-02
        99 |        50142 | 2012-01-02
        44 |       131255 | 2012-01-02
...

5.6 - vmart_query_06.sql

Output.
-- vmart_query_06.sql
-- EXISTS predicate
-- Orders placed by the vendor who got the best deal
-- on January 4, 2004
SELECT store_key, order_number, date_ordered
FROM store.store_orders_fact ord, public.vendor_dimension vd
WHERE ord.vendor_key = vd.vendor_key
AND vd.deal_size IN (
   SELECT MAX(deal_size)
   FROM public.vendor_dimension)
AND date_ordered = '2013-01-04';

Output

 store_key | order_number | date_ordered
-----------+--------------+--------------
        45 |       202416 | 2013-01-04
        24 |       250295 | 2013-01-04
       121 |       251417 | 2013-01-04
       198 |        75716 | 2013-01-04
       166 |        36008 | 2013-01-04
        27 |       150241 | 2013-01-04
       148 |       182207 | 2013-01-04
         9 |       188567 | 2013-01-04
       113 |        66017 | 2013-01-04
...

5.7 - vmart_query_07.sql

Output.
-- vmart_query_07.sql
-- Multicolumn subquery
-- Which products have the highest cost,
-- grouped by category and department
SELECT product_description, sku_number, department_description
FROM public.product_dimension
WHERE (category_description, department_description, product_cost) IN (
   SELECT category_description, department_description,
   MAX(product_cost) FROM product_dimension
   GROUP BY category_description, department_description);

Output

product_description        |      sku_number       |   department_description
---------------------------+-----------------------+---------------------------------
 Brand #601 steak           | SKU-#601             | Meat
 Brand #649 brooms          | SKU-#649             | Cleaning supplies
 Brand #677 veal            | SKU-#677             | Meat
 Brand #1371 memory card    | SKU-#1371            | Photography
 Brand #1761 catfish        | SKU-#1761            | Seafood
 Brand #1810 frozen pizza   | SKU-#1810            | Frozen Goods
 Brand #1979 canned peaches | SKU-#1979            | Canned Goods
 Brand #2097 apples         | SKU-#2097            | Produce
 Brand #2287 lens cap       | SKU-#2287            | Photography
...

5.8 - vmart_query_08.sql

Output.
-- vmart_query_08.sql
-- between online_sales_fact and online_page_dimension
SELECT page_description, page_type, start_date, end_date
FROM online_sales.online_sales_fact f, online_sales.online_page_dimension d
WHERE f.online_page_key = d.online_page_key
AND page_number IN
   (SELECT MAX(page_number)
     FROM online_sales.online_page_dimension)
AND page_type = 'monthly' AND start_date = '2012-06-02';

Output

      page_description     | page_type | start_date | end_date
---------------------------+-----------+------------+-----------
Online Page Description #1 | monthly   | 2012-06-02 | 2012-06-11
Online Page Description #1 | monthly   | 2012-06-02 | 2012-06-11
Online Page Description #1 | monthly   | 2012-06-02 | 2012-06-11
Online Page Description #1 | monthly   | 2012-06-02 | 2012-06-11
Online Page Description #1 | monthly   | 2012-06-02 | 2012-06-11
Online Page Description #1 | monthly   | 2012-06-02 | 2012-06-11
Online Page Description #1 | monthly   | 2012-06-02 | 2012-06-11
Online Page Description #1 | monthly   | 2012-06-02 | 2012-06-11
Online Page Description #1 | monthly   | 2012-06-02 | 2012-06-11
Online Page Description #1 | monthly   | 2012-06-02 | 2012-06-11
Online Page Description #1 | monthly   | 2012-06-02 | 2012-06-11
Online Page Description #1 | monthly   | 2012-06-02 | 2012-06-11
(12 rows)

5.9 - vmart_query_09.sql

Output.
-- vmart_query_09.sql
-- Equi join
-- Joins online_sales_fact table and the call_center_dimension
-- table with the ON clause
SELECT sales_quantity, sales_dollar_amount, transaction_type, cc_name
FROM online_sales.online_sales_fact
INNER JOIN online_sales.call_center_dimension
ON (online_sales.online_sales_fact.call_center_key
      = online_sales.call_center_dimension.call_center_key
     AND sale_date_key = 156)
ORDER BY sales_dollar_amount DESC;

Output

 sales_quantity | sales_dollar_amount | transaction_type |      cc_name
----------------+---------------------+------------------+-------------------
              7 |                 589 | purchase         | Central Midwest
              8 |                 589 | purchase         | South Midwest
              8 |                 589 | purchase         | California
              1 |                 587 | purchase         | New England
              1 |                 586 | purchase         | Other
              1 |                 584 | purchase         | New England
              4 |                 584 | purchase         | New England
              7 |                 581 | purchase         | Mid Atlantic
              5 |                 579 | purchase         | North Midwest
              8 |                 577 | purchase         | North Midwest
              4 |                 577 | purchase         | Central Midwest
              2 |                 575 | purchase         | Hawaii/Alaska
              4 |                 573 | purchase         | NY Metro
              4 |                 572 | purchase         | Central Midwest
              1 |                 570 | purchase         | Mid Atlantic
              9 |                 569 | purchase         | Southeastern
              1 |                 569 | purchase         | NY Metro
              5 |                 567 | purchase         | Other
              7 |                 567 | purchase         | Hawaii/Alaska
              9 |                 567 | purchase         | South Midwest
              1 |                 566 | purchase         | New England
...