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: Apellido, Nombre
|   personal_ID | Apellido, Nombre    | eMail                         | Telefono       |
|--------------:|:--------------------|:------------------------------|:---------------|
|             1 | Jackson, Fabiola    | fabiola.jackson@bikes.shop    | (831) 555-5554 |
|             2 | Copeland, Mireya    | mireya.copeland@bikes.shop    | (831) 555-5555 |
|             3 | Serrano, Genna      | genna.serrano@bikes.shop      | (831) 555-5556 |
|             4 | Wiggins, Virgie     | virgie.wiggins@bikes.shop     | (831) 555-5557 |
|             5 | David, Jannette     | jannette.david@bikes.shop     | (516) 379-4444 |
|             6 | Boyer, Marcelene    | marcelene.boyer@bikes.shop    | (516) 379-4445 |
|             7 | Daniel, Venita      | venita.daniel@bikes.shop      | (516) 379-4446 |
|             8 | Vargas, Kali        | kali.vargas@bikes.shop        | (972) 530-5555 |
|             9 | Terrell, Layla      | layla.terrell@bikes.shop      | (972) 530-5556 |
|            10 | Houston, Bernardine | bernardine.houston@bikes.shop | (972) 530-5557 |


Table: Categoria
|   categoria_ID | categoria_nombre    |
|---------------:|:--------------------|
|              1 | Children Bicycles   |
|              2 | Comfort Bicycles    |
|              3 | Cruisers Bicycles   |
|              4 | Cyclocross Bicycles |
|              5 | Electric Bikes      |
|              6 | Mountain Bikes      |
|              7 | Road Bikes          |


Table: Horario
|   tienda_ID | Horario       |
|------------:|:--------------|
|           1 | 08:00 a 21:00 |
|           2 | 09:00 a 20:00 |
|           3 | 08:30 a 19:30 |


Table: Importando Tienda
|   tienda_ID | tienda_nombre    | telefono_tienda   | email_tienda         | calle_tienda        | ciudad_tienda   | estado_tienda   |   codigopostal_tienda |   Metros Superficie | Horario       | Taller   |
|------------:|:-----------------|:------------------|:---------------------|:--------------------|:----------------|:----------------|----------------------:|--------------------:|:--------------|:---------|
|           1 | Santa Cruz Bikes | (831) 476-4321    | santacruz@bikes.shop | 3700 Portola Drive  | Santa Cruz      | CA              |                 95060 |                 550 | 08:00 a 21:00 | No       |
|           2 | Baldwin Bikes    | (516) 379-8888    | baldwin@bikes.shop   | 4200 Chestnut Lane  | Baldwin         | NY              |                 11432 |                 950 | 09:00 a 20:00 | Sí       |
|           3 | Rowlett Bikes    | (972) 530-5555    | rowlett@bikes.shop   | 8000 Fairway Avenue | Rowlett         | TX              |                 75088 |                 390 | 08:30 a 19:30 | No       |


Table: Marca
|   marca_ID | marca_nombre   |
|-----------:|:---------------|
|          1 | Electra        |
|          2 | Haro           |
|          3 | Heller         |
|          4 | Pure Cycles    |
|          5 | Ritchey        |
|          6 | Strider        |
|          7 | Sun Bicycles   |
|          8 | Surly          |
|          9 | Trek           |


Table: Metros Superfiecie
|   tienda_ID |   Metros Superficie |
|------------:|--------------------:|
|           1 |                 550 |
|           2 |                 950 |
|           3 |                 390 |


Table: Nombre, Apellido
|   personal_ID | Nombre, Apellido    | eMail                         | Telefono       |
|--------------:|:--------------------|:------------------------------|:---------------|
|             1 | Fabiola, Jackson    | fabiola.jackson@bikes.shop    | (831) 555-5554 |
|             2 | Mireya, Copeland    | mireya.copeland@bikes.shop    | (831) 555-5555 |
|             3 | Genna, Serrano      | genna.serrano@bikes.shop      | (831) 555-5556 |
|             4 | Virgie, Wiggins     | virgie.wiggins@bikes.shop     | (831) 555-5557 |
|             5 | Jannette, David     | jannette.david@bikes.shop     | (516) 379-4444 |
|             6 | Marcelene, Boyer    | marcelene.boyer@bikes.shop    | (516) 379-4445 |
|             7 | Venita, Daniel      | venita.daniel@bikes.shop      | (516) 379-4446 |
|             8 | Kali, Vargas        | kali.vargas@bikes.shop        | (972) 530-5555 |
|             9 | Layla, Terrell      | layla.terrell@bikes.shop      | (972) 530-5556 |
|            10 | Bernardine, Houston | bernardine.houston@bikes.shop | (972) 530-5557 |


