Contents
|
Data
Architect
Professional database modeling |
Chapter 8
Concepts
About this chapter
This chapter is an introduction to some of the concepts used by Data Architect.
Entity Relationship Diagram (ERD)
An ERD is a relatively simple
diagram which shows the central elements of a database and the relationships
those elements have to each other. The most used database element is the
Entity and a Relationship. The Entity is typically represented in the diagram,
as some form of box while the Relationships are some form of line connecting
two Entities.
ERD's are extremely useful to quickly understand many aspects of
a database design. For example;
- the size of the database
in terms of the number of entities
- the complexity of the
database in terms of the number of attributes in each entity and the nature
of the relationships
- the general topic of
data intended to be stored by the table and column names and data types
- the elegance of the
database design by the logical grouping of data into entities
Logical
Model (LM)
The Logical Model is a type of
ERD which depicts the database structures without regard to the specific
database for which it will ultimately be implemented in. This allows for
database design in the very early stages of system development and provides a
way to 'flesh-out' the design without the encumbrances associated with implementation
details. The LM, once complete, is morphed into a Physical Model where it
can be optimized for a specific database.
|
figure 1
Logical Model
|
Physical
Model (PM)
The Physical Model is a type
of ERD which deals with a specific implementation of a database design.
For example; the PM may be for a MySQL or a DB2 database. The PM is database
specific because it often uses features, such as particular data types,
only found in the specific database. The database elements are usually referred
to by slightly different names in a PM as the following chart shows;
Logical Model
|
Physical Model
|
Description
|
Entity
|
Table
|
a logical grouping of related data
|
Relationship
|
Reference
|
shows the relationship between two entities
or tables
|
Attribute
|
Column
|
also known as a Field
|
Data Architect is ERD centric.
This means that the creation of an ERD is the first thing you will do in
Data Architect.
|
figure 2
Physical Model
|
Figure 2
shows a simple example
of a Physical Model containing two Tables and a Reference. The Tables use
table and column names which consider the target database. The column data-types
may also use types found only in the target database. The Reference shows
that Customers can have Orders and shows that we must use tbOrders.fkCustomer
and tbCustomers.pkCustomer to navigate between these two tables. The Parent
table is tbCustomers (because that is where the line arrow points) and the
Child table is tbOrders.
