Enhancement of a Transformation Algorithm to Migrate SQL Database into NoSQL Graph Database

Hira Lal Bhandari; Roshan Chitrakar

doi:10.5334/dsj-2024-035

Figures & Tables

CSV	Comma-separated values
DBMS	Database Management System
IDE	Integrated Development Environment
JSON	JavaScript Object Notation
Neo4j	Network Exploration and Optimization “for” Java
NOSQL	Not Only Structure Query Language
RDBMS	Relational Database Management System
SQL	Structured Query Language

Model Transformation from Relational to NoSQL Graph Database.

Migration of Database from Relational Database MySQL Server to NoSQL Graph Database Neo4j.

Table 1

Differences between Meta Analyzer Tools.

Criteria	SchemaCrawler	DbSchema	SchemaSpy
Type	Free	Commercial	Free
Export type	CSV, HTML, JSON, Plain text	HTML, MS Excel, PDF	HTML
Runs on	Linux, Mac OS, Windows	Windows	Linux, Mac OS, Windows

Table 2

Enhancing an Algorithm Proposed by Shabana et al.

ALGORITHM PROPOSED BY SHABANA RAMZAN ET AL. TO MIGRATE DATA FROM MS SQL SERVER TO ORACLE NOSQL

ALGORITHM AFTER MODIFICATION TO MIGRATE DATA FROM MYSQL SERVER TO NEO4J GRAPH DATABASE

Construct: make a table object of the source.
Construct: Make a lean of the instances in the target tabular from those in the source table.
Representing: Target tabular instances map to objects in the source table.
Construct: make an article for every section of the source table.
Mapping: Map from target field object to source column object.
If value is true
Pick the rule for the transformation.
// the standard is appropriate or not when more than one rule is free for a similar sort of item.
endif
Construct: The object for the target table is created.
Construct: Every column’s target object is made.
Select primary key.
Keep a background marked by changed objects // to prevent the creation of target objects in duplicate.

Construct: make a tabular form of the source.
Construct: Creating a rundown of targeted nodes from the source table’s items. // Table object of SQL table is addressed as a named hub in a diagram data set
Mapping: Target node objects map to objects in the source table. // A node in a graph database has been used to map the source table’s schema.
Construct: make an article for every section of the source table.
Mapping: Target node property to object map from source column. // Oracle’s NoSQL source column is mapped with a table object, whereas the graph database’s source column is mapped with a node property.
If value is true
Pick the rule for the transformation.// transformation rule that has been modified in accordance with the data set must be mapped and transformed from a SQL to a NoSQL graph database, whereas the previous algorithm utilized transformation rule that was transferred from an Oracle non-structured database to a relational database.
// the standard is appropriate or not when more than one rule is free for a similar sort of item.
endif
Create: The instance with the targeted labeled node is created.// In a graph database, the object that represents the source table is labeled a node.
Construct: target hub property of every section is made. // the section is planned and changed as hub property in a diagram data set.
Select primary key.
Keep a background marked by changed objects // to prevent the creation of target objects in duplicate.

Table 3

Data Source.

SN	DATA SET	SOURCE	FORMAT	SIZE
1	Classicmodels	https://www.mysqltutorial.org/mysql-sample-database.aspx	.sql	205 KB
2	Sampledb	https://sample-videos.com/download-sample-sql.php	.sql	4,340 KB
3	Northwind	https://en.wikiversity.org/wiki/Database_Examples/Northwind/	.sql	64 KB
4	Hrdb	https://www.sqltutorial.org/sql-sample-database/	.sql	23 KB
5	chinook	https://github.com/lerocha/chinook-database/releases?fbclid=IwAR34vMltOsbXO_bBu2Rkmw3dvYvWTqdaP1BlF1c9JPrWSjCQ2ENyyBYvT_8	.sql	1,827 KB

Retailer of Scale Models of Classic Cars Database.

Table 4

Schematic View of sampledb Database.

