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: Calendar
| Date                    | Year-Week   | Year-Month   | Month   | Mth.   |   Month Number |   Day offset |   Week of Year |   Year |   Week offset |   Month offset |   Year offset |
|:------------------------|:------------|:-------------|:--------|:-------|---------------:|-------------:|---------------:|-------:|--------------:|---------------:|--------------:|
| 2016-01-01 00:00:00.000 | 2015-53     | 2016-01      | januar  | jan    |              1 |        -2377 |              1 |   2016 |          -340 |            -78 |            -6 |
| 2016-01-02 00:00:00.000 | 2015-53     | 2016-01      | januar  | jan    |              1 |        -2376 |              1 |   2016 |          -340 |            -78 |            -6 |
| 2016-01-03 00:00:00.000 | 2015-53     | 2016-01      | januar  | jan    |              1 |        -2375 |              1 |   2016 |          -339 |            -78 |            -6 |
| 2016-01-04 00:00:00.000 | 2016-01     | 2016-01      | januar  | jan    |              1 |        -2374 |              2 |   2016 |          -339 |            -78 |            -6 |
| 2016-01-05 00:00:00.000 | 2016-01     | 2016-01      | januar  | jan    |              1 |        -2373 |              2 |   2016 |          -339 |            -78 |            -6 |
| 2016-01-06 00:00:00.000 | 2016-01     | 2016-01      | januar  | jan    |              1 |        -2372 |              2 |   2016 |          -339 |            -78 |            -6 |
| 2016-01-07 00:00:00.000 | 2016-01     | 2016-01      | januar  | jan    |              1 |        -2371 |              2 |   2016 |          -339 |            -78 |            -6 |
| 2016-01-08 00:00:00.000 | 2016-01     | 2016-01      | januar  | jan    |              1 |        -2370 |              2 |   2016 |          -339 |            -78 |            -6 |
| 2016-01-09 00:00:00.000 | 2016-01     | 2016-01      | januar  | jan    |              1 |        -2369 |              2 |   2016 |          -339 |            -78 |            -6 |
| 2016-01-10 00:00:00.000 | 2016-01     | 2016-01      | januar  | jan    |              1 |        -2368 |              2 |   2016 |          -338 |            -78 |            -6 |


Table: Customer
|   No. | Name                       | City      | County   | Customer                         |
|------:|:---------------------------|:----------|:---------|:---------------------------------|
| 10000 | Kontorcentralen A/S        | Nyborg    |          | 10000 Kontorcentralen A/S        |
| 20000 | Ravel Møbler               | Atlanta   |          | 20000 Ravel Møbler               |
| 30000 | Lauritzen Kontormøbler A/S | Koge      |          | 30000 Lauritzen Kontormøbler A/S |
| 40000 | Litware, Inc.              | Hilliard  |          | 40000 Litware, Inc.              |
| 50000 | Relecloud                  | Guildford |          | 50000 Relecloud                  |


Table: Item
| No.    | No. 2   | Description              | Vendor no.   | Item category code   | Item                            | Parent Code   | Item category description   | Inventory value   |   Rank of most stock value | Turnover rate last 90 days   | Turnover rate last 90 days (bins)   |
|:-------|:--------|:-------------------------|:-------------|:---------------------|:--------------------------------|:--------------|:----------------------------|:------------------|---------------------------:|:-----------------------------|:------------------------------------|
| 1896-S |         | ATHEN Skrivebord         | 30000        | SORTBORD             | 1896-S ATHEN Skrivebord         | MØBLER        | Forskellige borde           | 17348             |                          5 |                              |                                     |
| 1906-S |         | ATHEN Skuffemodul        | 30000        | SORTBORD             | 1906-S ATHEN Skuffemodul        | MØBLER        | Forskellige borde           | 9395              |                          6 |                              |                                     |
| 1920-S |         | ANTWERPEN Konferencebord | 20000        | SORTBORD             | 1920-S ANTWERPEN Konferencebord | MØBLER        | Forskellige borde           | 28080             |                          4 |                              |                                     |
| 1900-S |         | PARIS Gæstestol, sort    | 20000        | STOL                 | 1900-S PARIS Gæstestol, sort    | MØBLER        | Kontorstol                  | 0                 |                         12 |                              |                                     |
| 1908-S |         | LONDON Drejestol, blå    | 30000        | STOL                 | 1908-S LONDON Drejestol, blå    | MØBLER        | Kontorstol                  | 2469              |                          9 |                              |                                     |
| 1928-S |         | AMSTERDAM Lampe          | 10000        | DIV                  | 1928-S AMSTERDAM Lampe          |               | Diverse                     | 1904              |                         10 |                              |                                     |
| 1925-W |         | Konferencebundt 1-6      |              |                      | 1925-W Konferencebundt 1-6      |               |                             |                   |                         12 |                              |                                     |
| 1929-W |         | Konferencebundt 1-8      |              |                      | 1929-W Konferencebundt 1-8      |               |                             |                   |                         12 |                              |                                     |
| 1936-S |         | BERLIN Gæstestol, gul    | 20000        | STOL                 | 1936-S BERLIN Gæstestol, gul    | MØBLER        | Kontorstol                  | 83500             |                          1 |                              |                                     |
| 1953-W |         | Gæstesektion 1           |              |                      | 1953-W Gæstesektion 1           |               |                             | 0                 |                         12 |                              |                                     |


