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: Average Entrance
|    ID | Gender   |   Year Level | Enrol Date              | Confirmation Date       |   Enrolment Year |   Confirmation Month |
|------:|:---------|-------------:|:------------------------|:------------------------|-----------------:|---------------------:|
| 38532 | F        |           11 | 2003-02-03 00:00:00.000 | 2003-01-14 00:00:00.000 |             2003 |                    1 |
| 38859 | F        |           11 | 2003-02-03 00:00:00.000 | 2003-01-31 00:00:00.000 |             2003 |                    1 |
| 38850 | M        |           11 | 2003-02-03 00:00:00.000 | 2003-02-03 00:00:00.000 |             2003 |                    2 |
| 39072 | F        |           11 | 2003-03-11 00:00:00.000 | 2003-03-11 00:00:00.000 |             2003 |                    3 |
| 39383 | F        |           11 | 2003-07-16 00:00:00.000 | 2003-05-19 00:00:00.000 |             2003 |                    5 |
| 39812 | M        |           11 | 2003-07-16 00:00:00.000 | 2003-06-17 00:00:00.000 |             2003 |                    6 |
| 39948 | M        |           11 | 2003-07-16 00:00:00.000 | 2003-06-27 00:00:00.000 |             2003 |                    6 |
| 40749 | F        |           11 | 2004-02-02 00:00:00.000 | 2004-01-13 00:00:00.000 |             2004 |                    1 |
| 41352 | F        |           11 | 2004-07-20 00:00:00.000 | 2004-04-20 00:00:00.000 |             2004 |                    4 |
| 41168 | F        |           11 | 2004-07-20 00:00:00.000 | 2004-04-21 00:00:00.000 |             2004 |                    4 |


