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: desafiofinal category
|   idcategory | category        |   desafiofinal.subcategory.idsubCategory | desafiofinal.subcategory.subCategory   |   desafiofinal.subcategory.category_idcategory |
|-------------:|:----------------|-----------------------------------------:|:---------------------------------------|-----------------------------------------------:|
|            1 | Furniture       |                                        1 | Bookcases                              |                                              1 |
|            1 | Furniture       |                                        2 | Furnishings                            |                                              1 |
|            1 | Furniture       |                                        3 | Chairs                                 |                                              1 |
|            1 | Furniture       |                                        4 | Tables                                 |                                              1 |
|            2 | Office Supplies |                                        5 | Art                                    |                                              2 |
|            2 | Office Supplies |                                        6 | Envelopes                              |                                              2 |
|            2 | Office Supplies |                                        7 | Binders                                |                                              2 |
|            2 | Office Supplies |                                        8 | Storage                                |                                              2 |
|            2 | Office Supplies |                                        9 | Fasteners                              |                                              2 |
|            2 | Office Supplies |                                       10 | Appliances                             |                                              2 |


Table: desafiofinal city
|   idcity | city         |   state_idstate |
|---------:|:-------------|----------------:|
|     4249 | Aylesbury    |             123 |
|     4292 | Barnsley     |             123 |
|     4299 | Basingstoke  |             123 |
|     4365 | Birmingham   |             123 |
|     4369 | Blackpool    |             123 |
|     4379 | Bognor Regis |             123 |
|     4385 | Bolton       |             123 |
|     4405 | Bournemouth  |             123 |
|     4408 | Bradford     |             123 |
|     4423 | Brighton     |             123 |


Table: desafiofinal country
|   idcountry | country        |
|------------:|:---------------|
|           1 | Germany        |
|           2 | Belgium        |
|           3 | Nigeria        |
|           4 | Iran           |
|           5 | United Kingdom |
|           6 | Saudi Arabia   |
|           7 | Cote dIvoire   |
|           8 | Brazil         |
|           9 | Mexico         |
|          10 | Ghana          |


Table: desafiofinal customer
| idcustomer   | customerName    |   city_idcity |
|:-------------|:----------------|--------------:|
| AA-10315     | Alex Avila      |          4116 |
| AA-10375     | Allen Armold    |          4504 |
| AA-10480     | Andrew Allen    |          4335 |
| AA-10645     | Anna Andreadi   |          4556 |
| AA-315       | Alex Avila      |          4457 |
| AA-375       | Allen Armold    |          4457 |
| AA-480       | Andrew Allen    |          4310 |
| AA-645       | Anna Andreadi   |          4139 |
| AB-10015     | Aaron Bergman   |          4186 |
| AB-10060     | Adam Bellavance |          4478 |


Table: desafiofinal priority
|   idpriority | priority   | desafiofinal.torder.idorder   | desafiofinal.torder.orderDate   |   desafiofinal.torder.priority_idpriority | desafiofinal.torder.customer_idcustomer   |
|-------------:|:-----------|:------------------------------|:--------------------------------|------------------------------------------:|:------------------------------------------|
|            1 | Medium     | AE-2011-9160                  | 2011-10-03 00:00:00.000         |                                         1 | PO-8865                                   |
|            1 | Medium     | AE-2014-4120                  | 2014-03-07 00:00:00.000         |                                         1 | JD-5790                                   |
|            1 | Medium     | AG-2011-1070                  | 2011-01-10 00:00:00.000         |                                         1 | TH-11235                                  |
|            1 | Medium     | AG-2011-1390                  | 2011-08-16 00:00:00.000         |                                         1 | DH-3675                                   |
|            1 | Medium     | AG-2011-1440                  | 2011-12-15 00:00:00.000         |                                         1 | DB-3210                                   |
|            1 | Medium     | AG-2011-2040                  | 2011-01-01 00:00:00.000         |                                         1 | TB-11280                                  |
|            1 | Medium     | AG-2011-3130                  | 2011-03-11 00:00:00.000         |                                         1 | AJ-780                                    |
|            1 | Medium     | AG-2011-380                   | 2011-03-03 00:00:00.000         |                                         1 | CC-2370                                   |
|            1 | Medium     | AG-2011-450                   | 2011-11-07 00:00:00.000         |                                         1 | BC-1125                                   |
|            1 | Medium     | AG-2011-500                   | 2011-11-23 00:00:00.000         |                                         1 | AS-285                                    |


