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        |
|-------------:|:----------------|
|            1 | Furniture       |
|            2 | Office Supplies |


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   |
|-------------:|:-----------|
|            1 | Medium     |
|            2 | High       |
|            3 | Low        |
|            4 | Critical   |


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 ratings
|   sk |   version | date_from               | date_to                 | orderid      |   rating |
|-----:|----------:|:------------------------|:------------------------|:-------------|---------:|
|    4 |         1 | 1900-01-01 00:00:00.000 | 2199-12-31 23:59:59.000 | AG-2011-2040 |        4 |
|    6 |         1 | 1900-01-01 00:00:00.000 | 2199-12-31 23:59:59.000 | AG-2011-380  |        4 |
|   10 |         1 | 1900-01-01 00:00:00.000 | 2199-12-31 23:59:59.000 | AG-2011-7230 |        4 |
|   11 |         1 | 1900-01-01 00:00:00.000 | 2199-12-31 23:59:59.000 | AG-2011-9060 |        4 |
|   19 |         1 | 1900-01-01 00:00:00.000 | 2199-12-31 23:59:59.000 | AG-2013-2970 |        4 |
|   21 |         1 | 1900-01-01 00:00:00.000 | 2199-12-31 23:59:59.000 | AG-2013-9570 |        4 |
|   22 |         1 | 1900-01-01 00:00:00.000 | 2199-12-31 23:59:59.000 | AG-2014-2110 |        4 |
|   26 |         1 | 1900-01-01 00:00:00.000 | 2199-12-31 23:59:59.000 | AG-2014-7430 |        4 |
|   28 |         1 | 1900-01-01 00:00:00.000 | 2199-12-31 23:59:59.000 | AG-2014-8710 |        4 |
|   35 |         1 | 1900-01-01 00:00:00.000 | 2199-12-31 23:59:59.000 | AL-2012-9980 |        4 |


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   |
|:-------------|:------------------------|----------------------:|:----------------------|
| AE-2011-9160 | 2011-10-03 00:00:00.000 |                     1 | PO-8865               |
| AE-2014-4120 | 2014-03-07 00:00:00.000 |                     1 | JD-5790               |
| AG-2011-1070 | 2011-01-10 00:00:00.000 |                     1 | TH-11235              |
| AG-2011-1390 | 2011-08-16 00:00:00.000 |                     1 | DH-3675               |
| AG-2011-1440 | 2011-12-15 00:00:00.000 |                     1 | DB-3210               |
| AG-2011-2040 | 2011-01-01 00:00:00.000 |                     1 | TB-11280              |
| AG-2011-3130 | 2011-03-11 00:00:00.000 |                     1 | AJ-780                |
| AG-2011-380  | 2011-03-03 00:00:00.000 |                     1 | CC-2370               |
| AG-2011-450  | 2011-11-07 00:00:00.000 |                     1 | BC-1125               |
| AG-2011-500  | 2011-11-23 00:00:00.000 |                     1 | AS-285                |


Table: rating
| orderid      |   rating |
|:-------------|---------:|
| AG-2011-2040 |        4 |
| AG-2011-380  |        4 |
| AG-2011-7230 |        4 |
| AG-2011-9060 |        4 |
| AG-2013-2970 |        4 |
| AG-2013-9570 |        4 |
| AG-2014-2110 |        4 |
| AG-2014-7430 |        4 |
| AG-2014-8710 |        4 |
| AL-2012-9980 |        4 |

 


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 ratings
data: desafiofinal ratings.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: rating
data: rating.csv
label: desafiofinal subcategory
category_idcategory
desafiofinal category
idcategory
desafiofinal customer
city_idcity
desafiofinal city
idcity
desafiofinal city
state_idstate
desafiofinal state
idstate
desafiofinal state
country_idcountry
desafiofinal country
idcountry
desafiofinal torder
customer_idcustomer
desafiofinal customer
idcustomer
desafiofinal torder
priority_idpriority
desafiofinal priority
idpriority
desafiofinal sales
order_idorder
desafiofinal torder
idorder
desafiofinal product
subCategory_idsubCategory
desafiofinal subcategory
idsubCategory
desafiofinal sales
product_idproduct
desafiofinal product
idproduct
desafiofinal torder
idorder
rating
orderid
output: {
    "joins": [
        {
            "from_table": "desafiofinal subcategory",
            "from_column": "category_idcategory",
            "to_table": "desafiofinal category",
            "to_column": "idcategory"
        },
        {
            "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 torder",
            "from_column": "customer_idcustomer",
            "to_table": "desafiofinal customer",
            "to_column": "idcustomer"
        },
        {
            "from_table": "desafiofinal torder",
            "from_column": "priority_idpriority",
            "to_table": "desafiofinal priority",
            "to_column": "idpriority"
        },
        {
            "from_table": "desafiofinal sales",
            "from_column": "order_idorder",
            "to_table": "desafiofinal torder",
            "to_column": "idorder"
        },
        {
            "from_table": "desafiofinal product",
            "from_column": "subCategory_idsubCategory",
            "to_table": "desafiofinal subcategory",
            "to_column": "idsubCategory"
        },
        {
            "from_table": "desafiofinal sales",
            "from_column": "product_idproduct",
            "to_table": "desafiofinal product",
            "to_column": "idproduct"
        },
        {
            "from_table": "desafiofinal torder",
            "from_column": "idorder",
            "to_table": "rating",
            "to_column": "orderid"
        }
    ]
}