AQL Tutorial

Tasks <-> Skills <-> People


PREFACE - AQL and SQL

This AQL file interacts with a MySQL database running on catinf.com. To run the tutorial, it is necessary to download the MySQL JDBC driver. The following command (updated with the correct path to the MySQL JDBC driver jar file) then places that driver on the java classpath:

pragma setup_classpath = load_jars {
	"/Users/ryan/Desktop/mysql.jar"
}

INTRODUCTION

This file contains a sequence of models intended to illustrate the use of Algebraic Query Language (AQL) to formalize database concepts in a mathematically verifiable way. Each AQL file in this sequence will build on previous ideas, introducing AQL features in the context of conceptual models relating skills to tasks and people.

The following is a brief outline of the sequence of models and what the reader should take away from reviewing each model in the sequence.

This file introduces a series of AQL constructions and compares those constructions with analogous relational constructions where appropriate. We begin with a set of independent schemas and produce a single schema that is an an integrated assembly of the three source schemas. The following is a table of contents describing the structure of the sequence of AQL constructions and concepts developed in this file. The reader should not expect to understand this list at this point. Several passes through the tutorial may be required to fully grasp all that is presented. This file is an executable which when run will generate an execution graphical user interface where the results can be viewed. Similarly this file will be published to a series of web pages where the results of each construction will be displayed.

  1. typeside -- User defined kind for customization of data types.
  2. schema -- Extends the type side to define objects of kind schema.
  3. category -- Polymorphic abstraction that unifies many AQL concepts and constructions.
  4. schema as category -- Schemas interpreted through the abstract lens of categories.
  5. instance -- Extends the schema kind. Instance kinds can be thought of as the data that "fills" the schema.
  6. instance as category -- Instances interpreted through the abstract lens of categories.
  7. transform -- map from one instance to another of the same schema.
  8. the category of instances of a schema -- Instance kinds all typed by the same schema related by transforms as arrows.
  9. instance coproduct -- Universal construction that "adds" multiple instances associated with a schema.
  10. instance coequalizer -- Universal construction that uses a linking instance and a pair of transforms to merge records records within an instance.
  11. schema mappings, queries and the category of schemas -- Schema mappings define the arrows in the category of schemas. Queries generalize schema mappings and provide functionality similar to SQL queries.
  12. schema colimits -- A kind that unifies coproducts and coequalizers but in the context of the category of schemas rather than the category of instances of a schema. Generally formalizes a diagram of schemas connected by mappings that can be used to assemble source schemas into a single target schema.
  13. jdbc pragmas, imports and exports -- Integration of AQL with external data sources and targets. Pragmas are a kind that allows commands to be submitted to a JDBC driver to be executed against an external repository. Imports and exports are features the provide a mechanism for translating between traditional data repositories and AQL semantics.
  14. Migrating data between schemas -- This is the problem of Extracting - Transforming - Loading or ETL. This is where we tackle problems that involve moving data between schemas and the larger problem of bringing multiple source schemas together and integrating them.
  15. sigma --| delta --| pi -- The machinery AQL uses to move data around the enterprise. These mappings are the building blocks of the eval construction that moves data through AQL queries.

As we proceed we will draw analogies to relational constructions that may be more familiar to practicing database engineers. We open with a brief review of the essential features of relational algebra as they are related to AQL features.

The structural element that forms the foundation of relational algebra is the relvar. A relvar is a mathematical definition that roughly corresponds to the structure of a single table. Commercial implementations of relvars are table definitions based on a set of fixed types provided by the relational database management system (RDBMS) that define the domain over which each column of the table is allowed to vary. Having defined one or more table structures, constraints can be added to further restrict the values over which a particular column may vary.

In contrast, AQL provides a declarative structure that allows the user to define an algebraic definition of the type side. In its current implementation AQL defines this algebra by wrapping a selection of Java types and functions. In this sequence of files we will restrict the type side to a string type compatible with a MySQL Varchar. This will allow us to label records in a way that will provide human readable output associated with the sequence of models we produce. This will also allow us to integrate with an external RDBMS by exporting and importing various AQL constructions.

AQL models always begin with a type side declaration. The type side defines a foundation that can then be extended to define schemas.


Type Sides

Type sides are an AQL construction that extends the built in types typically found in RDBMSs and provides the possibility of introducing programming language features that can be used to enhance the power of a database. These features are roughly analogous to stored functions that can be invoked inline within an SQL statement. To illustrate how this capability works we will create a type side that exposes a string type which we label Varchar in anticipation of later integration with an external RDBMS. To this we add the Java string matches method to illustrate how Java functions can be exposed to the AQL framework. For more details we refer the reader to the examples available by selecting the dropdown in the upper right hand corner of the application. Specifically, the AQL Tutorial example.

typeside TypeSide = literal {
	java_types
		Bool = "java.lang.Boolean"
		Varchar = "java.lang.String"
	java_constants
		Bool = "return java.lang.Boolean.parseBoolean(input[0])"
		Varchar = "return input[0]"
	java_functions
		Matches : Varchar, Varchar -> Bool = "return input[0].matches(input[1])"
		true : Bool = "return true"
}

SCHEMAS

As mentioned above the schema is declared as an extension of the type side. Notice that in contrast with relational algebra there is no notion of an entity or relvar in isolation. The closest analog to a relvar in AQL is a schema with a single entity. With that in mind we proceed by creating a sequence of schemas, each with a single entity.

The first schema we create is minimal in that it contains a single entity Task with a single attribute that allows us to label a task. AQL contains a variety of constructions that build on on previously defined constructions. Following the programming language literature we will refer to such constructions as kinds. In AQL a kind is either a typeside, schema, instance, (schema) mapping, (instance) transform, query, graph, pragma, schema_colimit, or constraints.

By convention we prefix the schema name with the letter "s" to remind the reader when that name is encountered in the construction of a dependent kind that the object is of kind schema.

By clicking on the run button AQL will construct a runtime viewing window that has a list of each of the constructions created in this model in a pane on the left hand side of the window. A view of each construction is available by clicking on the construction of interest in the left hand pane.

Similarly this editor has a pane on the right hand side containing an outline of the constructions contained in this file. One can navigate to a construction by clicking on that construction in the outline.

schema sT = literal : TypeSide {
	entities
		Task
	attributes
		TName : Task -> Varchar
}

Now we create a slightly more interesting schema. This schema still consists of only one entity, but that entity now has a pair of attributes.

schema sSnT = literal : TypeSide {
	entities
		SkillNeededForTask
	attributes
		skill : SkillNeededForTask -> Varchar
		task : SkillNeededForTask -> Varchar
}

CATEGORIES INTRODUCED

Schemas as Categories

The third schema we introduce has two entities, Skill and SkillClass that define a simple two level taxonomy for skills. This brings into play additional sections as part of the declarative structure of a schema. First the foreign_keys section. In AQL, schemas as first class objects come equipped with all of the machinery for defining a schema as a kind. This includes declaration of dependencies between entities. These dependencies are given the name foreign key so as to be consistent with relational nomenclature.

Finally we have a third section that has no analog in SQL systems, the observation_equations section. To understand this section we must take time for a brief digression to introduce the abstract notion of a category.

AQL is motivated by a branch of mathematics known as category theory. The reader who is interested in systematic development of category theory should refer to any one of a number of excellent introductions. Here are several such references:

We begin with an intuitive description of categories and identify specific concrete examples as they relate to AQL. This is followed by a formal description of a category. A category consists of a set of objects connected by arrows. As such it is related to a directed graph where the objects are nodes and arrows are edges with direction indicated by their associated arrow. In addition to the properties of a directed graph each object of a category is required to have a distinguished arrow that identifies that object and whose source and target are equal to the object it identifies.

Let's consider the three schemas we have so far constructed, but with an empty type side (i.e. no attributes). Each could be viewed as an abstract category where the set of objects is the set of entities and its arrows are foreign keys. Hence schemas are the first example of an AQL construction that can be interpreted as a category.

With this in mind sT and sSnT are both one object categories. By virtue of the fact that the set of identity arrows and the set of objects are essentially the same set (technically isomorphic to each other) we can unambiguously refer to each identity arrow using the same label as its corresponding entity. Similarly sS (again neglecting attributes) is a two object category with one non-identity arrow, the foreign key fk_HasClass. This category is sometimes called the "arrow category:. In future examples we will see that more generally schemas with empty type sides are not strictly speaking categories, but rather presentations of categories. This means they have all the information necessary to generate a category through a process known as saturation.

Abstract categories are by design devoid of attachments to any specific context. In order to leverage the context free nature of category theory we need a mechanism by which we can anchor a category into a computational environment. Functional programming languages such as Haskell provide one strategy for doing this. AQL provides another strategy that is motivated by the capabilities of modern database management systems, so AQL extends the basic notion of an abstract category with a type side. We will rely on the above intuitive motivation and will not formally develop this idea here. The interested reader is referred to the research paper upon which the AQL implementation is based:

https://arxiv.org/abs/1602.03501

There is an additional feature of categories that is critical to this discussion and that lies at the heart of the expressive power of category theory. This the notion of a path. A category by definition contains all paths that can be generated by composing arrows head to tail. This process of generating all paths implied by arrow composition is the aforementioned saturation.

The order in which one chooses to compose arrows has no effect on the result so AQL provides a convenient syntax for indicating a path using the dot notation (elaborated below). Having added additional arrows for each path we now have the ability to declare two paths that begin and end on the same object to be equal and so the declarative structure of a category includes equations.

Path equations can be used to complete an intuitive explanation of the properties of identity arrows. Any path that includes an identity arrow is equal to the path obtained when that identity arrow is omitted. Considering arrow composition as analogous to addition we can think of identity arrows as analogous to zero, but rather than having one zero we have one for each object in the category. Category theorists make this analogy by saying that identity arrows are paths of length zero. To further strengthen this analogy it can be shown that the counting numbers under the binary operation of addition define a one object category. Each arrow of the category is a counting number. Zero is the identity arrow. Addition is arrow composition.

Category Definition

Here is a formal definition of a category following the presentation in Category Theory for Computing Sciences chapter 2.

We begin with a formal definition of a directed graph: A directed graph C is a pair of functions source: C_0 -> C_1 and target: C_0 -> C_1. The elements of C_0 we call nodes/objects and the elements of C_1 we call edges/arrows. An arrow path of length n in C is a sequence of arrows arranged head to tail as follows:

     f_n    f_n-1    ...     f_2     f_1
  . ----> . ----> .  ...  . ----> . ----> . 

For any adjacent pair of arrows in such a path we must have target(f_k) = source(f_k-1). Such adjacent pairs we call composable pairs. In such a graph one can define sets of paths of length n which we designate C_n.

The definition of a category extends the definition of a directed graph: A category is a graph C together with two functions c: C_2 -> C_1 and u: C_0 -> C_1 with properties C-1 through C-4 below. The function c we call composition, and if (f, g) is a composable pair, c(f,g) is written f.g and is called the composite of f followed by g. If A is an object of C, u(A) is denoted id_A or just A, which is called the identity of the object A.

We now introduce the fourth section in the schema declaration below, the observation_equations section. The observation_equations section is an example of a path equation. Notice that SCNameOnSkill is an arrow from Skill to Varchar. As such it is a path of length one. Similarly there is a path from Skill to Varchar that is a path of length two obtained by composing the foreign key arrow fk_HasClass with the attribute arrow SCName. The observation equation is a declaration that those two paths are equal.

The practical significance of this is far reaching, but in this case it provides us with a mechanism to denormalize our schema while guaranteeing that no inconsistencies will be allowed.

Much of the power of category theory comes from its polymorphic nature. Many different contexts can be interpreted through the abstract lens of category theory. We will see in the coming discussion that many of the kinds supported by AQL can be interpreted using category theory.

schema sS = literal : TypeSide {
	entities
		Skill SkillClass
	foreign_keys
		fk_HasClass : Skill -> SkillClass
	attributes
		SCName : SkillClass -> Varchar
		SCNameOnSkl : Skill -> Varchar
		SName : Skill -> Varchar
	observation_equations
		forall _x. _x.SCNameOnSkl = _x.fk_HasClass.SCName
}

INSTANCES

As schemas extend the type side so instances extend schemas. An AQL instance can be conceptualized as the data that "fills" a schema. Minimally, an instance consists of a set of generators for each entity in the schema. A generator is string used by AQL that serves to identify a particular record in an entity. The user can optionally create equational definitions which associate the generator of one entity with that of another or a generator with an attribute value. In the absence of such an association AQL will create what is called a Skolem term. Skolem terms are essential for creating a database instance that can be formally defined and verified as consistent. Skolem terms at a type are referred to as a labeled null. Skolem terms at an entity are referred to as a meaningless identifier. Notice that labeled nulls are very different than nulls in currently available RDBMSs.

Every null in AQL is typed by its associated generator and the path to the location in a schema where a value is missing. For example, in the instance definition below we failed to provide any generators for skill classes and so AQL created a Skolem term for each unknown skill class beginning with a skill generator followed by the foreign key path. So for the Math skill there is an unknown generator s0.fk_HasClass whose unknown name is s0.fk_HasClass.SCName.

The following is a representative instance of skills. The way to think about this representative is that it is one member of a universe of possible instances all of which are derived from the same schema type sS. To summarize, so far we have introduced the idea that AQL constructions are organized around the kind hierarchy:

typeside <- schema <- instance.

Running this file and selecting an instance below will display a tabular representation of each instance.

instance iSkl = literal : sS {
	generators
		s0 s1 s2 s3 s4 s5 s6 s7 s8 s9 : Skill
	equations
		s0.SName = CategoryTheory       s1.SName = CompSci              s2.SName = DBA                  
		s3.SName = Pharma               s4.SName = Programming          s5.SName = Lecturing            
		s6.SName = GrantWriting         s7.SName = Finance              s8.SName = Management           
		s9.SName = PaperWriting         
}
Skill
IDSCNameOnSklSNamefk_HasClass
s0s0.fk_HasClass.SCNameCategoryTheorys0.fk_HasClass
s1s1.fk_HasClass.SCNameCompScis1.fk_HasClass
s2s2.fk_HasClass.SCNameDBAs2.fk_HasClass
s3s3.fk_HasClass.SCNamePharmas3.fk_HasClass
s4s4.fk_HasClass.SCNameProgrammings4.fk_HasClass
s5s5.fk_HasClass.SCNameLecturings5.fk_HasClass
s6s6.fk_HasClass.SCNameGrantWritings6.fk_HasClass
s7s7.fk_HasClass.SCNameFinances7.fk_HasClass
s8s8.fk_HasClass.SCNameManagements8.fk_HasClass
s9s9.fk_HasClass.SCNamePaperWritings9.fk_HasClass
SkillClass
IDSCName
s0.fk_HasClasss0.fk_HasClass.SCName
s1.fk_HasClasss1.fk_HasClass.SCName
s2.fk_HasClasss2.fk_HasClass.SCName
s3.fk_HasClasss3.fk_HasClass.SCName
s4.fk_HasClasss4.fk_HasClass.SCName
s5.fk_HasClasss5.fk_HasClass.SCName
s6.fk_HasClasss6.fk_HasClass.SCName
s7.fk_HasClasss7.fk_HasClass.SCName
s8.fk_HasClasss8.fk_HasClass.SCName
s9.fk_HasClasss9.fk_HasClass.SCName

