Task: equi-join-detect (source dataset: Auto-BI)

Your are given a database with related tables used for business intelligence analysis. Your task is to identify all key/foreign-key join relationships between the tables for this database. Recall that a join is a relationship between two tables, that allows rows from one table to be combined with rows from another, based on a set of common join columns.
 
 
No explanation needed. Please return all join relationships in the following JSON format:
{
  "joins": [
    {
      "from_table": "table-1",
      "from_column": "column-1",
      "to_table": "table-2",
      "to_column": "column-3"
    }, 
	...
  ]
}

Tables:

Table: Coal_price
| Date                    | Market       |   Price (ZAR) |
|:------------------------|:-------------|--------------:|
| 2008-01-01 00:00:00.000 | Export       |        602.11 |
| 2008-01-01 00:00:00.000 | South Africa |        604.24 |
| 2009-01-01 00:00:00.000 | Export       |        889.64 |
| 2009-01-01 00:00:00.000 | South Africa |        690.36 |
| 2010-01-01 00:00:00.000 | Export       |        776.83 |
| 2010-01-01 00:00:00.000 | South Africa |        781.75 |
| 2011-01-01 00:00:00.000 | Export       |        864.65 |
| 2011-01-01 00:00:00.000 | South Africa |        898.9  |
| 2012-01-01 00:00:00.000 | Export       |        961    |
| 2012-01-01 00:00:00.000 | South Africa |        957    |


Table: data3
|   years |   Number of Executives Resigned |   Number of non-executive resigned | coal cost(%)   |
|--------:|--------------------------------:|-----------------------------------:|:---------------|
|    2014 |                               1 |                                 13 |                |
|    2015 |                               1 |                                  3 |                |
|    2016 |                               1 |                                  8 |                |
|    2017 |                               1 |                                  8 | 3.5            |
|    2018 |                               3 |                                  9 | 3.8            |
|    2019 |                               6 |                                  3 | 4              |


Table: Debt
|   years |   Revenue (R billion) | Operating profit (R billion)   | Total debt (R billion)   |
|--------:|----------------------:|:-------------------------------|:-------------------------|
|    2008 |                  44.4 |                                | 105                      |
|    2009 |                  53.3 |                                | 139.7                    |
|    2010 |                  71.1 | 2.3                            |                          |
|    2011 |                  91.4 |                                | 178.5                    |
|    2012 |                 114.7 | 13.2                           | 213.3                    |
|    2013 |                 128.8 | 18.8                           | 236.7                    |
|    2014 |                 138.3 | 16.5                           | 234.5                    |
|    2015 |                 147.7 | 3.6                            | 277.4                    |
|    2016 |                 164.2 | 5.1                            | 306.9                    |
|    2017 |                 177.1 | 0.888                          | 336.7                    |


Table: Employees
| Year                    | Employee metric           |   Value | Total Employee Cost to Company   |
|:------------------------|:--------------------------|--------:|:---------------------------------|
| 2003-01-01 00:00:00.000 | Number of employees       |   31972 |                                  |
| 2003-01-01 00:00:00.000 | Average cost per employee |  348333 | 11136902676                      |
| 2004-01-01 00:00:00.000 | Number of employees       |   31520 |                                  |
| 2004-01-01 00:00:00.000 | Average cost per employee |  366946 | 11566137920                      |
| 2005-01-01 00:00:00.000 | Number of employees       |   31475 |                                  |
| 2005-01-01 00:00:00.000 | Average cost per employee |  447562 | 14087013950                      |
| 2006-01-01 00:00:00.000 | Number of employees       |   31485 |                                  |
| 2006-01-01 00:00:00.000 | Average cost per employee |  241156 | 7592796660                       |
| 2007-01-01 00:00:00.000 | Number of employees       |   32674 |                                  |
| 2007-01-01 00:00:00.000 | Average cost per employee |  289251 | 9450987174                       |


