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: CurrentIssues
| Tail Number   | State         |   Severity Score | Date                    | MessageSummary                                                | Type                |   Part reference | Details                                                                                                                                        |   Airplane part | Model reference   |
|:--------------|:--------------|-----------------:|:------------------------|:--------------------------------------------------------------|:--------------------|-----------------:|:-----------------------------------------------------------------------------------------------------------------------------------------------|----------------:|:------------------|
| 1AOFA         | Immediate     |                1 | 2017-01-01 00:00:00.000 | Significant engine second stage pressure anomaly.             | Anomaly             |             3840 | https://msit.powerbi.com/groups/me/reports/4bb3bd1d-51ad-4548-be20-75d0f138038c?InitialSlide=ReportSection&filter=Anomalies/AnomalyId eq '101' |              38 | 101               |
| 1AOFB         | Preventative  |                3 | 2017-01-01 00:00:00.000 | Fuel consumption increase predicted, engine wash recommended. | Recommendation      |             3620 | https://msit.powerbi.com/groups/me/reports/5aadf4c7-e1b9-4050-af26-142af050cd5e/ReportSection                                                  |              36 |                   |
| 1AOJA         | Required      |                2 | 2017-01-01 00:00:00.000 | Maintenance Message combination suggests significant issues.  | Maintenance message |             2784 | https://msit.powerbi.com/groups/me/reports/c42e7e53-951e-452a-ad3c-3a99add49b0c/ReportSection1                                                 |              27 |                   |
| 1AOJE         | Required      |                2 | 2017-01-01 00:00:00.000 | Bleed air anomaly prediction.                                 | ML model forecast   |             7390 | https://msit.powerbi.com/groups/me/reports/5aadf4c7-e1b9-4050-af26-142af050cd5e/ReportSection                                                  |              73 |                   |
| 1AOJF         | Preventative  |                3 | 2017-01-01 00:00:00.000 | Significant engine second stage pressure anomaly.             | Anomaly             |             3840 | https://msit.powerbi.com/groups/me/reports/4bb3bd1d-51ad-4548-be20-75d0f138038c?InitialSlide=ReportSection&filter=Anomalies/AnomalyId eq '102' |              38 | 102               |
| 1AOTD         | Preventative  |                3 | 2017-01-01 00:00:00.000 | Fuel consumption increase predicted, engine wash recommended. | Recommendation      |             3620 | https://msit.powerbi.com/groups/me/reports/5aadf4c7-e1b9-4050-af26-142af050cd5e/ReportSection                                                  |              36 |                   |
| 1ABKD         | Required      |                2 | 2017-01-01 00:00:00.000 | Maintenance Message combination suggests significant issues.  | Maintenance message |             2784 | https://msit.powerbi.com/groups/me/reports/c42e7e53-951e-452a-ad3c-3a99add49b0c/ReportSection1                                                 |              27 |                   |
| 1ABKE         | Preventative  |                3 | 2017-01-01 00:00:00.000 | Bleed air anomaly prediction.                                 | ML model forecast   |             7390 | https://msit.powerbi.com/groups/me/reports/5aadf4c7-e1b9-4050-af26-142af050cd5e/ReportSection                                                  |              79 |                   |
| 1ABKF         | InMaintenance |                4 | 2017-01-01 00:00:00.000 | Significant engine second stage pressure anomaly.             | Anomaly             |             3840 | https://msit.powerbi.com/groups/me/reports/4bb3bd1d-51ad-4548-be20-75d0f138038c?InitialSlide=ReportSection&filter=Anomalies/AnomalyId eq '101' |              57 | 101               |
| 1ABKG         | Immediate     |                1 | 2017-01-01 00:00:00.000 | Fuel consumption increase predicted, engine wash recommended. | Recommendation      |             3620 | https://msit.powerbi.com/groups/me/reports/5aadf4c7-e1b9-4050-af26-142af050cd5e/ReportSection                                                  |              89 |                   |


Table: EnginePositions
| Reg   | Make   |   Type |   EnginePos |
|:------|:-------|-------:|------------:|
| 1AOFA | Boeing |    787 |           1 |
| 1AOFB | Boeing |    787 |           2 |
| 1AOJA | Boeing |    787 |           3 |
| 1AOJE | Boeing |    787 |           4 |
| 1AOJF | Boeing |    787 |           5 |
| 1AOFA | Boeing |    787 |           6 |
| 1AOFB | Boeing |    787 |           7 |
| 1AOJA | Boeing |    787 |           8 |
| 1AOJE | Boeing |    787 |           9 |
| 1AOJF | Boeing |    787 |          10 |


