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: Aggregation Table
|   Average | Value   |
|----------:|:--------|
|   174.925 | Measure |


Table: Append1
| Type   | Material   | Description   |
|:-------|:-----------|:--------------|
| A      | X          | plastic       |
| A      | X          | plastic       |
| B      | Y          | metal         |
| B      | Z          | rubber        |
| A      | 1          | cover         |
| A      | 2          | top           |
| B      | 3          | middle        |
| A      | a          | type1         |
| A      | b          | type2         |


Table: Calendar
| Date                    |   Week |
|:------------------------|-------:|
| 2020-01-01 00:00:00.000 |      1 |
| 2020-01-02 00:00:00.000 |      1 |
| 2020-01-03 00:00:00.000 |      1 |
| 2020-01-04 00:00:00.000 |      1 |
| 2020-01-05 00:00:00.000 |      2 |
| 2020-01-06 00:00:00.000 |      2 |
| 2020-01-07 00:00:00.000 |      2 |
| 2020-01-08 00:00:00.000 |      2 |
| 2020-01-09 00:00:00.000 |      2 |
| 2020-01-10 00:00:00.000 |      2 |


Table: Production data
|   prod_order_number |   P21_hour_qty |
|--------------------:|---------------:|
|                1001 |              1 |
|                1001 |              1 |
|                1002 |              2 |
|                1003 |              3 |


Table: Query1
| Type   | Material   | Description   |
|:-------|:-----------|:--------------|
| A      | X          | plastic       |
| A      | X          | plastic       |
| B      | Y          | metal         |
| B      | Z          | rubber        |
| A      | 1          | cover         |
| A      | 2          | top           |
| B      | 3          | middle        |
| A      | a          | type1         |
| A      | b          | type2         |


Table: Table (10)
| Column1   |   Column2 |
|:----------|----------:|
| A         |      0.9  |
| B         |      0.91 |
| C         |      0.92 |
| D         |      0.93 |
| E         |      0.99 |
| F         |      0.98 |


Table: Table (11)
| Column1                 | Custom   |
|:------------------------|:---------|
| 2020-01-01 00:00:00.000 | 1/2/2020 |


Table: Table (12)
|   Auto ID |   Roll No | Name   |   Marks | Gender   |   Top N |
|----------:|----------:|:-------|--------:|:---------|--------:|
|         1 |     12324 | ABC    |     515 | M        |       3 |
|         2 |      2323 | LMN    |     525 | M        |       2 |
|         3 |     23234 | XYN    |     535 | F        |       1 |
|         4 |     65655 | DEF    |     525 | M        |       2 |
|         5 |    345345 | PRS    |     510 | F        |       4 |


Table: Table (13)
| Time                    |   device |     m1 |    m2 |    m3 |   State |   Cycles Desired |   Cycle Change |   Cycles |   Cycles Count |
|:------------------------|---------:|-------:|------:|------:|--------:|-----------------:|---------------:|---------:|---------------:|
| 2020-09-01 00:01:00.000 |        4 | -0.103 |   0   | 100   |       1 |                1 |              1 |        1 |              1 |
| 2020-09-01 12:56:00.000 |        4 | -0.103 |   0   | 100   |       1 |                1 |              0 |        1 |              1 |
| 2020-09-01 13:26:00.000 |        4 | -0.103 |   0   | 100   |       1 |                1 |              0 |        1 |              1 |
| 2020-09-01 13:26:00.000 |        4 | -0.594 |   0   | 100   |       4 |                1 |              0 |        1 |              1 |
| 2020-09-01 13:31:00.000 |        4 | -0.594 |  -0.1 | 100   |       4 |                1 |              0 |        1 |              1 |
| 2020-09-01 13:36:00.000 |        4 | -0.654 | -15   |  99.9 |       4 |                1 |              0 |        1 |              1 |
| 2020-09-01 20:31:00.000 |        4 |  0.02  | -24.7 |  77.3 |       4 |                1 |              0 |        1 |              1 |
| 2020-09-01 20:33:00.000 |        4 | 16.927 |   0   |  77.8 |       3 |                1 |              0 |        1 |              1 |
| 2020-09-01 20:38:00.000 |        4 | 17.407 |   1.5 |  79.1 |       3 |                1 |              0 |        1 |              1 |
| 2020-09-01 20:58:00.000 |        4 | 17.331 |   7.3 |  84.4 |       3 |                1 |              0 |        1 |              1 |


