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: Account
|   Account | Description                               | Cat1                                            | Cat2                                            | Cat8   | RACP&L                                     |
|----------:|:------------------------------------------|:------------------------------------------------|:------------------------------------------------|:-------|:-------------------------------------------|
|      5000 | Income - Daily Care Fees                  | Cat1-Care Client Income-Client Fees             | Cat2-Care Client Income-Client Fees             |        | Care Client Income-Client Fees             |
|      5010 | Income - Hardship Fee Reduction           | Cat1-Care Client Income-Client Fees             | Cat2-Care Client Income-Client Fees             |        | Care Client Income-Client Fees             |
|      5020 | Income - Income Tested Fees               | Cat1-Care Client Income-Income Tested Fee       | Cat2-Care Client Income-Income Tested Fee       |        | Care Client Income-Income Tested Fee       |
|      5025 | Income - Means Tested Fees                | Cat1-Care Client Income-Means tested fees       | Cat2-Care Client Income-Means tested fees       |        | Care Client Income-Means tested fees       |
|      5030 | Income - Accommodation Charge             | Cat1-Accommodation Income-Accommodation charges | Cat2-Accommodation Income-Accommodation charges |        | Accommodation Income-Accommodation charges |
|      5035 | Income - Daily Accommodation Contribution | Cat1-Daily Accommodation Contribution           | Cat2-Daily Accommodation Contribution           |        | Daily Accommodation Contribution           |
|      5040 | Income - Other Agreed Amounts             | Cat1-Accommodation Income-Sundry income         | Cat2-Accommodation Income-Sundry income         |        | Accommodation Income-Sundry income         |
|      5050 | Income - Bond Retentions                  | Cat1-Accommodation Income-Bond retentions       | Cat2-Accommodation Income-Bond retentions       |        | Accommodation Income-Bond retentions       |
|      5060 | Income - Periodic Bond Retentions         | Cat1-Accommodation Income-Bond retentions       | Cat2-Accommodation Income-Bond retentions       |        | Accommodation Income-Bond retentions       |
|      5100 | Resident Maintenance Fees                 | Cat1-Care Client Income-Client Fees             | Cat2-Care Client Income-Client Fees             |        | Care Client Income-Client Fees             |


Table: DateTable
| Date                    |   Year |   MonthOfYear | MonthInCalendar         |   FY |   FP | Month   |
|:------------------------|-------:|--------------:|:------------------------|-----:|-----:|:--------|
| 2020-07-01 00:00:00.000 |   2020 |             7 | 2020-07-01 00:00:00.000 | 2021 |    1 | Jul     |
| 2020-07-02 00:00:00.000 |   2020 |             7 | 2020-07-01 00:00:00.000 | 2021 |    1 | Jul     |
| 2020-07-03 00:00:00.000 |   2020 |             7 | 2020-07-01 00:00:00.000 | 2021 |    1 | Jul     |
| 2020-07-04 00:00:00.000 |   2020 |             7 | 2020-07-01 00:00:00.000 | 2021 |    1 | Jul     |
| 2020-07-05 00:00:00.000 |   2020 |             7 | 2020-07-01 00:00:00.000 | 2021 |    1 | Jul     |
| 2020-07-06 00:00:00.000 |   2020 |             7 | 2020-07-01 00:00:00.000 | 2021 |    1 | Jul     |
| 2020-07-07 00:00:00.000 |   2020 |             7 | 2020-07-01 00:00:00.000 | 2021 |    1 | Jul     |
| 2020-07-08 00:00:00.000 |   2020 |             7 | 2020-07-01 00:00:00.000 | 2021 |    1 | Jul     |
| 2020-07-09 00:00:00.000 |   2020 |             7 | 2020-07-01 00:00:00.000 | 2021 |    1 | Jul     |
| 2020-07-10 00:00:00.000 |   2020 |             7 | 2020-07-01 00:00:00.000 | 2021 |    1 | Jul     |


Table: Facility
|   Segment Value 2 |
|------------------:|
|              1001 |
|              1004 |
|              1005 |
|              1006 |
|              1008 |
|              1009 |
|              1012 |
|              1013 |
|              3006 |
|              5001 |


Table: Key Measures - Harsh



