Thinking in Sets. Auxiliary, Temporal and Virtual Tables in SQL

Cover......Page 1
Copyright......Page 5
Contents......Page 8
Preface......Page 18
1. SQL Is Declarative, Not Procedural......Page 22
1.1 Different Programming Models......Page 23
1.2 Different Data Models......Page 25
1.2.1 Columns Are Not Fields......Page 26
1.2.2 Rows Are Not Records......Page 28
1.2.3 Tables Are Not Files......Page 32
1.2.4 Relational Keys Are Not Record Locators......Page 34
1.2.5 Kinds of Keys......Page 36
1.2.6 Desirable Properties of Relational Keys......Page 38
1.2.7 Unique But Not Invariant......Page 39
1.3 Tables as Entities......Page 40
1.5 Statements Are Not Procedures......Page 41
1.6.1 Table Splitting......Page 42
1.6.2 Column Splitting......Page 43
1.6.4 Faking Non-1NF Data......Page 45
1.6.5 Molecular Data Elements......Page 46
1.6.6 Isomer Data Elements......Page 47
1.6.7 Validating a Molecule......Page 48
2. Hardware, Data Volume, and Maintaining Databases......Page 50
2.1 Parallelism......Page 51
2.2 Cheap Main Storage......Page 52
2.4 Cheaper Secondary and Tertiary Storage......Page 53
2.6 The Mindset Has Not Changed......Page 54
3. Data Access and Records......Page 58
3.1.1 Tape-Searching Algorithms......Page 59
3.2 Indexes......Page 60
3.2.2 Multiple-Table Indexes......Page 61
3.3 Hashing......Page 62
3.3.4 Folding......Page 63
3.3.6 Collisions......Page 64
3.6.1 Row-Based Storage......Page 65
3.6.2 Column-Based Storage......Page 66
3.7 JOIN Algorithms......Page 67
3.7.2 Sort-Merge Join Method......Page 68
3.7.4 Shin’s Algorithm......Page 69
4. Lookup Tables......Page 72
4.1 Data Element Names......Page 73
4.2 Multiparameter Lookup Tables......Page 76
4.3 Constants Table......Page 77
4.4 OTLT or MUCK Table Problems......Page 80
4.5 Definition of a Proper Table......Page 83
5.1 Sequence Table......Page 86
5.1.1 Creating a Sequence Table......Page 88
5.1.2 Sequence Constructor......Page 89
5.1.3 Replacing an Iterative Loop......Page 90
5.2.1 Permutations via Recursion......Page 93
5.2.2 Permutations via CROSS JOIN......Page 94
5.3 Functions......Page 96
5.3.1 Functions without a Simple Formula......Page 97
5.4 Encryption via Tables......Page 99
5.5 Random Numbers......Page 100
5.6 Interpolation......Page 104
6. Views......Page 108
6.1.1 Efficient Access and Computations......Page 109
6.1.2 Column Renaming......Page 110
6.1.4 The VIEW Synchronization Rule......Page 111
6.2 Updatable and Read-Only VIEWs......Page 112
6.3.2 Calculated Columns......Page 114
6.3.3 Translated Columns......Page 115
6.3.4 Grouped VIEWs......Page 116
6.3.5 UNIONed VIEWs......Page 117
6.3.7 Nested VIEWs......Page 119
6.4.1 Class Hierarchies in SQL......Page 121
6.5 How VIEWs Are Handled in the Database System......Page 124
6.5.2 VIEW Materialization......Page 125
6.6 In-Line Text Expansion......Page 126
6.7 WITH CHECK OPTION Clause......Page 127
6.7.1 WITH CHECK OPTION as CHECK( ) clause......Page 131
6.8 Dropping VIEWs......Page 133
6.9.1 Domain Support......Page 134
6.9.3 VIEWs for Table Level CHECK( ) Constraints......Page 135
6.9.4 One VIEW per Base Table......Page 136
7. Virtual Tables......Page 138
7.1.1 Column Naming Rules......Page 139
7.1.2 Scoping Rules......Page 140
7.1.3 Exposed Table Names......Page 142
7.1.4 LATERAL() Clause......Page 143
7.2.1 Nonrecursive CTEs......Page 145
7.2.2 Recursive CTEs......Page 147
7.3.2 Vendors Models......Page 149
7.4 The Information Schema......Page 150
7.4.2 A Quick List of VIEWS and Their Purposes......Page 151
7.4.4 Definition Schema......Page 153
7.4.5 INFORMATION_SCHEMA Assertions......Page 156
8.1 Functions without a Simple Formula......Page 158
8.1.1 Encryption via Tables......Page 159
8.2.1 Check Digits Defined......Page 160
8.2.2 Error Detection versus Error Correction......Page 161
8.3.1 Weighted-Sum Algorithms......Page 162
8.3.2 Power-Sum Check Digits......Page 165
8.3.3 Luhn Algorithm......Page 166
8.3.4 Dihedral Five Check Digit......Page 167
8.4 Declarations, Not Functions, Not Procedures......Page 169
8.5 Data Mining for Auxiliary Tables......Page 173
9.1 The Nature of Time......Page 176
9.1.1 Durations, Not Chronons......Page 177
9.1.2 Granularity......Page 179
9.2 The ISO Half-Open Interval Model......Page 180
9.2.1 Use of NULL for “Eternity”......Page 182
9.2.2 Single Timestamp Tables......Page 183
9.2.3 Overlapping Intervals......Page 185
9.3 State Transition Tables......Page 195
9.4 Consolidating Intervals......Page 199
9.4.1 Cursors and Triggers......Page 201
9.4.3 CTE Solution......Page 202
9.5 Calendar Tables......Page 203
9.5.1 Day of Week via Tables......Page 204
9.5.2 Holiday Lists......Page 205
9.5.4 Self-Updating Views......Page 207
9.6 History Tables......Page 209
9.6.1 Audit Trails......Page 210
10. Scrubbing Data with Non-1NF Tables......Page 212
10.1 Repeated Groups......Page 213
10.1.1 Sorting within a Repeated Group......Page 216
10.2 Designing Scrubbing Tables......Page 219
10.3 Scrubbing Constraints......Page 222
10.4 Calendar Scrubs......Page 223
10.4.1 Special Dates......Page 224
10.5 String Scrubbing......Page 225
10.6 Sharing SQL Data......Page 226
10.6.1 A Look at Data Evolution......Page 227
10.6.2 Databases......Page 228
10.7 Extract, Transform, and Load Products......Page 229
10.7.1 Loading Data Warehouses......Page 230
10.7.2 Doing It All in SQL......Page 232
10.7.3 Extract, Load, and then Transform......Page 233
11. Thinking in SQL......Page 236
11.1.1 The Whole and Not the Parts......Page 237
11.1.2 Characteristic Functions......Page 238
11.1.3 Locking into a Solution Early......Page 240
11.2.2 Add the Words “Set of All…” in Front......Page 241
11.2.4 You Can Still Use Stubs......Page 242
11.2.5 Do Not Worry about Displaying the Data......Page 243
11.2.7 Do Not Be Afraid to Throw Away Your First......Page 244
11.2.8 Save Your First Attempts at DML......Page 245
11.2.10 Draw Circles and Set Diagrams......Page 246
11.2.11 Learn Your Dialect......Page 247
11.2.12 Imagine that Your WHERE Clause......Page 248
11.3 Do Not Use BIT or BOOLEAN Flags in SQL......Page 249
11.3.1 Flags Are at the Wrong Level......Page 250
11.3.2 Flags Confuse Proper Attributes......Page 251
12. Group Characteristics......Page 256
12.1 Grouping Is Not Equality......Page 258
12.2 Using Groups without Looking Inside......Page 259
12.2.1 Semiset-Oriented Approach......Page 260
12.2.2 Grouped Solutions......Page 261
12.2.3 Aggregated Solutions......Page 262
12.3 Grouping over Time......Page 263
12.3.1 Piece-by-Piece Solution......Page 264
12.3.2 Data as a Whole Solution......Page 265
12.4 Other Tricks with HAVING Clauses......Page 266
12.5.1 GROUPING SET Clause......Page 268
12.5.3 The CUBE Clause......Page 270
12.6 The WINDOW Clause......Page 271
12.6.1 The PARTITION BY Clause......Page 272
12.6.2 The ORDER BY Clause......Page 273
12.6.4 Programming Tricks......Page 274
13.1 Signs of Bad SQL......Page 276
13.1.3 Cursors......Page 277
13.1.5 Table-Valued Functions......Page 278
13.1.7 Formatting in the Database......Page 279
13.1.11 Attribute Splitting Across Rows......Page 280
13.2 Methods of Attack......Page 281
13.2.1 Cursor-Based Solution......Page 282
13.2.2 Semiset-Oriented Approach......Page 283
13.2.4 Advantages of Set-Oriented Code......Page 285
13.3 Translating Vague Specifications......Page 286
13.3.1 Go Back to the DDL......Page 287
13.3.2 Changing Specifications......Page 290
14.1 Clearing out Spaces in a String......Page 294
14.1.1 Procedural Solution #1......Page 295
14.1.2 Functional Solution #1......Page 296
14.1.3 Functional Solution #2......Page 299
14.2 The PRD() Aggregate Function......Page 301
14.3 Long Parameter Lists in Procedures and Functions......Page 303
14.3.1 The IN() Predicate Parameter Lists......Page 304
15.1 Procedural Solutions......Page 308
15.1.1 Reordering on a Numbering Column......Page 310
15.2.1 Simple Row Numbering......Page 312
15.2.2 RANK( ) and DENSE_RANK( )......Page 313
15.3 Sections......Page 314
16.1 Procedural Solution......Page 318
16.3 Other Kinds of Computed Data......Page 320
17.1 Triggers for Computations......Page 322
17.2 Complex Constraints via CHECK( ) and CASE......Page 323
17.3 Complex Constraints via VIEWs......Page 326
17.3.1 Set-Oriented Solutions......Page 327
17.4.2 WITH CHECK OPTION Clause......Page 329
17.4.3 WITH CHECK OPTION as CHECK( ) clause......Page 334
17.4.4 How VIEWs Behave......Page 335
17.4.5 UNIONed VIEWs......Page 337
17.4.6 Simple INSTEAD OF Triggers......Page 338
17.4.7 Warnings about INSTEAD OF Triggers......Page 342
18.1 Removing Letters in a String......Page 344
18.1.1 The Procedural Solution......Page 345
18.1.2 Pure SQL Solution......Page 346
18.2 Two Approaches to Sudoku......Page 347
18.2.2 Data-Driven Approach......Page 348
18.2.3 Handling the Given Digits......Page 350
18.3 Data Constraint Approach......Page 352
18.4 Bin Packing Problems......Page 356
18.4.2 The SQL Approach......Page 357
18.5 Inventory Costs over Time......Page 360
18.5.1 Inventory UPDATE Statements......Page 364
18.5.2 Bin Packing Returns......Page 365
Index......Page 370