Converting an ERD to a Physical Database

View from Laguna Beach in Orange County, Southern California

Articles Index

Course Listing

Free Catalog

Request Information

What's New

View related PowerPoint slides on Slideshare

Glossary of Database Terms

 

Page 1 | Page 2

-Microsoft Access Example-

About

The purpose of this article is to provide the student with an approach to converting an Entity Relationship Diagram (ERD) into a physical database. It is is a top down approach modeled after Teorey and Kroenke. It assumes the database implementor is going to be using the Microsoft Access GUI tool to generate the DDL "behind the scenes" and is familiar with this tool. If coded by hand many steps would be combined. In fact, the same is true with a GUI, but thinking of it as four steps can still be helpful.

This article does not treat some advanced topics such as relationships involving three or more entities. Rather, we will focus on "binary" relationships involving two entities which are those most commonly encountered in commercial practice.

An alternative bottom up approach modeled after Hernandez will be presented in a later article.

In a nutshell there are four steps:

  1. Covert the entities into tables. There are four "sub-steps" here depending on the relationship(s) the entities participate in:

    • one-to-one

    • one-to-many

    • many-to-many

    • supertype-subtype

  2. Apply business rules to tables created by setting field (or column) constraints appropriately

  3. Create references between tables (in Access called permanent links) and referential integrity, applying appropriate business rules.

  4. Create appropriate indexes. For the typical OLTP database we are considering here this would mean ensuring that primary and foreign keys are indexed.

  5. I am beginning to think there is another step that can be placed right here: create supporting views (in Access - saved queries). But we'll leave this off for now.

Step 1: Convert Entities

One-to-one relationships

Two entities participating in a one-to-one relationships are each converted to a table. Typically the table name is plural where it makes sense. For example: Employees, Parts, Suppliers, etc. Some people further like to prefix the table name with tbl or an equivalent convention. For example: tblEmployees, tblSuppliers.

The primary key of one of the tables is placed in the other table as a foreign key. Which table gets the foreign key depends on your application. If for example we had a field engineering application with car and engineer entities where at most one car was assigned to an engineer we might place the foreign key in the engineer table. We would do this because we would typically be working with the engineer table but might occasionally want to see information about his or her assigned car. Either will work.

One more thing: optionality. If the assignment is optional, then set the foreign key to allow nulls.

One-to-many relationships

This will probably be your most common case. Fortunately, it is also the simplest. Each of the two entities is converted to a table. The primary key of the table on the one side is placed in the table on the many side as a foreign key. Again, if the relationship is optional, set the foreign key to allow nulls. (In Access this would mean set the required property to no. We will discuss field properties in greater depth later.)

Many-to-many relationships

Your design may or may not have any many-to-many relationships. You may have "flattened" the design already by converting the many-to-many to one-to manies. We'll assume your diagram still has a many-to-many relationship.

First convert the two entities to tables. Then create a third table, often called an intersection or junction table (but there are many different terms used). If there is no obvious name, give it a name representative of the two other tables. Example: students are in a many-to-many relationship with courses. We could call the intersection table student_courses. In this case, though, we can probably use a "real" name such as enrollments.

Calendar of Public Database and Microsft Access Classes

Many-to-many relationships (Cont'd)

Place the primary keys of the two original tables into the intersection table as foreign keys. These two columns then often form a composite primary key for this table. They are not optional, so don't allow nulls. Create additional columns for the attributes of this relationship from your diagram. Using enrollments as an example the composite primary key might be studentid, courseid. Alternatively, we could create a separate surrogate ("auto-number") primary key. However, in this case you may need to add a unique constraint or index later to preserve the unique pairing implied by the original composite primary key. Additional columns might be semester and grade.

Supertype-Subtype Relationships

Convert the supertype entity to a table. For columns use the attributes that are common across all the subtypes. For example say in our field engineering example we had a document supertype. All documents have a date, author and hyperlink. Place these in the new table. Use the entity identifier as the primary key - say document number in this example. You may also want to add an indicator column that holds a code showing which subtype instance a given supertype instance is related to.

Now create a table for each of the subtype entities. Each table will have the same primary key as the supertype - in this case document number. The primary key also serves as a foreign key. Now create columns in each new table for those attributes that are unique to that subtype.

Step 2: Set Field Constraints

Once you have your table defined you will want to make sure you have the field properties set to enforce those business rules that can be enforced at the field level. Typically, these are things like requiring a customer name in a customers table, making sure a line item quantity sold is greater than 0, etc. Let's take the relevant Access field properties one-by-one. (Be aware you have to set these manually if you use an Access template or wizard.)

Default value: Would you like the field to default to a given value every time the user adds a new record? For example, automatically insert today's date for an order date field? Then place your default value here.

Validation Rule: Use this property to check an entry against a business rule such as making sure a quantity is > 0. This rule is applied by the database when the user leaves the field.

Validation Text: Place here the error message you wish the user to receive if the Validation Rule is violated.

Required: This is equivalent in SQL databases to allowing NULL's or not. It is a very important property. If the attribute is optional then Required should be set to No, if mandatory then set to Yes. Use this property to ensure that the user enters things like the customers last name, the date of an order, etc. Otherwise you run the risk of compromising the integrity of the database with incomplete records that cannot be used in searches, reports, etc.

This rule is applied by the database when the user causes the record to be saved.

Indexed: We will cover this in step 4.

Table constraints: The above constraints are all column constraints. They apply to only one column or field. To create a constraint that applies to more than one field in the table, say checking that the ship date is greater than or equal to the order date, use a table constraint. To do this right click on the design surface of the Table Designer and select Properties. There will be a Validation Rule and Validation Text property as above. The difference is that you can refer to multiple columns in the rule. These rules are applied when the user causes the record to be saved.

Steps 3 and 4:

The next two steps are to create the relationships and then set up the indexing.

Continue to Page 2.

 


Submit the form below to be notified of new articles or other resources made available...

Receive course announcements and news. Sign Up Today!





Email Marketing by VerticalResponse

Learn more about our Microsoft Access, SQL and Database Design courses..

View course catalog.


Please send comments and suggestions to WebMaster at dhdurso.org

Copyright 2017 D.H.D'Urso & Associates
Laguna Niguel, Orange County, CA

Articles IndexClose Window | Back | Top