Table: Fleet
| Reg   | Make   | Type   |   SubType |   Type code | Configuration   |   PAX | Airline   | MakeAndConfig       |   Status |   First |   Business |   Extra |   Economy |
|:------|:-------|:-------|----------:|------------:|:----------------|------:|:----------|:--------------------|---------:|--------:|-----------:|--------:|----------:|
| 1AOFA | Boeing | 787    |         8 |          13 | C25Y322         |   353 | Premium   | BoeingC21Y314       |        2 |       6 |         25 |       0 |       322 |
| 1AOFB | Boeing | 787    |         8 |          13 | C25Y322         |   353 | Premium   | BoeingC21Y314       |        2 |       6 |         25 |       0 |       322 |
| 1AOJA | Boeing | 787    |         9 |          14 | C25Y322         |   387 | Premium   | BoeingC35Y340       |        3 |       0 |         39 |       0 |       348 |
| 1AOJE | Boeing | 787    |         9 |          14 | C25Y322         |   387 | Premium   | BoeingC35Y340       |        1 |       0 |         39 |       0 |       348 |
| 1AOJF | Boeing | 787    |         9 |          14 | C25Y322         |   387 | Domestic  | BoeingC35Y340       |        1 |       0 |         39 |       0 |       348 |
| 1AOTD | Boeing | 787    |         9 |          14 | C25Y322         |   387 | Domestic  | BoeingC35Y340       |        3 |       0 |         39 |       0 |       348 |
| 1ABKD | Airbus | A380   |       841 |           6 | F14C64W36Y341   |   485 | Premium   | AirbusF12C60Y399    |        3 |      14 |         64 |       0 |       407 |
| 1ABKE | Airbus | A380   |       841 |           6 | F14C64W36Y341   |   485 | Premium   | AirbusF12C60Y399    |        3 |      14 |         64 |       0 |       407 |
| 1ABKF | Airbus | A380   |       841 |           5 | F14C64W36Y341   |   455 | Budget    | AirbusF12C60W36Y333 |        3 |      14 |         64 |      36 |       341 |
| 1ABKG | Airbus | A380   |       841 |           6 | F14C64W36Y341   |   485 | Budget    | AirbusF12C60Y399    |        2 |      14 |         64 |       0 |       407 |


Table: FlightSchedule
| AircraftRegistration   | Identifier                      | StartDate               | EndDate                 | Duration   | Origin   | Destination   | Next   | Activity    | Type        |    dLat |    dLong |
|:-----------------------|:--------------------------------|:------------------------|:------------------------|:-----------|:---------|:--------------|:-------|:------------|:------------|--------:|---------:|
| 1AOTD                  | SH101                           | 2017-01-01 22:08:00.000 | 2017-01-01 14:46:00.000 | 04:38      | JFK      | AKL           | 1      | InFlight    | InFlight    | 33.6407 | -84.4277 |
| 1AOTD                  | Maintenance flight preparations | 2017-01-01 14:46:00.000 | 2017-01-01 17:18:00.000 | 02:32      | AKL      | AKL           |        | OnGround    | OnGround    | 33.6407 | -84.4277 |
| 1AOFA                  | Maintenance                     | 2017-01-01 07:20:00.000 | 2017-01-01 14:28:00.000 | 07:08      | ATL      | ATL           | 1      | Maintenance | Maintenance | 41.9742 | -87.9075 |
| 1AOFA                  | PR125 flight preparations       | 2017-01-01 14:28:00.000 | 2017-01-01 15:14:00.000 | 00:46      | ATL      | ATL           |        | OnGround    | OnGround    | 41.9742 | -87.9075 |
| 1ABCK                  | PR125                           | 2017-01-01 14:30:00.000 | 2017-01-01 21:22:00.000 | 5:52       | OHR      | LHR           | 1      | InFlight    | InFlight    | 51.4699 |  -0.4545 |
| 1ABCK                  | PR135 flight preparations       | 2017-01-01 21:21:00.000 | 2017-01-01 22:34:00.000 | 1:12       | DXB      | DXB           |        | OnGround    | OnGround    | 51.4699 |  -0.4545 |
| 1ABRJ                  | PR136                           | 2017-01-01 20:51:00.000 | 2017-01-02 06:05:00.000 | 07:46      | LAX      | DXB           | 1      | InFlight    | InFlight    | 25.2529 |  55.3657 |
| 1ABRJ                  | PR280 flight preparations       | 2017-01-02 06:16:00.000 | 2017-01-02 09:15:00.000 | 02:59      | DXB      | DXB           |        | OnGround    | OnGround    | 25.2529 |  55.3657 |
| 1ABCK                  | PR125                           | 2017-01-01 22:34:00.000 | 2017-01-02 09:22:00.000 | 10:48      | DXB      | SYD           |        | InFlight    | InFlight    | 25.2529 |  55.3657 |
| 1ABCK                  | PR135 flight preparations       | 2017-01-02 09:22:00.000 | 2017-01-02 12:34:00.000 | 3:12       | SYD      | SYD           |        | OnGround    | OnGround    | 25.2529 |  55.3657 |