Analogous to the skills instance above we create a representative instance for tasks.

instance iTsk = literal : sT {
	generators
		t1 t2 t3 t4 t5 t6 t7 t8 t9 t10 : Task
	equations
		t1.TName = LaunchCIStartup      t2.TName = BuildAQL             t3.TName = WriteAQLTutorial     
		t4.TName = BuildPhrmColim       t5.TName = BuildFncColim        t6.TName = WriteCTPaper         
		t7.TName = TeachCT              t8.TName = TeachCS              t9.TName = RunPhrmIntProject    
		t10.TName = RunFncIntProject    
}
Task
IDTName
t1LaunchCIStartup
t2BuildAQL
t3WriteAQLTutorial
t4BuildPhrmColim
t5BuildFncColim
t6WriteCTPaper
t7TeachCT
t8TeachCS
t9RunPhrmIntProject
t10RunFncIntProject

The definition of an entity with a pair of attributes provides us with the ability to create an instance that associates each such pair with the same generator. Here we illustrate this idea with such a representative instance.

instance iSnT = literal : sSnT {
	generators
		st1 st2 st3 st4 st5 st6 st7 st8 st9 st10 st11 st12 st13 st14 st15 st16 st17 st18 st19 st20 st21 st22 st23 st24 : SkillNeededForTask
	equations
		st1.skill = CompSci             st1.task = LaunchCIStartup      st2.skill = Programming         
		st2.task = LaunchCIStartup      st3.skill = GrantWriting        st3.task = LaunchCIStartup      
		st4.skill = PaperWriting        st4.task = LaunchCIStartup      st5.skill = CompSci             
		st5.task = BuildAQL             st6.skill = Programming         st6.task = BuildAQL             
		st7.skill = Management          st7.task = BuildAQL             st8.skill = Programming         
		st8.task = WriteAQLTutorial     st9.skill = Lecturing           st9.task = WriteAQLTutorial     
		st10.skill = PaperWriting       st10.task = WriteAQLTutorial    st11.skill = Pharma             
		st11.task = BuildPhrmColim      st12.skill = Programming        st12.task = BuildPhrmColim      
		st13.skill = Finance            st13.task = BuildFncColim       st14.skill = Programming        
		st14.task = BuildFncColim       st15.skill = CategoryTheory     st15.task = WriteCTPaper        
		st16.skill = PaperWriting       st16.task = WriteCTPaper        st17.skill = CategoryTheory     
		st17.task = TeachCT             st18.skill = Lecturing          st18.task = TeachCT             
		st19.skill = Pharma             st19.task = RunPhrmIntProject   st20.skill = Management         
		st20.task = RunPhrmIntProject   st21.skill = Finance            st21.task = RunFncIntProject    
		st22.skill = Management         st22.task = RunFncIntProject    st23.skill = CompSci            
		st23.task = TeachCS             st24.skill = Lecturing          st24.task = TeachCS             
}
SkillNeededForTask
IDskilltask
st1CompSciLaunchCIStartup
st2ProgrammingLaunchCIStartup
st3GrantWritingLaunchCIStartup
st4PaperWritingLaunchCIStartup
st5CompSciBuildAQL
st6ProgrammingBuildAQL
st7ManagementBuildAQL
st8ProgrammingWriteAQLTutorial
st9LecturingWriteAQLTutorial
st10PaperWritingWriteAQLTutorial
st11PharmaBuildPhrmColim
st12ProgrammingBuildPhrmColim
st13FinanceBuildFncColim
st14ProgrammingBuildFncColim
st15CategoryTheoryWriteCTPaper
st16PaperWritingWriteCTPaper
st17CategoryTheoryTeachCT
st18LecturingTeachCT
st19PharmaRunPhrmIntProject
st20ManagementRunPhrmIntProject
st21FinanceRunFncIntProject
st22ManagementRunFncIntProject
st23CompSciTeachCS
st24LecturingTeachCS

Instance Categories

Like schemas, instances can also be viewed as categories. To make this clearer consider these alternative syntaxes for declaring an instance using the equations keyword. The following are both equivalent to the sSkl declaration above:

instance iSkl = literal : sS {
    generators
        s0 s1 s2 s3 s4 s5 s6 s7 s8 s9 : Skill
    equations
      s0.SName = CategoryTheory    s1.SName = CompSci        s2.SName = DBA
      s3.SName = Pharma            s4.SName = Programming    s5.SName = Lecturing
      s6.SName = GrantWriting      s7.SName = Finance        s8.SName = Management
      s9.SName = PaperWriting
}
instance iSkl = literal : sS {
    generators
        s0 s1 s2 s3 s4 s5 s6 s7 s8 s9 : Skill
    equations
      SName(s0) = CategoryTheory    SName(s1) = CompSci        SName(s2) = DBA
      SName(s3) = Pharma            SName(s4) = Programming    SName(s5) = Lecturing
      SName(s6) = GrantWriting      SName(s7) = Finance        SName(s8) = Management
      SName(s9) = PaperWriting
}

The objects of an instance category are the generators plus the Skolem terms produced by those generators. The arrows are either a foreign key evaluated at a generator/Skolem term or an attribute evaluated at a generator/Skolem term. Like a schema the literal declaration of an instance is a presentation of an instance category. As you will see when you run an AQL model with a literal instance declaration AQL will fill Skolem terms as implied by the schema and the provided generators.

Notice these equations declare instance arrows in a way that is closely analogous to an RDF triple. The subject of a triple is a generator/Skolem term, the object of a triple is either a literal (value on the type side) or another generator/Skolem term and the predicate is the type on the arrow as determined by the underlying schema. As such AQL instances are closely related to graph databases although they are very strongly typed by their underlying schema.

In order to extend the instance iSkl to include classes we need to decide how we want to partition the skills we have into broad equivalence classes (we list the generators for convenience):

AQL provides the ability to extend any construction by importing a construction already in place, so using the path dot notation we extend the iSkl instance as follows:

instance iSklExt = literal : sS {
	imports
		iSkl
	equations
		s0.fk_HasClass = s1.fk_HasClass
		s0.fk_HasClass = s2.fk_HasClass
		s0.fk_HasClass = s4.fk_HasClass
		s3.fk_HasClass = s5.fk_HasClass
		s3.fk_HasClass = s7.fk_HasClass
		s3.fk_HasClass = s8.fk_HasClass
		s6.fk_HasClass = s9.fk_HasClass
}
Skill
IDSCNameOnSklSNamefk_HasClass
s0s0.fk_HasClass.SCNameCategoryTheorys0.fk_HasClass
s1s0.fk_HasClass.SCNameCompScis0.fk_HasClass
s2s0.fk_HasClass.SCNameDBAs0.fk_HasClass
s3s5.fk_HasClass.SCNamePharmas5.fk_HasClass
s4s0.fk_HasClass.SCNameProgrammings0.fk_HasClass
s5s5.fk_HasClass.SCNameLecturings5.fk_HasClass
s6s9.fk_HasClass.SCNameGrantWritings9.fk_HasClass
s7s5.fk_HasClass.SCNameFinances5.fk_HasClass
s8s5.fk_HasClass.SCNameManagements5.fk_HasClass
s9s9.fk_HasClass.SCNamePaperWritings9.fk_HasClass
SkillClass
IDSCName
s0.fk_HasClasss0.fk_HasClass.SCName
s5.fk_HasClasss5.fk_HasClass.SCName
s9.fk_HasClasss9.fk_HasClass.SCName

Notice that each of the paths in the equations above have a direct correspondence with a Skolem term. Hence, in contrast with traditional database NULL values, Skolem terms can be manipulated in the same way any other value can be manipulated. If you compare iSkl and iSklExt in the runtime GUI you will note that the number of Skolem terms has decreased from ten (one for each skill) to three (one for each equivalence class).

The Category of Instances of a Schema

Now lets see if we can give these skill class labeled nulls labels. To do this we have to introduce another example of a category, the universe of possible instances that are all based on a particular schema. For example all instances of sS define a category. The objects are instances of sS and we already have two examples, iSkl and iSklExt. The arrows are transforms from one instance of sS to another instance of sS. A transform is an instance map that preserves the structure of the instance. We will return to transforms in a moment, but for now lets create a third instance with labels for our three skill classes.

instance iSklCls = literal : sS {
	generators
		sc0 sc1 sc2 : SkillClass
	equations
		sc0.SCName = Cognitive
		sc1.SCName = Social
		sc2.SCName = Psychomotor
}
Skill
IDSCNameOnSklSNamefk_HasClass
SkillClass
IDSCName
sc0Cognitive
sc1Social
sc2Psychomotor

An important idea in category theory is the notion of a universal construction. We now introduce the AQL implementation of such a construction. The idea is that given a particular structure defined by a diagram one can consider all possible examples of that structure in a particular category. By that we mean all examples that have the same shape as the diagram in question. Let's consider a diagram called a cospan. It has this shape: t1: A --> X <-- B :t2. As we were currently talking about the category of instances of the schema sS, the objects A, X, and B are instances and the two arrows are transforms. A cospan in this category is any valid collection of three instances of sS that are related to one another via a pair of transforms.

Given a choice of instances say iSklExt and iSklCls we can consider all possible ways of combining iSklExt and iSklCls with a pair of transforms to get a third instance X. The universal way of combining iSklExt and iSklCls to get an X is the one to which all others can be related in a unique way. That is to say, for any X and any pair of valid transforms t1 and t2 the universal cospan iA: iSklExt --> iSklCP <-- iSklCls :iB is the cospan such that there exists a unique transform:

t(t1,t2): iSklCP --> X such that
iA.t(t1,t2) = t1 and
iB.t(t1,t2) = t2

for any X and any pair of transforms (t1, t2). What this means is that the pair of arrows t1 and t2 can be replaced with the single arrow t(t1,t2).

Conceptually this construction is a generalization of the SQL "UNION ALL" statement. It is a generalization in that it applies to a pair of instances of a schema rather than a pair of relations of a relvar. This construction is known as a coproduct. We say that iA and iB are injections of the instances iSklExt and iSklCls into the coproduct. You can see the effect of this construction by running this file and selecting iSklCP in the viewer. Notice the result is an instance sS that is the disjoint union of the records of iSklExt and iSklCls that has respected the foreign key fk_HasClass.

instance iSklCP = coproduct (iSklExt) + (iSklCls) : sS
Skill
IDSCNameOnSklSNamefk_HasClass
s0s0.fk_HasClass.SCNameCategoryTheorys0.fk_HasClass
s1s0.fk_HasClass.SCNameCompScis0.fk_HasClass
s2s0.fk_HasClass.SCNameDBAs0.fk_HasClass
s3s5.fk_HasClass.SCNamePharmas5.fk_HasClass
s4s0.fk_HasClass.SCNameProgrammings0.fk_HasClass
s5s5.fk_HasClass.SCNameLecturings5.fk_HasClass
s6s9.fk_HasClass.SCNameGrantWritings9.fk_HasClass
s7s5.fk_HasClass.SCNameFinances5.fk_HasClass
s8s5.fk_HasClass.SCNameManagements5.fk_HasClass
s9s9.fk_HasClass.SCNamePaperWritings9.fk_HasClass
SkillClass
IDSCName
s0.fk_HasClasss0.fk_HasClass.SCName
s5.fk_HasClasss5.fk_HasClass.SCName
s9.fk_HasClasss9.fk_HasClass.SCName
sc0Cognitive
sc1Social
sc2Psychomotor

Now that we have all of the desired records in the same instance we want to identify the generators that came from iSklCls with the Skolem terms that came from the iSklExt instance. To achieve this result we would like to declare that each of the Skolem terms from iSklExt should be merged with the appropriate generator coming from iSklCls. Recall we have already done this by importing an existing instance into a new instance and adding equations. Here is the solution using that approach.

instance iSklSklCls1 = literal : sS {
	imports
		iSklCP
	equations
		s0.fk_HasClass = sc0
		s3.fk_HasClass = sc1
		s6.fk_HasClass = sc2
}
Skill
IDSCNameOnSklSNamefk_HasClass
s0CognitiveCategoryTheorys0.fk_HasClass
s1CognitiveCompScis0.fk_HasClass
s2CognitiveDBAs0.fk_HasClass
s3SocialPharmas5.fk_HasClass
s4CognitiveProgrammings0.fk_HasClass
s5SocialLecturings5.fk_HasClass
s6PsychomotorGrantWritings9.fk_HasClass
s7SocialFinances5.fk_HasClass
s8SocialManagements5.fk_HasClass
s9PsychomotorPaperWritings9.fk_HasClass
SkillClass
IDSCName
s0.fk_HasClassCognitive
s5.fk_HasClassSocial
s9.fk_HasClassPsychomotor

There is an alternative construction that is universal. First we create an instance iSLnk with a generator for each of the skill classes. We then create two instance transforms, the first mapping each link generator to a iSklExt Skolem term and the second mapping each link generator to our iSklCls generators. The new universal construction is known as a coequalizer. An instance coequalizer uses a pair of transforms to define collections of records that are identified as equal. This approach has the advantage that linking instances and transforms can be imported from an external database using SQL queries.

This construction is a generalization of the notion of partitioning a set into equivalence classes. Later we will see how these constructions can be applied to other categories in AQL.

instance iSLnk = literal : sS {
	generators
		scLnk0 scLnk1 scLnk2 : SkillClass
}
Skill
IDSCNameOnSklSNamefk_HasClass
SkillClass
IDSCName
scLnk0scLnk0.SCName
scLnk1scLnk1.SCName
scLnk2scLnk2.SCName

transform tS1 = literal : iSLnk -> iSklCP {
	entities
		scLnk0 -> s0.fk_HasClass
		scLnk1 -> s3.fk_HasClass
		scLnk2 -> s6.fk_HasClass
}
transform tS2 = literal : iSLnk -> iSklCP {
	entities
		scLnk0 -> sc0
		scLnk1 -> sc1
		scLnk2 -> sc2
}
instance iSklSklCls2 = coequalize tS1 tS2
Skill
IDSCNameOnSklSNamefk_HasClass
s0CognitiveCategoryTheorys0.fk_HasClass
s1CognitiveCompScis0.fk_HasClass
s2CognitiveDBAs0.fk_HasClass
s3SocialPharmas5.fk_HasClass
s4CognitiveProgrammings0.fk_HasClass
s5SocialLecturings5.fk_HasClass
s6PsychomotorGrantWritings9.fk_HasClass
s7SocialFinances5.fk_HasClass
s8SocialManagements5.fk_HasClass
s9PsychomotorPaperWritings9.fk_HasClass
SkillClass
IDSCName
s0.fk_HasClassCognitive
s5.fk_HasClassSocial
s9.fk_HasClassPsychomotor

Notice we can summarize the sequence of constructions above this as follows:

s0.fk_HasClass <- scLnk0 -> sc0
s3.fk_HasClass <- scLnk1 -> sc1
s6.fk_HasClass <- scLnk2 -> sc2,

which in turn is structurally equivalent to the equations in iSklSklCls1.


AQL QUERIES

And the Category of Schemas

The next example of a category we need is the category of schemas. Since a schema can be viewed as a presentation of a category the category of schemas is closely related to the category of categories. The objects of the category of schemas are of course schemas and the arrows are referred to in AQL as mappings. There is a syntax for the literal declaration of a mapping that is provided by AQL, but we will not elaborate on that syntax as you will see that most applications of mappings are either implicit in other constructions or generalizations to mappings as in the case of queries which we introduce in this section.

Conceptually mappings preserve the structure of a schema, that is, they map entities and foreign keys/attributes in a consistent way. For the interested reader the name given mappings in the mathematical literature is functor, as a functor can be viewed as a generalized function. Queries further generalize mappings in the same way that a cospan A -> B <- C generalizes a single arrow A -> C.

AQL queries can be used to extract some part of a schema instance as well as to calculate derived results. This construction is the query kind. We illustrate in our first example a simple form of an AQL query that is very similar to an SQL query.

We make use of the Java string method "matches" we have exposed through the type side. This method allows us to pass a regular expression that can be used as a filter for any skill name ending in the string "Writing".

query qWrtngSklS = simple : sS {
			from	s : Skill
				where	Matches(s.SName, .*Writing) = true
				return	Skill_Class_Name -> s.SCNameOnSkl
					Skill_Name -> s.SName

}

By virtue of AQL's ability to define multiple instances associated with a schema, to execute a query it must be evaluated on an instance to define a new instance. When queries are of the simple type as in this example the schema is implicitly defined as having a single entity with attributes as listed in the return clause.

instance iWrtngSklS = eval qWrtngSklS iSkl
Q
IDSkill_Class_NameSkill_Name
(s=s6)s6.fk_HasClass.SCNameGrantWriting
(s=s9)s9.fk_HasClass.SCNamePaperWriting

To illustrate the more complete query syntax we must explicitly declare a target schema. Below we rewrite qWrtngSklS to illustrate the more complete syntax. First we define the target schema with a single entity tSkill and the two attributes listed in the return clause above:

schema sSkl = literal : TypeSide {
	entities
		tSkill
	attributes
		Skill_Class_Name : tSkill -> Varchar
		Skill_Name : tSkill -> Varchar
}

Next we create a query whose kind is an arrow from the source schema sS to the target schema sSkl. This complete syntax requires a query sub-section for each entity in the target schema. Additionally the complete syntax requires an assignment for all attributes defined in the target schema. The query qWrtngSklC is functionally equivalent to qWrtngSklS. If you run this model you will notice that the instance iWrtngSklS is identical to the instance iWrtngSklC.

query qWrtngSklC = literal : sS -> sSkl {
	entities
		tSkill -> {	from	s : Skill
				where	Matches(s.SName, .*Writing) = true
				return	Skill_Class_Name -> s.SCNameOnSkl
					Skill_Name -> s.SName}
}
instance iWrtngSklC = eval qWrtngSklC iSkl
tSkill
IDSkill_Class_NameSkill_Name
(s=s6)s6.fk_HasClass.SCNameGrantWriting
(s=s9)s9.fk_HasClass.SCNamePaperWriting

Queries as structure preserving maps of schemas have the capability of mapping not only entities but also foreign keys. In this example we will build a query that has as its source and target schemas sS but is not an identity. This example filters for those skills that are classified as social skills.

Given its novel nature, let's take a moment to elaborate on the syntax of the foreign_keys section. We have duplicated the declaration of the foreign key we are constructing in a comment as reference. The fk_HasClass foreign key enforces a functional dependency between the generators of the entity Skill and the generators of of the entity SkillClass. In the entities section we have generator variables sc for the SkillClass entity and s for the Skill entity. These are variables that vary over the generators and Skolem terms created by the saturation process. The task at hand is to use those variables and the source foreign key to construct a functional dependency in the target schema. We do this by taking a SkillClass generator and assigning it as the return value of the target foreign key fk_HasClass. Which generator do we assign? The one we get by taking a skill generator and following it to its skill class. Notice that this is opposite to the direction of the arrow associated with the fk_SkillClass functional dependency.

Note: If you removed the where clause from each entity sub-section you would have the identity mapping.

query qScl = literal : sS -> sS {
	entities
		Skill -> {from	s : Skill
				where	s.fk_HasClass.SCName = Social
				return	SCNameOnSkl -> s.SCNameOnSkl
					SName -> s.SName}

		SkillClass -> {from	sc : SkillClass
				where	sc.SCName = Social
				return	SCName -> sc.SCName}
	foreign_keys
		fk_HasClass -> 	{sc -> s.fk_HasClass}
}
instance iSclSkl = eval qScl iSklSklCls1
Skill
IDSCNameOnSklSNamefk_HasClass
(s=s3)SocialPharma(sc=s5.fk_HasClass)
(s=s5)SocialLecturing(sc=s5.fk_HasClass)
(s=s7)SocialFinance(sc=s5.fk_HasClass)
(s=s8)SocialManagement(sc=s5.fk_HasClass)
SkillClass
IDSCName
(sc=s5.fk_HasClass)Social


SCHEMA COLIMIT

Next we introduce an AQL feature that allows us to assemble multiple source schemas into a single target schema. This construction is called a schema colimit by virtue of its mathematical origins in category theory. Generally colimits are a class of universal constructions that take a diagram of objects connected by arrows and assemble those objects in a way consistent with the diagram. We have already seen an example of a colimit in the instance coproduct. Now we apply AQL's implementation of that same universal construction to the category of schemas. When applied to schemas the coproduct results in a construction analogous to what is sometimes called "landing" tables from multiple source schemas into a single schema where data transforms can be conveniently developed.

In this particular case we "land" the three schemas sT, sS, sSnT using the single structure S_T_SnT_SumAuto whose kind is schema_colimit. We will develop the connection of a schema coproduct with a instance coproduct in a moment, but for now examine the automatically generated coproduct in the runtime viewer.

schema_colimit S_T_SnT_SumAuto = coproduct sT + sS + sSnT

The automatically generated schema colimit prefixes each object within the target schema with the label associated with the source schema from which that object originated. With the modify command we have the option of providing whatever names we choose independent of the names associated with the source schemas from which they originated. In this case we designate each entity with the prefix l to indicate that it was landed using the coproduct operation.

schema_colimit S_T_SnT_Sum = modify S_T_SnT_SumAuto {
	rename entities
		sS_Skill -> lSkill
		sS_SkillClass -> lSkillClass
		sT_Task -> lTask
		sSnT_SkillNeededForTask -> lSkillNeededForTask
	rename foreign_keys
		sS_Skill -> lSkill
		sS_SkillClass -> lSkillClass
		sT_Task -> lTask
		sSnT_SkillNeededForTask -> lSkillNeededForTask
		sS_fk_HasClass -> lfk_HasClass
	rename attributes
		sS_Skill -> lSkill
		sS_SkillClass -> lSkillClass
		sT_Task -> lTask
		sSnT_SkillNeededForTask -> lSkillNeededForTask
		sS_fk_HasClass -> lfk_HasClass
		sS_SName -> lSName
		sS_SCNameOnSkl -> lSCNameOnSkl
		sS_SCName -> lSCName
		sT_TName -> lTName
		sSnT_skill -> lskill
		sSnT_task -> ltask
}

Schema Colimit Diagram

Recalling our earlier description of the instance coproduct which was a cospan of instances, a schema coproduct is a cospan of schemas and as such includes a schema and a pair of structure preserving maps of schemas.

With the sequence of constructions below we reveal that inner structure of the schema colimit. This structure consists of a target schema and a schema mapping from each source schema to that target. This means that a schema colimit can be thought of as a diagram of schemas and schema mappings. In this particular case, using the labels assigned below, we have the following diagram:

                 mSToS_T_SnT_Sum        mTToS_T_Snt_Sum
                 sS --------> sS_T_Snt_Sum <-------- sT
                                   /\
                 mSnTToS_T_SnT_Sum |
                                   |
                                 sSnT
schema sS_T_SnT_Sum = getSchema S_T_SnT_Sum
mapping mSToS_T_SnT_Sum = getMapping S_T_SnT_Sum sS
mapping mTToS_T_Snt_Sum = getMapping S_T_SnT_Sum sT
mapping mSnTToS_T_SnT_Sum = getMapping S_T_SnT_Sum sSnT

Migrating Data Between Schemas

Sigma --| Delta --| Pi

Let's recap where we are. We have a set of three source schemas sS, sT, and sSnT, each with an instance; iSklSklCls1, iTsk, and iSnT respectively. We have a target schema sS_T_SnT_Sum. We also have mappings that take each source schema to the target schema. What we want is a construction similar to eval that can be applied to a mapping rather than a query, but the schema colimit is giving us mappings not queries.

Recall that queries generalize mappings. It so happens there are three constructions that are related to eval in the same way mappings are related to queries. In fact eval and it's dual coeval, which we have not discussed, are generalizations of these constructions. To develop these ideas would be a lengthy digression so we will only mention them in passing along with some informal remarks about their properties.

These three constructions are called sigma, pi and delta. They all can be understood as moving instance data through mappings in a way that is analogous to how eval moves instances through queries. This family of functors collectively define AQL's implementation of Extration, Transformation and Load abbreviated ETL processes. Sigma, pi and delta are all structure preserving maps between the category of instances of one schema to the category of instances of another schema, so they are functors. This means that sigma, pi and delta map not only instances but instance transforms. These three mappings are related as follows. Delta pulls data backward through a mapping and generalizes the notion of projection. Both Sigma and Pi push data forward through a mapping and generalize sums/unions and products/joins respectively. Given a single schema mapping, Delta can be paired with sigma on the left and pi on the right to create data migration round trips.

To see this, consider schemas sA, sB, mapping m and instance iA of sA. Without yet specifying specifics the AQL syntax would be:

mapping m = literal : sA -> sB {...} 
instance iB = literal : sB {...}

Delta can move the instance iB backward through m into sA as follows:

instance iDlt_m_iB = delta m iB

Similarly, sigma can move this new instance forward through the same m into sB:

instance iSgmDlt_m_iB = delta m iDlt_m_iB

A natural thing to ask is how iSgmDlt_m_iB and iB are related. The answer is they are related by a transform known as the unit of the sigma --| delta adjoint pair. Notice we could also start with an instance iA of sA and apply first sigma m and then delta m to product an instance iDltSgm_m_iA. iA and iDltSgm_m_iA are related by the counit of the sigma --| delta adjoint pair. In a similar way delta --| pi form an adjoint pair with associated unit and counit transforms. We say that delta is right adjoint to sigma and left adjoint to pi.

Of the three, sigma has the property that generators of the source instance are preserved in the target instance. This means we can move instances and transforms between schemas in a way that permits us to merge instance data. Let's see how we can use sigma to solve the problem at hand and later we will return to broader questions about the nature of sigma, pi and delta and their associated units and counits.

Recall we have a target schema that contains all of the entities from our three source schemas we can use the sigma construction to push the instances in the source schemas forward through the mappings generated by the schema_colimit construction to create an instance in the sS_T_SnT_Sum assembly. Having done so we would have three separate instances in the target schema sS_T_SnT_Sum. With all of these instances in the same schema we can make use of our familiar instance coproduct construction. This would look like the following:

instance iSklSklCls_In_S_T_SnT_Sum = sigma mSToS_T_SnT_Sum iSklSklCls1
instance iTsk_In_S_T_SnT_Sum = sigma mSToT_T_SnT_Sum iTsk
instance iSnT_In_S_T_SnT_Sum = sigma mSnTToS_T_SnT_Sum iSnT
instance iS_T_SnT_Sum = coproduct
    iSklSklCls_In_S_T_SnT_Sum
    iTsk_In_S_T_SnT_Sum
    iSnT_In_S_T_SnT_Sum
    : sS_T_SnT_Sum

AQL provides the coproduct_sigma construction which performs the above sequence in one step:

instance iS_T_SnT_Sum = coproduct_sigma (mSToS_T_SnT_Sum) (iSklSklCls1) (mTToS_T_Snt_Sum) (iTsk) (mSnTToS_T_SnT_Sum) (iSnT) : sS_T_SnT_Sum
lSkill
IDlSCNameOnSkllSNamelfk_HasClass
s0CognitiveCategoryTheorys0.lfk_HasClass
s1CognitiveCompScis0.lfk_HasClass
s2CognitiveDBAs0.lfk_HasClass
s3SocialPharmas5.lfk_HasClass
s4CognitiveProgrammings0.lfk_HasClass
s5SocialLecturings5.lfk_HasClass
s6PsychomotorGrantWritings9.lfk_HasClass
s7SocialFinances5.lfk_HasClass
s8SocialManagements5.lfk_HasClass
s9PsychomotorPaperWritings9.lfk_HasClass
lSkillClass
IDlSCName
s0.lfk_HasClassCognitive
s5.lfk_HasClassSocial
s9.lfk_HasClassPsychomotor
lSkillNeededForTask
IDlskillltask
st1CompSciLaunchCIStartup
st2ProgrammingLaunchCIStartup
st3GrantWritingLaunchCIStartup
st4PaperWritingLaunchCIStartup
st5CompSciBuildAQL
st6ProgrammingBuildAQL
st7ManagementBuildAQL
st8ProgrammingWriteAQLTutorial
st9LecturingWriteAQLTutorial
st10PaperWritingWriteAQLTutorial
st11PharmaBuildPhrmColim
st12ProgrammingBuildPhrmColim
st13FinanceBuildFncColim
st14ProgrammingBuildFncColim
st15CategoryTheoryWriteCTPaper
st16PaperWritingWriteCTPaper
st17CategoryTheoryTeachCT
st18LecturingTeachCT
st19PharmaRunPhrmIntProject
st20ManagementRunPhrmIntProject
st21FinanceRunFncIntProject
st22ManagementRunFncIntProject
st23CompSciTeachCS
st24LecturingTeachCS
lTask
IDlTName
t1LaunchCIStartup
t2BuildAQL
t3WriteAQLTutorial
t4BuildPhrmColim
t5BuildFncColim
t6WriteCTPaper
t7TeachCT
t8TeachCS
t9RunPhrmIntProject
t10RunFncIntProject


AQL Queries Continued

In the current context we have as source schema the result of the colimit construction. If you run this file and look at the colimit schema sS_T_SnT_Sum and associated instance you will discover the we have assembled a target schema where the entities of the source schemas are independent of one another. We now use the query construction to introduce foreign key constraints in a way that is analogous to the addition of the foreign key constraints supported in RDBMSs.

Recall that a non-simple AQL query requires we define the target schema first. This schema is identical to the output of the colimit construction but with the addition of a pair of foreign keys from the SkillNdFrTask entity with the (skill, task) attribute pair to the Skill and Task entities respectively. This produces a schema which is known in category theory as a span and it can again be summarized as a diagram, but in contrast with the case of the colimit construction this is a diagram of entities rather than schemas. We have enclosed the Skill and SkillClass entities with parentheses to indicate that they serve as a reference taxonomy.

          fk_HasClass    fk_SnT_S             fk_SnT_T
(SkillClass <----- Skill) <----- SkillNdFrTask -----> Task

We again make use of the observation_equations section to denormalize all of the attributes that are available through paths as attributes directly available to the SkillNdFrTask entity.