Table: Productos no vendidos en 2018
|   producto_ID.1 | producto_nombre                       |
|----------------:|:--------------------------------------|
|               1 | Trek 820 - 2016                       |
|              16 | Electra Townie Original 7D EQ - 2016  |
|              30 | Surly Ice Cream Truck Frameset - 2017 |
|              73 | Sun Bicycles Brickell Tandem 7 - 2017 |
|              74 | Electra Cruiser Lux 1 - 2017          |
|              83 | Trek Boy's Kickster - 2015/2017       |
|              94 | Haro Shredder Pro 20 - 2017           |
|             121 | Surly Krampus Frameset - 2018         |
|             125 | Trek Kids' Dual Sport - 2018          |
|             154 | Trek Domane SLR 6 Disc Women's - 2018 |


Table: Productos
|   producto_ID | producto_nombre                    |   marca_ID | marca_nombre   |   categoria_ID | categoria_nombre   |   modelo_año |   precio_lista |
|--------------:|:-----------------------------------|-----------:|:---------------|---------------:|:-------------------|-------------:|---------------:|
|           112 | Trek 820 - 2018                    |          9 | Trek           |              6 | Mountain Bikes     |         2018 |         379.99 |
|           113 | Trek Marlin 5 - 2018               |          9 | Trek           |              6 | Mountain Bikes     |         2018 |         489.99 |
|           114 | Trek Marlin 6 - 2018               |          9 | Trek           |              6 | Mountain Bikes     |         2018 |         579.99 |
|           115 | Trek Fuel EX 8 29 - 2018           |          9 | Trek           |              6 | Mountain Bikes     |         2018 |        3199.99 |
|           116 | Trek Marlin 7 - 2017/2018          |          9 | Trek           |              6 | Mountain Bikes     |         2018 |         749.99 |
|           117 | Trek Ticket S Frame - 2018         |          9 | Trek           |              6 | Mountain Bikes     |         2018 |        1469.99 |
|           118 | Trek X-Caliber 8 - 2018            |          9 | Trek           |              6 | Mountain Bikes     |         2018 |         999.99 |
|           119 | Trek Kids' Neko - 2018             |          9 | Trek           |              6 | Mountain Bikes     |         2018 |         469.99 |
|           120 | Trek Fuel EX 7 29 - 2018           |          9 | Trek           |              6 | Mountain Bikes     |         2018 |        2499.99 |
|           123 | Trek Farley Carbon Frameset - 2018 |          9 | Trek           |              6 | Mountain Bikes     |         2018 |         999.99 |


Table: resumen de vta 2018
| Source.Name     |   venta_ID |   cliente_ID |   estado_pedido | fecha_pedido            | fecha_requerido         | fecha_envio   |   tienda_ID |   personal_ID |   item_ID |   producto_ID |   cantidad |   precio_venta | Sub-Total Venta   |   descuento | Venta Total Final   |
|:----------------|-----------:|-------------:|----------------:|:------------------------|:------------------------|:--------------|------------:|--------------:|----------:|--------------:|-----------:|---------------:|:------------------|------------:|:--------------------|
| Ventas2018.xlsx |       1479 |           23 |               2 | 2018-04-01 00:00:00.000 | 2018-04-01 00:00:00.000 |               |           2 |             7 |         1 |           252 |          1 |        2999.99 | 2999,99           |        0.05 | 2849,9905           |
| Ventas2018.xlsx |       1479 |           23 |               2 | 2018-04-01 00:00:00.000 | 2018-04-01 00:00:00.000 |               |           2 |             7 |         2 |           256 |          1 |         899.99 | 899,99            |        0.1  | 809,991             |
| Ventas2018.xlsx |       1480 |           27 |               2 | 2018-04-01 00:00:00.000 | 2018-04-01 00:00:00.000 |               |           2 |             7 |         1 |           308 |          2 |         749.99 | 1499,98           |        0.1  | 1349,982            |
| Ventas2018.xlsx |       1480 |           27 |               2 | 2018-04-01 00:00:00.000 | 2018-04-01 00:00:00.000 |               |           2 |             7 |         2 |            82 |          2 |         659.99 | 1319,98           |        0.1  | 1187,982            |
| Ventas2018.xlsx |       1480 |           27 |               2 | 2018-04-01 00:00:00.000 | 2018-04-01 00:00:00.000 |               |           2 |             7 |         3 |            92 |          2 |         209.99 | 419,98            |        0.07 | 390,5814            |
| Ventas2018.xlsx |       1480 |           27 |               2 | 2018-04-01 00:00:00.000 | 2018-04-01 00:00:00.000 |               |           2 |             7 |         4 |           225 |          2 |         959.99 | 1919,98           |        0.07 | 1785,5814           |
| Ventas2018.xlsx |       1482 |           94 |               1 | 2018-04-01 00:00:00.000 | 2018-04-01 00:00:00.000 |               |           2 |             7 |         1 |            48 |          1 |        1499.99 | 1499,99           |        0.1  | 1349,991            |
| Ventas2018.xlsx |       1482 |           94 |               1 | 2018-04-01 00:00:00.000 | 2018-04-01 00:00:00.000 |               |           2 |             7 |         2 |           113 |          1 |         489.99 | 489,99            |        0.05 | 465,4905            |
| Ventas2018.xlsx |       1482 |           94 |               1 | 2018-04-01 00:00:00.000 | 2018-04-01 00:00:00.000 |               |           2 |             7 |         3 |            58 |          2 |        4999.99 | 9999,98           |        0.07 | 9299,9814           |
| Ventas2018.xlsx |       1482 |           94 |               1 | 2018-04-01 00:00:00.000 | 2018-04-01 00:00:00.000 |               |           2 |             7 |         4 |            40 |          1 |        4999.99 | 4999,99           |        0.05 | 4749,9905           |


