formula1.sql

CREATE TABLE team(
	id integer unique,
	name VARCHAR(50) ,
	country VARCHAR(50),
	primary key (id)
);

CREATE  TABLE car(
	car_number integer unique,
	weight integer not null,
	maxSpeed integer not null,
	team_id integer ,
	FOREIGN KEY (team_id ) REFERENCES team(id),
	primary key (car_number)
);



CREATE  TABLE pilot(
	id integer unique,
	name VARCHAR(50) ,
	birthdate date , 
	country VARCHAR(50),
	team_id integer,
	car_number integer , 
	FOREIGN KEY(car_number) REFERENCES car(car_number),
	FOREIGN KEY(team_id) REFERENCES team(id),
	primary key (id)
);





CREATE TABLE race(
	id integer unique,
	raceDate date default (CURRENT_DATE()),
	numberOfLoop integer not null,
	pilot_id  integer,
	FOREIGN KEY (pilot_id ) REFERENCES pilot(id),
	primary key(id)
);


CREATE TABLE participation(
	pilot_id integer not null,
	race_id integer not null,
	startingPosition text,
	finilPosition text,
	FOREIGN KEY (pilot_id ) REFERENCES pilot(id),
	FOREIGN KEY (race_id ) REFERENCES race(id),
	primary key(pilot_id, race_id)
);


CREATE TABLE circuit(
	race_id int,
	city text  not null,
	country text not null,
	distance  decimal(6, 2) not null,
	FOREIGN KEY (race_id) REFERENCES race(id)
);