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: benfordHalifax
|   Digit |   Actual Count |   Actual % |   Expected % |
|--------:|---------------:|-----------:|-------------:|
|       1 |             15 |  0.294118  |        0.301 |
|       2 |              8 |  0.156863  |        0.176 |
|       3 |              3 |  0.0588235 |        0.125 |
|       4 |              3 |  0.0588235 |        0.096 |
|       5 |              6 |  0.117647  |        0.079 |
|       6 |              6 |  0.117647  |        0.067 |
|       7 |              5 |  0.0980392 |        0.058 |
|       8 |              1 |  0.0196078 |        0.051 |
|       9 |              4 |  0.0784314 |        0.046 |


Table: benfordOttawa
|   Digit |   Actual Count |   Actual % |   Expected % |
|--------:|---------------:|-----------:|-------------:|
|       1 |            186 |  0.280543  |        0.301 |
|       2 |            125 |  0.188537  |        0.176 |
|       3 |             68 |  0.102564  |        0.125 |
|       4 |             73 |  0.110106  |        0.096 |
|       5 |             52 |  0.0784314 |        0.079 |
|       6 |             56 |  0.0844646 |        0.067 |
|       7 |             38 |  0.0573152 |        0.058 |
|       8 |             36 |  0.0542986 |        0.051 |
|       9 |             29 |  0.0437406 |        0.046 |


Table: BUDGET
| Date                    | City       | Budgeted Profit   |   Budgeted Profit - imputed |
|:------------------------|:-----------|:------------------|----------------------------:|
| 2017-01-27 00:00:00.000 | Abbotsford | 46995.8195        |                     46995.8 |
| 2017-02-17 00:00:00.000 | Abbotsford | 238656.88125      |                    238657   |
| 2017-03-16 00:00:00.000 | Abbotsford |                   |                    459546   |
| 2017-04-30 00:00:00.000 | Abbotsford | 184279.6774       |                    184280   |
| 2017-05-28 00:00:00.000 | Abbotsford | 191602.4949       |                    191602   |
| 2017-06-24 00:00:00.000 | Abbotsford | 292012.861        |                    292013   |
| 2017-07-30 00:00:00.000 | Abbotsford | 844816.621        |                    844817   |
| 2017-08-25 00:00:00.000 | Abbotsford | 84751.25516       |                     84751.3 |
| 2017-09-29 00:00:00.000 | Abbotsford | 986375.29729      |                    986375   |
| 2017-10-21 00:00:00.000 | Abbotsford | 172657.6          |                    172658   |


Table: CITIES
| City       | Province         |   Population |
|:-----------|:-----------------|-------------:|
| Abbotsford | British Columbia |       151683 |
| Calgary    | Alberta          |      1110000 |
| Edmonton   | Alberta          |      1058000 |
| Halifax    | Nova Scotia      |       359111 |
| Hamilton   | Ontario          |       721053 |
| Kitchener  | Ontario          |       417001 |
| Montreal   | Quebec           |      3678000 |
| Oshawa     | Ontario          |       450963 |
| Ottawa     | Ontario          |      1145000 |
| Regina     | Saskatchewan     |       176183 |


Table: Halifax
| Order ID       | Order Date              | City    | Product Name                      |   Profit |   LeadingDigit |
|:---------------|:------------------------|:--------|:----------------------------------|---------:|---------------:|
| US-2017-167920 | 2017-12-09 00:00:00.000 | Halifax | Salmon Jerky Dog Treats - 6 pack  |    605.6 |              6 |
| CA-2017-123687 | 2017-05-26 00:00:00.000 | Halifax | Salmon Fillet                     |    772.8 |              7 |
| CA-2017-146535 | 2017-11-24 00:00:00.000 | Halifax | Smoked Pink 2.5 oz                |   1411.2 |              1 |
| CA-2017-143567 | 2017-11-02 00:00:00.000 | Halifax | Smoked Salmon Strips 8 count      |   2606.8 |              2 |
| US-2017-167920 | 2017-12-09 00:00:00.000 | Halifax | Salmon Jerky Dog Treats - 10 pack |   2649.6 |              2 |
| CA-2017-154816 | 2017-11-06 00:00:00.000 | Halifax | Salmon Jerky 24 bags              |   2832.2 |              2 |
| CA-2017-141446 | 2017-09-16 00:00:00.000 | Halifax | Fancy Pink Salmon 10 oz           |   3240   |              3 |
| CA-2017-155292 | 2017-10-30 00:00:00.000 | Halifax | Atlantic Filets 5 oz 14 portions  |   4239.2 |              4 |
| US-2017-158526 | 2017-12-29 00:00:00.000 | Halifax | Atlantic Filets 5 oz 12 portions  |   5889.1 |              5 |
| CA-2017-117695 | 2017-08-05 00:00:00.000 | Halifax | Smoked Salmon Strips 20 count     |   6329.6 |              6 |


Table: KPI Target Profit Variance
|   KPI Target Profit Variance |
|-----------------------------:|
|                        -0.5  |
|                        -0.45 |
|                        -0.4  |
|                        -0.35 |
|                        -0.3  |
|                        -0.25 |
|                        -0.2  |
|                        -0.15 |
|                        -0.1  |
|                        -0.05 |