Table: Pareto
|   Airplane part |   Count of issue (last 90 days) |   Hours of delay (last 90 days) |
|----------------:|--------------------------------:|--------------------------------:|
|              27 |                              35 |                              12 |
|              32 |                              11 |                              22 |
|              38 |                              88 |                              14 |
|              73 |                               8 |                              61 |
|              36 |                              21 |                              10 |
|              81 |                              55 |                              31 |
|              98 |                              41 |                               3 |


Table: PartsErrors
| Engine part index       | Description                     |   Faults | Part name               |
|:------------------------|:--------------------------------|---------:|:------------------------|
| Air inlet               | Example Air inlet               |       39 | Air inlet               |
| Body                    | Example Body                    |       32 | Body                    |
| Fuel pump 1             | Example Fuel pump 1             |       20 | Fuel pump 1             |
| Fuel pump 2             | Example Fuel pump 2             |       56 | Fuel pump 2             |
| Fuelpump 3              | Example Fuelpump 3              |        6 | Fuelpump 3              |
| Cone                    | Example Cone                    |       65 | Cone                    |
| Low-pressure compressor | Example Low-pressure compressor |       17 | Low-pressure compressor |
| Bleed intake            | Example Bleed intake            |        3 | Bleed intake            |
| Diverter                | Example Diverter                |       29 | Diverter                |
| Fan                     | Example Fan                     |        7 | Fan                     |


Table: PMResults
|   Engine ID |   Cycle |   Remaining Useful Life |   EngineID |
|------------:|--------:|------------------------:|-----------:|
|           2 |       1 |                 195.441 |          2 |
|           3 |       1 |                 204.414 |          3 |
|           4 |       1 |                 189.139 |          4 |
|           5 |       1 |                 196.355 |          5 |
|           6 |       1 |                 186.107 |          6 |
|           7 |       1 |                 145.483 |          7 |
|           8 |       1 |                 188.215 |          8 |
|           9 |       1 |                 227.826 |          9 |
|          10 |       1 |                 222.019 |         10 |
|          11 |       1 |                 163.892 |         11 |


Table: RemainingUsefulLife
|   Engine ID |   Last Cycle |   Remaining Useful Life |   PlaneStatus |
|------------:|-------------:|------------------------:|--------------:|
|           1 |           96 |                138.537  |             3 |
|           2 |          103 |                109.397  |             2 |
|           3 |           81 |                118.475  |             3 |
|           4 |          105 |                 92.7924 |             1 |
|           5 |          111 |                 91.0476 |             1 |
|           6 |           90 |                103.698  |             2 |
|           7 |           74 |                167.173  |             4 |
|           8 |           75 |                131.412  |             3 |
|           9 |           67 |                125.445  |             3 |
|          10 |           84 |                140.549  |             3 |