schema sSTSpn = literal : TypeSide {
	entities
		Skill SkillClass SkillNdFrTask Task
	foreign_keys
		fk_ForTask : SkillNdFrTask -> Task
		fk_HasClass : Skill -> SkillClass
		fk_SkillNeeded : SkillNdFrTask -> Skill
	attributes
		SCName : SkillClass -> Varchar
		SCNameOnSkl : Skill -> Varchar
		SName : Skill -> Varchar
		TName : Task -> Varchar
		skill : SkillNdFrTask -> Varchar
		skillclass : SkillNdFrTask -> Varchar
		task : SkillNdFrTask -> Varchar
	observation_equations
		forall _x. _x.SCNameOnSkl = _x.fk_HasClass.SCName
		forall _x. _x.skill = _x.fk_SkillNeeded.SName
		forall _x. _x.skillclass = _x.fk_SkillNeeded.fk_HasClass.SCName
		forall _x. _x.task = _x.fk_ForTask.TName
}

Given the similarity of the source and target schemas the entity sub-sections are all direct mappings that reproduce exact copies of the source entities instances with those of the target entities with the exception of the SkllNdFrTask target entity. In order to construct that entity we must have generators on both sides of the new foreign keys we are introducing. By default when multiple source generator variables are provided for a target entity each generator for the target comes from the Cartesian product of the source generators. The where clause has two equations implicitly connected by a logical and. This effectively constrains the target entities to an equationally defined subset of a Cartesian product, also known as a join, so this construction effectively joins the three source entities and makes all of their generators available for the creation of the new foreign keys. The foreign keys can now be assigned the values for the Skill and Task generators based on the values in the SkillNdFrTask section.

query qS_T_SnT_SumToSTSpn = literal : sS_T_SnT_Sum -> sSTSpn {
	entities
		Skill -> {from	ss : lSkill
				return	SCNameOnSkl -> ss.lSCNameOnSkl
					SName -> ss.lSName}

		SkillClass -> {from	scsc : lSkillClass
				return	SCName -> scsc.lSCName}

		SkillNdFrTask -> {from	sn : lSkill
					snt : lSkillNeededForTask
					nt : lTask
				where	snt.lskill = sn.lSName
					snt.ltask = nt.lTName
				return	skill -> snt.lskill
					skillclass -> sn.lfk_HasClass.lSCName
					task -> snt.ltask}

		Task -> {from	tt : lTask
				return	TName -> tt.lTName}
	foreign_keys
		fk_ForTask -> tt -> nt

		fk_HasClass -> scsc -> ss.lfk_HasClass

		fk_SkillNeeded -> ss -> sn
}

Pushing the instance generated by the coproduct_sigma construction through the query above using eval produces an instance reflecting the foreign keys introduced in the target schema we set up as part of the query definition.

instance iSTSpn = eval qS_T_SnT_SumToSTSpn iS_T_SnT_Sum
Skill
IDSCNameOnSklSNamefk_HasClass
(ss=s0)CognitiveCategoryTheory(scsc=s0.lfk_HasClass)
(ss=s1)CognitiveCompSci(scsc=s0.lfk_HasClass)
(ss=s2)CognitiveDBA(scsc=s0.lfk_HasClass)
(ss=s3)SocialPharma(scsc=s5.lfk_HasClass)
(ss=s4)CognitiveProgramming(scsc=s0.lfk_HasClass)
(ss=s5)SocialLecturing(scsc=s5.lfk_HasClass)
(ss=s6)PsychomotorGrantWriting(scsc=s9.lfk_HasClass)
(ss=s7)SocialFinance(scsc=s5.lfk_HasClass)
(ss=s8)SocialManagement(scsc=s5.lfk_HasClass)
(ss=s9)PsychomotorPaperWriting(scsc=s9.lfk_HasClass)
SkillClass
IDSCName
(scsc=s0.lfk_HasClass)Cognitive
(scsc=s5.lfk_HasClass)Social
(scsc=s9.lfk_HasClass)Psychomotor
SkillNdFrTask
IDskillskillclasstaskfk_ForTaskfk_SkillNeeded
(snt=st1)(sn=s1)(nt=t1)CompSciCognitiveLaunchCIStartup(tt=t1)(ss=s1)
(snt=st2)(sn=s4)(nt=t1)ProgrammingCognitiveLaunchCIStartup(tt=t1)(ss=s4)
(snt=st3)(sn=s6)(nt=t1)GrantWritingPsychomotorLaunchCIStartup(tt=t1)(ss=s6)
(snt=st4)(sn=s9)(nt=t1)PaperWritingPsychomotorLaunchCIStartup(tt=t1)(ss=s9)
(snt=st5)(sn=s1)(nt=t2)CompSciCognitiveBuildAQL(tt=t2)(ss=s1)
(snt=st6)(sn=s4)(nt=t2)ProgrammingCognitiveBuildAQL(tt=t2)(ss=s4)
(snt=st7)(sn=s8)(nt=t2)ManagementSocialBuildAQL(tt=t2)(ss=s8)
(snt=st8)(sn=s4)(nt=t3)ProgrammingCognitiveWriteAQLTutorial(tt=t3)(ss=s4)
(snt=st9)(sn=s5)(nt=t3)LecturingSocialWriteAQLTutorial(tt=t3)(ss=s5)
(snt=st10)(sn=s9)(nt=t3)PaperWritingPsychomotorWriteAQLTutorial(tt=t3)(ss=s9)
(snt=st11)(sn=s3)(nt=t4)PharmaSocialBuildPhrmColim(tt=t4)(ss=s3)
(snt=st12)(sn=s4)(nt=t4)ProgrammingCognitiveBuildPhrmColim(tt=t4)(ss=s4)
(snt=st13)(sn=s7)(nt=t5)FinanceSocialBuildFncColim(tt=t5)(ss=s7)
(snt=st14)(sn=s4)(nt=t5)ProgrammingCognitiveBuildFncColim(tt=t5)(ss=s4)
(snt=st15)(sn=s0)(nt=t6)CategoryTheoryCognitiveWriteCTPaper(tt=t6)(ss=s0)
(snt=st16)(sn=s9)(nt=t6)PaperWritingPsychomotorWriteCTPaper(tt=t6)(ss=s9)
(snt=st17)(sn=s0)(nt=t7)CategoryTheoryCognitiveTeachCT(tt=t7)(ss=s0)
(snt=st18)(sn=s5)(nt=t7)LecturingSocialTeachCT(tt=t7)(ss=s5)
(snt=st19)(sn=s3)(nt=t9)PharmaSocialRunPhrmIntProject(tt=t9)(ss=s3)
(snt=st20)(sn=s8)(nt=t9)ManagementSocialRunPhrmIntProject(tt=t9)(ss=s8)
(snt=st21)(sn=s7)(nt=t10)FinanceSocialRunFncIntProject(tt=t10)(ss=s7)
(snt=st22)(sn=s8)(nt=t10)ManagementSocialRunFncIntProject(tt=t10)(ss=s8)
(snt=st23)(sn=s1)(nt=t8)CompSciCognitiveTeachCS(tt=t8)(ss=s1)
(snt=st24)(sn=s5)(nt=t8)LecturingSocialTeachCS(tt=t8)(ss=s5)
Task
IDTName
(tt=t1)LaunchCIStartup
(tt=t2)BuildAQL
(tt=t3)WriteAQLTutorial
(tt=t4)BuildPhrmColim
(tt=t5)BuildFncColim
(tt=t6)WriteCTPaper
(tt=t7)TeachCT
(tt=t8)TeachCS
(tt=t9)RunPhrmIntProject
(tt=t10)RunFncIntProject


JDBC Pragmas, Imports and Exports

This first sequence exports the iSTSpn created above. Exporting a JDBC instance consolidates several sets of commands. First the export issues data definition (DDL) commands against the target RDBMS that create tables and foreign keys that are as close as possible to the associated AQL schema kind. The second sequence of commands inserts instance records into the tables just created. Since this file can be executed several times we must first check to see if the table structure already exists and if so drop those tables. This task is performed using the pragma pCleanUp. In order to display the results of these commands the set of tables in the target schema are listed using the pShwTbls pragma.

First clean out the target tables if present from a previous run.

pragma pCleanUp = sql "com.mysql.jdbc.Driver" "jdbc:mysql://mysql.catinf.com:3306/ci_test?user=catinfcom&password=FDiUY!pr" {
"DROP TABLE IF EXISTS sts_SkillNdFrTask"
"DROP TABLE IF EXISTS sts_Skill"
"DROP TABLE IF EXISTS sts_SkillClass"
"DROP TABLE IF EXISTS sts_Task"
}
START
DROP TABLE IF EXISTS sts_SkillNdFrTask

Updated 0 rows.
END

START
DROP TABLE IF EXISTS sts_Skill

Updated 0 rows.
END

START
DROP TABLE IF EXISTS sts_SkillClass

Updated 0 rows.
END

START
DROP TABLE IF EXISTS sts_Task

Updated 0 rows.
END

Export the results of the above construction of the span and its representative instance. The export_jdbc_instance defaults strings varchar(64). In this case we illustrate how that default can be overridden using the varchar_length option.

pragma eSTS = export_jdbc_instance iSTSpn "com.mysql.jdbc.Driver" "jdbc:mysql://mysql.catinf.com:3306/ci_test?user=catinfcom&password=FDiUY!pr" sts_ {
	options
		varchar_length = 256
}
Exported 47 rows.

Display the contents of the ci_test schema in MySQL.

pragma pShwTbls = sql "com.mysql.jdbc.Driver" "jdbc:mysql://mysql.catinf.com:3306/ci_test?user=catinfcom&password=FDiUY!pr" {
"SHOW  TABLES"
}
START
SHOW  TABLES

TABLE_NAME SkillClassMatch
TABLE_NAME SkillMatch
TABLE_NAME edge
TABLE_NAME edgeAuto
TABLE_NAME edgeAutoFull
TABLE_NAME edgeKind
TABLE_NAME edge_128k
TABLE_NAME edge_16k
TABLE_NAME edge_1k
TABLE_NAME edge_2k
TABLE_NAME edge_32k
TABLE_NAME edge_4k
TABLE_NAME edge_64k
TABLE_NAME edge_8k
TABLE_NAME node
TABLE_NAME nodeKind
TABLE_NAME nodeKindAuto
TABLE_NAME nodeKindTest
TABLE_NAME nodeKindTest2
TABLE_NAME sample_edge
TABLE_NAME sample_node
TABLE_NAME sps_GeneralSkill
TABLE_NAME sps_Person
TABLE_NAME sps_SkillClass
TABLE_NAME sps_SkillPrvdByPerson
TABLE_NAME sps_SpecificSkill
TABLE_NAME sts_Skill
TABLE_NAME sts_SkillClass
TABLE_NAME sts_SkillNdFrTask
TABLE_NAME sts_Task
END

The default AQL import process from a relational database submits an SQL query through the JDBC driver for the source RDBMS for each entity declared in the target schema sSTSpn above. Due to the extensive differences between AQL and RDBMS semantics, the translation process has a number of different options that can be used to provide flexibility. We make use of the default behavior for this import but will return to import options in a later example. The import defaults require a complete specification of all foreign key and attribute columns.

instance iSTSpnImp = import_jdbc sSTSpn "com.mysql.jdbc.Driver" "jdbc:mysql://mysql.catinf.com:3306/ci_test?user=catinfcom&password=FDiUY!pr" {
	Task -> select id, TName from sts_Task
	Skill -> select id, fk_HasClass, SName, SCNameOnSkl from sts_Skill
	SkillClass -> select id, SCName from sts_SkillClass
	SkillNdFrTask -> select id, fk_SkillNeeded, fk_ForTask, skill, task, skillclass from sts_SkillNdFrTask
}
Skill
IDSCNameOnSklSNamefk_HasClass
0CognitiveCategoryTheory20
1CognitiveCompSci20
2CognitiveProgramming20
3SocialLecturing21
4CognitiveDBA20
5PsychomotorGrantWriting22
6SocialPharma21
7SocialFinance21
8SocialManagement21
9PsychomotorPaperWriting22
SkillClass
IDSCName
20Cognitive
21Social
22Psychomotor
SkillNdFrTask
IDskillskillclasstaskfk_ForTaskfk_SkillNeeded
23CategoryTheoryCognitiveWriteCTPaper100
24PaperWritingPsychomotorWriteCTPaper109
25ProgrammingCognitiveWriteAQLTutorial112
26LecturingSocialWriteAQLTutorial113
27PaperWritingPsychomotorWriteAQLTutorial119
28FinanceSocialRunFncIntProject127
29ManagementSocialRunFncIntProject128
30ProgrammingCognitiveBuildPhrmColim132
31PharmaSocialBuildPhrmColim136
32CompSciCognitiveLaunchCIStartup141
33ProgrammingCognitiveLaunchCIStartup142
34GrantWritingPsychomotorLaunchCIStartup145
35PaperWritingPsychomotorLaunchCIStartup149
36CompSciCognitiveBuildAQL151
37ProgrammingCognitiveBuildAQL152
38ManagementSocialBuildAQL158
39PharmaSocialRunPhrmIntProject166
40ManagementSocialRunPhrmIntProject168
41CategoryTheoryCognitiveTeachCT170
42LecturingSocialTeachCT173
43CompSciCognitiveTeachCS181
44LecturingSocialTeachCS183
45ProgrammingCognitiveBuildFncColim192
46FinanceSocialBuildFncColim197
Task
IDTName
10WriteCTPaper
11WriteAQLTutorial
12RunFncIntProject
13BuildPhrmColim
14LaunchCIStartup
15BuildAQL
16RunPhrmIntProject
17TeachCT
18TeachCS
19BuildFncColim

Having constructed a span modeling the way skills are related to tasks, we now turn to another model involving a span relating skills to people. We could build this model up piece by piece as we did above, but AQL's schema declaration can build such a schema in one step. To illustrate a more elaborate example of AQL's integration capabilities the set of tables used to model skills for this schema has an additional level to the taxonomy. The creation of this schema is a precondition for the upcoming import. In order to extract existing data from a relational database into an AQL schema we must first define an AQL schema that is compatible with the external SQL system. The schema below defines the structure into which we target our import.

schema sSPSpn = literal : TypeSide {
	entities
		GeneralSkill Person SkillClass SkillPrvdByPerson SpecificSkill
	foreign_keys
		fk_HasClass : GeneralSkill -> SkillClass
		fk_IsType : SpecificSkill -> GeneralSkill
		fk_SpP_P : SkillPrvdByPerson -> Person
		fk_SpP_S : SkillPrvdByPerson -> SpecificSkill
	attributes
		GSName : GeneralSkill -> Varchar
		GSSCName : GeneralSkill -> Varchar
		PName : Person -> Varchar
		SCName : SkillClass -> Varchar
		SSGSName : SpecificSkill -> Varchar
		SSName : SpecificSkill -> Varchar
		SSSCName : SpecificSkill -> Varchar
		person : SkillPrvdByPerson -> Varchar
		skill : SkillPrvdByPerson -> Varchar
	observation_equations
		forall _x. _x.GSSCName = _x.fk_HasClass.SCName
		forall _x. _x.SSGSName = _x.fk_IsType.GSName
		forall _x. _x.SSSCName = _x.fk_IsType.fk_HasClass.SCName
		forall _x. _x.person = _x.fk_SpP_P.PName
		forall _x. _x.skill = _x.fk_SpP_S.SSName
}

