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 sequence cust_seq;
create sequence cust_seq start with 1000;

drop table customers cascade constraints;
create table customers (
  cno      number(10) not null primary key,
  cname    varchar2(30),
  street   varchar2(50),
  city     varchar2(30),
  state    varchar2(30),
  zip      number(5),
  phone    char(12),
  email    varchar2(50),
  password varchar2(15),
  u_access varchar2(50),
  l_access Date);
 
drop sequence cart_seq;
create sequence cart_seq start with 1;

drop table cart cascade constraints;
create table cart(
  cartno   number(10) not null,
  cno      number(10) not null references customers,
  pno      number(5) not null references parts,
  qty      integer check(qty >= 0) not null,
  primary key (cartno, pno)); 

drop sequence o_seq;
create sequence o_seq start with 100;

drop table orders cascade constraints;
create table orders (
  ono      number(5) not null primary key,
  cno      number(10) references customers,
  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));

insert into parts values
  (10506,'Land Before Time I',200,19.99,20);
insert into parts values
  (10507,'Land Before Time II',156,19.99,20);
insert into parts values
  (10508,'Land Before Time III',190,19.99,20); 
insert into parts values
  (10509,'Land Before Time IV',60,19.99,20);
insert into parts values
  (10601,'Sleeping Beauty',300,24.99,20);
insert into parts values
  (10701,'When Harry Met Sally',120,19.99,30);
insert into parts values
  (10800,'Dirty Harry',140,14.99,30);
insert into parts values
  (10900,'Dr. Zhivago',100,24.99,30);