Table: Purchase Line
|   buy-from vendor no. |   Document no. |   Line no. | No.    | Expected receipt date   | Description               |   Quantity |   Outstanding quantity | Amount   | Outstanding amount   | Outstanding amount (lcy)   | Line amount   |   Quantity (base) | Purchasing code   | Order date   |
|----------------------:|---------------:|-----------:|:-------|:------------------------|:--------------------------|-----------:|-----------------------:|:---------|:---------------------|:---------------------------|:--------------|------------------:|:------------------|:-------------|
|                 20000 |         106002 |      10000 | 1964-S | 2020-04-10 00:00:00.000 | TOKYO Gæstestol, blå      |         14 |                     14 | 11,690   | 14,612.5             | 14,612.5                   | 11,690        |                14 |                   | 04/10/20     |
|                 40000 |         106003 |      10000 | 1900-S | 2020-04-14 00:00:00.000 | PARIS Gæstestol, sort     |          8 |                      8 | 6,680    | 6,680                | 6,680                      | 6,680         |                 8 |                   | 04/14/20     |
|                 40000 |         106003 |      20000 | 1908-S | 2020-04-14 00:00:00.000 | LONDON Drejestol, blå     |         20 |                     20 | 16,460   | 16,460               | 16,460                     | 16,460        |                20 |                   | 04/14/20     |
|                 40000 |         106003 |      30000 | 1906-S | 2020-04-14 00:00:00.000 | ATHEN Skuffemodul         |         20 |                     20 | 37,580   | 37,580               | 37,580                     | 37,580        |                20 |                   | 04/14/20     |
|                 30000 |         106004 |      10000 | 1980-S | 2020-05-02 00:00:00.000 | MOSCOW Drejestol, rød     |         10 |                     10 | 8,230    | 10,287.5             | 10,287.5                   | 8,230         |                10 |                   | 05/02/20     |
|                 30000 |         106004 |      20000 | 1996-S | 2020-05-02 00:00:00.000 | ATLANTA Whiteboard, basis |          2 |                      2 | 12,110   | 15,137.5             | 15,137.5                   | 12,110        |                 2 |                   | 05/02/20     |
|                 50000 |         106005 |      10000 | LISE   | 2020-04-12 00:00:00.000 | Rebecca Christensen       |          4 |                      4 | 1,720    | 1,720                | 1,720                      | 1,720         |                 4 |                   | 04/12/20     |
|                 50000 |         106005 |      20000 | MARTIN | 2020-04-12 00:00:00.000 | Martin Hansen             |          8 |                      8 | 3,120    | 3,120                | 3,120                      | 3,120         |                 8 |                   | 04/12/20     |
|                 10000 |         106006 |      10000 | 1900-S | 2021-03-09 00:00:00.000 | PARIS Gæstestol, sort     |          0 |                      0 | 0        | 0                    | 0                          | 0             |                 0 |                   | 03/09/21     |
|                 10000 |         106007 |      10000 | 1896-S | 2021-03-09 00:00:00.000 | ATHEN Skrivebord          |         10 |                      0 | 43,370   | 0                    | 0                          | 43,370        |                10 |                   | 03/09/21     |


