create table manufacturer
(manufacturer_id varchar2(5) primary key,
 name varchar2(30),
 city varchar2(20),
 state varchar2(2),
 rep_id number(2),
 bid_preference number(2));

create table ship
(ship_no varchar2(5) primary key,
class number(2),
capacity number(20,2),
purch_date date,
manufacturer_id varchar2(5) references manufacturer(manufacturer_id));

create table captain
(capt_id  varchar2(6) primary key,
license_grade number(2),
fname varchar2(25),
lname varchar2(25),
dob  date);

create table item
(item_no   varchar2(5) primary key,
item_type  varchar2(5),
description varchar2(40),
weight	number(10,2));

create table distance
(origin  varchar2(20),
destination varchar2(20),
miles  number(15,2),
days number(4),
primary key (origin,destination));

create table shipment
(shipment_id  varchar2(7) primary key,
ship_no  varchar2(5)  references ship(ship_no),
capt_id  varchar2(6)  references captain(capt_id),
shipment_date  date,
origin  varchar2(20),
destination varchar2(20),
arrival_date  date,
foreign key (origin,destination) references distance(origin,destination));

create table shipment_line
(shipment_id varchar2(7) references shipment(shipment_id),
item_no varchar2(5) references item(item_no),
quantity number (10,2),
primary key (shipment_id,item_no));