Table: executive
| year      | CEO Title        |   Period(Months) | Salaries   |
|:----------|:-----------------|-----------------:|:-----------|
| 2009/2010 | Mpho Makwana     |                8 |            |
| 2010/2014 | Brian Dames      |               45 | 5.7        |
| 2014      | collin matjila   |                6 |            |
| 2014/2015 | tshediso matona  |                7 |            |
| 2015/2016 | brian Molefe     |               19 | 9.47       |
| 2016/2017 | Matshela koko    |                6 | 3.8        |
| 2017      | johnny dladla    |                5 |            |
| 2017/2018 | sean maritz      |                4 | 2.6        |
| 2018/2019 | phakamani hadebe |               19 | 8.6        |
| 2019      | jabu mabuza      |                6 | 1.6        |


Table: Location
|   id | location    |   province_id_fk |
|-----:|:------------|-----------------:|
|    1 | Atlantis    |                4 |
|    2 | Balfour     |                1 |
|    3 | Bergville   |                8 |
|    4 | Bethal      |                1 |
|    5 | Cape Town   |                4 |
|    6 | East London |                5 |
|    7 | Emalahleni  |                1 |
|    8 | Ermelo      |                1 |
|    9 | Grabouw     |                4 |
|   10 | Ladysmith   |                8 |


Table: Province
|   id | province      |
|-----:|:--------------|
|    1 | Mpumalanga    |
|    2 | North West    |
|    3 | Gauteng       |
|    4 | Western Cape  |
|    5 | Eastern Cape  |
|    6 | Northern Cape |
|    7 | Free State    |
|    8 | Kwazulu Natal |
|    9 | Limpopo       |


Table: Provincial_elec_distributed_GWh
| Date                    | Province     |   Electricity Distributed Gwh |
|:------------------------|:-------------|------------------------------:|
| 2002-01-01 00:00:00.000 | Western Cape |                          1605 |
| 2002-02-01 00:00:00.000 | Western Cape |                          1578 |
| 2002-03-01 00:00:00.000 | Western Cape |                          1716 |
| 2002-04-01 00:00:00.000 | Western Cape |                          1587 |
| 2002-05-01 00:00:00.000 | Western Cape |                          1666 |
| 2002-06-01 00:00:00.000 | Western Cape |                          1736 |
| 2002-07-01 00:00:00.000 | Western Cape |                          1832 |
| 2002-08-01 00:00:00.000 | Western Cape |                          1796 |
| 2002-09-01 00:00:00.000 | Western Cape |                          1658 |
| 2002-10-01 00:00:00.000 | Western Cape |                          1721 |


