----------
-- set up inventory and orders tables for trigger examples
-- (includes bonus: naming of primary and foreign key 
-- constraints)
--
-- last modified: 2013-12-02
----------

--------
-- set up tables for trigger example
--------

-- set up an inventory table
-- (bonus aspect: giving a name to the primary key, which is
-- actually a constraint object)

drop table inventory cascade constraints;

create table inventory
(item_num                varchar2(3),
 item_name               varchar2(15),
 item_quantity           integer                default(0),
 item_price              decimal(5,2),
 constraint inventory_pk primary key(item_num));

-- set up a simple orders table

drop table orders cascade constraints;

create table orders
(order_num      varchar2(3),
 cust_name      varchar2(20),
 item_num       varchar2(3)     not null,
 order_quantity integer         default(1),
 constraint     orders_pk       primary key(order_num),
 constraint     inventory_orders_fk 
                foreign key     (item_num)      references inventory);