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



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


DO $$
BEGIN
IF EXISTS (SELECT * FROM information_schema.tables 
	WHERE table_name = 'likes') THEN
   DROP Table Likes;
END IF;
IF EXISTS (SELECT * FROM information_schema.tables 
	WHERE table_name = 'frequents') THEN
   DROP Table Frequents;
END IF;
IF EXISTS (SELECT * FROM information_schema.tables 
	WHERE table_name = 'serves') THEN
   DROP Table Serves;
END IF;


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

create table Likes( 
	person char(20), 
	drink char(20), 
PRIMARY KEY (person, drink)); 

create table Frequents( 
	person char(20), 
	bar char(20), 
PRIMARY KEY (person, bar));

create table Serves( 
	bar char(20), 
	drink char(20), 
PRIMARY KEY (bar, drink)); 


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

insert into Likes values ('Alice', 'Whitebeer'); 
insert into Likes values ('Bob', 'Brownbeer'); 
insert into Likes values ('Charlie', 'Whitebeer'); 
insert into Likes values ('Charlie', 'Blackbeer'); 

insert into Serves values ('Groundbar', 'Whitebeer'); 
insert into Serves values ('Seabar', 'Whitebeer'); 
insert into Serves values ('Seabar', 'Blackbeer'); 
insert into Serves values ('Skybar', 'Whitebeer'); 
insert into Serves values ('Skybar', 'Brownbeer'); 
insert into Serves values ('Skybar', 'Blackbeer'); 

insert into Frequents values ('Alice', 'Seabar'); 
insert into Frequents values ('Alice', 'Skybar'); 
insert into Frequents values ('Bob', 'Groundbar'); 
insert into Frequents values ('Bob', 'Seabar'); 
insert into Frequents values ('Charlie', 'Seabar'); 
END $$