What do you think?
Rate this book
SQL and Relational Theory: How to Write Accurate SQL Code
Understanding SQL's underlying theory is the best way to guarantee that your SQL code is correct and your database schema is robust and maintainable. On the other hand, if you're not well versed in the theory, you can fall into several traps. In SQL and Relational Theory, author C.J. Date demonstrates how you can apply relational theory directly to your use of SQL. With numerous examples and clear explanations of the reasoning behind them, you'll learn how to deal with common SQL dilemmas, such as:
Should database access granted be through views instead of base tables?
Nulls in your database are causing you to get wrong answers. Why? What can you do about it?
Could you write an SQL query to find employees who have never been in the same department for more than six months at a time?
SQL supports "quantified comparisons," but they're better avoided. Why? How do you avoid them?
Constraints are crucially important, but most SQL products don't support them properly. What can you do to resolve this situation?
Database theory and practice have evolved since Edgar Codd originally defined the relational model back in 1969. Independent of any SQL products, SQL and Relational Theory draws on decades of research to present the most up-to-date treatment of the material available anywhere. Anyone with a modest to advanced background in SQL will benefit from the many insights in this book.
Should database access granted be through views instead of base tables?
Nulls in your database are causing you to get wrong answers. Why? What can you do about it?
Could you write an SQL query to find employees who have never been in the same department for more than six months at a time?
SQL supports "quantified comparisons," but they're better avoided. Why? How do you avoid them?
Constraints are crucially important, but most SQL products don't support them properly. What can you do to resolve this situation?
Database theory and practice have evolved since Edgar Codd originally defined the relational model back in 1969. Independent of any SQL products, SQL and Relational Theory draws on decades of research to present the most up-to-date treatment of the material available anywhere. Anyone with a modest to advanced background in SQL will benefit from the many insights in this book.
432 pages, Kindle Edition
First published January 1, 2009
Ratings & Reviews
Friends & Following
Create a free account to discover what your friends think of this book!
Community Reviews
Displaying 1 - 18 of 18 reviews
March 31, 2013
C.J. Date is angry.
He's been working with the relational algebra for a long time—longer than SQL's been around—and he wishes people would use it. He wrote Databases, Types, and the Relational Model: The Third Manifesto with Hugh Darwen to try to rectify things, but productization seems elusive.
In the meantime, thankfully, Date has squinted hard at SQL and managed to find ways to approach it actually relationally (no, SQL as normally practiced can't be said to be relational—another example of terminology being appropriated for political/commercial gain rather than genuine conformance).
This book is indispensable if you want to actually understand how the usual use of SQL results in data anomalies and how to formulate queries from basic notions of set theory and first-order logic. In fact, the chapter dealing specifically with deriving queries from first-order logic alone is worth the price of admission.
He's been working with the relational algebra for a long time—longer than SQL's been around—and he wishes people would use it. He wrote Databases, Types, and the Relational Model: The Third Manifesto with Hugh Darwen to try to rectify things, but productization seems elusive.
In the meantime, thankfully, Date has squinted hard at SQL and managed to find ways to approach it actually relationally (no, SQL as normally practiced can't be said to be relational—another example of terminology being appropriated for political/commercial gain rather than genuine conformance).
This book is indispensable if you want to actually understand how the usual use of SQL results in data anomalies and how to formulate queries from basic notions of set theory and first-order logic. In fact, the chapter dealing specifically with deriving queries from first-order logic alone is worth the price of admission.
March 20, 2012
A painful read and that's not because of the topic matter.
The author desperately needed an editor to come in and horrible sentence structure and ditch a good 25% of the text in this book. The author's writing style left me screaming "GET TO THE @^$#ING POINT".
It's hard to get anything out a book when every other sentence has parenthesis and references to asides. A decent editor would have avoided this and greatly improved the book. I'm sure the author knows his stuff and I'm sure the book has great insight but there is way too much noise to pick through and life is too short.
The author desperately needed an editor to come in and horrible sentence structure and ditch a good 25% of the text in this book. The author's writing style left me screaming "GET TO THE @^$#ING POINT".
It's hard to get anything out a book when every other sentence has parenthesis and references to asides. A decent editor would have avoided this and greatly improved the book. I'm sure the author knows his stuff and I'm sure the book has great insight but there is way too much noise to pick through and life is too short.
February 5, 2012
Date's book is almost two books in one. First is a book on relational theory. As such, it is meant for deep reading. The second is SQL, the Good Parts. And as such it can get pendantic. But for someone who already knows something about working with data, it can be a good discussion on what you can and cannot get away with.
The first part of the book is an introduction to relational theory, mixed with the author's discussion of what is wrong with the SQL specification and the various implementations in database management systems that are currently available. While I appreciate the discussions from the point of view of making definitions clear, the numerous digressions into the failings of SQL become distraction after a point.
The second part is dominated by how to think in relational terms, and often the implementation within SQL. This is probably the valuable part of the book. Actually, because this is approached as much from a relational theory point of view as an SQL one, it becomes quite applicable to non-SQL forms of manipulating data (I usually manipulate data through programming languages such as R or Python). What this book becomes is a illustration of how to think when digging into data, and what transformations are reasonable and which gets you into trouble.
This is not light reading, or a reference to use when learning how to use SQL, or data manipulation tools in general. If you get around the discussion of the faults of implementations, it is a book on how to think when manipulating data as the first steps in data analysis.
Note: I received a free electronic copy of this book through the O'Reilly Blogger program
The first part of the book is an introduction to relational theory, mixed with the author's discussion of what is wrong with the SQL specification and the various implementations in database management systems that are currently available. While I appreciate the discussions from the point of view of making definitions clear, the numerous digressions into the failings of SQL become distraction after a point.
The second part is dominated by how to think in relational terms, and often the implementation within SQL. This is probably the valuable part of the book. Actually, because this is approached as much from a relational theory point of view as an SQL one, it becomes quite applicable to non-SQL forms of manipulating data (I usually manipulate data through programming languages such as R or Python). What this book becomes is a illustration of how to think when digging into data, and what transformations are reasonable and which gets you into trouble.
This is not light reading, or a reference to use when learning how to use SQL, or data manipulation tools in general. If you get around the discussion of the faults of implementations, it is a book on how to think when manipulating data as the first steps in data analysis.
Note: I received a free electronic copy of this book through the O'Reilly Blogger program
January 24, 2013
"SQL and Relational Theory", a book written by one of E.F. Codd's contemporaries, C.J. Date, is intended to give database practitioners an introduction to relational theory and its relationship with the SQL standard and syntax. The book accomplishes this goal but not in a clean way. The author has a lot to say about the SQL standard, much of which isn't unfair, but he does it in a way that is disruptive to the flow of the book.
Much of the conversation around SQL in this book tends be normative in nature, focusing how things ought to be instead of how they are. Reading this book was a grind, perhaps because my expectation was anchored in pragmatic grounds. There is a lot of value in the book, the consequences of NULLs, the dangers of duplicate rows, and so on. There's even a refresher on De Morgan's Laws which, like most people, I promptly forgot about after graduating from college.
C.J. Date is a very special person and connects us with an era that is fading, when the database was merely a physical instantiation of relational theory. He has a lot to say that is important about the state of databases, especially given that they have become our era's internal combustion engine. My preference would have been that he made a special section to discuss the current state of affairs and what needs to be done to correct them. Weaving the normative conversation throughout the book was disruptive to reading it.
I am not sure I would recommend the book even to those interested in relational theory. I would suggest looking closely at the preview text to see if it's a book you would be interested in.
Much of the conversation around SQL in this book tends be normative in nature, focusing how things ought to be instead of how they are. Reading this book was a grind, perhaps because my expectation was anchored in pragmatic grounds. There is a lot of value in the book, the consequences of NULLs, the dangers of duplicate rows, and so on. There's even a refresher on De Morgan's Laws which, like most people, I promptly forgot about after graduating from college.
C.J. Date is a very special person and connects us with an era that is fading, when the database was merely a physical instantiation of relational theory. He has a lot to say that is important about the state of databases, especially given that they have become our era's internal combustion engine. My preference would have been that he made a special section to discuss the current state of affairs and what needs to be done to correct them. Weaving the normative conversation throughout the book was disruptive to reading it.
I am not sure I would recommend the book even to those interested in relational theory. I would suggest looking closely at the preview text to see if it's a book you would be interested in.
January 25, 2023
Real relational algebra, with attribute sets and tuple sets defining relation, no nulls, no triple-valued logic and relation-called attributes, is rigorous and would be a better language for interacting with databases. But one's eyes bleed from sentences like "Before I finish with this section, I'd like to emphasize something I've touched on several times already: namely, the fact that there's a logical difference between a relation as such, on the one hand, and a picture of a relation as shown in Fig 1.1, on the other." Where are the editors? The book would be one tenth as long...
Abandoned halfway through chapter 7.
Abandoned halfway through chapter 7.
April 22, 2019
I never thought I would read such an emotional book about relational database theory.
March 28, 2021
I do not think I learnt much about relational theory or improved my SQL from this book. This book does raises some practices which were picked up naturally after using SQL for some time, so while this book claims to target those who already knew what they were doing with SQL, it doesn't provide much value to them.
It also claims to be much more theoretical than practical and tries to divorce itself from the implementation of the code. As a reader I would say implementation of the code is exactly what I was looking for since its title was "How to write accurate SQL code". I guess "optimized" and "accurate" are two very different ideas which my lack of proficiency English might have caused a misunderstanding.
Compared to the twelve chapters I would say the Appendix ended up being a more enjoyable read. It was a struggle to finish the chapters. I would recommend starting from Appendix E: Summary of recommendations, and use it as an index to the context of his recommendation among his chapters. Appendix F was also a good read, to understand some historical context of the industry although I'm sure the stuff mentioned are outdated as of today.
It also claims to be much more theoretical than practical and tries to divorce itself from the implementation of the code. As a reader I would say implementation of the code is exactly what I was looking for since its title was "How to write accurate SQL code". I guess "optimized" and "accurate" are two very different ideas which my lack of proficiency English might have caused a misunderstanding.
Compared to the twelve chapters I would say the Appendix ended up being a more enjoyable read. It was a struggle to finish the chapters. I would recommend starting from Appendix E: Summary of recommendations, and use it as an index to the context of his recommendation among his chapters. Appendix F was also a good read, to understand some historical context of the industry although I'm sure the stuff mentioned are outdated as of today.
July 24, 2012
It was quite a trudge through this book.
(As a disclaimer on this review, I should mention I only have a basic familiarity of SQL and this book assumes a more thorough knowledge of it from the outset. I doubt I'm qualified to have a strong an opinion on the book.)
One of the more compelling aspects of this book was that is seemed to target an approach of "theory in use." Since that's an expression that as far as I know I just made up, I'll define it quickly. Many technical books either fall loosely into the practical programming category, often as programming "cookbooks" (do X rather than the typical solution Y when solving problem Z -- here are some common-sense, rather than heavily theory-bound, examples showing why X is better), or they fall more into the theory and algorithm category as closer to textbooks (here's the math to solve a problem Z and one possible implementation X of solving it). SQL and Relational Theory seems to fall in the middle somewhere: it presumes you know, in general, how to solve a problem Z along with a typical solution X then dives into describing how the theory actually demands an alternate solution of Y.
That approach makes it pretty interesting and unique. It's refreshing and reassuring to see the author cares about and knows the math behind his arguments; yet my previous use of the word "demands" is intended to also highlight a peculilarity of this book.
I carefully chose the word "demands" to describe how this book describes its solutions. Lurking between the lines seems to be the sentiment, "Sure you could do it other ways, but obviously you'd be wrong..." It's truly surprising how fervently the author feels about the theory behind what's "correct." I myself tend to like to know and pursue the theory behind code (more than most, I believe) - but at times the author's fervency about correctness seemed - well, in many senses of the word, academic. In the real world, people don't model systems perfectly and if they tried to, nothing would ever get finished. A perfect language and a perfect model aren't going to exist in practice; yet, it seemed from the tone of the book, the author is desperately frustrated that the mathematically ideal database query language is not being used and people are doing things that are just flat out incorrect!
I see a huge number of flaws in the SQL language after having read this, but at the same time had to disagree with the author's seeming rejection of anything that is not emergent from theory alone...and just practical. The author strikes me as right in the majority of his cases, but not all. I think I'd rather the author's own Tutorial D were the predominant database language, rather than SQL (it seems a much cleaner and better designed language); yet at the same time, there was a bit too much certainty about what "is" correct in this book itself.
To give one example, I had wonder about his insistence that the specification of the MIN/MAX functions of an empty relvar should be correspondingly the maximum and minimum values for the scalar type in question. Why are those better? That doesn't seem any more theoretically correct than the SQL specification that they be null values. In fact, it seems to me, that the MIN and MAX of an empty set are undefined, and thus NULL is a far more appropriate value. Yet, it seems the author's incredulity that SQL is not modeled using pure binary logical (i.e. TRUE/FALSE, no NULL) carries over and forces him to reject the SQL specification as clearly incorrect. Perhaps it is incorrect -- but I'm not convinced it is (as posited by the text) self-evidently incorrect.
Taking Javascript as extreme counter-example to the author's theory-modeled Tutorial D language, the flexibility (nulls, undefined, virtually no typing, no constraints, etc.) and how incredibly useful Javascript can be, made me a bit frustrated with the author's seeming insistence on "obvious" correctness due to initial premises that might not be best in practice.
Considering the above as a non-expert with regards to SQL, I'll conjecture that SQL and Relational Theory is likely a great perspective on the topic, but impractical if it were treated as the complete picture.
(As a disclaimer on this review, I should mention I only have a basic familiarity of SQL and this book assumes a more thorough knowledge of it from the outset. I doubt I'm qualified to have a strong an opinion on the book.)
One of the more compelling aspects of this book was that is seemed to target an approach of "theory in use." Since that's an expression that as far as I know I just made up, I'll define it quickly. Many technical books either fall loosely into the practical programming category, often as programming "cookbooks" (do X rather than the typical solution Y when solving problem Z -- here are some common-sense, rather than heavily theory-bound, examples showing why X is better), or they fall more into the theory and algorithm category as closer to textbooks (here's the math to solve a problem Z and one possible implementation X of solving it). SQL and Relational Theory seems to fall in the middle somewhere: it presumes you know, in general, how to solve a problem Z along with a typical solution X then dives into describing how the theory actually demands an alternate solution of Y.
That approach makes it pretty interesting and unique. It's refreshing and reassuring to see the author cares about and knows the math behind his arguments; yet my previous use of the word "demands" is intended to also highlight a peculilarity of this book.
I carefully chose the word "demands" to describe how this book describes its solutions. Lurking between the lines seems to be the sentiment, "Sure you could do it other ways, but obviously you'd be wrong..." It's truly surprising how fervently the author feels about the theory behind what's "correct." I myself tend to like to know and pursue the theory behind code (more than most, I believe) - but at times the author's fervency about correctness seemed - well, in many senses of the word, academic. In the real world, people don't model systems perfectly and if they tried to, nothing would ever get finished. A perfect language and a perfect model aren't going to exist in practice; yet, it seemed from the tone of the book, the author is desperately frustrated that the mathematically ideal database query language is not being used and people are doing things that are just flat out incorrect!
I see a huge number of flaws in the SQL language after having read this, but at the same time had to disagree with the author's seeming rejection of anything that is not emergent from theory alone...and just practical. The author strikes me as right in the majority of his cases, but not all. I think I'd rather the author's own Tutorial D were the predominant database language, rather than SQL (it seems a much cleaner and better designed language); yet at the same time, there was a bit too much certainty about what "is" correct in this book itself.
To give one example, I had wonder about his insistence that the specification of the MIN/MAX functions of an empty relvar should be correspondingly the maximum and minimum values for the scalar type in question. Why are those better? That doesn't seem any more theoretically correct than the SQL specification that they be null values. In fact, it seems to me, that the MIN and MAX of an empty set are undefined, and thus NULL is a far more appropriate value. Yet, it seems the author's incredulity that SQL is not modeled using pure binary logical (i.e. TRUE/FALSE, no NULL) carries over and forces him to reject the SQL specification as clearly incorrect. Perhaps it is incorrect -- but I'm not convinced it is (as posited by the text) self-evidently incorrect.
Taking Javascript as extreme counter-example to the author's theory-modeled Tutorial D language, the flexibility (nulls, undefined, virtually no typing, no constraints, etc.) and how incredibly useful Javascript can be, made me a bit frustrated with the author's seeming insistence on "obvious" correctness due to initial premises that might not be best in practice.
Considering the above as a non-expert with regards to SQL, I'll conjecture that SQL and Relational Theory is likely a great perspective on the topic, but impractical if it were treated as the complete picture.
January 5, 2011
NOT FOR YOUR AVERAGE PROGRAMMER
This book takes a deep look in to how databases should be and not how they are and how you can make use of them. As a book on DB mathematics it would get 5 stars but as a book to teach "how to write accurate SQL code" It falls far short of it's goal.
Issues
1) Most people who are looking to "write accurate SQL code" are not at the mathematical level that this book requires. They are your average Joe Programmer and while they could benefit greatly from the math behind DBs the level of detail this book assumes would be non-existent.
2) The question sections at the end of the chapters are a great idea but most questions there have nothing to do with bettering your use of SQL.
3) A huge focus on the book is the authors pet project Tutoral D. While it's very interesting, is not SQL.
4) The authors feeling that DBs should not allow nulls or duplicates does not mean we need to be consistently told that. I get it, you have a very good argument as to why they are bad (Hell I kind of agree with you now) but you do not need to remind me every time you talk about them that you hate them.
5) The authors style tends to be too precise. This is not the worst thing but he will derail him self mid sentience to correct something he just said so that it is 100% accurate. The book is filled with foot notes yet it needs many more.
All in all as a database design and theory book 5 Stars. He clearly knows his stuff, has some very convincing ideas and has taught me allot about the theory. However As a book on "how to write accurate SQL code" the books main advice seems to be "SQL is bad. Someone should really get on fixing that."
This book takes a deep look in to how databases should be and not how they are and how you can make use of them. As a book on DB mathematics it would get 5 stars but as a book to teach "how to write accurate SQL code" It falls far short of it's goal.
Issues
1) Most people who are looking to "write accurate SQL code" are not at the mathematical level that this book requires. They are your average Joe Programmer and while they could benefit greatly from the math behind DBs the level of detail this book assumes would be non-existent.
2) The question sections at the end of the chapters are a great idea but most questions there have nothing to do with bettering your use of SQL.
3) A huge focus on the book is the authors pet project Tutoral D. While it's very interesting, is not SQL.
4) The authors feeling that DBs should not allow nulls or duplicates does not mean we need to be consistently told that. I get it, you have a very good argument as to why they are bad (Hell I kind of agree with you now) but you do not need to remind me every time you talk about them that you hate them.
5) The authors style tends to be too precise. This is not the worst thing but he will derail him self mid sentience to correct something he just said so that it is 100% accurate. The book is filled with foot notes yet it needs many more.
All in all as a database design and theory book 5 Stars. He clearly knows his stuff, has some very convincing ideas and has taught me allot about the theory. However As a book on "how to write accurate SQL code" the books main advice seems to be "SQL is bad. Someone should really get on fixing that."
July 25, 2016
Reader have to be very attentive to grasp some useful information about relational theory and how use it to write SQL. Endless purist`s lament about imperfection of the real world. Author criticizes the problems of existing RDBMS and SQL only from position of 100% relational theory compliance and never considers other factors which influenced these implementations. With all due respect to author`s contribution into relational theory his views on physical DB implementation are rather mythical and strange.
Even if considering this book as a pure SQL critique — it is still terribly written, like it never been edited: killing sentences structure, endless footnotes by author himself to explain what did he mean. and parts like extensive explanation of predicate logic.
Even if considering this book as a pure SQL critique — it is still terribly written, like it never been edited: killing sentences structure, endless footnotes by author himself to explain what did he mean. and parts like extensive explanation of predicate logic.
January 4, 2016
An okay book sadly made silly by the sheer amount of indiscriminate pedantry. The author seeks to make the case that being a pedant when thinking about relational theory is important, particularly when compared to SQL's lazy implementation or contradiction of said theory. TRUE ENOUGH. But some things are more important to be a pedant about than others. I did not, for example, need a page's explanation to grasp that a paper representation of a relation isn't the same thing as the relation itself. Let it lie, Plato!
February 27, 2011
Pleasantly precise.
Want to Read
October 9, 2011Reading this on my phone as an epub, so it's a bit slow going. I have the older edition of this in print, but it's changed enough that I'm trying to stick to the newer material.
April 7, 2013
I always wondered why SQL works the way it does. This book has the answers.
August 10, 2015
A "relationally" written book.
October 27, 2015
Perfeito! Explicação muito bem detalhada sobre os tópicos, apresenta de forma clara seu raciocínio e objetivos.
Read
April 11, 2016Relational theory in depth. Clearly explains how SQL isn't as relational as you may think...
July 10, 2015
Level: Intermidiate to advance
Displaying 1 - 18 of 18 reviews