Database Reference
In-Depth Information
• The diamond represents a relationship between these two tables, and the “1:N”
within the diamond indicates that it is a one-to-many relationship.
• The vertical line next to the AGENTS table indicates that a client must be associ-
ated with an agent, and the circle next to the CLIENTS table indicates that an
agent doesn't necessarily have to be associated with a client.
Fields are also defined and associated with the appropriate tables during the data modeling
phase. Each table is assigned a
primary key,
various levels of data integrity are identified
and implemented, and relationships are established via
foreign keys.
Once the initial table
structures are complete and the relationships have been established according to the data
model, the database is ready to go through the Normalization phase.
Normalizationistheprocessofdecomposinglargetablesintosmalleronesinordertoelim-
inate redundant data and duplicate data, and to avoid problems with inserting, updating, or
deleting data. During the Normalization process, table structures are tested against
normal
forms
and then modified if any of the aforementioned problems are found. A normal form
is a specific set of rules that can be used to test a table structure to ensure that it is sound
and free of problems. There are a number of normal forms, and each one is used to test for
a particular set of problems. The normal forms currently in use are First Normal Form, Se-
cond Normal Form, Third Normal Form, Fourth Normal Form, Fifth Normal Form, Sixth
Normal Form, Boyce-Codd Normal Form, and Domain/Key Normal Form.
The Design Method Presented in This Topic
The design method that I use in this topic is one that I've developed over the years. It in-
corporates a requirements analysis and a simple ER diagramming method to diagram the
database structure. However, it
does not
incorporate the traditional Normalization process
or involve the use of normal forms. The reason is simple: Normal forms can be confusing
to anyone who has not taken the time to study formal relational database theory. For ex-
ample, examine the following definition of Third Normal Form:
A relvar is in 3NF if and only if it is in 2NF and every non-key
attribute is nontransitively dependent on the primary key.
1
This description is relatively meaningless to a reader who is unfamiliar with the terms
relvar, 3NF, 2NF, non-key attribute, transitively dependent,
and
primary key.
The process of designing a database is not and should not be hard to understand. As long
as the process is presented in a straightforward manner and each concept or technique is
clearly explained, anyone should be able to design a database properly. For example, the
followingdefinition isderivedfromthe
results
ofusingThirdNormalFormagainstatable
structure, and I believe most people will find it clear and easy to understand:
