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: Brand
|   Brand_ID | Brand   |
|-----------:|:--------|
|          1 | Brand 1 |
|          2 | Brand 2 |
|          3 | Brand 3 |
|          4 | Brand 4 |
|          6 | Brand 6 |
|          7 | Brand 7 |
|          8 | Brand 8 |


Table: Channel
|   Channel_ID | ChannelName   |
|-------------:|:--------------|
|            1 | Channel 1     |
|            2 | Channel 2     |
|            3 | Channel 3     |
|            4 | Channel 4     |
|            5 | Channel 5     |
|            6 | Channel 6     |
|            7 | Channel 7     |
|            8 | Channel 8     |
|            9 | Channel 9     |


Table: Customer
|   Customer_ID | CustomerNumber   | CustomerName   |   ChannelID |   GradeID |   OwnedByRepresentativeID | CreatedDate             | CreatedBy   | ModifiedDate   | ModifiedBy   |
|--------------:|:-----------------|:---------------|------------:|----------:|--------------------------:|:------------------------|:------------|:---------------|:-------------|
|             1 | A1               | Customer 1     |           4 |         2 |                         1 | 2020-08-05 00:00:00.000 | System      |                | NULL         |
|             2 | A2               | Customer 2     |           4 |         2 |                         1 | 2020-08-05 00:00:00.000 | System      |                | NULL         |
|            13 | A3               | Customer 3     |           1 |         3 |                         1 | 2020-08-05 00:00:00.000 | System      |                | NULL         |
|          7346 | H1               | Customer 4     |           2 |         3 |                         8 | 2020-08-05 00:00:00.000 | System      |                | NULL         |
|          7347 | H2               | Customer 5     |           4 |         4 |                         8 | 2020-08-05 00:00:00.000 | System      |                | NULL         |
|          7348 | H3               | Customer 6     |           2 |         4 |                         8 | 2020-08-05 00:00:00.000 | System      |                | NULL         |
|          7442 | Y1               | Customer 7     |           4 |         1 |                        10 | 2020-08-05 00:00:00.000 | System      |                | NULL         |
|          7443 | Z1               | Customer 8     |           3 |         1 |                         9 | 2020-08-05 00:00:00.000 | System      |                | NULL         |
|          7444 | Z2               | Customer 9     |           7 |         5 |                         9 | 2020-08-05 00:00:00.000 | System      |                | NULL         |
|          7445 | H1               | Customer 10    |           4 |         5 |                         8 | 2020-08-05 00:00:00.000 | System      |                | NULL         |


