gistfile1.txt

USE Formule1;

CREATE TABLE team (
    id INTEGER  PRIMARY KEY,
    name VARCHAR(100) UNIQUE ,
    country VARCHAR(100),
);

CREATE TABLE car (
    id INTEGER PRIMARY KEY,
    `number` INTEGER NOT NULL, 
    weight INTEGER NOT NULL,
    max_speed INTEGER NOT NULL,
    team_id INTEGER NOT NULL,
    FOREIGN KEY (team_id) REFERENCES team(id),
    CHECK (weight > 0 AND max_speed > 0),
    UNIQUE (`number`, team_id)
);

CREATE TABLE pilot (
    id INTEGER PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    birthdate DATE NOT NULL,
    country VARCHAR(100) NOT NULL,
    car_id INTEGER NOT NULL,
    FOREIGN KEY (car_id) REFERENCES car(id)
);

CREATE TABLE circuit (
    id INTEGER PRIMARY KEY,
    city VARCHAR(100) NOT NULL,
    country VARCHAR(100) NOT NULL,
    distance INTEGER NOT NULL,
    CHECK (distance > 0)
);

CREATE TABLE race (
    id INTEGER PRIMARY KEY 
    `date` DATE NOT NULL DEFAULT (CURRENT_DATE),
    number_of_laps INTEGER NOT NULL,
    circuit_id INTEGER NOT NULL,
    FOREIGN KEY (circuit_id) REFERENCES circuit(id),
    CHECK (number_of_laps > 0),
    UNIQUE (`date`, circuit_id)
);

CREATE TABLE participation (
    pilot_id INTEGER NOT NULL,
    race_id INTEGER NOT NULL,
    starting_position INTEGER NOT NULL,
    final_position INTEGER, 
    FOREIGN KEY (pilot_id) REFERENCES pilot(id),
    FOREIGN KEY (race_id) REFERENCES race(id),
    PRIMARY KEY (pilot_id, race_id)
);