We have already set up a set of tables and associated instance in our source RDBMS so we import that instance using a command analogous to the iSTSpnImp imported instance.

instance iSPSpnImp = import_jdbc sSPSpn "com.mysql.jdbc.Driver" "jdbc:mysql://mysql.catinf.com:3306/ci_test?user=catinfcom&password=FDiUY!pr" {
	SkillClass -> select id, SCName from sps_SkillClass
	SkillPrvdByPerson -> select id, fk_SpP_S, fk_SpP_P, skill, person from sps_SkillPrvdByPerson
	GeneralSkill -> select id, fk_HasClass, GSName, GSSCName from sps_GeneralSkill
	Person -> select id, PName from sps_Person
	SpecificSkill -> select id, fk_IsType, SSName, SSGSName, SSSCName from sps_SpecificSkill
}
GeneralSkill
IDGSNameGSSCNamefk_HasClass
66ComputingCognitive49
67TeachingSocial48
68PerformingPsychomotor47
69BusinessSocial48
70WritingPsychomotor47
71MathCognitive49
Person
IDPName
72Jim
73Joan
74John
75Jane
SkillClass
IDSCName
47Psychomotor
48Social
49Cognitive
SkillPrvdByPerson
IDpersonskillfk_SpP_Pfk_SpP_S
50JimCompSci7281
51JimGrantWriting7284
52JimPaperWriting7279
53JimManagement7282
54JoanManagement7382
55JoanPaperWriting7379
56JoanCategoryTheory7386
57JoanGrantWriting7384
58JohnFinance7483
59JohnDBA7480
60JohnPaperWriting7479
61JohnManagement7482
62JaneDBA7580
63JanePharma7578
64JanePaperWriting7579
65JaneManagement7582
SpecificSkill
IDSSGSNameSSNameSSSCNamefk_IsType
76PerformingDancingPsychomotor68
77ComputingProgrammingCognitive66
78BusinessPharmaSocial69
79WritingPaperWritingPsychomotor70
80ComputingDBACognitive66
81ComputingCompSciCognitive66
82BusinessManagementSocial69
83BusinessFinanceSocial69
84WritingGrantWritingPsychomotor70
85TeachingLecturingSocial67
86MathCategoryTheoryCognitive71

In this example we begin to see for the first time a more representative example of the full capability of the schema_colimit construction. In this more interesting colimit we are gluing (equating) on a defined overlap that is associated with the slightly different strategies for modeling skill taxonomies. In this case we are assuming that the Skill entity from sSTSpn can be equated with the SpecificSkill entity from sSPSpn and likewise the SkillClass entities from both. We also note what this implies about foreign keys. Since SpecificSkill has a foreign key path through GeneralSkill we equate that path with the foreign key fk_HasClass from sSTSpn. In so doing we consolidate the two schemas in a way that eliminates redundant entities and paths.

Generally this is what a colimit does. It merges multiple objects of a category into a single object in a way that equates all paths from objects in the colimit diagram (in this case schemas) to the universal colimit (in this case the schema_colimit). These path equalities (in this case the pair of paths from a link schema through sSTSpn and sSPSpn to sSTSpn_SPSpn_CoLim) serve to define ways in which structure is merged across the objects in the colimit diagram. Although this approach is supported by AQL there is an equivalent alternative.

One particularly intuitive way to implement a colimit involves deconstructing the colimit into two steps. The first step is to take the coproduct. The coproduct is a special case of a colimit where the colimit diagram consists of a set of discrete objects without any connecting arrows. With such a diagram the colimit assembles the source objects disjointly. The second step is the quotient which provides for the merging of structure across and within the source objects. What is the structure being merged? It is the structure of a category, which consists of objects and paths.

Recall the comparison we illustrated between the use of path equations in the instance declaration associated with iSklSklCls1 and the use of a link instance and pair of transforms used in the construction of isklSklCls2. Similarly the following sequence follows the sequence you have seen before where we disjointly assembled sS, sT and sSnT. In that sequence we followed with a query to create foreign keys. In this sequence we are doing something different. Instead of joining a set of disjoint entities we are gluing a pair of schema with some shared structure.

schema_colimit STSpn_SPSpn_CoLimAuto = quotient sSTSpn + sSPSpn {
	entity_equations
		sSTSpn.SkillClass = sSPSpn.SkillClass
		sSTSpn.Skill = sSPSpn.SpecificSkill
	path_equations
		sSTSpn.SkillClass = sSPSpn.SkillClass
		sSTSpn.Skill = sSPSpn.SpecificSkill
		sSTSpn_fk_HasClass = sSPSpn_fk_IsType.sSPSpn_fk_HasClass

}

As we did in the construction of S_T_SnT_Sum we can relabel components in the colimit schema using the modify command. An important point to notice is that attributes coming from different source schemas must be maintained as distinct attributes in the colimit. This must be done even though conceptually they may duplicate one another. This is essential as there is no guarantee that different sources will be consistent in labeling equivalent records.

schema_colimit STSpn_SPSpn_CoLim = modify STSpn_SPSpn_CoLimAuto {
	rename entities
		sSPSpn_GeneralSkill -> GeneralSkill
		sSPSpn_Person -> Person
		sSPSpn_SkillClass__sSTSpn_SkillClass -> SkillClass
		sSPSpn_SkillPrvdByPerson -> SkillPrvdByPerson
		sSPSpn_SpecificSkill__sSTSpn_Skill -> SpecificSkill
		sSTSpn_SkillNdFrTask -> SkillNdFrTask
		sSTSpn_Task -> Task
	rename foreign_keys
		sSPSpn_GeneralSkill -> GeneralSkill
		sSPSpn_Person -> Person
		sSPSpn_SkillClass__sSTSpn_SkillClass -> SkillClass
		sSPSpn_SkillPrvdByPerson -> SkillPrvdByPerson
		sSPSpn_SpecificSkill__sSTSpn_Skill -> SpecificSkill
		sSTSpn_SkillNdFrTask -> SkillNdFrTask
		sSTSpn_Task -> Task
		sSTSpn_fk_SkillNeeded -> fk_SkillNeeded
		sSTSpn_fk_ForTask -> fk_ForTask
		sSPSpn_fk_SpP_S -> fk_SpP_S
		sSPSpn_fk_SpP_P -> fk_SpP_P
		sSPSpn_fk_IsType -> fk_IsType
		sSPSpn_fk_HasClass -> fk_HasClass
	rename attributes
		sSPSpn_GeneralSkill -> GeneralSkill
		sSPSpn_Person -> Person
		sSPSpn_SkillClass__sSTSpn_SkillClass -> SkillClass
		sSPSpn_SkillPrvdByPerson -> SkillPrvdByPerson
		sSPSpn_SpecificSkill__sSTSpn_Skill -> SpecificSkill
		sSTSpn_SkillNdFrTask -> SkillNdFrTask
		sSTSpn_Task -> Task
		sSTSpn_fk_SkillNeeded -> fk_SkillNeeded
		sSTSpn_fk_ForTask -> fk_ForTask
		sSPSpn_fk_SpP_S -> fk_SpP_S
		sSPSpn_fk_SpP_P -> fk_SpP_P
		sSPSpn_fk_IsType -> fk_IsType
		sSPSpn_fk_HasClass -> fk_HasClass
		sSTSpn_skillclass -> ST_SCNameOnSnT
		sSPSpn_SCName -> SP_SCName
		sSTSpn_task -> ST_TNameOnSnT
		sSTSpn_skill -> ST_SNameOnSnT
		sSTSpn_SName -> ST_SklName
		sSTSpn_TName -> ST_TName
		sSTSpn_SCName -> ST_SCName
		sSTSpn_SCNameOnSkl -> ST_SCNameOnSS
		sSPSpn_SSGSName -> SP_GSNameOnSS
		sSPSpn_SSName -> SP_SSName
		sSPSpn_SSSCName -> SP_SCNameOnSS
		sSPSpn_person -> SP_PNameOnSnP
		sSPSpn_skill -> SP_SNameOnSnP
		sSPSpn_PName -> SP_PName
		sSPSpn_GSSCName -> SP_SCNameOnGS
		sSPSpn_GSName -> SP_GSName
	remove foreign_keys
		sSTSpn_fk_HasClass -> fk_IsType.fk_HasClass
	remove attributes
		
}

Provide addressable labels for the various components of the schema_colimit STSpn_SPSpn_CoLim.

schema sSTSpn_SPSpn_CoLim = getSchema STSpn_SPSpn_CoLim
mapping mSTSpnToSTSpn_SPSpn_CoLim = getMapping STSpn_SPSpn_CoLim sSTSpn
mapping mSPSpnToSTSpn_SPSpn_CoLim = getMapping STSpn_SPSpn_CoLim sSPSpn

Again as we did with the instances coming from the disjoint schemas sS, sT, and sSnT, we must deal with the instance data coming from the two imports. We use coproduct_sigma to land those two instances into the assembled colimit_schema of the two sources schemas sSTSpn and sSPSpn.

instance iST_SP_Sum = coproduct_sigma (mSTSpnToSTSpn_SPSpn_CoLim) (iSTSpnImp) (mSPSpnToSTSpn_SPSpn_CoLim) (iSPSpnImp) : sSTSpn_SPSpn_CoLim
GeneralSkill
IDSP_GSNameSP_SCNameOnGSfk_HasClass
0.fk_IsType0.SP_GSNameOnSS4.SP_SCNameOnSS0.fk_IsType.fk_HasClass
1.fk_IsType1.SP_GSNameOnSS4.SP_SCNameOnSS0.fk_IsType.fk_HasClass
2.fk_IsType2.SP_GSNameOnSS4.SP_SCNameOnSS0.fk_IsType.fk_HasClass
3.fk_IsType3.SP_GSNameOnSS7.SP_SCNameOnSS3.fk_IsType.fk_HasClass
4.fk_IsType4.SP_GSNameOnSS4.SP_SCNameOnSS0.fk_IsType.fk_HasClass
5.fk_IsType5.SP_GSNameOnSS5.SP_SCNameOnSS5.fk_IsType.fk_HasClass
6.fk_IsType6.SP_GSNameOnSS7.SP_SCNameOnSS3.fk_IsType.fk_HasClass
7.fk_IsType7.SP_GSNameOnSS7.SP_SCNameOnSS3.fk_IsType.fk_HasClass
8.fk_IsType8.SP_GSNameOnSS7.SP_SCNameOnSS3.fk_IsType.fk_HasClass
9.fk_IsType9.SP_GSNameOnSS5.SP_SCNameOnSS5.fk_IsType.fk_HasClass
55.fk_SpP_S.fk_IsTypeWritingPsychomotor47
56.fk_SpP_S.fk_IsTypeMathCognitive49
58.fk_SpP_S.fk_IsTypeBusinessSocial48
59.fk_SpP_S.fk_IsTypeComputingCognitive49
67TeachingSocial48
68PerformingPsychomotor47
Person
IDSP_PName
50.fk_SpP_PJim
55.fk_SpP_PJoan
58.fk_SpP_PJohn
62.fk_SpP_PJane
SkillClass
IDSP_SCNameST_SCName
0.fk_IsType.fk_HasClass4.SP_SCNameOnSSCognitive
3.fk_IsType.fk_HasClass7.SP_SCNameOnSSSocial
5.fk_IsType.fk_HasClass5.SP_SCNameOnSSPsychomotor
47Psychomotor76.ST_SCNameOnSS
48Social61.fk_SpP_S.ST_SCNameOnSS
49Cognitive59.fk_SpP_S.ST_SCNameOnSS
SkillNdFrTask
IDST_SCNameOnSnTST_SNameOnSnTST_TNameOnSnTfk_ForTaskfk_SkillNeeded
23CognitiveCategoryTheoryWriteCTPaper100
24PsychomotorPaperWritingWriteCTPaper109
25CognitiveProgrammingWriteAQLTutorial112
26SocialLecturingWriteAQLTutorial113
27PsychomotorPaperWritingWriteAQLTutorial119
28SocialFinanceRunFncIntProject127
29SocialManagementRunFncIntProject128
30CognitiveProgrammingBuildPhrmColim132
31SocialPharmaBuildPhrmColim136
32CognitiveCompSciLaunchCIStartup141
33CognitiveProgrammingLaunchCIStartup142
34PsychomotorGrantWritingLaunchCIStartup145
35PsychomotorPaperWritingLaunchCIStartup149
36CognitiveCompSciBuildAQL151
37CognitiveProgrammingBuildAQL152
38SocialManagementBuildAQL158
39SocialPharmaRunPhrmIntProject166
40SocialManagementRunPhrmIntProject168
41CognitiveCategoryTheoryTeachCT170
42SocialLecturingTeachCT173
43CognitiveCompSciTeachCS181
44SocialLecturingTeachCS183
45CognitiveProgrammingBuildFncColim192
46SocialFinanceBuildFncColim197
SkillPrvdByPerson
IDSP_PNameOnSnPSP_SNameOnSnPfk_SpP_Pfk_SpP_S
50JimCompSci50.fk_SpP_P50.fk_SpP_S
51JimGrantWriting50.fk_SpP_P57.fk_SpP_S
52JimPaperWriting50.fk_SpP_P55.fk_SpP_S
53JimManagement50.fk_SpP_P61.fk_SpP_S
54JoanManagement55.fk_SpP_P61.fk_SpP_S
55JoanPaperWriting55.fk_SpP_P55.fk_SpP_S
56JoanCategoryTheory55.fk_SpP_P56.fk_SpP_S
57JoanGrantWriting55.fk_SpP_P57.fk_SpP_S
58JohnFinance58.fk_SpP_P58.fk_SpP_S
59JohnDBA58.fk_SpP_P59.fk_SpP_S
60JohnPaperWriting58.fk_SpP_P55.fk_SpP_S
61JohnManagement58.fk_SpP_P61.fk_SpP_S
62JaneDBA62.fk_SpP_P59.fk_SpP_S
63JanePharma62.fk_SpP_P63.fk_SpP_S
64JanePaperWriting62.fk_SpP_P55.fk_SpP_S
65JaneManagement62.fk_SpP_P61.fk_SpP_S
SpecificSkill
IDSP_GSNameOnSSSP_SCNameOnSSSP_SSNameST_SCNameOnSSST_SklNamefk_IsType
00.SP_GSNameOnSS4.SP_SCNameOnSS0.SP_SSNameCognitiveCategoryTheory0.fk_IsType
11.SP_GSNameOnSS4.SP_SCNameOnSS1.SP_SSNameCognitiveCompSci1.fk_IsType
22.SP_GSNameOnSS4.SP_SCNameOnSS2.SP_SSNameCognitiveProgramming2.fk_IsType
33.SP_GSNameOnSS7.SP_SCNameOnSS3.SP_SSNameSocialLecturing3.fk_IsType
44.SP_GSNameOnSS4.SP_SCNameOnSS4.SP_SSNameCognitiveDBA4.fk_IsType
55.SP_GSNameOnSS5.SP_SCNameOnSS5.SP_SSNamePsychomotorGrantWriting5.fk_IsType
66.SP_GSNameOnSS7.SP_SCNameOnSS6.SP_SSNameSocialPharma6.fk_IsType
77.SP_GSNameOnSS7.SP_SCNameOnSS7.SP_SSNameSocialFinance7.fk_IsType
88.SP_GSNameOnSS7.SP_SCNameOnSS8.SP_SSNameSocialManagement8.fk_IsType
99.SP_GSNameOnSS5.SP_SCNameOnSS9.SP_SSNamePsychomotorPaperWriting9.fk_IsType
50.fk_SpP_SComputingCognitiveCompSci59.fk_SpP_S.ST_SCNameOnSS50.fk_SpP_S.ST_SklName59.fk_SpP_S.fk_IsType
55.fk_SpP_SWritingPsychomotorPaperWriting76.ST_SCNameOnSS55.fk_SpP_S.ST_SklName55.fk_SpP_S.fk_IsType
56.fk_SpP_SMathCognitiveCategoryTheory59.fk_SpP_S.ST_SCNameOnSS56.fk_SpP_S.ST_SklName56.fk_SpP_S.fk_IsType
57.fk_SpP_SWritingPsychomotorGrantWriting76.ST_SCNameOnSS57.fk_SpP_S.ST_SklName55.fk_SpP_S.fk_IsType
58.fk_SpP_SBusinessSocialFinance61.fk_SpP_S.ST_SCNameOnSS58.fk_SpP_S.ST_SklName58.fk_SpP_S.fk_IsType
59.fk_SpP_SComputingCognitiveDBA59.fk_SpP_S.ST_SCNameOnSS59.fk_SpP_S.ST_SklName59.fk_SpP_S.fk_IsType
61.fk_SpP_SBusinessSocialManagement61.fk_SpP_S.ST_SCNameOnSS61.fk_SpP_S.ST_SklName58.fk_SpP_S.fk_IsType
63.fk_SpP_SBusinessSocialPharma61.fk_SpP_S.ST_SCNameOnSS63.fk_SpP_S.ST_SklName58.fk_SpP_S.fk_IsType
76PerformingPsychomotorDancing76.ST_SCNameOnSS76.ST_SklName68
77ComputingCognitiveProgramming59.fk_SpP_S.ST_SCNameOnSS77.ST_SklName59.fk_SpP_S.fk_IsType
85TeachingSocialLecturing61.fk_SpP_S.ST_SCNameOnSS85.ST_SklName67
Task
IDST_TName
10WriteCTPaper
11WriteAQLTutorial
12RunFncIntProject
13BuildPhrmColim
14LaunchCIStartup
15BuildAQL
16RunPhrmIntProject
17TeachCT
18TeachCS
19BuildFncColim