Table: Sample
| Tail Number   | State         |   Severity Score | Icon URL                                             |
|:--------------|:--------------|-----------------:|:-----------------------------------------------------|
| 1AOFA         | Immediate     |                1 | https://storage.googleapis.com/powerbi/airplaneC.png |
| 1AOFB         | Preventative  |                3 | https://storage.googleapis.com/powerbi/airplaneC.png |
| 1AOJA         | Required      |                2 | https://storage.googleapis.com/powerbi/airplaneC.png |
| 1AOJE         | Required      |                2 | https://storage.googleapis.com/powerbi/airplaneC.png |
| 1AOJF         | Preventative  |                3 | https://storage.googleapis.com/powerbi/airplaneC.png |
| 1AOTD         | Preventative  |                3 | https://storage.googleapis.com/powerbi/airplaneC.png |
| 1ABKD         | Required      |                2 | https://storage.googleapis.com/powerbi/airplaneC.png |
| 1ABKE         | Preventative  |                3 | https://storage.googleapis.com/powerbi/airplaneC.png |
| 1ABKF         | InMaintenance |                4 | https://storage.googleapis.com/powerbi/airplaneC.png |
| 1ABKG         | Immediate     |                1 | https://storage.googleapis.com/powerbi/airplaneC.png |


Table: SanKey
| From     | To         |     Value | Airplane Tail   | Fleet    | Optimizer   | Recommendation   | TechKey                     | Column9   | 0.1   |
|:---------|:-----------|----------:|:----------------|:---------|:------------|:-----------------|:----------------------------|:----------|:------|
| Premium  | Efficiency | 0.135211  | 1AOFA           | Premium  | Fuel        | Efficiency       | FuelPremiumEfficiency1AOFA  |           |       |
| Premium  | Efficiency | 0.190098  | 1AOFB           | Premium  | Fuel        | Efficiency       | FuelPremiumEfficiency1AOFB  |           |       |
| Premium  | Efficiency | 0.19428   | 1AOJA           | Premium  | Fuel        | Efficiency       | FuelPremiumEfficiency1AOJA  |           |       |
| Premium  | Efficiency | 0.132547  | 1AOJE           | Premium  | Fuel        | Efficiency       | FuelPremiumEfficiency1AOJE  |           |       |
| Domestic | Efficiency | 0.300347  | 1AOJF           | Domestic | Fuel        | Efficiency       | FuelDomesticEfficiency1AOJF |           |       |
| Domestic | Efficiency | 0.284018  | 1AOTD           | Domestic | Fuel        | Efficiency       | FuelDomesticEfficiency1AOTD |           |       |
| Premium  | Efficiency | 0.0956718 | 1ABKD           | Premium  | Fuel        | Efficiency       | FuelPremiumEfficiency1ABKD  |           |       |
| Premium  | Efficiency | 0.0952035 | 1ABKE           | Premium  | Fuel        | Efficiency       | FuelPremiumEfficiency1ABKE  |           |       |
| Budget   | Efficiency | 0.968589  | 1ABKF           | Budget   | Fuel        | Efficiency       | FuelBudgetEfficiency1ABKF   |           |       |
| Budget   | Efficiency | 0.957241  | 1ABKG           | Budget   | Fuel        | Efficiency       | FuelBudgetEfficiency1ABKG   |           |       |