Table: Sales Line
|   Sell-to customer no. |   Document no. |   Line no. | No.    | Shipment date           | Description                |   Quantity |   Line discount amount |   Quantity shipped |   Quantity invoiced |   Bill-to customer no. |   Line amount | Posting date   |   Quantity (base) |   Vendor No. |
|-----------------------:|---------------:|-----------:|:-------|:------------------------|:---------------------------|-----------:|-----------------------:|-------------------:|--------------------:|-----------------------:|--------------:|:---------------|------------------:|-------------:|
|                  20000 |           1001 |      10000 | 1936-S | 2020-12-02 00:00:00.000 | BERLIN Gæstestol, gul      |         10 |                      0 |                  0 |                   0 |                  20000 |         10710 |                |                10 |        20000 |
|                  40000 |           1002 |      10000 | 2000-S | 2020-12-02 00:00:00.000 | SYDNEY Swivel Chair, green |          5 |                      0 |                  0 |                   0 |                  40000 |          5280 |                |                 5 |        30000 |
|                  10000 |         101001 |      10000 | 1996-S | 2020-04-02 00:00:00.000 | ATLANTA Whiteboard, basis  |         12 |                      0 |                  0 |                   0 |                  10000 |         93156 |                |                12 |        30000 |
|                  10000 |         101002 |      10000 | 1968-S | 2020-05-01 00:00:00.000 | MEXICO Drejestol, sort     |         10 |                      0 |                  0 |                   0 |                  10000 |         10560 |                |                10 |        30000 |
|                  10000 |         101002 |      20000 | 1928-S | 2020-05-01 00:00:00.000 | AMSTERDAM Lampe            |          7 |                      0 |                  0 |                   0 |                  10000 |          2135 |                |                 7 |        10000 |
|                  30000 |         101003 |      10000 | 1920-S | 2020-04-22 00:00:00.000 | ANTWERPEN Konferencebord   |          8 |                      0 |                  0 |                   0 |                  30000 |         28792 |                |                 8 |        20000 |
|                  40000 |         101004 |      10000 | 2000-S | 2020-05-13 00:00:00.000 | SYDNEY Swivel Chair, green |          3 |                      0 |                  0 |                   0 |                  40000 |          3168 |                |                 3 |        30000 |
|                  10000 |         102199 |      10000 | 1968-S | 2020-12-02 00:00:00.000 | MEXICO Drejestol, sort     |          5 |                      0 |                  0 |                   0 |                  10000 |          5280 |                |                 5 |        30000 |
|                  10000 |         102199 |      20000 | 1996-S | 2020-12-02 00:00:00.000 | ATLANTA Whiteboard, basis  |          7 |                      0 |                  0 |                   0 |                  10000 |         54341 |                |                 7 |        30000 |
|                  10000 |         102200 |      10000 | 2000-S | 2020-12-02 00:00:00.000 | SYDNEY Drejestol, grøn     |          2 |                      0 |                  0 |                   0 |                  10000 |          2112 |                |                 2 |        30000 |


Table: Salesperson
| Code   | Name                 | Job title   |
|:-------|:---------------------|:------------|
| AD     | Anton Dahl           |             |
| AH     | Anne-Mette Henriksen |             |
| FF     | Frederik Friis       |             |
| HH     | Hanne Holm           |             |
| JM     | Jacob Mathiasen      |             |
| LS     | Lucas Skov           |             |
| MF     | Maibritt Frandsen    |             |


Table: Value Entry
|   Entry no. | Item no.   |   Source no. |   Document no. | Description   |   Valued quantity |   Sales amount (actual) | Salespers./purch. code   | Source code   | Source type   |   Cost amount (actual) | External document no.   |   Document line no. | Entry type   | No.   | Posting date            |   Vendor No (Item) |   item ledger entry quantity |
|------------:|:-----------|-------------:|---------------:|:--------------|------------------:|------------------------:|:-------------------------|:--------------|:--------------|-----------------------:|:------------------------|--------------------:|:-------------|:------|:------------------------|-------------------:|-----------------------------:|
|         406 | 1896-S     |        20000 |         108001 |               |                 4 |                       0 |                          | KØB           | Vendor        |                  17348 |                         |               10000 | Direct Cost  |       | 2019-01-01 00:00:00.000 |              30000 |                            4 |
|         407 | 1906-S     |        30000 |         108002 |               |                 5 |                       0 |                          | KØB           | Vendor        |                   9395 |                         |               10000 | Direct Cost  |       | 2019-01-01 00:00:00.000 |              30000 |                            5 |
|         409 | 1928-S     |        40000 |         108004 |               |                 8 |                       0 |                          | KØB           | Vendor        |                   1904 |                         |               10000 | Direct Cost  |       | 2019-01-01 00:00:00.000 |              10000 |                            8 |
|         411 | 1960-S     |        40000 |         108006 |               |                 2 |                       0 |                          | KØB           | Vendor        |                   1670 |                         |               10000 | Direct Cost  |       | 2019-01-01 00:00:00.000 |              20000 |                            2 |
|         412 | 1964-S     |        10000 |         108007 |               |                 4 |                       0 |                          | KØB           | Vendor        |                   3340 |                         |               10000 | Direct Cost  |       | 2019-01-01 00:00:00.000 |              20000 |                            4 |
|         413 | 2000-S     |        20000 |         108008 |               |                10 |                       0 |                          | KØB           | Vendor        |                   8230 |                         |               10000 | Direct Cost  |       | 2019-01-01 00:00:00.000 |              30000 |                           10 |
|         414 | 1968-S     |        40000 |         108009 |               |                 2 |                       0 |                          | KØB           | Vendor        |                   1646 |                         |               10000 | Direct Cost  |       | 2019-01-01 00:00:00.000 |              30000 |                            2 |
|         415 | 1968-S     |        20000 |         108010 |               |                 3 |                       0 |                          | KØB           | Vendor        |                   2469 |                         |               10000 | Direct Cost  |       | 2019-01-01 00:00:00.000 |              30000 |                            3 |
|         416 | 1968-S     |        10000 |         108011 |               |                 5 |                       0 |                          | KØB           | Vendor        |                   4115 |                         |               10000 | Direct Cost  |       | 2019-01-01 00:00:00.000 |              30000 |                            5 |
|         418 | 1920-S     |        40000 |         108013 |               |                10 |                       0 |                          | KØB           | Vendor        |                  28080 |                         |               10000 | Direct Cost  |       | 2019-09-01 00:00:00.000 |              20000 |                           10 |


