Joe Celko's Data, Measurements and Standards in SQL (eBook)

Front Cover 1
Joe Celko's Data, Measurements and Standards in SQL 4
Copyright Page 5
Contents 6
Introduction 14
PART 1 HISTORY, STANDARDS, AND DESIGNING DATA 20
CHAPTER 1 Scales and Measurements 22
1.1. Measurement Theory 23
1.1.1. Range, Granularity and Your Instruments 25
1.1.2. Range 27
1.1.3. Granularity, Accuracy and Precision 27
1.2. Defining a Measurement 29
1.3. Tolerance 30
1.3.1. Scale Conversion Errors 31
1.4. Validation 31
1.5. Verification 32
1.5.1. Erroneous Values 32
1.5.2. Phony Values 33
1.5.3. Degree of Trust Versus Risk of Error 33
CHAPTER 2 Validation 36
2.1. Look-Up Tables 37
2.1.1. Auxiliary Tables for Noncomputed Data 39
2.2. Check Digits 40
2.2.1. Error Detection Versus Error Correction 41
2.2.2. Check Digit Algorithms 41
2.3. Declarations, not Functions, not Procedures 49
2.4. Patterns and Regular Expressions 53
2.4.1. Tricks with Patterns 54
2.4.2. Results with NULL Values and Empty Strings 56
2.4.3. Like is not Equality 56
2.4.4. Avoiding the Like Predicate with a Join 57
2.4.5. CASE Expressions and Like Predicates 59
2.4.6. Similar to Predicates 60
2.4.7. Tricks With Strings 61
2.4.8. Regular Expression Web Sites 64
2.5. Nondatabase Validation 64
CHAPTER 3 Data Encoding Schemes 66
3.1. Bad Encoding Schemes 67
3.2. Encoding Scheme Types 70
3.2.1. Enumeration Encoding 70
3.2.2. Measurement Encoding 71
3.2.3. Abbreviation Encoding 72
3.2.4. Algorithmic Encoding 72
3.2.5. Hierarchical Encoding Schemes 74
3.2.6. Vector Encoding 75
3.2.7. Concatenation Encoding 76
3.3. Atomic Versus Scalar 77
3.4. Transition States 78
3.4.1. State Transitions 78
3.4.2. State Transition DDL 79
3.4.3. State Transition Tables 80
3.4.4. Automatic State Transition Tables 82
3.5. General Guidelines for Designing Encoding Schemes 82
3.5.1. Use Existing Encoding Standards 82
3.5.2. Allow for Expansion 83
3.5.3. Use Explicit Missing Values to Avoid NULLs 83
3.5.4. Translate Codes for the End User 84
3.5.5. One True Look-up Table 85
3.6. Keep the Codes in the Database 88
3.7. Multiple Character Sets 89
CHAPTER 4 Scales 90
4.1. Bit Flags are not Scales 91
4.1.1. BITs 92
4.1.2. BITs and Booleans 93
4.1.3. BIT Vectors 94
4.1.4. Replacing BITs 95
4.2. Dimensionless Measurements 97
4.3. Types of Scales 97
4.3.1. Nominal Scales 98
4.3.2. Categorical Scales 98
4.3.3. Absolute Scales 100
4.3.4. Ordinal Scales 101
4.3.5. Rank Scales 102
4.3.6. Interval Scales 103
4.3.7. Ratio Scales 104
4.4. Using Scales 104
4.5. Scale Conversion 105
4.6. Derived Units 107
4.7. Punctuation and Standard Units 109
4.8. General Guidelines for Using Scales in a Database 111
CHAPTER 5 Data with Ignorance 114
5.1. Get it Right 114
5.2. Replace Bad Values with a General Dummy Value 115
5.3. Replace Bad Values with a Statistical Dummy Value 115
5.4. Replace Bad Values to Complete a Statistical Distribution 116
5.5. Replace Bad Values with Statistical Profiling 117
CHAPTER 6 Keys 118
6.1. Uniqueness 118
6.2. A Key Cannot Be NULL 118
6.3. Invariant Or Universally Controlled Values 119
6.4. Surrogate And Physical Locators Keys 120
6.4.1. Physical Locators for Performance 121
6.4.2. Physical Locators for Lack of a Proper Key 122
6.4.3. Trusted Sources 123
PART 2 A SAMPLING OF STANDARDS 124
CHAPTER 7 Dates 126
7.1. ISO-8601 standard 126
7.1.1. Year Field 127
7.1.2. Month Field 128
7.1.3. Week Field 128
7.1.4. Day Field 129
7.1.5. Time Field 130
7.1.6. Time Zones and Daylight Saving Time 131
7.2. Putting it all Together 132
7.3. Durations and Intervals 132
7.3.1. Time Intervals 133
CHAPTER 8 Sex Codes 134
8.1. Sex Codes 134
8.2. Other Sources 136
CHAPTER 9 Ethnicity and Race Codes 138
9.1. Race Versus Ethnicity 138
9.2. U.K. Ethnic Groups 139
CHAPTER 10 ISO-3166 and Other Country Codes 142
10.1. ISO 3166-1 142
10.2. ISO 3166-2 142
10.3. ISO 3166-3 143
CHAPTER 11 Language Codes 144
CHAPTER 12 Currency Codes 146
12.1. Noncurrency Units 147
CHAPTER 13 National Identification Numbers 148
13.1. Social Security Numbers 149
13.1.1. SSN Area Numbers 149
13.1.2. SSN Group Numbers 153
13.1.3. SSN Serial Numbers 154
13.1.4. SSN Validation 154
13.2. Social Insurance Number 155
13.2.1. SIN First Digit 155
13.2.2. SIN Validation 156
13.3. Swedish Personal Identity Number 156
13.3.1. Personnummer Format 156
13.4. Eu Biometric Passports 157
13.4.1. Fingerprint Classification Systems 158
CHAPTER 14 Occupations 160
14.1. National Occupational Classification (NOC) 161
CHAPTER 15 Colors 164
15.1. International Color Consortium 165
CHAPTER 16 Telephone Numbers 166
16.1. The International Telephone Number Components 166
16.2. Subscriber Local Exchange or Prefix 168
CHAPTER 17 E-Mail Addresses 170
CHAPTER 18 Universal Postal Union 172
18.1. ZIP Code 173
18.1.1. Basic ZIP Code 173
18.1.2. ZIP + 4 173
18.1.3. Postal Zone Charts 174
18.1.4. ZIP Code Validation 175
18.2. Canadian Postal Codes 175
18.3. Postcodes in the United Kingdom 177
18.3.1. Postcode Formats 177
18.3.2. Greater London Postcodes 178
18.4. Case Expression for Many International Postal Codes 179
CHAPTER 19 Hierarchical Triangular Mesh 182
CHAPTER 20 Shoe Sizes 186
CHAPTER 21 International Clothing Sizes 188
CHAPTER 22 ICD Codes 192
22.1. Local Versions 192
22.2. Mental and Behavioral Disorders 193
CHAPTER 23 Vehicle Identification Number (VIN) 194
23.1. Vin Format 194
CHAPTER 24 Freight Containers 198
24.1. Freight Container Codes 198
24.1.1. Map Letters to Numeric Values 199
24.1.2. Assign Weights to Each Position 199
24.1.3. Compute the Check Digit from the Weighted Sum 199
24.2. Size and Type Codes 199
24.3. Related ISO Standards 199
CHAPTER 25 Credit Card Numbers 202
25.1. Card Issuers 202
25.2. Account Number 203
25.3. Other Numbers 203
25.4. Personal Identification Numbers 204
25.5. PCI DSS and Related Standards 204
25.6. Tools and More Information 205
CHAPTER 26 SWIFT And Related Banking Standards 206
26.1. BIC Codes 207
26.2. International Bank Account Number (IBAN) 208
26.2.1. IBAN Check Digits 209
CHAPTER 27 Data Universal Numbering System 212
CHAPTER 28 Global Trade Item Number 214
28.1. GTIN Family 215
28.1.1. Prefixes for UPC 215
28.2. ISBN 216
CHAPTER 29 Digital Object Identifier (DOI) 218
29.1. DOI Syntax 218
29.2. ISBN-A 220
CHAPTER 30 Audiovisual Media 222
30.1. Format 223
30.1.1. Root 223
30.1.2. Episode or Part 223
30.1.3. Version 223
30.2. Cataloging AV Materials 224
CHAPTER 31 Isin And Related Securities Identifiers 226
31.1. Cusip 227
31.2. Sedol and Other Countries 228
31.3. Classification of Financial Instruments 228
CHAPTER 32 Temperature Scales 230
32.1. Celsius Scale 231
32.2. Fahrenheit Scale 232
32.3. Kelvin Scale 232
32.4. Other Temperature Scales 233
32.4.1. Rankine Scale 233
32.4.2. Delisle Scale 233
32.4.3. Newton Scale 234
32.4.4. Réaumur Scale 234
32.4.5. Rømer Scale 234
CHAPTER 33 National Animal Identification System (NAIS) 236
33.1. Premises Identification Number (PIN) 236
33.2. Animal Identification Number (AIN and GIN) 237
33.3. Animal Tracking and other Considerations 240
CHAPTER 34 Iso 216 Paper Sizes ("A," "B," And "C " Series) 242
34.1. "B" Series 243
34.2. "C" Series 244
34.3. Tolerances 244
34.4. Non-ISO Paper Sizes 244
CHAPTER 35 Compass Points 246
35.1. Traditional Compass Points 246
35.2. Other Compass Point Systems 247
CHAPTER 36 Unicode 250
36.1. Types of Written Languages 251
36.2. Practical and Political Problems 251
36.3. Normalization 252
CHAPTER 37 Driver's Licenses 254
37.1. ID-1 Cards 255
37.2. U.S. Driver's Licenses 255
37.3. Enhanced Driver's License (EDL) 256
CHAPTER 38 Currency Units and Near Money 258
38.1. Stock Exchanges and Nondecimal Units 259
38.2. Decimalization in the United Kingdom 259
38.3. Physical Currency Choices 260
38.4. Coupons 262
38.4.1. Types of Coupons 263
38.4.2. Trade Groups and References 264
CHAPTER 39 Recipes and Food Preparation 266
39.1. Weight Versus Volume 266
39.2. Scaling a Recipe 267
CHAPTER 40 Portable Document Format (PDF) 268
40.1. ISO 32000 268
40.2. Things that Paper Cannot do 270
CHAPTER 41 Temporal Data 272
41.1. Notes On Calendar Standards 272
41.1.1. The Julian and Gregorian Calendars 273
41.1.2. Computerizing Calendars 275
41.1.3. Leap Seconds 276
41.1.4. Calendar Tables 277
41.2. SQL Temporal Data Types 280
41.2.1. Tips for Handling Dates, Timestamps and Times 281
41.2.2. Date Format Standards 281
41.2.3. Handling Timestamps 282
41.2.4. Handling Times 284
41.3. Expressions with Date Arithmetic 286
41.4. The Nature of Temporal Data Models 287
41.5. References on Temporal Data and SQL 288
CHAPTER 42 Additive Congruential Generators 290
CHAPTER 43 Traditional and Metric Typographic Units 294
43.1. DIN 16507-2 Font Sizes 296
Index 300
A 300
B 300
C 300
D 301
E 302
F 303
G 303
H 303
I 303
J 305
K 305
L 305
M 305
N 305
O 306
P 306
Q 307
R 307
S 307
T 308
U 309
V 309
W 310
X 310
Y 310
Z 310