In order to make instance assembly clear we illustrate two equivalent methods. The second method has the same preconditions as the first but performs the record linking transparently as part of the import process.

Merging records by importing a link table and transforms from tables constructed externally.

In this sequence we create external tables using SQL statements against the tables generated in the export. The essence of this approach is matching records based on attributes. Clearly this will only work if we can get exact string matches. This process can be generalized using any number of technical enhancements to include fuzzing matching using things like edit distance or matching through machine learning approaches.

As is well known reliable integration depends on standardization and/or investment in the construction of translations between systems that make use of different reference information, e.g. skill classification in this case.

pragma pSetUpSkillMatch = sql "com.mysql.jdbc.Driver" "jdbc:mysql://mysql.catinf.com:3306/ci_test?user=catinfcom&password=FDiUY!pr" {
"DROP TABLE IF EXISTS SkillMatch"
"DROP TABLE IF EXISTS SkillClassMatch"
"create table SkillMatch as
select
  concat('s', t.id) as sm_id,
  p.id as ps_id,
  t.id as ts_id,
  t.fk_HasClass
from
  sps_SpecificSkill p
  join sts_Skill t
    on p.SSName = t.SName
  join sps_GeneralSkill gs
    on p.fk_IsType = gs.id"
"create table SkillClassMatch as
select
  concat('sc', t.id) as scm_id,
  p.id as psc_id,
  t.id as tsc_id
from
  sps_SkillClass as p
  join sts_SkillClass as t
    on p.SCName = t.SCName"
}
START
DROP TABLE IF EXISTS SkillMatch

Updated 0 rows.
END

START
DROP TABLE IF EXISTS SkillClassMatch

Updated 0 rows.
END

START
create table SkillMatch as
select
  concat('s', t.id) as sm_id,
  p.id as ps_id,
  t.id as ts_id,
  t.fk_HasClass
from
  sps_SpecificSkill p
  join sts_Skill t
    on p.SSName = t.SName
  join sps_GeneralSkill gs
    on p.fk_IsType = gs.id

Updated 10 rows.
END

START
create table SkillClassMatch as
select
  concat('sc', t.id) as scm_id,
  p.id as psc_id,
  t.id as tsc_id
from
  sps_SkillClass as p
  join sts_SkillClass as t
    on p.SCName = t.SCName

Updated 3 rows.
END

Notice that this instance import is very different than the ones we used to import iSTSpnImp and iSPSpnImp. We have neglected to specify all entities and of the entities we have specified we are only importing the ids. In order to accomplish this we must first specify the import_joined option as false. This allows us to import each attribute in a separate select statement. In this case we are only interested in the generators and so only one select statement is specified for each incoming entity. The second option we need to override is specify that we import the instance as a theory. This incurs the theorem proving burden of a literal instance declaration but allows for a partial specification of the instance. The consequence of these two options is to allow us to import an instance that essentially the same as the literal iSklLnk instance above.

instance iSkillMatch = import_jdbc sSTSpn_SPSpn_CoLim "com.mysql.jdbc.Driver" "jdbc:mysql://mysql.catinf.com:3306/ci_test?user=catinfcom&password=FDiUY!pr" {
	SkillClass -> select scm_id from SkillClassMatch
	SpecificSkill -> select sm_id from SkillMatch
	options
		import_joined = false
		import_as_theory = true
}
GeneralSkill
IDSP_GSNameSP_SCNameOnGSfk_HasClass
s0.fk_IsTypes0.SP_GSNameOnSSs0.SP_SCNameOnSSs0.fk_IsType.fk_HasClass
s1.fk_IsTypes1.SP_GSNameOnSSs1.SP_SCNameOnSSs1.fk_IsType.fk_HasClass
s2.fk_IsTypes2.SP_GSNameOnSSs2.SP_SCNameOnSSs2.fk_IsType.fk_HasClass
s3.fk_IsTypes3.SP_GSNameOnSSs3.SP_SCNameOnSSs3.fk_IsType.fk_HasClass
s4.fk_IsTypes4.SP_GSNameOnSSs4.SP_SCNameOnSSs4.fk_IsType.fk_HasClass
s5.fk_IsTypes5.SP_GSNameOnSSs5.SP_SCNameOnSSs5.fk_IsType.fk_HasClass
s6.fk_IsTypes6.SP_GSNameOnSSs6.SP_SCNameOnSSs6.fk_IsType.fk_HasClass
s7.fk_IsTypes7.SP_GSNameOnSSs7.SP_SCNameOnSSs7.fk_IsType.fk_HasClass
s8.fk_IsTypes8.SP_GSNameOnSSs8.SP_SCNameOnSSs8.fk_IsType.fk_HasClass
s9.fk_IsTypes9.SP_GSNameOnSSs9.SP_SCNameOnSSs9.fk_IsType.fk_HasClass
Person
IDSP_PName
SkillClass
IDSP_SCNameST_SCName
s0.fk_IsType.fk_HasClasss0.SP_SCNameOnSSs0.ST_SCNameOnSS
s1.fk_IsType.fk_HasClasss1.SP_SCNameOnSSs1.ST_SCNameOnSS
s2.fk_IsType.fk_HasClasss2.SP_SCNameOnSSs2.ST_SCNameOnSS
s3.fk_IsType.fk_HasClasss3.SP_SCNameOnSSs3.ST_SCNameOnSS
s4.fk_IsType.fk_HasClasss4.SP_SCNameOnSSs4.ST_SCNameOnSS
s5.fk_IsType.fk_HasClasss5.SP_SCNameOnSSs5.ST_SCNameOnSS
s6.fk_IsType.fk_HasClasss6.SP_SCNameOnSSs6.ST_SCNameOnSS
s7.fk_IsType.fk_HasClasss7.SP_SCNameOnSSs7.ST_SCNameOnSS
s8.fk_IsType.fk_HasClasss8.SP_SCNameOnSSs8.ST_SCNameOnSS
s9.fk_IsType.fk_HasClasss9.SP_SCNameOnSSs9.ST_SCNameOnSS
sc20sc20.SP_SCNamesc20.ST_SCName
sc21sc21.SP_SCNamesc21.ST_SCName
sc22sc22.SP_SCNamesc22.ST_SCName
SkillNdFrTask
IDST_SCNameOnSnTST_SNameOnSnTST_TNameOnSnTfk_ForTaskfk_SkillNeeded
SkillPrvdByPerson
IDSP_PNameOnSnPSP_SNameOnSnPfk_SpP_Pfk_SpP_S
SpecificSkill
IDSP_GSNameOnSSSP_SCNameOnSSSP_SSNameST_SCNameOnSSST_SklNamefk_IsType
s0s0.SP_GSNameOnSSs0.SP_SCNameOnSSs0.SP_SSNames0.ST_SCNameOnSSs0.ST_SklNames0.fk_IsType
s1s1.SP_GSNameOnSSs1.SP_SCNameOnSSs1.SP_SSNames1.ST_SCNameOnSSs1.ST_SklNames1.fk_IsType
s2s2.SP_GSNameOnSSs2.SP_SCNameOnSSs2.SP_SSNames2.ST_SCNameOnSSs2.ST_SklNames2.fk_IsType
s3s3.SP_GSNameOnSSs3.SP_SCNameOnSSs3.SP_SSNames3.ST_SCNameOnSSs3.ST_SklNames3.fk_IsType
s4s4.SP_GSNameOnSSs4.SP_SCNameOnSSs4.SP_SSNames4.ST_SCNameOnSSs4.ST_SklNames4.fk_IsType
s5s5.SP_GSNameOnSSs5.SP_SCNameOnSSs5.SP_SSNames5.ST_SCNameOnSSs5.ST_SklNames5.fk_IsType
s6s6.SP_GSNameOnSSs6.SP_SCNameOnSSs6.SP_SSNames6.ST_SCNameOnSSs6.ST_SklNames6.fk_IsType
s7s7.SP_GSNameOnSSs7.SP_SCNameOnSSs7.SP_SSNames7.ST_SCNameOnSSs7.ST_SklNames7.fk_IsType
s8s8.SP_GSNameOnSSs8.SP_SCNameOnSSs8.SP_SSNames8.ST_SCNameOnSSs8.ST_SklNames8.fk_IsType
s9s9.SP_GSNameOnSSs9.SP_SCNameOnSSs9.SP_SSNames9.ST_SCNameOnSSs9.ST_SklNames9.fk_IsType
Task
IDST_TName

The pair of transform imports below like the instance transform above are restricted to record ids. It may be instructive to compare the results of the iSkillMatch instance and tSMToSTSpn and tSMToSPSpn transforms with the literal iSLnk instance and the literal tS1 and tS2 transforms we created to merge skill class Skolem terms with skill class generators and labels.

transform tSMToSTSpn = import_jdbc "com.mysql.jdbc.Driver" "jdbc:mysql://mysql.catinf.com:3306/ci_test?user=catinfcom&password=FDiUY!pr" : iSkillMatch -> iST_SP_Sum {
	SkillClass -> select scm_id, tsc_id from SkillClassMatch
	SpecificSkill -> select sm_id, ts_id from SkillMatch
}
transform tSMToSPSpn = import_jdbc "com.mysql.jdbc.Driver" "jdbc:mysql://mysql.catinf.com:3306/ci_test?user=catinfcom&password=FDiUY!pr" : iSkillMatch -> iST_SP_Sum {
	SkillClass -> select scm_id, psc_id from SkillClassMatch
	SpecificSkill -> select sm_id, ps_id from SkillMatch
}
instance iSTSpn_SPSpn_Merge1 = coequalize tSMToSTSpn tSMToSPSpn
GeneralSkill
IDSP_GSNameSP_SCNameOnGSfk_HasClass
0.fk_IsTypeMathCognitive0.fk_IsType.fk_HasClass
1.fk_IsTypeComputingCognitive0.fk_IsType.fk_HasClass
3.fk_IsTypeTeachingSocial3.fk_IsType.fk_HasClass
5.fk_IsTypeWritingPsychomotor5.fk_IsType.fk_HasClass
6.fk_IsTypeBusinessSocial3.fk_IsType.fk_HasClass
68PerformingPsychomotor5.fk_IsType.fk_HasClass
Person
IDSP_PName
50.fk_SpP_PJim
55.fk_SpP_PJoan
58.fk_SpP_PJohn
62.fk_SpP_PJane
SkillClass
IDSP_SCNameST_SCName
0.fk_IsType.fk_HasClassCognitiveCognitive
3.fk_IsType.fk_HasClassSocialSocial
5.fk_IsType.fk_HasClassPsychomotorPsychomotor
SkillNdFrTask
IDST_SCNameOnSnTST_SNameOnSnTST_TNameOnSnTfk_ForTaskfk_SkillNeeded
23CognitiveCategoryTheoryWriteCTPaper100
24PsychomotorPaperWritingWriteCTPaper109
25CognitiveProgrammingWriteAQLTutorial112
26SocialLecturingWriteAQLTutorial113
27PsychomotorPaperWritingWriteAQLTutorial119
28SocialFinanceRunFncIntProject127
29SocialManagementRunFncIntProject128
30CognitiveProgrammingBuildPhrmColim132
31SocialPharmaBuildPhrmColim136
32CognitiveCompSciLaunchCIStartup141
33CognitiveProgrammingLaunchCIStartup142
34PsychomotorGrantWritingLaunchCIStartup145
35PsychomotorPaperWritingLaunchCIStartup149
36CognitiveCompSciBuildAQL151
37CognitiveProgrammingBuildAQL152
38SocialManagementBuildAQL158
39SocialPharmaRunPhrmIntProject166
40SocialManagementRunPhrmIntProject168
41CognitiveCategoryTheoryTeachCT170
42SocialLecturingTeachCT173
43CognitiveCompSciTeachCS181
44SocialLecturingTeachCS183
45CognitiveProgrammingBuildFncColim192
46SocialFinanceBuildFncColim197
SkillPrvdByPerson
IDSP_PNameOnSnPSP_SNameOnSnPfk_SpP_Pfk_SpP_S
50JimCompSci50.fk_SpP_P1
51JimGrantWriting50.fk_SpP_P5
52JimPaperWriting50.fk_SpP_P9
53JimManagement50.fk_SpP_P8
54JoanManagement55.fk_SpP_P8
55JoanPaperWriting55.fk_SpP_P9
56JoanCategoryTheory55.fk_SpP_P0
57JoanGrantWriting55.fk_SpP_P5
58JohnFinance58.fk_SpP_P7
59JohnDBA58.fk_SpP_P4
60JohnPaperWriting58.fk_SpP_P9
61JohnManagement58.fk_SpP_P8
62JaneDBA62.fk_SpP_P4
63JanePharma62.fk_SpP_P6
64JanePaperWriting62.fk_SpP_P9
65JaneManagement62.fk_SpP_P8
SpecificSkill
IDSP_GSNameOnSSSP_SCNameOnSSSP_SSNameST_SCNameOnSSST_SklNamefk_IsType
0MathCognitiveCategoryTheoryCognitiveCategoryTheory0.fk_IsType
1ComputingCognitiveCompSciCognitiveCompSci1.fk_IsType
2ComputingCognitiveProgrammingCognitiveProgramming1.fk_IsType
3TeachingSocialLecturingSocialLecturing3.fk_IsType
4ComputingCognitiveDBACognitiveDBA1.fk_IsType
5WritingPsychomotorGrantWritingPsychomotorGrantWriting5.fk_IsType
6BusinessSocialPharmaSocialPharma6.fk_IsType
7BusinessSocialFinanceSocialFinance6.fk_IsType
8BusinessSocialManagementSocialManagement6.fk_IsType
9WritingPsychomotorPaperWritingPsychomotorPaperWriting5.fk_IsType
76PerformingPsychomotorDancingPsychomotor76.ST_SklName68
Task
IDST_TName
10WriteCTPaper
11WriteAQLTutorial
12RunFncIntProject
13BuildPhrmColim
14LaunchCIStartup
15BuildAQL
16RunPhrmIntProject
17TeachCT
18TeachCS
19BuildFncColim