Table: Dates
| Date                    |   Year |   QuarterOfYear |   MonthOfYear |   DayOfMonth |   DateInt | Month Name   | Month & Year   | Quarter & Year   |   DayOfWeek | DayOfWeekName   | WeekEnding              | MonthEnding             |   ISO Weeknumber |   ISO Year |   ISO Day of Year | Week & Year   |   WeeknYear |   MonthnYear |   QuarternYear | Fiscal Year   | Fiscal Quarter   |   Fiscal Period | IsAfterToday   | IsWorkingDay   | IsHoliday   |   WeekOffset |   MonthOffset |   QuarterOffset |   YearOffset |   FiscalYearOffset | WeekCompleted   | MonthCompleted   | QuarterCompleted   |
|:------------------------|-------:|----------------:|--------------:|-------------:|----------:|:-------------|:---------------|:-----------------|------------:|:----------------|:------------------------|:------------------------|-----------------:|-----------:|------------------:|:--------------|------------:|-------------:|---------------:|:--------------|:-----------------|----------------:|:---------------|:---------------|:------------|-------------:|--------------:|----------------:|-------------:|-------------------:|:----------------|:-----------------|:-------------------|
| 2020-01-01 00:00:00.000 |   2020 |               1 |             1 |            1 |  20200101 | January      | Jan 2020       | Q1 2020          |           2 | Wednesday       | 2020-01-05 00:00:00.000 | 2020-01-31 00:00:00.000 |                1 |       2020 |                 3 | 2020-01       |    20200100 |     20200100 |       20200100 | FY20          | FQ3              |               7 | False          | True           | Unknown     |          -35 |            -8 |              -2 |            0 |                 -1 | True            | True             | True               |
| 2020-01-02 00:00:00.000 |   2020 |               1 |             1 |            2 |  20200102 | January      | Jan 2020       | Q1 2020          |           3 | Thursday        | 2020-01-05 00:00:00.000 | 2020-01-31 00:00:00.000 |                1 |       2020 |                 4 | 2020-01       |    20200100 |     20200100 |       20200100 | FY20          | FQ3              |               7 | False          | True           | Unknown     |          -35 |            -8 |              -2 |            0 |                 -1 | True            | True             | True               |
| 2020-01-03 00:00:00.000 |   2020 |               1 |             1 |            3 |  20200103 | January      | Jan 2020       | Q1 2020          |           4 | Friday          | 2020-01-05 00:00:00.000 | 2020-01-31 00:00:00.000 |                1 |       2020 |                 5 | 2020-01       |    20200100 |     20200100 |       20200100 | FY20          | FQ3              |               7 | False          | True           | Unknown     |          -35 |            -8 |              -2 |            0 |                 -1 | True            | True             | True               |
| 2020-01-06 00:00:00.000 |   2020 |               1 |             1 |            6 |  20200106 | January      | Jan 2020       | Q1 2020          |           0 | Monday          | 2020-01-12 00:00:00.000 | 2020-01-31 00:00:00.000 |                2 |       2020 |                 8 | 2020-02       |    20200200 |     20200100 |       20200100 | FY20          | FQ3              |               7 | False          | True           | Unknown     |          -34 |            -8 |              -2 |            0 |                 -1 | True            | True             | True               |
| 2020-01-07 00:00:00.000 |   2020 |               1 |             1 |            7 |  20200107 | January      | Jan 2020       | Q1 2020          |           1 | Tuesday         | 2020-01-12 00:00:00.000 | 2020-01-31 00:00:00.000 |                2 |       2020 |                 9 | 2020-02       |    20200200 |     20200100 |       20200100 | FY20          | FQ3              |               7 | False          | True           | Unknown     |          -34 |            -8 |              -2 |            0 |                 -1 | True            | True             | True               |
| 2020-01-08 00:00:00.000 |   2020 |               1 |             1 |            8 |  20200108 | January      | Jan 2020       | Q1 2020          |           2 | Wednesday       | 2020-01-12 00:00:00.000 | 2020-01-31 00:00:00.000 |                2 |       2020 |                10 | 2020-02       |    20200200 |     20200100 |       20200100 | FY20          | FQ3              |               7 | False          | True           | Unknown     |          -34 |            -8 |              -2 |            0 |                 -1 | True            | True             | True               |
| 2020-01-09 00:00:00.000 |   2020 |               1 |             1 |            9 |  20200109 | January      | Jan 2020       | Q1 2020          |           3 | Thursday        | 2020-01-12 00:00:00.000 | 2020-01-31 00:00:00.000 |                2 |       2020 |                11 | 2020-02       |    20200200 |     20200100 |       20200100 | FY20          | FQ3              |               7 | False          | True           | Unknown     |          -34 |            -8 |              -2 |            0 |                 -1 | True            | True             | True               |
| 2020-01-10 00:00:00.000 |   2020 |               1 |             1 |           10 |  20200110 | January      | Jan 2020       | Q1 2020          |           4 | Friday          | 2020-01-12 00:00:00.000 | 2020-01-31 00:00:00.000 |                2 |       2020 |                12 | 2020-02       |    20200200 |     20200100 |       20200100 | FY20          | FQ3              |               7 | False          | True           | Unknown     |          -34 |            -8 |              -2 |            0 |                 -1 | True            | True             | True               |
| 2020-01-13 00:00:00.000 |   2020 |               1 |             1 |           13 |  20200113 | January      | Jan 2020       | Q1 2020          |           0 | Monday          | 2020-01-19 00:00:00.000 | 2020-01-31 00:00:00.000 |                3 |       2020 |                15 | 2020-03       |    20200300 |     20200100 |       20200100 | FY20          | FQ3              |               7 | False          | True           | Unknown     |          -33 |            -8 |              -2 |            0 |                 -1 | True            | True             | True               |
| 2020-01-14 00:00:00.000 |   2020 |               1 |             1 |           14 |  20200114 | January      | Jan 2020       | Q1 2020          |           1 | Tuesday         | 2020-01-19 00:00:00.000 | 2020-01-31 00:00:00.000 |                3 |       2020 |                16 | 2020-03       |    20200300 |     20200100 |       20200100 | FY20          | FQ3              |               7 | False          | True           | Unknown     |          -33 |            -8 |              -2 |            0 |                 -1 | True            | True             | True               |