Table: SA_National_electricity_data
| Date                    |   Total electricity Generated (GWh) |   Total electricity imported (GWh) |   Total electricity consumed in Power Stations and Auxiliary Systems |   Total electricity exported (GWh) |   Total electricity Available for Distribution in South Africa |   Electricity Generated by Eskom |   Electricity Imported by Eskom |   Electricity Consumed in Eskom Power Stations and Auxiliary Systems |   Electricity Exported by Eskom |   Electricity Available for Distribution in South Africa from Eskom | Electricity distributed   |   Physical Volume of Electricity Production Index (Base: 2015=100) |   Total - All producers (Electricity available for distribution in South Africa) |   Physical Volume of Electricity Production Index (Base: 2015=100)_1 | Electricity generated per capita (kWh)   |
|:------------------------|------------------------------------:|-----------------------------------:|---------------------------------------------------------------------:|-----------------------------------:|---------------------------------------------------------------:|---------------------------------:|--------------------------------:|---------------------------------------------------------------------:|--------------------------------:|--------------------------------------------------------------------:|:--------------------------|-------------------------------------------------------------------:|---------------------------------------------------------------------------------:|---------------------------------------------------------------------:|:-----------------------------------------|
| 1985-01-01 00:00:00.000 |                               11119 |                                 86 |                                                                 1058 |                                 76 |                                                          10073 |                            10085 |                              82 |                                                                  892 |                              76 |                                                                9201 |                           |                                                                533 |                                                                            10461 |                                                                  554 |                                          |
| 1985-02-01 00:00:00.000 |                               10439 |                                190 |                                                                  898 |                                 69 |                                                           9662 |                             9448 |                             186 |                                                                  789 |                              69 |                                                                8776 |                           |                                                                501 |                                                                            10693 |                                                                  554 |                                          |
| 1985-03-01 00:00:00.000 |                               11660 |                                 47 |                                                                  978 |                                 79 |                                                          10650 |                            10699 |                              42 |                                                                  882 |                              79 |                                                                9780 |                           |                                                                559 |                                                                            10620 |                                                                   56 |                                          |
| 1985-04-01 00:00:00.000 |                               11214 |                                 74 |                                                                  982 |                                 70 |                                                          10236 |                            10241 |                              68 |                                                                  878 |                              70 |                                                                9361 |                           |                                                                538 |                                                                            10783 |                                                                  567 |                                          |
| 1985-05-01 00:00:00.000 |                               12091 |                                 46 |                                                                 1001 |                                 84 |                                                          11052 |                            11086 |                              40 |                                                                  892 |                              84 |                                                               10150 |                           |                                                                 58 |                                                                            10781 |                                                                  566 |                                          |
| 1985-06-01 00:00:00.000 |                               12334 |                                 71 |                                                                  998 |                                 85 |                                                          11322 |                            11055 |                              66 |                                                                  866 |                              85 |                                                               10170 |                           |                                                                592 |                                                                            10917 |                                                                   57 |                                          |
| 1985-07-01 00:00:00.000 |                               12975 |                                 30 |                                                                 1075 |                                 95 |                                                          11835 |                            11630 |                              25 |                                                                  947 |                              95 |                                                               10613 |                           |                                                                622 |                                                                            10749 |                                                                  564 |                                          |
| 1985-08-01 00:00:00.000 |                               12621 |                                 13 |                                                                 1023 |                                 95 |                                                          11516 |                            11394 |                               7 |                                                                  896 |                              95 |                                                               10410 |                           |                                                                605 |                                                                            10820 |                                                                  568 |                                          |
| 1985-09-01 00:00:00.000 |                               11860 |                                  5 |                                                                  963 |                                 96 |                                                          10806 |                            10825 |                               1 |                                                                  847 |                              96 |                                                                9883 |                           |                                                                569 |                                                                            10854 |                                                                   57 |                                          |
| 1985-10-01 00:00:00.000 |                               11981 |                                  0 |                                                                  883 |                                 93 |                                                          11005 |                            11071 |                               0 |                                                                  771 |                              93 |                                                               10207 |                           |                                                                575 |                                                                            10814 |                                                                  564 |                                          |


Table: SA_Population
| Year                    | Province      |   Population |
|:------------------------|:--------------|-------------:|
| 1996-01-01 00:00:00.000 | Gauteng       |      7348423 |
| 1996-01-01 00:00:00.000 | KwaZulu-Natal |      8417021 |
| 1996-01-01 00:00:00.000 | Eastern Cape  |      6302525 |
| 1996-01-01 00:00:00.000 | Western Cape  |      3956875 |
| 1996-01-01 00:00:00.000 | Limpopo       |      4929368 |
| 1996-01-01 00:00:00.000 | Mpumalanga    |      2800711 |
| 1996-01-01 00:00:00.000 | North West    |      3354825 |
| 1996-01-01 00:00:00.000 | Free State    |      2633504 |
| 1996-01-01 00:00:00.000 | Northern Cape |       840321 |
| 2001-01-01 00:00:00.000 | Gauteng       |      8837178 |


