SQL is full of difficulties and traps for the unwary. You can avoid them if you understand relational theory, but only if you know how to put the theory into practice. In this insightful book, author C.J. Date explains relational theory in depth, and demonstrates through numerous examples and exercises how you can apply it directly to your use of SQL.
This second edition includes new material on recursive queries, “missing information” without nulls, new update operators, and topics such as aggregate operators, grouping and ungrouping, and view updating. If you have a modest-to-advanced background in SQL, you’ll learn how to deal with a host of common SQL dilemmas.
Why is proper column naming so important?
Nulls in your database are causing you to get wrong answers. Why? What can you do about it?
Is it possible to 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 the relational model was developed more than 40 years ago. SQL and Relational Theory draws on decades of research to present the most up-to-date treatment of SQL available.
C.J. Date has a stature that is unique within the database industry. A prolific writer well known for the bestselling textbook An Introduction to Database Systems (Addison-Wesley), he has an exceptionally clear style when writing about complex principles and theory.
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 ONE 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, UNGROUP, AND RELATION VALUED ATTRIBUTES
“WHAT IF” QUERIES
A NOTE ON RECURSION
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
WHY DO WE NEED 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 SIMPLIFIED BNF GRAMMAR
Appendix The Relational Model
THE RELATIONAL MODEL vs. OTHERS
THE SIGNIFICANCE OF THEORY
THE RELATIONAL MODEL DEFINED
OBJECTIVES OF THE RELATIONAL MODEL
SOME DATABASE PRINCIPLES
WHAT REMAINS TO BE DONE?
Appendix SQL Departures from the Relational Model
Appendix A Relational Approach to Missing Information
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.
Comments about oreilly SQL and Relational Theory, 2nd Edition:
If you're a cloistered academic with absolutely no need to use a real-world SQL-based DBMS to solve problems, then you might find this book useful. On the other hand, if you use SQL Server, Oracle, DB2, etc to design databases for end-users and applications, then this book isn't going to help you design better database ER models.
You will get to read endless pages of repetitive nonsense about how tables are or are not relvars and rows are or are not tuples, and countless variations on this same theme. Who cares whether SQL has no concept of a 0-tuple, or that field names should be called headings, etc?
I read the first four chapters of this book, progressively reluctantly but by the time I got to the fifth chapter it began to dawn on me that I wasn't going to learn a thing, and since much of it is based on Tutorial D (and entirely theoretical language, as far as I can tell), and not SQL, it was even more pointless.
My only saving grace is that I did not have to pay for it myself, since my boss bought it (at my request, I am ashamed to say). I also spent six or so hours of the past two days recovering from an appendectomy reading it when I could have done something more useful, like count grains of rice in a jar.
I am now going to see if there is such a thing as an SQL Cookbook, with real-world problems and suggested solutions to same.
Bottom Line No, I would not recommend this to a friend
Comments about oreilly SQL and Relational Theory, 2nd Edition:
It's about Relational model and SQL. Someone who interest about Database, SQL and relational model, that should read it. You will learn SQL and Relational theory from Professional. When you read each chapter finish, you will see "Exercise", that will help you memorize and understand more. It has well-formed paragraphs and many references; it's good for readers. It can be reference book in the college or university.
Bottom Line Yes, I would recommend this to a friend