Table: Vendor
|   No. | Name                     | City        | County   | Vendor                         |
|------:|:-------------------------|:------------|:---------|:-------------------------------|
| 10000 | Fredborg Lamper A/S      | København K |          | 10000 Fredborg Lamper A/S      |
| 20000 | Elvstrøm Træindustri A/S | Koge        |          | 20000 Elvstrøm Træindustri A/S |
| 30000 | Schmeichel Møbler A/S    | Copenhagen  |          | 30000 Schmeichel Møbler A/S    |
| 40000 | Wide World Importers     | Manchester  |          | 40000 Wide World Importers     |
| 50000 | Fabrikam, Inc.           | Atlanta     |          | 50000 Fabrikam, Inc.           |

 


tables: name: Calendar
data: Calendar.csv
name: Customer
data: Customer.csv
name: Item
data: Item.csv
name: Purchase Line
data: Purchase Line.csv
name: Sales Line
data: Sales Line.csv
name: Salesperson
data: Salesperson.csv
name: Value Entry
data: Value Entry.csv
name: Vendor
data: Vendor.csv
label: Sales Line
No.
Item
No.
Value Entry
Source no.
Customer
No.
Value Entry
Salespers./purch. code
Salesperson
Code
Sales Line
Sell-to customer no.
Customer
No.
Value Entry
Item no.
Item
No.
Value Entry
Posting date
Calendar
Date
Sales Line
Shipment date
Calendar
Date
Value Entry
Vendor No (Item)
Vendor
No.
Sales Line
Vendor No.
Vendor
No.
Purchase Line
No.
Item
No.
Purchase Line
Expected receipt date
Calendar
Date
Purchase Line
Outstanding quantity
Salesperson
Code
Purchase Line
buy-from vendor no.
Vendor
No.
output: {
    "joins": [
        {
            "from_table": "Sales Line",
            "from_column": "No.",
            "to_table": "Item",
            "to_column": "No."
        },
        {
            "from_table": "Value Entry",
            "from_column": "Source no.",
            "to_table": "Customer",
            "to_column": "No."
        },
        {
            "from_table": "Value Entry",
            "from_column": "Salespers./purch. code",
            "to_table": "Salesperson",
            "to_column": "Code"
        },
        {
            "from_table": "Sales Line",
            "from_column": "Sell-to customer no.",
            "to_table": "Customer",
            "to_column": "No."
        },
        {
            "from_table": "Value Entry",
            "from_column": "Item no.",
            "to_table": "Item",
            "to_column": "No."
        },
        {
            "from_table": "Value Entry",
            "from_column": "Posting date",
            "to_table": "Calendar",
            "to_column": "Date"
        },
        {
            "from_table": "Sales Line",
            "from_column": "Shipment date",
            "to_table": "Calendar",
            "to_column": "Date"
        },
        {
            "from_table": "Value Entry",
            "from_column": "Vendor No (Item)",
            "to_table": "Vendor",
            "to_column": "No."
        },
        {
            "from_table": "Sales Line",
            "from_column": "Vendor No.",
            "to_table": "Vendor",
            "to_column": "No."
        },
        {
            "from_table": "Purchase Line",
            "from_column": "No.",
            "to_table": "Item",
            "to_column": "No."
        },
        {
            "from_table": "Purchase Line",
            "from_column": "Expected receipt date",
            "to_table": "Calendar",
            "to_column": "Date"
        },
        {
            "from_table": "Purchase Line",
            "from_column": "Outstanding quantity",
            "to_table": "Salesperson",
            "to_column": "Code"
        },
        {
            "from_table": "Purchase Line",
            "from_column": "buy-from vendor no.",
            "to_table": "Vendor",
            "to_column": "No."
        }
    ]
}