Table: Dates
| Date                    |   Year |   Quarter Of Year |   Month Of Year |   Day Of Month |   DateInt | Month Name   | Short Month   | Month & Year   | Quarter & Year   | Quarter   | Day Name   | Week Ending             |   Week Number |   MonthnYear |   QuarternYear |   Short Year | FY   | Month Ending            |   Day Of Week | Week Starting           | Month Starting          |
|:------------------------|-------:|------------------:|----------------:|---------------:|----------:|:-------------|:--------------|:---------------|:-----------------|:----------|:-----------|:------------------------|--------------:|-------------:|---------------:|-------------:|:-----|:------------------------|--------------:|:------------------------|:------------------------|
| 2000-07-03 00:00:00.000 |   2000 |                 3 |               7 |              3 |  20000703 | July         | Jul           | Jul 2000       | Q3 2000          | Q3        | Monday     | 2000-07-09 00:00:00.000 |            28 |     20000700 |       20000300 |            0 | FY1  | 2000-07-31 00:00:00.000 |             0 | 2000-07-03 00:00:00.000 | 2000-07-01 00:00:00.000 |
| 2000-07-04 00:00:00.000 |   2000 |                 3 |               7 |              4 |  20000704 | July         | Jul           | Jul 2000       | Q3 2000          | Q3        | Tuesday    | 2000-07-09 00:00:00.000 |            28 |     20000700 |       20000300 |            0 | FY1  | 2000-07-31 00:00:00.000 |             1 | 2000-07-03 00:00:00.000 | 2000-07-01 00:00:00.000 |
| 2000-07-05 00:00:00.000 |   2000 |                 3 |               7 |              5 |  20000705 | July         | Jul           | Jul 2000       | Q3 2000          | Q3        | Wednesday  | 2000-07-09 00:00:00.000 |            28 |     20000700 |       20000300 |            0 | FY1  | 2000-07-31 00:00:00.000 |             2 | 2000-07-03 00:00:00.000 | 2000-07-01 00:00:00.000 |
| 2000-07-06 00:00:00.000 |   2000 |                 3 |               7 |              6 |  20000706 | July         | Jul           | Jul 2000       | Q3 2000          | Q3        | Thursday   | 2000-07-09 00:00:00.000 |            28 |     20000700 |       20000300 |            0 | FY1  | 2000-07-31 00:00:00.000 |             3 | 2000-07-03 00:00:00.000 | 2000-07-01 00:00:00.000 |
| 2000-07-07 00:00:00.000 |   2000 |                 3 |               7 |              7 |  20000707 | July         | Jul           | Jul 2000       | Q3 2000          | Q3        | Friday     | 2000-07-09 00:00:00.000 |            28 |     20000700 |       20000300 |            0 | FY1  | 2000-07-31 00:00:00.000 |             4 | 2000-07-03 00:00:00.000 | 2000-07-01 00:00:00.000 |
| 2000-07-08 00:00:00.000 |   2000 |                 3 |               7 |              8 |  20000708 | July         | Jul           | Jul 2000       | Q3 2000          | Q3        | Saturday   | 2000-07-09 00:00:00.000 |            28 |     20000700 |       20000300 |            0 | FY1  | 2000-07-31 00:00:00.000 |             5 | 2000-07-03 00:00:00.000 | 2000-07-01 00:00:00.000 |
| 2000-07-09 00:00:00.000 |   2000 |                 3 |               7 |              9 |  20000709 | July         | Jul           | Jul 2000       | Q3 2000          | Q3        | Sunday     | 2000-07-09 00:00:00.000 |            28 |     20000700 |       20000300 |            0 | FY1  | 2000-07-31 00:00:00.000 |             6 | 2000-07-03 00:00:00.000 | 2000-07-01 00:00:00.000 |
| 2001-07-09 00:00:00.000 |   2001 |                 3 |               7 |              9 |  20010709 | July         | Jul           | Jul 2001       | Q3 2001          | Q3        | Monday     | 2001-07-15 00:00:00.000 |            28 |     20010700 |       20010300 |            1 | FY2  | 2001-07-31 00:00:00.000 |             0 | 2001-07-09 00:00:00.000 | 2001-07-01 00:00:00.000 |
| 2001-07-10 00:00:00.000 |   2001 |                 3 |               7 |             10 |  20010710 | July         | Jul           | Jul 2001       | Q3 2001          | Q3        | Tuesday    | 2001-07-15 00:00:00.000 |            28 |     20010700 |       20010300 |            1 | FY2  | 2001-07-31 00:00:00.000 |             1 | 2001-07-09 00:00:00.000 | 2001-07-01 00:00:00.000 |
| 2001-07-11 00:00:00.000 |   2001 |                 3 |               7 |             11 |  20010711 | July         | Jul           | Jul 2001       | Q3 2001          | Q3        | Wednesday  | 2001-07-15 00:00:00.000 |            28 |     20010700 |       20010300 |            1 | FY2  | 2001-07-31 00:00:00.000 |             2 | 2001-07-09 00:00:00.000 | 2001-07-01 00:00:00.000 |


Table: Forecast Selection
| Forecast Option   |
|:------------------|
| Forecast          |
| Confirmed         |


Table: Future Status Lookup
| Future Status Code   | Future Status       | Synergy Meaning   |   Status Seq |
|:---------------------|:--------------------|:------------------|-------------:|
| A                    | Assessment          |                   |            0 |
| C                    | Cancelled           | CANCELLED         |            0 |
| D                    | Defer               |                   |            0 |
| E                    | Enrolment Waitlist  |                   |            0 |
| I                    | Interview           |                   |            0 |
| L                    | Enrolment Confirmed | FINALISED         |            0 |
| O                    | Offer               |                   |            0 |
| R                    | Reserved            |                   |            0 |
| W                    | Wait Listed         | APPLICATION       |            0 |
| X                    | Do Not Offer        |                   |            0 |