Table: desafiofinal product
| idproduct        | productName                               |   subCategory_idsubCategory |
|:-----------------|:------------------------------------------|----------------------------:|
| OFF-EAT-10000522 | Eaton Computer Printout Paper, Multicolor |                          13 |
| OFF-EAT-10000652 | Eaton Computer Printout Paper, 8.5 x 11   |                          13 |
| OFF-EAT-10000820 | Eaton Message Books, Multicolor           |                          13 |
| OFF-EAT-10000854 | Eaton Parchment Paper, Recycled           |                          13 |
| OFF-EAT-10001137 | Eaton Parchment Paper, 8.5 x 11           |                          13 |
| OFF-EAT-10001303 | Eaton Message Books, Premium              |                          13 |
| OFF-EAT-10001933 | Eaton Cards & Envelopes, Recycled         |                          13 |
| OFF-EAT-10002068 | Eaton Note Cards, Premium                 |                          13 |
| OFF-EAT-10002122 | Eaton Cards & Envelopes, Premium          |                          13 |
| OFF-EAT-10002674 | Eaton Memo Slips, Multicolor              |                          13 |


Table: desafiofinal sales
|   idsales |   sales |   quantity |   discount | shipDate                |   shippingCost |   profit | order_idorder   | product_idproduct   |
|----------:|--------:|-----------:|-----------:|:------------------------|---------------:|---------:|:----------------|:--------------------|
|       104 |  287.94 |          2 |          0 | 2014-08-08 00:00:00.000 |          55.6  |        0 | IN-2014-76114   | FUR-BO-10004699     |
|       274 |  103.92 |          2 |          0 | 2011-12-27 00:00:00.000 |          10.87 |        0 | AG-2011-4560    | FUR-DEF-10000572    |
|       961 |  287.94 |          2 |          0 | 2012-04-02 00:00:00.000 |          17.53 |        0 | IN-2012-30117   | FUR-BO-10004699     |
|      1185 |   58.6  |          2 |          0 | 2014-12-14 00:00:00.000 |           8.12 |        0 | MX-2014-157042  | FUR-FU-10000110     |
|      4042 |  918.78 |          2 |          0 | 2014-09-24 00:00:00.000 |         112.71 |        0 | IR-2014-7430    | FUR-HON-10001689    |
|      4043 |  103.92 |          2 |          0 | 2011-11-02 00:00:00.000 |          28.43 |        0 | IR-2011-3500    | FUR-DEF-10000572    |
|      5058 |  109.64 |          2 |          0 | 2014-09-29 00:00:00.000 |          15.97 |        0 | MX-2014-169684  | FUR-CH-10003392     |
|      5283 |  190.44 |          2 |          0 | 2014-09-18 00:00:00.000 |          11.05 |        0 | MX-2014-116855  | FUR-BO-10000148     |
|      5470 |   58.6  |          2 |          0 | 2012-04-25 00:00:00.000 |           4.61 |        0 | MX-2012-146115  | FUR-FU-10000110     |
|      6025 |  178.56 |          2 |          0 | 2013-12-02 00:00:00.000 |           9.23 |        0 | IR-2013-6110    | FUR-OFF-10002511    |