Table: ORDERS
| Order ID       | Order Date              | City   | Product Name                                      |   Profit |
|:---------------|:------------------------|:-------|:--------------------------------------------------|---------:|
| CA-2017-106824 | 2017-07-07 00:00:00.000 | Ottawa | Salmon Jerky Dog Treats - 6 pack                  |    118.8 |
| US-2017-163790 | 2017-11-02 00:00:00.000 | Ottawa | Fancy Pink Salmon 12 oz                           |    167.4 |
| CA-2017-105487 | 2017-10-08 00:00:00.000 | Ottawa | Wild Alaskan Salmon in water -Reduced sodium 8 oz |    406.8 |
| CA-2017-126536 | 2017-10-12 00:00:00.000 | Ottawa | Smoked Pink 2.5 oz                                |    435.6 |
| CA-2017-129378 | 2017-10-01 00:00:00.000 | Ottawa | Salmon Jerky Dog Treats - 10 pack                 |    504   |
| CA-2017-117261 | 2017-09-04 00:00:00.000 | Ottawa | Salmon Jerky Dog Treats - 3 pack                  |    543.2 |
| US-2017-123862 | 2017-01-07 00:00:00.000 | Ottawa | Salmon Jerky Dog Treats - 6 pack                  |    610.2 |
| CA-2017-166933 | 2017-04-24 00:00:00.000 | Ottawa | Smoked Salmon Trio in Wooden box 10 oz            |    651.6 |
| CA-2017-130106 | 2017-05-22 00:00:00.000 | Ottawa | Lemon Pepper Salmon 5 oz                          |    722.8 |
| US-2017-116652 | 2017-09-15 00:00:00.000 | Ottawa | Salmon Fillet                                     |    840   |


Table: Ottawa
| Order ID       | Order Date              | City   | Product Name                                      |   Profit |   LeadingDigit |
|:---------------|:------------------------|:-------|:--------------------------------------------------|---------:|---------------:|
| CA-2017-106824 | 2017-07-07 00:00:00.000 | Ottawa | Salmon Jerky Dog Treats - 6 pack                  |    118.8 |              1 |
| US-2017-163790 | 2017-11-02 00:00:00.000 | Ottawa | Fancy Pink Salmon 12 oz                           |    167.4 |              1 |
| CA-2017-105487 | 2017-10-08 00:00:00.000 | Ottawa | Wild Alaskan Salmon in water -Reduced sodium 8 oz |    406.8 |              4 |
| CA-2017-126536 | 2017-10-12 00:00:00.000 | Ottawa | Smoked Pink 2.5 oz                                |    435.6 |              4 |
| CA-2017-129378 | 2017-10-01 00:00:00.000 | Ottawa | Salmon Jerky Dog Treats - 10 pack                 |    504   |              5 |
| CA-2017-117261 | 2017-09-04 00:00:00.000 | Ottawa | Salmon Jerky Dog Treats - 3 pack                  |    543.2 |              5 |
| US-2017-123862 | 2017-01-07 00:00:00.000 | Ottawa | Salmon Jerky Dog Treats - 6 pack                  |    610.2 |              6 |
| CA-2017-166933 | 2017-04-24 00:00:00.000 | Ottawa | Smoked Salmon Trio in Wooden box 10 oz            |    651.6 |              6 |
| CA-2017-130106 | 2017-05-22 00:00:00.000 | Ottawa | Lemon Pepper Salmon 5 oz                          |    722.8 |              7 |
| US-2017-116652 | 2017-09-15 00:00:00.000 | Ottawa | Salmon Fillet                                     |    840   |              8 |

 


tables: name: benfordHalifax
data: benfordHalifax.csv
name: benfordOttawa
data: benfordOttawa.csv
name: BUDGET
data: BUDGET.csv
name: CITIES
data: CITIES.csv
name: Halifax
data: Halifax.csv
name: KPI Target Profit Variance
data: KPI Target Profit Variance.csv
name: ORDERS
data: ORDERS.csv
name: Ottawa
data: Ottawa.csv
label: BUDGET
City
CITIES
City
ORDERS
City
CITIES
City
Ottawa
City
CITIES
City
Ottawa
LeadingDigit
benfordOttawa
Digit
Halifax
City
CITIES
City
Halifax
LeadingDigit
benfordHalifax
Digit
output: {
    "joins": [
        {
            "from_table": "BUDGET",
            "from_column": "City",
            "to_table": "CITIES",
            "to_column": "City"
        },
        {
            "from_table": "ORDERS",
            "from_column": "City",
            "to_table": "CITIES",
            "to_column": "City"
        },
        {
            "from_table": "Ottawa",
            "from_column": "City",
            "to_table": "CITIES",
            "to_column": "City"
        },
        {
            "from_table": "Ottawa",
            "from_column": "LeadingDigit",
            "to_table": "benfordOttawa",
            "to_column": "Digit"
        },
        {
            "from_table": "Halifax",
            "from_column": "City",
            "to_table": "CITIES",
            "to_column": "City"
        },
        {
            "from_table": "Halifax",
            "from_column": "LeadingDigit",
            "to_table": "benfordHalifax",
            "to_column": "Digit"
        }
    ]
}