FIELD	TYPE	NULL	KEY	DEFAULT	EXTRA
user_id	int	NO	PRI	NULL	auto_increment
Username	varchar(255)	YES		NULL
first_name	varchar(50)	YES		NULL
last_name	varchar(50)	YES		NULL
Gender	varchar(10)	YES		NULL
Password	varchar(50)	YES		NULL
Status	tinyint	YES		NULL

Entity Relationship Diagram for northwind Database.

Entity Relationship Diagram for hrdb Database.

Entity Relationship Diagram for chinook Database.

The Process of Schema Mapping for Data Transformation (Legler & Naumann 2007).

Query Execution Time for classicmodels Database.

Query Execution Time for sampledb Database.

Query Execution Time for northwind Database.

Query Execution Time for chinook Database.

Table 5

Data Conversion and Migration.

DATABASE	NUMBER OF RECORDS	TRANSFORMATION TIME (IN SECONDS)	RELATION (YES/NO)
Classicmodels	3,864	11 sec.	Yes
Sampled	50,000	12 sec.	No
Northwind	932	10 sec.	Yes
Hrdb	136	8 sec.	Yes
chinook	15,587	8 sec.	Yes

Table 6

Query Elapsed Time in Milliseconds for Database classicmodels.

NO.	QUERY	ELAPSED TIME MYSQL	ELAPSED TIME NEO4J
1.	Insert	32 ms	4.33 ms
2.	Delete	703 ms	74 ms
3.	Retrieve	20 ms	46.67 ms
4.	Multiple retrieve	3 ms	18.33 ms
5.	Retrieval with single join	47 ms	11.33 ms
6.	Retrieval with multiple join	15 ms	1360 ms
7.	Update	47 ms	51.33 ms

Table 7

Query Elapsed Time in Milliseconds for Database Sampled.

NO.	QUERY	ELAPSED TIME MYSQL	ELAPSED TIME NEO4J
1.	Insert	0.0 ms	52 ms
2.	Delete	16 ms	6 ms
3.	Retrieve	16 ms	1209 ms
4.	Multiple retrieve	47 ms	1900 ms
5.	Retrieval with single join	No join	No join
6.	Retrieval with multiple join	No join	No join
7.	Update	0.0 ms	287 ms

Table 8

Query Elapsed Time in Milliseconds for Database northwind.

NO.	QUERY	ELAPSED TIME MYSQL	ELAPSED TIME NEO4J
1.	Insert	46 ms	216 ms
2.	Delete	31 ms	28 ms
3.	Retrieve	0.0 ms	215 ms
4.	Multiple retrieve	31 ms	156 ms
5.	Retrieval with single join	47 ms	199 ms
6.	Retrieval with multiple join	130 ms	212 ms
7.	Update	63 ms	330 ms

Table 9

Query Elapsed Time in Milliseconds for Database hrdb.

NO.	QUERY	ELAPSED TIME MYSQL	ELAPSED TIME NEO4J
1.	Insert	16 ms	56 ms
2.	Delete	381 ms	60 ms
3.	Retrieve	0.0 ms	16 ms
4.	Multiple retrieve	0.0 ms	32 ms
5.	Retrieval with single join	0.0 ms	17 ms
6.	Retrieval with multiple join	0.0 ms	11 ms
7.	Update	78 ms	143 ms

Table 10

Query Elapsed Time in Milliseconds for Database Chinook.

NO.	QUERY	ELAPSED TIME MYSQL	ELAPSED TIME NEO4J
1.	Insert	63 ms	305Ms
2.	Delete	15 ms	61 Ms
3.	Retrieve	0.0 ms	160 ms
4.	Multiple retrieve	0.0 ms	124 ms
5.	Retrieval with single join	16 ms	183ms
6.	Retrieval with multiple join	16 ms	156036 ms
7.	Update	16 ms	294 ms

Enhancement of a Transformation Algorithm to Migrate SQL Database into NoSQL Graph Database

Figures & Tables

Figure 1

Figure 2

Figure 3

Figure 4

Table 1

Table 2

Table 3

Figure 5

Table 4

Figure 6

Figure 7

Figure 8

Figure 9

Figure 10

Figure 11

Figure 12

Figure 13

Figure 14