Table: desafiofinal state
|   idstate | state                   |   country_idcountry |
|----------:|:------------------------|--------------------:|
|         1 | North Rhine-Westphalia  |                   1 |
|         2 | Baden-Württemberg       |                   1 |
|         3 | East Flanders           |                   2 |
|         4 | Abia                    |                   3 |
|         5 | Khuzestan               |                   4 |
|         6 | Scotland                |                   5 |
|         7 | Asir                    |                   6 |
|         8 | Lagunes                 |                   7 |
|         9 | Pernambuco              |                   8 |
|        10 | Abuja Capital Territory |                   3 |


Table: desafiofinal subcategory
|   idsubCategory | subCategory   |   category_idcategory |
|----------------:|:--------------|----------------------:|
|               1 | Bookcases     |                     1 |
|               2 | Furnishings   |                     1 |
|               3 | Chairs        |                     1 |
|               4 | Tables        |                     1 |
|               5 | Art           |                     2 |
|               6 | Envelopes     |                     2 |
|               7 | Binders       |                     2 |
|               8 | Storage       |                     2 |
|               9 | Fasteners     |                     2 |
|              10 | Appliances    |                     2 |


Table: desafiofinal torder
| idorder      | orderDate               |   priority_idpriority | customer_idcustomer   |   desafiofinal.sales.idsales |   desafiofinal.sales.sales |   desafiofinal.sales.quantity |   desafiofinal.sales.discount | desafiofinal.sales.shipDate   |   desafiofinal.sales.shippingCost |   desafiofinal.sales.profit | desafiofinal.sales.order_idorder   | desafiofinal.sales.product_idproduct   |
|:-------------|:------------------------|----------------------:|:----------------------|-----------------------------:|---------------------------:|------------------------------:|------------------------------:|:------------------------------|----------------------------------:|----------------------------:|:-----------------------------------|:---------------------------------------|
| AG-2011-1390 | 2011-08-16 00:00:00.000 |                     1 | DH-3675               |                        12621 |                      26.22 |                             2 |                             0 | 2011-08-21 00:00:00.000       |                              2.41 |                        1.8  | AG-2011-1390                       | OFF-IBI-10000440                       |
| AG-2011-1440 | 2011-12-15 00:00:00.000 |                     1 | DB-3210               |                        16613 |                      58.74 |                             2 |                             0 | 2011-12-20 00:00:00.000       |                              3.04 |                       20.52 | AG-2011-1440                       | OFF-AVE-10004909                       |
| AG-2011-2040 | 2011-01-01 00:00:00.000 |                     1 | TB-11280              |                        16617 |                     408.3  |                             2 |                             0 | 2011-01-06 00:00:00.000       |                             35.46 |                      106.14 | AG-2011-2040                       | OFF-TEN-10000025                       |
| AG-2011-500  | 2011-11-23 00:00:00.000 |                     1 | AS-285                |                        16611 |                      54.9  |                             2 |                             0 | 2011-11-27 00:00:00.000       |                              3.48 |                       15.36 | AG-2011-500                        | OFF-WIL-10002153                       |
| AG-2011-7230 | 2011-01-07 00:00:00.000 |                     1 | AC-420                |                        10530 |                      91.38 |                             2 |                             0 | 2011-01-11 00:00:00.000       |                              4.36 |                       14.58 | AG-2011-7230                       | OFF-STA-10000244                       |
| AG-2011-8930 | 2011-12-26 00:00:00.000 |                     1 | MW-8220               |                          182 |                     116.04 |                             2 |                             0 | 2011-12-27 00:00:00.000       |                              9.32 |                       27.84 | AG-2011-8930                       | FUR-HON-10002599                       |
| AG-2012-4060 | 2012-03-19 00:00:00.000 |                     1 | LM-7065               |                        10872 |                     109.92 |                             2 |                             0 | 2012-03-21 00:00:00.000       |                              9.1  |                       38.46 | AG-2012-4060                       | OFF-BIC-10001823                       |
| AG-2012-4630 | 2012-05-28 00:00:00.000 |                     1 | CP-2340               |                         6477 |                     824.28 |                             2 |                             0 | 2012-05-30 00:00:00.000       |                             90.77 |                      280.2  | AG-2012-4630                       | FUR-BUS-10001187                       |
| AG-2012-4910 | 2012-11-16 00:00:00.000 |                     1 | KF-6285               |                        10545 |                      46.5  |                             2 |                             0 | 2012-11-22 00:00:00.000       |                              2.47 |                        9.72 | AG-2012-4910                       | OFF-TEN-10000894                       |
| AG-2013-5820 | 2013-11-05 00:00:00.000 |                     1 | ES-4080               |                        10870 |                      60.96 |                             2 |                             0 | 2013-11-08 00:00:00.000       |                              7.42 |                        5.46 | AG-2013-5820                       | OFF-XER-10003901                       |


