This book was designed as an advanced supplement to collegiate study in relational database theory and database management, suitable for AACSB collegiate business school curricula and advanced computer science courses, but it is also valuable for the professional developer and IT manager who wishes to create and enforce SQL design standards and common problem solving algorithms. This is the first of its kind book to cover SQL Design Patterns. Leveraging on the popularity of Design Patterns books for procedural programming, this text categorized and explains common SQL structures and describes design patterns that allow a common framework for all SQL queries. This book emphasizes theoretical foundation for almost every query problem in relational databases.

* Apply theoretical design patterns to relational calculus. * Apply relational division, set joins, user-defined aggregates, and pivot to access coding style. * Reinforce quantitative methods for relational closure.

SQL Design Patterns Book Preface

SQL is a very successful language. Yet, is there a place for an advanced SQL book in the era of “Learn Technology X in 10 Minutes”? Indeed, the tech industry today emphasizes a shallow knowledge of a huge number of technical skills, rather than deep knowledge of a more specialized skill. That’s how workers have been getting jobs, by knowing a tiny bit of many skills employers might need. Someone who knows how to use SQL in an advanced way does not have a proportionate advantage in getting a job over someone who knows only the basics. If this second person, however, also knows J2EE, XSLT, Ajax, Flash, or any other flash in the pan to a similarly basic level, then he or she has a greater employment advantage.

The major flaw of this line of reason is equating the sophistication of SQL to these rather unsound technologies. This might be surprising to a newcomer who generally finds SQL a little bit old fashioned compared to the “modern” programming languages. It is almost as old as C, which spawned at least 3 newer generation languages already, and it looks like COBOL, so why isn’t it obsolete yet? Let me assure you that this appearance is misleading. Under the cover of sloppy and archaic syntax, a high abstraction language is found.

SQL programming is very unusual from the procedural perspective: there is no explicit flow control, no loops and no variables either to apply operations to or to store intermediate results into. SQL heavily leverages predicates instead, which elevates it to Logic Programming. Then, the grouping and aggregation syntax blends naturally into this already formidable logic foundation. Anybody who rediscovers that matrix or polynomial multiplication can be written in just three lines of code has a long lasting impression.

These ingredients that make SQL unique partially explain why advanced SQL programming does not revolve around syntax features, but demands a SQL programmer to develop an ability to recognize complex patterns. Yes, beyond a certain point a skill of piling up subqueries does not give much of a return and one has to study some rudimentary theory, which classifies known SQL solutions into patterns.

Patterns in procedural programming became popular a decade ago, originated with a landmark book by Gamma et. al. Each pattern has its name so that developers could quickly refer to it by just a name. “Oh, that’s a singleton!” instead of a lengthy description and often accompanied with a code snippet.

Patterns received a sour reception in a high abstraction language community. The prevailing perception was that patterns are a signature of low level programming. When a programmer sees patterns in her programs, it is a sign of trouble. The shape of a program should reflect only the problem it needs to solve. Any other regularity in the code indicates that abstractions are not powerful enough.

In reality, however, any language is quite limited in its abstraction power, declarative languages notwithstanding. Sooner or later we have to find workarounds for those limitations. This is how SQL patterns were born!

Patterns greatly improve our communication capabilities. On internet SQL forums it is not uncommon for people to ask the same question over and over again. Pattern names such as Interval coalesce or Relational division, for example, rarely surface in the discussion thread, giving way to numerous reply messages pointlessly competing to see who can find a query that does not look intimidating. Patterns allow succinct replies like this: “Lookup the Interval coalesce method in the … textbook”.

Establishing common pattern names is the first goal of this book. Most of the patterns have standard names: Skyline query, Pivot, or Nested Intervals, for example. Few do not; we have to work out a name, like the fancy sounding Discrete interval sampling, for example.

When presenting SQL patterns in this book I decided to dismiss the standard template form. Template is perfect for reference material, but is a nuisance for a textbook. More important than this stylistic comment, however, is the fact that fairly soon you might stumble into patterns that require little familiarity with undergraduate level math. Don’t be discouraged, however: as John Garmany suggested, many topics start making sense on second reading. In the Indicator Functions section, for example, you may want to skip the theory, first, rewind to the sample problem and SQL solutions, then, rollback to the theory.

List of SQL Design patterns:

 Counting
 Conditional summation
 Integer generator
 String/Collection decomposition
 List Aggregate
 Enumerating pairs
 Enumerating sets
 Interval coalesce
 Discrete interval sampling
 User-defined aggregate
 Pivot
 Symmetric difference
 Histogram
 Skyline query
 Relational division
 Outer union
 Complex constraint
 Nested intervals
 Transitive closure
 Hierarchical total
.

About the Author:

Vadim Tropashko

Vadim Tropashko graduated from Moscow Institute of Physics and Technology in 1984. Tropashko researched Petri Nets for five years following graduation. In the early 90s, his interests switched to OOP. Tropashko translated "The C++ Programming Language" by B.Stroustrup into Russian. He was a C++ instructor at University of Radio and Electronics in Belarus. In the mid 90s, Tropashko's interest changed from OOP to databases. Vadim Tropashko has now worked for Oracle since 1998.

Table of Contents:
 

Chapter 1 - Counting in SQL List of patterns Introduction to SQL Counting Counting Ordered Rows Conditional Summation with CASE Operator Indicator and Step Functions A Case for the CASE Operator Summarizing by more than one Relation Interval Coalesce Chapter 2 - Integer Generators in SQL Integers Relation Recursive With Big Table Table Function Cube Hierarchical Query String Decomposition Enumerating Pairs Enumerating Sets of Integers Discrete Interval Sampling Chapter 3 - Exotic Operators in SQL Introduction to SQL exotic operators List Aggregate Product Factorial Interpolation Pivot Symmetric Difference Histograms in SQL Equal-Width Histogram Equal-Height Histogram Logarithmic Buckets Skyline Query Relational Division Outer Union

Chapter 4 - SQL Constraints Function Based Constraints Symmetric Functions Materialized View Constraints Disjoint Sets Disjoint Intervals Temporal Foreign Key Constraint Cardinality Constraint   Chapter 5 - Trees in SQL Materialized Path Nested Sets Interval Halving From Binary to N-ary Trees Matrix Encoding Parent and Children Query Nested Intervals Descendants Query Ancestor Criteria Ancestors Query Converting Matrix to Path Inserting Nodes Relocating Tree Branches Ordering Exotic Labeling Schemas Dietz Encoding Pre-order – Depth Encoding Reversed Nesting Ordered Partitions Chapter 6 - Graphs in SQL Schema Design Tree Constraint Transitive Closure Recursive SQL Connect By Incremental Evaluation Hierarchical Weighted Total Generating Baskets Comparing Hierarchies

Topic Index

Index: