We’ve seen that relational databases are collections of tables and that we can use one or more tables in a query to build a table of results that answer an information need. We’ve also seen how the relational data model makes this possible. Each table has a primary key, one or more columns whose values are unique for each row in that table. Some tables also have foreign keys, one or more columns whose values “reference” another row somewhere based on a primary key. Since the primary key’s value is unique, the same value in a foreign key can be used to single out or look up a specific row in a table. In that way we can use foreign keys to match up corresponding rows, in effect gluing smaller rows together to make bigger rows.

We’ve also seen that schema diagrams indicate this structure to us. By leaving out the data and focusing on table names, column names, primary keys, and foreign keys, we can see which tables have relationships between their rows when one foreign key references a related primary key in a key pairing.

Then we took a detour to learn how to identify the records or entities in a problem. We know that records roughly correspond to tables in a relational database, so it should not be surprising that once we have identified the entities and their relationships, we should be able to convert those into a relational database schema.

With a couple of basic principles or rules to follow, we can write down a set of steps for transforming an ERD into a relational database schema. What we’ll see is that the relationship are the main factor determining what schema we end up with. The most important part of the relationship to pay attention to is it cardinality, so we consider the possibilities below and treat each cardinality in turn.

Many-to-many relationships

We’ve already seen that “Reserve” in Sailors Reserve Boats is a many to many relationship. We’ve also seen the schema diagram for the Sailors Reserves Boats database has 3 tables in it. The Sailors table matches up directly with the Sailors entity, and the Boats table and Boats entity correspond. It should be no surprise that the Reserves table corresponds to the relationship. A binary many-to-many relationship in ERD becomes 3 tables in a relational database schema. Sometimes a sailor reserves a boat, so a particular sailor row must be matched up with a particular boat row. The reserves table accomplishes this matchup by having a foreign key to each of the other two tables.

The figure below shows the correspondence between the ERD and the schema diagram. Each entity is a table as is the relationship. Each line between the entity and the relationship in the diagram indicates an entity may participate many times in the relationship. Each of these lines correspond to foreign keys in the schema diagram. The foreign keys are in the table corresponding to the relationship, and they reference tables corresponding to the entities.

A many-to-many relationship in an ERD becomes a table with 2 foreign keys in the relational database schema. Any attributes of the relationship are also columns in that table.

Based on the observations above, two rules for converting an ERD into a schema diagram can be written down. They’re written a little more generally than just for a binary many to many relationship so that we can use them later in other situations, too.

  1. Entities participating as a “many” in a relationship become a table when converting the ERD into a schema. If the entity has a key, it can be the primary key of the table. If it has no key, create one for the table. Any other attributes of the entity become columns of the table.
  2. Many-to-many relationships become tables. The table corresponding to the relationship will have a foreign key referencing the primary key of each participating entity. Any attributes of the relationship become attributes of the table.
In the company database, an employee works on project. An employee may work on many projects, and a project may be worked on by many employees. How many tables would the company database have from this part of the ERD?

Since works on is many-to-many, it will become a table as will the employee entity and the project on. works_on will have a foreign key referencing the primary key of the employee table and one referencing the primary key of the project table.

Many-to-one relationships

Many-to-one and one-to-many relationships are equivalent since we can always “reverse” an ERD by reading it backwards. Everything in this section for one kind will apply to the other. For the moment, imagine the Sailors Reserve Boats ERD reflected the fact that a sailor could reserve at most one boat. Note that a boat can still be reserved by more than one sailor. The relationship is many-to-one. This hypothetical ERD would appear as:

A binary many-to-1 realationship in an ERD showing “a sailor may reserve at most 1 boat”

To have a relational database that stored this same information, imagine that Dustin and Lubber both could reserve boat 101 while the two Horatios could reserve boat 102. Brutus could reserve boat 103. The relationship set looks like:

A many-to-one relationship set for Sailors reserving at most 1 Boat.

Unlike the many-to-many ERD which requires three tables in the database, this many-to-one ERD can be stored in only two tables. The Reserves table is unnecessary. We can add a foreign key to the Sailors table referencing Boats, and for each sailor (each row in the Sailors tables), we can put the id of the boat that Sailor can reserve.

A Sailors table with a foreign key referencing the primary key of the Boats table captures the information in the relationship set.

This approach works because of the “at most one”. We only have to store up to 1 boat id, so adding one column could do that. We can’t add a foreign key to the Boats table and have it still work. We don’t know the upper limit on how many Sailors may reserve that boat. It could be 2, in which case we’d need 2 different columns containing sailor ids. But it could be 5,000 sailors, in which case we’d need 5,000 different columns containing ids. There’s only one table where it makes sense to have the foreign key–the entity with the “at most one” participation in the relationship.

