[Database.Design.and.Relational.Theory(2012.4)].C.J.Date.文字版.pdfDatabase designand Relational TheoryNormal forms andAll That JazzC.. DateDatabase Design and Relational Theoryby C. J DaCopyright C 2012 C.J. Date. All rights reservedPrinted in the united states of americaPublished by o'Reilly media, Inc1005 Gravenstein Highway North, Sebastopol, CA 95472O Reilly books may be purchased for educational, business, or sales promotional use Online editions are alsoavailableformosttitles(http:/iMy.safaribooksonline.com).Formoreinformationcontactourcorporate/institutionalsalesdepartment:(800)998-9938orcorporate(@oreilly.comPrinting history:April 2012:First editionRevision history:2012-04-09 First releaseSeehttp:/oreilly.com/catalog/errata.cspisbn=0636920025276forreleasedetailsNutshell Handbook, the Nutshell Handbook logo, and the O'Reilly logo are registered trademarks ofO'Reilly Media, Inc. Database Design and Relational Theory: Normal Forms and All That jazz and related tradedress are trademarks of o'reilly media, IncMany of the designations used by manufacturers and sellers to distinguish their products are claimed astrademarks. Where those designations appear in this book, and O'Reilly media, Inc, was aware of atrademark claim, the designations have been printed in caps or initial capsWhile every precaution has been taken in the preparation of this book, the publisher and authors assumeno responsibility for errors or omissions, or for damages resulting from the use of the information containedhereinISBN:978-1-449-32801-6LS门In computing elegance is not a dispensable luxurybut a quality that decides between success and failure- edsger w. DijkstrThe ill design is most ill for the designer-HesiodIt is to be noted that when any part of this paper is dullthere is design in it-Sir richard SteeleThe idea of a formal design discipline is often rejected on account ofvague cultural /philosophical condemnations such as"stifling creativitythis is more pronounced. where a romantic vision of the humanitiesin fact idealizes technical incompetencelWe/ know that for the sake of reliability and intellectual controlwe have to keep the design simple and disentangledEdsger W. DijkstrMy designs are strictly honorable.一A1◆◆◆◆◆To my wife lindyand my daughters sarah and Jenniewith all my loveabout the authorC. J. Date is an independent author, lecturer, researcher, and consultant, specializing in relational databasetechnology. He is best known for his book An Introduction to Database Systems( &th edition, Addison-Wesley,2004), which has sold some 850,000 copies at the time of writing and is used by several hundred colleges anduniversities worldwide. He is also the author of many other books on database management, including mostrecentlFrom 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 withHugh Darwen, 2010)From Ventus: Go Faster The TransRelational Approach to DBMS Implementation(2011)I From O'Reilly: SOL and Relational Theory: How to Write Accurate SOL Code(2nd edition, 2012)Mr Date was inducted into the Computing Industry Hall of Fame in 2004. He enjoys a reputation that issecond to none for his ability to explain complex technical subjects in a clear and understandable fashionContentsPreface xiPART ISETTING THE SCENE 1Chapter 1Preliminaries 3Some quotes from the literature 3A note on terminology 5The running example 6eysThe place of design theory 8Aims of this book 11Concluding remarks 1212Chapter 2Prerequisites 1515Relations and relvars 16Predicates and propositions 18More on suppliers and parts 2022PART IIFUNCTIONAL DEPENDENCIES. BOY CE/CODD NORMAL FORMAND RELATED MATTERS 25Chapter 3Normalization Some generalities 27Normalization serves two purposes 29Update anomalies 31The normal form hierarchy 32Normalization and constraints 34Concluding remarks 35Exercises 36Chapter 4FDs and bCnF (Informal 37First normal form 37Functional dependency40Keys revisited 42Second normal form 43Third normal formBoyce/Codd normal form 45Exercises 47ContentsChapter 5FDs and bcnF (formPreliminary definitions 49Functional dependenceBoyce/Codd normal form 52eaths IheoremExercises 56Chapter 6Preserving Fds 59An unfortunate conflict 60ole 63And another 64And still another 66A procedure that works 67Identity decompoMore on the conflict 72Independent projections 73Exercises 74Chapter 7FD Axiomatization 75Armstrongs axioms 75Additional rules 76Proving the additional rules 78Another kind of clo79Exercises 80Chapter 8Denormalization""Denormalize for performance"? 83What does deWhat denormalization isnt() 86What denormalization isnt(In 88Denormalization considered harmful () 90Denormalization considered harmful (i 91a final remark 92Exercises 92Contents viiPART IIIJOIN DEPENDENCIES. FIFTH NORMAL FORMAND RELATED MATTERS 95Chapter gJDs and 5nF (formal) 97Join dependencies-the basic idea 98a relvar in bCnf and not 5NF 100Cyclic rules 103Concluding remarks 104Exercises 105Chapter 10JDs and 5nF (Formal 107doin dep107Fifth normal form 109JDs implied by keys 110a useful theorem 113FDs arent JDs 114Update anomalies revisited 114Exercises 116Chapter 11Implicit dependencies 117Irrelevant components 117Combining components 118Irreducible ds 119Summary so far 121The chase algorithm 123Concluding remarks 127Exercises 127Chapter 12MVDs and 4NF 129An introductory example 129Multivalued dependencies(informal) 131Multivalued dependencies(formal) 132Fourth normal form 133Axiomatization 134Embedded dependencies 135Exercises 136viii ContentsChapter 13Additional normal Forms 139Equality dependencies 139Sixth normal form 141Superkey normal form 143Redundancy free normal form 144Domain-key normal form 149Concluding remarks 150Exercises 152PART IVORTHOGONALITY 155Chapter 14The Principle of orthogonal Design 157Two cheers for normalization 157a motivatile159A simpler example 160Tuples vs propositioitions 163The first example revisited 166The second example revisited 168The finala clarification 168Concluding remarks 170Es171PARTⅤREDUNDANCY 173Chapter 15We Need more science 175A little history 177Database design is predicate d178Example 1 180Example 2 181Example 3 181Example 4 181Example 5 182.XampleExample 7 185Example 8 187Example 9 188Example 10 189Example 11 190Example 12 190