|
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.
|