Table: Sheet1
|   years |   Revenue (R billion) | Operating profit (R billion)   | Total debt (R billion)   | Employee benefit expenses (R billion)   |   Number of employees | Electrical output capacity (GWh)   |
|--------:|----------------------:|:-------------------------------|:-------------------------|:----------------------------------------|----------------------:|:-----------------------------------|
|    2010 |                  71.1 | 2.3                            |                          |                                         |                36.547 | 232.812                            |
|    2011 |                  91.4 |                                | 178.5                    |                                         |                39.034 | 237.43                             |
|    2012 |                 114.7 | 13.2                           | 213.3                    | 20.1                                    |                44.913 | 224.785                            |
|    2013 |                 128.8 | 18.8                           | 236.7                    |                                         |                46.624 | 216.561                            |
|    2014 |                 138.3 | 16.5                           | 234.5                    | 25.6                                    |                46.919 |                                    |
|    2015 |                 147.7 | 3.6                            | 277.4                    | 25.9                                    |                46.49  | 226.3                              |
|    2016 |                 164.2 | 5.1                            | 306.9                    | 29.2                                    |                47.978 | 238.599                            |
|    2017 |                 177.1 | 0.888                          | 336.7                    | 33.1                                    |                47.658 | 220.166                            |
|    2018 |                 177.4 | -233                           | 348.1                    | 29.4                                    |                48.628 | 221.936                            |
|    2019 |                 179.8 | -207                           | 387.2                    | 33.3                                    |                46.665 | 218.939                            |


Table: Stations
|   id | station_name   |   locations_id_fk |
|-----:|:---------------|------------------:|
|    1 | Arnot          |                13 |
|    2 | Camden         |                 8 |
|    3 | Duvha          |                 7 |
|    4 | Grootvlei      |                 2 |
|    5 | Hendrina       |                13 |
|    6 | Kelvin         |                25 |
|    7 | Kendal         |                 7 |
|    8 | Komati         |                13 |
|    9 | Kriel          |                 4 |
|   10 | Kusile         |                17 |


Table: Stations_Dynamics
|   station_id |   status_id_fk |   operator_id_fk | planned_decommissioning_year   | nominal_capacity_MW   |   id |
|-------------:|---------------:|-----------------:|:-------------------------------|:----------------------|-----:|
|            1 |              1 |                1 | 2025-2029                      | 2232                  |    1 |
|            2 |              1 |                1 | 2020-2023                      | 1481                  |    2 |
|            3 |              1 |                1 | 2030-2034                      | 2875                  |    3 |
|            4 |              1 |                1 | 2025-2028                      | 570                   |    4 |
|            5 |              1 |                1 | 2021-2027                      | 1293                  |    5 |
|            6 |              1 |                3 | Unplanned                      | 214                   |    6 |
|            7 |              1 |                1 | 2038-2043                      | 3840                  |    7 |
|            8 |              1 |                1 | 2024-2028                      | NULL                  |    8 |
|            9 |              1 |                1 | 2026-2029                      | 2850                  |    9 |
|           10 |              2 |                1 | Unplanned                      | 720                   |   10 |


Table: Stations_Meta
|   station_id | coordinates                     |   comission_year_start |   commissioned_year_end |   installed_capacity |   station_type_id_fk |   id |
|-------------:|:--------------------------------|-----------------------:|------------------------:|---------------------:|---------------------:|-----:|
|            1 | 25Â°56â€²38â€³S 29Â°47â€²22â€³E |                   1971 |                    1975 |               2352   |                    1 |    1 |
|            2 | 26Â°37â€²13â€³S 30Â°05â€²38â€³E |                   1967 |                    1969 |               1536   |                    1 |    2 |
|            3 | 25Â°57â€²50â€³S 29Â°20â€²14â€³E |                   1980 |                    1984 |               3600   |                    1 |    3 |
|            4 | 26Â°46â€²S 28Â°30â€²E           |                   1969 |                    1977 |               1180   |                    1 |    4 |
|            5 | 26Â°01â€²59â€³S 29Â°36â€²00â€³E |                   1970 |                    1976 |               1893   |                    1 |    5 |
|            6 | 26Â°06â€²58â€³S 28Â°11â€²38â€³E |                   1957 |                    1957 |                214   |                    1 |    6 |
|            7 | 26Â°05â€²24â€³S 28Â°58â€²17â€³E |                   1988 |                    1922 |               4116   |                    1 |    7 |
|            8 | 26Â°05â€²24â€³S 29Â°28â€²19â€³E |                   1961 |                    1966 |                990   |                    1 |    8 |
|            9 | 26Â°15â€²15â€³S 29Â°10â€²46â€³E |                   1976 |                    1979 |                  3   |                    1 |    9 |
|           10 | 25Â°54â€²59â€³S 28Â°55â€²02â€³E |                   2017 |                    2021 |                 -4.8 |                    1 |   10 |