Table: TechKeys
| Recommendation   | Technique      | Fleet   | TYPE       | Airplane Tail   | Strategy   | ENGINE TYPE   |     Score | Optimizer   | Optimizer-Description                                |   Optimizer-Weight |   Score Weighted |   Copy | JFK                           |
|:-----------------|:---------------|:--------|:-----------|:----------------|:-----------|:--------------|----------:|:------------|:-----------------------------------------------------|-------------------:|-----------------:|-------:|:------------------------------|
| Availability     | Add Aircraft   | Premium | B777-312ER | 1ABKH           | Lease      | GE90-115B     | 0.0680588 | Lease       | Manage Leaser Return Options (Target Life at Return) |                0.5 |        0.0340294 |      1 | LeasePremiumAvailability1ABKH |
| Availability     | Larger Planes  | Premium | B777-312ER | 1ABNA           | Lease      | GE90-115B     | 0.148968  | Lease       | Manage Leaser Return Options (Target Life at Return) |                0.5 |        0.0744842 |      1 | LeasePremiumAvailability1ABNA |
| Availability     | Smaller Planes | Premium | B777-312ER | 1ABNB           | Lease      | GE90-115B     | 0.521136  | Lease       | Manage Leaser Return Options (Target Life at Return) |                0.5 |        0.260568  |      1 | LeasePremiumAvailability1ABNB |
| Availability     | Add Aircraft   | Premium | B777-312ER | 1ABDF           | Lease      | GE90-115B     | 0.6899    | Lease       | Manage Leaser Return Options (Target Life at Return) |                0.5 |        0.34495   |      1 | LeasePremiumAvailability1ABDF |
| Availability     | Larger Planes  | Premium | B777-312ER | 1ABCI           | Lease      | GE90-115B     | 0.53834   | Lease       | Manage Leaser Return Options (Target Life at Return) |                0.5 |        0.26917   |      1 | LeasePremiumAvailability1ABCI |
| Availability     | Smaller Planes | Premium | B777-312ER | 1ABCJ           | Lease      | GE90-115B     | 0.222315  | Lease       | Manage Leaser Return Options (Target Life at Return) |                0.5 |        0.111157  |      1 | LeasePremiumAvailability1ABCJ |
| Availability     | Add Aircraft   | Premium | B777-312ER | 1ABAN           | Lease      | GE90-115B     | 0.174715  | Lease       | Manage Leaser Return Options (Target Life at Return) |                0.5 |        0.0873577 |      1 | LeasePremiumAvailability1ABAN |
| Availability     | Larger Planes  | Premium | B777-312ER | 1ABAO           | Lease      | GE90-115B     | 0.934158  | Lease       | Manage Leaser Return Options (Target Life at Return) |                0.5 |        0.467079  |      1 | LeasePremiumAvailability1ABAO |
| Availability     | Smaller Planes | Premium | B777-312ER | 1ABDA           | Lease      | GE90-115B     | 0.712219  | Lease       | Manage Leaser Return Options (Target Life at Return) |                0.5 |        0.356109  |      1 | LeasePremiumAvailability1ABDA |
| Availability     | Add Aircraft   | Premium | B777-312ER | 1ABAM           | Lease      | GE90-115B     | 0.14932   | Lease       | Manage Leaser Return Options (Target Life at Return) |                0.5 |        0.0746602 |      1 | LeasePremiumAvailability1ABAM |

 


tables: name: CurrentIssues
data: CurrentIssues.csv
name: EnginePositions
data: EnginePositions.csv
name: Fleet
data: Fleet.csv
name: FlightSchedule
data: FlightSchedule.csv
name: Pareto
data: Pareto.csv
name: PartsErrors
data: PartsErrors.csv
name: PMResults
data: PMResults.csv
name: RemainingUsefulLife
data: RemainingUsefulLife.csv
name: Sample
data: Sample.csv
name: SanKey
data: SanKey.csv
name: TechKeys
data: TechKeys.csv
label: CurrentIssues
Airplane part
Pareto
Airplane part
EnginePositions
Reg
Fleet
Reg
CurrentIssues
Tail Number
Fleet
Reg
FlightSchedule
AircraftRegistration
Fleet
Reg
PMResults
Engine ID
EnginePositions
EnginePos
TechKeys
JFK
SanKey
TechKey
PMResults
Engine ID
RemainingUsefulLife
Engine ID
Sample
Tail Number
Fleet
Reg
output: {
    "joins": [
        {
            "from_table": "CurrentIssues",
            "from_column": "Airplane part",
            "to_table": "Pareto",
            "to_column": "Airplane part"
        },
        {
            "from_table": "EnginePositions",
            "from_column": "Reg",
            "to_table": "Fleet",
            "to_column": "Reg"
        },
        {
            "from_table": "CurrentIssues",
            "from_column": "Tail Number",
            "to_table": "Fleet",
            "to_column": "Reg"
        },
        {
            "from_table": "FlightSchedule",
            "from_column": "AircraftRegistration",
            "to_table": "Fleet",
            "to_column": "Reg"
        },
        {
            "from_table": "PMResults",
            "from_column": "Engine ID",
            "to_table": "EnginePositions",
            "to_column": "EnginePos"
        },
        {
            "from_table": "TechKeys",
            "from_column": "JFK",
            "to_table": "SanKey",
            "to_column": "TechKey"
        },
        {
            "from_table": "PMResults",
            "from_column": "Engine ID",
            "to_table": "RemainingUsefulLife",
            "to_column": "Engine ID"
        },
        {
            "from_table": "Sample",
            "from_column": "Tail Number",
            "to_table": "Fleet",
            "to_column": "Reg"
        }
    ]
}