Joe Celko's Data, Measurements and Standards in SQL

Joe Celko's Data, Measurements and Standards in SQL

by Joe Celko
Released September 2009
Publisher(s): Morgan Kaufmann
ISBN: 9780080884455

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

Joe Celko has looked deep into the code of SQL programmers and found a consistent and 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.

Data, Measurements and Standards in SQL reveals the shift these 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, Celko enables readers to write and implement portable data that can interface to any number of external application systems!

This book doesn't limit itself to one subject, but serves as a detailed synopsis of measurement scales and data standards for all industries, thereby giving RDBMS programmers and designers the knowledge and know-how they need to communicate effectively across business boundaries.

* Collects and details the diverse data standards of myriad industries under one cover, thereby creating a definitive, one-stop-shopping opportunity for database programmers.

* Enables readers to write and implement portable data that can interface to any number external application systems, allowing readers to cross business boundaries and move up the career ladder.

* Expert advice from one of the most-read SQL authors in the world who is well known for his ten years of service on the ANSI SQL standards committee and Readers Choice Award winning column in Intelligent Enterprise.

Table of contents

  1. Cover image
  2. Table of Contents
  3. Copyright
  4. Introduction
  5. Part 1. History, Standards, and Designing Data
  6. Chapter 1. Scales and Measurements
  7. 1.1. Measurement Theory
  8. 1.2. Defining a Measurement
  9. 1.3. Tolerance
  10. 1.4. Validation
  11. 1.5. Verification
  12. Chapter 2. Validation
  13. 2.1. Look-Up Tables
  14. 2.2. Check Digits
  15. 2.3. Declarations, Not Functions, Not Procedures
  16. 2.4. Patterns and Regular Expressions
  17. 2.5. Nondatabase Validation
  18. Chapter 3. Data Encoding Schemes
  19. 3.1. Bad Encoding Schemes
  20. 3.2. Encoding Scheme Types
  21. 3.3. Atomic Versus Scalar
  22. 3.4. Transition States
  23. 3.5. General Guidelines for Designing Encoding Schemes
  24. 3.6. Keep the Codes in the Database
  25. 3.7. Multiple Character Sets
  26. Chapter 4. Scales
  27. 4.1. BIT Flags are Not Scales
  28. 4.2. Dimensionless Measurements
  29. 4.3. Types of Scales
  30. 4.4. Using Scales
  31. 4.5. Scale Conversion
  32. 4.6. Derived Units
  33. 4.7. Punctuation and Standard Units
  34. 4.8. General Guidelines for Using Scales in a Database
  35. Chapter 5. Data with Ignorance
  36. 5.1. Get it Right
  37. 5.2. Replace Bad Values with a General Dummy Value
  38. 5.3. Replace Bad Values with a Statistical Dummy Value
  39. 5.4. Replace Bad Values to Complete a Statistical Distribution
  40. 5.5. Replace Bad Values with Statistical Profiling
  41. Chapter 6. Keys
  42. 6.1. Uniqueness
  43. 6.2. A Key Cannot be NULL
  44. 6.3. Invariant or Universally Controlled Values
  45. 6.4. Surrogate and Physical Locators Keys
  46. Part 2. A Sampling of Standards
  47. Chapter 7. Dates
  48. 7.1. ISO-8601 Standard
  49. 7.2. Putting it All Together
  50. 7.3. Durations and Intervals
  51. Chapter 8. Sex Codes
  52. 8.1. Sex Codes
  53. 8.2. Other Sources
  54. Chapter 9. Ethnicity and Race Codes
  55. 9.1. Race Versus Ethnicity
  56. 9.2. U.K. Ethnic Groups
  57. Chapter 10. ISO-3166 and Other Country Codes
  58. 10.1. ISO 3166-1
  59. 10.2. ISO 3166-2
  60. 10.3. ISO 3166-3
  61. Chapter 11. Language Codes
  62. Chapter 12. Currency Codes
  63. 12.1. Noncurrency Units
  64. Chapter 13. National Identification Numbers
  65. 13.1. Social Security Numbers
  66. 13.2. Social Insurance Number
  67. 13.3. Swedish Personal Identity Number
  68. 13.4. EU Biometric Passports
  69. Chapter 14. Occupations
  70. 14.1. National Occupational Classification (NOC)
  71. Chapter 15. Colors
  72. 15.1. International Color Consortium
  73. Chapter 16. Telephone Numbers
  74. 16.1. The International Telephone Number Components
  75. 16.2. Subscriber Local Exchange or Prefix
  76. Chapter 17. E-Mail Addresses
  77. Chapter 18. Universal Postal Union
  78. 18.1. ZIP Code
  79. 18.2. Canadian Postal Codes
  80. 18.3. Postcodes in the United Kingdom
  81. 18.4. Case Expression for Many International Postal Codes
  82. Chapter 19. Hierarchical Triangular Mesh
  83. Chapter 20. Shoe Sizes
  84. 20.1. Mondopoint System
  85. Chapter 21. International Clothing Sizes
  86. Chapter 22. ICD Codes
  87. 22.1. Local Versions
  88. 22.2. Mental and Behavioral Disorders
  89. Chapter 23. Vehicle Identification Number (VIN)
  90. 23.1. VIN Format
  91. Chapter 24. Freight Containers
  92. 24.1. Freight Container Codes
  93. 24.2. Size and Type Codes
  94. 24.3. Related ISO Standards
  95. Chapter 25. Credit Card Numbers
  96. 25.1. Card Issuers
  97. 25.2. Account Number
  98. 25.3. Other Numbers
  99. 25.4. Personal Identification Numbers
  100. 25.5. PCI DSS and Related Standards
  101. 25.6. Tools and More Information
  102. Chapter 26. SWIFT and Related Banking Standards
  103. 26.1. BIC Codes
  104. 26.2. International Bank Account Number (IBAN)
  105. Chapter 27. Data Universal Numbering System
  106. Chapter 28. Global Trade Item Number
  107. 28.1. GTIN Family
  108. 28.2. ISBN
  109. Chapter 29. Digital Object Identifier (DOI)
  110. 29.1. DOI Syntax
  111. 29.2. ISBN-A
  112. Chapter 30. Audiovisual Media
  113. 30.1. Format
  114. 30.2. Cataloging AV Materials
  115. Chapter 31. ISIN and Related Securities Identifiers
  116. 31.1. CUSIP
  117. 31.2. SEDOL and Other Countries
  118. 31.3. Classification of Financial Instruments
  119. Chapter 32. Temperature Scales
  120. 32.1. Celsius Scale
  121. 32.2. Fahrenheit Scale
  122. 32.3. Kelvin Scale
  123. 32.4. Other Temperature Scales
  124. Chapter 33. National Animal Identification System (NAIS)
  125. 33.1. Premises Identification Number (PIN)
  126. 33.2. Animal Identification Number (AIN and GIN)
  127. 33.3. Animal Tracking and Other Considerations
  128. Chapter 34. ISO 216 Paper Sizes (“A,” “B,” and “C” Series)
  129. 34.1. “B” Series
  130. 34.2. “C” Series
  131. 34.3. Tolerances
  132. 34.4. Non-ISO Paper Sizes
  133. Chapter 35. Compass Points
  134. 35.1. Traditional Compass Points
  135. 35.2. Other Compass Point Systems
  136. Chapter 36. Unicode
  137. 36.1. Types of Written Languages
  138. 36.2. Practical and Political Problems
  139. 36.3. Normalization
  140. Chapter 37. Driver’s Licenses
  141. 37.1. ID-1 Cards
  142. 37.2. U.S. Driver’s Licenses
  143. 37.3. Enhanced driver’s License (EDL)
  144. Chapter 38. Currency Units and Near Money
  145. 38.1. Stock Exchanges and Nondecimal Units
  146. 38.2. Decimalization in the United Kingdom
  147. 38.3. Physical Currency Choices
  148. 38.4. Coupons
  149. Chapter 39. Recipes and Food Preparation
  150. 39.1. Weight Versus Volume
  151. 39.2. Scaling a Recipe
  152. Chapter 40. Portable Document Format (PDF)
  153. 40.1. ISO 32000
  154. 40.2. Things that Paper Cannot Do
  155. Chapter 41. Temporal Data
  156. 41.1. Notes on Calendar Standards
  157. 41.2. SQL Temporal Data Types
  158. 41.3. Expressions with Date Arithmetic
  159. 41.4. The Nature of Temporal Data Models
  160. Chapter 42. Additive Congruential Generators
  161. Chapter 43. Traditional and Metric Typographic Units
  162. 43.1. DIN 16507-2 Font Sizes
  163. Index

Product information

  • Title: Joe Celko's Data, Measurements and Standards in SQL
  • Author(s): Joe Celko
  • Release date: September 2009
  • Publisher(s): Morgan Kaufmann
  • ISBN: 9780080884455