Thursday , August 18 2022

SQL Server Graph Database

In today’s article we will examine the SQL Server Graph Database.

I will try to explain the Graph database features, which provide complex and hierarchical relationship management, which started to be used with SQL Server 2017, in the article.

We can say that Graph database is critical to show the complex hierarchical data we experience while working with relational database systems and NoSQL databases, to eliminate performance problems such as the number of relationship levels and database sizes.

Where can we use a graphical database?

Social networking

Fraud detection

Product recommendations

IT Network analysis

Social recommendations

etc.

The graphical database consists of two elements.

Node: Represents entities within the graphical database. customer, employee vs.

Edge: Represents the – Node – relationship between entities.

After brief information about SQL Server Graph database, let’s perform a demo about its usage and consolidate our knowledge.

Example scenario: Let’s describe Person, City, Team, Stat and the relationships between them.

As a first step, let’s create a database named “DMCGraph” with the following code block in order to implement the scenario.

Let’s start creating the “Node” tables. First, let’s take action for users.

Looking at the code block above, you can see that it’s like a classic table creation process. The only difference is that you specify a “Node” with the phrase “AS NODE” at the end of the table syntax.

The Node named “Users” consists of a UserID and Username information, we need to take care that there is a Primary key in the node. To check whether a table is a node or edge, we use the is_node and is_edge columns in the sys.tables system table.

select name,is_node,is_edge from sys.tables

In the picture above, we see that the table named users is a node. We have already stated that there is a node with “AS NODE” while defining it.

Now we have a graph table named users and let’s enter data into it.

insert into Users values
(‘Caglar’),(‘Baki’),(‘Contemporary’),(‘Musa’),(‘Sait’),(‘Burak’)

Now let’s view the content of the node named users.

select * from dbo.Users

When you examine the picture above, you will see that there is a column other than the UserID and UserName that we defined. The data content here includes JSON data and the default sort value starts from 0.

Similarly, let’s create Team, Stat and city nodes.

For the team;

For the state;

For the city;

We have created the node we will need for the graph database. Now it’s time to create an edge.

Let’s create an edge named FavoriteTeam, WhichCityTeam and TeamsInStad.

After running the above code block, let’s check is_node and is_edge over sys.tables.

We made the definitions as seen in the picture above. Now it’s time to insert insert on Edge. Edge is used to describe the relationship between two or more nodes.

Assuming that a user is holding a tool, let’s insert the edge named Team Retained. But before we do this, let’s view the HeldTeam edge.

select * from RetainedTeam

 

As seen in the picture above, there are 3 columns on the edge. When we look at the column nomenclature, it actually has a structure that tells us the relationship between nodes.

Let’s take action for users number 1. (I associated user #1 with team #1.)

Let’s perform the operation again for users number 1. (I associated user #1 with team #4.)

Let’s perform the operation again for users number 2. (I associated user #2 with team #3.)

Let’s perform the operation again for users number 3. (I associated user #3 with team #1.)

Let’s add a record for whichCityTeam.

I added team number 1 for city number 1.

I added team number 4 for city number 2.

I added team number 3 for city number 1.

Let’s add a record for TeamsStadium.

I matched the number 1 team with the number 1 stat.

I matched team number 2 with stat number 2.

I matched team #3 with stat #3.

To view the data;

In the select statement, you must specify the match statement in the where condition. For example, you need to use the following query to display the teams held by Users.

In the above example, as the users node, I named the Retained Team edge as tt and the teams node as t. The output is as follows.

Similarly, as a different example, we can use the following query to get the answer to the question Which City hosts which team.

I used t for Teams node, hst for WhichCityTeam, c for Cities and made it match with match.

If we want to write the query that includes the teams of the users and the cities of the teams;

For Deletion of Data;

To perform a Delete operation on Edge, you need to specify the values of $from_id and $to_id.

When you run the query of the Picture above again, you will not see the Trabzonspor information.

Graph databases are changing the way many operations from complex operations are implemented.

In this article, we briefly reviewed how to create nodes and edges in graph databases.

We also saw how to implement relationships between different nodes and how to add, read, and delete edges.

 

About Çağlar Özenç

Leave a Reply

Your email address will not be published. Required fields are marked *