Finally we do this the easy way. We import everything in one go using the quotient_jdbc import. It is important to note that this does require the construction of the SkillMatch and SkillClassMatch tables in the source RDBMS as a precondition.

instance iSTSpn_SPSpn_Merge2 = quotient_jdbc com.mysql.jdbc.Driver jdbc:mysql://mysql.catinf.com:3306/ci_test?user=catinfcom&password=FDiUY!pr {
select
   ps_id as sp_id,
   ts_id as st_id
from
   SkillMatch
union
select
   psc_id as sp_id,
   tsc_id as st_id
from
   SkillClassMatch
}
GeneralSkill
IDSP_GSNameSP_SCNameOnGSfk_HasClass
0.fk_IsTypeMathCognitive0.fk_IsType.fk_HasClass
1.fk_IsTypeComputingCognitive0.fk_IsType.fk_HasClass
3.fk_IsTypeTeachingSocial3.fk_IsType.fk_HasClass
5.fk_IsTypeWritingPsychomotor5.fk_IsType.fk_HasClass
6.fk_IsTypeBusinessSocial3.fk_IsType.fk_HasClass
68PerformingPsychomotor5.fk_IsType.fk_HasClass
Person
IDSP_PName
50.fk_SpP_PJim
55.fk_SpP_PJoan
58.fk_SpP_PJohn
62.fk_SpP_PJane
SkillClass
IDSP_SCNameST_SCName
0.fk_IsType.fk_HasClassCognitiveCognitive
3.fk_IsType.fk_HasClassSocialSocial
5.fk_IsType.fk_HasClassPsychomotorPsychomotor
SkillNdFrTask
IDST_SCNameOnSnTST_SNameOnSnTST_TNameOnSnTfk_ForTaskfk_SkillNeeded
23CognitiveCategoryTheoryWriteCTPaper100
24PsychomotorPaperWritingWriteCTPaper109
25CognitiveProgrammingWriteAQLTutorial112
26SocialLecturingWriteAQLTutorial113
27PsychomotorPaperWritingWriteAQLTutorial119
28SocialFinanceRunFncIntProject127
29SocialManagementRunFncIntProject128
30CognitiveProgrammingBuildPhrmColim132
31SocialPharmaBuildPhrmColim136
32CognitiveCompSciLaunchCIStartup141
33CognitiveProgrammingLaunchCIStartup142
34PsychomotorGrantWritingLaunchCIStartup145
35PsychomotorPaperWritingLaunchCIStartup149
36CognitiveCompSciBuildAQL151
37CognitiveProgrammingBuildAQL152
38SocialManagementBuildAQL158
39SocialPharmaRunPhrmIntProject166
40SocialManagementRunPhrmIntProject168
41CognitiveCategoryTheoryTeachCT170
42SocialLecturingTeachCT173
43CognitiveCompSciTeachCS181
44SocialLecturingTeachCS183
45CognitiveProgrammingBuildFncColim192
46SocialFinanceBuildFncColim197
SkillPrvdByPerson
IDSP_PNameOnSnPSP_SNameOnSnPfk_SpP_Pfk_SpP_S
50JimCompSci50.fk_SpP_P1
51JimGrantWriting50.fk_SpP_P5
52JimPaperWriting50.fk_SpP_P9
53JimManagement50.fk_SpP_P8
54JoanManagement55.fk_SpP_P8
55JoanPaperWriting55.fk_SpP_P9
56JoanCategoryTheory55.fk_SpP_P0
57JoanGrantWriting55.fk_SpP_P5
58JohnFinance58.fk_SpP_P7
59JohnDBA58.fk_SpP_P4
60JohnPaperWriting58.fk_SpP_P9
61JohnManagement58.fk_SpP_P8
62JaneDBA62.fk_SpP_P4
63JanePharma62.fk_SpP_P6
64JanePaperWriting62.fk_SpP_P9
65JaneManagement62.fk_SpP_P8
SpecificSkill
IDSP_GSNameOnSSSP_SCNameOnSSSP_SSNameST_SCNameOnSSST_SklNamefk_IsType
0MathCognitiveCategoryTheoryCognitiveCategoryTheory0.fk_IsType
1ComputingCognitiveCompSciCognitiveCompSci1.fk_IsType
2ComputingCognitiveProgrammingCognitiveProgramming1.fk_IsType
3TeachingSocialLecturingSocialLecturing3.fk_IsType
4ComputingCognitiveDBACognitiveDBA1.fk_IsType
5WritingPsychomotorGrantWritingPsychomotorGrantWriting5.fk_IsType
6BusinessSocialPharmaSocialPharma6.fk_IsType
7BusinessSocialFinanceSocialFinance6.fk_IsType
8BusinessSocialManagementSocialManagement6.fk_IsType
9WritingPsychomotorPaperWritingPsychomotorPaperWriting5.fk_IsType
76PerformingPsychomotorDancingPsychomotor76.ST_SklName68
Task
IDST_TName
10WriteCTPaper
11WriteAQLTutorial
12RunFncIntProject
13BuildPhrmColim
14LaunchCIStartup
15BuildAQL
16RunPhrmIntProject
17TeachCT
18TeachCS
19BuildFncColim

Prior to wrapping up let's return to an examination of the data migration functors sigma, delta and pi as well as their connection with querying. We mentioned before that the coproduct_sigma could be constructed into first the migration of instances separately into the colimit schema and then taking the coproduct of each result. Let's examine what some of those intermediate steps do.

instance iSTSpnImpInCoLim = sigma mSTSpnToSTSpn_SPSpn_CoLim iSTSpnImp
GeneralSkill
IDSP_GSNameSP_SCNameOnGSfk_HasClass
0.fk_IsType0.SP_GSNameOnSS1.SP_SCNameOnSS0.fk_IsType.fk_HasClass
1.fk_IsType1.SP_GSNameOnSS1.SP_SCNameOnSS0.fk_IsType.fk_HasClass
2.fk_IsType2.SP_GSNameOnSS1.SP_SCNameOnSS0.fk_IsType.fk_HasClass
3.fk_IsType3.SP_GSNameOnSS3.SP_SCNameOnSS3.fk_IsType.fk_HasClass
4.fk_IsType4.SP_GSNameOnSS1.SP_SCNameOnSS0.fk_IsType.fk_HasClass
5.fk_IsType5.SP_GSNameOnSS9.SP_SCNameOnSS5.fk_IsType.fk_HasClass
6.fk_IsType6.SP_GSNameOnSS3.SP_SCNameOnSS3.fk_IsType.fk_HasClass
7.fk_IsType7.SP_GSNameOnSS3.SP_SCNameOnSS3.fk_IsType.fk_HasClass
8.fk_IsType8.SP_GSNameOnSS3.SP_SCNameOnSS3.fk_IsType.fk_HasClass
9.fk_IsType9.SP_GSNameOnSS9.SP_SCNameOnSS5.fk_IsType.fk_HasClass
Person
IDSP_PName
SkillClass
IDSP_SCNameST_SCName
0.fk_IsType.fk_HasClass1.SP_SCNameOnSSCognitive
3.fk_IsType.fk_HasClass3.SP_SCNameOnSSSocial
5.fk_IsType.fk_HasClass9.SP_SCNameOnSSPsychomotor
SkillNdFrTask
IDST_SCNameOnSnTST_SNameOnSnTST_TNameOnSnTfk_ForTaskfk_SkillNeeded
23CognitiveCategoryTheoryWriteCTPaper100
24PsychomotorPaperWritingWriteCTPaper109
25CognitiveProgrammingWriteAQLTutorial112
26SocialLecturingWriteAQLTutorial113
27PsychomotorPaperWritingWriteAQLTutorial119
28SocialFinanceRunFncIntProject127
29SocialManagementRunFncIntProject128
30CognitiveProgrammingBuildPhrmColim132
31SocialPharmaBuildPhrmColim136
32CognitiveCompSciLaunchCIStartup141
33CognitiveProgrammingLaunchCIStartup142
34PsychomotorGrantWritingLaunchCIStartup145
35PsychomotorPaperWritingLaunchCIStartup149
36CognitiveCompSciBuildAQL151
37CognitiveProgrammingBuildAQL152
38SocialManagementBuildAQL158
39SocialPharmaRunPhrmIntProject166
40SocialManagementRunPhrmIntProject168
41CognitiveCategoryTheoryTeachCT170
42SocialLecturingTeachCT173
43CognitiveCompSciTeachCS181
44SocialLecturingTeachCS183
45CognitiveProgrammingBuildFncColim192
46SocialFinanceBuildFncColim197
SkillPrvdByPerson
IDSP_PNameOnSnPSP_SNameOnSnPfk_SpP_Pfk_SpP_S
SpecificSkill
IDSP_GSNameOnSSSP_SCNameOnSSSP_SSNameST_SCNameOnSSST_SklNamefk_IsType
00.SP_GSNameOnSS1.SP_SCNameOnSS0.SP_SSNameCognitiveCategoryTheory0.fk_IsType
11.SP_GSNameOnSS1.SP_SCNameOnSS1.SP_SSNameCognitiveCompSci1.fk_IsType
22.SP_GSNameOnSS1.SP_SCNameOnSS2.SP_SSNameCognitiveProgramming2.fk_IsType
33.SP_GSNameOnSS3.SP_SCNameOnSS3.SP_SSNameSocialLecturing3.fk_IsType
44.SP_GSNameOnSS1.SP_SCNameOnSS4.SP_SSNameCognitiveDBA4.fk_IsType
55.SP_GSNameOnSS9.SP_SCNameOnSS5.SP_SSNamePsychomotorGrantWriting5.fk_IsType
66.SP_GSNameOnSS3.SP_SCNameOnSS6.SP_SSNameSocialPharma6.fk_IsType
77.SP_GSNameOnSS3.SP_SCNameOnSS7.SP_SSNameSocialFinance7.fk_IsType
88.SP_GSNameOnSS3.SP_SCNameOnSS8.SP_SSNameSocialManagement8.fk_IsType
99.SP_GSNameOnSS9.SP_SCNameOnSS9.SP_SSNamePsychomotorPaperWriting9.fk_IsType
Task
IDST_TName
10WriteCTPaper
11WriteAQLTutorial
12RunFncIntProject
13BuildPhrmColim
14LaunchCIStartup
15BuildAQL
16RunPhrmIntProject
17TeachCT
18TeachCS
19BuildFncColim

As you can see by inspecting the runtime result we have loaded the instance iSTSpnImp into the assembled schema we created using the colimit. Since there are entities in the colimit schema not present in sSTSpn. You will notice that some entities are empty; in particular, those entities that arose from sSPSpn. There are also Skolem terms that are generated due to the introduction GeneralSkill referenced by SpecialSkill not present in sSTSpn.

Let's close the loop using delta right adjoint to sigma. We present the instance generated by sigma above to delta at the same map. We have pushed iSTSpnImp forward through sigma and then pulled the result back through the same schema map.

instance iSgmDltSTSpnImp = delta mSTSpnToSTSpn_SPSpn_CoLim iSTSpnImpInCoLim
Skill
IDSCNameOnSklSNamefk_HasClass
Skill 0CognitiveCategoryTheorySkillClass 0.fk_IsType.fk_HasClass
Skill 1CognitiveCompSciSkillClass 0.fk_IsType.fk_HasClass
Skill 2CognitiveProgrammingSkillClass 0.fk_IsType.fk_HasClass
Skill 3SocialLecturingSkillClass 3.fk_IsType.fk_HasClass
Skill 4CognitiveDBASkillClass 0.fk_IsType.fk_HasClass
Skill 5PsychomotorGrantWritingSkillClass 5.fk_IsType.fk_HasClass
Skill 6SocialPharmaSkillClass 3.fk_IsType.fk_HasClass
Skill 7SocialFinanceSkillClass 3.fk_IsType.fk_HasClass
Skill 8SocialManagementSkillClass 3.fk_IsType.fk_HasClass
Skill 9PsychomotorPaperWritingSkillClass 5.fk_IsType.fk_HasClass
SkillClass
IDSCName
SkillClass 0.fk_IsType.fk_HasClassCognitive
SkillClass 3.fk_IsType.fk_HasClassSocial
SkillClass 5.fk_IsType.fk_HasClassPsychomotor
SkillNdFrTask
IDskillskillclasstaskfk_ForTaskfk_SkillNeeded
SkillNdFrTask 23CategoryTheoryCognitiveWriteCTPaperTask 10Skill 0
SkillNdFrTask 24PaperWritingPsychomotorWriteCTPaperTask 10Skill 9
SkillNdFrTask 25ProgrammingCognitiveWriteAQLTutorialTask 11Skill 2
SkillNdFrTask 26LecturingSocialWriteAQLTutorialTask 11Skill 3
SkillNdFrTask 27PaperWritingPsychomotorWriteAQLTutorialTask 11Skill 9
SkillNdFrTask 28FinanceSocialRunFncIntProjectTask 12Skill 7
SkillNdFrTask 29ManagementSocialRunFncIntProjectTask 12Skill 8
SkillNdFrTask 30ProgrammingCognitiveBuildPhrmColimTask 13Skill 2
SkillNdFrTask 31PharmaSocialBuildPhrmColimTask 13Skill 6
SkillNdFrTask 32CompSciCognitiveLaunchCIStartupTask 14Skill 1
SkillNdFrTask 33ProgrammingCognitiveLaunchCIStartupTask 14Skill 2
SkillNdFrTask 34GrantWritingPsychomotorLaunchCIStartupTask 14Skill 5
SkillNdFrTask 35PaperWritingPsychomotorLaunchCIStartupTask 14Skill 9
SkillNdFrTask 36CompSciCognitiveBuildAQLTask 15Skill 1
SkillNdFrTask 37ProgrammingCognitiveBuildAQLTask 15Skill 2
SkillNdFrTask 38ManagementSocialBuildAQLTask 15Skill 8
SkillNdFrTask 39PharmaSocialRunPhrmIntProjectTask 16Skill 6
SkillNdFrTask 40ManagementSocialRunPhrmIntProjectTask 16Skill 8
SkillNdFrTask 41CategoryTheoryCognitiveTeachCTTask 17Skill 0
SkillNdFrTask 42LecturingSocialTeachCTTask 17Skill 3
SkillNdFrTask 43CompSciCognitiveTeachCSTask 18Skill 1
SkillNdFrTask 44LecturingSocialTeachCSTask 18Skill 3
SkillNdFrTask 45ProgrammingCognitiveBuildFncColimTask 19Skill 2
SkillNdFrTask 46FinanceSocialBuildFncColimTask 19Skill 7
Task
IDTName
Task 10WriteCTPaper
Task 11WriteAQLTutorial
Task 12RunFncIntProject
Task 13BuildPhrmColim
Task 14LaunchCIStartup
Task 15BuildAQL
Task 16RunPhrmIntProject
Task 17TeachCT
Task 18TeachCS
Task 19BuildFncColim