The many-to-one relationship and its equivalent schema. Both entities are converted into tables. The many-to-1 relationship becomes a foreign key in the entity that participates at most 1 time. Any columns in the relationship accompany that foreign key. A rule of thumb is that the arrow in the schema diagram will point towards the 1 in the ERD (from Sailor to Boat).

In general relationships with at most one “many” in them don’t have to become tables. A foreign key can be added in the at most one relationship. When we look at the correspondence between the ERD and schema, we can add a handy rule of thumb that the foreign key in the diagram points “towards the 1” in the ERD. We add it to the table corresponding to the entity on the far side of the diamond from the 1, and it references the table for the entity closest to that 1.

Now our process is:

  1. Entities participating as a “many” in a relationship become a table when converting the ERD into a schema. If the entity has a key, it can be the primary key of the table. If it has no key, create one for the table. Any other attributes of the entity become columns of the table.
  2. Many-to-many relationships become tables. The table corresponding to the relationship will have a foreign key referencing the primary key of each participating entity. Any attributes of the relationship become attributes of the table.
  3. A binary many-to-one or one-to-many relationship does not become a table. It becomes a foreign key in the entity that participates many times referencing the entity that participates on time. Any attributes the relationship has become columns in the table with the new foreign key.

One-to-one relationships

One-to-one relationships present no new challenges when converting an ERD to a schema. Foreign keys work because they allow one record to reference at most one other. In a one-to-one relationship, the foreign key can be associated with either entity’s tables. Just pick one or flip a coin to decide which entity’s table gets the foreign key attribute.

  1. Entities participating as a “many” in a relationship become a table when converting the ERD into a schema. If the entity has a key, it can be the primary key of the table. If it has no key, create one for the table. Any other attributes of the entity become columns of the table.
  2. Many-to-many relationships become tables. The table corresponding to the relationship will have a foreign key referencing the primary key of each participating entity. Any attributes of the relationship become attributes of the table.
  3. A binary many-to-one or one-to-many relationship does not become a table. It becomes a foreign key in the entity that participates many times referencing the entity that participates on time. Any attributes the relationship has become columns in the table with the new foreign key.
  4. For a one-to-one relationship, the table associated with either entity is a valid place to add a foreign key and columns arising from any attributes associated with the one-to-one relationship.

Weak Entities

The remaining feature of ERDs we haven’t dealt with are weak entities, entities which depend on another entity for part of their key. We saw that every weak entity participates exactly 1 time in the relationship with the strong entity that provides part of its key. The relationship between the weak entity and its supporting one will be either one-to-one or many-to-one. If it’s one to one, the weak entity may not have to become it’s own table because we can follow rule 4 above. If it’s many-to-one, we need to make the weak entity a table with a foreign key referencing the other entity.

Since every table in a database must have a primary key, we need to make sure any table created from a weak entity has its own primary key. We will have to add a column from the other entity to ensure that happens. The previous paragraph indicated that we would add a foreign key for many-to-one relationships. The only new twist when we do that for a table arising from a weak entity is that the added foreign key will also become part of the table’s primary key.

  1. Entities participating as a “many” in a relationship become a table when converting the ERD into a schema. If the entity has a key, it can be the primary key of the table. If it has no key, create one for the table. Any other attributes of the entity become columns of the table.
  2. Many-to-many relationships become tables. The table corresponding to the relationship will have a foreign key referencing the primary key of each participating entity. Any attributes of the relationship become attributes of the table.
  3. A binary many-to-one or one-to-many relationship does not become a table. It becomes a foreign key in the entity that participates many times referencing the entity that participates on time. Any attributes the relationship has become columns in the table with the new foreign key.
  4. For a one-to-one relationship, the table associated with either entity is a valid place to add a foreign key and columns arising from any attributes associated with the one-to-one relationship.
  5. A weak entity in a many-to-one relationship will become a new table in the schema. The new table must have a foreign key that is also part of its primary key referencing the table resulting from the entity that owns part of the weak entity’s key. Any attributes the relationship contains become columns along with the new foreign key in the weak entity’s table.

Summary

The overall rules are written in technical language to make them precise for all the different possible situations that may arise, but the “in a nutshell” version is that the cardinality of relationships determines the process

  1. Many-to-many relationships become tables with foreign keys to participating entities
  2. One-to relationships become foreign keys referencing the record/entity participating at most one time.
  3. Weak entities follow the same rules except the added foreign key will be part of the primary key, too
  4. Every attribute in an ERD becomes a column in a table. If the attribute belongs to a relationship, it becomes a column in the same table that has the relationship’s foreign key(s).