Relational Modeling – Basics

Relational Tables

The main construct in a relational schema is a relational table (also known as a relation). Like any other table, a relational table has rows and columns. Every relational table must comply with the following conditions:

1. Each column must be named. Within one relational table the name of each column must be different.
2. No two rows in a relational table can be identical.
3. All data values in each column must be from the same predefined domain.
4. In each row, every column should hold just one value from its predefined domain.

In a relational schema we only show the relational tables with their column names, without the data. Later, those tables are implemented in a functioning database and populated with data. To illustrate each of the conditions listed above, we will show an example of an already populated table.

Let’s look at an example of two similar tables, where one is a relational table (complying with the rules stated above) and the other one is not.

Note that the first table complies with the above listed conditions, and it is therefore a relational table:

1. Each column has a different name.
2. No two rows are identical.
3. All values in each column are from a predefined domain (all ids are digits, all movie names are phrases, all movie genres are single words, all movie lengths are minutes).
4. All values in each column are single values from a predefined domain.

Note that the second table violates the above listed conditions, and it is therefore NOT a relational table:

1. Two column names are identical.
2. Two rows are identical.
3. Not all values in each column are from a predefined domain. Movie lengths values are supposed to be listed in minutes, but that is not the case for the third row.
4. Not all values in each column are single values from a predefined domain. In the second row there are two values listed in the third column.

Primary Key

In a relational schema, each relational table has a Primary Key which is a column (or a set of columns) whose value is unique for each row. Most relational tables have a single-column primary key. In such cases the name of the primary key column is underlined in order to distinguish it from the other columns in the relation. For example, in the relational table MOVIES (introduced above) would be depicted in a relational schema as shown here:

Mapping an Entity from an ERD to a Relational Table in a Relational Schema

As mentioned, the recommended way to create a relational schema is to convert (map) an ER Diagram into a relational schema. This conversion is relatively straightforward and it simply requires following prescribed mapping rules. The rule for mapping an entity states that each entity becomes a relational table, and each attribute becomes a column on the mapped relational table. If an entity has one unique attribute (which is the most common scenario), this attribute becomes the primary key of the mapped relational table. An example of the mapping process is shown here, where entity MOVIE is mapped into a relational table:

If a database designer has an ER Diagram based on the requirements, they can follow the mapping rules and straightforwardly create a relational schema. In fact, ERD Plus has a conversion mechanism (based on the mapping rules) that can automatically create a relational schema from an ER Diagram.

If a database designer is tasked with creating a relational schema without having an ER Diagram first, they have to create a relational schema from scratch one table at a time (hopefully still following some set of requirements). ERD Plus provides a way to create relational schemas from scratch.

Here are a few more examples of entities mapped into relational tables. Entity CUSTOMER mapped into a relational table:

Entity REGION mapped into a relational table:

Foreign Key

A mechanism that is used to depict relationships in the relational database model is called a Foreign Key. We will illustrate this mechanism using several examples. In particular, we will show how foreign keys are used to implement 1:M, M:N, and 1:1 relationships.

Mapping 1:M relationships from an ERD to a Relational Schema

To illustrate the concept of a foreign key and the rules for mapping 1:M relationships, we show how the 1:M relationship LocatedIn and the entities involved in this relationship (CUSTOMER and LOCATION) are mapped into a relational schema:

As shown here, the foreign key is a column in a relational table that refers to a primary key column in another (referred) relational table. To demonstrate how the foreign key functions, we show here sample data for CUSTOMER and REGION relational tables:

Note how foreign key column RegionID in the relational table CUSTOMER indicates for each customer the region that they are located in: Sue and Lisa are located in the North region, while Joe and Bob are located in the South region.

The rule for mapping 1:M relationships states that the M side gets the foreign key from the 1 side. Therefore, in this example, CUSTOMER (on the M side of LocatedIn) got the foreign key RegionID from REGIONS (on the 1 side).

Let us now observe another example of mapping a 1:M relationship, this time using the relationship ProducedBy, as shown here:

Note that the relational table MOVIE, mapped from the M side of the 1:M relationship, has a foreign key, as prescribed by the rule for mapping 1:M relationships. In this example, in the ER Diagram, the participation of MOVIE in the Produces relationship is optional, i.e. each movie is produced by one studio or no studio. Because of that, the foreign key StudioID in the mapped MOVIE relational table is optional (optional columns are marked with the (O) marking) meaning that each row may or may not contain a value for that column. This is illustrated by sample data for STUDIO and MOVIE tables, where movie Misty Square is an independent film not produced by any studio:

While a foreign key column may or may not be optional (as we just illustrated with the above examples), a primary key column can never be optional. In each relational table, every row will have a value in a primary key column.

Mapping M:N relationships from an ERD to a Relational Schema

To illustrate the rules for mapping M:N relationships, we show how the M:N relationship Buys and the entities involved in this relationship (CUSTOMER and MOVIE) are mapped into a relational schema:

The rule for mapping M:N relationships states that in addition to relational tables representing both entities (in this case CUSTOMER and MOVIE), a new, so-called bridge table (in this case BUYS) is created to represent the M:N relationship itself. This additional relational table contains two foreign keys, each referring to the primary keys of the tables resulting from the entities that were involved in the M:N relationship (in this case CustID from CUSTOMER and MovieID from MOVIE). These two foreign keys form a so-called composite primary key. To illustrate the bridge table and composite primary key, we show here sample data for CUSTOMER, MOVIE and BUYS relational tables:

Note how the bridge table BUYS contains information about which customer bought which movies. The data in the BUYS relational table shows that Sue bought two movies (Big Sky and Strange Seas), Joe bought two movies (Big Sky and Misty Square), Bob bought one movie (Stay Invisible), and Lisa bought one movie (Stay Invisible). One movie (Checking Out) has been not bought yet, which is consistent with the optional participation in the ER Diagram indicating that a movie can be bought by between zero and many customers.

The primary key of the BUYS relational table is a composite primary key composed of two columns: CustID and MovieID. Note that neither one of these columns in the relational table BUYS is unique on their own. Values repeat in both CustID and MovieID column in this table. However, every row in the BUYS relational table has a unique combination of CustID and MovieID values, which will never repeat. For example, C1 &11 combination shown in the first row of the BUYS relational table will not repeat in any other row.

Composite primary keys are marked by each component being underlined. A common mistake by beginners is to interpret two or more columns being underlined in one relational table as multiple primary keys. That is never the case, as every relational table has one and only one primary key. So, if a relational table has more than one column underlined, that always indicates a primary key composed of multiple columns (and never multiple primary keys).

Finally, note that the foreign key columns forming a composite key of a bridge table can never be optional (regardless of whether the M:N relationship that is being mapped has optional cardinality on either or both sides) because those columns form a composite primary key, and (as we stated before) no primary key column can be optional.

If a M:N relationship contains attributes, those attributes are mapped into the resulting bridge table. To illustrate we show how the M:N relationship Buys with attributes and the entities involved in this relationship (CUSTOMER and MOVIE) are mapped into a relational schema:

To further illustrate a bridge table with additional attributes, we show here sample data for CUSTOMER, MOVIE and BUYS relational tables:

For every instance of a customer buying a movie, we can now see the date and the dollar amount. For example, Sue bought the movie Big Sky on Jan, 1st 2026 and paid $9.99.

Mapping 1:1 relationships from an ERD to a Relational Schema

To illustrate the rules for mapping 1:1 relationships, we show how the 1:1 relationship DesignatesAsAmbassador and the entities involved in this relationship (CUSTOMER and REGION) are mapped into a relational schema:

The rule for mapping 1:1 relationships states that one of the tables gets the foreign key from the other one. A database designer can choose which table gets the foreign key from the other table. In this case, the database designer chose that the relational table REGION gets the foreign key from the relational table CUSTOMER. The designer could have chosen that the relational table CUSTOMER gets the foreign key from the relational table REGION, but in that case the foreign key would have been optional, as customers may or may not be designated as ambassadors to a region. In the chosen option where a foreign key is in the relational table REGION, this foreign key is not optional as every region must have a designated customer ambassador.

Note that the database designer chose to rename the foreign key in the relational table REGION from CustID to AmbassadorCustID to better explain the role of that particular foreign key in that table. Renaming a foreign key is a legal option, used occasionally when keeping the same name for the foreign key and the primary key that it refers to may not fully explain the role of the foreign key.

To demonstrate how the foreign key functions in the 1:1 mapping, we show here sample data for the CUSTOMER and REGION relational tables:

For every region we can see who their designated customer ambassador is. North region’s ambassador is Sue and South region’s ambassador is Lisa.

Relational Schemas

To summarize all the introduced concepts, we present the entire relational schema for the TMSC Movie Streaming Company mapped from the TMSC Movie Streaming Company ER Diagram:

To demonstrate how a relational schema enables the data in all relational tables of a database to be connected, we show here sample data for the TMSC Movie Streaming Company database:

Because all tables are connected with the system of foreign keys, all kinds of queries can be posed to this database. For example, a query can find out all the regions that have customers who bought movies produced by the studio Nebula Works. Here is the description of how this query would work:

1. look up the primary key value for Nebula Works in the relational table STUDIO: S2
2. look up in the MOVIE relational table which movies are produced by studio whose primary key is S2: 22 and 33
3. look up in the BUYS relational table which customers bought movies 22 and 33: C1, C3, and C4
4. look up in the CUSTOMER relational table in which regions are customers C1, C3, and C4 located: R1 and R2
5. look up in the REGIONS relational table the names of regions R1 and R2: North and South

Therefore, the answer to the question “What are the regions that have the customers who bought movies produced by the Nebula Works studio?” is “Regions North and South”.

The language for specifying such queries is SQL. ERD Plus allows users to learn and practice SQL skills on a preloaded set of relational databases.

This was a brief overview of Relational Modeling. To learn more about additional Relational Modeling concepts (such as implementing multivalued attributes as additional relational database table, using recursive foreign keys, etc.) read Chapter 3 of the following database textbook:

https://www.prospectpressvt.com/textbooks/jukic-database-systems-introduction-to-databases-and-data-warehouses-3-0

or find other sources of information about these topics.