HackYourFuture Belgium - SQL Databases - Week 2 - Solution 3

HackYourFuture Belgium - SQL Databases - Week 2 - Solution 3.sql

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

CREATE TABLE car (
    id INT,
    `number` INT NOT NULL, -- it is a good practice to enclose reserved keywords with backticks (`)
    weight INT NOT NULL,
    max_speed INT NOT NULL,
    team_id INT NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (team_id) REFERENCES team(id),
    CHECK (weight > 0 AND max_speed > 0),
    UNIQUE (`number`, team_id)
);

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

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

CREATE TABLE race (
    id INT,
    `date` DATE NOT NULL DEFAULT (CURRENT_DATE), -- when creating a record, if the date is not specified, the current date will be used
    number_of_laps INT NOT NULL,
    circuit_id INT NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (circuit_id) REFERENCES circuit(id),
    CHECK (number_of_laps > 0),
    UNIQUE (`date`, circuit_id)
);

CREATE TABLE participation (
    pilot_id INT NOT NULL,
    race_id INT NOT NULL,
    starting_position INT NOT NULL,
    final_position INT NULL, -- must be nullable to allow creation without a value and later update once the race is finished
    FOREIGN KEY (pilot_id) REFERENCES pilot(id),
    FOREIGN KEY (race_id) REFERENCES race(id),
    PRIMARY KEY (pilot_id, race_id)
);