formula1.sql

formula1.sql

CREATE TABLE Team (
    team_id INT PRIMARY KEY,
    name VARCHAR(100),
    country VARCHAR(100),
    UNIQUE (name)
)

CREATE TABLE Car (
    carNumber INT PRIMARY KEY,
    weight INT,
    maxSpeed INT,
    team_id INT,
    FOREIGN KEY (team_id) REFERENCES Team(team_id)
)

CREATE TABLE Pilot (
    pilot_id INT PRIMARY KEY,
    name VARCHAR(100),
    birthDate DATE,
    carNumber INT,
    FOREIGN KEY (carNumber) REFERENCES Car(carNumber)
)

CREATE TABLE Circuit (
   circuit_id INT PRIMARY KEY,
   city VARCHAR(100),
   country VARCHAR(100),
   distance INT
)

CREATE TABLE Race (
   race_id INT PRIMARY KEY,
   date DATE,
   numberOfLaps INT,
   circuit_id INT,
   FOREIGN KEY (circuit_id) REFERENCES Circuit(circuit_id)
)

CREATE TABLE Participation (
  pilot_id INT ,
  race_id INT ,
  startingPosition INT ,
  finalPosition INT ,
  FOREIGN KEY (pilot_id) REFERENCES Pilot(pilot_id),
  FOREIGN KEY (race_id) REFERENCES Race(race_Id),
  PRIMARY KEY (pilot_id, race_id)
)