Table Of Content

www.it-ebooks.info www.it-ebooks.info View Updating and Relational Theory Solving the View Update Problem C. J. Date www.it-ebooks.info View Updating and Relational Theory by C. J. Date Copyright © 2013 C. J. Date. All rights reserved. Printed in the United States of America. Published by O’Reilly Media, Inc., 1005 Gravenstein Highway North, Sebastopol, CA 95472 O’Reilly books may be purchased for educational, business, or sales promotional use. Online editions are also available for most titles (http://my.safaribooksonline.com). For more information, contact our corporate/institutional sales department: (800) 998-9938 or [email protected]. Printing History: January 2013: First Edition. Revision History: 2012-12-12 First release See http://oreilly.com/catalog/errata.csp?isbn=0636920028437 for release details. Nutshell Handbook, the Nutshell Handbook logo, and the O’Reilly logo are registered trademarks of O’Reilly Media, Inc. View Updating and Relational Theory and related trade dress are trademarks of O’Reilly Media, Inc. Many of the designations used by manufacturers and sellers to distinguish their products are claimed as trademarks. Where those designations appear in this book, and O’Reilly Media, Inc., was aware of a trademark claim, the designations have been printed in caps or initial caps. While every precaution has been taken in the preparation of this book, the publisher and author assume no responsibility for errors or omissions, or for damages resulting from the use of the information contained herein. ISBN: 978-1-449-35784-9 [LSI] www.it-ebooks.info Intension extension Edgar F. Codd Invented a notion We now know as views Now view and base relvar Exchangeability Got us all singing Those view update blues —Anon.: Where Bugs Go The duke of Ormond took a view yesterday of his troop, and ordered all that had bay or grey horses to change them for black. —earliest known example (1693) of view updating, quoted in the Oxford English Dictionary from “A Brief Historical Relation of State Affairs 1678–1714,” by Narcissus Luttrell (1857) A little learning is a dangerous thing; Drink deep, or taste not the Pierian spring: There shallow drafts intoxicate the brain, And drinking largely sobers us again. —Alexander Pope: An Essay on Criticism (1711) ─── ♦♦♦♦♦ ─── To my wife Lindy and my daughters Sarah and Jennie with all my love www.it-ebooks.info A b o u t t h e A u t h o r C. J. Date is an independent author, lecturer, researcher, and consultant, specializing in relational database technology. He is best known for his book An Introduction to Database Systems (8th edition, Addison-Wesley, 2004), which has sold well over 850,000 copies at the time of writing and is used by several hundred colleges and universities worldwide. He is also the author of numerous other books on database management, including most recently:  From Addison-Wesley: Databases, Types, and the Relational Model: The Third Manifesto (3rd edition, coauthored with Hugh Darwen, 2006)  From Trafford: Logic and Databases: The Roots of Relational Theory (2007)  From Apress: The Relational Database Dictionary, Extended Edition (2008)  From Trafford: Database Explorations: Essays on The Third Manifesto and Related Topics (coauthored with Hugh Darwen, 2010)  From Ventus: Go Faster! The TransRelationalTM Approach to DBMS Implementation (2002, 2011)  From O’Reilly: SQL and Relational Theory: How to Write Accurate SQL Code (2nd edition, 2012)  From O’Reilly: Database Design and Relational Theory: Normal Forms and All That Jazz (2012) Mr. Date was inducted into the Computing Industry Hall of Fame in 2004. He enjoys a reputation that is second to none for his ability to explain complex technical subjects in a clear and understandable fashion. www.it-ebooks.info C o n t e n t s Preface ix Foreword xv Chapter 1 A Motivating Example 1 The Principle of Interchangeability 3 Base tables only: constraints 5 Base tables only: compensatory actions 6 Views: constraints and compensatory actions 8 There’s no magic 9 Concluding remarks 10 Chapter 2 The Technical Context 11 Relations and relvars 12 Relational assignment 15 Integrity constraints 19 Relvar predicates 21 MATCHING, NOT MATCHING, and EXTEND 25 Databases and dbvars 28 Chapter 3 The View Concept: A Closer Look 31 Views are pseudovariables 33 Data independence 34 How not to do it 38 Constraints and predicates 41 Information equivalence 46 Concluding remarks 49 Chapter 4 Restriction Views 55 The motivating example revisited 55 More on compensatory actions 59 What about triggers? 64 What about explicit UPDATE operations? 66 www.it-ebooks.info vi Contents Suppliers and shipments 68 The motivating example continued 72 Putting it all together 74 The point at last 75 Overlapping restrictions 77 Concluding remarks 79 Chapter 5 Projection Views 81 Example 1: a nonloss decomposition 81 Example 1 continued: the projection relvars 88 Example 1 continued: views 89 Example 2: another nonloss decomposition 90 Example 3: a lossy decomposition 97 Concluding remarks 103 Chapter 6 Join Views I: One to One Joins 105 Example 1: information equivalence 106 Example 2: information hiding 108 Concluding remarks 116 Chapter 7 Join Views II: Many to Many Joins 119 Example 1: information equivalence 119 Projection views revisited 127 Example 2: information hiding 128 Concluding remarks 130 Chapter 8 Join Views III: One to Many Joins 131 Example 1: information equivalence 131 Example 2: information hiding 135 Concluding remarks 137 Chapter 9 Intersection Views 141 Example 1: explicit overlap 142 Example 2: implicit overlap 146 Concluding remarks 153 www.it-ebooks.info Contents vii Chapter 10 Union Views 155 Example 1: disjoint union 155 Example 2: explicit overlap 157 Example 3: implicit overlap 160 Concluding remarks 166 Chapter 11 Difference Views 169 Example 1: implicit overlap 169 Example 2: explicit overlap 176 Concluding remarks 179 Chapter 12 Group and Ungroup Views 181 The GROUP and UNGROUP operators 181 A GROUP / UNGROUP example 185 A SUMMARIZE example 188 Chapter 13 Extension and Summarization Views 193 An EXTEND example 193 Another SUMMARIZE example 197 Chapter 14 Updating through Expressions 201 Semantics not syntax (?) 201 Some well known tautologies 204 “Semantic transformations” 207 Information equivalence revisited 209 Concluding remarks 213 Chapter 15 Ambiguity Revisited 215 Predicates and constraints revisited 216 An intersection example 218 Union and difference examples 220 More on predicates 223 Concluding remarks 224 www.it-ebooks.info viii Contents Appendix A Some Remarks on Relational Assignment 227 Appendix B Relational Operators 233 Index 237 www.it-ebooks.info