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: Habitat
|   HabitatId | HabitatName   |   EnvironmentId | BackColour   | ForeColour   |
|------------:|:--------------|----------------:|:-------------|:-------------|
|           1 | Grasslands    |               1 | Light green  | Black        |
|           2 | Forest        |               1 | Dark green   | White        |
|           3 | Fresh water   |               3 | LightBlue    | Dark blue    |
|           4 | Salt water    |               3 | #78aaf5      | White        |
|           5 | Desert        |               1 | #d6a740      | Black        |
|           6 | Urban         |               1 | #222         | White        |
|           7 | Sky           |               2 | #0a66f0      | White        |


Table: Mode
|   EnvironmentId | EnvironmentName   |
|----------------:|:------------------|
|               1 | Land              |
|               2 | Air               |
|               3 | Water             |


Table: POS Type
|   CentreTypeId | CentreTypeName   |
|---------------:|:-----------------|
|              1 | Factory Outlet   |
|              2 | Retail Park      |
|              3 | Shopping Centre  |
|              4 | Shopping Park    |


Table: POS
|   CentreId | CentreName                   |   TownId |   CentreTypeId | PostCode   |   SquareMetres |   NumberUnits |
|-----------:|:-----------------------------|---------:|---------------:|:-----------|---------------:|--------------:|
|          1 | Pavilion Shopping Centre     |      180 |              3 | EN8 7BZ    |          15143 |            62 |
|          2 | Times Square Shopping Centre |      170 |              3 | SM1 1LF    |          15143 |            71 |
|          4 | Norman Park                  |        9 |              3 | CB3 8EL    |          15228 |            11 |
|          7 | Cannon Park Shopping Centre  |       48 |              3 | CV4 7EH    |          15329 |            36 |
|         12 | Kingsmead Shopping Centre    |       69 |              3 | GU14 7SJ   |          15515 |            69 |
|         13 | Market Quay                  |       68 |              3 | PO16       |          15515 |             0 |
|         19 | Old George Mall              |      148 |              3 | SP1 2NH    |          15794 |            37 |
|         20 | The Nicholson's Centre       |      113 |              3 | SL6 1LB    |          15794 |            64 |
|         25 | The Longford Shopping Centre |       70 |              3 | TW13 4BH   |          15979 |            85 |
|         26 | Southgate Shopping Centre    |       15 |              3 | BA1 1TQ    |          16072 |            34 |


Table: Product
|   ProductId | ProductName   | Animal    |   HabitatId |   Legs |   FamilyId |   WeightGrams |   ProductionCost | Product Species   |
|------------:|:--------------|:----------|------------:|-------:|-----------:|--------------:|-----------------:|:------------------|
|           1 | Sammy         | Snake     |           1 |      0 |          1 |           950 |             7.19 | Reptile           |
|           2 | Pokyo         | Penguin   |           4 |      2 |          3 |           850 |             4.5  | Bird              |
|           3 | Fenella       | Frog      |           3 |      4 |          4 |           400 |            10.79 | Amphibian         |
|           4 | Layla         | Lemur     |           2 |      2 |          5 |           550 |             4.28 | Mammal            |
|           5 | Dave          | Dachsund  |           1 |      4 |          5 |           775 |             5.85 | Mammal            |
|           6 | Kylie         | Camel     |           5 |      4 |          5 |          1200 |             3.15 | Mammal            |
|           7 | Jeremy        | Jackdaw   |           7 |      2 |          3 |           295 |             7.65 | Bird              |
|           8 | Faye          | Fox       |           6 |      4 |          5 |           420 |             4.95 | Mammal            |
|           9 | Oliver        | Owl       |           7 |      2 |          3 |           380 |             6.75 | Bird              |
|          10 | Cleopatra     | Clownfish |           4 |      0 |          2 |           290 |             2.69 | Fish              |


