formula1.sql

create table team (
id INTEGER,
name TEXT,
country TEXT,
PRIMARY KEY (id)
);


CREATE TABLE car(
id INTEGER,
teamId INTEGER,
carNumber INTEGER,
weight INTEGER,
maxSpeed INTEGER,
PRIMARY KEY (id),
FOREIGN KEY(teamId) REFERENCES team(id)
);

CREATE TABLE pilot (
id INTEGER,
carId INTEGER,
name TEXT,
birthDate INTEGER,
country TEXT,
PRIMARY KEY (id),
FOREIGN KEY (carId) REFERENCES car(id),
);

create table race (
id INTEGER,
pilotId INTEGER,
date DATE,
numberOfLaps INTEGER,
primary KEY(id),
FOREIGN KEY (pilotId) REFERENCES pilot(id)
);

create table participation (
pilotId INTEGER,
raceId INTEGER,
startingPosition TEXT,
finalPosition TEXT,
FOREIGN KEY (pilotID) REFERENCES pilot(id),
FOREIGN KEY (raceId) REFERENCES race(id),
PRIMAR KEY(pilotId, raceId)
);

create table circuit (
city TEXT,
country TEXT,
distance INTEGER
);