Table: Stations_Operator_Info
|   id | operator_name                |
|-----:|:-----------------------------|
|    1 | Eskom                        |
|    2 | City of Tshwane              |
|    3 | Aldwych International        |
|    4 | International power          |
|    5 | Kakamas Hydro Electric Power |
|    6 | Bethlehem Hydro              |
|    7 | IPSA Group                   |
|    8 | City of Cape Town            |


Table: Stations_Status_Info
|   id | status                |
|-----:|:----------------------|
|    1 | Operational           |
|    2 | Under construction    |
|    3 | 1st Unit Operational  |
|    4 | Decommissioned        |
|    5 | Awaiting construction |
|    6 | Project on hold       |


Table: Stations_Type_Info
|   id | station_type                    |
|-----:|:--------------------------------|
|    1 | Coal                            |
|    2 | Gas/liquid fuel turbine station |
|    3 | Nuclear                         |
|    4 | Pumped storage scheme           |
|    5 | Hydroelectric station           |
|    6 | Wind energy                     |


Table: Tweets
| name              | location               | date                    | text                                                                                                                                         | municipality   | hashtags   |   polarity score |
|:------------------|:-----------------------|:------------------------|:---------------------------------------------------------------------------------------------------------------------------------------------|:---------------|:-----------|-----------------:|
| Eskom Hld SOC Ltd | Gauteng, South African | 2020-09-16 10:41:19.000 | @the_concurrer Your query has been sent to Customer Services.                                                                                |                |            |                0 |
| Eskom Hld SOC Ltd | Gauteng, South African | 2020-09-16 10:27:30.000 | @DrLMahlare Hi, can you DM us your email address? We will ask Customer Services to send you the process.                                     |                |            |                0 |
| Eskom Hld SOC Ltd | Gauteng, South African | 2020-09-15 20:29:50.000 | @Prince00557024 Separate post                                                                                                                |                |            |                0 |
| Eskom Hld SOC Ltd | Gauteng, South African | 2020-09-14 08:28:45.000 | @GOTH_NERD Hi, your query has been escalated.                                                                                                |                |            |                0 |
| Eskom Hld SOC Ltd | Gauteng, South African | 2020-09-14 08:03:02.000 | Did you know that electricity tapped from high-voltage powerlines is not usable for household consumption? It can d… https://t.co/3hWRe6DNzL |                |            |                0 |
| Eskom Hld SOC Ltd | Gauteng, South African | 2020-09-13 10:00:07.000 | Only Eskom trained and authorized personnel are allowed to work on the power lines and other Eskom equipment. Commu… https://t.co/ukNNitMNQw |                |            |                0 |
| Eskom Hld SOC Ltd | Gauteng, South African | 2020-09-13 07:13:29.000 | @ViroCMN This is not correct.  Prepaid tarrif is based on usage. You can acquire more information here https://t.co/lRVHqFvrPY               |                |            |                0 |
| Eskom Hld SOC Ltd | Gauteng, South African | 2020-09-12 12:14:05.000 | @KingRodeo3 We have escalated to Customer Services.                                                                                          |                |            |                0 |
| Eskom Hld SOC Ltd | Gauteng, South African | 2020-09-11 17:55:59.000 | RT @PresidencyZA: The Political Task Team on Eskom, chaired by Deputy President @DDMabuza, has today, 11 September 2020, met to discuss pro… |                |            |                0 |
| Eskom Hld SOC Ltd | Gauteng, South African | 2020-09-11 14:33:03.000 | @littlemouse13 Hi, your query has been escalated to Customer Services.                                                                       |                |            |                0 |

 