Table: Distributor
|   Distributor_ID | DistributorName   |
|-----------------:|:------------------|
|                1 | Distributor 1     |
|                2 | Distributor 2     |
|                3 | Distributor 3     |
|                4 | Distributor 4     |
|                5 | Distributor 5     |
|               27 | Distributor 27    |


Table: Grade
|   Grade_ID | Grade   |
|-----------:|:--------|
|          1 | A       |
|          2 | B       |
|          3 | C       |
|          4 | D       |
|          5 | E       |


Table: Product
|   Item_ID |   BrandID | ItemDescription   |   UnitPrice |
|----------:|----------:|:------------------|------------:|
|         1 |         1 | Product 1         |          20 |
|         2 |         1 | Product 2         |          10 |
|         3 |         1 | Product 3         |          10 |
|         4 |         1 | Product 4         |          10 |
|         5 |         1 | Product 5         |          10 |
|         6 |         1 | Product 6         |          10 |
|         7 |         1 | Product 7         |          10 |
|         8 |         1 | Product 8         |          10 |
|         9 |         1 | Product 9         |          10 |
|        10 |         1 | Product 10        |          10 |


Table: Rep
|   Representative_ID | FirstName   | LastName   | Full Name   |
|--------------------:|:------------|:-----------|:------------|
|                   1 | Rep         | One        | Rep One     |
|                   8 | Rep         | Eight      | Rep Eight   |
|                   9 | Rep         | Nine       | Rep Nine    |
|                  10 | Rep         | Ten        | Rep Ten     |