Table: Mapping
| RESULT      | INCOME/EXPENDITURE   | Sub Heading         | Description                                  | Category2                                                                |   Index- Result |   Index-INCOME/EXPENDITURE |   Index-Sub-Heading |   Index-Description |
|:------------|:---------------------|:--------------------|:---------------------------------------------|:-------------------------------------------------------------------------|----------------:|---------------------------:|--------------------:|--------------------:|
| CARE RESULT | CARE INCOME          | Income - clients    | Client Fees                                  | Cat2-Care Client Income-Client Fees                                      |               1 |                          1 |                   1 |                1.01 |
| CARE RESULT | CARE INCOME          | Income - clients    | Means tested fees                            | Cat2-Care Client Income-Means tested fees                                |               1 |                          1 |                   1 |                1.02 |
| CARE RESULT | CARE INCOME          | Income - clients    | Income Tested Fee                            | Cat2-Care Client Income-Income Tested Fee                                |               1 |                          1 |                   1 |                1.03 |
| CARE RESULT | CARE INCOME          | Income - clients    | CDC - Service Delivery                       | Cat2-Care Client Income-CDC - Service Delivery                           |               1 |                          1 |                   1 |                1.04 |
| CARE RESULT | CARE INCOME          | Income - clients    | Extra service fees                           | Cat2-Care Client Income-Extra service fees                               |               1 |                          1 |                   1 |                1.05 |
| CARE RESULT | CARE INCOME          | Income - clients    | Care Sundry income                           | Cat2-Care Client Income-Sundry income                                    |               1 |                          1 |                   1 |                1.06 |
| CARE RESULT | CARE INCOME          | Income - government | Government subsidies - ACFI                  | Cat2-Care Government Income-Government subsidies - ACFI                  |               1 |                          1 |                   2 |                2.01 |
| CARE RESULT | CARE INCOME          | Income - government | Government subsidies - supplements           | Cat2-Care Government Income-Government subsidies - supplements           |               1 |                          1 |                   2 |                2.02 |
| CARE RESULT | CARE INCOME          | Income - government | Government subsidies - respite               | Cat2-Care Government Income-Government subsidies - respite               |               1 |                          1 |                   2 |                2.03 |
| CARE RESULT | CARE INCOME          | Income - government | Government subsidies - home & community care | Cat2-Care Government Income-Government subsidies - home & community care |               1 |                          1 |                   2 |                2.04 |


Table: PayComponent
| Category   | Pay Component   | Grouping       | Baseline   | Paid Hours   | Payroll Comment   | Before or After Tax   |
|:-----------|:----------------|:---------------|:-----------|:-------------|:------------------|:----------------------|
| Additions  | 101             | Other          |            | N            |                   |                       |
| Additions  | 102             | Other          |            | N            |                   |                       |
| Additions  | 103             | Other          |            | N            |                   |                       |
| Additions  | 10AL            | A/L Loading    |            | N            |                   |                       |
| Additions  | 12AL            | A/L Loading    |            | N            |                   |                       |
| Additions  | 15AL            | A/L Loading    |            | N            |                   |                       |
| Additions  | 45              | Directors Fees |            | N            |                   |                       |
| Additions  | 50AL            | A/L Loading    |            | N            |                   |                       |
| Additions  | 58              | Workers Comp   |            | Y            |                   |                       |
| Additions  | 70AL            | A/L Loading    |            | N            |                   |                       |