Table: Future Status
|   Future ID | Future Status   | Future Gender   |   Future Year Level |   Future Enrol Year |
|------------:|:----------------|:----------------|--------------------:|--------------------:|
|       51454 | W               | M               |                   7 |                2021 |
|       51821 | W               | M               |                   7 |                2021 |
|       52397 | W               | M               |                   7 |                2021 |
|       52615 | W               | M               |                   7 |                2021 |
|       52868 | W               | M               |                   7 |                2021 |
|       52872 | W               | M               |                   7 |                2021 |
|       52888 | W               | M               |                   7 |                2021 |
|       52963 | W               | M               |                   7 |                2021 |
|       53056 | W               | M               |                   7 |                2021 |
|       53061 | W               | M               |                   7 |                2021 |


Table: Gender
| Gender Code   | Gender   |
|:--------------|:---------|
| F             | Female   |
| M             | Male     |


Table: Historical Years
|   Number of Years |
|------------------:|
|                 3 |
|                 4 |
|                 5 |


Table: Left Students
|   Year Left |   Month Left | Student Left Date       |   Student ID | Student Gender   | Student Status   |   Left Year Level |
|------------:|-------------:|:------------------------|-------------:|:-----------------|:-----------------|------------------:|
|        2011 |            4 | 2011-04-14 00:00:00.000 |        10615 | M                | LOA              |                10 |
|        2011 |           10 | 2011-10-19 00:00:00.000 |        10726 | M                | LOA              |                 8 |
|        2010 |           12 | 2010-12-07 00:00:00.000 |        11108 | M                | LOA              |                 8 |
|        2010 |           12 | 2010-12-07 00:00:00.000 |        11108 | M                | LOA              |                 9 |
|        2010 |           12 | 2010-12-07 00:00:00.000 |        11108 | M                | LOA              |                10 |
|        2010 |           12 | 2010-12-07 00:00:00.000 |        11115 | M                | LOA              |                 6 |
|        2010 |           12 | 2010-12-07 00:00:00.000 |        11115 | M                | LOA              |                 7 |
|        2012 |            9 | 2012-09-05 00:00:00.000 |        11320 | M                | LOA              |                10 |
|        2011 |            8 | 2011-08-31 00:00:00.000 |        11517 | F                | LOA              |                10 |
|        2012 |           11 | 2012-11-16 00:00:00.000 |        11598 | F                | LOA              |                 8 |


Table: Student Monthly Statistics
| Process Date            |   Student ID | Entry Date              | Left Date   | Gender   | Student Status   | Campus   |   Year Level |   Year Level Sort | Type    |
|:------------------------|-------------:|:------------------------|:------------|:---------|:-----------------|:---------|-------------:|------------------:|:--------|
| 2014-01-31 00:00:00.000 |        52930 | 2013-01-29 00:00:00.000 |             | F        | NORMAL           | S        |            8 |               108 | Current |
| 2014-01-31 00:00:00.000 |        62811 | 2013-01-29 00:00:00.000 |             | F        | NORMAL           | S        |            8 |               108 | Current |
| 2014-01-31 00:00:00.000 |        63030 | 2013-01-29 00:00:00.000 |             | F        | NORMAL           | S        |            8 |               108 | Current |
| 2014-01-31 00:00:00.000 |        63709 | 2013-01-29 00:00:00.000 |             | F        | NORMAL           | S        |            8 |               108 | Current |
| 2014-01-31 00:00:00.000 |        63382 | 2013-01-29 00:00:00.000 |             | F        | NORMAL           | S        |            8 |               108 | Current |
| 2014-01-31 00:00:00.000 |        63421 | 2013-01-29 00:00:00.000 |             | F        | NORMAL           | S        |            8 |               108 | Current |
| 2014-01-31 00:00:00.000 |        61769 | 2013-01-29 00:00:00.000 |             | F        | NORMAL           | S        |            8 |               108 | Current |
| 2014-01-31 00:00:00.000 |        61335 | 2013-01-29 00:00:00.000 |             | F        | NORMAL           | S        |            8 |               108 | Current |
| 2014-01-31 00:00:00.000 |        64519 | 2013-01-29 00:00:00.000 |             | F        | NORMAL           | S        |            8 |               108 | Current |
| 2014-01-31 00:00:00.000 |        64631 | 2013-01-29 00:00:00.000 |             | F        | NORMAL           | S        |            8 |               108 | Current |


