-----------------------------------------------------------------
--  PLSQL                                                       -
--  Create Mail Order tables (incl. restock, odetails_errors    -
--  and orders_errors tables)                                   -
-----------------------------------------------------------------
SET echo ON
SET termout ON
SET feedback ON
SET pause ON
pause Press ENTER/RETURN TO continue

DROP TABLE zipcodes CASCADE CONSTRAINTS;
CREATE TABLE zipcodes (
  zip      NUMBER(5),
  city     VARCHAR2(30),
  PRIMARY KEY (zip));

DROP TABLE employees CASCADE CONSTRAINTS;
CREATE TABLE employees (
  eno      NUMBER(4) NOT NULL PRIMARY KEY,
  ename    VARCHAR2(30),
  zip      NUMBER(5) references zipcodes,
  hdate    DATE);

DROP TABLE parts CASCADE CONSTRAINTS;
CREATE TABLE parts(
  pno      NUMBER(5) NOT NULL PRIMARY KEY,
  pname    VARCHAR2(30),
  qoh      INTEGER CHECK(qoh >= 0),
  price    NUMBER(6,2) CHECK(price >= 0.0),
  olevel   INTEGER);

DROP TABLE customers CASCADE CONSTRAINTS;
CREATE TABLE customers (
  cno      NUMBER(5) NOT NULL PRIMARY KEY,
  cname    VARCHAR2(30),
  street   VARCHAR2(30),
  zip      NUMBER(5) references zipcodes,
  phone    CHAR(12));

DROP TABLE orders CASCADE CONSTRAINTS;
CREATE TABLE orders (
  ono      NUMBER(5) NOT NULL PRIMARY KEY,
  cno      NUMBER(5) references customers,
  eno      NUMBER(4) references employees,
  received DATE,
  shipped  DATE);

DROP TABLE odetails CASCADE CONSTRAINTS;
CREATE TABLE odetails (
  ono      NUMBER(5) NOT NULL references orders,
  pno      NUMBER(5) NOT NULL references parts,
  qty      INTEGER CHECK(qty > 0),
  PRIMARY KEY (ono,pno));

DROP TABLE restock CASCADE CONSTRAINTS;
CREATE TABLE restock(
  redate   DATE,
  repnum   NUMBER(5));

DROP TABLE odetails_errors;
CREATE TABLE odetails_errors (
  errdate  DATE,
  ono      NUMBER(5),
  pno      NUMBER(5),
  msg        VARCHAR2(50));

DROP TABLE orders_errors;
CREATE TABLE orders_errors (
  errdate  DATE,
  ono      NUMBER(5),
  msg        VARCHAR2(50));