create table region (
   regionkey  integer not null,
   name       char(25) not null,
   constraint region_pk primary key( regionkey )
);

create table nation (
   nationkey  integer not null,
   name       char(25) not null,
   regionkey  integer not null,
   constraint nation_pk primary key( nationkey ),
   constraint nation_fk1 foreign key( regionkey ) references region
);

create table part (
   partkey     integer not null,
   name        varchar(55) not null,
   type        varchar(25) not null,
   size        integer,
   container   integer,
   retailprice decimal(15,2) not null,
   constraint part_pk primary key( partkey )
);

create table supplier (
   suppkey     integer not null,
   name        char(25) not null,
   address     varchar(40) not null,
   nationkey   integer not null,
   phone       char(15),
   fax          char(15),
   acctbal     decimal(15,2) default 0.00,
   constraint supplier_pk primary key( suppkey )
);

create table partsupp (
   partkey     integer not null,
   suppkey     integer not null,
   availqty    integer not null default 0,
   supplycost  decimal(15,2) not null,
   constraint partsupp_pk primary key( partkey, suppkey ),
   constraint partsupp_fk1 foreign key( partkey ) references part,
   constraint partsupp_fk2 foreign key( suppkey ) references supplier
);

create table customer (
   custkey     integer not null,
   name        varchar(25) not null,
   address     varchar(40) not null,
   nationkey   integer not null,
   phone       char(15),
   fax         char(15),
   acctbal     decimal(15,2) default 0.00,
   constraint customer_pk primary key( custkey ),
   constraint customer_fk1 foreign key( nationkey ) references nation
);

create table order (
   orderkey       integer not null,
   custkey        integer not null,
   orderstatus    char(2) not null default 'no',
   totalprice     decimal(15,2) not null default 0.00,
   orderdate      date not null,
   constraint order_pk primary key( orderkey ),
   constraint order_fk1 foreign key( custkey ) references customer
);

create table lineitem (
   orderkey       integer not null,
   partkey        integer not null,
   suppkey        integer not null,
   linenumber     integer not null,
   quantity       integer not null default 1,
   extendedprice  decimal(15,2) not null default 0.00,
   discount       decimal(3,2),
   tax            decimal(3,2) default 0.15,
   constraint lineitem_pk primary key( orderkey, linenumber ),
   constraint lineitem_fk1 foreign key( orderkey ) references order,
   constraint lineitem_fk2 foreign key( partkey, suppkey ) references partsupp
);