CREATE TABLE region (
    r_regionkey  INT NOT NULL,           -- region key (primary key)
    r_name       CHAR(25) NOT NULL,      -- region name
    r_comment    VARCHAR(152),           -- remarks
    PRIMARY KEY (r_regionkey)
);

CREATE TABLE nation (
    n_nationkey  INT NOT NULL,           -- country key (primary key)
    n_name       CHAR(25) NOT NULL,      -- country name
    n_regionkey  INT NOT NULL,           -- region key (foreign key -> region)
    n_comment    VARCHAR(152),           -- remarks
    PRIMARY KEY (n_nationkey),
    FOREIGN KEY (n_regionkey) REFERENCES region(r_regionkey)
);

CREATE TABLE customer (
    c_custkey     INT NOT NULL,          -- customer key (primary key)
    c_name        VARCHAR(25) NOT NULL,  -- customer name
    c_address     VARCHAR(40) NOT NULL,  -- customer address
    c_nationkey   INT NOT NULL,          -- country key (foreign key -> nation)
    c_phone       CHAR(15) NOT NULL,     -- phone number
    c_acctbal     DECIMAL(15,2) NOT NULL,-- account balance
    c_mktsegment  CHAR(10) NOT NULL,     -- market segment
    c_comment     VARCHAR(117) NOT NULL, -- remarks
    PRIMARY KEY (c_custkey),
    FOREIGN KEY (c_nationkey) REFERENCES nation(n_nationkey)
);

CREATE TABLE part (
    p_partkey     INT NOT NULL,          -- part key (primary key)
    p_name        VARCHAR(55) NOT NULL,  -- part name
    p_mfgr        CHAR(25) NOT NULL,     -- manufacturer
    p_brand       CHAR(10) NOT NULL,     -- brand
    p_type        VARCHAR(25) NOT NULL,  -- part type
    p_size        INT NOT NULL,          -- size
    p_container   CHAR(10) NOT NULL,     -- container type
    p_retailprice DECIMAL(15,2) NOT NULL,-- retail price
    p_comment     VARCHAR(23) NOT NULL,  -- remarks
    PRIMARY KEY (p_partkey)
);

CREATE TABLE supplier (
    s_suppkey     INT NOT NULL,          -- supplier key (primary key)
    s_name        CHAR(25) NOT NULL,     -- supplier name
    s_address     VARCHAR(40) NOT NULL,  -- supplier address
    s_nationkey   INT NOT NULL,          -- country key (foreign key -> nation)
    s_phone       CHAR(15) NOT NULL,     -- phone number
    s_acctbal     DECIMAL(15,2) NOT NULL,-- account balance
    s_comment     VARCHAR(101) NOT NULL, -- remarks
    PRIMARY KEY (s_suppkey),
    FOREIGN KEY (s_nationkey) REFERENCES nation(n_nationkey)
);

CREATE TABLE partsupp (
    ps_partkey     INT NOT NULL,         -- part key (foreign key -> part)
    ps_suppkey     INT NOT NULL,         -- supplier key (foreign key -> supplier)
    ps_availqty    INT NOT NULL,         -- available quantity
    ps_supplycost  DECIMAL(15,2) NOT NULL,-- supply cost
    ps_comment     VARCHAR(199) NOT NULL,-- remarks
    PRIMARY KEY (ps_partkey, ps_suppkey),
    FOREIGN KEY (ps_partkey) REFERENCES part(p_partkey),
    FOREIGN KEY (ps_suppkey) REFERENCES supplier(s_suppkey)
);

CREATE TABLE orders (
    o_orderkey       INT NOT NULL,       -- order key (primary key)
    o_custkey        INT NOT NULL,       -- customer key (foreign key -> customer)
    o_orderstatus    CHAR(1) NOT NULL,   -- order status
    o_totalprice     DECIMAL(15,2) NOT NULL, -- order total price
    o_orderdate      DATE NOT NULL,      -- order date
    o_orderpriority  CHAR(15) NOT NULL,  -- order priority
    o_clerk          CHAR(15) NOT NULL,  -- order clerk
    o_shippriority   INT NOT NULL,       -- shipping priority
    o_comment        VARCHAR(79) NOT NULL, -- remarks
    PRIMARY KEY (o_orderkey),
    FOREIGN KEY (o_custkey) REFERENCES customer(c_custkey)
);

CREATE TABLE lineitem (
    l_orderkey       INT NOT NULL,       -- order key (foreign key -> orders)
    l_partkey        INT NOT NULL,       -- part key (foreign key -> part)
    l_suppkey        INT NOT NULL,       -- supplier key (foreign key -> supplier)
    l_linenumber     INT NOT NULL,       -- line number
    l_quantity       DECIMAL(15,2) NOT NULL, -- quantity
    l_extendedprice  DECIMAL(15,2) NOT NULL, -- extended price
    l_discount       DECIMAL(15,2) NOT NULL, -- discount
    l_tax            DECIMAL(15,2) NOT NULL, -- tax
    l_returnflag     CHAR(1) NOT NULL,   -- return flag
    l_linestatus     CHAR(1) NOT NULL,   -- line status
    l_shipdate       DATE NOT NULL,      -- ship date
    l_commitdate     DATE NOT NULL,      -- commit date
    l_receiptdate    DATE NOT NULL,      -- receipt date
    l_shipinstruct   CHAR(25) NOT NULL,  -- shipping instruction
    l_shipmode       CHAR(10) NOT NULL,  -- shipping mode
    l_comment        VARCHAR(44) NOT NULL, -- remarks
    PRIMARY KEY (l_orderkey, l_linenumber),
    FOREIGN KEY (l_orderkey) REFERENCES orders(o_orderkey),
    FOREIGN KEY (l_partkey, l_suppkey) REFERENCES partsupp(ps_partkey, ps_suppkey)
);