text: -- 
  -- Legal Notice 
  -- 
  -- This document and associated source code (the "Work") is a part of a 
  -- benchmark specification maintained by the TPC. 
  -- 
  -- The TPC reserves all right, title, and interest to the Work as provided 
  -- under U.S. and international laws, including without limitation all patent 
  -- and trademark rights therein. 
  -- 
  -- No Warranty 
  -- 
  -- 1.1 TO THE MAXIMUM EXTENT PERMITTED BY APPLICABLE LAW, THE INFORMATION 
  --     CONTAINED HEREIN IS PROVIDED "AS IS" AND WITH ALL FAULTS, AND THE 
  --     AUTHORS AND DEVELOPERS OF THE WORK HEREBY DISCLAIM ALL OTHER 
  --     WARRANTIES AND CONDITIONS, EITHER EXPRESS, IMPLIED OR STATUTORY, 
  --     INCLUDING, BUT NOT LIMITED TO, ANY (IF ANY) IMPLIED WARRANTIES, 
  --     DUTIES OR CONDITIONS OF MERCHANTABILITY, OF FITNESS FOR A PARTICULAR 
  --     PURPOSE, OF ACCURACY OR COMPLETENESS OF RESPONSES, OF RESULTS, OF 
  --     WORKMANLIKE EFFORT, OF LACK OF VIRUSES, AND OF LACK OF NEGLIGENCE. 
  --     ALSO, THERE IS NO WARRANTY OR CONDITION OF TITLE, QUIET ENJOYMENT, 
  --     QUIET POSSESSION, CORRESPONDENCE TO DESCRIPTION OR NON-INFRINGEMENT 
  --     WITH REGARD TO THE WORK. 
  -- 1.2 IN NO EVENT WILL ANY AUTHOR OR DEVELOPER OF THE WORK BE LIABLE TO 
  --     ANY OTHER PARTY FOR ANY DAMAGES, INCLUDING BUT NOT LIMITED TO THE 
  --     COST OF PROCURING SUBSTITUTE GOODS OR SERVICES, LOST PROFITS, LOSS 
  --     OF USE, LOSS OF DATA, OR ANY INCIDENTAL, CONSEQUENTIAL, DIRECT, 
  --     INDIRECT, OR SPECIAL DAMAGES WHETHER UNDER CONTRACT, TORT, WARRANTY,
  --     OR OTHERWISE, ARISING IN ANY WAY OUT OF THIS OR ANY OTHER AGREEMENT 
  --     RELATING TO THE WORK, WHETHER OR NOT SUCH AUTHOR OR DEVELOPER HAD 
  --     ADVANCE NOTICE OF THE POSSIBILITY OF SUCH DAMAGES. 
  -- 
  -- Contributors:
  -- Gradient Systems
  --
  
  -- ============================================================
  --   Database name:  tpcds_source                              
  --   DBMS name:      ANSI Level 2                              
  --   Created on:     2/8/2007  9:38 AM                         
  -- ============================================================
  
  -- ============================================================
  --   Table: s_catalog_page
  -- ============================================================
  create table s_catalog_page
  (
      cpag_catalog_number         integer               not null,
      cpag_catalog_page_number    integer               not null,
      cpag_department             char(20)                      ,
      cpag_id                     char(16)                      ,
      cpag_start_date             char(10)                      ,
      cpag_end_date               char(10)                      ,
      cpag_description            varchar(100)                  ,
      cpag_type                   varchar(100)
  );
  
  -- ============================================================
  --   Table: s_zip_to_gmt
  -- ============================================================
  create table s_zip_to_gmt
  (
      zipg_zip                    char(5)               not null,
      zipg_gmt_offset             integer               not null
  );
  
  -- ============================================================
  --   Table: s_purchase_lineitem                                
  -- ============================================================
  create table s_purchase_lineitem
  (
      plin_purchase_id            integer               not null,
      plin_line_number            integer               not null,
      plin_item_id                char(16)                      ,
      plin_promotion_id           char(16)                      ,
      plin_quantity               integer                       ,
      plin_sale_price             numeric(7,2)                  ,
      plin_coupon_amt             numeric(7,2)                  ,
      plin_comment                varchar(100)                  
  );
  
  -- ============================================================
  --   Table: s_customer
  -- ============================================================
  create table s_customer
  (
      cust_customer_id            char(16)              not null,
      cust_salutation             char(10)                      ,
      cust_last_name              char(20)                      ,
      cust_first_name             char(20)                      ,
      cust_preffered_flag         char(1)                       ,
      cust_birth_date             char(10)                      ,
      cust_birth_country          char(20)                      ,
      cust_login_id               char(13)                      ,
      cust_email_address          char(50)                      ,
      cust_last_login_chg_date    char(10)                      ,
      cust_first_shipto_date      char(10)                      ,
      cust_first_purchase_date    char(10)                      ,
      cust_last_review_date       char(10)                      ,
      cust_primary_machine_id     char(15)                      ,
      cust_secondary_machine_id   char(15)                      ,
      cust_street_number          smallint                      ,
      cust_suite_number           char(10)                      ,
      cust_street_name1           char(30)                      ,
      cust_street_name2           char(30)                      ,
      cust_street_type            char(15)                      ,
      cust_city                   char(60)                      ,
      cust_zip                    char(10)                      ,
      cust_county                 char(30)                      ,
      cust_state                  char(2)                       ,
      cust_country                char(20)                      ,
      cust_loc_type               char(20)                      ,
      cust_gender                 char(1)                       ,
      cust_marital_status         char(1)                       ,
      cust_educ_status            char(20)                      ,
      cust_credit_rating          char(10)                      ,
      cust_purch_est              numeric(7,2)                  ,
      cust_buy_potential          char(15)                      ,
      cust_depend_cnt             smallint                      ,
      cust_depend_emp_cnt         smallint                      ,
      cust_depend_college_cnt     smallint                      ,
      cust_vehicle_cnt            smallint                      ,
      cust_annual_income          numeric(9,2)
  );
  
  -- ============================================================
  --   Table: s_customer_address
  -- ============================================================
  create table s_customer_address
  (
      cadr_address_id             char(16)              not null,
      cadr_street_number          integer                       ,
      cadr_street_name1           char(25)                      ,
      cadr_street_name2           char(25)                      ,
      cadr_street_type            char(15)                      ,
      cadr_suitnumber             char(10)                      ,
      cadr_city                   char(60)                      ,
      cadr_county                 char(30)                      ,
      cadr_state                  char(2)                       ,
      cadr_zip                    char(10)                      ,
      cadr_country                char(20)
  );
  
  -- ============================================================
  --   Table: s_purchase                                         
  -- ============================================================
  create table s_purchase
  (
      purc_purchase_id            integer               not null,
      purc_store_id               char(16)                      ,
      purc_customer_id            char(16)                      ,
      purc_purchase_date          char(10)                      ,
      purc_purchase_time          integer                       ,
      purc_register_id            integer                       ,
      purc_clerk_id               integer                       ,
      purc_comment                char(100)                     
  );
  
  -- ============================================================
  --   Table: s_catalog_order                                    
  -- ============================================================
  create table s_catalog_order
  (
      cord_order_id               integer               not null,
      cord_bill_customer_id       char(16)                      ,
      cord_ship_customer_id       char(16)                      ,
      cord_order_date             char(10)                      ,
      cord_order_time             integer                       ,
      cord_ship_mode_id           char(16)                      ,
      cord_call_center_id         char(16)                      ,
      cord_order_comments         varchar(100)                  
  );
  
  -- ============================================================
  --   Table: s_web_order                                        
  -- ============================================================
  create table s_web_order
  (
      word_order_id               integer               not null,
      word_bill_customer_id       char(16)                      ,
      word_ship_customer_id       char(16)                      ,
      word_order_date             char(10)                      ,
      word_order_time             integer                       ,
      word_ship_mode_id           char(16)                      ,
      word_web_site_id            char(16)                      ,
      word_order_comments         char(100)                     
  );
  
  -- ============================================================
  --   Table: s_item
  -- ============================================================
  create table s_item
  (
      item_item_id                char(16)              not null,
      item_item_description       char(200)                     ,
      item_list_price             numeric(7,2)                  ,
      item_wholesale_cost         numeric(7,2)                  ,
      item_size                   char(20)                      ,
      item_formulation            char(20)                      ,
      item_color                  char(20)                      ,
      item_units                  char(10)                      ,
      item_container              char(10)                      ,
      item_manager_id             integer
  );
  
  -- ============================================================
  --   Table: s_catalog_order_lineitem                           
  -- ============================================================
  create table s_catalog_order_lineitem
  (
      clin_order_id               integer               not null,
      clin_line_number            integer               not null,
      clin_item_id                char(16)                      ,
      clin_promotion_id           char(16)                      ,
      clin_quantity               integer                       ,
      clin_sales_price            numeric(7,2)                  ,
      clin_coupon_amt             numeric(7,2)                  ,
      clin_warehouse_id           char(16)                      ,
      clin_ship_date              char(10)                      ,
      clin_catalog_number         integer                       ,
      clin_catalog_page_number    integer                       ,
      clin_ship_cost              numeric(7,2)                  
  );
  
  -- ============================================================
  --   Table: s_web_order_lineitem                               
  -- ============================================================
  create table s_web_order_lineitem
  (
      wlin_order_id               integer               not null,
      wlin_line_number            integer               not null,
      wlin_item_id                char(16)                      ,
      wlin_promotion_id           char(16)                      ,
      wlin_quantity               integer                       ,
      wlin_sales_price            numeric(7,2)                  ,
      wlin_coupon_amt             numeric(7,2)                  ,
      wlin_warehouse_id           char(16)                      ,
      wlin_ship_date              char(10)                      ,
      wlin_ship_cost              numeric(7,2)                  ,
      wlin_web_page_id            char(16)                      
  );
  
  -- ============================================================
  --   Table: s_store
  -- ============================================================
  create table s_store
  (
      stor_store_id               char(16)              not null,
      stor_closed_date            char(10)                      ,
      stor_name                   char(50)                      ,
      stor_employees              integer                       ,
      stor_floor_space            integer                       ,
      stor_hours                  char(20)                      ,
      stor_store_manager          char(40)                      ,
      stor_market_id              integer                       ,
      stor_geography_class        char(100)                     ,
      stor_market_manager         char(40)                      ,
      stor_tax_percentage         numeric(5,2)
  );
  
  -- ============================================================
  --   Table: s_call_center
  -- ============================================================
  create table s_call_center
  (
      call_center_id              char(16)              not null,
      call_open_date              char(10)                      ,
      call_closed_date            char(10)                      ,
      call_center_name            char(50)                      ,
      call_center_class           char(50)                      ,
      call_center_employees       integer                       ,
      call_center_sq_ft           integer                       ,
      call_center_hours           char(20)                      ,
      call_center_manager         char(40)                      ,
      call_center_tax_percentage  numeric(7,2)
  );
  
  -- ============================================================
  --   Table: s_web_site
  -- ============================================================
  create table s_web_site
  (
      wsit_web_site_id            char(16)              not null,
      wsit_open_date              char(10)                      ,
      wsit_closed_date            char(10)                      ,
      wsit_site_name              char(50)                      ,
      wsit_site_class             char(50)                      ,
      wsit_site_manager           char(40)                      ,
      wsit_tax_percentage         decimal(5,2)
  );
  
  -- ============================================================
  --   Table: s_warehouse
  -- ============================================================
  create table s_warehouse
  (
      wrhs_warehouse_id           char(16)              not null,
      wrhs_warehouse_desc         char(200)                     ,
      wrhs_warehouse_sq_ft        integer
  );
  
  -- ============================================================
  --   Table: s_web_page
  -- ============================================================
  create table s_web_page
  (
      wpag_web_page_id            char(16)              not null,
      wpag_create_date            char(10)                      ,
      wpag_access_date            char(10)                      ,
      wpag_autogen_flag           char(1)                       ,
      wpag_url                    char(100)                     ,
      wpag_type                   char(50)                      ,
      wpag_char_cnt               integer                       ,
      wpag_link_cnt               integer                       ,
      wpag_image_cnt              integer                       ,
      wpag_max_ad_cnt             integer
  );
  
  -- ============================================================
  --   Table: s_promotion
  -- ============================================================
  create table s_promotion
  (
      prom_promotion_id           char(16)              not null,
      prom_promotion_name         char(30)                      ,
      prom_start_date             char(10)                      ,
      prom_end_date               char(10)                      ,
      prom_cost                   numeric(7,2)                  ,
      prom_response_target        char(1)                       ,
      prom_channel_dmail          char(1)                       ,
      prom_channel_email          char(1)                       ,
      prom_channel_catalog        char(1)                       ,
      prom_channel_tv             char(1)                       ,
      prom_channel_radio          char(1)                       ,
      prom_channel_press          char(1)                       ,
      prom_channel_event          char(1)                       ,
      prom_channel_demo           char(1)                       ,
      prom_channel_details        char(100)                     ,
      prom_purpose                char(15)                      ,
      prom_discount_active        char(1)                       ,
      prom_discount_pct           numeric(5,2)
  );
  
  -- ============================================================
  --   Table: s_store_returns                                    
  -- ============================================================
  create table s_store_returns
  (
      sret_store_id               char(16)                      ,
      sret_purchase_id            char(16)              not null,
      sret_line_number            integer               not null,
      sret_item_id                char(16)              not null,
      sret_customer_id            char(16)                      ,
      sret_return_date            char(10)                      ,
      sret_return_time            char(10)                      ,
      sret_ticket_number          char(20)                      ,
      sret_return_qty             integer                       ,
      sret_return_amt             numeric(7,2)                  ,
      sret_return_tax             numeric(7,2)                  ,
      sret_return_fee             numeric(7,2)                  ,
      sret_return_ship_cost       numeric(7,2)                  ,
      sret_refunded_cash          numeric(7,2)                  ,
      sret_reversed_charge        numeric(7,2)                  ,
      sret_store_credit           numeric(7,2)                  ,
      sret_reason_id              char(16)                      
  );
  
  -- ============================================================
  --   Table: s_catalog_returns                                  
  -- ============================================================
  create table s_catalog_returns
  (
      cret_call_center_id         char(16)                      ,
      cret_order_id               integer               not null,
      cret_line_number            integer               not null,
      cret_item_id                char(16)              not null,
      cret_return_customer_id     char(16)                      ,
      cret_refund_customer_id     char(16)                      ,
      cret_return_date            char(10)                      ,
      cret_return_time            char(10)                      ,
      cret_return_qty             integer                       ,
      cret_return_amt             numeric(7,2)                  ,
      cret_return_tax             numeric(7,2)                  ,
      cret_return_fee             numeric(7,2)                  ,
      cret_return_ship_cost       numeric(7,2)                  ,
      cret_refunded_cash          numeric(7,2)                  ,
      cret_reversed_charge        numeric(7,2)                  ,
      cret_merchant_credit        numeric(7,2)                  ,
      cret_reason_id              char(16)                      ,
      cret_shipmode_id            char(16)                      ,
      cret_catalog_page_id        char(16)                      ,
      cret_warehouse_id           char(16)                      
  );
  
  -- ============================================================
  --   Table: s_web_returns
  -- ============================================================
  create table s_web_returns
  (
      wret_web_site_id            char(16)                      ,
      wret_order_id               integer               not null,
      wret_line_number            integer               not null,
      wret_item_id                char(16)              not null,
      wret_return_customer_id     char(16)                      ,
      wret_refund_customer_id     char(16)                      ,
      wret_return_date            char(10)                      ,
      wret_return_time            char(10)                      ,
      wret_return_qty             integer                       ,
      wret_return_amt             numeric(7,2)                  ,
      wret_return_tax             numeric(7,2)                  ,
      wret_return_fee             numeric(7,2)                  ,
      wret_return_ship_cost       numeric(7,2)                  ,
      wret_refunded_cash          numeric(7,2)                  ,
      wret_reversed_charge        numeric(7,2)                  ,
      wret_account_credit         numeric(7,2)                  ,
      wret_reason_id              char(16)
  );
  
  -- ============================================================
  --   Table: s_inventory                                        
  -- ============================================================
  create table s_inventory
  (
      invn_warehouse_id           char(16)              not null,
      invn_item_id                char(16)              not null,
      invn_date                   char(10)              not null,
      invn_qty_on_hand            integer                       
  );
  
  