What’s Wrong With My Data? - High-Performance Web Databases: Design, Development, and Deployment - page 419

In Exhibit 27-8, our hypothetical data warehouse draws information
from both Files “A” and “F”:

• Although Data File “F” has as a key, unfortunately it is not

unique, as can be seen from the two 523 rows. While under some cir-
cumstances this would boil down to a primary key issue (possibly
combined with a missing, invalid, or redundant data issue), in this par-
ticular case it appears we should be consolidating the data in the two
rows.
• There is no entry in File “F” corresponding to record 526 from File “A.”
This is a clear violation of referential integrity, as Lee must (at the very
least) have a current-year salary recorded.
• Record 525 in File “F” has no corresponding entry in File “A,” so just
who is this a salary record for? This is an example of an “orphaned”
record (does not have a “parent” record).

Identifier

Exhibit 27-8. Example of referential integrity problems.

Data File “A”

Identifier Last Name Sex Age Smoker

523 Smith M 00 N

524 Jones F 23 Y

526 Lee M 42

527 Frank 17 Y

528 Yu M 00 N

Data File “F”

Current
Salary

Yr-1
Salary

Yr-2
Salary

Yr-3
Salary Identifier

523 0 0 55000 45000

523 75000 65000 0 0

524 63000 58000 53000 50000

525 53500 48800 45000 42300

527 51800 47700 43500 40200

528 45000 45000 38500 37500

Special Case: Dates

Dates can be tricky to work with for a number of reasons:

• Many possible representations, including:
— Multiple date formats (e.g., YYDDD, CCYYMMDD, DDMMYY)
— Multiple internal storage options, depending on language, OS, etc.
— Dates are often stored in pure numeric fields, or within text fields