Table: Student Status
| Code   | Student Status   | Synergy Meaning   |
|:-------|:-----------------|:------------------|
|        | Not Selected     |                   |
| LEA    | Leaving          | LEAVING           |
| LFT    | Left             | LEFT              |
| LOA    | Leave of Absence | LOA               |
| NEW    | New Student      | NEW               |
| NOR    | Normal           | NORMAL            |
| RPT    | Repeating        | REPEATING         |


Table: Table
| Month Name   |   Av Entrants |   Month Number |   Entrants | Start                   | End                     |   Yrs |
|:-------------|--------------:|---------------:|-----------:|:------------------------|:------------------------|------:|
| July         |         14.5  |              7 |         58 | 1996-01-01 00:00:00.000 | 2017-12-31 00:00:00.000 |     4 |
| August       |          6.25 |              8 |         25 | 1996-01-01 00:00:00.000 | 2017-12-31 00:00:00.000 |     4 |
| September    |          6    |              9 |         24 | 1996-01-01 00:00:00.000 | 2017-12-31 00:00:00.000 |     4 |
| November     |          0.25 |             11 |          1 | 1996-01-01 00:00:00.000 | 2017-12-31 00:00:00.000 |     4 |
| December     |          0.25 |             12 |          1 | 1996-01-01 00:00:00.000 | 2017-12-31 00:00:00.000 |     4 |
| October      |          2.75 |             10 |         11 | 1996-01-01 00:00:00.000 | 2017-12-31 00:00:00.000 |     4 |
| May          |         15.25 |              5 |         61 | 1996-01-01 00:00:00.000 | 2017-12-31 00:00:00.000 |     4 |
| April        |         18.5  |              4 |         74 | 1996-01-01 00:00:00.000 | 2017-12-31 00:00:00.000 |     4 |
| June         |         18    |              6 |         72 | 1996-01-01 00:00:00.000 | 2017-12-31 00:00:00.000 |     4 |
| February     |         11    |              2 |         44 | 1996-01-01 00:00:00.000 | 2017-12-31 00:00:00.000 |     4 |


Table: Year Levels
| Campus   |   Year Level Code |   Year Level Sort | Year Level   | Year Level Coordinator   | Next Year Campus Male   |   Next Year Level Male | Next Year Campus Female   |   Next Year Level Female | Timetable Group   |   Years Until Graduation | Previous Year Level   |
|:---------|------------------:|------------------:|:-------------|:-------------------------|:------------------------|-----------------------:|:--------------------------|-------------------------:|:------------------|-------------------------:|:----------------------|
| K        |                30 |                30 | Pre Kinder   |                          | K                       |                     40 | K                         |                       40 | K                 |                       14 |                       |
| K        |                40 |                40 | Kindergarten |                          | J                       |                      0 | J                         |                        0 | K                 |                       13 | 30                    |
| J        |                 0 |               100 | Preparatory  |                          | J                       |                      1 | J                         |                        1 | J                 |                       12 | 40                    |
| J        |                 1 |               101 | Year 1       |                          | J                       |                      2 | J                         |                        2 | J                 |                       11 | 0                     |
| J        |                 2 |               102 | Year 2       |                          | J                       |                      3 | J                         |                        3 | J                 |                       10 | 1                     |
| J        |                 3 |               103 | Year 3       |                          | J                       |                      4 | J                         |                        4 | J                 |                        9 | 2                     |
| J        |                 4 |               104 | Year 4       |                          | J                       |                      5 | J                         |                        5 | J                 |                        8 | 3                     |
| J        |                 5 |               105 | Year 5       |                          | J                       |                      6 | J                         |                        6 | J                 |                        7 | 4                     |
| J        |                 6 |               106 | Year 6       |                          | S                       |                      7 | S                         |                        7 | J                 |                        6 | 5                     |
| S        |                 7 |               107 | Year 7       |                          | S                       |                      8 | S                         |                        8 | S                 |                        5 | 6                     |

 


