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.
Chapter 1 Setting the Scene
The Relational Model Is Much Misunderstood
Some Remarks on Terminology
Principles, Not Products
A Review of the Original Model
Model vs. Implementation
Properties of Relations
Base vs. Derived Relations
Relations vs. Relvars
Values vs. Variables
Chapter 2 Types and Domains
Types and Relations
Data Value Atomicity
What's a Type?
Scalar vs. Nonscalar Types
Scalar Types in SQL
Type Checking and Coercion in SQL
Collations in SQL
Row and Table Types in SQL
Chapter 3 Tuples and Relations, Rows and Tables
What's a Tuple?
Rows in SQL
What's a Relation?
Relations and Their Bodies
Relations Are n-Dimensional
TABLE_DUM and TABLE_DEE
Tables in SQL
Column Naming in SQL
Chapter 4 No Duplicates, No Nulls
What's Wrong with Duplicates?
Duplicates: Further Issues
Avoiding Duplicates in SQL
What's Wrong with Nulls?
Avoiding Nulls in SQL
A Remark on Outer Join
Chapter 5 Base Relvars, Base Tables
Updating Is Set Level
More on Candidate Keys
More on Foreign Keys
Relvars and Predicates
Relations vs. Types
Chapter 6 SQL and Relational Algebra I: The Original Operators
More on Closure
Union, Intersection, and Difference
Which Operators Are Primitive?
Formulating Expressions a Step at a Time
What Do Relational Expressions Mean?
Evaluating SQL Table Expressions
The Reliance on Attribute Names
Chapter 7 SQL and Relational Algebra II: Additional Operators
Semijoin and Semidifference
Image Relations bis
Group and Ungroup
"What If" Queries
What About ORDER BY?
Chapter 8 SQL and Constraints
Type Constraints in SQL
Database Constraints in SQL
Why Database Constraint Checking Must Be Immediate
But Doesn't Some Checking Have to Be Deferred?
Constraints and Predicates
Chapter 9 SQL and Views
Views Are Relvars
Views and Predicates
Views and Constraints
What Are Views For?
Views and Snapshots
Chapter 10 SQL and Logic
Simple and Compound Propositions
Simple and Compound Predicates
More on Quantification
Chapter 11 Using Logic to Formulate SQL Expressions
Some Transformation Laws
Example 1: Logical Implication
Example 2: Universal Quantification
Example 3: Implication and Universal Quantification
Example 4: Correlated Subqueries
Example 5: Naming Subexpressions
Example 6: More on Naming Subexpressions
Example 7: Dealing with Ambiguity
Example 8: Using COUNT
Example 9: Join Queries
Example 10: UNIQUE Quantification
Example 11: ALL or ANY Comparisons
Example 12: GROUP BY and HAVING
Chapter 12 Miscellaneous SQL Topics
"Possibly Nondeterministic" Expressions
A BNF Grammar for SQL Table Expressions
Appendix The Relational Model
The Relational Model vs. Others
The Relational Model Defined
Objectives of the Relational Model
Some Database Principles
What Remains to Be Done?
Appendix Database Design Theory
The Place of Design Theory
Functional Dependencies and Boyce/Codd Normal Form
C.J. Date has a stature that is unique within the database industry. C.J. is a prolific writer, and is well-known for his best-selling textbook: An Introduction to Database Systems (Addison Wesley). C.J. is an exceptionally clear-thinking writer who can lay out principles and theory in a way easily understood by his audience.
I have been a SQL guy for a long time especially specializing in writing better sql code. This notion was with me until I read this book. However, after reading this book, I felt that so many things I was missing behind my working on SQL. The book well covers the theory behind relational database management. I have read few books of the same author who can be arguably called as one of the pioneers in Relational Theory in database management. Anytime, I ventured to read any of books authored by him, I get fascinated by his way of explaining the SQL in relation to mathematics, set theory etc.
Initially, while reading the book I struggled to concentrate and relate the information to my daily working on SQL. The book really related the theory behind of working of various concepts in SQL such as constraints, views, operators, joins etc. The author has particularly stressed upon the reason behind various theories in SQL in various database platforms.
I was impressed on the topic on using logic to formulate sql expressions. It opens up the idea in readers mind on how to approach to a sql deduction.
I suggest this book to all the database professionals who want to know the why's of database theories. Trust me, it really helps and you will be amazed to know how much more you will know on SQL.
Bottom Line Yes, I would recommend this to a friend
SQL and Relational Theory How to Write Accurate SQL Code C.J. Date / O'REILLY This is the best SQL book I have read so far! I have read many SQL books but this one beats every single one of them. This book is about how you can apply relational theory directly to your use of SQL. My favorite chapter was chapter 1 it teaches SQL terminology. It talks about The Relational Model, Principles, Structural Features and many more. Another good chapter is chapter 11 where it shows how to formulate logic. This book is great if you want to learn SQL and its logic, I have read this book twice. After using SQL Server for many years this book opened up my eyes to some of the struggles I have come across, I have learned to use SQL logic wisely. I am ready to go out into the real world I felt I was trapped inside a box and there was no other way to do things in sql, I thought my way was the best way. But after reading and talking with some people I realized I am not the only one using sql there are many ways of writing sql to do the same thing but better performance and now that I met a few sql people I felt small because they are way smarter than me when it comes to sql. I recommend this book!
Bottom Line Yes, I would recommend this to a friend
Solid reference for data designers, Excellent for acadamia
Comments about oreilly SQL and Relational Theory:
I was looking for a good book on relational theory and found it with "SQL and Relational Theory". The author does a nice job of explaning the relational algebra that sits behind SQL.
The topics covered include recommedations that are derived from best-practices. This is where much of the books value lies. The guidelines given cover such issues as table design, the use of NULLs, better WHERE clause construction, and the use of joins.
This version is very much improved over its predecesor "Databases In Depth". The explanations are more detailed with lots of examples. There are more topics covered and more best-practices displayed.
Examples are generally written in SQL but there are also tables, graphics, and exercises to help. Answers to the exercises are provided in the appendix C.
Recommend for students taking the 300 and 400-level databases course and anyone designing databases.
When I first got this book I was very excited; the author, C.J. Date is one of those names that you hear tossed around with Codd. I was hoping that this book would be a good refresher on basic SQL theorems and relational design, and while I was not disappointed, I was a bit overwhelmed by the third chapter. Having recently seen the latest incarnation of Star Trek, I thought a science-fiction metaphor might be appropriate.
Reading this book made me realize that I'm an engineer; I am Scotty. I know how my particular environment works, I know when "I'm giving her all she's got", and I know how to tweak the system to keep it afloat when pressed with stressful situations. SQL Server is a wonderful platform, but like all database systems, it's got its own peculiar quirks. Like all good engineers, you can learn to make the best of those quirks, but it doesn't mean you understand how the engine is supposed to work.
Date is definitely Spock; beyond the logical comparisons (pardon the pun), Date's focus is on the theory of how the relational model is supposed to work, not necessarily how SQL is implemented in the various database platforms. Very early on, he distinguishes between the relational model and SQL, and it became increasingly clear as I read this book that SQL relational model. Date tries his best to explain a very complex subject in a very thorough method, but I'm afraid that I'm not one of his best students.
The math involved in the relational model is very complex, and at times, I found myself frustrated with a passage because I knew how to do something in SQL, but had no clue as to what Date was saying in a relational language. That's both sad and liberating in a way; I walked away from this book knowing first that I'm comfortable being an engineer, and second that I need to re-read this material occasionally. Date is like broccoli; he'll never be my favorite author, but I know that what he's writing is good for me.
--Please note that I originally posted this review at http://atlantamdf.blogspot.com (http://atlantamdf.blogspot.com) - Stu