/* clean up old tables;
   must drop tables with foreign keys first
   due to referential integrity constraints
 */

delete from manages;
drop table manages;

delete from works;
drop table works;

delete from company;
drop table company;

delete from employee;
drop table employee;

create table employee
   (employee_name 	varchar(15)	not null primary key,
    street	 	varchar(12)	not null,
    city	 	varchar(15)	not null);

create table company
   (company_name 	varchar(15)	not null primary key,
    city	 	varchar(15)	not null);

create table works
   (employee_name 	varchar(15)	not null primary key,
    company_name 	varchar(15)	not null,
    salary              number          not null,
    foreign key(company_name) references company(company_name),
    foreign key(employee_name) references employee(employee_name));

create table manages
   (employee_name	varchar(15)	not null primary key,
    manager_name        varchar(15)     not null,
    foreign key(employee_name) references works(employee_name),
    foreign key(manager_name) references works(employee_name));

/* populate relations */

insert into employee	values ('Jones',	'Main',		'Harrison');
insert into employee	values ('Smith',	'Main',		'Rye');
insert into employee	values ('Hayes',	'Main',		'Harrison');
insert into employee	values ('Curry',	'First',	'Rye');
insert into employee	values ('Lindsay',	'Park',		'Pittsfield');
insert into employee	values ('Turner',	'Putnam',	'Stamford');
insert into employee	values ('Piazza',	'Palm',		'Rye');
insert into employee	values ('Snyder',	'Lincoln',	'Rye');
insert into employee	values ('Williams',	'Nassau',	'Princeton');
insert into employee	values ('Gates',	'Elm',		'Rye');
insert into employee	values ('Adams',	'Park',		'Pittsfield');
insert into employee	values ('Johnson',	'Alma',		'Palo Alto');
insert into employee	values ('Glenn',	'Sand Hill',	'Woodside');
insert into employee	values ('McCoy',	'First',	'Rye');
insert into employee	values ('Wright',	'Safety',	'Rye');
insert into employee	values ('Brooks',	'Senator',	'Brooklyn');
insert into employee	values ('Green',	'Walnut',	'Stamford');
insert into employee	values ('Jackson',	'University',	'Salt Lake');
insert into employee	values ('Majeris',	'First',	'Rye');
insert into employee	values ('McBride',	'Safety',	'Rye');
insert into employee	values ('Brady',	'Senator',	'Brooklyn');

insert into company	values ('First Bank',	'Brooklyn');
insert into company	values ('Small Bank',	'Palo Alto');
insert into company	values ('IBM',	 	'Horseneck');
insert into company	values ('PizzaHut',	'Brooklyn');
insert into company	values ('Intel',	'Horseneck');
insert into company	values ('Microsoft',	'Horseneck');
insert into company	values ('Boeing',	'Bennington');
insert into company	values ('Lockheed',	'Rye');
insert into company	values ('McDonalds',	'Rye');

insert into works	values ('Jones',	'Microsoft',	'40000');
insert into works	values ('Smith',	'Intel',	'30000');
insert into works	values ('Snyder',	'Small Bank',	'20000');
insert into works	values ('Hayes',	'Microsoft',	'45000');
insert into works	values ('Curry',	'First Bank',	'50000');
insert into works	values ('Lindsay',	'Small Bank',	'25000');
insert into works	values ('Turner',	'First Bank',	'28000');
insert into works	values ('Williams',	'Boeing',	'30000');
insert into works	values ('Adams',	'Small Bank',	'15000');
insert into works	values ('Johnson',	'Small Bank',	'22000');
insert into works	values ('Glenn',	'First Bank',	'100000');
insert into works	values ('Wright',	'Boeing',	'80000');
insert into works	values ('Brooks',	'Small Bank',	'25000');
insert into works	values ('Green',	'First Bank',   '15000');
insert into works	values ('Jackson',	'IBM',		'200000');
insert into works	values ('Majeris',	'Lockheed',	'70000');
insert into works	values ('McBride',	'IBM',		'10000');
insert into works	values ('Gates',	'Microsoft',	'1000000');
insert into works	values ('McCoy',	'McDonalds',	'15000');
insert into works	values ('Piazza',	'PizzaHut',	'20000');
insert into works	values ('Brady',	'Boeing',	'66000');

insert into manages	values ('Glenn',	'Green');
insert into manages	values ('Green',	'Turner');
insert into manages	values ('Hayes',	'Jones');
insert into manages	values ('Adams',	'Lindsay');
insert into manages	values ('Gates',	'Jones');
insert into manages	values ('McBride',	'Jackson');
insert into manages	values ('Lindsay',	'Brooks');






