Week2 : Exercise3 : Hitomi

formula1.sql

CREATE TABLE Team (
id INTEGER NOT NULL,
name TEXT NOT NULL,
country TEXT,
PRIMARY KEY (id)
);

CREATE TABLE Car (
carNumber INTEGER,
wieght INTEGER,
maxSpeed INTEGER,
team_id INTEGER,
FOREIGN KEY (team_id) REFERENCES Team (id),
PRIMARY KEY (carNumber)
);

CREATE TABLE Pilot (
id INTEGER NOT NULL,
name TEXT NOT NULL,
birthDate DATE NOT NULL,
country TEXT,
carNumber INTEGER,
FOREIGN KEY (carNumber) REFERENCES Car (carNumber),
PRIMARY KEY (id)
);

CREATE TABLE Race (
id INTEGER NOT NULL,
name TEXT,
racedate DATE,
numberOfLaps INTEGER,
PRIMARY KEY (id)
);

CREATE TABLE Participation (
pilot_id INTEGER,
race_id INTEGER,
startingPosition INTEGER,
finalPosition INTEGER,
FOREIGN KEY (pilot_id) REFERENCES Pilot (id),
FOREIGN KEY (race_id) REFERENCES Race (id)
);

CREATE TABLE Circuit (
id INTEGER NOT NULL,
city TEXT NOT NULL,
country TEXT NOT NULL,
distance INTEGER NOT NULL,
PRIMARY KEY (id)
);

ALTER TABLE Race ADD COLUMN circuit_id INTEGER;
ALTER TABLE Race ADD FOREIGN KEY (circuit_id) REFERENCES Circuit (id);