formula1.sql

create table team (
	id integer auto_increment,
	name varchar(50) not null unique,
	country text not null,
	primary key (id)
);

create table car (
	id integer,
	carNumber integer not null unique,
	weight integer not null check (weight > 0),
	maxSpeed integer not null check (maxSpeed > 0),
	team_id integer,
	foreign key (team_id) references team(id),
	primary key (id)
);

create table pilot (
	id integer,
	name text not null,
	birthDate date not null,
	country text not null,
	team_id integer,
	car_id integer,
	foreign key (team_id) references team(id),
	foreign key (car_id) references car(id),
	primary key (id)
);

create table circuit (
	id integer not null unique,
	city text not null,
	country text not null,
	distance integer not null check (distance > 0),
	primary key (id)
);

create table race (
	id integer,
	race_date date default (current_date),
	numberOfLaps integer check (numberOfLaps > 0),
	circuit_id integer,
	foreign key (circuit_id) references circuit(id),
	primary key (id)
);

create table participation (
	startingPosition integer not null,
	finalPosition integer not null,
	pilot_id integer,
	race_id integer,
	foreign key (pilot_id) references pilot(id),
	foreign key (race_id) references race(id),
	primary key (pilot_id, race_id)
);