Table: PayData
| Pay Group   |   Emp Code | Costing Combination   |   Pay Component |   Pay Unit |   Pay Cost | Payrun Week   | Payrun Date             |   Costing Account |   Costing Facility | Category         |
|:------------|-----------:|:----------------------|----------------:|-----------:|-----------:|:--------------|:------------------------|------------------:|-------------------:|:-----------------|
| GP2         |     100751 | 6030.1013.00          |              75 |          8 |        142 | Week 52       | 2020-06-28 00:00:00.000 |              6030 |               1013 | Other Care Staff |
| GP2         |     102267 | 6030.1013.00          |              75 |          8 |        142 | Week 52       | 2020-06-28 00:00:00.000 |              6030 |               1013 | Other Care Staff |
| GP2         |     100775 | 6030.1013.00          |              75 |          8 |        142 | Week 50       | 2020-06-14 00:00:00.000 |              6030 |               1013 | Other Care Staff |
| GP2         |     100757 | 6030.1013.00          |              75 |          8 |        142 | Week 50       | 2020-06-14 00:00:00.000 |              6030 |               1013 | Other Care Staff |
| GP2         |     101991 | 6030.1013.00          |              75 |          8 |        142 | Week 50       | 2020-06-14 00:00:00.000 |              6030 |               1013 | Other Care Staff |
| GP2         |     100729 | 6030.1013.00          |              75 |          8 |        142 | Week 50       | 2020-06-14 00:00:00.000 |              6030 |               1013 | Other Care Staff |
| GP2         |     100728 | 6030.1013.00          |              75 |          8 |        142 | Week 50       | 2020-06-14 00:00:00.000 |              6030 |               1013 | Other Care Staff |
| GP2         |     102267 | 6030.1013.00          |              75 |          8 |        142 | Week 50       | 2020-06-14 00:00:00.000 |              6030 |               1013 | Other Care Staff |
| GP2         |     100775 | 6030.1013.00          |              75 |          8 |        142 | Week 48       | 2020-05-31 00:00:00.000 |              6030 |               1013 | Other Care Staff |
| GP2         |     100757 | 6030.1013.00          |              75 |          8 |        142 | Week 48       | 2020-05-31 00:00:00.000 |              6030 |               1013 | Other Care Staff |


Table: Transactions
| GL Account   | Segment Name   | NetBalance   | Debit Amount   | Credit Amount   | Trans Date   | Fiscal Year   | Fiscal Period   | Book   | Segment Value 1   | Segment Value 2   |
|:-------------|:---------------|:-------------|:---------------|:----------------|:-------------|:--------------|:----------------|:-------|:------------------|:------------------|
|              |                |              |                |                 |              |               |                 |        |                   |                   |
|              |                |              |                |                 |              |               |                 |        |                   |                   |
|              |                |              |                |                 |              |               |                 |        |                   |                   |
|              |                |              |                |                 |              |               |                 |        |                   |                   |
|              |                |              |                |                 |              |               |                 |        |                   |                   |
|              |                |              |                |                 |              |               |                 |        |                   |                   |
|              |                |              |                |                 |              |               |                 |        |                   |                   |
|              |                |              |                |                 |              |               |                 |        |                   |                   |
|              |                |              |                |                 |              |               |                 |        |                   |                   |
|              |                |              |                |                 |              |               |                 |        |                   |                   |

 


tables: name: Account
data: Account.csv
name: DateTable
data: DateTable.csv
name: Facility
data: Facility.csv
name: Key Measures - Harsh
data: Key Measures - Harsh.csv
name: Mapping
data: Mapping.csv
name: PayComponent
data: PayComponent.csv
name: PayData
data: PayData.csv
name: Transactions
data: Transactions.csv
label: Transactions
Segment Value 1
Account
Account
Transactions
Trans Date
DateTable
Date
Account
Cat2
Mapping
Category2
Transactions
Segment Value 2
Facility
Segment Value 2
PayData
Costing Facility
Facility
Segment Value 2
PayData
Pay Component
PayComponent
Pay Component
output: {
    "joins": [
        {
            "from_table": "Transactions",
            "from_column": "Segment Value 1",
            "to_table": "Account",
            "to_column": "Account"
        },
        {
            "from_table": "Transactions",
            "from_column": "Trans Date",
            "to_table": "DateTable",
            "to_column": "Date"
        },
        {
            "from_table": "Account",
            "from_column": "Cat2",
            "to_table": "Mapping",
            "to_column": "Category2"
        },
        {
            "from_table": "Transactions",
            "from_column": "Segment Value 2",
            "to_table": "Facility",
            "to_column": "Segment Value 2"
        },
        {
            "from_table": "PayData",
            "from_column": "Costing Facility",
            "to_table": "Facility",
            "to_column": "Segment Value 2"
        },
        {
            "from_table": "PayData",
            "from_column": "Pay Component",
            "to_table": "PayComponent",
            "to_column": "Pay Component"
        }
    ]
}