Table: Table (14)
| Selection   | First Name   | Last Name   | Department   | Salary   | Source   |
|:------------|:-------------|:------------|:-------------|:---------|:---------|
| Office      | ABC          | Corp        | NA           |          |          |
| A           | Josephine    | Darakjy     | Payroll      | 42000    | Internal |
| B           | Lenna        | Paprocki    | Research     | 58000    | Internal |
| C           | Simona       | Morasca     | Real Estate  | 75000    | External |
| D           | Leota        | Dilliard    | Marketing    | 60000    | External |


Table: Table (15)
| Weekending Date         | Test Date               | Student Name   |   Test Score |
|:------------------------|:------------------------|:---------------|-------------:|
| 2020-08-07 00:00:00.000 | 2020-08-03 00:00:00.000 | Ron            |         0.95 |
| 2020-08-07 00:00:00.000 | 2020-08-03 00:00:00.000 | Rudy           |         0.89 |
| 2020-08-07 00:00:00.000 | 2020-08-06 00:00:00.000 | James          |         0.87 |
| 2020-08-07 00:00:00.000 | 2020-08-05 00:00:00.000 | Ron            |         0.91 |
| 2020-08-14 00:00:00.000 | 2020-08-11 00:00:00.000 | Rudy           |         0.87 |
| 2020-08-21 00:00:00.000 | 2020-08-18 00:00:00.000 | James          |         0.93 |
| 2020-08-14 00:00:00.000 | 2020-08-12 00:00:00.000 | Ron            |         0.8  |
| 2020-08-14 00:00:00.000 | 2020-08-12 00:00:00.000 | Rudy           |         0.92 |
| 2020-08-21 00:00:00.000 | 2020-08-17 00:00:00.000 | Ron            |         0.85 |


Table: Table (16)
|   ID | Submitted Date          | Date Resolved           |
|-----:|:------------------------|:------------------------|
|    1 | 2020-02-17 00:00:00.000 | 2020-08-26 00:00:00.000 |
|    2 | 2020-02-27 00:00:00.000 | 2020-08-27 00:00:00.000 |
|    3 | 2020-04-29 00:00:00.000 | 2020-08-24 00:00:00.000 |
|    4 | 2020-04-30 00:00:00.000 | 2020-08-24 00:00:00.000 |
|    5 | 2020-05-04 00:00:00.000 | 2020-08-24 00:00:00.000 |
|    6 | 2020-05-07 00:00:00.000 | 2020-08-26 00:00:00.000 |
|    7 | 2020-05-22 00:00:00.000 | 2020-08-24 00:00:00.000 |
|    8 | 2020-06-04 00:00:00.000 | 2020-08-28 00:00:00.000 |
|    9 | 2020-06-10 00:00:00.000 | 2020-08-24 00:00:00.000 |
|   10 | 2020-06-11 00:00:00.000 | 2020-08-28 00:00:00.000 |


Table: Table (17)
|   Member Count |   Age - Category | District - Category   |
|---------------:|-----------------:|:----------------------|
|              2 |               23 | z                     |
|              6 |               23 | z                     |
|              5 |               23 | x                     |
|              4 |               29 | s                     |
|              1 |               29 | s                     |
|              2 |               29 | z                     |
|              9 |               36 | z                     |
|             10 |               36 | x                     |
|             24 |               36 | x                     |
|              9 |               36 | z                     |


Table: Table (18)
|   ID |   Time_1 |   Time_2 Desired |   Time_2 |
|-----:|---------:|-----------------:|---------:|
|    1 |       30 |               10 |       10 |
|    1 |       30 |               10 |       10 |
|    1 |       30 |               10 |       10 |
|    2 |       50 |               50 |       50 |
|    3 |       40 |               20 |       20 |
|    3 |       40 |               20 |       20 |
|    4 |       80 |               20 |       20 |
|    4 |       80 |               20 |       20 |
|    4 |       80 |               20 |       20 |
|    4 |       80 |               20 |       20 |


Table: Table (19)
| Date                    | SalesPerson   | Item   |   Value |
|:------------------------|:--------------|:-------|--------:|
| 2020-05-01 00:00:00.000 | ABC           | MM     |     100 |
| 2020-08-01 00:00:00.000 | ABC           | MM     |      30 |
| 2020-08-01 00:00:00.000 | XYZ           | NN     |      50 |
| 2020-09-05 00:00:00.000 | ABC           | QQ     |      60 |
| 2020-09-05 00:00:00.000 | XYZ           | NN     |      70 |