tables: name: Coal_price
data: Coal_price.csv
name: data3
data: data3.csv
name: Debt
data: Debt.csv
name: Employees
data: Employees.csv
name: executive
data: executive.csv
name: Location
data: Location.csv
name: Province
data: Province.csv
name: Provincial_elec_distributed_GWh
data: Provincial_elec_distributed_GWh.csv
name: SA_National_electricity_data
data: SA_National_electricity_data.csv
name: SA_Population
data: SA_Population.csv
name: Sheet1
data: Sheet1.csv
name: Stations
data: Stations.csv
name: Stations_Dynamics
data: Stations_Dynamics.csv
name: Stations_Meta
data: Stations_Meta.csv
name: Stations_Operator_Info
data: Stations_Operator_Info.csv
name: Stations_Status_Info
data: Stations_Status_Info.csv
name: Stations_Type_Info
data: Stations_Type_Info.csv
name: Tweets
data: Tweets.csv
label: SA_Population
Year
SA_National_electricity_data
Date
Provincial_elec_distributed_GWh
Date
SA_National_electricity_data
Date
Coal_price
Date
SA_National_electricity_data
Date
Employees
Year
SA_National_electricity_data
Date
Stations_Dynamics
status_id_fk
Stations_Status_Info
id
Stations_Dynamics
operator_id_fk
Stations_Operator_Info
id
Stations
id
Stations_Dynamics
station_id
Stations
locations_id_fk
Location
id
Location
province_id_fk
Province
id
Stations_Meta
station_id
Stations
id
Stations_Meta
station_type_id_fk
Stations_Type_Info
id
data3
years
Debt
years
Sheet1
years
Debt
years
output: {
    "joins": [
        {
            "from_table": "SA_Population",
            "from_column": "Year",
            "to_table": "SA_National_electricity_data",
            "to_column": "Date"
        },
        {
            "from_table": "Provincial_elec_distributed_GWh",
            "from_column": "Date",
            "to_table": "SA_National_electricity_data",
            "to_column": "Date"
        },
        {
            "from_table": "Coal_price",
            "from_column": "Date",
            "to_table": "SA_National_electricity_data",
            "to_column": "Date"
        },
        {
            "from_table": "Employees",
            "from_column": "Year",
            "to_table": "SA_National_electricity_data",
            "to_column": "Date"
        },
        {
            "from_table": "Stations_Dynamics",
            "from_column": "status_id_fk",
            "to_table": "Stations_Status_Info",
            "to_column": "id"
        },
        {
            "from_table": "Stations_Dynamics",
            "from_column": "operator_id_fk",
            "to_table": "Stations_Operator_Info",
            "to_column": "id"
        },
        {
            "from_table": "Stations",
            "from_column": "id",
            "to_table": "Stations_Dynamics",
            "to_column": "station_id"
        },
        {
            "from_table": "Stations",
            "from_column": "locations_id_fk",
            "to_table": "Location",
            "to_column": "id"
        },
        {
            "from_table": "Location",
            "from_column": "province_id_fk",
            "to_table": "Province",
            "to_column": "id"
        },
        {
            "from_table": "Stations_Meta",
            "from_column": "station_id",
            "to_table": "Stations",
            "to_column": "id"
        },
        {
            "from_table": "Stations_Meta",
            "from_column": "station_type_id_fk",
            "to_table": "Stations_Type_Info",
            "to_column": "id"
        },
        {
            "from_table": "data3",
            "from_column": "years",
            "to_table": "Debt",
            "to_column": "years"
        },
        {
            "from_table": "Sheet1",
            "from_column": "years",
            "to_table": "Debt",
            "to_column": "years"
        }
    ]
}