Table: Purchase
|   PurchaseId | PurchaseDate            | PurchaseDateTime        |   ProductId |   CentreId |   Quantity |   Price | PaymentDate   |   StaffId | Price Bracket   |
|-------------:|:------------------------|:------------------------|------------:|-----------:|-----------:|--------:|:--------------|----------:|:----------------|
|        15763 | 2017-01-04 00:00:00.000 | 2017-01-04 14:54:00.000 |           1 |        252 |          2 |     8.5 | NULL          |      4644 | Affordable      |
|        16093 | 2017-01-07 00:00:00.000 | 2017-01-07 12:44:00.000 |           1 |        341 |          2 |     8.5 | NULL          |      5573 | Affordable      |
|        17158 | 2017-01-13 00:00:00.000 | 2017-01-13 18:05:00.000 |           1 |        360 |          2 |     8.5 | NULL          |      5182 | Affordable      |
|        17637 | 2017-01-17 00:00:00.000 | 2017-01-17 15:40:00.000 |           1 |        309 |          2 |     8.5 | NULL          |      4922 | Affordable      |
|        17851 | 2017-01-19 00:00:00.000 | 2017-01-19 12:29:00.000 |           1 |        270 |          2 |     8.5 | NULL          |      4719 | Affordable      |
|        18325 | 2017-01-23 00:00:00.000 | 2017-01-23 16:58:00.000 |           1 |        326 |          2 |     8.5 | NULL          |      4991 | Affordable      |
|        18927 | 2017-01-29 00:00:00.000 | 2017-01-29 10:26:00.000 |           1 |        331 |          2 |     8.5 | NULL          |      5027 | Affordable      |
|        19027 | 2017-01-30 00:00:00.000 | 2017-01-30 18:24:00.000 |           1 |        383 |          2 |     8.5 | NULL          |      5296 | Affordable      |
|        19052 | 2017-01-31 00:00:00.000 | 2017-01-31 16:34:00.000 |           1 |        255 |          2 |     8.5 | NULL          |      4664 | Affordable      |
|        19271 | 2017-02-01 00:00:00.000 | 2017-02-01 16:55:00.000 |           1 |        375 |          2 |     8.5 | NULL          |      5380 | Affordable      |


Table: Region
|   RegionId | RegionName             |
|-----------:|:-----------------------|
|          1 | East Anglia            |
|          2 | East Midlands          |
|          3 | London                 |
|          4 | North                  |
|          5 | North West             |
|          6 | South East             |
|          7 | South West             |
|          8 | West Midlands          |
|          9 | Yorkshire & Humberside |


Table: Species
|   FamilyId | FamilyName   |
|-----------:|:-------------|
|          1 | Reptile      |
|          2 | Fish         |
|          3 | Bird         |
|          4 | Amphibian    |
|          5 | Mammal       |
|          6 | Insect       |


Table: Town
|   TownId | TownName          |   RegionId | Region Type   |
|---------:|:------------------|-----------:|:--------------|
|        1 | Aintree           |          5 | North West    |
|        2 | Aldershot         |          6 | South East    |
|        3 | Altrincham        |          5 | North West    |
|        4 | Andover           |          6 | South East    |
|        5 | Ashford           |          6 | South East    |
|        6 | Ashton Under Lyne |          5 | North West    |
|        7 | Aylesbury         |          6 | South East    |
|        8 | Banbury           |          6 | South East    |
|        9 | Barhill           |          1 | East Anglia   |
|       10 | Barking           |          3 | London        |

 


tables: name: Habitat
data: Habitat.csv
name: Mode
data: Mode.csv
name: POS Type
data: POS Type.csv
name: POS
data: POS.csv
name: Product
data: Product.csv
name: Purchase
data: Purchase.csv
name: Region
data: Region.csv
name: Species
data: Species.csv
name: Town
data: Town.csv
label: Product
HabitatId
Habitat
HabitatId
Habitat
EnvironmentId
Mode
EnvironmentId
Purchase
CentreId
POS
CentreId
POS
TownId
Town
TownId
POS
CentreTypeId
POS Type
CentreTypeId
Purchase
ProductId
Product
ProductId
Product
FamilyId
Species
FamilyId
Town
RegionId
Region
RegionId
output: {
    "joins": [
        {
            "from_table": "Product",
            "from_column": "HabitatId",
            "to_table": "Habitat",
            "to_column": "HabitatId"
        },
        {
            "from_table": "Habitat",
            "from_column": "EnvironmentId",
            "to_table": "Mode",
            "to_column": "EnvironmentId"
        },
        {
            "from_table": "Purchase",
            "from_column": "CentreId",
            "to_table": "POS",
            "to_column": "CentreId"
        },
        {
            "from_table": "POS",
            "from_column": "TownId",
            "to_table": "Town",
            "to_column": "TownId"
        },
        {
            "from_table": "POS",
            "from_column": "CentreTypeId",
            "to_table": "POS Type",
            "to_column": "CentreTypeId"
        },
        {
            "from_table": "Purchase",
            "from_column": "ProductId",
            "to_table": "Product",
            "to_column": "ProductId"
        },
        {
            "from_table": "Product",
            "from_column": "FamilyId",
            "to_table": "Species",
            "to_column": "FamilyId"
        },
        {
            "from_table": "Town",
            "from_column": "RegionId",
            "to_table": "Region",
            "to_column": "RegionId"
        }
    ]
}