SQL and Relational Theory

I have spent the last three or four weeks struggling through a book on relational database theory. Never content to know only the “how” of something, I wanted to learn some of the “why” behind SQL databases. This book covers that in depth, with a steep learning curve for someone like me who has a bit of experience using SQL in various applications, but who has never formally studied it. That is not a bad thing.

The book is SQL and Relational Theory: How to Write Accurate SQL Code by C. J. Date.

First, a bit of background. The author, C. J. Date, is well known in the field for his classic textbook An Introduction to Database Systems, which has gone through at least eight editions. He began working on databases in 1970 at IBM and worked with the inventor of the relational theory of database design, E. F. Codd. There is no doubt that this is a man who knows what he is talking about.

What this book sets out to do, in about 400 pages, is describe and define the relational model in greater depth and compare how SQL is currently used in many database applications like Oracle and MySQL to the theoretical ideal. As he does this, Date points out several inconsistencies with the SQL adaptation of the relational model and makes suggestions for how to adjust common usage to more closely conform to the ideal while acknowledging that at times perfect syncronization will be impossible using SQL. He also gives numerous examples of how it could be done in Tutorial D, which is an interesting study in itself.

I submit that this book is ideal for theorists, for highly qualified and experienced database administrators who want to learn at the feet of a sometimes emphatic and slightly dogmatic master who has been instrumental in the creation and spread of the relational database idea, but whose theory has not been perfectly implemented. If you merely want to know how to work within current implementation constraints without the larger picture, skip this book. If you want some fascinating, and sometimes difficult to grasp background and theory that will likely make you a better database wizard, this book is likely to help you get there.

Here are some highlights, sample questions and topics discussed in the book:

• What are the differences between the relational model and the various product implementations available today and in the past?
• Should a relational database allow duplicate entries?
• Why are null values a bad idea?
• What do relational expressions mean?
• Why must database constraint checking be immediate, at least in theory, and why is it often deferred in practice?

Seriously, that is just the beginning. There are excellent examples of SQL code throughout the book, great discussions of the mathematics behind the genesis of relational theory, and more. Beware, though, you may have to work to understand it, but it will be worthwhile for those who do.