In this chapter we explain the process of creating a relational database from an entity-relationship model. While many steps are largely mechanical, a number of decisions need to be made along the way. We will explore the trade-offs for each decision. We will use the computer manufacturer data model from Chapter 2.2 as our example.
This chapter assumes you are familiar with the basics of the relational model of the database, including tables and primary and foreign key constraints. The necessary foundations are covered in either Part I (Chapters 1.1 and 1.7 ) or Part III ( Chapter 3.1 ).
There are many ways to represent the relational database: logical or physical data models ( Chapter 2.4 ), text or tabular descriptions, or SQL code. Which you use will depend on your development process and needs. In this chapter, we will provide simple text descriptions in tabular format.
We start with the basic conversion rules, converting pieces of our example data model as we go. The full set of tables resulting from our conversion is given at the end of the chapter.
The first step in building a relational database from an ERD is creating a table from each entity in the data model. Weak entities need slightly different handling than regular entities, so we will address them separately, starting with regular entities.
First, decide on a name for the table - this does not have to be the same as the entity name! There are many naming schemes for tables. If you are building a database for a company or organization that has naming standards, you will of course want to follow those. Otherwise, choose a basic approach and be consistent. For example, some databases use plural nouns for tables, while others use singular nouns. In our data model from Chapter 2.2 , the entity employee might become a table named employee or employees. Another naming issue arises with table names containing multiple words; some databases choose to run these together, while others employ underscore characters. For example, the entity assembly line could become a table named assemblyline or assembly_line. In our examples below, we will use singular nouns and underscores.
Most attributes for the entity should be converted to columns in the new table. Do not create columns for derived attributes, as these values are not intended to be stored. Do not create columns for multivalued attributes; we will address these later. For composite attributes, create columns only for the component attributes, not the composite itself. As with entities, you will need to decide on a name for each new column, which does not have to be the same as the attribute name. You will also need to specify a type and any constraints for the column. Determining appropriate types for some columns may require consultation with your data domain experts. Constraints may be added as appropriate. In the descriptions below, we will use simple type and constraint descriptions, rather than SQL syntax.
Choose a key attribute (every regular entity should have at least one) and use the column created from it as the primary key for the new table. If the entity has multiple key attributes, you will need to decide which one makes most sense as a primary key. Simpler primary keys are usually preferred over more complex ones. If desired, you can constrain the columns resulting from other keys to be not null and unique similar to primary key columns. For example, an employee table might use a company generated ID number as its primary key, and also include a column for a government issued ID number which we would want to constrain to prevent duplicates.
Here is our ERD depiction of the employee entity:
Here is a preliminary conversion of the employee entity into a relational table named employee: