Beginning Database Design Solutions
Wrox Press (Verlag)
978-0-470-38549-4 (ISBN)
- Lieferbar
- Versandkostenfrei
- Auch auf Rechnung
- Artikel merken
This book is intended for IT professionals and students who want to learn how to design, analyze, and understand databases. The material will benefit those who want a better high-level understanding of databases such as proposal managers, architects, project managers, and even customers. The material will also benefit those who will actually design, build, and work with databases such as database designers, database administrators, and programmers. In many projects, these roles overlap so the same person may be responsible for working on the proposal, managing part of the project, and designing and creating the database. This book is aimed at IT professionals and students of all experience levels. It does not assume that you have any previous experience with databases or programs that use them. It doesn’t even assume that you have experience with computers. All you really need is a willingness and desire to learn.
This book explains database design. It tells how to plan a database’s structure so the database will be robust, resistant to errors, and flexible enough to accommodate a reasonable amount of future change. It explains how to discover database requirements, build data models to study data needs, and refine those models to improve the database’s effectiveness.
The book solidifies these concepts by working through a detailed example that designs a realistic database. Later chapters explain how to actually build databases using two common database products: Access 2007 and MySQL.
The book finishes by describing some of the topics you need to understand to keep a database running effectively such as database maintenance and security.
This book explains database design. It tells how to determine what should go in a database and how the database should be structured to give the best results.
To remain database neutral, the book does not assume you are using a particular database so you don’t need any particular software or hardware. To work through the Exercises, all you really need is a pencil and some paper. You are welcome to type solutions into your computer if you like but you may actually find working with pencil and paper easier than using a graphical design tool to draw pictures, at least until you are comfortable with database design and are ready to pick a computerized design tool.
Rod Stephens is a professional software developer who has built a wide variety of software and database applications in his 20-year career. He is the author of 18 books and more than 250 articles, and is a regular contributor to DevX.com (www.devx.com). Rod's clear, compelling writing makes it easy for less experienced readers to master new material. His current position as an adjunct instructor for ITT Technical institute has given him additional insight into the kinds of discussion, examples, and exercises that students find most helpful.
Introduction xxiii
Part I: Introduction to Databases and Database Design
Chapter 1: Goals of Effective Database Design 3
Understanding the Importance of Design 4
Information Containers 5
Strengths and Weaknesses of Information Containers 7
Desirable Database Features 8
CRUD 9
Retrieval 9
Consistency 10
Validity 10
Easy Error Correction 11
Speed 11
Atomic Transactions 12
ACID 13
Persistence and Backups 14
Low Cost and Extensibility 16
Ease of Use 16
Portability 16
Security 17
Sharing 18
Ability to Perform Complex Calculations 19
Consequences of Good and Bad Design 19
Summary 21
Exercises 22
Chapter 2: Database Types 23
Why Bother? 24
Flat Files 24
INI Files 26
Windows System Registry 26
Relational Databases 27
Spreadsheets 30
Hierarchical Databases 31
XML 34
XML Basics 34
XML Structures 37
XML Summary 39
Network 40
Object 42
Object-Relational 43
Exotic 44
Document-Oriented 44
Deductive 44
Dimensional 44
Temporal 45
Summary 45
Exercises 47
Chapter 3: Relational Database Fundamentals 49
Relational Points of View 49
Table, Rows, and Columns 50
Relations, Attributes, and Tuples 52
Keys 52
Indexes 54
Constraints 55
Basic Constraints 55
Check Constraints 55
Primary Key Constraints 55
Unique Constraints 56
Foreign Key Constraints 56
Database Operations 57
Summary 59
Exercises 59
Part II: Database Design Process and Techniques
Chapter 4: Understanding User Needs 65
Make a Plan 66
Bring a List of Questions 67
Functionality 67
Data Needs 67
Data Integrity 68
Security 68
Environment 69
Meet the Customers 69
Learn Who’s Who 70
Pick the Customers’ Brains 73
Walk a Mile in the User’s Shoes 73
Study Current Operations 74
Brainstorm 75
Look to the Future 76
Understand the Customers’ Reasoning 76
Learn What the Customers Really Need 77
Prioritize 78
Verify Your Understanding 79
Write the Requirements Document 80
Make Use Cases 81
Decide Feasibility 85
Summary 85
Exercises 86
Chapter 5: Translating User Needs into Data Models 89
What Are Data Models? 90
User Interface Models 92
Semantic Object Models 96
Classes and Objects 96
Cardinality 97
Identifiers 97
Putting It Together 97
Semantic Views 99
Class Types 100
Comments and Notes 106
Entity-Relationship Models 106
Entities, Attributes, and Identifiers 107
Relationships 107
Cardinality 108
Inheritance 109
Additional Conventions 112
Comments and Notes 113
Relational Models 113
Converting Semantic Object Models 113
Converting ER Diagrams 116
Summary 117
Exercises 118
Chapter 6: Extracting Business Rules 121
What Are Business Rules? 121
Identifying Key Business Rules 123
Extracting Key Business Rules 128
Multi-Tier Applications 129
Summary 133
Exercises 134
Chapter 7: Normalizing Data 137
What Is Normalization? 137
First Normal Form (1NF) 138
Second Normal Form (2NF) 146
Third Normal Form (3NF) 150
Stopping at Third Normal Form 154
Boyce-Codd Normal Form (BCNF) 154
Fourth Normal Form (4NF) 158
Fifth Normal Form (5NF) 162
Domain/Key Normal Form (DKNF) 165
Essential Redundancy 167
The Best Level of Normalization 168
Summary 169
Exercises 170
Chapter 8: Designing Databases to Support Software Applications 173
Plan Ahead 173
Document Everything 174
Consider Multi-Tier Architecture 175
Convert Domains into Tables 175
Keep Tables Focused 176
Use Three Kinds of Tables 176
Use Naming Conventions 178
Allow Some Redundant Data 179
Don’t Squeeze in Everything 180
Summary 181
Exercises 182
Chapter 9: Common Design Patterns 185
Associations 185
Many-to-Many Associations 186
Multiple Many-to-Many Associations 186
Multiple-Object Associations 188
Repeated Attribute Associations 191
Reflexive Associations 193
Temporal Data 200
Effective Dates 200
Deleted Objects 202
Deciding What to Temporalize 203
Logging and Locking 203
Audit Trails 203
Turnkey Records 204
Summary 205
Exercises 205
Chapter 10: Common Design Pitfalls 207
Lack of Preparation 207
Poor Documentation 208
Poor Naming Standards 208
Thinking Too Small 210
Not Planning for Change 210
Too Much Normalization 213
Insufficient Normalization 213
Insufficient Testing 214
Performance Anxiety 214
Mishmash Tables 215
Not Enforcing Constraints 217
Obsession with IDs 218
Not Defining Natural Keys 220
Summary 221
Exercises 222
Part III: A Detailed Case Study
Chapter 11: User Needs and Requirements 227
Meet the Customers 227
Pick the Customers’ Brains 229
Determining What the System Should Do 229
Determining How the Project Should Look 231
Determining What Data Is Needed for the User Interface 232
Determining Where the Data Should Come From 233
Determining How the Pieces of Data Are Related 233
Determining Performance Needs 235
Determining Security Needs 236
Determining Data Integrity Needs 236
Write Use Cases 238
Write the Requirements Document 242
Demand Feedback 243
Summary 244
Exercises 244
Chapter 12: Building a Data Model 245
Semantic Object Modeling 245
Building an Initial Semantic Object Model 245
Improving the Semantic Object Model 248
Entity-Relationship Modeling 250
Building an ER Diagram 250
Building a Combined ER Diagram 252
Improving the Entity-Relationship Diagram 254
Relational Modeling 256
Putting It All Together 259
Summary 260
Exercises 261
Chapter 13: Extracting Business Rules 263
Identifying Business Rules 263
Courses 264
CustomerCourses 266
Customers 267
Pets 267
Employees 267
Orders 267
OrderItems 268
InventoryItems 268
TimeEntries 268
Shifts 269
Persons 269
Phones 269
Vendors 269
Drawing a New Relational Model 270
Summary 270
Exercises 271
Chapter 14: Normalization and Refinement 273
Improving Flexibility 273
Verifying First Normal Form 275
Verifying Second Normal Form 278
Pets 279
TimeEntries 280
Verifying Third Normal Form 281
Summary 283
Exercises 284
Part IV: Implementing Databases (with Examples in Access and MySQL)
Chapter 15: Microsoft Access 287
Understanding Access 287
Getting Started 288
Defining Relationships 293
Creating Field Constraints 296
Creating Table Constraints 298
Creating Queries 301
Query Design View 304
SQL View 306
Summary 308
Exercises 308
Chapter 16: MySQL 313
Installing MySQL 313
Using MySQL Command Line Client 314
Executing SQL Scripts 318
Using MySQL Query Browser 319
Executing Queries 320
Editing Data 320
Creating and Modifying Databases 321
Using Scripts 327
Getting Syntax Help 329
Using MySQL Workbench 330
Loading Scripts 330
Creating EER Diagrams 331
Editing Databases 333
Defining Triggers 334
Exporting Scripts 336
Summary 338
Exercises 338
Part V: Advanced Topics
Chapter 17: Introduction to SQL 345
Background 345
Finding More Information 346
Standards 346
Basic Syntax 347
Command Overview 348
Create Table 350
Create Index 355
Drop 356
Insert 357
Select 358
Select Clause 358
From Clause 359
Where Clause 362
Group By Clause 362
Order By Clause 363
Update 364
Delete 365
Summary 366
Exercises 366
Chapter 18: Building Databases with SQL Scripts 369
Why Bother with Scripts? 369
Script Categories 370
Database Creation Scripts 370
Basic Initialization Scripts 370
Data Initialization Scripts 370
Cleanup Scripts 371
Saving Scripts 371
Ordering SQL Commands 371
Summary 376
Exercises 377
Chapter 19: Database Maintenance 379
Backups 379
Data Warehousing 382
Repairing the Database 383
Compacting the Database 383
Performance Tuning 383
Summary 386
Exercises 387
Chapter 20: Database Security 389
The Right Level of Security 389
Passwords 390
Single-Password Databases 390
Individual Passwords 390
Operating System Passwords 391
Good Passwords 391
Privileges 392
Initial Configuration and Privileges 398
Too Much Security 398
Physical Security 399
Summary 400
Exercises 401
Appendix A: Exercise Solutions 403
Chapter 1 403
Chapter 2 408
Chapter 3 411
Chapter 4 413
Chapter 5 417
Chapter 6 424
Chapter 7 430
Chapter 8 434
Chapter 9 436
Chapter 10 440
Chapter 11 442
Chapter 12 446
Chapter 13 449
Chapter 14 451
Chapter 15 454
Chapter 16 454
Chapter 17 454
Chapter 18 457
Chapter 19 459
Chapter 20 460
Appendix B: Sample Database Designs 467
Books 468
DVD and Movies 470
Music and CDs 471
Documents 472
Customer Orders 472
Employee Shifts and Timesheets 473
Employees, Projects, and Departments 473
Employee Skills and Qualifications 475
Identical Object Rental 476
Distinct Object Rental 476
Students, Courses, and Grades 478
Teams 479
Individual Sports 480
Vehicle Fleets 480
Contacts 483
Passengers 483
Recipes 485
Glossary 487
Index 497
| Erscheint lt. Verlag | 1.11.2008 |
|---|---|
| Sprache | englisch |
| Maße | 188 x 231 mm |
| Gewicht | 726 g |
| Einbandart | Paperback |
| Themenwelt | Mathematik / Informatik ► Informatik ► Datenbanken |
| ISBN-10 | 0-470-38549-9 / 0470385499 |
| ISBN-13 | 978-0-470-38549-4 / 9780470385494 |
| Zustand | Neuware |
| Haben Sie eine Frage zum Produkt? |
aus dem Bereich
