formula1.sql

-- Exercise: create a database for Formula 1 by following the diagram and writing a set of CREATE TABLE statements

CREATE TABLE teams (
    team_id INT NOT NULL,
    name TEXT NOT NULL,
    country TEXT,
    PRIMARY KEY (team_id)
);

CREATE TABLE cars (
    car_number INT NOT NULL,
    weight INT,
    max_speed INT,
    team_id INT,
    PRIMARY KEY (car_number),
    FOREIGN KEY (team_id) REFERENCES teams(team_id)
);

CREATE TABLE pilots (
    pilot_id INT NOT NULL,
    name TEXT,
    birthdate DATE,
    country TEXT,
    car_number INT,
    PRIMARY KEY (pilot_id),
    FOREIGN KEY (car_number) REFERENCES cars(car_number)
);

CREATE TABLE circuits (
    circuit_id INT NOT NULL,
    city TEXT,
    country TEXT,
    distance INT,
    PRIMARY KEY (circuit_id)
);

CREATE TABLE races (
    race_id INT NOT NULL,
    circuit_id INT,
    date DATE,
    number_of_laps INT,
    PRIMARY KEY (race_id),
    FOREIGN KEY (circuit_id) REFERENCES circuits(circuit_id)
);

CREATE TABLE participations (
    participation_id INT NOT NULL,
    pilot_id INT,
    race_id INT,
    starting_position INT,
    final_position INT,
    PRIMARY KEY (participation_id),
    FOREIGN KEY (pilot_id) REFERENCES pilots(pilot_id),
    FOREIGN KEY (race_id) REFERENCES races(race_id)
);