tables: name: Average Entrance
data: Average Entrance.csv
name: Dates
data: Dates.csv
name: Forecast Selection
data: Forecast Selection.csv
name: Future Status Lookup
data: Future Status Lookup.csv
name: Future Status
data: Future Status.csv
name: Gender
data: Gender.csv
name: Historical Years
data: Historical Years.csv
name: Left Students
data: Left Students.csv
name: Student Monthly Statistics
data: Student Monthly Statistics.csv
name: Student Status
data: Student Status.csv
name: Table
data: Table.csv
name: Year Levels
data: Year Levels.csv
label: Student Monthly Statistics
Process Date
Dates
Date
Student Monthly Statistics
Gender
Gender
Gender Code
Average Entrance
Gender
Gender
Gender Code
Future Status
Future Gender
Gender
Gender Code
Future Status
Future Status
Future Status Lookup
Future Status Code
Average Entrance
Year Level
Year Levels
Year Level Code
Student Monthly Statistics
Year Level
Year Levels
Year Level Code
Future Status
Future Year Level
Year Levels
Year Level Code
Average Entrance
Confirmation Date
Dates
Date
Left Students
Student Left Date
Dates
Date
Left Students
Student Gender
Gender
Gender Code
Left Students
Student Status
Student Status
Code
Left Students
Left Year Level
Year Levels
Year Level Code
output: {
    "joins": [
        {
            "from_table": "Student Monthly Statistics",
            "from_column": "Process Date",
            "to_table": "Dates",
            "to_column": "Date"
        },
        {
            "from_table": "Student Monthly Statistics",
            "from_column": "Gender",
            "to_table": "Gender",
            "to_column": "Gender Code"
        },
        {
            "from_table": "Average Entrance",
            "from_column": "Gender",
            "to_table": "Gender",
            "to_column": "Gender Code"
        },
        {
            "from_table": "Future Status",
            "from_column": "Future Gender",
            "to_table": "Gender",
            "to_column": "Gender Code"
        },
        {
            "from_table": "Future Status",
            "from_column": "Future Status",
            "to_table": "Future Status Lookup",
            "to_column": "Future Status Code"
        },
        {
            "from_table": "Average Entrance",
            "from_column": "Year Level",
            "to_table": "Year Levels",
            "to_column": "Year Level Code"
        },
        {
            "from_table": "Student Monthly Statistics",
            "from_column": "Year Level",
            "to_table": "Year Levels",
            "to_column": "Year Level Code"
        },
        {
            "from_table": "Future Status",
            "from_column": "Future Year Level",
            "to_table": "Year Levels",
            "to_column": "Year Level Code"
        },
        {
            "from_table": "Average Entrance",
            "from_column": "Confirmation Date",
            "to_table": "Dates",
            "to_column": "Date"
        },
        {
            "from_table": "Left Students",
            "from_column": "Student Left Date",
            "to_table": "Dates",
            "to_column": "Date"
        },
        {
            "from_table": "Left Students",
            "from_column": "Student Gender",
            "to_table": "Gender",
            "to_column": "Gender Code"
        },
        {
            "from_table": "Left Students",
            "from_column": "Student Status",
            "to_table": "Student Status",
            "to_column": "Code"
        },
        {
            "from_table": "Left Students",
            "from_column": "Left Year Level",
            "to_table": "Year Levels",
            "to_column": "Year Level Code"
        }
    ]
}