Read more
Informationen zum Autor Joe Celko served 10 years on ANSI/ISO SQL Standards Committee and contributed to the SQL-89 and SQL-92 Standards. Mr. Celko is author a series of books on SQL and RDBMS for Elsevier/MKP. He is an independent consultant based in Austin, Texas. He has written over 1200 columns in the computer trade and academic press, mostly dealing with data and databases. Through detailed examples and clear explanations, Celko guides programmers to conceptualize database problems as rooted in sets. The book focuses on three table-based SQL techniques that not only demonstrate this set characteristic of SQL, but also dramatically simplify application development. Zusammenfassung Perfectly intelligent programmers often struggle when forced to work with SQL. Why? This title intends to change the way you think about the problems you solve with SQL programs. Focusing on three key table-based techniques, it reveals their power through detailed examples and explanations.
List of contents
1 SQL Is Declarative, Not Procedural1.1 Different Programming Models1.2 Different Data Models1.2.1 Columns Are Not Fields1.2.2 Rows Are Not Records1.2.3 Tables Are Not Files 1.2.4 Relational Keys Are Not Record Locators 1.2.5 Kinds of Keys 1.2.6 Desirable Properties of Relational Keys 1.2.7 Unique But Not Invariant 1.3 Tables as Entities 1.4 Tables as Relationships1.5 Statements Are Not Procedures 1.6 Molecular, Atomic, and Subatomic Data Elements 1.6.1 Table Splitting 1.6.2 Column Splitting 1.6.3 Temporal Splitting 1.6.4 Faking Non-1NF Data 1.6.5 Molecular Data Elements 1.6.6 Isomer Data Elements 1.6.7 Validating a Molecule 2 Hardware, Data Volume, and Maintaining Databases 2.1 Parallelism 2.2 Cheap Main Storage 2.3 Solid-State Disk 2.4 Cheaper Secondary and Tertiary Storage 2.5 The Data Changed 2.6 The Mindset Has Not Changed 3 Data Access and Records 3.1 Sequential Access 3.1.1 Tape-Searching Algorithms 3.2 Indexes 3.2.1 Single-Table Indexes 3.2.2 Multiple-Table Indexes 3.2.3 Type of Indexes 3.3 Hashing 3.3.1 Digit Selection 3.3.2 Division Hashing 3.3.3 Multiplication Hashing 3.3.4 Folding 3.3.5 Table Lookups 3.3.6 Collisions 3.4 Bit Vector Indexes 3.5 Parallel Access 3.6 Row and Column Storage 3.6.1 Row-Based Storage 3.6.2 Column-Based Storage 3.7 JOIN Algorithms 3.7.1 Nested-Loop Join Algorithm 3.7.2 Sort-Merge Join Method 3.7.3 Hash Join Method 3.7.4 Shin's Algorithm 4 Lookup Tables 4.1 Data Element Names 4.2 Multiparameter Lookup Tables 4.3 Constants Table 4.4 OTLT or MUCK Table Problems 4.5 Defi nition of a Proper Table5 Auxiliary Tables 5.1 Sequence Table 5.1.1 Creating a Sequence Table 5.1.2 Sequence Constructor 5.1.3 Replacing an Iterative Loop 5.2 Permutations 5.2.1 Permutations via Recursion 5.2.2 Permutations via CROSS JOIN 5.3 Functions 5.3.1 Functions without a Simple Formula 5.4 Encryption via Tables 5.5 Random Numbers 5.6 Interpolation 6 Views 6.1 Mullins VIEW Usage Rules 6.1.1 Effi cient Access and Computations 6.1.2 Column Renaming 6.1.3 Proliferation Avoidance 6.1.4 The VIEW Synchronization Rule 6.2 Updatable and Read-Only VIEWs 6.3 Types of VIEWs 6.3.1 Single-Table Projection and Restriction 6.3.2 Calculated Columns 6.3.3 Translated Columns 6.3.4 Grouped VIEWs 6.3.5 UNIONed VIEWs 6.3.6 JOINs in VIEWs 6.3.7 Nested VIEWs 6.4 Modeling Classes with Tables 6.4.1 Class Hierarchies in SQL 6.4.2 Subclasses via ASSERTIONs and TRIGGERs 6.5 How VIEWs Are Handled in the Database System 6.5.1 VIEW Column List 6.5.2 VIEW Materialization 6.6 In-Line Text Expansion 6.7 WITH CHECK OPTION Clause 6.7.1 WITH CHECK OPTION as CHECK( ) Clause 6.8 Dropping VIEWs 6.9 Outdated Uses for VIEWs 6.9.1 Domain Support 6.9.2 Table Expression VIEWs 6.9.3 VIEWs for Table Level CHECK( ) Constraints 6.9.4 One VIEW per Base Table 7 Virtual Tables 7.1 Derived Tables 7.1.1 Column Naming Rules 7.1.2 Scoping Rules 7.1.3 Exposed Table Names 7.1.4 LATERAL() Clause 7.2 Common Table Expressions 7.2.1 Nonrecursive CTEs 7.2.2 Recursive CTEs 7.3 Temporary Tables 7.3.1 ANSI/ISO Standards 7.3.2 Vendors Models 7.4 The Information Schema 7.4.1 The INFORMATION_SCHEMA Declarations 7.4.2 A Quick List of VIEWS and Their Purposes 7.4.3 DOMAIN Declarations 7.4.4 Defi nition Schema 7.4.5 INFORMATION_SCHEMA Assertions 8 Complicated Functions via Tables 8.1 Functions without a Simple Formula 8.1.1 Encryption via Tables 8.2 Check Digits via Tables 8.2.1 Check Digits Defi ned 8.2.2 Error Detection versus Error Correction 8.3 Classes of Algorithms 8.3.1 Weighted-Sum Algorithms 8.3.2 Power-Sum Check Digits 8.3.3 Luhn Algorithm 8.3.4 Dihedral Five Check Digit 8.4 Declarations, Not Functions, Not Procedures 8.5 Data Mining for Auxiliary Tables 9 Temporal Tables 9.1 The Nature of Time 9.1.1 Durations, Not Chronons 9.1.2 Granularity 9.2 The ISO Half-Open Interval Model 9.2.1 Use of NULL for "Eternity” 9.2.2 Single Timestamp Tables 9.2.3 Overlapping Intervals 9.3 State Transition Tables 9.4 Consolidating Intervals 9.4.1 Cursors and Triggers 9.4.2 OLAP Function Solution 9.4.3
