DeMYSTiFieD / Databases DeMYSTiFieD, Second Edition / Andy Oppel / 799-0 / Curriculum Guide Dear Student: Our name says it all: the goal of the DeMYSTiFieD series is to help you master confusing Database Management Systems Certifi cate Courses subjects, understand complex textbooks, and succeed in your studies. . . . . . . . . . . . . . . . . . . . . Beginning Level . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . How can DeMYSTiFieD help you? It’s a no-brainer! Introduction to Relational Database Programming Language Courses Introduction to SQL Management Systems (C, Perl, Java, etc.) • Study with the best—all DeMYSTiFieD authors are experts in their fields of study. . . . . . . . . . . . . . . . . . . . . Intermediate Level . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . • Learn by doing—all DeMYSTiFieD books are packed Integrating SQL with DBMS Product-Specifi c with examples and practice opportunities. Advanced Database Advanced SQL Query Programming Languages Web Database Courses (Oracle, MySQL, Concepts Writing Development (C, Perl, Java, VBA, etc.) SQL Server, DB2, Access, etc.) • Grasp the critical concepts right away with highlighted chapter objectives. . . . . . . . . . . . . . . . . . . . . Advanced Level . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . • Get un-stuck with help from the “Still Struggling?” Data Modeling and Designing Business Designing, Building, Systems Analysis Building Database feature. We all need a little help sometimes. Database Design Intelligence Systems and Using Databases and Design Systems • Grade your own progress with a “Final Exam”at the end of each book and avoid the red pencil of doom. Computer Science Degree Courses • Move easily from subject to subject with a “Curriculum Guide” . . . . . . . . . . . . . . . . . . . . Beginning Level . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . that gives a logical path. Introduction to Introduction to Introduction to Database Introduction to DssuuebbMjjeeYccSttTs—i,F abielegDce obismr ate,h ceco oslnleefirgideees Sn ytpo aiunn’ lyils othuu, rrb nku ntsooi nwaeglseasdi-ngs eca,hn aodno adl g aaaccicnho iteuovn eht ieynlogpu, yrs opguoe acuilnast.la iNzneogd lm en cuaotrtsneifnru gws i hnagt . . . . . . . . . . . . . . . . . . . . InDattae Prromcesesindgiate Le velP ro .g . r. a. m. . m. . i.n .g . . . . . . . . . . . . . . . . . . . D. .a .t .a . S. t. r. u. c. t. u. r. e. s. . . . . . . . . . . . . . . . . .M . .a .n . a. g. .e .m . .e .n .t . S. y. .s t. e. m. . s. . . . . . . . . . . . . D. a. .t a. . L. a. n. .g .u . a. g. .e .s . . . . . . . . . . . . . . . . . . . . . . . . courses, and everything in between—DeMYSTiFieD is true to its motto: Algorithm Design and Analysis Logic and Computation Computer Architecture Operating Systems Hard stuff made easy™ Computer Networks and Human-Computer Security and Privacy Advanced Query Design Software Testing Data Communications Interaction Design Issues and Solutions . . . . . . . . . . . . . . . . . . . . Advanced Level . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Data Modeling and Artifi cial Real-Time Computer Graphics Compiler Design Systems Analysis Database Design Intelligence Systems Computing and Multimedia 799_Curriculum.indd 1 10/6/10 5:41:21 PM Databases DeMYSTiFieD® DDeeMMYYSSTTiiFFiieeDD // DDaattaabbaasseess DDeeMMYYSSTTiiFFiieeDD,, SSeeccoonndd EEddiittiioonn // AAnnddyy OOppppeell // 779999--00 // FFrroonntt MMaatttteerr DeMYSTiFieD® Series Advanced Statistics Demystified Math Proofs Demystified Algebra Demystified, 2e Math Word Problems Demystified Alternative Energy Demystified Mathematica Demystified ASP.NET 2.0 Demystified Matlab Demystified Astronomy Demystified Microbiology Demystified Biology Demystified Microeconomics Demystified Biophysics Demystified Nanotechnology Demystified Biotechnology Demystified OOP Demystified Business Calculus Demystified Operating Systems Demystified Business Math Demystified Organic Chemistry Demystified Business Statistics Demystified Pharmacology Demystified Calculus Demystified, 2e Physics Demystified, 2e Chemistry Demystified Physiology Demystified College Algebra Demystified Pre-Algebra Demystified, 2e Data Structures Demystified Precalculus Demystified Databases Demystified, 2e Probability Demystified Differential Equations Demystified Project Management Demystified Digital Electronics Demystified Quality Management Demystified Earth Science Demystified Quantum Mechanics Demystified Electricity Demystified Relativity Demystified Electronics Demystified Robotics Demystified Environmental Science Demystified Signals and Systems Demystified Everyday Math Demystified SQL Demystified Forensics Demystified Statistical Process Control Demystified Genetics Demystified Statistics Demystified Geometry Demystified Technical Analysis Demystified HTML & XHTML Demystified Technical Math Demystified Java Demystified Trigonometry Demystified JavaScript Demystified UML Demystified Lean Six Sigma Demystified Visual Basic 2005 Demystified Linear Algebra Demystified Visual C# 2005 Demystified Logic Demystified Web Design Demystified Macroeconomics Demystified XML Demystified The Demystified Series publishes more than 125 titles in all areas of academic study. For a complete list of titles, please visit www.mhprofessional.com. DeMYSTiFieD / Databases DeMYSTiFieD, Second Edition / Andy Oppel / 799-0 / Front Matter Databases DeMYSTiFieD® Second Edition andy Oppel New York Chicago San Francisco Lisbon London Madrid Mexico City Milan New Delhi San Juan Seoul Singapore Sydney Toronto Copyright © 2011 by The McGraw-Hill Companies. All rights reserved. Except as permitted under the United States Copyright Act of 1976, no part of this publication may be reproduced or distributed in any form or by any means, or stored in a database or retrieval system, without the prior written permission of the publisher. ISBN: 978-0-07-174800-1 MHID: 0-07-174800-8 The material in this eBook also appears in the print version of this title: ISBN: 978-0-07-174799-8, MHID: 0-07-174799-0. All trademarks are trademarks of their respective owners. Rather than put a trademark symbol after every occurrence of a trademarked name, we use names in an editorial fashion only, and to the benefi t of the trademark owner, with no intention of infringement of the trademark. Where such designations appear in this book, they have been printed with initial caps. McGraw-Hill eBooks are available at special quantity discounts to use as premiums and sales promotions, or for use in corporate training programs. To contact a representative please e-mail us at [email protected]. Trademarks: McGraw-Hill, the McGraw-Hill Publishing logo, DeMYSTiFieD®, and related trade dress are trademarks or registered trademarks of The McGraw-Hill Companies and/or its affi liates in the United States and other countries and may not be used without written permission. All other trademarks are the property of their respective owners. The McGraw-Hill Companies is not associated with any product or vendor mentioned in this book. Information has been obtained by McGraw-Hill from sources believed to be reliable. However, because of the possibility of human or mechanical error by our sources, McGraw-Hill, or others, McGraw-Hill does not guarantee the accuracy, adequacy, or completeness of any information and is not responsible for any errors or omissions or the results obtained from the use of such information. TERMS OF USE This is a copyrighted work and The McGraw-Hill Companies, Inc. (“McGrawHill”) and its licensors reserve all rights in and to the work. Use of this work is subject to these terms. Except as permitted under the Copyright Act of 1976 and the right to store and retrieve one copy of the work, you may not decompile, disassemble, reverse engineer, reproduce, modify, create derivative works based upon, transmit, dis- tribute, disseminate, sell, publish or sublicense the work or any part of it without McGraw-Hill’s prior consent. You may use the work for your own noncommercial and personal use; any other use of the work is strictly prohibited. Your right to use the work may be terminated if you fail to comply with these terms. THE WORK IS PROVIDED “AS IS.” McGRAW-HILL AND ITS LICENSORS MAKE NO GUARANTEES OR WARRANTIES AS TO THE ACCURACY, ADEQUACY OR COMPLETENESS OF OR RESULTS TO BE OBTAINED FROM USING THE WORK, INCLUDING ANY INFORMATION THAT CAN BE ACCESSED THROUGH THE WORK VIA HYPERLINK OR OTHERWISE, AND EXPRESSLY DISCLAIM ANY WARRANTY, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO IMPLIED WAR- RANTIES OF MERCHANTABILITY OR FITNESS FOR A PARTICULAR PURPOSE. McGraw-Hill and its licensors do not warrant or guarantee that the functions contained in the work will meet your requirements or that its operation will be uninterrupted or error free. Neither McGraw-Hill nor its licensors shall be liable to you or anyone else for any inaccuracy, error or omission, regardless of cause, in the work or for any damages resulting therefrom. McGraw-Hill has no responsibility for the content of any information accessed through the work. Under no circumstances shall McGraw-Hill and/or its licensors be liable for any indirect, incidental, special, punitive, consequential or similar damages that result from the use of or inability to use the work, even if any of them has been advised of the possibility of such damages. This limitation of liability shall apply to any claim or cause whatsoever whether such claim or cause arises in contract, tort or otherwise. The DeMYSTiFieD series helps students master complex and difficult subjects. Each book is filled with chapter quizzes, final exams, and user friendly content. Whether you want to master Spanish or get an A in Chemistry, DeMYSTiFieD will untangle confusing subjects, and make the hard stuff understandable. PRE-ALGEBRA DeMYSTiFied, 2e ALGEBRA DeMYSTiFied, 2e Allan G. Bluman Rhonda Huettenmueller ISBN-13: 978-0-07-174252-8 • $20.00 ISBN-13: 978-0-07-174361-7 • $20.00 CALCULUS DeMYSTiFied, 2e PHYSICS DeMYSTiFied, 2e Steven G. Krantz Stan Gibilisco ISBN-13: 978-0-07-174363-1 • $20.00 ISBN-13: 978-0-07-174450-8 • $20.00 DDeeMMYYSSTTiiFFiieeDD // DDaattaabbaasseess DDeeMMYYSSTTiiFFiieeDD,, SSeeccoonndd EEddiittiioonn // AAnnddyy OOppppeell // 779999--00 // FFrroonntt MMaatttteerr DeMYSTiFieD / Databases DeMYSTiFieD, Second Edition / Andy Oppel / 799-0 / Front Matter To Laurie, Keith, and Luke About the Author Andrew J. (Andy) Oppel is a proud graduate of The Boys’ Latin School of Maryland and of Transylvania University (Lexington, Kentucky), where he earned a BA in computer science in 1974. Since then, he has been continuously employed in a wide variety of information technology positions, including programmer, programmer/analyst, systems architect, project manager, senior database administrator, database group manager, consultant, database designer, data modeler, and data architect. In addition, he has served as a part-time instructor with the University of California, Berkeley, Extension for more than 25 years and received the Honored Instructor Award for the year 2000. His teaching work included developing three courses for UC Berkeley Extension: “Concepts of Database Management Systems,” “Introduction to Relational Database Management Systems,” and “Data Modeling and Database Design.” He also earned his Oracle 9i Database Associate certification in 2003. He is currently employed as a lead data modeler for Blue Shield of California. In addition to computer systems, Andy enjoys music (guitar and vocals), amateur radio, and soccer (referee instructor, U.S. Soccer). Andy has designed and implemented hundreds of databases for a wide range of applications, including medical research, banking, insurance, apparel manu- facturing, telecommunications, wireless communications, and human resources. He is the author of SQL Demystified (McGraw-Hill Professional, 2005), Data- bases: A Beginner’s Guide (McGraw-Hill Professional, 2009), and Data Model- ing: A Beginner’s Guide (McGraw-Hill Professional, 2009) and is co-author of SQL: A Beginner’s Guide, Third Edition (McGraw-Hill Professional, 2008), and SQL: The Complete Reference, Third Edition (McGraw-Hill Professional, 2009). His database product experience includes IMS, DB2, Sybase ASE, Microsoft SQL Server, Microsoft Access, MySQL, and Oracle. If you have any comments, please contact Andy at [email protected]. About the Technical Editor Aaron Davenport is a principal and senior technical consultant at LCS Technologies Inc., a database and application consulting company based in Sacramento, California. Aaron has been working with RDBMS technologies for over 12 years, with a focus on Oracle and MySQL platforms. Prior to joining LCS, Aaron had tenures at IBM, Gap Inc., and Yahoo! DeMYSTiFieD / Databases DeMYSTiFieD, Second Edition / Andy Oppel / 799-0 / Front Matter DeMYSTiFieD / Databases DeMYSTiFieD, Second Edition / Andy Oppel / 799-0 / Front Matter Contents Acknowledgments xiii Introduction xv chapter 1 Database Fundamentals 1 Properties of a Database 2 The Database Management System (DbMS) 3 Layers of Data Abstraction 3 Physical Data Independence 6 Logical Data Independence 8 Prevalent Database Models 8 Flat Files 9 The Hierarchical Model 12 The Network Model 14 The Relational Model 16 The Object-Oriented Model 18 The Object-Relational Model 20 A brief History of Databases 20 Why Focus on Relational? 23 Summary 23 chapter 2 Exploring Relational Database Components 27 Conceptual Database Design Components 28 Entities 29 Attributes 29 Relationships 30 business Rules 37 Logical/Physical Database Design Components 37 Tables 38 Columns and Data Types 40 vii