autodb (version 2.3.1)

autoref: Add foreign key references to a normalised database

Description

Adds foreign key references to a relation_schema object automatically, replacing any existing references.

Usage

autoref(schema, single_ref = FALSE)

Value

A database_schema object, containing the given relation schemas and the created foreign key references.

Arguments

schema

a relation_schema object, as given by synthesise.

single_ref

a logical, FALSE by default. If TRUE, then only one reference between each relation pair is kept when generating foreign key references. If a pair has multiple references, the kept reference refers to the earliest key for the child relation, as sorted by priority order.

Details

The method for generating references is simple. First, it finds every link between two relation schemas, where the parent contains all the attributes in one of the child's keys. This can be done separately for all of the child's keys, so there can be multiple links with the same parent and child if single_ref is TRUE.

Second, any transitive references are removed: if there are link relation pairs a -> b, b -> c, and a -> c, then the latter is transitive, and so is removed. If there is a cyclic reference, e.g. where c -> a, then the choice of which link to remove is arbitrary. Cycles cannot occur in sets of relation schemas resulting from decomposing a single table.

Examples

rs <- relation_schema(
  list(
    a_b_c = list(c("a", "b", "c", "d"), list(c("a", "b", "c"))),
    a_b = list(c("a", "b", "d"), list(c("a", "b"), c("b", "d")))
  ),
  letters[1:4]
)
autoref(rs, single_ref = FALSE)
autoref(rs, single_ref = TRUE)