CREATE TABLE city
(
    id        INTEGER not null
            primary key,
    city_name TEXT default NULL
);
city
CREATE TABLE games
(
    id         INTEGER not null
            primary key,
    games_year INTEGER          default NULL,
    games_name TEXT default NULL,
    season     TEXT default NULL
);
games
CREATE TABLE games_city
(
    games_id INTEGER default NULL,
    city_id  INTEGER default NULL,
    foreign key (city_id) references city(id),
    foreign key (games_id) references games(id)
);
games_city
CREATE TABLE medal
(
    id         INTEGER not null
            primary key,
    medal_name TEXT default NULL
);
medal
CREATE TABLE noc_region
(
    id          INTEGER not null
            primary key,
    noc         TEXT   default NULL,
    region_name TEXT default NULL
);
noc_region
CREATE TABLE person
(
    id        INTEGER not null
        primary key,
    full_name TEXT default NULL,
    gender    TEXT  default NULL,
    height    INTEGER          default NULL,
    weight    INTEGER          default NULL
);
person
CREATE TABLE games_competitor
(
    id        INTEGER not null
            primary key,
    games_id  INTEGER default NULL,
    person_id INTEGER default NULL,
    age       INTEGER default NULL,
    foreign key (games_id) references games(id),
    foreign key (person_id) references person(id)
);
games_competitor
CREATE TABLE person_region
(
    person_id INTEGER default NULL,
    region_id INTEGER default NULL,
    foreign key (person_id) references person(id),
    foreign key (region_id) references noc_region(id)
);
person_region
CREATE TABLE sport
(
    id         INTEGER not null
            primary key,
    sport_name TEXT default NULL
);
sport
CREATE TABLE event
(
    id         INTEGER not null
            primary key,
    sport_id   INTEGER          default NULL,
    event_name TEXT default NULL,
    foreign key (sport_id) references sport(id)
);
event
CREATE TABLE competitor_event
(
    event_id      INTEGER default NULL,
    competitor_id INTEGER default NULL,
    medal_id      INTEGER default NULL,
    foreign key (competitor_id) references games_competitor(id),
    foreign key (event_id) references event(id),
    foreign key (medal_id) references medal(id)
);
competitor_event
original_column_name,column_name,column_description,data_format,value_description
event_id,event id,"the id of the event
Maps to event(id)",integer,
competitor_id,competitor id,"the id of the competitor
Maps to games_competitor(id)",integer,"commonsense evidence:
The number of competitors who participated in a specific event could be calculated by grouping by event_id. "
medal_id,medal id,"the id of the medal
Maps to medal(id)",integer,"commonsense evidence:
For a specific event, the competitor whose medal_id = 1 is the champion, the competitor whose medal_id = 2 is the runner-up, and the competitor whose medal_id = 3 got third place. "
competitor_event
original_column_name,column_name,column_description,data_format,value_description
person_id,,"the id of the person
Maps to person(id)",integer,
region_id,,"the id of the noc region
Maps to noc_region(id)",integer,
person_region
original_column_name,column_name,column_description,data_format,value_description
id,,the unique identifier of the game,integer,
games_year,games year,the year of the game,integer,
games_name,games name,the name of the game,text,"commonsense evidence:
games_name is 'games_year season'"
season,,the season of the game,text,"commonsense evidence:
The Olympics are held every four years and include both summer and winter games. The summer games include a wide range of sports, such as athletics, swimming, and gymnastics, while the winter games feature sports that are played on ice or snow, such as ice hockey, figure skating, and skiing."
games
original_column_name,column_name,column_description,data_format,value_description
id,,the unique identifier of the city,integer,
city_name,city name,the name of the city,text,
city
original_column_name,column_name,column_description,data_format,value_description
id,,the unique identifier of the sport,integer,
sport_name,sport name,the name of the sport,text,
sport
original_column_name,column_name,column_description,data_format,value_description
id ,,the unique identifier of the noc region,integer,
noc,,the NOC code of the region,text,"commonsense evidence:
A NOC code is a number used by the International Olympic Committee (IOC) to identify each of the national Olympic committees (NOCs) from around the world. "
region_name,region name,the name of the region,text,
noc_region
original_column_name,column_name,column_description,data_format,value_description
games_id,games id,"the id of the game
Maps to games(id)",integer,
city_id,city id,"the id of the city that held the game
Maps to city(id)",integer,
games_city
original_column_name,column_name,column_description,data_format,value_description
id ,,the unique identifier of the record,,
games_id,games id,"the id of the game
Maps to games(id)",integer,
person_id,person id,"the id of the person
Maps to person(id)",integer,"commonsense evidence:
The times a person participated in the Olympics games could be calculated by grouping by person_id. "
age,,the age of the person when he/she participated in the game,integer,"commonsense evidence:
If person A's age was 22 when he participated in game A and his age was 24 when he participated in game B, it means that game A was held two years earlier than game B."
games_competitor
original_column_name,column_name,column_description,data_format,value_description
id,,the unique identifier of the event,integer,
sport_id,sport id,"the id of the sport
Maps to sport(id)",integer,"commonsense evidence:
There may be different events for the same sport in Olympics. "
event_name,event name,the name of the event,text,
event
original_column_name,column_name,column_description,data_format,value_description
id ,,the unique identifier of the metal,integer,
medal_name,medal name,the name of the medal,text,
medal
original_column_name,column_name,column_description,data_format,value_description
id,,the unique identifier of the person,integer,
full_name,full name,the full name of the person,text,"commonsense evidence:
A person's full name is the complete name that they are known by, which typically includes their first name, middle name (if applicable), and last name. "
gender,,the gender of the person,text,"commonsense evidence:
M stands for male and F stands for female. "
height,,the height of the person,integer,
weight,,the weight of the person,integer,"commonsense evidence:
The unit of height is cm, and the unit of weight is kg. If the height or the weight of the person is 0, it means his/her height or weight is missing. "
person