truncate table LINEITEM drop all storage;
truncate table ORDERS drop all storage;
truncate table CUSTOMER drop all storage;
truncate table PARTSUPP drop all storage;
truncate table PART drop all storage;
truncate table SUPPLIER drop all storage;
truncate table NATION drop all storage;
truncate table REGION drop all storage;
DROP TABLE LINEITEM CASCADE CONSTRAINTS ;
DROP TABLE ORDERS CASCADE CONSTRAINTS ;
DROP TABLE CUSTOMER CASCADE CONSTRAINTS ;
DROP TABLE PARTSUPP CASCADE CONSTRAINTS ;
DROP TABLE PART CASCADE CONSTRAINTS ;
DROP TABLE SUPPLIER CASCADE CONSTRAINTS ;
DROP TABLE NATION CASCADE CONSTRAINTS ;
DROP TABLE REGION CASCADE CONSTRAINTS ;
CREATE TABLE REGION(r_regionkey INTEGER NOT NULL
PRIMARY KEY,
r_name VARCHAR2 (25) NOT NULL,
r_comment VARCHAR2 (152) NOT NULL
);
CREATE TABLE NATION(
n_nationkey INTEGER NOT NULL PRIMARY KEY ,
n_name VARCHAR2(25) NOT NULL,
n_regionkey INTEGER NOT NULL,
n_comment VARCHAR2(152) NOT NULL,
CONSTRAINT fk_nation FOREIGN KEY(n_regionkey) references
REGION(r_regionkey));
CREATE TABLE CUSTOMER( c_custkey INTEGER NOT NULL
primary key,
c_name VARCHAR2(25) NOT NULL,
c_address VARCHAR2(40) NOT NULL,
c_nationkey INTEGER NOT NULL ,
c_phone VARCHAR2(15) NOT NULL,
c_acctbal NUMBER (*,2) NOT NULL,
c_mktsegment VARCHAR2(10) NOT NULL,
c_comment VARCHAR2(117) NOT NULL,
CONSTRAINT fk_customer FOREIGN KEY(c_nationkey) references
NATION(n_nationkey));
CREATE TABLE SUPPLIER(
s_suppkey INTEGER NOT NULL PRIMARY KEY,
s_name VARCHAR2 (25) NOT NULL,
s_address VARCHAR2 (40) NOT NULL,
s_nationkey INTEGER NOT NULL ,
s_phone VARCHAR2 (15) NOT NULL,
s_acctbal NUMBER (*,2) NOT NULL,
s_comment VARCHAR2 (101) NOT NULL,
CONSTRAINT fk_supplier FOREIGN KEY(s_nationkey) references
NATION(n_nationkey));
CREATE TABLE PART(
p_partkey INTEGER NOT NULL PRIMARY KEY,
p_name VARCHAR2(55) NOT NULL,
p_mfgr VARCHAR2(25) NOT NULL,
p_brand VARCHAR2(10) NOT NULL,
p_type VARCHAR2(25) NOT NULL,
p_size INTEGER NOT NULL,
p_container VARCHAR2(10) NOT NULL,
p_retailprice INTEGER NOT NULL,
p_comment VARCHAR2(23) NOT NULL);
CREATE TABLE PARTSUPP(
ps_partkey INTEGER NOT NULL ,
ps_suppkey INTEGER NOT NULL ,
ps_availqty INTEGER NOT NULL,
ps_supplycost INTEGER NOT NULL,
ps_comment VARCHAR2 (199) NOT NULL,
CONSTRAINT pk_partsupp PRIMARY KEY (ps_partkey, ps_suppkey),
CONSTRAINT fk1_partsupp FOREIGN KEY(ps_suppkey) references
SUPPLIER(s_suppkey),
CONSTRAINT fk2_partsupp FOREIGN KEY(ps_partkey) references
PART(p_partkey));
CREATE TABLE ORDERS(
o_orderkey INTEGER NOT NULL PRIMARY KEY,
o_custkey INTEGER NOT NULL,
o_orderstatus CHAR(1) NOT NULL,
o_totalprice INTEGER NOT NULL,
o_orderdate DATE NOT NULL,
o_orderpriority VARCHAR2(15) NOT NULL,
o_clerk VARCHAR2(15) NOT NULL,
o_shippriority INTEGER NOT NULL,
o_comment VARCHAR2(79) NOT NULL,
CONSTRAINT fk_orders FOREIGN KEY(o_custkey) references
CUSTOMER(c_custkey));
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 INTEGER NOT NULL,
l_extendedprice INTEGER NOT NULL,
l_discount INTEGER NOT NULL,
l_tax INTEGER 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 VARCHAR2(25) NOT NULL,
l_shipmode VARCHAR2(10) NOT NULL,
l_comment VARCHAR2(44) NOT NULL,
CONSTRAINT pk_lineitem PRIMARY KEY (l_orderkey, l_linenumber),
CONSTRAINT fk1_lineorder FOREIGN KEY(l_orderkey) references ORDERS(o_orderkey), 
CONSTRAINT fk2_lineorder FOREIGN KEY(l_partkey, l_suppkey) references PARTSUPP(ps_partkey,
ps_suppkey));