Databases Reference
In-Depth Information
You need to ensure that data is accurate when it is entered and remains accu-
rate during updates and modifications. Two of the primary tools for ensuring
this have already been mentioned, primary keys and foreign keys. Another fac-
tor already mentioned is controlling nullability.
As fast as computers have become, their speeds are certainly not infinite and
the time it takes to find data stored on disks and bring it into primary memory
for processing is a crucial performance issue. Data storage, retrieval, and pro-
cessing speeds all matter. Regardless of how elegant an application and the data-
base structures it uses are, it must meet performance expectations to be a suc-
cess. Because of this, you may need to make changes to your table design that
go beyond converting entities and applying normalization rules.
5.2.1 Ensuring Data Integrity
Modern DBMSs are designed to help you ensure data integrity. Some of these
are considered to be part of the basic SQL features set while others are database
specific. To understand what you have available, however, you need a better
understanding of just what data integrity encompasses.
Data integrity requirements are typically broken down into key areas. These are:
▲
Domain integrity:
ensures that the values entered into specified columns
are legal.
▲
Entity integrity:
ensures that each row is uniquely identified.
▲
Referential integrity:
ensures that references with other tables remain
valid.
▲
Policy integrity:
ensures that values adhere to established business rules.
Domain, entity, and referential integrity issues are actually relatively straight-
forward. It's a little more difficult to make universal statements about policy
integrity. Policy integrity requirements are business and business rule specific, as
are actions taken to ensure them.
Addressing Domain Integrity
The first step in ensuring domain integrity is specifying an appropriate data type
for each table column. This limits the type of data that you can store in the col-
umn and to some extent sets the data storage format. Data type also relates to
data volume assessment because it determines how much space is required for
storing column data.
You also control domain integrity through:
▲
Nullability:
determines whether or not a column can contain null values.
You could use this, for example, to differentiate between a zero value and
a field that has never held a value.
