Photo by Daniela Cuevas on Unsplash
Northwind is a well-known e-commerce database which is largely used for training purposes across various database platforms. Refer to w3schools.com for more info.
In this demonstration we are going to use the RDF Knowledge Graph version of the Northwind database to provide hands-on experience on Amazon Neptune by executing use case stories (SPARQL queries) on a Jupyter Notebook. It’s a practical, learn-as-you-go experience.
Try it yourself: The SPARQL queries from this article are available in the RDF Studio Query Library when connected to the Northwind database — filter by Northwind Use Case category to find them. If you're not already connected to Northwind, connect via Connect tab first, then navigate to Query -> Library.
Northwind Use Case
The Enterprise Knowledge Graph Forum (EKGF) is now part of the Object Management Group (OMG). The EKGF was established to define best practice and mature the marketplace for EKG adoption and provides 10 guiding principles which are intended to provide guidelines for the development and deployment of an Enterprise Knowledge Graph (EKG). The principles emphasise shared meaning and content reuse that are the cornerstone of operating in complex and interconnected environments.
The Northwind Use Case demonstrates Principle 7, which says:
All artefacts around and information in the EKG are linked to defined and prioritised use cases. Nothing in the EKG exists without a known business justification and purpose.
Here's a simplified version of the Northwind schema (RDF Graph Database). The full version of the Northwind Ontology can be found under the tab Model -> Ontologies -> Northwind Card -> Edit In Graph Editor.
This demonstration covers a great deal of the syntax and semantics of the SPARQL query language, including FILTER, UNION, LIMIT, OFFSET, GROUP BY, ORDER BY, DISTINCT, OPTIONAL, BIND, BOUND, MINUS, FILTER NOT EXISTS, INSERT, DELETE, DESCRIBE, CONSTRUCT, REGEX, CONTAINS, HAVING, as well as String Matching and Manipulation, Aggregation Functions, Subqueries, and Property Paths, among others.
FILTER
UNION
LIMIT
OFFSET
GROUP BY
ORDER BY
DISTINCT
OPTIONAL
BIND
BOUND
MINUS
FILTER NOT EXISTS
INSERT
DELETE
DESCRIBE
CONSTRUCT
REGEX
CONTAINS
HAVING
When documenting your use cases, identify key stakeholders, outline primary and secondary business outcomes for short and long terms, document relevant personas, and capture the concepts involved in the use case and stories.
Crafting effective user stories is important for a comprehensive understanding of your project. Consider this template: 'As a Role, I want Desired Outcome in order to achieve Business Objective'. This structured approach ensures clear communication of user needs, desired outcomes, and the ultimate business goal.
The following are the Stories of the Northwind Use Case:
Create a concise report listing all the employees in the company Full story: As a Human Resources Manager, I want to create a concise report listing all the employees in the companyin order to learn their respective positions within the organization Main Concepts: Human Resources Manager, Employee Query: Given a Human Resources Manager persona, WHEN they want to create a report with all employees in the company, THEN the system should execute a SPARQL query to retrieve values for the rdfs:label, foaf:title, foaf:lastName, and foaf:firstName properties of each Employee.
Create a concise report listing all the employees in the company
Full story: As a Human Resources Manager, I want to create a concise report listing all the employees in the companyin order to learn their respective positions within the organization
Main Concepts: Human Resources Manager, Employee
Query: Given a Human Resources Manager persona, WHEN they want to create a report with all employees in the company, THEN the system should execute a SPARQL query to retrieve values for the rdfs:label, foaf:title, foaf:lastName, and foaf:firstName properties of each Employee.
Amazon Neptune associates every triple with a named graph. The default graph is defined as the union of all named graphs. If you submit a SPARQL query without explicitly specifying a graph via the GRAPH keyword or constructs such as FROM NAMED, Neptune always considers all triples in your DB instance. Triples that appear in more than one graph are returned only once.
GRAPH
FROM NAMED
In this demonstration, we will skip the template for the remaining stories and provide only brief descriptions for simplicity.
Note that the same filter can be applied directly as follows:
Note that the query above shows two ways of implementing the filter. You can comment out the first filter and uncomment the second one to verify its result.
The same result can be obtained by using MINUS.
NOT EXISTS and MINUS in SPARQL represent two ways of thinking about negation and they will be explored in more details in a future article.
NOT EXISTS
SPARQL
Note: Fax was a machine from the 90s able to scan and transmit a document over the phone line:-)
The same result can be obtained by using the NOT EXISTS filter below.
Checking if new customer has been added successfully.
Step 1: Insert
Step 2: Update (Delete/Insert)
Checking if existing customer has been updated successfully.
Checking if existing customer has been deleted successfully.
The next queries focus on recommendations.
Query: Customers who bought product-61 also bought which products in the same order and how many times?
Query: Customers who bought product-61 also bought which products across all orders and how many times?
Query: How many times products 2 and 61 where bought by the same customer.
For didactical purposes, the following are the 6 steps taken to develop the Property Path query above.
Step 1: Graph pattern traversing Product, OrderDetail, Order and Customer nodes, which are represented by the order, orderDetail, product and customer bound variables in the query.
Product
OrderDetail
Order
Customer
order
orderDetail
product
customer
# List order items that contain product-61 SELECT * WHERE { ?orderDetail :hasProduct ?product ; :belongsToOrder ?order . ?order :hasCustomer ?customer . FILTER (?product = :product-61) }
Note the naming convention where bound variable names are declared in CamelCase, whereas the Class names, as seen in the diagram, are declared in PascalCase.
Step 2: Invert the hasProduct path expression to match the following direction: product → orderDetail → order → customer
# The same result is returned SELECT * WHERE { ?product ^:hasProduct ?orderDetail . # Invert direction ?orderDetail :belongsToOrder ?order . ?order :hasCustomer ?customer . FILTER (?product = :product-61) }
Step 3: Use sequence path to omit the binding of the?orderDetail variable.
SELECT * WHERE { ?product ^:hasProduct/:belongsToOrder ?order . ?order :hasCustomer ?customer . FILTER (?product = :product-61) }
Step 4: Use sequence path to omit the binding of the?order variable.
# All customers that bought product-61 # Distinct eliminates duplicates in case the same customer bought a product more than once SELECT DISTINCT * WHERE { ?product ^:hasProduct/:belongsToOrder/:hasCustomer ?customer . FILTER (?product = :product-61) } ORDER BY ?product
Step 5: Bind the filter value to the subject variable directly.
SELECT DISTINCT * WHERE { :product-61 ^:hasProduct/:belongsToOrder/:hasCustomer ?customer . } ORDER BY ?product
Step 6: And finally, omit the binding of the?customer variable and invert the full path back to product.
SELECT (COUNT (1) AS ?Count) WHERE { :product-2 ^:hasProduct/:belongsToOrder/:hasCustomer/ ^(^:hasProduct/:belongsToOrder/:hasCustomer) :product-61 }
Note the panel on the right, which contains details on the node selected: order-10370.
order-10370
Zooming in you you can see that the order-10370 has employee, customer, shipper and two order details, which contain products with their respective categories and suppliers.
employee
shipper
order details
products
categories
suppliers
As stated previously, the Northwind Notebook has been planned to be added to the Amazon Neptune Jupyter Notebooks sample applications under the following location: 01-Neptune-Database > 03-Sample-Applications > 07-Northwind-Use-Case
01-Neptune-Database > 03-Sample-Applications > 07-Northwind-Use-Case
You can download the resources used in this demonstration from the following locations in the Northwind GitHub repository:
%load
