Part 1 covers the need, principles, and variety of measurement standards and Part 2 provides examples of actual standards across many industries such as language codes and phone numbers. This book is extremely informative and easy-to-read (I like Joe's writing style), and I found myself laughing out loud at several points in the book most notably around beer and Mad Magazine's metric system. As a data analyst and modeler, one of my tasks is to work with the business to understand and document existing domains as well as new and improved enterprise/industry domains. I will use this book as a reference in this endeavor.
Author(s): Joe Celko
Series: The Morgan Kaufmann series in data management systems
Publisher: Morgan Kaufmann Publishers/Elsevier
Year: 2010
Language: English
Pages: 282
City: Burlington, MA
Cover Page......Page 1
Copyright Page......Page 2
Introduction......Page 3
Common Units Evolve to Standards......Page 4
What Does this Have to do with Databases?......Page 6
History, Standards, and Designing Data......Page 9
Scales and Measurements......Page 11
Measurement theory......Page 12
Range, Granularity, and Your Instruments......Page 14
Granularity, Accuracy, and Precision......Page 16
Defining a measurement......Page 18
Tolerance......Page 19
Validation......Page 20
Erroneous Values......Page 21
Degree of Trust versus Risk of Error......Page 22
Validation......Page 24
Look-up tables......Page 25
Auxiliary Tables for Noncomputed Data......Page 27
Check digits......Page 28
Check Digit Algorithms......Page 29
Weighted-Sum Algorithms......Page 30
Power-Sum Check Digits......Page 32
Luhn Algorithm......Page 33
Dihedral Five Check Digit......Page 34
Declarations, not functions, not procedures......Page 37
Patterns and regular expressions......Page 41
Tricks with Patterns......Page 42
LIKE Is Not Equality......Page 44
Avoiding the LIKE Predicate with a Join......Page 45
CASE Expressions and LIKE Predicates......Page 47
Similar to Predicates......Page 48
String Character Content......Page 49
Searching versus Declaring a String......Page 50
Creating an Index on a String......Page 51
Nondatabase validation......Page 52
Data Encoding Schemes......Page 54
Bad encoding schemes......Page 55
Enumeration Encoding......Page 58
Measurement Encoding......Page 59
Algorithmic Encoding......Page 60
Hierarchical Encoding Schemes......Page 62
Vector Encoding......Page 63
Concatenation Encoding......Page 64
Atomic versus scalar......Page 65
State Transitions......Page 66
State Transition DDL......Page 67
State Transition Tables......Page 68
Use Existing Encoding Standards......Page 70
Use Explicit Missing Values to Avoid NULLs......Page 71
Translate Codes for the End User......Page 72
One True Look-up Table......Page 73
Keep the codes in the database......Page 76
Multiple character sets......Page 77
Scales......Page 78
BIT flags are not scales......Page 79
BITs......Page 80
Coupling:......Page 81
BIT Vectors......Page 82
Replacing BITs......Page 83
Types of scales......Page 85
Categorical Scales......Page 86
Absolute Scales......Page 88
Ordinal Scales......Page 89
Rank Scales......Page 90
Interval Scales......Page 91
Using scales......Page 92
Scale conversion......Page 93
Derived units......Page 95
Punctuation and standard units......Page 97
General guidelines for using scales in a database......Page 99
Get it right......Page 102
Replace bad values with a statistical dummy value......Page 103
Replace bad values to complete a statistical distribution......Page 104
Replace bad values with statistical profiling......Page 105
A Key cannot be NULL......Page 106
Invariant or universally controlled values......Page 107
Surrogate and physical locators keys......Page 108
Physical Locators for Performance......Page 109
Physical Locators for Lack of a Proper Key......Page 110
Trusted Sources......Page 111
A Sampling of Standards......Page 0
ISO-8601 standard......Page 114
Year Field......Page 115
Week Field......Page 116
Day within Year......Page 117
Second Field......Page 118
Time Zones and Daylight Saving Time......Page 119
Durations and intervals......Page 120
Time Intervals......Page 121
Sex codes......Page 122
Other sources......Page 124
Race versus ethnicity......Page 125
U.K. ethnic groups......Page 126
ISO 3166-2......Page 128
ISO 3166-3......Page 129
Language Codes......Page 130
Currency Codes......Page 132
Noncurrency units......Page 133
National Identification Numbers......Page 134
SSN Area Numbers......Page 135
SSN Group Numbers......Page 139
SSN Validation......Page 140
SIN First digit......Page 141
Personnummer Format......Page 142
EU Biometric Passports......Page 143
Fingerprint Classifi cation Systems......Page 144
Occupations......Page 146
National occupational classification (NOC)......Page 147
Occupational Structure by Skill Type......Page 148
Colors......Page 149
International color consortium......Page 150
The international telephone number components......Page 151
Subscriber local exchange or prefix......Page 153
E-Mail Addresses......Page 155
Universal Postal Union......Page 157
ZIP + 4......Page 158
Postal Zone Charts......Page 159
Canadian postal codes......Page 160
Postcode Formats......Page 162
Greater London Postcodes......Page 163
Case expression for many international postal codes......Page 164
Hierarchical Triangular Mesh......Page 167
Shoe Sizes......Page 170
Mondopoint System......Page 171
International Clothing Sizes......Page 172
Local versions......Page 176
Mental and behavioral disorders......Page 177
VIN format......Page 178
Freight Container Codes......Page 182
Related ISO standards......Page 183
Card issuers......Page 185
Other numbers......Page 186
PCI DSS and related standards......Page 187
Tools and more information......Page 188
SWIFT and Related Banking Standards......Page 189
BIC codes......Page 190
International bank account number (IBAN)......Page 191
IBAN Check Digits......Page 192
Data Universal Numbering System......Page 194
Global Trade Item Number......Page 195
Prefixes for UPC......Page 196
ISBN......Page 197
DOI syntax......Page 198
ISBN-A......Page 200
Audiovisual Media......Page 201
Version......Page 202
Cataloging AV materials......Page 203
ISIN and Related Securities Identifiers......Page 204
CUSIP......Page 205
Classification of financial instruments......Page 206
Temperature Scales......Page 207
Celsius scale......Page 208
Kelvin scale......Page 209
Delisle Scale......Page 210
Rømer Scale......Page 211
Premises identification number (PIN)......Page 212
Animal identification number (AIN and GIN)......Page 213
Animal tracking and other considerations......Page 216
ISO 216 Paper Sizes (“A,” “B,” and “C” Series)......Page 218
“B” series......Page 219
Non-ISO paper sizes......Page 220
Traditional compass points......Page 221
Other compass point systems......Page 222
Unicode......Page 224
Practical and political problems......Page 225
Normalization......Page 226
Driver’s Licenses......Page 228
U.S. driver’s licenses......Page 229
Enhanced driver’s license (EDL)......Page 230
Currency Units and Near Money......Page 232
Decimalization in the united kingdom......Page 233
Physical currency choices......Page 234
Coupons......Page 236
Types of Coupons......Page 237
Trade Groups and References......Page 238
Weight versus volume......Page 240
Scaling a recipe......Page 241
ISO 32000......Page 242
Things that paper cannot do......Page 244
Notes on calendar standards......Page 245
The Julian and Gregorian Calendars......Page 246
Computerizing Calendars......Page 248
Leap Seconds......Page 249
Calendar Tables......Page 250
SQL temporal data types......Page 253
Date Format Standards......Page 254
Handling Timestamps......Page 255
Handling Times......Page 257
Expressions with date arithmetic......Page 259
The nature of temporal data models......Page 260
Additive Congruential Generators......Page 262
Traditional and Metric Typographic Units......Page 266
DIN 16507-2 font sizes......Page 268
C......Page 272
D......Page 273
E......Page 274
I......Page 275
N......Page 277
P......Page 278
S......Page 279
T......Page 280
V......Page 281
Z......Page 282
