Details

Beginning Database Design Solutions


Beginning Database Design Solutions


1. Aufl.

von: Rod Stephens

28,99 €

Verlag: Wiley
Format: PDF
Veröffentl.: 03.04.2009
ISBN/EAN: 9780470440513
Sprache: englisch
Anzahl Seiten: 560

DRM-geschütztes eBook, Sie benötigen z.B. Adobe Digital Editions und eine Adobe ID zum Lesen.

Beschreibungen

The vast majority of software applications use relational databases that virtually every application developer must work with. <i>This book</i> introduces you to database design, whether you're a DBA or database developer. You'll discover what databases are, their goals, and why proper design is necessary to achieve those goals. Additionally, you'll master how to structure the database so it gives good performance while minimizing the chance for error. You will learn how to decide what should be in a database to meet the application's requirements.
<p>Introduction xxiii</p> <p><b>Part I: Introduction to Databases and Database Design</b></p> <p><b>Chapter 1: Goals of Effective Database Design 3</b></p> <p>Understanding the Importance of Design 4</p> <p>Information Containers 5</p> <p>Strengths and Weaknesses of Information Containers 7</p> <p>Desirable Database Features 8</p> <p>CRUD 9</p> <p>Retrieval 9</p> <p>Consistency 10</p> <p>Validity 10</p> <p>Easy Error Correction 11</p> <p>Speed 11</p> <p>Atomic Transactions 12</p> <p>ACID 13</p> <p>Persistence and Backups 14</p> <p>Low Cost and Extensibility 16</p> <p>Ease of Use 16</p> <p>Portability 16</p> <p>Security 17</p> <p>Sharing 18</p> <p>Ability to Perform Complex Calculations 19</p> <p>Consequences of Good and Bad Design 19</p> <p>Summary 21</p> <p>Exercises 22</p> <p><b>Chapter 2: Database Types 23</b></p> <p>Why Bother? 24</p> <p>Flat Files 24</p> <p>INI Files 26</p> <p>Windows System Registry 26</p> <p>Relational Databases 27</p> <p>Spreadsheets 30</p> <p>Hierarchical Databases 31</p> <p>XML 34</p> <p>XML Basics 34</p> <p>XML Structures 37</p> <p>XML Summary 39</p> <p>Network 40</p> <p>Object 42</p> <p>Object-Relational 43</p> <p>Exotic 44</p> <p>Document-Oriented 44</p> <p>Deductive 44</p> <p>Dimensional 44</p> <p>Temporal 45</p> <p>Summary 45</p> <p>Exercises 47</p> <p><b>Chapter 3: Relational Database Fundamentals 49</b></p> <p>Relational Points of View 49</p> <p>Table, Rows, and Columns 50</p> <p>Relations, Attributes, and Tuples 52</p> <p>Keys 52</p> <p>Indexes 54</p> <p>Constraints 55</p> <p>Basic Constraints 55</p> <p>Check Constraints 55</p> <p>Primary Key Constraints 55</p> <p>Unique Constraints 56</p> <p>Foreign Key Constraints 56</p> <p>Database Operations 57</p> <p>Summary 59</p> <p>Exercises 59</p> <p><b>Part II: Database Design Process and Techniques</b></p> <p><b>Chapter 4: Understanding User Needs 65</b></p> <p>Make a Plan 66</p> <p>Bring a List of Questions 67</p> <p>Functionality 67</p> <p>Data Needs 67</p> <p>Data Integrity 68</p> <p>Security 68</p> <p>Environment 69</p> <p>Meet the Customers 69</p> <p>Learn Who’s Who 70</p> <p>Pick the Customers’ Brains 73</p> <p>Walk a Mile in the User’s Shoes 73</p> <p>Study Current Operations 74</p> <p>Brainstorm 75</p> <p>Look to the Future 76</p> <p>Understand the Customers’ Reasoning 76</p> <p>Learn What the Customers Really Need 77</p> <p>Prioritize 78</p> <p>Verify Your Understanding 79</p> <p>Write the Requirements Document 80</p> <p>Make Use Cases 81</p> <p>Decide Feasibility 85</p> <p>Summary 85</p> <p>Exercises 86</p> <p><b>Chapter 5: Translating User Needs into Data Models 89</b></p> <p>What Are Data Models? 90</p> <p>User Interface Models 92</p> <p>Semantic Object Models 96</p> <p>Classes and Objects 96</p> <p>Cardinality 97</p> <p>Identifiers 97</p> <p>Putting It Together 97</p> <p>Semantic Views 99</p> <p>Class Types 100</p> <p>Comments and Notes 106</p> <p>Entity-Relationship Models 106</p> <p>Entities, Attributes, and Identifiers 107</p> <p>Relationships 107</p> <p>Cardinality 108</p> <p>Inheritance 109</p> <p>Additional Conventions 112</p> <p>Comments and Notes 113</p> <p>Relational Models 113</p> <p>Converting Semantic Object Models 113</p> <p>Converting ER Diagrams 116</p> <p>Summary 117</p> <p>Exercises 118</p> <p><b>Chapter 6: Extracting Business Rules 121</b></p> <p>What Are Business Rules? 121</p> <p>Identifying Key Business Rules 123</p> <p>Extracting Key Business Rules 128</p> <p>Multi-Tier Applications 129</p> <p>Summary 133</p> <p>Exercises 134</p> <p><b>Chapter 7: Normalizing Data 137</b></p> <p>What Is Normalization? 137</p> <p>First Normal Form (1NF) 138</p> <p>Second Normal Form (2NF) 146</p> <p>Third Normal Form (3NF) 150</p> <p>Stopping at Third Normal Form 154</p> <p>Boyce-Codd Normal Form (BCNF) 154</p> <p>Fourth Normal Form (4NF) 158</p> <p>Fifth Normal Form (5NF) 162</p> <p>Domain/Key Normal Form (DKNF) 165</p> <p>Essential Redundancy 167</p> <p>The Best Level of Normalization 168</p> <p>Summary 169</p> <p>Exercises 170</p> <p><b>Chapter 8: Designing Databases to Support Software Applications 173</b></p> <p>Plan Ahead 173</p> <p>Document Everything 174</p> <p>Consider Multi-Tier Architecture 175</p> <p>Convert Domains into Tables 175</p> <p>Keep Tables Focused 176</p> <p>Use Three Kinds of Tables 176</p> <p>Use Naming Conventions 178</p> <p>Allow Some Redundant Data 179</p> <p>Don’t Squeeze in Everything 180</p> <p>Summary 181</p> <p>Exercises 182</p> <p><b>Chapter 9: Common Design Patterns 185</b></p> <p>Associations 185</p> <p>Many-to-Many Associations 186</p> <p>Multiple Many-to-Many Associations 186</p> <p>Multiple-Object Associations 188</p> <p>Repeated Attribute Associations 191</p> <p>Reflexive Associations 193</p> <p>Temporal Data 200</p> <p>Effective Dates 200</p> <p>Deleted Objects 202</p> <p>Deciding What to Temporalize 203</p> <p>Logging and Locking 203</p> <p>Audit Trails 203</p> <p>Turnkey Records 204</p> <p>Summary 205</p> <p>Exercises 205</p> <p><b>Chapter 10: Common Design Pitfalls 207</b></p> <p>Lack of Preparation 207</p> <p>Poor Documentation 208</p> <p>Poor Naming Standards 208</p> <p>Thinking Too Small 210</p> <p>Not Planning for Change 210</p> <p>Too Much Normalization 213</p> <p>Insufficient Normalization 213</p> <p>Insufficient Testing 214</p> <p>Performance Anxiety 214</p> <p>Mishmash Tables 215</p> <p>Not Enforcing Constraints 217</p> <p>Obsession with IDs 218</p> <p>Not Defining Natural Keys 220</p> <p>Summary 221</p> <p>Exercises 222</p> <p><b>Part III: A Detailed Case Study</b></p> <p><b>Chapter 11: User Needs and Requirements 227</b></p> <p>Meet the Customers 227</p> <p>Pick the Customers’ Brains 229</p> <p>Determining What the System Should Do 229</p> <p>Determining How the Project Should Look 231</p> <p>Determining What Data Is Needed for the User Interface 232</p> <p>Determining Where the Data Should Come From 233</p> <p>Determining How the Pieces of Data Are Related 233</p> <p>Determining Performance Needs 235</p> <p>Determining Security Needs 236</p> <p>Determining Data Integrity Needs 236</p> <p>Write Use Cases 238</p> <p>Write the Requirements Document 242</p> <p>Demand Feedback 243</p> <p>Summary 244</p> <p>Exercises 244</p> <p><b>Chapter 12: Building a Data Model 245</b></p> <p>Semantic Object Modeling 245</p> <p>Building an Initial Semantic Object Model 245</p> <p>Improving the Semantic Object Model 248</p> <p>Entity-Relationship Modeling 250</p> <p>Building an ER Diagram 250</p> <p>Building a Combined ER Diagram 252</p> <p>Improving the Entity-Relationship Diagram 254</p> <p>Relational Modeling 256</p> <p>Putting It All Together 259</p> <p>Summary 260</p> <p>Exercises 261</p> <p><b>Chapter 13: Extracting Business Rules 263</b></p> <p>Identifying Business Rules 263</p> <p>Courses 264</p> <p>CustomerCourses 266</p> <p>Customers 267</p> <p>Pets 267</p> <p>Employees 267</p> <p>Orders 267</p> <p>OrderItems 268</p> <p>InventoryItems 268</p> <p>TimeEntries 268</p> <p>Shifts 269</p> <p>Persons 269</p> <p>Phones 269</p> <p>Vendors 269</p> <p>Drawing a New Relational Model 270</p> <p>Summary 270</p> <p>Exercises 271</p> <p><b>Chapter 14: Normalization and Refinement 273</b></p> <p>Improving Flexibility 273</p> <p>Verifying First Normal Form 275</p> <p>Verifying Second Normal Form 278</p> <p>Pets 279</p> <p>TimeEntries 280</p> <p>Verifying Third Normal Form 281</p> <p>Summary 283</p> <p>Exercises 284</p> <p><b>Part IV: Implementing Databases (with Examples in Access and MySQL)</b></p> <p><b>Chapter 15: Microsoft Access 287</b></p> <p>Understanding Access 287</p> <p>Getting Started 288</p> <p>Defining Relationships 293</p> <p>Creating Field Constraints 296</p> <p>Creating Table Constraints 298</p> <p>Creating Queries 301</p> <p>Query Design View 304</p> <p>SQL View 306</p> <p>Summary 308</p> <p>Exercises 308</p> <p><b>Chapter 16: MySQL 313</b></p> <p>Installing MySQL 313</p> <p>Using MySQL Command Line Client 314</p> <p>Executing SQL Scripts 318</p> <p>Using MySQL Query Browser 319</p> <p>Executing Queries 320</p> <p>Editing Data 320</p> <p>Creating and Modifying Databases 321</p> <p>Using Scripts 327</p> <p>Getting Syntax Help 329</p> <p>Using MySQL Workbench 330</p> <p>Loading Scripts 330</p> <p>Creating EER Diagrams 331</p> <p>Editing Databases 333</p> <p>Defining Triggers 334</p> <p>Exporting Scripts 336</p> <p>Summary 338</p> <p>Exercises 338</p> <p><b>Part V: Advanced Topics</b></p> <p><b>Chapter 17: Introduction to SQL 345</b></p> <p>Background 345</p> <p>Finding More Information 346</p> <p>Standards 346</p> <p>Basic Syntax 347</p> <p>Command Overview 348</p> <p>Create Table 350</p> <p>Create Index 355</p> <p>Drop 356</p> <p>Insert 357</p> <p>Select 358</p> <p>Select Clause 358</p> <p>From Clause 359</p> <p>Where Clause 362</p> <p>Group By Clause 362</p> <p>Order By Clause 363</p> <p>Update 364</p> <p>Delete 365</p> <p>Summary 366</p> <p>Exercises 366</p> <p><b>Chapter 18: Building Databases with SQL Scripts 369</b></p> <p>Why Bother with Scripts? 369</p> <p>Script Categories 370</p> <p>Database Creation Scripts 370</p> <p>Basic Initialization Scripts 370</p> <p>Data Initialization Scripts 370</p> <p>Cleanup Scripts 371</p> <p>Saving Scripts 371</p> <p>Ordering SQL Commands 371</p> <p>Summary 376</p> <p>Exercises 377</p> <p><b>Chapter 19: Database Maintenance 379</b></p> <p>Backups 379</p> <p>Data Warehousing 382</p> <p>Repairing the Database 383</p> <p>Compacting the Database 383</p> <p>Performance Tuning 383</p> <p>Summary 386</p> <p>Exercises 387</p> <p><b>Chapter 20: Database Security 389</b></p> <p>The Right Level of Security 389</p> <p>Passwords 390</p> <p>Single-Password Databases 390</p> <p>Individual Passwords 390</p> <p>Operating System Passwords 391</p> <p>Good Passwords 391</p> <p>Privileges 392</p> <p>Initial Configuration and Privileges 398</p> <p>Too Much Security 398</p> <p>Physical Security 399</p> <p>Summary 400</p> <p>Exercises 401</p> <p>Appendix A: Exercise Solutions 403</p> <p>Chapter 1 403</p> <p>Chapter 2 408</p> <p>Chapter 3 411</p> <p>Chapter 4 413</p> <p>Chapter 5 417</p> <p>Chapter 6 424</p> <p>Chapter 7 430</p> <p>Chapter 8 434</p> <p>Chapter 9 436</p> <p>Chapter 10 440</p> <p>Chapter 11 442</p> <p>Chapter 12 446</p> <p>Chapter 13 449</p> <p>Chapter 14 451</p> <p>Chapter 15 454</p> <p>Chapter 16 454</p> <p>Chapter 17 454</p> <p>Chapter 18 457</p> <p>Chapter 19 459</p> <p>Chapter 20 460</p> <p>Appendix B: Sample Database Designs 467</p> <p>Books 468</p> <p>DVD and Movies 470</p> <p>Music and CDs 471</p> <p>Documents 472</p> <p>Customer Orders 472</p> <p>Employee Shifts and Timesheets 473</p> <p>Employees, Projects, and Departments 473</p> <p>Employee Skills and Qualifications 475</p> <p>Identical Object Rental 476</p> <p>Distinct Object Rental 476</p> <p>Students, Courses, and Grades 478</p> <p>Teams 479</p> <p>Individual Sports 480</p> <p>Vehicle Fleets 480</p> <p>Contacts 483</p> <p>Passengers 483</p> <p>Recipes 485</p> <p>Glossary 487</p> <p>Index 497</p>
<b>Rod Stephens</b> 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.
<p>Beginning Database Design Solutions</p> <p>Databases play a critical role in the business operations of most organizations; they're the central repository for critical information on products, customers, suppliers, sales, and a host of other essential information. It's no wonder that the majority of all business computing involves database applications.</p> <p>With so much at stake, you'd expect most IT professionals would have a firm understanding of good database design. But in fact most learn through a painful process of trial and error, with predictably poor results.</p> <p>This book provides readers with proven methods and tools for designing efficient, reliable, and secure databases. Author Rod Stephens explains how a database should be organized to ensure data integrity without sacrificing performance. He shares procedures for designing robust, flexible, and secure databases that provide a solid foundation for all of your database applications. The methods and techniques in this book can be applied to any database environment, including Oracle®, Microsoft Access®, SQL Server®, and MySQL®. You'll learn the basics of good database design and ultimately discover how to design a real-world database.?</p> <p>What you will learn from this book</p> <ul> <li> <p>How to identify database requirements that meet users' needs</p> </li> <li> <p>Ways to build data models using a variety of modeling techniques, including entity-relational models, user-interface models, and semantic object models</p> </li> <li> <p>Tips for understanding the different types of databases, including relational databases, flat files, spreadsheets, XML and object databases</p> </li> <li> <p>How to refine and tune the design to improve database performance</p> </li> <li> <p>Techniques for understanding the relationship between good application design and database design</p> </li> <li> <p>Methods for designing flexible and robust databases that can adapt to business change and growth</p> </li> <li> <p>Ways to design for ease of maintenance and support</p> </li> <li> <p>How to avoid common database design mistakes</p> </li> </ul> <p>Who this book is for</p> <p>This book is for anyone interested in learning to design, build, analyze, and understand databases. No previous experience with databases or programs is required.</p> <p>Wrox Beginning guides are crafted to make learning programming languages and technologies easier than you think, providing a structured, tutorial format that will guide you through all the techniques involved.</p>

Diese Produkte könnten Sie auch interessieren:

Cyber Security and Digital Forensics
Cyber Security and Digital Forensics
von: Mangesh M. Ghonge, Sabyasachi Pramanik, Ramchandra Mangrulkar, Dac-Nhuong Le
PDF ebook
190,99 €
Smart Systems for Industrial Applications
Smart Systems for Industrial Applications
von: C. Venkatesh, N. Rengarajan, P. Ponmurugan, S. Balamurugan
EPUB ebook
190,99 €
Green Internet of Things and Machine Learning
Green Internet of Things and Machine Learning
von: Roshani Raut, Sandeep Kautish, Zdzislaw Polkowski, Anil Kumar, Chuan-Ming Liu
EPUB ebook
190,99 €