Databases Reference
In-Depth Information
was no need to keep all the information associated with a particular object together in
a single record. You could decompose the information into associated units and simply
join the appropriate units together when you needed information that crossed table
boundaries.
There are many different methodologies for normalizing data. The following is one
example:
1. Identify the objects your application needs to know (the
entities
). Examples of en‐
tities, as shown in
Figure 4-3
, include employees, locations, and jobs.
2. Identify the individual pieces of data, referred to by data modelers as
attributes
, for
these entities. In
Figure 4-3
, employee name and salary are attributes. Typically,
entities correspond to tables and attributes correspond to columns.
3. As a potential last step in the process, identify
relationships
between the entities
based on your business. These relationships are implemented in the database sche‐
ma through the use of a combination known as a foreign key. For example, the
primary key of the DEPARTMENT NUMBER table would be a foreign key column
in the EMPLOYEE NAME table used to identify the DEPARTMENT NUMBER for
the department in which an employee works. A foreign key is a type of constraint;
constraints are discussed later in this chapter.
Normalization provides benefits by avoiding storage of redundant data. Storing the
department in every employee record not only would waste space but also would lead
to a data maintenance issue. If the department name changed, you would have to update
every employee record, even though no employees had actually changed departments.
By normalizing the department data into a table and simply pointing to the appropriate
row from the employee rows, you avoid both duplication of data and this type of prob‐
lem.
Normalization also reduces the amount of data that any one row in a table contains.
The less data in a row, the less I/O is needed to retrieve it, which helps to avoid this
performance bottleneck. In addition, the smaller the size of a row, the more rows are
retrieved per data block, which increases the likelihood that more than one desired row
will be retrieved in a single I/O operation. And the smaller the row, the more rows will
be kept in Oracle's system buffers, which also increases the likelihood that a row will be
available in memory when it's needed, thereby avoiding the need for any disk I/O at all.
Finally, the process of normalization includes the creation of foreign key relationships
and other data constraints. These relationships build a level of data integrity directly
into your database design.
Figure 4-3
shows a simple list of attributes grouped into entities and linked by a foreign
key relationship.