We see an instance that looks the same as iSTSpnImp originally imported. Intuitively we can think of this as delta projecting the part of the colimit schema instance back into the schema from which it came. It should not be surprising that this would yield the identity. This can be verified by looking at the unit transform and comparing it to the identity transform.

transform tUnit = unit mSTSpnToSTSpn_SPSpn_CoLim iSTSpnImp
transform tIdSTSpnImp = id iSTSpnImp

The SkillClass transform output is not immediately obviously the identity, but if you unravel the provenance you will find that it is. Start by looking at instance iSTSpnImp and you will see for entity Skill:

ID     SCNameOnSkl    fk_HasClass
---    -----------    -----------
100    Cognitive      122
...    ...            ...
102    Social         120
...    ...            ...
105    Psychomotor    121

Now look at the result of the quotient on the schema colimit in any of the three instances above, say iSTSpn_SPSpn_Merge3. Specifically the SkillClass entity:

ID                             ST_SCName
---------------------------    -----------
[100.fk_IsType.fk_HasClass]    Cognitive
[102.fk_IsType.fk_HasClass]    Social
[105.fk_IsType.fk_HasClass]    Psychomotor

Finally look at the instance iSgmDltSTSpnImp and you will see for SkillClass:

ID                                         ST_SCName
---------------------------------------    -----------
<SkillClass[100.fk_IsType.fk_HasClass]>    Cognitive
<SkillClass[102.fk_IsType.fk_HasClass]>    Social
<SkillClass[105.fk_IsType.fk_HasClass]>    Psychomotor

We can conclude:

<SkillClass[100.fk_IsType.fk_HasClass]>    -> 122
<SkillClass[102.fk_IsType.fk_HasClass]>    -> 120
<SkillClass[105.fk_IsType.fk_HasClass]>    -> 121 

Let's try the other direction. We start with the merged instance in the schema colimit and use delta to project back through the mapping that inserted the schema sSTSpn into the schema colimit. As you would expect you loose almost all of the data that was supplied by iSPSpnImp. We now have 11 skills rather than the 10 skills provided by iSTSpnImp and so have picked up an additional Skolem term due to the "dancing" skill that came from iSPSpnImp.

instance iCoLimInSTSpn = delta mSTSpnToSTSpn_SPSpn_CoLim iSTSpn_SPSpn_Merge2
Skill
IDSCNameOnSklSNamefk_HasClass
Skill 0CognitiveCategoryTheorySkillClass 0.fk_IsType.fk_HasClass
Skill 1CognitiveCompSciSkillClass 0.fk_IsType.fk_HasClass
Skill 2CognitiveProgrammingSkillClass 0.fk_IsType.fk_HasClass
Skill 3SocialLecturingSkillClass 3.fk_IsType.fk_HasClass
Skill 4CognitiveDBASkillClass 0.fk_IsType.fk_HasClass
Skill 5PsychomotorGrantWritingSkillClass 5.fk_IsType.fk_HasClass
Skill 6SocialPharmaSkillClass 3.fk_IsType.fk_HasClass
Skill 7SocialFinanceSkillClass 3.fk_IsType.fk_HasClass
Skill 8SocialManagementSkillClass 3.fk_IsType.fk_HasClass
Skill 9PsychomotorPaperWritingSkillClass 5.fk_IsType.fk_HasClass
Skill 76Psychomotor76.ST_SklNameSkillClass 5.fk_IsType.fk_HasClass
SkillClass
IDSCName
SkillClass 0.fk_IsType.fk_HasClassCognitive
SkillClass 3.fk_IsType.fk_HasClassSocial
SkillClass 5.fk_IsType.fk_HasClassPsychomotor
SkillNdFrTask
IDskillskillclasstaskfk_ForTaskfk_SkillNeeded
SkillNdFrTask 23CategoryTheoryCognitiveWriteCTPaperTask 10Skill 0
SkillNdFrTask 24PaperWritingPsychomotorWriteCTPaperTask 10Skill 9
SkillNdFrTask 25ProgrammingCognitiveWriteAQLTutorialTask 11Skill 2
SkillNdFrTask 26LecturingSocialWriteAQLTutorialTask 11Skill 3
SkillNdFrTask 27PaperWritingPsychomotorWriteAQLTutorialTask 11Skill 9
SkillNdFrTask 28FinanceSocialRunFncIntProjectTask 12Skill 7
SkillNdFrTask 29ManagementSocialRunFncIntProjectTask 12Skill 8
SkillNdFrTask 30ProgrammingCognitiveBuildPhrmColimTask 13Skill 2
SkillNdFrTask 31PharmaSocialBuildPhrmColimTask 13Skill 6
SkillNdFrTask 32CompSciCognitiveLaunchCIStartupTask 14Skill 1
SkillNdFrTask 33ProgrammingCognitiveLaunchCIStartupTask 14Skill 2
SkillNdFrTask 34GrantWritingPsychomotorLaunchCIStartupTask 14Skill 5
SkillNdFrTask 35PaperWritingPsychomotorLaunchCIStartupTask 14Skill 9
SkillNdFrTask 36CompSciCognitiveBuildAQLTask 15Skill 1
SkillNdFrTask 37ProgrammingCognitiveBuildAQLTask 15Skill 2
SkillNdFrTask 38ManagementSocialBuildAQLTask 15Skill 8
SkillNdFrTask 39PharmaSocialRunPhrmIntProjectTask 16Skill 6
SkillNdFrTask 40ManagementSocialRunPhrmIntProjectTask 16Skill 8
SkillNdFrTask 41CategoryTheoryCognitiveTeachCTTask 17Skill 0
SkillNdFrTask 42LecturingSocialTeachCTTask 17Skill 3
SkillNdFrTask 43CompSciCognitiveTeachCSTask 18Skill 1
SkillNdFrTask 44LecturingSocialTeachCSTask 18Skill 3
SkillNdFrTask 45ProgrammingCognitiveBuildFncColimTask 19Skill 2
SkillNdFrTask 46FinanceSocialBuildFncColimTask 19Skill 7
Task
IDTName
Task 10WriteCTPaper
Task 11WriteAQLTutorial
Task 12RunFncIntProject
Task 13BuildPhrmColim
Task 14LaunchCIStartup
Task 15BuildAQL
Task 16RunPhrmIntProject
Task 17TeachCT
Task 18TeachCS
Task 19BuildFncColim

Sigma then pushes this projected instance back into the schema colimit and we end up with an instance that looks like the one sigma pushed forward from iSTSpnImp but with the extra skill record. The round trip has eliminated all traces of iSTSpn_SPSpn_Merge3 that came from iSPSpnImp except the record that additional "Dancing" skill that is now Skolemized.

instance iDltSgmCoLim = sigma mSTSpnToSTSpn_SPSpn_CoLim iCoLimInSTSpn
GeneralSkill
IDSP_GSNameSP_SCNameOnGSfk_HasClass
Skill 0.fk_IsTypeSkill 0.SP_GSNameOnSSSkill 2.SP_SCNameOnSSSkill 2.fk_IsType.fk_HasClass
Skill 1.fk_IsTypeSkill 1.SP_GSNameOnSSSkill 2.SP_SCNameOnSSSkill 2.fk_IsType.fk_HasClass
Skill 2.fk_IsTypeSkill 2.SP_GSNameOnSSSkill 2.SP_SCNameOnSSSkill 2.fk_IsType.fk_HasClass
Skill 3.fk_IsTypeSkill 3.SP_GSNameOnSSSkill 3.SP_SCNameOnSSSkill 3.fk_IsType.fk_HasClass
Skill 4.fk_IsTypeSkill 4.SP_GSNameOnSSSkill 2.SP_SCNameOnSSSkill 2.fk_IsType.fk_HasClass
Skill 5.fk_IsTypeSkill 5.SP_GSNameOnSSSkill 9.SP_SCNameOnSSSkill 5.fk_IsType.fk_HasClass
Skill 6.fk_IsTypeSkill 6.SP_GSNameOnSSSkill 3.SP_SCNameOnSSSkill 3.fk_IsType.fk_HasClass
Skill 7.fk_IsTypeSkill 7.SP_GSNameOnSSSkill 3.SP_SCNameOnSSSkill 3.fk_IsType.fk_HasClass
Skill 8.fk_IsTypeSkill 8.SP_GSNameOnSSSkill 3.SP_SCNameOnSSSkill 3.fk_IsType.fk_HasClass
Skill 9.fk_IsTypeSkill 9.SP_GSNameOnSSSkill 9.SP_SCNameOnSSSkill 5.fk_IsType.fk_HasClass
Skill 76.fk_IsTypeSkill 76.SP_GSNameOnSSSkill 9.SP_SCNameOnSSSkill 5.fk_IsType.fk_HasClass
Person
IDSP_PName
SkillClass
IDSP_SCNameST_SCName
Skill 2.fk_IsType.fk_HasClassSkill 2.SP_SCNameOnSSCognitive
Skill 3.fk_IsType.fk_HasClassSkill 3.SP_SCNameOnSSSocial
Skill 5.fk_IsType.fk_HasClassSkill 9.SP_SCNameOnSSPsychomotor
SkillNdFrTask
IDST_SCNameOnSnTST_SNameOnSnTST_TNameOnSnTfk_ForTaskfk_SkillNeeded
SkillNdFrTask 23CognitiveCategoryTheoryWriteCTPaperTask 10Skill 0
SkillNdFrTask 24PsychomotorPaperWritingWriteCTPaperTask 10Skill 9
SkillNdFrTask 25CognitiveProgrammingWriteAQLTutorialTask 11Skill 2
SkillNdFrTask 26SocialLecturingWriteAQLTutorialTask 11Skill 3
SkillNdFrTask 27PsychomotorPaperWritingWriteAQLTutorialTask 11Skill 9
SkillNdFrTask 28SocialFinanceRunFncIntProjectTask 12Skill 7
SkillNdFrTask 29SocialManagementRunFncIntProjectTask 12Skill 8
SkillNdFrTask 30CognitiveProgrammingBuildPhrmColimTask 13Skill 2
SkillNdFrTask 31SocialPharmaBuildPhrmColimTask 13Skill 6
SkillNdFrTask 32CognitiveCompSciLaunchCIStartupTask 14Skill 1
SkillNdFrTask 33CognitiveProgrammingLaunchCIStartupTask 14Skill 2
SkillNdFrTask 34PsychomotorGrantWritingLaunchCIStartupTask 14Skill 5
SkillNdFrTask 35PsychomotorPaperWritingLaunchCIStartupTask 14Skill 9
SkillNdFrTask 36CognitiveCompSciBuildAQLTask 15Skill 1
SkillNdFrTask 37CognitiveProgrammingBuildAQLTask 15Skill 2
SkillNdFrTask 38SocialManagementBuildAQLTask 15Skill 8
SkillNdFrTask 39SocialPharmaRunPhrmIntProjectTask 16Skill 6
SkillNdFrTask 40SocialManagementRunPhrmIntProjectTask 16Skill 8
SkillNdFrTask 41CognitiveCategoryTheoryTeachCTTask 17Skill 0
SkillNdFrTask 42SocialLecturingTeachCTTask 17Skill 3
SkillNdFrTask 43CognitiveCompSciTeachCSTask 18Skill 1
SkillNdFrTask 44SocialLecturingTeachCSTask 18Skill 3
SkillNdFrTask 45CognitiveProgrammingBuildFncColimTask 19Skill 2
SkillNdFrTask 46SocialFinanceBuildFncColimTask 19Skill 7
SkillPrvdByPerson
IDSP_PNameOnSnPSP_SNameOnSnPfk_SpP_Pfk_SpP_S
SpecificSkill
IDSP_GSNameOnSSSP_SCNameOnSSSP_SSNameST_SCNameOnSSST_SklNamefk_IsType
Skill 0Skill 0.SP_GSNameOnSSSkill 2.SP_SCNameOnSSSkill 0.SP_SSNameCognitiveCategoryTheorySkill 0.fk_IsType
Skill 1Skill 1.SP_GSNameOnSSSkill 2.SP_SCNameOnSSSkill 1.SP_SSNameCognitiveCompSciSkill 1.fk_IsType
Skill 2Skill 2.SP_GSNameOnSSSkill 2.SP_SCNameOnSSSkill 2.SP_SSNameCognitiveProgrammingSkill 2.fk_IsType
Skill 3Skill 3.SP_GSNameOnSSSkill 3.SP_SCNameOnSSSkill 3.SP_SSNameSocialLecturingSkill 3.fk_IsType
Skill 4Skill 4.SP_GSNameOnSSSkill 2.SP_SCNameOnSSSkill 4.SP_SSNameCognitiveDBASkill 4.fk_IsType
Skill 5Skill 5.SP_GSNameOnSSSkill 9.SP_SCNameOnSSSkill 5.SP_SSNamePsychomotorGrantWritingSkill 5.fk_IsType
Skill 6Skill 6.SP_GSNameOnSSSkill 3.SP_SCNameOnSSSkill 6.SP_SSNameSocialPharmaSkill 6.fk_IsType
Skill 7Skill 7.SP_GSNameOnSSSkill 3.SP_SCNameOnSSSkill 7.SP_SSNameSocialFinanceSkill 7.fk_IsType
Skill 8Skill 8.SP_GSNameOnSSSkill 3.SP_SCNameOnSSSkill 8.SP_SSNameSocialManagementSkill 8.fk_IsType
Skill 9Skill 9.SP_GSNameOnSSSkill 9.SP_SCNameOnSSSkill 9.SP_SSNamePsychomotorPaperWritingSkill 9.fk_IsType
Skill 76Skill 76.SP_GSNameOnSSSkill 9.SP_SCNameOnSSSkill 76.SP_SSNamePsychomotorSkill 76.ST_SklNameSkill 76.fk_IsType
Task
IDST_TName
Task 10WriteCTPaper
Task 11WriteAQLTutorial
Task 12RunFncIntProject
Task 13BuildPhrmColim
Task 14LaunchCIStartup
Task 15BuildAQL
Task 16RunPhrmIntProject
Task 17TeachCT
Task 18TeachCS
Task 19BuildFncColim

transform tCounit = counit mSTSpnToSTSpn_SPSpn_CoLim iSTSpn_SPSpn_Merge2

The counit transform injects iDltSgmColim back into the merged instance iSTSpn_SPSpn_Merge3.