SQL and Relational Theory: How to Write Accurate SQL Code
Language: English
Pages: 582
ISBN: 1491941170
Format: PDF / Kindle (mobi) / ePub
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 that theory into practice. In this book, Chris Date explains relational theory in depth, and demonstrates through numerous examples and exercises how you can apply it to your use of SQL.
This third edition has been revised, extended, and improved throughout. Topics whose treatment has been expanded include data types and domains, table comparisons, image relations, aggregate operators and summarization, view updating, and subqueries. A special feature of this edition is a new appendix on NoSQL and relational theory.
- Could you write an SQL query to find employees who have worked at least once in every programming department in the company? And be sure it’s correct?
- Why is proper column naming so important?
- Nulls in the database cause wrong answers. Why? What you can do about it?
- How can image relations help you formulate complex SQL queries?
- SQL supports "quantified comparisons," but they’re better avoided. Why? And how?
Database theory and practice have evolved considerably since Codd first defined the relational model, back in 1969. This book draws on decades of experience to present the most up to date treatment of the material available anywhere. Anyone with a modest to advanced background in SQL can benefit from the insights it contains. The book is product independent.
the following expression, though syntactically legal, would not be a correct formulation of the query (why not?): { PX.PNO , TOTQ := SUM ( SPX.QTY WHERE SPX.PNO = PX.PNO ) } Answer: Because duplicate quantities would be eliminated before the sum is computed. Example 7: Get part cities that store more than five red parts. { PX.CITY } WHERE COUNT ( PY WHERE PY.CITY = PX.CITY AND PY.COLOR = 'Red' ) > 5 Sample Constraints Now I’d like to give some examples of the use of relational
it does apply to. EXAMPLE 5: NAMING SUBEXPRESSIONS Another query: “Get full supplier details for suppliers who supply all purple parts.” Note: This query, or one very like it, is often used to demonstrate a flaw in the relational divide operator as originally defined. See the further remarks on this topic at the end of the present section. Here first is a logical formulation: { SX } WHERE FORALL PX ( IF PX.COLOR = 'Purple' THEN EXISTS SPX ( SPX.SNO = SX.SNO AND SPX.PNO = PX.PNO ) )
remove (say) the SNAME component, what’s left is indeed still a tuple: viz., a tuple with three components (a tuple of degree three). Every subset of a heading is a heading: For example, consider the heading of the suppliers relation in Figure 1-3. That heading has four attributes: SNO, SNAME, STATUS, and CITY. And if we remove (say) the SNAME and STATUS attributes, what’s left is still a heading, a heading of degree two. Every subset of a body is a body: For example, consider the body of
the chapter was a little light on technical substance, therefore). Anyway, just to review briefly: I explained why we’d be concerned with principles, not products, and why I’d be using formal terminology such as relation, tuple, and attribute (at least in relational contexts) in place of their more “user friendly” SQL counterparts. I gave an overview of the original model, touching in particular on the following concepts: type (or domain), n-ary relation, tuple, attribute, candidate key (key
SUMMARIZE ( S WHERE CITY = 'London' ) PER ( TABLE_DEE ) : { N := COUNT ( SNO ) } Note: Tutorial D allows the PER clause to be omitted, in which case PER (TABLE_DEE) is assumed by default. The foregoing SUMMARIZE could therefore be simplified slightly, thus: SUMMARIZE ( S WHERE CITY = 'London' ) : { N := COUNT ( SNO ) } EXTEND SP WHERE SNO = ‘S1’ : { SNO := ‘S7’ , QTY = 0.5 * QTY } 7.2 In what circumstances (if any) are r1 MATCHING r2 and r2 MATCHING r1 equivalent? 7.3 Show that