-- Sccsid:     @(#)dss.ddl	2.1.8.1

CREATE TABLE region (
  r_regionkey  INTEGER NOT NULL,
  r_name       CHAR(25) NOT NULL,
  r_comment    VARCHAR(152)
);

CREATE INDEX r_regionkey ON region (r_regionkey);
CREATE INDEX r_name ON region (r_name);

CREATE TABLE nation (
  n_nationkey  INTEGER NOT NULL,
  n_name       CHAR(25) NOT NULL,
  n_regionkey  INTEGER NOT NULL,
  n_comment    VARCHAR(152)
);

CREATE INDEX n_nationkey ON nation (n_nationkey);
CREATE INDEX n_name ON nation (n_name);
CREATE INDEX n_regionkey ON nation (n_regionkey);

CREATE TABLE part (
  p_partkey     INTEGER NOT NULL,
  p_name        VARCHAR(55) NOT NULL,
  p_mfgr        CHAR(25) NOT NULL,
  p_brand       CHAR(10) NOT NULL,
  p_type        VARCHAR(25) NOT NULL,
  p_size        INTEGER NOT NULL,
  p_container   CHAR(10) NOT NULL,
  p_retailprice DECIMAL(15,2) NOT NULL,
  p_comment     VARCHAR(23) NOT NULL
);

CREATE INDEX p_partkey ON part (p_partkey);
CREATE INDEX p_name ON part (p_name);
CREATE INDEX p_brand ON part (p_brand);
CREATE INDEX p_type ON part (p_type);
CREATE INDEX p_size ON part (p_size);
CREATE INDEX p_container ON part (p_container);

CREATE TABLE supplier (
  s_suppkey     INTEGER NOT NULL,
  s_name        CHAR(25) NOT NULL,
  s_address     VARCHAR(40) NOT NULL,
  s_nationkey   INTEGER NOT NULL,
  s_phone       CHAR(15) NOT NULL,
  s_acctbal     DECIMAL(15,2) NOT NULL,
  s_comment     VARCHAR(101) NOT NULL
);

CREATE INDEX s_suppkey ON supplier (s_suppkey);
CREATE INDEX s_name ON supplier (s_name);
CREATE INDEX s_nationkey ON supplier (s_nationkey);
CREATE INDEX s_acctbal ON supplier (s_acctbal);
CREATE INDEX s_comment ON supplier (s_comment);

CREATE TABLE partsupp (
  ps_partkey     INTEGER NOT NULL,
  ps_suppkey     INTEGER NOT NULL,
  ps_availqty    INTEGER NOT NULL,
  ps_supplycost  DECIMAL(15,2)  NOT NULL,
  ps_comment     VARCHAR(199) NOT NULL
);

CREATE INDEX ps_partsuppkey ON partsupp (ps_partkey,ps_suppkey);
CREATE INDEX ps_partkey ON partsupp (ps_partkey);
CREATE INDEX ps_suppkey ON partsupp (ps_suppkey);
CREATE INDEX ps_availqty ON partsupp (ps_availqty);
CREATE INDEX ps_supplycost ON partsupp (ps_supplycost);

CREATE TABLE customer (
  c_custkey     INTEGER NOT NULL,
  c_name        VARCHAR(25) NOT NULL,
  c_address     VARCHAR(40) NOT NULL,
  c_nationkey   INTEGER NOT NULL,
  c_phone       CHAR(15) NOT NULL,
  c_acctbal     DECIMAL(15,2)   NOT NULL,
  c_mktsegment  CHAR(10) NOT NULL,
  c_comment     VARCHAR(117) NOT NULL
);

CREATE INDEX c_custkey ON customer (c_custkey);
CREATE INDEX c_name ON customer (c_name);
CREATE INDEX c_address ON customer (c_address);
CREATE INDEX c_nationkey ON customer (c_nationkey);
CREATE INDEX c_phone ON customer (c_phone);
CREATE INDEX c_acctbal ON customer (c_acctbal);
CREATE INDEX c_mktsegment ON customer (c_mktsegment);
CREATE INDEX c_comment ON customer (c_comment);

CREATE TABLE orders (
  o_orderkey       INTEGER NOT NULL,
  o_custkey        INTEGER NOT NULL,
  o_orderstatus    CHAR(1) NOT NULL,
  o_totalprice     DECIMAL(15,2) NOT NULL,
  o_orderdate      DATE NOT NULL,
  o_orderpriority  CHAR(15) NOT NULL,  -- R
  o_clerk          CHAR(15) NOT NULL,  -- R
  o_shippriority   INTEGER NOT NULL,
  o_comment        VARCHAR(79) NOT NULL
);

CREATE INDEX o_orderkey ON orders (o_orderkey);
CREATE INDEX o_custkey ON orders (o_custkey);
CREATE INDEX o_orderstatus ON orders (o_orderstatus);
CREATE INDEX o_totalprice ON orders (o_totalprice);
CREATE INDEX o_orderdate ON orders (o_orderdate);
CREATE INDEX o_orderpriority ON orders (o_orderpriority);
CREATE INDEX o_shippriority ON orders (o_shippriority);
CREATE INDEX o_comment ON orders (o_comment);

CREATE TABLE lineitem (
  l_orderkey    INTEGER NOT NULL,
  l_partkey     INTEGER NOT NULL,
  l_suppkey     INTEGER NOT NULL,
  l_linenumber  INTEGER NOT NULL,
  l_quantity    DECIMAL(15,2) NOT NULL,
  l_extendedprice  DECIMAL(15,2) NOT NULL,
  l_discount    DECIMAL(15,2) NOT NULL,
  l_tax         DECIMAL(15,2) NOT NULL,
  l_returnflag  CHAR(1) NOT NULL,
  l_linestatus  CHAR(1) NOT NULL,
  l_shipdate    DATE NOT NULL,
  l_commitdate  DATE NOT NULL,
  l_receiptdate DATE NOT NULL,
  l_shipinstruct CHAR(25) NOT NULL,  -- R
  l_shipmode     CHAR(10) NOT NULL,  -- R
  l_comment      VARCHAR(44) NOT NULL
);

CREATE INDEX l_orderlinekey ON lineitem (l_orderkey,l_linenumber);
CREATE INDEX l_orderkey ON lineitem (l_orderkey);
CREATE INDEX l_partsupp ON lineitem (l_partkey,l_suppkey);
CREATE INDEX l_quantity ON lineitem (l_quantity);
CREATE INDEX l_extendedprice ON lineitem (l_extendedprice);
CREATE INDEX l_discount ON lineitem (l_discount);
CREATE INDEX l_returnflag ON lineitem (l_returnflag);
CREATE INDEX l_linestatus ON lineitem (l_linestatus);
CREATE INDEX l_shipdate ON lineitem (l_shipdate);
CREATE INDEX l_commitdate ON lineitem (l_commitdate);
CREATE INDEX l_receiptdate ON lineitem (l_receiptdate);
CREATE INDEX l_shipinstruct ON lineitem (l_shipinstruct);
CREATE INDEX l_shipmode ON lineitem (l_shipmode);