Want a NOOK? Explore Now
Joe Celko has looked deep into the code of SQL programmers and found a troubling pattern - a frightening lack of consistency between their individual encoding schemes and those of the industries in which they operate. This translates into a series of incompatible databases, each one an island unto itself that is unable to share information with others in an age of internationalization and business interdependence. Such incompatibility severely hinders information flow and the quality of company data. This book reveals the shift programmers need to make to overcome this deadlock. By collecting and detailing the diverse standards of myriad industries, and then giving a declaration for the units that can be used in an SQL schema, Joe enables readers to write and implement portable data that can interface to any number of external application systems!
"In physical science the first essential step in the direction of learning any subject is to find principles of numerical reckoning and practicable methods for measuring some quality connected with it. I often say that when you can measure what you are speaking about, and express it in numbers, you know something about it; but when you cannot measure it, when you cannot express it in numbers, your knowledge is of a meager and unsatisfactory kind; it may be the beginning of knowledge, but you have scarcely in your thoughts advanced to the state of Science, whatever the matter may be."
PLA, Vol. 1, Electrical Units of Measurement, 1883-05-03
Before you can put data into a database, you actually need to think about how it will be represented and manipulated. Most programmers have never heard of measurement theory or thought about the best way to represent their data. They either use whatever was there before or invent their own schemes on the fly. Most of the time, the data is put into the database in the units in which it was collected without regard to even a quick validation. It is assumed the input is in an appropriate unit, with appropriate scale and precision. In short, application programmers and users are perfect. This tendency to believe the computer, no matter how absurd the data, is called the "Garbage In, Gospel out" principle in IT folklore.
This unwillingness to do validation and verification is probably the major reason for the lack of data quality.
1.1. MEASUREMENT THEORY
"Measure all that is measurable and attempt to make measurable that which is not yet so."
—Galileo (1564–1642)
Measurement theory is a branch of applied mathematics that is useful in data analysis and database design. Measurements are not the same as the attribute being measured. Measurement is not just assigning numbers to things or their attributes so much as it is finding a property in things that can be expressed in numbers or other computable symbols. This structure is the scale used to take the measurement; the numbers or symbols represent units of measure.
Strange as it might seem, measurement theory came from psychology, not mathematics, statistics, or computer science. S. S. Stevens originated the idea of levels of measurement and classification of scales in 1946 for psychology testing. This is more recent than you would have thought. Scales are classified into types by the properties they do or do not have. The properties with which we are concerned are the following.
1. There is a natural origin point on the scale. This is sometimes called a zero, but it does not literally have to be a numeric zero. For example, if the measurement is the distance between objects, the natural zero is zero meters—you cannot get any closer than that. If the measurement is the temperature of objects, the natural zero is absolute zero—nothing can get any colder. However, consider time; it goes from an eternal past into an eternal future, so you cannot find a natural origin for it.
2. Meaningful operations can be performed on the units. It makes sense to add weights together to get a new weight. Adding temperatures has to consider mass. Dates can be subtracted to give a duration in days. However, adding names or shoe sizes together is absurd.
3. There is a natural ordering to the units. It makes sense to speak about events occurring before or after one another in time or a physical object being heavier, longer, or hotter than another object.
But the alphabetical order imposed on a list of names is arbitrary, not natural—a foreign language, with different names for the same objects, would impose another alphabetical ordering. And that assumes the other language even had an alphabet for an ordering; Chinese, for example, does not.
4. There is a natural metric function on the units. A metric function has nothing to do with the "metric system" of measurements, which is more properly called SI, for "Systemé International d'units" in French. Metric functions have the following three properties:
a. The metric between an object and itself is the natural origin of the scale. We can write this in a notation as M(a, a) = 0.
b. The order of the objects in the metric function does not matter. Again in the semimathematical notation, M(a, b) = M(b, a).
c. There is a natural additive function that obeys the rule that M(a, b) M(b, c) >= M(a, c), which is also known as the triangular inequality.
This notation is meant to be more general than just arithmetic. The "zero" in the first property is the origin of the scale, not just a numeric zero. The third property, defined with a "plus" and a "greater than or equal" sign, is a symbolic way of expressing general ordering relationships. The "greater than or equal" sign refers to a natural ordering on the attribute being measured. The "plus" sign refers to a meaningful operation in regard to that ordering, not just arithmetic addition.
The special case of the third property, where the "greater than or equal to" is always "greater than," is very desirable to people because it means that they can use numbers for units and do simple arithmetic with the scales. This is called a strong metric property. For example, human perceptions of sound and light intensity follow a cube root law—that is, if you double the intensity of light, the perception of the intensity increases by only 20% (Stevens 1957). The actual formula is "Physical intensity to the 0.3 power equals perceived intensity" in English. Knowing this, designers of stereo equipment use controls that work on a logarithmic scale internally but that show evenly spaced marks on the control panel of the amplifier.
It is possible to have a scale that has any combination of the metric properties. For example, instead of measuring the distance between two places in meters, you can measure it in units of effort. This is the old Chinese system, which had uphill and downhill units of distance, so you could estimate the time required to make a journey on foot.
Does this system of distances have the property that M(a, a) = 0? Yes; it takes no effort to get to where you are already located. Does it have the property that M(a, b) = M(b, a)? No; it takes less effort to go downhill than to go uphill. Does it have the property that M(a, b) M(b, c) >= M (a, c)? Yes with the direction considered; the amount of effort needed to go directly to a place will always be less than the effort of making another stop along the way.
As you can see, these properties can be more intuitive than mathematical. Obviously, we like the more mathematical side of this model because it fits into a database, but you have to be aware of the intuitive side.
1.1.1. Range, Granularity, and Your Instruments
"The only man who behaves sensibly is my tailor; he takes my measurements anew every time he sees me, while all the rest go on with their old measurements and expect me to fit them."
—George Bernard Shaw
Range and granularity are properties of the way the measurements are made. Since we have to store data in a database within certain limits, they are very important to a database designer. The type of scales is unrelated to whether you use discrete or continuous variables. While measurements in a database are always discrete due to finite precision, attributes can be conceptually either discrete or continuous regardless of measurement level. Temperature is usually regarded as a continuous attribute, so temperature measurement to the nearest degree Celsius is a ratio-level measurement of a continuous attribute.
However, quantum mechanics holds that the universe is fundamentally discrete, so temperature may actually be a discrete attribute. In ordinal scales for continuous attributes, ties are impossible (or have probability zero). In ordinal scales for discrete attributes, ties are possible. Nominal scales usually apply to discrete attributes. Nominal scales for continuous attributes can be modeled but are rarely used.
Aside from these philosophical considerations, there is the practical aspect of the instrument used for the measurement. A radio telescope, surveyor's transit, meter stick, and a micrometer are tools that measure distance. Nobody would claim that they are interchangeable. I can use a measuring tape to fit furniture in my house but not to make a mechanical wristwatch or to measure the distance to the moon.
From a purely scientific viewpoint, measurements should be reduced to the least precise instrument's readings. This means that you can be certain that the final results of calculations can be justified.
From a practical viewpoint, measurements are often adjusted by statistical considerations. This means that final results of calculations will be closer to reality—assuming that the adjustments were valid. This is particularly true for missing data, which we will discuss later.
But for now consider the simple example of a database showing that Joe Celko bought 500 bananas this week. Unless I just started a gorilla ranch, this is absurd and probably ought to be adjusted to five bananas or less. On the other hand, if the Dairy Queen Company orders five bananas this week, this is absurd. They are a corporation that had about 6000 restaurants in the United States, Canada, and 20 foreign countries in 2007, all of which make a lot of banana splits every day.
1.1.2. Range
A scale also has other properties that are of interest to someone building a database. First, scales have a range—what are the highest and lowest values that can appear on the scale? It is possible to have a finite or an infinite limit on either the lower or the upper bound. Overflow and underflow errors are the result of range violations inside the database hardware.
Database designers do not have infinite storage, so we have to pick a subrange to use in the database when we have no upper or lower bound. For example, very few computer calendar routines will handle geological time periods. But then very few companies have bills that have been outstanding for that long, either, so we do not mind a range of less than 100 years. Oops! There are contracts and leases that are good for decades, centuries, or eternity. The range depends on the situation.
(Continues...)
Excerpted from Joe Celko's Data, Measurements and Standards in SQL by Joe Celko Copyright © 2010 by Elsevier Inc. . Excerpted by permission of MORGAN KAUFMANN. All rights reserved. No part of this excerpt may be reproduced or reprinted without permission in writing from the publisher.
Excerpts are provided by Dial-A-Book Inc. solely for the personal use of visitors to this web site.
0.0. Introduction
1.0. Scales and Measurement Theory
2.0. Data Encoding Schemes
2.1. Classification
2.2. Check Digits
3.0. Physical Measurements
3.1. Brief History of Measurement Standards
3.2. Rise of ISO and Other Organizations
3.3. ISO-2955 Standard
3.3.1. Basic Units
3.3.2. Compound Units
3.4. Color Systems
4.0. Temporal Measurements
4.1. The Calendar
4.1.1. A History of the Common Era Calendar
4.1.2. ISO-8601 Standard
4.1.3. Implementations in SQL
4.2. The Clock
4.2.1. Leap Seconds, UTC and Atomic Clocks
4.2.2. Time Zones in SQL
5.0. Industry Standards For Products
5.1. UPC and EAN Bar Codes
5.2. Standard Packaging Units
5.2.1. U.S. Systems
5.2.2. European Union and Other European Systems
5.2.3. Asian and Other Systems
5.3. Bank Standards
5.3.1. Account Numbers
5.3.2. Checking
5.3.2.1. U.S. Banking System
5.3.2.2. IBAN and Other International Standards
6.0. Postal Address Encoding
6.1. Formatting Names
6.2. ZIP Codes in the United States
6.3. Various Foreign Systems
6.4. Systems Used by UPS and other Delivery Services
7.0. Currency
7.1. GAAP Rules in the United States
7.2. Rules for Euro Conversions
8.0. Internet
8.1. Email Addresses
8.2. IP Addresses
9.0. Business Codes
9.1. The Rise and Fall of EDI and ANSI X12
9.2. XML Standards in Various Industries
9.3. Company Identifiers
9.3.1. DUNS
9.3.2. Stock Ticker Symbols
9.4. Chart of Accounts
10.0. Medical Systems
10.1. ICD Codes
10.2. HIPAA Codes
10.3. Drug Codes
11.0. General Classification Systems
11.1. Alphabetic Order
11.1.1. History
11.1.2. Problems with Foreign Alphabets
11.2. Dewey Decimal Classification
11.3. Library of Congress
Overview
Joe Celko has looked deep into the code of SQL programmers and found a troubling pattern - a frightening lack of consistency between their individual encoding schemes and those of the industries in which they operate. This translates into a series of incompatible databases, each one an island unto itself that is unable to share information with others in an age of internationalization and business interdependence. Such incompatibility severely hinders information flow and the quality of company data. This book ...