3.1. Implementing Two-entity Shapes in relational databases

Let’s examine how the relationships shown on conceptual models can actually be implemented using relational database tables.

Suppose there was more that we wanted remember about each town besides its name. For example, the town of Bemidji, MN, USA has a nickname and motto, as seen on its wikipedia page. This would cause us to make this evolution of our data model:

Creature plus town LDS conceptual model

Now we would create a table in the relational database for the Town entity’s instances, with columns for the extra attributes. We would also change the Creature entity so that the column that once had the town name is now replaced by the TownId column from the new Town table.

3.2. Create the new Town Table

Note that when you run this, nothing appears to happen, since all you are doing is making the table.

3.3. Populate Town table with data instances

Here are some example sample instances of town.

3.4. Re-create a new Creature table

Note that when you run this, nothing appears to happen, since all you are doing is making the table.

3.4.1. Relating instances through data

Note in the above creation of the creature table we now have a column called townId, and have said that this references the townId column in the town table. Now look at the new insert commands below for instances of creature data. Notice how the creature named Bannon is declared to have townId ‘p’, which we know from the town data table instances indicates a town named ‘Philadelphia’.

Important

In database terms, townId is a foreign key from creature into town. Notice that from the LDS data model shown above, foreign key mappings like this are made on the relationship for the one-end opposite of the original entity called Creature. We are adding a new column to Creature that did not appear as an attribute on the original LDS.

3.5. Populate Creature table with data instances

Gotcha!

Notice that in most databases we can add a creature with a value for a townId that does not yet exist, as in the following example (an instance of town with townId ‘z’ has not yet been entered into the town table).

3.6. Peek ahead: natural join

We will explain the following query in more detail later. For now simply note that we can get creature and town data back together again. This is the whole purpose of relational databases. We should never be afraid of creating a new entity from what was once a single attribute at some point during our conceptual modeling process.

You have attempted of activities on this page