Graph Modeling/Analytics Concepts and Integrating Graph with Relational Data

Learning outcomes

ServiceHub wants to answer relationship-heavy questions: “Which technician is assigned to which open work order?”, later expanding toward referrals, assets, dependencies and multi-hop paths. Repeated self-joins and recursive SQL remain valid relational tools, but a property graph exposes relational rows as vertices (entities), edges (relationships), labels and properties, letting SQL express graph patterns directly. In 26ai, SQL property graphs and GRAPH_TABLE align with SQL:2023 graph syntax.

1. Property graph is a query/model layer over relational objects

A property graph has vertices and directed/undirected edges. Both can carry labels and key/value properties. Oracle SQL property graphs reference tables/views as graph element tables. The relational tables remain the data of record; graph DDL defines keys, labels, exposed properties and source/destination relationships.

2. Privilege boundary

GRANT CREATE PROPERTY GRAPH TO servicehub_owner;

To create a graph in another schema requires CREATE ANY PROPERTY GRAPH, which is broader and unnecessary for this local lab. Query users can receive READ/SELECT on the graph without automatically receiving direct SELECT on every base table.

3. Build relational vertices and edges

BEGIN EXECUTE IMMEDIATE 'DROP TABLE sh24_assignment PURGE';EXCEPTION WHEN OTHERS THEN IF SQLCODE != -942 THEN RAISE; END IF; END;/BEGIN EXECUTE IMMEDIATE 'DROP TABLE sh24_graph_order PURGE';EXCEPTION WHEN OTHERS THEN IF SQLCODE != -942 THEN RAISE; END IF; END;/BEGIN EXECUTE IMMEDIATE 'DROP TABLE sh24_graph_tech PURGE';EXCEPTION WHEN OTHERS THEN IF SQLCODE != -942 THEN RAISE; END IF; END;/CREATE TABLE sh24_graph_tech (  technician_id NUMBER PRIMARY KEY,  tech_name VARCHAR2(80) NOT NULL,  region_code VARCHAR2(8) NOT NULL);CREATE TABLE sh24_graph_order (  work_order_id NUMBER PRIMARY KEY,  status_code VARCHAR2(12) NOT NULL,  priority_code NUMBER NOT NULL);CREATE TABLE sh24_assignment (  assignment_id NUMBER PRIMARY KEY,  technician_id NUMBER NOT NULL    REFERENCES sh24_graph_tech(technician_id),  work_order_id NUMBER NOT NULL    REFERENCES sh24_graph_order(work_order_id),  assigned_at TIMESTAMP DEFAULT SYSTIMESTAMP NOT NULL,  CONSTRAINT sh24_assignment_uq    UNIQUE(technician_id,work_order_id));INSERT INTO sh24_graph_tech VALUES(101,'Nigar Aliyeva','AZ-N');INSERT INTO sh24_graph_tech VALUES(102,'Kamran Hasanov','AZ-N');INSERT INTO sh24_graph_order VALUES(9001,'OPEN',1);INSERT INTO sh24_graph_order VALUES(9002,'CLOSED',3);INSERT INTO sh24_assignment(assignment_id,technician_id,work_order_id)VALUES(1,101,9001);INSERT INTO sh24_assignment(assignment_id,technician_id,work_order_id)VALUES(2,102,9002);COMMIT;

4. Create the SQL property graph

CREATE PROPERTY GRAPH sh24_service_graph  VERTEX TABLES (    sh24_graph_tech AS technicians      KEY(technician_id)      LABEL technician      PROPERTIES(technician_id,tech_name,region_code),    sh24_graph_order AS orders      KEY(work_order_id)      LABEL work_order      PROPERTIES(work_order_id,status_code,priority_code)  )  EDGE TABLES (    sh24_assignment AS assigned_to      KEY(assignment_id)      SOURCE KEY(technician_id)        REFERENCES technicians(technician_id)      DESTINATION KEY(work_order_id)        REFERENCES orders(work_order_id)      LABEL assigned_to      PROPERTIES(assignment_id,assigned_at)  );

The graph maps rows to graph elements; it does not copy the rows into a separate graph database.

5. Query a graph pattern with GRAPH_TABLE

SELECT *FROM GRAPH_TABLE(  sh24_service_graph  MATCH    (t IS technician)    -[a IS assigned_to]->    (w IS work_order)  WHERE w.status_code='OPEN'  COLUMNS(    t.technician_id AS technician_id,    t.tech_name     AS technician_name,    w.work_order_id AS work_order_id,    w.priority_code AS priority_code  ));

