formula1

formula1.sql

-- Create Team table
CREATE TABLE Team (
    team_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    country VARCHAR(100) NOT NULL
);

-- Create Car table
CREATE TABLE Car (
    car_number INT PRIMARY KEY,
    weight FLOAT NOT NULL,
    max_speed FLOAT NOT NULL,
    team_id INT,
    FOREIGN KEY (team_id) REFERENCES Team(team_id)
);

-- Create Pilot table
CREATE TABLE Pilot (
    pilot_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    birth_date DATE NOT NULL,
    country VARCHAR(100) NOT NULL,
    car_number INT,
    FOREIGN KEY (car_number) REFERENCES Car(car_number)
);

-- Create Circuit table
CREATE TABLE Circuit (
    circuit_id INT PRIMARY KEY AUTO_INCREMENT,
    city VARCHAR(100) NOT NULL,
    country VARCHAR(100) NOT NULL,
    distance FLOAT NOT NULL
);

-- Create Race table
CREATE TABLE Race (
    race_id INT PRIMARY KEY AUTO_INCREMENT,
    date DATE NOT NULL,
    number_of_laps INT NOT NULL,
    circuit_id INT,
    FOREIGN KEY (circuit_id) REFERENCES Circuit(circuit_id)
);

-- Create Participation table
CREATE TABLE Participation (
    participation_id INT PRIMARY KEY AUTO_INCREMENT,
    pilot_id INT,
    race_id INT,
    starting_position INT NOT NULL,
    final_position INT,
    FOREIGN KEY (pilot_id) REFERENCES Pilot(pilot_id),
    FOREIGN KEY (race_id) REFERENCES Race(race_id)
);