Table: resumen por tienda
| tienda_nombre    | categoria_nombre    |   resumen por tienda y categoria |
|:-----------------|:--------------------|---------------------------------:|
| Baldwin Bikes    | Cruisers Bicycles   |                        151981    |
| Baldwin Bikes    | Mountain Bikes      |                        269915    |
| Rowlett Bikes    | Mountain Bikes      |                         39150.6  |
| Santa Cruz Bikes | Mountain Bikes      |                         95789.6  |
| Rowlett Bikes    | Cruisers Bicycles   |                         21790.9  |
| Santa Cruz Bikes | Cruisers Bicycles   |                         59571.1  |
| Rowlett Bikes    | Cyclocross Bicycles |                          7089.18 |
| Santa Cruz Bikes | Electric Bikes      |                        104311    |
| Rowlett Bikes    | Electric Bikes      |                         50667.9  |
| Rowlett Bikes    | Children Bicycles   |                          5035.81 |


Table: Taller
|   tienda_ID | Taller   |
|------------:|:---------|
|           1 | No       |
|           2 | Sí       |
|           3 | No       |


Table: Ventas
| Source.Name     |   venta_ID |   cliente_ID |   estado_pedido | fecha_pedido            | fecha_requerido         | fecha_envio             |   tienda_ID |   personal_ID |   item_ID |   producto_ID |   cantidad |   precio_venta |   descuento |
|:----------------|-----------:|-------------:|----------------:|:------------------------|:------------------------|:------------------------|------------:|--------------:|----------:|--------------:|-----------:|---------------:|------------:|
| Ventas2017.xlsx |        637 |          512 |               4 | 2017-01-03 00:00:00.000 | 2017-01-05 00:00:00.000 | 2017-01-05 00:00:00.000 |           2 |             6 |         1 |             9 |          1 |        2999.99 |        0.1  |
| Ventas2017.xlsx |        639 |          550 |               4 | 2017-01-06 00:00:00.000 | 2017-01-07 00:00:00.000 | 2017-01-07 00:00:00.000 |           2 |             6 |         1 |            15 |          1 |         529.99 |        0.07 |
| Ventas2017.xlsx |        644 |          439 |               4 | 2017-01-08 00:00:00.000 | 2017-01-11 00:00:00.000 | 2017-01-10 00:00:00.000 |           2 |             6 |         1 |             9 |          1 |        2999.99 |        0.07 |
| Ventas2017.xlsx |        645 |         1199 |               4 | 2017-01-08 00:00:00.000 | 2017-01-11 00:00:00.000 | 2017-01-10 00:00:00.000 |           2 |             6 |         1 |            99 |          1 |         299.99 |        0.07 |
| Ventas2017.xlsx |        648 |          684 |               4 | 2017-01-09 00:00:00.000 | 2017-01-11 00:00:00.000 | 2017-01-11 00:00:00.000 |           2 |             6 |         1 |            24 |          1 |         549.99 |        0.07 |
| Ventas2017.xlsx |        665 |           29 |               4 | 2017-01-21 00:00:00.000 | 2017-01-22 00:00:00.000 | 2017-01-23 00:00:00.000 |           2 |             6 |         1 |            58 |          1 |        4999.99 |        0.07 |
| Ventas2017.xlsx |        670 |          361 |               4 | 2017-01-24 00:00:00.000 | 2017-01-25 00:00:00.000 | 2017-01-25 00:00:00.000 |           2 |             6 |         1 |           108 |          1 |         449.99 |        0.1  |
| Ventas2017.xlsx |        682 |          614 |               4 | 2017-01-29 00:00:00.000 | 2017-01-30 00:00:00.000 | 2017-01-30 00:00:00.000 |           2 |             6 |         1 |            39 |          1 |        1499.99 |        0.2  |
| Ventas2017.xlsx |        684 |          762 |               4 | 2017-01-31 00:00:00.000 | 2017-02-01 00:00:00.000 | 2017-02-02 00:00:00.000 |           2 |             6 |         1 |            56 |          1 |        5499.99 |        0.07 |
| Ventas2017.xlsx |        710 |          295 |               4 | 2017-02-13 00:00:00.000 | 2017-02-15 00:00:00.000 | 2017-02-16 00:00:00.000 |           2 |             6 |         1 |            42 |          1 |        2299.99 |        0.05 |

 