MATCH describes graph structure; WHERE filters properties; COLUMNS turns graph solutions back into ordinary SQL rows. That result can be joined to relational, JSON, XML or spatial expressions in the same SQL statement.

6. Compare with ordinary relational SQL

SELECT  t.technician_id,  t.tech_name AS technician_name,  w.work_order_id,  w.priority_codeFROM sh24_graph_tech tJOIN sh24_assignment a  ON a.technician_id=t.technician_idJOIN sh24_graph_order w  ON w.work_order_id=a.work_order_idWHERE w.status_code='OPEN';

For one simple relationship, the relational join is arguably clearer. Graph becomes more compelling when the query itself is naturally a path/pattern—variable-length paths, multiple relationship types, graph algorithms or relationship-centric exploration. The right choice is query clarity and measured performance, not data-model branding.

7. Inspect graph metadata

SELECT  graph_name,  graph_mode,  allows_mixed_types,  inmemoryFROM user_property_graphsWHERE graph_name='SH24_SERVICE_GRAPH';SELECT  graph_name,  element_name,  object_name,  element_kindFROM user_pg_elementsWHERE graph_name='SH24_SERVICE_GRAPH'ORDER BY element_kind,element_name;SELECT  graph_name,  label_nameFROM user_pg_labelsWHERE graph_name='SH24_SERVICE_GRAPH'ORDER BY label_name;

These dictionary views are themselves new 26ai surfaces. They prove graph metadata exists; they do not prove the graph model is appropriate for your workload.

8. Deliberately wrong: create graph edges without relational integrity

If the application maintains a separate copied edge store without foreign-key/business validation, relationships can point to missing technicians or work orders. In this lab the edge table itself prevents that:

INSERT INTO sh24_assignment(  assignment_id,technician_id,work_order_id)VALUES(3,999,9001);-- Expected:-- ORA-02291: integrity constraint ... violated - parent key not found

Graph syntax does not eliminate data-integrity design. The safe architecture keeps identity/referential constraints in the relational source where possible, then projects graph semantics over them.

9. Multi-hop awareness without overcomplicating the first lab

GRAPH_TABLE supports richer path patterns, including variable-length traversal. Before replacing recursive CONNECT BY/recursive subquery factoring, prototype the actual path depth, branching factor, filters, returned properties and concurrency. A graph expression that is clearer can still be slower for a particular physical workload—or vice versa.

10. RDF/OWL is a different graph model

RDF (Resource Description Framework) represents subject-predicate-object statements identified by URIs; OWL and RDFS add semantic vocabularies/inference. Property graphs instead attach properties directly to vertices/edges and are usually simpler for application relationships. Oracle supports both. Current licensing lists Property Graph and RDF Graph Technologies across all listed offerings; RDF support has specific setup/install prerequisites and restricted-use Partitioning rights for the RDF/property-graph schema infrastructure.

11. Graph analytics component boundary

SQL property graph creation/query runs in Oracle AI Database. Advanced graph analytics and Graph Studio can involve Oracle Graph Server and Client/PGX; current release 26.3 is a separately installed component for user-managed deployments, while Autonomous can provide Graph Studio as part of the service environment. This chapter does not require that external topology.

12. Cleanup

DROP PROPERTY GRAPH sh24_service_graph;DROP TABLE sh24_assignment PURGE;DROP TABLE sh24_graph_order PURGE;DROP TABLE sh24_graph_tech PURGE;

13. Production judgment

Use a SQL property graph when relationship/path queries are central and projecting existing relational truth avoids duplicate pipelines/security/recovery. Keep simple joins simple. For graph algorithms or very large graph-native workloads, measure SQL graph/Graph Server against specialized platforms using equivalent data, traversal semantics and concurrency.

Current 26ai property graphs are available in Free under the current licensing matrix; CREATE PROPERTY GRAPH privilege is required. No restart, pack or COMPATIBLE change is needed for the lab. RDF is supported but has separate semantic/setup concepts. Lesson 5 now forces the architecture question the chapter has been building toward: when is convergence actually cheaper and when does a specialist system earn its place?

Authoritative references

• CREATE PROPERTY GRAPH — SQL property graph DDL/privileges
• GRAPH_TABLE Operator — SQL:2023 graph pattern queries
• USER_PROPERTY_GRAPHS — 26ai graph dictionary
• Oracle Property Graph — Graph Server/Client and SQL graph overview
• Licensing Information — Property Graph/RDF licensing