Databases Demystified- P1

Chia sẻ: Thanh Cong | Ngày: | Loại File: PDF | Số trang:50

Thêm vào BST

Báo xấu

86
lượt xem 6
download

Databases Demystified- P1

Mô tả tài liệu

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

Tôi nợ nhiều để cha mẹ tôi để cung cấp cho tôi với một nền giáo dục tuyệt vời và tình yêu một của cả hai học tập và giảng dạy. Tôi tín dụng Trường Boys'Latin của Maryland và cuối Jack H. Williams, hiệu trưởng, với việc giảng dạy cho tôi để viết một cách hiệu quả. Và tôi tín dụng Đại học Transylvania andDr. James E. Miller cho introducingme đến hấp dẫn thế giới của các hệ thống thông tin và providingmewith các công cụ cho việc học tập liên tục....

Chủ đề:

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

Lưu

Nội dung Text: Databases Demystified- P1

www.dbebooks.com - Free Books & magazines Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
DATABASES DEMYSTIFIED
This page intentionally left blank.
DATABASES DEMYSTIFIED ANDREW J. OPPEL McGraw-Hill/Osborne New York Chicago San Francisco Lisbon London Madrid Mexico City Milan New Delhi San Juan Seoul Singapore Sydney Toronto
Copyright © 2004 by The McGraw-Hill Companies. All rights reserved. Manufactured in the United States of America. 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. 0-07-146960-5 The material in this eBook also appears in the print version of this title: 0-07-225364-9. 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 corporate training programs. For more information, please contact George Hoare, Special Sales, at george_hoare@mcgraw-hill.com or (212) 904-4069. 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 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 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 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. DOI: 10.1036/0071469605
To everyone from whom I have learned so much about so many things, including the many teachers, students, and co-workers I have had the pleasure of knowing.
ABOUT THE AUTHOR Andrew J. (Andy) Oppel is a proud graduate of The Boys’ Latin School of Mary- land and of Transylvania University (Lexington, KY) where he earned a BA in com- puter 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, and data architect. In addition, he has been a part-time instructor with the University of California (Berkeley) Extension for over 20 years, and received the Honored Instructor Award for the year 2000. His teaching work has included developing two courses for UC Extension, “Concepts of Database Management Systems” and “Introduction to Relational Database Man- agement Systems.” He also earned his Oracle 9i Database Associate certification in 2003. He is currently employed as the principal data architect for Ceridian, a leading provider of human resource solutions. Aside from 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 manufactur- ing, telecommunications, wireless communications, and human resources. His da- tabase product experience includes IMS, DB2, Sybase, Microsoft SQL Server, Microsoft Access, MySQL, and Oracle (versions 7, 8, 8i, and 9i). Copyright © 2004 by The McGraw-Hill Companies. Click here for terms of use.
CONTENTS AT A GLANCE CHAPTER 1 Database Fundamentals 1 CHAPTER 2 Exploring Relational Database Components 25 CHAPTER 3 Forms-Based Database Queries 51 CHAPTER 4 Introduction to SQL 89 CHAPTER 5 The Database Life Cycle 129 CHAPTER 6 Logical Database Design Using Normalization 145 CHAPTER 7 Data and Process Modeling 179 CHAPTER 8 Physical Database Design 203 CHAPTER 9 Connecting Databases to the Outside World 227 CHAPTER 10 Database Security 247 CHAPTER 11 Database Implementation 273 CHAPTER 12 Databases for Online Analytical Processing 293 Final Exam 307 Answers to Quizzes and Final Exam 325 Index 329 vii
This page intentionally left blank.
For more information about this title, click here CONTENTS Acknowledgments xvii Introduction xix CHAPTER 1 Database Fundamentals 1 Properties of a Database 1 The Database Management System (DBMS) 2 Layers of Data Abstraction 3 Physical Data Independence 5 Logical Data Independence 6 Prevalent Database Models 7 Flat Files 7 The Hierarchical Model 9 The Network Model 11 The Relational Model 13 The Object-Oriented Model 15 The Object-Relational Model 16 A Brief History of Databases 17 Why Focus on Relational? 19 Quiz 20 CHAPTER 2 Exploring Relational Database Components 25 Conceptual Database Design Components 26 Entities 27 ix
Databases Demystified x Attributes 27 Relationships 28 Business Rules 32 Logical/Physical Database Design Components 33 Tables 33 Columns and Data Types 34 Constraints 37 Integrity Constraints 42 Views 45 Quiz 46 CHAPTER 3 Forms-Based Database Queries 51 QBE: The Roots of Forms-Based Queries 52 Getting Started in Microsoft Access 52 The Microsoft Access Relationships Panel 55 The Microsoft Access Table Design View 57 Creating Queries in Microsoft Access 59 Example 3-1: List All Customers 62 Example 3-2: Choosing Columns to Display 63 Example 3-3: Sorting Results 64 Example 3-4: Advanced Sorting 66 Example 3-5: Choosing Rows to Display 66 Example 3-6: Compound Row Selection 68 Example 3-7: Using Not Equal 70 Example 3-8: Joining Tables 70 Example 3-9: Limiting Join Results 72 Example 3-10: Outer Joins 75 Example 3-11: Multiple Joins; Calculated Columns 77 Example 3-12: Aggregate Functions 80 Example 3-13: Self-Joins 82 Quiz 85
CONTENTS xi CHAPTER 4 Introduction to SQL 89 The History of SQL 90 Getting Started with Oracle SQL 91 Where’s the Data? 96 Finding Database Objects Using Catalog Views 97 Viewing Database Objects Using Oracle Enterprise Manager 98 Data Query Language (DQL): The SELECT Statement 100 Example 4-1: Listing All Employees 100 Example 4-2: Limiting Columns to Display 100 Example 4-3: Sorting Results 102 Choosing Rows to Display 103 Joining Tables 108 Aggregate Functions 112 Data Manipulation Language (DML) 114 Transaction Support (COMMIT and ROLLBACK) 114 The INSERT Statement 115 The UPDATE Statement 116 The DELETE Statement 117 Data Definition Language (DDL) Statements 118 The CREATE TABLE Statement 118 The ALTER TABLE Statement 119 The CREATE VIEW Statement 121 The CREATE INDEX Statement 121 The DROP Statement 122 Data Control Language (DCL) Statements 122 The GRANT Statement 123 The REVOKE Statement 123 Quiz 124
Databases Demystified xii CHAPTER 5 The Database Life Cycle 129 The Traditional Method 130 Planning 130 Requirements Gathering 132 Conceptual Design 135 Logical Design 136 Physical Design 136 Construction 137 Implementation and Rollout 138 Ongoing Support 138 Nontraditional Methods 139 Prototyping 139 Rapid Application Development (RAD) 140 Quiz 141 CHAPTER 6 Logical Database Design Using Normalization 145 The Need for Normalization 147 Insert Anomaly 148 Delete Anomaly 148 Update Anomaly 148 Applying the Normalization Process 148 Choosing a Primary Key 151 First Normal Form: Eliminating Repeating Data 153 Second Normal Form: Eliminating Partial Dependencies 156 Third Normal Form: Eliminating Transitive Dependencies 158 Beyond Third Normal Form 160 Denormalization 163 Practice Problems 164 TLA University Academic Tracking 164
CONTENTS xiii Computer Books Company 170 Quiz 174 CHAPTER 7 Data and Process Modeling 179 Entity Relationship Modeling 180 ERD Formats 180 Super Types and Subtypes 184 Guidelines for Drawing ERDs 188 Process Models 189 The Flowchart 190 The Function Hierarchy Diagram 192 The Swim Lane Diagram 193 The Data Flow Diagram 194 Relating Entities and Processes 196 Quiz 198 CHAPTER 8 Physical Database Design 203 Designing Tables 204 Implementing Super Types and Subtypes 208 Naming Conventions 211 Integrating Business Rules and Data Integrity 214 NOT NULL Constraints 216 Primary Key Constraints 216 Referential (Foreign Key) Constraints 216 Unique Constraints 217 Check Constraints 218 Data Types, Precision, and Scale 218 Triggers 219 Designing Views 220 Adding Indexes for Performance 221 Quiz 222 CHAPTER 9 Connecting Databases to the Outside World 227 Deployment Models 228 Centralized Model 228
Databases Demystified xiv Distributed Model 229 Client/Server Model 231 Connecting Databases to the Web 235 Introduction to the Internet and the Web 236 Components of the Web “Technology Stack” 238 Invoking Transactions from Web Pages 239 Connecting Databases to Applications 240 Connecting Databases via ODBC 240 Connecting Databases to Java Applications 241 Quiz 242 CHAPTER 10 Database Security 247 Why Is Security Necessary? 247 Database Server Security 249 Physical Security 249 Network Security 250 System-Level Security 255 Database Client and Application Security 255 Login Credentials 256 Data Encryption 256 Other Client Considerations 257 Database Access Security 258 Database Security Architectures 259 Schema Owner Accounts 263 System Privileges 264 Object Privileges 265 Roles 265 Views 266 Security Monitoring and Auditing 267 Quiz 268 CHAPTER 11 Database Implementation 273 Cursor Processing 273
CONTENTS xv Transaction Management 276 What Is a Transaction? 276 DBMS Support for Transactions 276 Locking and Transaction Deadlock 278 Performance Tuning 283 Tuning Database Queries 284 Tuning DML Statements 286 Change Control 287 Quiz 288 CHAPTER 12 Databases for Online Analytical Processing 293 Data Warehouses 294 OLTP Systems Compared with Data Warehouse Systems 295 Data Warehouse Architecture 296 Data Marts 301 Data Mining 302 Quiz 303 Final Exam 307 Answers to Quizzes and Final Exam 325 Chapter 1 325 Chapter 2 325 Chapter 3 326 Chapter 4 326 Chapter 5 326 Chapter 6 326 Chapter 7 326 Chapter 8 327 Chapter 9 327 Chapter 10 327 Chapter 11 327 Chapter 12 327 Index 329
This page intentionally left blank.
ACKNOWLEDGMENTS I owe much to my parents for providing me with an excellent education and a love of both learning and teaching. I credit The Boys’ Latin School of Maryland and the late Jack H. Williams, headmaster, with teaching me to write effectively. And I credit Transylvania University and Dr. James E. Miller for introducing me to the fascinating world of information systems and providing me with the tools for continuous learning. I’d like to thank the wonderful people at McGraw-Hill/Osborne for the opportunity to write my first book and for their excellent support during the writing process. Finally, my thanks to my wife Laurie and our sons Keith and Luke for their support, patience, and understanding during the long hours it took to produce this book. xvii Copyright © 2004 by The McGraw-Hill Companies. Click here for terms of use.
This page intentionally left blank.
INTRODUCTION Thirty years ago, databases were found only in special research laboratories where computer scientists struggled with ways to make them efficient and useful, and pub- lished 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, web site searches, purchases, and most other transac- tions are recorded in and served by databases. As with many fast-growing technologies, industry standards have lagged behind the development of database technology, resulting in a myriad of commercial prod- ucts, 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 Demystified examines all of the major database models, in- cluding hierarchical, network, relational, object-oriented, and object-relational. However, Databases Demystified concentrates heavily upon the relational and ob- ject-relational models 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 designing the struc- ture of the database correctly. Without a thorough understanding of the problem the database is intended to solve, and without knowledge of the best practices for orga- nizing the required data, the implemented database becomes an unwieldy beast that requires constant attention. Databases Demystified focuses on transformation of re- quirements into a working database model with special emphasis on a process called normalization, which has proven to be an effective technique for designing rela- tional 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 xix Copyright © 2004 by The McGraw-Hill Companies. Click here for terms of use.