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);
insert into parts values 
  (20001, 'Braveheart', 1000, 15.99, 20);
insert into parts values 
  (20002, 'Lawrence of Arabia', 1000, 15.99, 20);
insert into parts values 
  (20003, 'Casablanca', 1000, 15.99, 20);
insert into parts values 
  (20004, 'Mickey Blue Eyes', 1000, 15.99, 20);
insert into parts values 
  (20005, 'The Godfather I', 1000, 15.99, 20);
insert into parts values 
  (20006, 'The Godfather II', 1000, 15.99, 20);
insert into parts values 
  (20007, 'The Godfather III', 1000, 15.99, 20);
insert into parts values 
  (20008, 'The Mummy', 1000, 15.99, 20);
insert into parts values 
  (20009, 'Instinct', 1000, 15.99, 20);
insert into parts values 
  (20010, 'Star Wars', 1000, 15.99, 20);
insert into parts values 
  (20011, 'Sci-Fi', 1000, 15.99, 20);
insert into parts values 
  (20012, 'Payback', 1000, 15.99, 20);
insert into parts values 
  (20013, 'Matrix', 1000, 15.99, 20);
insert into parts values 
  (20014, 'Titanic', 1000, 15.99, 20);
insert into parts values 
  (20015, 'Mission Impossible', 1000, 15.99, 20);
insert into parts values 
  (20016, 'Broken Arrow', 1000, 15.99, 20);
insert into parts values 
  (20017, 'Braveheart', 1000, 15.99, 20);
insert into parts values 
  (20018, 'Air Force One', 1000, 15.99, 20);
insert into parts values 
  (20019, 'Entrapment', 1000, 15.99, 20);
insert into parts values 
  (20020, 'Gone with the Wind', 1000, 15.99, 20);