Table: Medidas


 


tables: name: desafiofinal category
data: desafiofinal category.csv
name: desafiofinal city
data: desafiofinal city.csv
name: desafiofinal country
data: desafiofinal country.csv
name: desafiofinal customer
data: desafiofinal customer.csv
name: desafiofinal priority
data: desafiofinal priority.csv
name: desafiofinal product
data: desafiofinal product.csv
name: desafiofinal sales
data: desafiofinal sales.csv
name: desafiofinal state
data: desafiofinal state.csv
name: desafiofinal subcategory
data: desafiofinal subcategory.csv
name: desafiofinal torder
data: desafiofinal torder.csv
name: Medidas
data: Medidas.csv
label: desafiofinal customer
city_idcity
desafiofinal city
idcity
desafiofinal city
state_idstate
desafiofinal state
idstate
desafiofinal state
country_idcountry
desafiofinal country
idcountry
desafiofinal priority
desafiofinal.torder.customer_idcustomer
desafiofinal customer
idcustomer
desafiofinal torder
customer_idcustomer
desafiofinal customer
idcustomer
desafiofinal sales
product_idproduct
desafiofinal product
idproduct
desafiofinal torder
desafiofinal.sales.product_idproduct
desafiofinal product
idproduct
desafiofinal product
subCategory_idsubCategory
desafiofinal subcategory
idsubCategory
desafiofinal category
desafiofinal.subcategory.idsubCategory
desafiofinal subcategory
idsubCategory
output: {
    "joins": [
        {
            "from_table": "desafiofinal customer",
            "from_column": "city_idcity",
            "to_table": "desafiofinal city",
            "to_column": "idcity"
        },
        {
            "from_table": "desafiofinal city",
            "from_column": "state_idstate",
            "to_table": "desafiofinal state",
            "to_column": "idstate"
        },
        {
            "from_table": "desafiofinal state",
            "from_column": "country_idcountry",
            "to_table": "desafiofinal country",
            "to_column": "idcountry"
        },
        {
            "from_table": "desafiofinal priority",
            "from_column": "desafiofinal.torder.customer_idcustomer",
            "to_table": "desafiofinal customer",
            "to_column": "idcustomer"
        },
        {
            "from_table": "desafiofinal torder",
            "from_column": "customer_idcustomer",
            "to_table": "desafiofinal customer",
            "to_column": "idcustomer"
        },
        {
            "from_table": "desafiofinal sales",
            "from_column": "product_idproduct",
            "to_table": "desafiofinal product",
            "to_column": "idproduct"
        },
        {
            "from_table": "desafiofinal torder",
            "from_column": "desafiofinal.sales.product_idproduct",
            "to_table": "desafiofinal product",
            "to_column": "idproduct"
        },
        {
            "from_table": "desafiofinal product",
            "from_column": "subCategory_idsubCategory",
            "to_table": "desafiofinal subcategory",
            "to_column": "idsubCategory"
        },
        {
            "from_table": "desafiofinal category",
            "from_column": "desafiofinal.subcategory.idsubCategory",
            "to_table": "desafiofinal subcategory",
            "to_column": "idsubCategory"
        }
    ]
}