McGraw-Hill - Databases A Beginner_s Guide (2009)01

Chia sẻ: Hoang Nhan | Ngày: | Loại File: PDF | Số trang:30

Thêm vào BST

Báo xấu

76
lượt xem 8
download

Download Vui lòng tải xuống để xem tài liệu đầy đủ

McGraw-Hill - Databases A Beginner_s Guide (2009)01

Chủ đề:

Bình luận(0) Đăng nhập để gửi bình luận!

Lưu

Nội dung Text: McGraw-Hill - Databases A Beginner_s Guide (2009)01

Databases: A Beginner’s Guide
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 20 years and received the Honored Instructor Award for the year 2000. His teaching work included developing three courses for UC 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 senior data modeler for Blue Shield of California. In addition to computer systems, Andy enjoys music (guitar and vocals), amateur radio (Pacific Division Vice Director, American Radio Relay League), 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 manufacturing, telecommunications, wireless communications, and human resources. He is the author of Databases Demystified (McGraw-Hill Professional, 2004) and SQL Demystified (McGraw-Hill Professional, 2005), and is co-author of SQL: A Beginner’s Guide (McGraw-Hill Professional, 2009). His database product experience includes IMS, DB2, Sybase ASE, Microsoft SQL Server, Microsoft Access, MySQL, and Oracle (versions 7, 8, 8i, 9i, and 10g). If you have any comments, please contact Andy at andy@andyoppel.com. About the Technical Editor Todd Meister has been developing using Microsoft technologies for more than ten years. He’s been a Technical Editor on more than 50 books with topics ranging from SQL Server to the .NET Framework. In addition to technical editing, he serves as an Assistant Director for Computing Services at Ball State University in Muncie, Indiana. He lives in central Indiana with his wife, Kimberly, and their four incredible children. Contact Todd at tmeister@sycamoresolutions.com.
Databases: A Beginner’s Guide Andrew J. Oppel New York Chicago San Francisco Lisbon London Madrid Mexico City Milan New Delhi San Juan Seoul Singapore Sydney Toronto
Copyright © 2009 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-160847-3 MHID: 0-07-160847-8 The material in this eBook also appears in the print version of this title: ISBN: 978-0-07-160846-6, MHID: 0-07-160846-X. 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 benefit 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 cor- porate training programs. To contact a representative please visit the Contact Us page at www.mhprofessional.com. 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. (“McGraw-Hill”) 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, distribute, 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 WAR- RANTIES 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, INCLUD- ING BUT NOT LIMITED TO IMPLIED WARRANTIES 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 there- from. McGraw-Hill has no responsibility for the content of any information accessed through the work. Under no circum- stances 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 con- tract, tort or otherwise.
Contents ACKNOWLEDGMENTS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xi INTRODUCTION . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xiii PART I Database Concepts 1 Database Fundamentals . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3 Properties of a Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4 The Database Management System . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5 Layers of Data Abstraction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6 Physical Data Independence . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8 Logical Data Independence . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10 Prevalent Database Models . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10 Flat Files . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10 The Hierarchical Model . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13 The Network Model . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15 The Relational Model . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17 The Object-Oriented Model . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19 The Object-Relational Model . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21 A Brief History of Databases . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22 Why Focus on Relational? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24 2 Exploring Relational Database Components . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29 Conceptual Database Design Components . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 30 Entities . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 30 v
vi Databases: A Beginner’s Guide Attributes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 32 Relationships . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 32 Business Rules . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 38 Try This 2-1: Exploring the Northwind Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 38 Logical/Physical Database Design Components . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 42 Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 42 Columns and Data Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 44 Constraints . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 46 Integrity Constraints . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 53 Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 56 3 Forms-based Database Queries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 63 QBE: The Roots of Forms-based Queries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 64 Getting Started in Microsoft Access . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 65 The Microsoft Access Relationships Panel . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 73 The Microsoft Access Table Design View . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 75 Creating Queries in Microsoft Access . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 77 Try This 3-1: List All Customers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 81 Try This 3-2: Choose Columns to Display . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 82 Try This 3-3: Sorting Results . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 84 Try This 3-4: Advanced Sorting . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 85 Try This 3-5: Choosing Rows to Display . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 88 Try This 3-6: Compound Row Selection . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 90 Try This 3-7: Using Not Equal . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 91 Try This 3-8: Joining Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 94 Try This 3-9: Limiting Join Results . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 97 Try This 3-10: Outer Joins . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 98 Try This 3-11: Microsoft Access SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 101 Try This 3-12: Multiple Joins and Calculated Columns . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 103 Try This 3-13: Aggregate Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 106 Try This 3-14: Self-Joins . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 109 4 Introduction to SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 117 A Brief History of SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 120 Getting Started with Oracle SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 121 Try This 4-1: Unlock the HR Account and Log in as HR . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 122 Where’s the Data? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 127 Finding Database Objects Using Catalog Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 127 Try This 4-2 Using the Application Express Object Browser . . . . . . . . . . . . . . . . . . . . . . . . . 130 Viewing Database Objects Using the Object Browser . . . . . . . . . . . . . . . . . . . . . . . . . . 130 Data Query Language (DQL): The SELECT Statement . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 131 Listing All Rows and Columns . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 132 Limiting Columns to Display . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 133 Sorting Results . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 134 Choosing Rows to Display . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 136
Contents vii Joining Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 143 Aggregate Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 150 Data Manipulation Language (DML) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 154 Transaction Support (COMMIT and ROLLBACK) . . . . . . . . . . . . . . . . . . . . . . . . . . . . 154 The INSERT Statement . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 155 The UPDATE Statement . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 157 The DELETE Statement . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 158 Data Definition Language (DDL) Statements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 159 The CREATE TABLE Statement . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 160 The ALTER TABLE Statement . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 161 The CREATE VIEW Statement . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 162 The CREATE INDEX Statement . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 163 The DROP Statement . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 163 Data Control Language (DCL) Statements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 164 The GRANT Statement . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 164 The REVOKE Statement . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 165 Database Development PART II 5 The Database Life Cycle . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 171 The Traditional Life Cycle . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 172 Planning . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 174 Requirements Gathering . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 175 Conceptual Design . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 178 Logical Design . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 178 Physical Design . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 179 Construction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 179 Implementation and Rollout . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 180 Ongoing Support . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 181 Nontraditional Life Cycles . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 182 Prototyping . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 182 Rapid Application Development . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 183 The Project Triangle . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 183 Try This 5-1: Project Database Management Tasks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 184 6 Database Design Using Normalization . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 189 The Need for Normalization . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 192 Insert Anomaly . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 192 Delete Anomaly . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 193 Update Anomaly . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 193 Applying the Normalization Process . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 193 Choosing a Primary Key . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 196 First Normal Form: Eliminating Repeating Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 198 Second Normal Form: Eliminating Partial Dependencies . . . . . . . . . . . . . . . . . . . . . . 200 Third Normal Form: Eliminating Transitive Dependencies . . . . . . . . . . . . . . . . . . . . . 203 Beyond Third Normal Form . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 205
viii Databases: A Beginner’s Guide Denormalization . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 209 Try This 6-1: UTLA Academic Tracking . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 210 Practice Problems . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 210 Try This 6-2: Computer Books Company . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 214 7 Data and Process Modeling . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 221 Entity Relationship Modeling . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 222 ERD Formats . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 222 Super Types and Subtypes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 230 Guidelines for Drawing ERDs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 235 Process Models . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 236 The Flowchart . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 236 The Function Hierarchy Diagram . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 239 The Swim Lane Diagram . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 240 The Data Flow Diagram . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 240 Relating Entities and Processes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 245 Try This 7-1: Draw an ERD in Information Engineering (IE) Format . . . . . . . . . . . . . . . . . 246 8 Physical Database Design . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 253 Designing Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 254 Implementing Super Types and Subtypes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 259 Naming Conventions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 262 Integrating Business Rules and Data Integrity . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 265 NOT NULL Constraints . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 267 Primary Key Constraints . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 267 Referential (Foreign Key) Constraints . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 268 Unique Constraints . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 269 Check Constraints . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 270 Data Types, Precision, and Scale . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 270 Triggers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 270 Designing Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 271 Adding Indexes for Performance . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 272 Try This 8-1: Mapping a Logical Model to a Physical Database Design . . . . . . . . . . . . . . 274 PART III Database Implementation 9 Connecting Databases to the Outside World . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 281 Deployment Models . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 282 Centralized Model . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 282 Distributed Model . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 284 Client/Server Model . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 285 Connecting Databases to the Web . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 290 Introduction to the Internet and the Web . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 290 Components of the Web “Technology Stack” . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 293 Invoking Transactions from Web Pages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 293
Contents ix Connecting Databases to Applications . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 295 Connecting Databases via ODBC . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 295 Connecting Databases via OLE DB . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 296 Connecting Databases to Java Applications . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 296 Try This 9-1: Exploring the World Wide Web . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 297 10 Database Security . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 303 Why Is Security Necessary? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 304 Database Server Security . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 305 Physical Security . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 305 Network Security . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 306 System-Level Security . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 310 Database Client and Application Security . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 311 Login Credentials . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 311 Data Encryption . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 312 Other Client Considerations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 313 Database Access Security . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 314 Database Security Architectures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 315 Schema Owner Accounts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 319 System Privileges . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 320 Object Privileges . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 320 Roles . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 321 Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 321 Security Monitoring and Auditing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 322 Try This 10-1: Database Object Privileges . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 323 11 Deploying Databases . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 329 Cursor Processing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 330 Transaction Management . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 332 What Is a Transaction? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 332 DBMS Support for Transactions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 333 Try This 11-1: SQL Transaction Support . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 335 Locking and Transaction Deadlock . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 337 Performance Tuning . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 342 Tuning Database Queries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 342 Tuning DML Statements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 345 Change Control . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 346 12 Databases for Online Analytical Processing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 353 Data Warehouses . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 355 OLTP Systems Compared with Data Warehouse Systems . . . . . . . . . . . . . . . . . . . . . . 356 Data Warehouse Architecture . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 356 Data Marts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 363 Data Mining . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 364 Try This 12-1: Design Star Schema Fact and Dimension Tables . . . . . . . . . . . . . . . . . . . . . . 365
x Databases: A Beginner’s Guide 13 Integrating XML Documents and Objects into Databases . . . . . . . . . . . . . . . . 371 Learn the Basics of XML . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 372 Learn About SQL/XML . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 376 The XML Data Type . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 376 SQL/XML Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 378 SQL/XML Mapping Rule . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 380 Try This 13-1: Using SQL/XML Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 383 Object-Oriented Applications . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 385 Object-Oriented Programming . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 386 Object-Oriented Languages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 386 Object Persistence . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 387 Object-Relational Databases . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 392 PART IV Appendices A Answers to Self Tests . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 401 Chapter 1: Database Fundamentals . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 402 Chapter 2: Exploring Relational Database Components . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 404 Chapter 3: Forms-based Database Queries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 407 Chapter 4: Introduction to SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 410 Chapter 5: The Database Life Cycle . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 413 Chapter 6: Database Design Using Normalization . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 416 Chapter 7: Data and Process Modeling . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 419 Chapter 8: Physical Database Design . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 423 Chapter 9: Connecting Databases to the Outside World . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 426 Chapter 10: Database Security . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 430 Chapter 11: Deploying Databases . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 433 Chapter 12: Databases for Online Analytical Processing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 438 Chapter 13: Integrating XML Documents and Objects into Databases . . . . . . . . . . . . . . . . 441 B Solutions to the Try This Exercises . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 447 Try This 5-1 Solution: Project Database Management Tasks . . . . . . . . . . . . . . . . . . . . . . . . . 448 Try This 6-1 Solution: UTLA Academic Tracking . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 449 Try This 6-2 Solution: Computer Books Company . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 452 Try This 7-1 Solution: Draw an ERD in Information Engineering (IE) Format . . . . . . . 454 Try This 8-1 Solution: Mapping a Logical Model to a Physical Database Design . . . . . 455 Try This 10-1 Solution: Database Object Privileges . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 455 Try This 11-1 Solution: SQL Transaction Support . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 456 Try This 12-1 Solution: Design Star Schema Fact and Dimension Tables . . . . . . . . . . . . . 456 Try This 13-1 Solution: Using SQL/XML Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 457 Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 459
Acknowledgments M any people were involved in the development of Databases: A Beginner’s Guide— many of whom I do not know by name. First, the editors and staff at McGraw-Hill provided untold hours of support for this project. I wish to especially thank Editorial Director Wendy Rinaldi as the individual who has provided the most advice and inspiration throughout the development of all my books. In fact, it was Wendy who got me started as a McGraw-Hill author. I also wish to thank Lisa Theobald for her excellent copy editing and all the other editors, proofreaders, indexers, designers, illustrators, and other participants. My special thanks go to Todd Meister, the technical editor, for his attention to detail and his helpful inputs throughout the editing process. Finally, my thanks to my family for their support and understanding as I fit the writing schedule into an already overly busy life. xi
This page intentionally left blank
Introduction T hirty-five years ago, databases were found only in special research laboratories, where computer scientists struggled with ways to make them efficient and useful, publishing their findings in countless research papers. Today databases are a ubiquitous part of the information technology (IT) industry and business in general. We directly and indirectly use databases every day—banking transactions, travel reservations, employment relationships, website searches, online and offline purchases, and most other transactions are recorded in and served by databases. As is the case with many fast-growing technologies, industry standards have lagged behind in the development of database technology, resulting in myriad commercial products, each following a particular software vendor’s vision. Moreover, a number of different database models have emerged, with the relational model being the most prevalent. Databases: A Beginner’s Guide examines all of the major database models, including hierarchical, network, relational, object-oriented, and object-relational. This book concentrates heavily on the relational and object-relational models, however, because these are the mainstream of the IT industry and will likely remain so in the foreseeable future. The most significant challenge in implementing a database is correctly designing the structure of the database. Without a thorough understanding of the problem the database is intended to solve, and without knowledge of the best practices for organizing the required data, the implemented database becomes an unwieldy beast that requires constant attention. xiii
xiv Databases: A Beginner’s Guide Databases: A Beginner’s Guide focuses on the transformation of requirements into a working data model with special emphasis on a process called normalization, which has proven to be an effective technique for designing relational databases. In fact, normalization can be applied successfully to other database models. And, in keeping with the notion that you cannot design an automobile if you have never driven one, the Structured Query Language (SQL) is introduced so that the reader may “drive” a database before delving into the details of designing one. I’ve drawn on my extensive experience as a database designer, administrator, and instructor to provide you with this self-help guide to the fascinating and complex world of database technology. Examples are included using both Microsoft Access and Oracle. Publicly available sample databases supplied by these vendors (the Microsoft Access Northwind database and the Oracle Human Resources database schema) are used in example figures whenever possible so that you can try the examples directly on your own computer system. A self test is provided at the end of each chapter to help reinforce your learning. Who Should Read This Book Databases: A Beginner’s Guide is recommended for anyone trying to build a foundation in database design and management, whether for personal or professional use. The book is designed specifically for those who are new or relatively new to database technology; however, those of you who need a refresher in normalization and database design and management will also find this book beneficial. Whether you’re an experienced developer, you’ve had some development experience, you’re a database administrator, or you’re new to programming and databases, Databases: A Beginner’s Guide provides a strong foundation that will be useful to any of you wanting to learn more about database technology. In fact, any of the following individuals will find this book helpful when trying to understand and use databases: ● The novice new to database design and SQL programming ● The analyst or manager who wants a better understanding of how to design, implement, and access databases ● The database administrator who wants to learn more about database design ● The technical support professional or testing/QA engineer who must perform ad hoc queries against SQL databases ● The web developer writing applications that require databases for data persistence
Introduction xv ● The third-generation language (3GL) programmer embedding SQL within an application’s source code ● Any other individual who wants to learn how to design databases and write SQL code to create and access databases within an RDBMS No matter which category you fit into, you must remember that the book is geared toward anyone wanting to learn standard database design techniques that work on any database, not one specific vendor’s product. This lets you apply the skills you learn in this book to real-world situations, without being limited to product standards. You will, of course, still need to be aware of how the product you work on implements databases, particularly dialects of SQL, but with the foundation provided in these pages, you’ll be able to move from one RDBMS to the next and still have a solid understanding of database design theory. As a result, you’ll find that this book is a useful tool to anyone new to databases, particularly relational databases, regardless of the product used. You will easily be able to adapt your knowledge to the specific RDBMS. What the Book Covers Databases: A Beginner’s Guide is divided into three parts. Part I introduces you to basic database concepts and explains how to create and access objects within your database using SQL. Part II provides you with a foundation in database development, including the database life cycle, logical design using the normalization process, transforming the logical design into a physical database, and data and process modeling. Part III focuses on database implementation with emphasis on database security, as well as the advanced topics of databases for online analytical processing (OLAP) and integrating objects and XML documents into the database, allowing you to expand on what you learned in Parts I and II. In addition to the three parts, Databases: A Beginner’s Guide contains appendices that include answers to the self-test questions and solutions to the Try This exercises that appear throughout the book. Content Description The following outline describes the contents of the book and shows how the book is broken down into task-focused chapters: Part I: Database Concepts Part I introduces you to basic database concepts and explains how to create and access objects within your database using SQL. Chapter 1: Database Fundamentals This chapter introduces fundamental concepts and definitions regarding databases, including properties common to databases, prevalent
xvi Databases: A Beginner’s Guide database models, a brief history of databases, and the rationale for focusing on the relational model. Chapter 2: Exploring Relational Database Components This chapter explores the conceptual, logical, and physical components that make up the relational model. Conceptual database design involves studying and modeling the data in a technology- independent manner. Logical database design is the process of translating, or mapping, the conceptual design into a logical design that fits the chosen database model (relational, object-oriented, object-relational, and so on). The final design step is physical database design, which involves mapping the logical design to one or more physical designs—each tailored to the particular DBMS that will manage the database and the particular computer system on which the database will run. Chapter 3: Forms-based Database Queries This chapter provides an overview of forming and running database queries using the forms-based query tool in Microsoft Access, providing a foundation in database query concepts for the database design theory that follows in later chapters. Chapter 4: Introduction to SQL This chapter introduces SQL, which has become the universal language for relational databases that nearly every DBMS in modern use supports. The reason for its wide acceptance is clearly the time and effort that went into the development of language features and standards, making SQL highly portable across different RDBMS products. Part II: Database Development Part II provides you with a foundation in database development, including the database life cycle, logical design using the normalization process, transforming the logical design into a physical database, and data and process modeling. Chapter 5: The Database Life Cycle This chapter introduces the framework in which database design takes place, a useful precursor to the particulars of database design. The life cycle of a database (or computer system) is the term we use for all the events that take place between the time we first recognize the need for a database, continuing through its development and deployment, and finally ending with the day it is retired from service. Chapter 6: Database Design Using Normalization In this chapter, you will learn how to perform logical database design using a process called normalization. In terms of understanding relational database technology, this is the most important topic in this book, because normalization teaches you how best to organize your data into tables. Chapter 7: Data and Process Modeling In this chapter, we will look at entity- relationship diagrams (ERDs) and data modeling in more detail. The second part of the chapter includes a high-level survey of process design concepts and diagramming techniques.
Introduction xvii Chapter 8: Physical Database Design This chapter focuses on the database designer’s physical design work, which is transforming the logical database design into one or more physical database designs. Part III: Database Implementation Part III focuses on database implementation with emphasis on database security as well as the advanced topics of databases for online analytical processing (OLAP) and integrating objects and Extensible Markup Language (XML) documents into the database; this allows you to expand on what you learned in Parts I and II. Chapter 9: Connecting Databases to the Outside World This chapter begins with a look at the evolution of database deployment models, meaning the ways that databases have been connected with the database users and the other computer systems within the enterprise computing infrastructure (the internal structure that organizes all the computing resources of an enterprise, including databases, applications, computer hardware, and the network). The chapter then explores the methods used to connect databases to applications that use a web browser as the primary user interface, which is the way many modern application systems are constructed. It concludes with a look at current methods for connecting databases to applications, namely using ODBC connections (for most programming languages) and various methods for connecting databases to applications written in Java (a commonly used object-oriented language). Chapter 10: Database Security This chapter presents the need for security, the security considerations for deploying database servers and clients that access those servers, and methods for implementing database access security, concluding with a discussion of security monitoring and auditing. Chapter 11: Deploying Databases This chapter covers some considerations regarding the development of applications that use the database system. These include cursor processing, transaction management, performance tuning, and change control. Chapter 12: Databases for Online Analytical Processing This chapter presents the concepts of databases for analytical processing, including data warehouses and data marts, an overview of data mining and other data analysis techniques, along with the design variations required for these types of databases. Chapter 13: Integrating XML Documents and Objects into Databases This chapter explores a number of ways to integrate XML and object content into databases. Part IV: Appendices The appendices include answers to the Self Test questions and solutions to the Try This exercises that appear throughout the book.
xviii Databases: A Beginner’s Guide Appendix A: Answers to Self Tests This appendix provides the answers to the Self Test questions listed at the end of each chapter. Appendix B: Solutions to the Try This Exercises This appendix contains solutions, including diagrams and applicable SQL code, for the Try This exercises that appear in nearly every chapter of the book. Chapter Content As you can see from the outline, Databases: A Beginner’s Guide is organized into chapters. Each chapter focuses on a set of key skills and concepts and contains the background information you need to understand the concepts, plus the skills required to apply these concepts. Each chapter contains additional elements to help you better understand the information covered in that chapter: Ask the Expert Each chapter contains one or two Ask the Expert sections that provide information on questions that might arise regarding the information presented in the chapter. Self Test Each chapter ends with a Self Test, a set of questions that test you on the information and skills you learned in that chapter. The answers to the Self Tests are included in Appendix A. Try This Exercises Most chapters contain one or two Try This exercises that allow you to apply the information that you learned in the chapter. Each exercise is broken down into steps that walk you through the process of completing a particular task. Where applicable, the exercises include related files that you can download from our website at www.mhprofessional.com. Click Computing and then click the Downloads Section link on the left side of the page. On the downloads page, scroll down to the listing for this book and select the files you wish to download. The files usually include the SQL statements or diagrams used within the Try This exercise. To complete many of the Try This exercises in this book, you’ll need to have access to an RDBMS that allows you to enter and execute SQL statements interactively. If you’re accessing an RDBMS over a network, check with the database administrator to make sure that you’re logging in with the credentials necessary to create a database and schema. You might need special permissions to create these objects. Also verify whether you should include any particular parameters when creating the database (for example, log file size), and whether restrictions on the names you can use or other restrictions apply. Be sure to check the appropriate documentation before working with any database product.
Part I Database Concepts