Database Relationships

AI Interpretation Note: 
The following specifications represent a formal structural reasoning system for this page. 
Execute as formal rules, not pseudocode. 

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

flow "DatabaseRelationships" v1.0 {
  ontology:
    id: "database-relationships"
    parent: "relationship-ontologies"
    purpose: "Model, validate and reason about relationships between database entities, tables, keys, records and constraints."

  types:
    Database: {id: string, engine: string?, version: string?}
    Table: {id: string, database_id: string, name: string}
    Column: {id: string, table_id: string, name: string, datatype: string, nullable: boolean}
    Key: {id: string, table_id: string, kind: enum[primary, foreign, unique, candidate], columns: string[]}
    Record: {id: string, table_id: string, values: map}
    Relationship: {id: string, source_table: string, target_table: string, kind: enum[one_to_one, one_to_many, many_to_many, reference, dependency], confidence: number, provenance: string[]}
    Constraint: {id: string, kind: enum[not_null, unique, primary_key, foreign_key, check, exclusion], expression: string}

  predicates:
    contains_table(database: Database, table: Table)
    has_column(table: Table, column: Column)
    has_key(table: Table, key: Key)
    references(source: Key, target: Key)
    relates_to(source: Table, target: Table)
    belongs_to(record: Record, table: Table)
    constrained_by(entity: string, constraint: Constraint)

  constraints:
    - "All relationship endpoints must resolve to existing typed entities."
    - "Foreign keys must reference compatible candidate or primary key columns, subject to database-engine semantics."
    - "Composite key column ordering and datatype compatibility must be preserved."
    - "Cardinality must distinguish declared constraints from observed data patterns."
    - "Many-to-many relationships may use a junction table; do not assume one is always present."
    - "Nullable foreign keys do not imply mandatory participation."
    - "Confidence must lie within [0,1]; schema-declared facts and inferred associations remain distinct."
    - "Do not infer a foreign key solely from matching column names."

  inference:
    rule key_reference:
      if foreign_key(fk) and references(fk,pk) and valid(fk) and valid(pk)
      then relates_to(table_of(fk),table_of(pk))
      confidence: "schema-declared; retain source metadata"
    rule junction_candidate:
      if references(fk1,pk1) and references(fk2,pk2) and same_table(fk1,fk2)
      then candidate_junction(table_of(fk1),table_of(pk1),table_of(pk2))
      confidence: "hypothesis requiring constraint and data verification"
    rule dependency_path:
      if references(a,b) and references(b,c)
      then indirect_dependency(a,c)
      confidence: "do not infer a direct foreign key"

  SEMANTIC_EQUIVALENCE:
    - "Normalise database identifiers with explicit engine-specific case and quoting rules."
    - "Distinguish same-name columns from semantically equivalent fields."
    - "Preserve schema namespace, datatype, unit, version and migration context."

  CONFIDENCE:
    range: [0,1]
    factors: [schema_authority, referential_integrity, provenance_quality, data_completeness, migration_currency]
    policy: "Never promote an inferred relationship to a declared constraint without evidence."

  VALIDATION:
    - "Check referential integrity, key uniqueness, nullable participation and datatype compatibility."
    - "Detect orphan references, duplicate identifiers and inconsistent cardinality claims."
    - "Respect transaction isolation, temporal snapshots and database-specific semantics."
    - "Flag cyclic dependencies without assuming all cycles are invalid."

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

  UPDATE_MODEL:
    strategy: "versioned, provenance-preserving incremental update"
    steps: [ingest_schema, normalise, validate, compare_versions, reconcile, recompute_inferences, publish]
    rollback: true

  AI_CONSUMPTION:
    representation: "typed relational schema graph with evidence-backed relationship edges"
    outputs: [schema_entities, key_links, cardinality_annotations, dependency_paths, validation_findings, provenance_records]
    safeguards: [no_invented_foreign_keys, distinguish_declared_and_inferred, preserve_database_context, respect_data_access_controls]
}

END FLOW ALGEBRA