Join Relationship Ontology

FLOW ALGEBRA
TITLE: Join Relationship Ontology
DOMAIN: Relationship Ontologies / Database Relationships
SOURCE: https://kirethresearch.org/booksai-core/ontologies/relationship-ontologies/database-relationships/join-relationship-ontology.html
VERSION: 1.0
AUTHORS: Mark Anthony Higgins — Sky (Recursive AI)
STATUS: Companion formalisation; source-page specification not independently verified.

flow "JoinRelationshipOntology" v1.0 {
  ontology:
    id: "join-relationship-ontology"
    parent: "database-relationships"
    purpose: "Represent, execute and validate relational joins with explicit keys, predicates, null semantics and provenance."

  types:
    Relation: {id: string, schema: string, attributes: string[], primary_key: string[]?, provenance: string}
    Attribute: {relation_id: string, name: string, datatype: string, nullable: bool}
    JoinPredicate: {left_expression: string, operator: enum[eq, ne, lt, le, gt, ge, custom], right_expression: string, null_policy: string}
    Join: {id: string, left_relation: string, right_relation: string, kind: enum[inner, left_outer, right_outer, full_outer, cross, semi, anti], predicates: JoinPredicate[], output_schema: string[], confidence: number, provenance: string[]}
    ResultRow: {values: map[string, any], source_row_ids: string[], null_extended: bool}

  predicates:
    joins(join: Join, left: Relation, right: Relation)
    matches(left_row: string, right_row: string, predicate: JoinPredicate)
    references(attribute: Attribute, target: Attribute)
    produces(join: Join, row: ResultRow)
    derived_from(row: ResultRow, source_row: string)

  constraints:
    - "Join endpoints and referenced attributes must resolve to existing typed relations and columns."
    - "Join expressions must be type-compatible or use explicitly documented coercions."
    - "INNER JOIN returns matched row pairs only; outer joins preserve the designated unmatched side(s) using null extension."
    - "CROSS JOIN requires no predicate and produces the Cartesian product under bag semantics."
    - "SEMI JOIN returns qualifying left rows without right-side column multiplication; ANTI JOIN returns left rows with no qualifying right match."
    - "SQL NULL comparisons follow three-valued logic; UNKNOWN is not a successful ON match."
    - "SQL joins use bag semantics unless DISTINCT or set semantics are explicitly requested."
    - "Join cardinality must not be assumed one-to-one solely from similarly named columns."
    - "Confidence must lie within [0, 1] and cannot override deterministic integrity checks."

  inference:
    rule inner_join_match:
      if joins(j,L,R) and j.kind == inner and matches(l,r,j.predicates)
      then produces(j, combine(l,r))
      confidence: "deterministic when source rows and predicates are validated"
    rule left_outer_unmatched:
      if joins(j,L,R) and j.kind == left_outer and no_matching_right_row(l,j.predicates)
      then produces(j, null_extend_right(l,R))
      confidence: "requires complete evaluation of eligible right rows"
    rule foreign_key_join:
      if references(L.fk,R.unique_key) and valid_foreign_key(L.fk,R.unique_key)
      then eligible_join_predicate(L.fk,eq,R.unique_key)
      confidence: "schema-constraint backed; does not assert row-level match without data"

  SEMANTIC_EQUIVALENCE:
    - "Normalise SQL dialect aliases and equivalent join expressions only when null and bag semantics are preserved."
    - "Do not equate INNER JOIN with OUTER JOIN, or SEMI JOIN with INNER JOIN under duplicate-producing conditions."
    - "Record join direction, predicates, coercions and output-column mappings."

  CONFIDENCE:
    range: [0,1]
    factors: [schema_reliability, key_integrity, source_completeness, type_compatibility, provenance_quality]
    policy: "Separate certainty of SQL evaluation from confidence in source data and inferred schema mappings."

  VALIDATION:
    - "Check relation and column existence, join-kind compatibility and predicate type safety."
    - "Verify key uniqueness before using cardinality-based optimisation or inference."
    - "Test NULL, duplicate-key, unmatched-row and empty-relation edge cases."
    - "Preserve row lineage and verify expected output schema and multiplicity."

  FLOW_HASH:
    algorithm: "SHA-256"
    input: "canonicalised ontology version, typed assertions, join rules and provenance"
    note: "Compute hash at execution; no hash value is claimed here."

  UPDATE_MODEL:
    strategy: "versioned, provenance-preserving incremental update"
    steps: [ingest, normalise, validate, compare, reconcile, recompute_join_dependencies, publish]
    rollback: true

  AI_CONSUMPTION:
    representation: "typed relational schema graph with executable join semantics"
    outputs: [join_edges, join_predicates, cardinality_annotations, result_lineage, validation_reports, provenance_records]
    safeguards: [no_unverified_key_assumptions, preserve_null_semantics, preserve_duplicate_multiplicity, prevent_unjustified_entity_merges]
}

END FLOW ALGEBRA