Andre Long
CISP 350 Oracle
Febuary 28, 2006
Chapter 2 PSC 

CASE#4

CREATE TABLE category
( cat_id	number(6),
  cat_desc	varchar2(20),
  CONSTRAINT	cattegory_cat_id_pk		PRIMARY KEY(cat_id));
  
CREATE TABLE shipment
( ship_id	number(6),
  ship_date_expected	date,
  CONSTRAINT	shipment_ship_id_pk		PRIMARY KEY(ship_id));
  
CREATE TABLE item
( item_id	number(6),
  item_desc	varchar2(30),
  cat_id	number(6),
  item_image	blob,
  CONSTRAINT	item_item_id_pk	PRIMARY KEY(item_id),
  CONSTRAINT	item_cat_id_fk	FOREIGN KEY(cat_id)	REFERENCES category(cat_id));
  
CREATE TABLE color
( color		varchar2(20),
  CONSTRAINT	color_color_pk	PRIMARY KEY(color));
  
CREATE TABLE inventory
( inv_id	number(6),
  item_id	number(6),
  color		varchar2(20),
  inv_size	varchar2(4),
  inv_price	number(6),
  inv_qoh	number(6),
  CONSTRAINT	inventory_inv_id_pk	PRIMARY KEY(inv_id),
  CONSTRAINT	inventory_item_id_fk	FOREIGN KEY(item_id)	REFERENCES item(item_id),
  CONSTRAINT	inventory_color_fk	FOREIGN KEY(color)	REFERENCES color(color));
  
CREATE TABLE order_line
( o_id		number(6),
  inv_id	number(6),
  ql_quantity	number(6),
  CONSTRAINT	order_line_o_id_fk	FOREIGN KEY(o_id)	REFERENCES orders(o_id),
  CONSTRAINT	order_line_inv_id_fk	FOREIGN KEY(inv_id)	REFERENCES inventory(inv_id));
  
CREATE TABLE shipment_line
( ship_id	number(6),
  inv_id	number(6),
  sl_quantity	number(6),
  sl_date_recieved	date,
  CONSTRAINT	shipment_line_ship_id_fk	FOREIGN KEY(ship_id)	REFERENCES shipment(ship_id),
  CONSTRAINT	shipment_line_inv_id_fk		FOREIGN KEY(inv_id)		REFERENCES inventory(inv_id));