Table: Sales
|   OrderHeader_ID |   RepresentativeID |   CustomerID |   ChannelID |   Grade_ID |   DistributorID | OrderDate               | RequiredDate            | CreatedDate             | CreatedBy   | ModifiedDate            | ModifiedBy   |   OrderLine_ID |   ItemID |   Brand_ID |   OrderQuantity | BonusQuantity   |
|-----------------:|-------------------:|-------------:|------------:|-----------:|----------------:|:------------------------|:------------------------|:------------------------|:------------|:------------------------|:-------------|---------------:|---------:|-----------:|----------------:|:----------------|
|                1 |                 10 |         7442 |           4 |          1 |               1 | 2020-08-07 00:00:00.000 | 2020-08-11 00:00:00.000 | 2020-08-07 11:49:50.280 | System      |                         | System       |              1 |        1 |          1 |               4 |                 |
|                1 |                 10 |         7442 |           4 |          1 |               1 | 2020-08-07 00:00:00.000 | 2020-08-11 00:00:00.000 | 2020-08-07 11:49:50.280 | System      |                         | System       |              2 |       39 |          3 |               1 |                 |
|                1 |                 10 |         7442 |           4 |          1 |               1 | 2020-08-07 00:00:00.000 | 2020-08-11 00:00:00.000 | 2020-08-07 11:49:50.280 | System      |                         | System       |              3 |       50 |          7 |               2 |                 |
|                2 |                 10 |         7442 |           4 |          1 |               2 | 2020-08-07 00:00:00.000 | 2020-08-14 00:00:00.000 | 2020-08-07 11:52:34.920 | System      |                         | System       |              4 |        1 |          1 |               4 | 1               |
|                2 |                 10 |         7442 |           4 |          1 |               2 | 2020-08-07 00:00:00.000 | 2020-08-14 00:00:00.000 | 2020-08-07 11:52:34.920 | System      |                         | System       |              5 |        2 |          1 |               2 |                 |
|                3 |                 10 |         7442 |           4 |          1 |               2 | 2020-08-07 00:00:00.000 | 2020-08-19 00:00:00.000 | 2020-08-07 11:53:37.873 | System      |                         | System       |              6 |        1 |          1 |               4 |                 |
|                4 |                 10 |         7442 |           4 |          1 |               1 | 2020-08-07 00:00:00.000 | 2020-08-11 00:00:00.000 | 2020-08-07 11:54:24.553 | System      |                         | System       |              7 |        1 |          1 |               4 | 1               |
|                6 |                 10 |         7442 |           4 |          1 |               3 | 2020-08-07 00:00:00.000 | 2020-08-19 00:00:00.000 | 2020-08-07 11:55:35.566 | System      |                         | System       |              8 |        1 |          1 |               1 |                 |
|                7 |                  9 |         7443 |           3 |          1 |               4 | 2020-08-10 00:00:00.000 | 2020-08-12 00:00:00.000 | 2020-08-10 15:15:30.126 | System      | 2020-08-11 10:10:59.210 | System       |              9 |        1 |          1 |               1 | 1               |
|                7 |                  9 |         7443 |           3 |          1 |               4 | 2020-08-10 00:00:00.000 | 2020-08-12 00:00:00.000 | 2020-08-10 15:15:30.126 | System      | 2020-08-11 10:10:59.210 | System       |             11 |       14 |          1 |               1 |                 |


Table: Visits
|   CustomerVisit_ID |   CustomerID |   ChannelID |   Grade_ID |   RepresentativeID | VisitDate               | PreviousVisitID   | PreviousVisitDate   | CreatedDate             | CreatedBy   | ModifiedDate            | ModifiedBy   |   ItemID |   Brand_ID |   Quantity | SampleQty   |
|-------------------:|-------------:|------------:|-----------:|-------------------:|:------------------------|:------------------|:--------------------|:------------------------|:------------|:------------------------|:-------------|---------:|-----------:|-----------:|:------------|
|                  1 |            1 |           4 |          2 |                 10 | 2020-08-07 00:00:00.000 |                   |                     | 2020-08-07 12:01:24.866 | System      | 2020-08-24 00:00:00.000 | System       |        2 |          1 |          1 |             |
|                  1 |            1 |           4 |          2 |                 10 | 2020-08-07 00:00:00.000 |                   |                     | 2020-08-07 12:01:24.866 | System      | 2020-08-24 00:00:00.000 | System       |        3 |          1 |          1 |             |
|                  1 |            1 |           4 |          2 |                 10 | 2020-08-07 00:00:00.000 |                   |                     | 2020-08-07 12:01:24.866 | System      | 2020-08-24 00:00:00.000 | System       |        4 |          1 |          1 |             |
|                  1 |            1 |           4 |          2 |                 10 | 2020-08-07 00:00:00.000 |                   |                     | 2020-08-07 12:01:24.866 | System      | 2020-08-24 00:00:00.000 | System       |        5 |          1 |          1 |             |
|                  2 |         7442 |           4 |          1 |                 10 | 2020-08-07 00:00:00.000 |                   |                     | 2020-08-07 12:04:28.516 | System      |                         | System       |        1 |          1 |          1 |             |
|                  2 |         7442 |           4 |          1 |                 10 | 2020-08-07 00:00:00.000 |                   |                     | 2020-08-07 12:04:28.516 | System      |                         | System       |        2 |          1 |          1 |             |
|                  4 |         7443 |           3 |          1 |                  9 | 2020-08-10 00:00:00.000 |                   |                     | 2020-08-10 15:18:06.693 | System      |                         | System       |        1 |          1 |          1 |             |
|                  4 |         7443 |           3 |          1 |                  9 | 2020-08-10 00:00:00.000 |                   |                     | 2020-08-10 15:18:06.693 | System      |                         | System       |        4 |          1 |          1 |             |
|                  4 |         7443 |           3 |          1 |                  9 | 2020-08-10 00:00:00.000 |                   |                     | 2020-08-10 15:18:06.693 | System      |                         | System       |       10 |          1 |          1 |             |
|                  4 |         7443 |           3 |          1 |                  9 | 2020-08-10 00:00:00.000 |                   |                     | 2020-08-10 15:18:06.693 | System      |                         | System       |       20 |          1 |          1 |             |

 


