create table employee(
	employee_name	varchar(20),
	street			varchar(20),
	city			varchar(20),
	primary key (employee_name)
);

create table company(
	company_name	varchar(30), 
	city			varchar(20),
	primary key (company_name)
);

create table works(
	employee_name	varchar(20), 
	company_name	varchar(30), 
	salary			numeric(8,2),
	primary key (employee_name),
    foreign key (employee_name) references employee(employee_name),
    foreign key (company_name) references company(company_name)
);

create table manages(
	employee_name	varchar(20), 
	manager_name	varchar(20),
	primary key (employee_name),
    foreign key (employee_name) references employee(employee_name),
    foreign key (manager_name) references employee(employee_name)
);


insert into employee values ('Heinz Mustermann', 'Rosengarten 70', 'Mannheim');
insert into employee values ('Max Mustermann', 'Musterstraße', 'Musterstadt');
insert into employee values ('James Meist', 'Saalstraße', 'Darmstadt');
insert into employee values ('Maxi Schmidt', 'Schillerstraße', 'Worms');
insert into employee values ('Julia Hems', 'Robertstraße', 'Speyer');
insert into employee values ('Christoph Rot', 'Salisstraße', 'Heidelberg');
insert into employee values ('Jones', 'Teststraße', 'Speyer');

insert into company values ('ABB', 'Mannheim');
insert into company values ('Small Bank Corporation', 'Mannheim');
insert into company values ('Small Bank Corporation Ludwigshafen', 'Ludwigshafen');
insert into company values ('BASF', 'Ludwigshafen');
insert into company values ('HeidelbergCement', 'Heidelberg');
insert into company values ('Deutsche Bank Heidelberg', 'Heidelberg');
insert into company values ('Fraport', 'Frankfurt');
insert into company values ('Deutsche Bank Frankfurt', 'Frankfurt');
insert into company values ('First Bank Corporation', 'Mannheim');

insert into works values ('Heinz Mustermann', 'ABB', '45041,70');
insert into works values ('Max Mustermann', 'ABB', '50451,70');
insert into works values ('James Meist', 'HeidelbergCement', '60000,62');
insert into works values ('Maxi Schmidt', 'First Bank Corporation', '41000,80');
insert into works values ('Julia Hems', 'First Bank Corporation', '52121,00');
insert into works values ('Christoph Rot', 'First Bank Corporation', '105100,00');
insert into works values ('Jones', 'ABB', '15041,70');

insert into manages values ('Maxi Schmidt', 'Julia Hems');
insert into manages values ('Christoph Rot', 'Maxi Schmidt');
insert into manages values ('Heinz Mustermann', 'Max Mustermann');

