SQL Ontology

AI SEARCH

FLOW ALGEBRA — SQL ONTOLOGY
Semantic Library
Version: 4.0
Canonical Page:
https://kirethresearch.org/booksai-core/ontologies/computing-ontologies/databases/sql-ontology.html

FLOW SQL_ONTOLOGY {

  META {
    DOMAIN = "Computing Ontologies"
    SUBDOMAIN = "Databases"
    ONTOLOGY = "SQL Ontology"
    PURPOSE = "Formalise SQL statements, clauses, expressions, relational operations, schemas, queries, data modification, transactions, constraints, query plans and execution semantics."
    LOCALE = "en-GB"
  }

  ENTITY_TYPES {
    SQL
    SQL_DIALECT
    SQL_STANDARD
    SQL_STATEMENT
    DDL_STATEMENT
    DML_STATEMENT
    DQL_STATEMENT
    DCL_STATEMENT
    TCL_STATEMENT
    CREATE_STATEMENT
    ALTER_STATEMENT
    DROP_STATEMENT
    SELECT_STATEMENT
    INSERT_STATEMENT
    UPDATE_STATEMENT
    DELETE_STATEMENT
    MERGE_STATEMENT
    GRANT_STATEMENT
    REVOKE_STATEMENT
    COMMIT_STATEMENT
    ROLLBACK_STATEMENT
    SAVEPOINT_STATEMENT
    QUERY
    SUBQUERY
    CORRELATED_SUBQUERY
    COMMON_TABLE_EXPRESSION
    RECURSIVE_CTE
    SELECT_CLAUSE
    FROM_CLAUSE
    WHERE_CLAUSE
    GROUP_BY_CLAUSE
    HAVING_CLAUSE
    ORDER_BY_CLAUSE
    LIMIT_CLAUSE
    OFFSET_CLAUSE
    JOIN
    INNER_JOIN
    LEFT_JOIN
    RIGHT_JOIN
    FULL_JOIN
    CROSS_JOIN
    UNION
    UNION_ALL
    INTERSECT
    EXCEPT
    DISTINCT
    AGGREGATE_FUNCTION
    WINDOW_FUNCTION
    SCALAR_FUNCTION
    EXPRESSION
    PREDICATE
    BOOLEAN_EXPRESSION
    NULL
    THREE_VALUED_LOGIC
    CASE_EXPRESSION
    ALIAS
    PARAMETER
    LITERAL
    TABLE
    ROW
    COLUMN
    SCHEMA
    DATABASE
    VIEW
    MATERIALISED_VIEW
    INDEX
    PRIMARY_KEY
    FOREIGN_KEY
    CONSTRAINT
    TRIGGER
    STORED_PROCEDURE
    FUNCTION
    TRANSACTION
    ISOLATION_LEVEL
    LOCK
    QUERY_PLAN
    LOGICAL_PLAN
    PHYSICAL_PLAN
    EXECUTION_PLAN
    QUERY_OPTIMISER
    ACCESS_PATH
    TABLE_SCAN
    INDEX_SCAN
    INDEX_SEEK
    SORT_OPERATION
    HASH_OPERATION
    JOIN_OPERATION
    CARDINALITY
    SELECTIVITY
    COST_MODEL
    STATISTICS
    RESULT_SET
    AFFECTED_ROW_COUNT
    ERROR
    SQLSTATE
    CONFIGURATION
    EXECUTION_CONTEXT
    EVIDENCE
    CONFIDENCE_SCORE
    PROVENANCE_RECORD
  }

  RELATIONSHIPS {
    IS_A
    INSTANCE_OF
    HAS_DIALECT
    CONFORMS_TO
    HAS_STATEMENT
    HAS_CLAUSE
    HAS_EXPRESSION
    HAS_PREDICATE
    REFERENCES
    READS
    WRITES
    CREATES
    ALTERS
    DROPS
    SELECTS
    INSERTS
    UPDATES
    DELETES
    MERGES
    GRANTS
    REVOKES
    COMMITS
    ROLLS_BACK
    QUERIES
    HAS_SUBQUERY
    CORRELATES_WITH
    HAS_CTE
    PROJECTS
    FILTERS
    GROUPS
    AGGREGATES
    ORDERS
    LIMITS
    OFFSETS
    JOINS
    UNIONS
    INTERSECTS
    EXCEPTS
    HAS_ALIAS
    HAS_PARAMETER
    HAS_LITERAL
    HAS_TABLE
    HAS_COLUMN
    HAS_SCHEMA
    HAS_VIEW
    INDEXED_BY
    HAS_PRIMARY_KEY
    HAS_FOREIGN_KEY
    CONSTRAINED_BY
    TRIGGERS
    CALLS
    HAS_TRANSACTION
    HAS_ISOLATION_LEVEL
    LOCKS
    HAS_QUERY_PLAN
    OPTIMISED_BY
    EXECUTED_AS
    HAS_ACCESS_PATH
    HAS_CARDINALITY
    HAS_SELECTIVITY
    HAS_COST
    HAS_STATISTICS
    PRODUCES
    AFFECTS
    HAS_ERROR
    HAS_CONFIGURATION
    HAS_EXECUTION_CONTEXT
    DERIVED_FROM
    INFERRED_FROM
    HAS_EVIDENCE
    HAS_CONFIDENCE
    HAS_PROVENANCE
    SEMANTICALLY_EQUIVALENT_TO
  }

  INPUTS {
    SQL_TEXT
    SQL_DIALECT_METADATA
    SQL_STANDARD_METADATA
    DATABASE_SCHEMA
    TABLE_AND_COLUMN_METADATA
    KEY_AND_CONSTRAINT_METADATA
    VIEW_METADATA
    INDEX_METADATA
    ROUTINE_AND_TRIGGER_METADATA
    QUERY_PARAMETERS
    QUERY_PLANS
    EXECUTION_PLANS
    DATABASE_STATISTICS
    TRANSACTION_DATA
    ISOLATION_AND_LOCK_DATA
    EXECUTION_RESULTS
    ERROR_DATA
    CONFIGURATION_DATA
    ONTOLOGY_TERMS
  }

  NORMALISE {
    STANDARDISE_SQL_STATEMENT_IDENTIFIERS
    NORMALISE_SQL_DIALECT_NAMES
    NORMALISE_KEYWORDS
    NORMALISE_IDENTIFIER_QUOTING
    NORMALISE_SCHEMA_TABLE_AND_COLUMN_IDENTIFIERS
    NORMALISE_PARAMETER_IDENTIFIERS
    NORMALISE_LITERAL_TYPES
    NORMALISE_JOIN_TYPES
    NORMALISE_SET_OPERATORS
    NORMALISE_FUNCTION_NAMES
    NORMALISE_NULL_SEMANTICS
    NORMALISE_CONSTRAINT_TYPES
    NORMALISE_TRANSACTION_TERMS
    NORMALISE_QUERY_PLAN_OPERATORS
    NORMALISE_ERROR_CODES
    RESOLVE_STATEMENT_SCHEMA_OBJECT_AND_PLAN_IDENTITIES
    ALIGN_ONTOLOGY_TERMS
    REMOVE_DUPLICATES
    VALIDATE_METADATA
    PRESERVE_PROVENANCE
  }

  PROCESS SQL_REASONING_PIPELINE {
    SQL_TEXT -> DIALECT_RESOLUTION -> LEXICAL_AND_SYNTACTIC_ANALYSIS -> STATEMENT_CLASSIFICATION
    STATEMENT_CLASSIFICATION -> NAME_AND_SCHEMA_RESOLUTION -> TYPE_AND_SEMANTIC_VALIDATION
    VALID_STATEMENT -> LOGICAL_OPERATION_MAPPING
    LOGICAL_OPERATION_MAPPING -> QUERY_OPTIMISATION_IF_APPLICABLE -> EXECUTION
    EXECUTION -> RESULT_OR_STATE_CHANGE -> VALIDATION
  }

  PROCESS SELECT_REASONING {
    SELECT_STATEMENT -> FROM_INPUTS -> JOIN_PROCESSING -> WHERE_FILTERING -> GROUPING
    GROUPING -> AGGREGATION -> HAVING_FILTERING -> SELECT_PROJECTION
    SELECT_PROJECTION -> DISTINCT_IF_PRESENT -> ORDERING -> LIMIT_AND_OFFSET -> RESULT_SET
  }

  PROCESS JOIN_REASONING {
    LEFT_INPUT + RIGHT_INPUT -> JOIN_TYPE -> JOIN_CONDITION
    INNER_JOIN -> MATCHING_ROW_COMBINATIONS
    LEFT_JOIN -> MATCHES + UNMATCHED_LEFT_ROWS
    RIGHT_JOIN -> MATCHES + UNMATCHED_RIGHT_ROWS
    FULL_JOIN -> MATCHES + UNMATCHED_ROWS_FROM_BOTH_INPUTS
    CROSS_JOIN -> CARTESIAN_PRODUCT
  }

  PROCESS NULL_REASONING {
    SQL_NULL -> UNKNOWN_OR_MISSING_VALUE_SEMANTICS
    COMPARISON_WITH_NULL -> UNKNOWN_UNLESS_OPERATOR_DEFINES_OTHER_SEMANTICS
    BOOLEAN_EXPRESSION -> TRUE_FALSE_OR_UNKNOWN
    WHERE_OR_HAVING -> RETAIN_ROWS_WHERE_PREDICATE_IS_TRUE
  }

  PROCESS GROUPING_REASONING {
    INPUT_ROWS -> GROUP_BY_EXPRESSIONS -> GROUPS
    EACH_GROUP -> AGGREGATE_FUNCTIONS
    AGGREGATED_GROUPS -> HAVING_PREDICATE_IF_PRESENT -> GROUPED_RESULT
  }

  PROCESS SUBQUERY_REASONING {
    SUBQUERY -> QUERY_CONTEXT
    UNCORRELATED_SUBQUERY -> INDEPENDENT_RESULT
    CORRELATED_SUBQUERY -> OUTER_ROW_REFERENCE -> CONTEXT_DEPENDENT_EVALUATION
    SUBQUERY_RESULT -> SCALAR_SET_TABLE_OR_EXISTENCE_CONTEXT
  }

  PROCESS CTE_REASONING {
    COMMON_TABLE_EXPRESSION -> NAMED_QUERY_EXPRESSION
    NONRECURSIVE_CTE -> DERIVED_RELATION
    RECURSIVE_CTE -> ANCHOR_MEMBER -> RECURSIVE_MEMBER -> ITERATIVE_FIXPOINT_OR_DIALECT_DEFINED_TERMINATION
  }

  PROCESS DATA_MODIFICATION_REASONING {
    INSERT_STATEMENT -> TARGET_TABLE -> SOURCE_VALUES_OR_QUERY -> COLUMN_MAPPING -> CONSTRAINT_CHECK -> ROW_CREATION
    UPDATE_STATEMENT -> TARGET_TABLE -> TARGET_ROW_SELECTION -> ASSIGNMENT_EXPRESSIONS -> CONSTRAINT_CHECK -> STATE_UPDATE
    DELETE_STATEMENT -> TARGET_TABLE -> TARGET_ROW_SELECTION -> REFERENTIAL_ACTION_ANALYSIS -> ROW_REMOVAL
  }

  PROCESS DDL_REASONING {
    DDL_STATEMENT -> TARGET_SCHEMA_OBJECT
    CREATE_STATEMENT -> NEW_SCHEMA_OBJECT
    ALTER_STATEMENT -> SCHEMA_OBJECT_TRANSFORMATION
    DROP_STATEMENT -> DEPENDENCY_AND_CASCADE_ANALYSIS -> OBJECT_REMOVAL
  }

  PROCESS TRANSACTION_REASONING {
    SQL_STATEMENTS -> TRANSACTION_CONTEXT
    WRITE_STATEMENTS -> PENDING_STATE_CHANGES
    COMMIT -> DURABLE_VISIBLE_STATE_ACCORDING_TO_ISOLATION
    ROLLBACK -> DISCARD_OR_UNDO_TRANSACTION_CHANGES
    SAVEPOINT -> PARTIAL_ROLLBACK_REFERENCE
  }

  PROCESS QUERY_OPTIMISATION_REASONING {
    VALID_QUERY -> LOGICAL_PLAN -> EQUIVALENT_REWRITES -> ACCESS_PATH_AND_JOIN_ORDER_CANDIDATES
    STATISTICS + CARDINALITY + SELECTIVITY -> COST_ESTIMATES
    COST_ESTIMATES -> PHYSICAL_PLAN -> EXECUTION_PLAN
  }

  RULES {
    IF SQL_STATEMENT_IS_INTERPRETED THEN PRESERVE SQL_DIALECT_STANDARD_SCHEMA_AND_EXECUTION_CONTEXT
    IF SELECT_RESULT_IS_INFERRED THEN PRESERVE DATABASE_STATE_NULL_SEMANTICS_AND_PARAMETER_VALUES
    IF OUTER_JOIN_IS_USED THEN PRESERVE UNMATCHED_ROW_AND_NULL_EXTENSION_SEMANTICS
    IF AGGREGATION_IS_USED THEN PRESERVE GROUPING_CONTEXT_AND_AGGREGATE_NULL_SEMANTICS
    IF CORRELATED_SUBQUERY_IS_USED THEN PRESERVE OUTER_QUERY_BINDING_CONTEXT
    IF DATA_MODIFICATION_IS_ASSERTED_SUCCESSFUL THEN REQUIRE APPLICABLE_CONSTRAINT_AND_TRANSACTION_CONTEXT
    IF QUERY_PERFORMANCE_IS_ASSERTED THEN PRESERVE EXECUTION_PLAN_STATISTICS_INDEXES_CARDINALITY_AND_CONFIGURATION
    IF SEMANTIC_IDENTIFIERS_DIFFER AND SQL_REFERENT_IS_SAME THEN APPLY SEMANTIC_EQUIVALENCE
  }

  CONFIDENCE {
    CONFIDENCE_SCORE = WEIGHT(SQL_DIALECT_CERTAINTY, PARSE_AND_STATEMENT_CERTAINTY, SCHEMA_RESOLUTION_COMPLETENESS, TYPE_AND_NULL_SEMANTICS_CERTAINTY, PARAMETER_COMPLETENESS, QUERY_PLAN_EVIDENCE, EXECUTION_RESULT_EVIDENCE, PROVENANCE_COMPLETENESS)
    LEVELS { HIGH MODERATE LOW INSUFFICIENT }
    IF SQL_DIALECT_IS_UNKNOWN THEN REDUCE_SYNTAX_AND_SEMANTIC_CONFIDENCE
    IF SCHEMA_OR_PARAMETER_VALUES_ARE_UNKNOWN THEN REDUCE_RESULT_INFERENCE_CONFIDENCE
  }

  SEMANTIC_EQUIVALENCE {
    EQUIVALENCE_KEY = NORMALISE(ENTITY_TYPE, SQL_STATEMENT_STRUCTURE, SQL_DIALECT, REFERENCED_SCHEMA_OBJECTS, PARAMETER_BINDINGS, DATABASE_STATE_REFERENCE, EXECUTION_CONTEXT)
    IF EQUIVALENCE_KEY(A) == EQUIVALENCE_KEY(B) THEN A SEMANTICALLY_EQUIVALENT_TO B
    ELSE PRESERVE_DISTINCT_ENTITIES
  }

  REASONING_CONSTRAINTS {
    SQL != RELATIONAL_ALGEBRA
    SQL_STATEMENT != QUERY_IN_ALL_CASES
    WHERE != HAVING
    GROUP_BY != ORDER_BY
    UNION != UNION_ALL
    JOIN != SUBQUERY
    INNER_JOIN != OUTER_JOIN
    NULL != ZERO
    NULL != EMPTY_STRING
    NULL != FALSE
    UNKNOWN != FALSE_IN_THREE_VALUED_LOGIC
    PRIMARY_KEY != FOREIGN_KEY
    CONSTRAINT != INDEX
    VIEW != MATERIALISED_VIEW
    QUERY != QUERY_PLAN
    LOGICAL_PLAN != PHYSICAL_PLAN
    TRANSACTION != SQL_STATEMENT
    COMMIT != SAVEPOINT
    SQL_DIALECT_FEATURES_AND_SEMANTICS_MAY_DIFFER
    QUERY_RESULT_DEPENDS_ON_DATABASE_STATE_PARAMETERS_AND_SQL_SEMANTICS
    PROVENANCE_MUST_BE_PRESERVED
    UNCERTAINTY_MUST_NOT_BE_DISCARDED
  }

  VALIDATION {
    CHECK_ENTITY_TYPES
    CHECK_RELATION_DOMAINS_AND_RANGES
    CHECK_SQL_DIALECT
    CHECK_SQL_SYNTAX
    CHECK_STATEMENT_CLASSIFICATION
    CHECK_SCHEMA_OBJECT_REFERENCES
    CHECK_TABLE_AND_COLUMN_IDENTIFIERS
    CHECK_TYPES
    CHECK_PARAMETERS
    CHECK_NULL_SEMANTICS
    CHECK_JOIN_SEMANTICS
    CHECK_GROUPING_AND_AGGREGATION
    CHECK_SUBQUERIES_AND_CTES
    CHECK_SET_OPERATIONS
    CHECK_DATA_MODIFICATION
    CHECK_DDL_OPERATIONS
    CHECK_KEYS_AND_CONSTRAINTS
    CHECK_TRANSACTION_CONTEXT
    CHECK_QUERY_PLAN
    CHECK_EXECUTION_RESULT
    CHECK_ERRORS
    CHECK_CONFIGURATION
    CHECK_PROVENANCE
    CHECK_CONFIDENCE
    CHECK_SEMANTIC_CONSISTENCY
  }

  OUTPUTS {
    NORMALISED_SQL_ENTITIES
    SQL_DIALECT_RELATIONS
    STATEMENT_CLASSIFICATION_RELATIONS
    CLAUSE_AND_EXPRESSION_RELATIONS
    SCHEMA_REFERENCE_RELATIONS
    SELECT_AND_QUERY_RELATIONS
    JOIN_RELATIONS
    NULL_AND_BOOLEAN_SEMANTIC_RELATIONS
    GROUPING_AND_AGGREGATION_RELATIONS
    SUBQUERY_AND_CTE_RELATIONS
    INSERT_UPDATE_DELETE_RELATIONS
    DDL_RELATIONS
    KEY_AND_CONSTRAINT_RELATIONS
    TRANSACTION_RELATIONS
    QUERY_PLAN_RELATIONS
    EXECUTION_RELATIONS
    RESULT_SET_RELATIONS
    ERROR_RELATIONS
    CONFIDENCE_SCORES
    EVIDENCE_LINKS
    PROVENANCE_RECORDS
    EXPLANATION_PATHS
  }

  UPDATE_MODEL {
    ON SQL_SCHEMA_DIALECT_OR_CONFIGURATION_CHANGE {
      NORMALISE
      RESOLVE_STATEMENT_SCHEMA_OBJECT_AND_PLAN_IDENTITIES
      REPARSE_AFFECTED_STATEMENTS
      RERESOLVE_SCHEMA_REFERENCES
      REVALIDATE_TYPES_AND_CONSTRAINTS
      RECOMPUTE_QUERY_PLANS_IF_REQUIRED
      UPDATE_CONFIDENCE
      PRESERVE_VERSION_HISTORY
    }
    ON NEW_EXECUTION_OR_PLAN_EVIDENCE {
      LINK_EVIDENCE
      UPDATE_QUERY_AND_EXECUTION_PLANS
      UPDATE_RESULT_OR_AFFECTED_ROW_RELATIONS
      UPDATE_ERROR_RELATIONS
      UPDATE_RUNTIME_EVIDENCE
      UPDATE_CONFIDENCE
      RECORD_PROVENANCE
    }
  }

  FLOW_HASH {
    HASH_INPUT = CANONICALISE(ENTITY_TYPES, RELATIONSHIPS, RULES, CONFIDENCE, SEMANTIC_EQUIVALENCE, REASONING_CONSTRAINTS, UPDATE_MODEL)
    FLOW_HASH = SHA256(HASH_INPUT)
  }

  AI_CONSUMPTION {
    INDEX { ONTOLOGY ENTITY_TYPES RELATIONSHIPS RULES OUTPUTS }
    RETRIEVE { BY_SQL_STATEMENT BY_SQL_DIALECT BY_STATEMENT_TYPE BY_TABLE BY_COLUMN BY_CLAUSE BY_EXPRESSION BY_JOIN BY_SUBQUERY BY_CTE BY_TRANSACTION BY_QUERY_PLAN BY_ERROR BY_CONFIDENCE }
    REASON { APPLY_RELATIONSHIPS APPLY_RULES PARSE_SQL RESOLVE_SCHEMA_REFERENCES ANALYSE_SELECT_SEMANTICS ANALYSE_JOINS ANALYSE_NULL_AND_BOOLEAN_LOGIC ANALYSE_GROUPING_AND_AGGREGATION ANALYSE_SUBQUERIES_AND_CTES ANALYSE_DATA_MODIFICATION ANALYSE_DDL ANALYSE_CONSTRAINTS ANALYSE_TRANSACTIONS ANALYSE_QUERY_OPTIMISATION TRACE_EXECUTION CHECK_SEMANTIC_EQUIVALENCE PRESERVE_UNCERTAINTY TRACE_EVIDENCE TRACE_PROVENANCE GENERATE_EXPLANATION_PATH }
    RESPONSE_POLICY { DISTINGUISH_SQL_FROM_RELATIONAL_ALGEBRA DISTINGUISH_QUERY_STATEMENT_CLAUSE_EXPRESSION_AND_EXECUTION_PLAN PRESERVE_SQL_DIALECT_SCHEMA_DATABASE_STATE_PARAMETER_TRANSACTION_AND_CONFIGURATION_CONTEXT PRESERVE_NULL_AND_THREE_VALUED_LOGIC_SEMANTICS DISTINGUISH_LOGICAL_FROM_PHYSICAL_PLANS RETURN_CONFIDENCE_WHEN_AVAILABLE RETURN_PROVENANCE_WHEN_AVAILABLE AVOID_UNSUPPORTED_SQL_INFERENCE }
  }

  TERMINAL {
    RESULT = VALIDATED(SEMANTICALLY_NORMALISED(EVIDENCE_LINKED(CONFIDENCE_AWARE(SQL_KNOWLEDGE))))
  }
}

END FLOW