Table: Table (2)
|   SampleID | Date                    |   Outcome | Custom               |
|-----------:|:------------------------|----------:|:---------------------|
|          1 | 2020-01-01 00:00:00.000 |         1 | 9/4/2020 10:51:34 PM |
|          2 | 2020-01-01 00:00:00.000 |         0 | 9/4/2020 10:51:34 PM |
|          3 | 2020-01-01 00:00:00.000 |         1 | 9/4/2020 10:51:34 PM |


Table: Table (20)
|   Time |   Rain mm |   Event Group |
|-------:|----------:|--------------:|
|      1 |         1 |             1 |
|      2 |         2 |             1 |
|      3 |         1 |             1 |
|      4 |         0 |             0 |
|      5 |         0 |             0 |
|      6 |         1 |             2 |
|      7 |         4 |             2 |
|      8 |         2 |             2 |
|      9 |         1 |             2 |
|     10 |         0 |             0 |


Table: Table (21)
| Customer   | Product   |   Serial | StartDate               | EndDate    |   Period (M) | Free / Subscription   |   Given Free | StartDate (FreeWare)    | EndDate (FreeWare)   | Serial (FreeWare)   | Serial (Sub)   | StartDate (Sub)         | EndDate (Sub)   | Converted?   |
|:-----------|:----------|---------:|:------------------------|:-----------|-------------:|:----------------------|-------------:|:------------------------|:---------------------|:--------------------|:---------------|:------------------------|:----------------|:-------------|
| A          | WA        |    15685 | 2019-01-01 00:00:00.000 | 31/12/2019 |           12 | Free                  |            1 | 2020-01-01 00:00:00.000 | 31/5/2020            | 67732               | 60719          | 2020-01-07 00:00:00.000 | 30/6/2021       | Y            |
| A          | WA        |    67732 | 2020-01-01 00:00:00.000 | 31/5/2020  |            5 | Free                  |            1 | 2020-01-01 00:00:00.000 | 31/5/2020            | 67732               | 60719          | 2020-01-07 00:00:00.000 | 30/6/2021       | Y            |
| A          | WA        |    12114 | 2020-01-06 00:00:00.000 | 30/6/2020  |            1 | Sub                   |            1 | 2020-01-01 00:00:00.000 | 31/5/2020            | 67732               | 60719          | 2020-01-07 00:00:00.000 | 30/6/2021       | Y            |
| A          | WA        |    60719 | 2020-01-07 00:00:00.000 | 30/6/2021  |           12 | Sub                   |            1 | 2020-01-01 00:00:00.000 | 31/5/2020            | 67732               | 60719          | 2020-01-07 00:00:00.000 | 30/6/2021       | Y            |
| A          | QWS       |    32789 | 2019-01-01 00:00:00.000 | 31/12/2019 |           12 | Free                  |            1 | 2019-01-01 00:00:00.000 | 31/12/2019           | 32789               |                |                         |                 | N            |
| A          | KS        |    48065 | 2020-01-07 00:00:00.000 | 30/6/2021  |           12 | Sub                   |            0 |                         |                      |                     | 48065          | 2020-01-07 00:00:00.000 | 30/6/2021       | N            |
| B          | WA        |    18944 | 2020-01-01 00:00:00.000 | 31/5/2020  |            5 | Free                  |            1 | 2020-01-01 00:00:00.000 | 31/5/2020            | 18944               | 56311          | 2020-01-06 00:00:00.000 | 30/6/2020       | Y            |
| B          | WA        |    56311 | 2020-01-06 00:00:00.000 | 30/6/2020  |            1 | Sub                   |            1 | 2020-01-01 00:00:00.000 | 31/5/2020            | 18944               | 56311          | 2020-01-06 00:00:00.000 | 30/6/2020       | Y            |
| B          | KS        |    77724 | 2020-01-07 00:00:00.000 | 30/6/2021  |           12 | Sub                   |            0 |                         |                      |                     | 77724          | 2020-01-07 00:00:00.000 | 30/6/2021       | N            |
| C          | QWS       |    19615 | 2020-01-07 00:00:00.000 | 30/6/2021  |           12 | Sub                   |            0 |                         |                      |                     | 19615          | 2020-01-07 00:00:00.000 | 30/6/2021       | N            |


