-------------------------------------------------------------
-- cs3200 Database design
-- v180121
-------------------------------------------------------------



-------------------------
-- Drop tables if they already exist
-------------------------



DO $$
BEGIN
IF EXISTS (SELECT * FROM information_schema.tables 
	WHERE table_name = 'item') THEN
   DROP Table Item;
END IF;
IF EXISTS (SELECT * FROM information_schema.tables 
	WHERE table_name = 'purchase2') THEN
   DROP Table Purchase2;
END IF;


---------------------------
-- Create the tables
--------------------------- 

create table Item ( 
	name varchar(20) PRIMARY KEY, 
	category varchar(20)); 

create table Purchase2 ( 
	iName varchar(20), 
	store varchar(20), 
	month int, 
PRIMARY KEY (iname,store,month) ); 


---------------------------
-- Populate the tables
---------------------------

insert into Item values ('Gizmo', 'Gadget'); 
insert into Item values ('Camera', 'Photo'); 
insert into Item values ('OneClick', 'Photo'); 

insert into Purchase2 values ('Gizmo', 'Wiz', 8); 
insert into Purchase2 values ('Camera', 'Ritz', 8); 
insert into Purchase2 values ('Camera', 'Wiz', 9); 
END $$