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



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

DO $$
BEGIN
IF EXISTS (SELECT * FROM information_schema.tables 
	WHERE table_name = 'purchase') THEN
   DROP Table Purchase;
END IF;
IF EXISTS (SELECT * FROM information_schema.tables 
	WHERE table_name = 'customer') THEN
   DROP Table Customer;
END IF; 


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

create table Customer (
	cid int,
	name varchar(100),
	age int,
	address varchar(100),
	salary int,
PRIMARY KEY (cid) );

create table Purchase (
	oid int,
	date date,
	cid int,
	amount int,
PRIMARY KEY (oid),
FOREIGN KEY (cid) REFERENCES Customer );



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

insert into Customer values (1, 'Ramesh',   32, 'Ahmedabad', 2000);
insert into Customer values (2, 'Khilan',   25, 'Delhi'    , 1500);
insert into Customer values (3, 'kaushik',  23, 'Kota'     , 2000);
insert into Customer values (4, 'Chaitali', 25, 'Mumbai'   , 6500);
insert into Customer values (5, 'Hardik',   27, 'Bhopal'   , 8500);
insert into Customer values (6, 'Komal',    22, 'MP'       , 4500);
insert into Customer values (7, 'Muffy',    24, 'Indore'   , 10000);

insert into Purchase values (102,'2009-10-08 00:00:00',3,3000);
insert into Purchase values (100,'2009-10-08 00:00:00',3,1500);
insert into Purchase values (101,'2009-11-20 00:00:00',2,1560);
insert into Purchase values (103,'2008-05-20 00:00:00',4,2060);
END $$