tables: name: Brand
data: Brand.csv
name: Channel
data: Channel.csv
name: Customer
data: Customer.csv
name: Dates
data: Dates.csv
name: Distributor
data: Distributor.csv
name: Grade
data: Grade.csv
name: Product
data: Product.csv
name: Rep
data: Rep.csv
name: Sales
data: Sales.csv
name: Visits
data: Visits.csv
label: Sales
ItemID
Product
Item_ID
Visits
ItemID
Product
Item_ID
Sales
Brand_ID
Brand
Brand_ID
Visits
Brand_ID
Brand
Brand_ID
Sales
RepresentativeID
Rep
Representative_ID
Visits
RepresentativeID
Rep
Representative_ID
Sales
DistributorID
Distributor
Distributor_ID
Sales
Grade_ID
Grade
Grade_ID
Visits
Grade_ID
Grade
Grade_ID
Sales
CustomerID
Customer
Customer_ID
Visits
CustomerID
Customer
Customer_ID
Sales
OrderDate
Dates
Date
Visits
VisitDate
Dates
Date
Sales
ChannelID
Channel
Channel_ID
Visits
ChannelID
Channel
Channel_ID
output: {
    "joins": [
        {
            "from_table": "Sales",
            "from_column": "ItemID",
            "to_table": "Product",
            "to_column": "Item_ID"
        },
        {
            "from_table": "Visits",
            "from_column": "ItemID",
            "to_table": "Product",
            "to_column": "Item_ID"
        },
        {
            "from_table": "Sales",
            "from_column": "Brand_ID",
            "to_table": "Brand",
            "to_column": "Brand_ID"
        },
        {
            "from_table": "Visits",
            "from_column": "Brand_ID",
            "to_table": "Brand",
            "to_column": "Brand_ID"
        },
        {
            "from_table": "Sales",
            "from_column": "RepresentativeID",
            "to_table": "Rep",
            "to_column": "Representative_ID"
        },
        {
            "from_table": "Visits",
            "from_column": "RepresentativeID",
            "to_table": "Rep",
            "to_column": "Representative_ID"
        },
        {
            "from_table": "Sales",
            "from_column": "DistributorID",
            "to_table": "Distributor",
            "to_column": "Distributor_ID"
        },
        {
            "from_table": "Sales",
            "from_column": "Grade_ID",
            "to_table": "Grade",
            "to_column": "Grade_ID"
        },
        {
            "from_table": "Visits",
            "from_column": "Grade_ID",
            "to_table": "Grade",
            "to_column": "Grade_ID"
        },
        {
            "from_table": "Sales",
            "from_column": "CustomerID",
            "to_table": "Customer",
            "to_column": "Customer_ID"
        },
        {
            "from_table": "Visits",
            "from_column": "CustomerID",
            "to_table": "Customer",
            "to_column": "Customer_ID"
        },
        {
            "from_table": "Sales",
            "from_column": "OrderDate",
            "to_table": "Dates",
            "to_column": "Date"
        },
        {
            "from_table": "Visits",
            "from_column": "VisitDate",
            "to_table": "Dates",
            "to_column": "Date"
        },
        {
            "from_table": "Sales",
            "from_column": "ChannelID",
            "to_table": "Channel",
            "to_column": "Channel_ID"
        },
        {
            "from_table": "Visits",
            "from_column": "ChannelID",
            "to_table": "Channel",
            "to_column": "Channel_ID"
        }
    ]
}