Learning MySQL

Using the Entity Relationship Model

In this section, we’ll look at the steps required to manually translate an ER model into database tables. We’ll then perform these steps using the music database as an example. In Using Tools for Database Design,” we’ll see how we can automate this process with the MySQL Workbench tool.

Mapping Entities and Relationships to Database Tables

When converting an ER model to a database schema, we work through each entity and then through each relationship according to the following rules to end up with a set of database tables.

Map the entities to database tables

For each strong entity, create a table comprising its attributes and designate the primary key. The parts of any composite attributes are also included here.

For each weak entity, create a table comprising its attributes and including the primary key of its owning entity. The primary key of the owning entity is known as a foreign key here, because it’s a key not of this table, but of another table. The primary key of the table for the weak entity is the combination of the foreign key and the partial key of the weak entity. If the relationship with the owning entity has any attributes, add them to this table.

For each multivalued attribute of an entity, create a table comprising the entity’s primary key and the attribute.

Map the relationships to database tables

For each one-to-one relationship between two entities, include the primary key of one entity as a foreign key in the table belonging to the other. If one entity participates totally in the relationship, place the foreign key in its table. If both participate totally in the relationship, consider merging them into a single table.

For each nonidentifying one-to-many relationship between two entities, include the primary key of the entity on the “1” side as a foreign key in the table for the entity on the “N” side. Add any attributes of the relationship in the table alongside the foreign key. Note that identifying one-to-many relationships (between a weak entity and its owning entity) are captured as part of the entity-mapping stage.

For each many-to-many relationship between two entities, create a new table containing the primary key of each entity as the primary key, and add any attributes of the relationship. This step helps to identify intermediate entities.

For each relationship involving more than two entities, create a table with the primary keys of all the participating entities, and add any attributes of the relationship.

Converting the Music Database ER Model to a Database Schema

Following the mapping rules as just described, we first map entities to database tables:

• For the strong entity Artist, we create the table artist comprising the attributes artist_id and artist_name, and designate artist_id as the primary key.

• For the weak entity Album, we create the table album comprising the attributes album_id and album_name, and include the primary key artist_id of the owning Artist entity as a foreign key. The primary key of the album table is the combination {artist_id, album_id}.

• For the weak entity Track, we create the table track comprising the attributes track_id, track_name, and time, and include the primary key {artist_id, album_id} of the owning Album entity as a foreign key. The primary key of the track table is the combination {artist_id, album_id, track_id}.

• For the weak entity Played, we create the table played comprising the attribute played, and include the primary key {artist_id, album_id, track_id} of the owning Track entity as a foreign key. The primary key of the played table is the combination {artist_id, album_id, track_id, played}.

• There are no multivalued attributes in our design, nor are there any nonweak relationships between our entities, so our mapping is complete here.

You don’t have to use consistent names across all tables; for example, you could have a column musician in the album table that contains the artist ID that you call artist_id in the artist table. Obviously, it’s much better to use a consistent naming convention to avoid confusion. Some designers put fk_ in front of columns that contain foreign keys; for example, in the album table, we could store the artist ID in the fk_artist_id column. We don’t use this convention in this book.