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:
-
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
-
Apply business rules to tables created by setting field
(or column) constraints appropriately
-
Create references between tables (in Access called permanent
links) and
referential integrity, applying appropriate
business rules.
-
Create appropriate indexes. For the typical OLTP database we
are considering here this would mean ensuring that primary
and foreign keys are indexed.
-
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.
|
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.
|