Table: Table (3)
| Person   | Activite   | Start                   | End                     | Location   |   Consequective |   Group |
|:---------|:-----------|:------------------------|:------------------------|:-----------|----------------:|--------:|
| Person 1 | Surgery    | 2019-06-05 17:29:00.000 | 2019-06-05 18:02:00.000 | Surgery 1  |               1 |       1 |
| Person 1 | Ward       | 2019-06-05 18:03:00.000 | 2019-06-06 22:44:00.000 | Ward 135   |               1 |       1 |
| Person 1 | Ward       | 2019-06-06 22:45:00.000 | 2019-06-14 10:00:00.000 | Ward 354   |               1 |       1 |
| Person 1 | Ward       | 2020-03-26 10:54:00.000 | 2020-03-26 16:48:00.000 | Ward 237   |               0 |       2 |
| Person 1 | Ward       | 2020-03-26 16:49:00.000 | 2020-03-27 15:45:00.000 | Ward 236   |               1 |       2 |
| Person 1 | Ward       | 2020-03-27 15:46:00.000 | 2020-04-03 12:29:00.000 | Ward 303   |               1 |       2 |
| Person 2 | Ward       | 2019-06-24 14:57:00.000 | 2019-06-27 20:50:00.000 | Ward 137   |               1 |       1 |
| Person 2 | Ward       | 2019-06-27 20:51:00.000 | 2019-06-28 20:32:00.000 | Ward 137   |               1 |       1 |
| Person 2 | Ward       | 2019-06-28 20:33:00.000 | 2019-07-06 09:05:00.000 | Ward 67    |               1 |       1 |
| Person 2 | Ward       | 2019-07-06 09:06:00.000 | 2019-07-09 14:19:00.000 | Ward 136   |               1 |       1 |


Table: Table (4)
|   INVOICE AMOUNT |   Column 2 |   Column 3 |   Column 4 |   Column 5 |
|-----------------:|-----------:|-----------:|-----------:|-----------:|
|            18500 |    5388.89 |    5388.89 |       6000 |    5388.89 |
|            18500 |    5388.89 |    5388.89 |       6000 |    5388.89 |
|            18500 |    5388.89 |    5388.89 |       6000 |    5388.89 |
|            18500 |    5388.89 |    5388.89 |       6000 |    5388.89 |
|            19000 |    5388.89 |    5388.89 |       6500 |    5388.89 |
|             5000 |    5388.89 |    5388.89 |      -7500 |    5388.89 |
|            19000 |    5388.89 |    5388.89 |       6500 |    5388.89 |
|            19000 |    5388.89 |    5388.89 |       6500 |    5388.89 |
|            25000 |    5388.89 |    5388.89 |      12500 |    5388.89 |


Table: Table (5)
| Type   | Material1   | Description1   | Materia2   | Description2   | Material3   | Description3   |
|:-------|:------------|:---------------|:-----------|:---------------|:------------|:---------------|
| A      | X           | plastic        | 1          | cover          | a           | type1          |
| A      | X           | plastic        | 2          | top            | b           | type2          |
| B      | Y           | metal          | 3          | middle         |             |                |
| B      | Z           | rubber         |            |                |             |                |


Table: Table (6)
| Type   | Material   | Description   |
|:-------|:-----------|:--------------|
| A      | X          | plastic       |
| A      | X          | plastic       |
| B      | Y          | metal         |
| B      | Z          | rubber        |


Table: Table (7)
| Type   | Material   | Description   |
|:-------|:-----------|:--------------|
| A      | 1          | cover         |
| A      | 2          | top           |
| B      | 3          | middle        |
| B      |            |               |


Table: Table (8)
| Type   | Material   | Description   |
|:-------|:-----------|:--------------|
| A      | a          | type1         |
| A      | b          | type2         |
| B      |            |               |
| B      |            |               |


Table: Table (9)
| Member ID   |   Age - Category | District - Category   |
|:------------|-----------------:|:----------------------|
| A           |               23 | z                     |
| b           |               23 | z                     |
| c           |               23 | x                     |
| d           |               29 | s                     |
| e           |               29 | s                     |
| f           |               29 | z                     |
| g           |               36 | z                     |
| h           |               36 | x                     |
| i           |               36 | x                     |
| j           |               36 | z                     |


Table: Table 2
|   Value |   Column |
|--------:|---------:|
|       1 |  3.20762 |
|       2 |  3.39991 |
|       3 |  3.59944 |
|       4 |  3.80616 |
|       5 |  4.01997 |
|       6 |  4.24074 |
|       7 |  4.46833 |
|       8 |  4.70255 |
|       9 |  4.94317 |
|      10 |  5.18992 |


Table: Table 3
| key      | date                    |   value2 |
|:---------|:------------------------|---------:|
| asfasf44 | 2020-07-31 00:00:00.000 |       56 |
| asfasf45 | 2020-07-31 00:00:00.000 |       45 |
| asfasf44 | 2020-08-15 00:00:00.000 |       44 |
| asfasf45 | 2020-08-15 00:00:00.000 |       33 |


Table: Table 4
| key      |   value1 |
|:---------|---------:|
| asfasf44 |       34 |
| asfasf45 |      463 |
| asfasf46 |       23 |
| asfasf47 |      534 |
| asfasf48 |        6 |
| asfasf49 |      657 |


Table: Table 5
| Selection                          | First Name   | Last Name   | Department   | Salary   |
|:-----------------------------------|:-------------|:------------|:-------------|:---------|
| A                                  | Josephine    | Darakjy     | Payroll      | 42000    |
| B                                  | Lenna        | Paprocki    | Research     | 58000    |
| C                                  | Simona       | Morasca     | Real Estate  | 75000    |
| D                                  | Leota        | Dilliard    | Marketing    | 60000    |
|                                    |              |             | Minimum      | 42000    |
|                                    |              |             | Maximum      | 75000    |
|                                    |              |             | Average      | 58750    |
| Office                             | ABC          | Corp        |              |          |
| Source:  and Internal and External |              |             |              |          |


Table: Table
| Date                    |   Value |
|:------------------------|--------:|
| 2019-01-01 00:00:00.000 |      74 |
| 2019-01-02 00:00:00.000 |     177 |
| 2019-01-03 00:00:00.000 |     298 |
| 2019-01-04 00:00:00.000 |     156 |
| 2019-01-05 00:00:00.000 |      60 |
| 2019-01-06 00:00:00.000 |     252 |
| 2019-01-07 00:00:00.000 |     265 |
| 2019-01-08 00:00:00.000 |      95 |
| 2019-01-09 00:00:00.000 |     180 |
| 2019-01-10 00:00:00.000 |      97 |

 


tables: name: Aggregation Table
data: Aggregation Table.csv
name: Append1
data: Append1.csv
name: Calendar
data: Calendar.csv
name: Production data
data: Production data.csv
name: Query1
data: Query1.csv
name: Table (10)
data: Table (10).csv
name: Table (11)
data: Table (11).csv
name: Table (12)
data: Table (12).csv
name: Table (13)
data: Table (13).csv
name: Table (14)
data: Table (14).csv
name: Table (15)
data: Table (15).csv
name: Table (16)
data: Table (16).csv
name: Table (17)
data: Table (17).csv
name: Table (18)
data: Table (18).csv
name: Table (19)
data: Table (19).csv
name: Table (2)
data: Table (2).csv
name: Table (20)
data: Table (20).csv
name: Table (21)
data: Table (21).csv
name: Table (3)
data: Table (3).csv
name: Table (4)
data: Table (4).csv
name: Table (5)
data: Table (5).csv
name: Table (6)
data: Table (6).csv
name: Table (7)
data: Table (7).csv
name: Table (8)
data: Table (8).csv
name: Table (9)
data: Table (9).csv
name: Table 2
data: Table 2.csv
name: Table 3
data: Table 3.csv
name: Table 4
data: Table 4.csv
name: Table 5
data: Table 5.csv
name: Table
data: Table.csv
label: Append1
Description
Table (7)
Description
Table (8)
Description
Table (7)
Description
Append1
Description
Table (5)
Description2
Table (8)
Description
Table (5)
Description2
Query1
Description
Table (7)
Description
Query1
Description
Table (5)
Description2
Table 3
key
Table 4
key
Table (18)
ID
Table (16)
ID
output: {
    "joins": [
        {
            "from_table": "Append1",
            "from_column": "Description",
            "to_table": "Table (7)",
            "to_column": "Description"
        },
        {
            "from_table": "Table (8)",
            "from_column": "Description",
            "to_table": "Table (7)",
            "to_column": "Description"
        },
        {
            "from_table": "Append1",
            "from_column": "Description",
            "to_table": "Table (5)",
            "to_column": "Description2"
        },
        {
            "from_table": "Table (8)",
            "from_column": "Description",
            "to_table": "Table (5)",
            "to_column": "Description2"
        },
        {
            "from_table": "Query1",
            "from_column": "Description",
            "to_table": "Table (7)",
            "to_column": "Description"
        },
        {
            "from_table": "Query1",
            "from_column": "Description",
            "to_table": "Table (5)",
            "to_column": "Description2"
        },
        {
            "from_table": "Table 3",
            "from_column": "key",
            "to_table": "Table 4",
            "to_column": "key"
        },
        {
            "from_table": "Table (18)",
            "from_column": "ID",
            "to_table": "Table (16)",
            "to_column": "ID"
        }
    ]
}