create table person(
	driver_id	    varchar(20),
	name			varchar(20),
	address			varchar(20),
	primary key (driver_id)
);

create table car(
	license     	varchar(30), 
	model			varchar(20),
    year			int,
	primary key (license)
);

create table accident(
	report_number	varchar(20), 
	date	        date, 
	location		varchar(40),
	primary key (report_number)
);

create table owns(
	driver_id	varchar(20), 
	license 	varchar(20),
	primary key (driver_id, license),
    foreign key (driver_id) references person(driver_id),
    foreign key (license) references car(license)
);

create table participated(
	report_number	varchar(20), 
	license         varchar(20),
    driver_id	    varchar(20), 
	damage_amount 	numeric(8,2),
	primary key (report_number, license),
    foreign key (report_number) references accident(report_number),
    foreign key (license) references car(license),
    foreign key (driver_id) references person(driver_id)
);


insert into person values ('abc123', 'Heinz Mustermann', 'Rosengarten 70');
insert into person values ('def456', 'John Smith', 'Teststraße');
insert into person values ('ghi789', 'James Meist', 'Saalstraße');
insert into person values ('hrgr65', 'Maxi Schmidt', 'Schillerstraße');
insert into person values ('wev788', 'Julia Hems', 'Robertstraße');
insert into person values ('etn648', 'Christoph Rot', 'Salisstraße');


insert into car values ('AABB2000', 'e klasse', 1970);
insert into car values ('SGRT6487','Mazda', 2010);
insert into car values ('FEBT4657','A3', 2011);
insert into car values ('WBTR1357','i3', 2015);
insert into car values ('BRZK6574','c klasse', 1999);

insert into accident values ('AR2197', '2012-01-01', 'Berlin');
insert into accident values ('BE4684', '2009-05-03', 'Bremen');
insert into accident values ('GU1574', '1989-04-18', 'München');
insert into accident values ('WR7456', '1989-11-03', 'Köln');
insert into accident values ('VF6871', '1995-12-10', 'Speyer');

insert into owns values ('abc123', 'AABB2000');
insert into owns values ('def456', 'SGRT6487');
insert into owns values ('ghi789', 'FEBT4657');
insert into owns values ('hrgr65', 'WBTR1357');
insert into owns values ('wev788', 'BRZK6574');

insert into participated values ('AR2197', 'AABB2000', 'abc123', '1578,24');
insert into participated values ('BE4684', 'SGRT6487', 'def456', '3514,47');
insert into participated values ('GU1574', 'FEBT4657', 'ghi789', '5145,87');
insert into participated values ('WR7456', 'WBTR1357', 'hrgr65', '5875,64');
insert into participated values ('VF6871', 'BRZK6574', 'wev788', '1354,31');