tables: name: Apellido, Nombre
data: Apellido, Nombre.csv
name: Categoria
data: Categoria.csv
name: Horario
data: Horario.csv
name: Importando Tienda
data: Importando Tienda.csv
name: Marca
data: Marca.csv
name: Metros Superfiecie
data: Metros Superfiecie.csv
name: Nombre, Apellido
data: Nombre, Apellido.csv
name: Productos no vendidos en 2018
data: Productos no vendidos en 2018.csv
name: Productos
data: Productos.csv
name: resumen de vta 2018
data: resumen de vta 2018.csv
name: resumen por tienda
data: resumen por tienda.csv
name: Taller
data: Taller.csv
name: Ventas
data: Ventas.csv
label: Nombre, Apellido
personal_ID
Apellido, Nombre
personal_ID
Productos
marca_ID
Marca
marca_ID
Productos
categoria_ID
Categoria
categoria_ID
Horario
tienda_ID
Importando Tienda
tienda_ID
Taller
tienda_ID
Importando Tienda
tienda_ID
Metros Superfiecie
Metros Superficie
Importando Tienda
Metros Superficie
Ventas
personal_ID
Apellido, Nombre
personal_ID
Ventas
producto_ID
Productos
producto_ID
Ventas
tienda_ID
Metros Superfiecie
tienda_ID
resumen de vta 2018
personal_ID
Apellido, Nombre
personal_ID
resumen de vta 2018
producto_ID
Productos
producto_ID
resumen por tienda
categoria_nombre
Categoria
categoria_nombre
resumen de vta 2018
tienda_ID
Importando Tienda
tienda_ID
resumen por tienda
tienda_nombre
Importando Tienda
tienda_nombre
output: {
    "joins": [
        {
            "from_table": "Nombre, Apellido",
            "from_column": "personal_ID",
            "to_table": "Apellido, Nombre",
            "to_column": "personal_ID"
        },
        {
            "from_table": "Productos",
            "from_column": "marca_ID",
            "to_table": "Marca",
            "to_column": "marca_ID"
        },
        {
            "from_table": "Productos",
            "from_column": "categoria_ID",
            "to_table": "Categoria",
            "to_column": "categoria_ID"
        },
        {
            "from_table": "Horario",
            "from_column": "tienda_ID",
            "to_table": "Importando Tienda",
            "to_column": "tienda_ID"
        },
        {
            "from_table": "Taller",
            "from_column": "tienda_ID",
            "to_table": "Importando Tienda",
            "to_column": "tienda_ID"
        },
        {
            "from_table": "Metros Superfiecie",
            "from_column": "Metros Superficie",
            "to_table": "Importando Tienda",
            "to_column": "Metros Superficie"
        },
        {
            "from_table": "Ventas",
            "from_column": "personal_ID",
            "to_table": "Apellido, Nombre",
            "to_column": "personal_ID"
        },
        {
            "from_table": "Ventas",
            "from_column": "producto_ID",
            "to_table": "Productos",
            "to_column": "producto_ID"
        },
        {
            "from_table": "Ventas",
            "from_column": "tienda_ID",
            "to_table": "Metros Superfiecie",
            "to_column": "tienda_ID"
        },
        {
            "from_table": "resumen de vta 2018",
            "from_column": "personal_ID",
            "to_table": "Apellido, Nombre",
            "to_column": "personal_ID"
        },
        {
            "from_table": "resumen de vta 2018",
            "from_column": "producto_ID",
            "to_table": "Productos",
            "to_column": "producto_ID"
        },
        {
            "from_table": "resumen por tienda",
            "from_column": "categoria_nombre",
            "to_table": "Categoria",
            "to_column": "categoria_nombre"
        },
        {
            "from_table": "resumen de vta 2018",
            "from_column": "tienda_ID",
            "to_table": "Importando Tienda",
            "to_column": "tienda_ID"
        },
        {
            "from_table": "resumen por tienda",
            "from_column": "tienda_nombre",
            "to_table": "Importando Tienda",
            "to_column": "tienda_nombre"
        }
    ]
}