13.10. Same Base A and B, 1 - 1 match over cols Aid(E),Bid(E)

It is important to realize when relations have the same base. It is because they have the same identifying columns. In many sophisticated databases, it will take a step of using a reduce to create a relation that will have the same base as another relation and can then be Match Joined. For example, let’s consider this larger portion of the schema for the database we have been working with:


Large portion of LDS including Achievement and Aspiration

Though there are several chicken-feet-out shapes involving Town, what we want to focus on is the two parallel chicken-feet-in shapes whose intersection entities are Achievement and Aspiration, respectively, between Creature and Skill. This set of two different chicken-feet-in shapes involving entities like Creature and Skill is relatively common in databases.

In this database, there can be more than one of the same Skill achieved by a creature, i.e. there can be more than one pair of the same creatureId, skillCode data values in Achievement. (A creature can achieve the same skill more than once.) However, the data for Aspiration contains one creatureId, skillCode pair of data values, because those two columns form its identifier.

We might want to ask an English query like this:

What creatures have achieved and aspired to the same Skill?

One way to interpret this query is to decide that we simply want a single instance of a creature having achieved a skill (creatureId, skillCode) with the creatureId, skillCode of the Aspiration.

To perform this match of Aspiration data with Achievement data we must be careful to make certain that we have a 1 - 1 correspondence between columns we want to match over in the input relations. So with this interpretation, we would perform a Reduce first on Achievement, so that the precedence chart looks like this:


Precedence chart for Aspiring and Achieving Creature-Skill Pair

After the Reduce, the Match Join is a same base (Creature-Skill Pair), 1 - 1 case and therefore Symmetric-either.

The SQL for this chart then becomes:

13.10.1. A Different Interpretation

Another way to consider the posed query is to maintain the M - 1 correspondence between Achievement as A and Aspiration as B by not first doing the Reduce shown above. In this case we can state that what we want to get back is every Achievement matched to Aspiration through creatureId and skillCode.

If you would like, try the chart for this different case, now that you have answered these questions.

You have attempted of activities on this page