Databases Reference
In-Depth Information
the values for the attributes of an expression, use a PL/SQL built-in package to add the
expression to the column, and use standard SQL to set the values for the expression. To
compare values to an expression, you use the EVALUATE operator in the WHERE clause
of your SQL statement.
Expressions can be used to define complex qualities for rows, since an expression can
have many attributes. You can also use expressions to implement many-to-many rela‐
tionships without an intermediary table by using expressions from two tables to join
the tables.
With the Enterprise Edition of Oracle, you can add an index to an expression, which
can provide the same performance benefits of an index to the values defined as an
expression.
Data Design
Tables and columns present a logical view of the data in a relational database. The
flexibility of a relational database gives you many options for grouping the individual
pieces of data, represented by the columns, into a set of tables. To use Oracle most
effectively, you should understand and follow some firmly established principles of
database design.
The topic of database design is vast and deep: we won't even pretend to offer more than
a cursory overview. However, there are many great topics and resources available on
the fundamentals of good database design. When E. F. Codd created the concept of a
relational database in the 1960s, he also began work on the concept of
normalized
data
design. The theory behind normalized data design is pretty straightforward: a table
should contain only the information that is directly related to the key value of the table.
The process of assembling these logical units of information is called
normalization
of
the database design.
Normalized Forms
In fact, there is more than one type of normalization. Each step in the normalization
process ends with a specific result called a
normalized form
. There are five standard
normalized forms, which are referred to as first normal form (1NF), second normal
form (2NF), and so on. The normalization process that we describe briefly in this section
results in third normal form (3NF), the most common type of normalization.
Explaining the complete concepts that lie behind the different normal forms is beyond
the scope of this chapter and topic.
The concept of normalized table design was tailored to the capabilities of the relational
database. Because you could join data from different tables together in a query, there
