SQLite data analysis and extension

Requirement:

You have a "categories" table with three columns: "id," "name," and "main_category_id," containing 118,118 rows. The "id" field represents the category's unique identifier, the "name" field contains the category's English name, and the "main_category_id" is currently empty (NULL), serving as a foreign key referencing the yet-to-be-populated "main_categories" table.

Here's an example of the "categories" table content:

id name main_category_id
1 attributed-no-source NULL
2 best NULL
6 out-of-control NULL
8 worst NULL
9 dance NULL
Notably, the "name" column contains whole words or hyphenated words.

Additionally, there is an empty "main_categories" table with two columns: "id" and "name."

Task:

Your task is to cluster the categories, find meaningful cluster names that reflect the grouped categories, populate the "main_categories" table with these cluster names, and establish foreign key relationships between the "main_categories" and "categories" tables using the "id" column of the "main_categories" table and the "main_category_id" column of the "categories" table. To maintain consistency, the cluster names should be in lowercase, whether they consist of whole words or hyphenated words.

Acceptance Criteria (AC):

AC1: The "main_categories" table must contain the primary category clusters. For example:

id name
1 love
2 life
3 friendship
4 inspiration
5 wisdom
6 relationships
... ...
AC2: The "main_category_id" column in the "categories" table should contain the ID of the cluster (acting as a foreign key referencing the "id" of the "main_categories" table). This should be applied to all 118,118 rows in the "categories" table. For example:

id name main_category_id
3 life 2
4 love 1
7 truth 5
19 philosophy 5
20 friendship 3
23 marriage 6
33 friends 3
50 desire 1
52 honesty 5
55 passion 1
56 reality 2
57 relationships 6
... ... ...