Beginning Database Design Solutions: Understanding and Implementing Database Design Concepts for the Cloud and Beyond

ISBN-13: 9781394155729 / Angielski / Miękka / 2023 / 560 str.

Rod (C# Helper; VB Helper) Stephens

Spis treści

Introduction xxvPart 1: Introduction to Databases and Database DesignChapter 1: Database Design Goals 3The Importance of Design 4Information Containers 6Strengths and Weaknesses of Information Containers 8Desirable Database Features 9Crud 10Retrieval 10Consistency 11Validity 11Easy Error Correction 12Speed 13Atomic Transactions 13Acid 14Base 16NewSQL 17Persistence and Backups 17Low Cost and Extensibility 18Ease of Use 19Portability 19Security 20Sharing 21Ability to Perform Complex Calculations 21CAP Theorem 22Cloud Considerations 22Legal and Security Considerations 23Consequences of Good and Bad Design 24Summary 26Chapter 2: Relational Overview 29Picking a Database 30Relational Points of View 31Table, Rows, and Columns 32Relations, Attributes, and Tuples 34Keys 34Indexes 36Constraints 37Domain Constraints 37Check Constraints 37Primary Key Constraints 38Unique Constraints 38Foreign Key Constraints 38Database Operations 40Popular RDBs 41Spreadsheets 43Summary 44Chapter 3: NoSQL OVERVIEW 47The Cloud 47Picking a Database 50NoSQL Philosophy 50NoSQL Databases 50Document Databases 51Key- Value Database 52Column- Oriented Databases 53Graph Databases 53Street Networks 54Communication Networks 55Social Media Apps 55E- Commerce Programs 55Algorithms 56Hierarchical Databases 56Less Exotic Options 59Flat Files 59XML Files 60XML Basics 61XML Practices 64XML Summary 66JSON Files 67Spreadsheets 69More Exotic Options 70Object 70Deductive 70Dimensional 70Temporal 71Database Pros and Cons 72Relational 72General NoSQL 73Quick Guidelines 74Summary 76Part 2: Database Design Process and TechniquesChapter 4: Understanding User Needs 83Make a Plan 84Bring a List of Questions 85Functionality 85Data Needs 86Data Integrity 86Security 87Environment 88Meet the Customers 88Learn Who's Who 89Pick the Customers' Brains 93Walk a Mile in the User's Shoes 93Study Current Operations 94Brainstorm 94Look to the Future 95Understand the Customers' Reasoning 96Learn What the Customers Really Need 97Prioritize 98Verify Your Understanding 99Create the Requirements Document 101Make Use Cases 102Decide Feasibility 106Summary 106Chapter 5: Translating User Needs Into Data Models 111What Are Data Models? 112User Interface Models 114Semantic Object Models 118Classes and Objects 119Cardinality 120Identifiers 120Putting It Together 121Semantic Views 122Class Types 124Simple Objects 124Composite Objects 124Compound Objects 125Hybrid Objects 125Association Objects 126Inherited Objects 128Comments and Notes 129Entity- Relationship Models 130Entities, Attributes, and Identifiers 131Relationships 132Cardinality 133Inheritance 134Additional Conventions 136Comments and Notes 137Relational Models 137Converting Semantic Object Models 138Converting ER Diagrams 140Summary 142Chapter 6: Extracting Business Rules 145What Are Business Rules? 145Identifying Key Business Rules 147Extracting Key Business Rules 152Multi- Tier Applications 154Summary 158Chapter 7: Normalizing Data 163What Is Normalization? 163First Normal Form (1NF) 164Second Normal Form (2NF) 173Third Normal Form (3NF) 177Stopping at Third Normal Form 181Boyce- Codd Normal Form (BCNF) 181Fourth Normal Form (4NF) 185Fifth Normal Form (5NF) 190Domain/Key Normal Form (DKNF) 193Essential Redundancy 195The Best Level of Normalization 197NoSQL Normalization 197Summary 199Chapter 8: Designing Databases to Support Software 203Plan Ahead 204Document Everything 204Consider Multi- Tier Architecture 205Convert Domains into Tables 205Keep Tables Focused 206Use Three Kinds of Tables 207Use Naming Conventions 209Allow Some Redundant Data 210Don't Squeeze in Everything 211Summary 212Chapter 9: Using Common Design Patterns 215Associations 216Many- to- Many Associations 216Multiple Many- to- Many Associations 216Multiple- Object Associations 218Repeated Attribute Associations 221Reflexive Associations 222One- to- One Reflexive Associations 223One- to- Many Reflexive Associations 224Hierarchical Data 225Hierarchical Data with NoSQL 228Network Data 229Network Data with NoSQL 231Temporal Data 232Effective Dates 232Deleted Objects 233Deciding What to Temporalize 234Logging and Locking 236Audit Trails 236Turnkey Records 237Summary 238Chapter 10: Avoiding Common Design Pitfalls 241Lack of Preparation 241Poor Documentation 242Poor Naming Standards 242Thinking Too Small 244Not Planning for Change 245Too Much Normalization 248Insufficient Normalization 248Insufficient Testing 249Performance Anxiety 249Mishmash Tables 250Not Enforcing Constraints 253Obsession with IDs 253Not Defining Natural Keys 256Summary 257Part 3: a Detailed Case StudyChapter 11: Defining User Needs and Requirements 263Meet the Customers 263Pick the Customers' Brains 265Determining What the System Should Do 265Determining How the Project Should Look 267Determining What Data Is Needed for the User Interface 268Determining Where the Data Should Come From 269Determining How the Pieces of Data Are Related 269Determining Performance Needs 271Determining Security Needs 272Determining Data Integrity Needs 273Write Use Cases 275Write the Requirements Document 279Demand Feedback 280Summary 281Chapter 12: Building a Data Model 283Semantic Object Modeling 283Building an Initial Semantic Object Model 283Improving the Semantic Object Model 286Entity- Relationship Modeling 289Building an ER Diagram 289Building a Combined ER Diagram 291Improving the Entity- Relationship Diagram 293Relational Modeling 294Putting It All Together 298Summary 299Chapter 13: Extracting Business Rules 303Identifying Business Rules 303Courses 304CustomerCourses 306Customers 307Pets 307Employees 307Orders 307OrderItems 308InventoryItems 308TimeEntries 308Shifts 309Persons 309Phones 309Vendors 309Drawing a New Relational Model 310Summary 310Chapter 14: Normalizing and Refining 313Improving Flexibility 313Verifying First Normal Form 315Verifying Second Normal Form 318Pets 319TimeEntries 320Verifying Third Normal Form 321Summary 323Part 4: Example ProgramsChapter 15: Example Overview 327Tool Choices 327Jupyter Notebook 329Visual Studio 331Database Adapters 332Packages in Jupyter Notebook 333Packages in Visual Studio 334Program Passwords 336Summary 336Chapter 16: MariaDB IN PYTHON 339Install MariaDB 340Run HeidiSQL 340Create the Program 343Install pymysql 344Create the Database 344Define Tables 346Create Data 348Fetch Data 350Summary 352Chapter 17: MariaDB IN C# 355Create the Program 355Install MySqlConnector 356Create the Database 356Define Tables 358Create Data 360Fetch Data 364Summary 366Chapter 18: PostgreSQL IN PYTHON 369Install PostgreSQL 370Run pgAdmin 371Design the Database 371Create a User 371Create the Database 373Define the Tables 374Define the customers Table 374Define the orders Table 376Define the order_items Table 377Create the Program 378Install Psycopg 379Connect to the Database 379Delete Old Data 380Create Customer Data 380Create Order Data 382Create Order Item Data 383Close the Connection 384Perform Queries 384Summary 386Chapter 19: PostgreSQL IN C# 389Create the Program 389Install Npgsql 389Connect to the Database 390Delete Old Data 391Create Customer Data 392Create Order Data 393Create Order Item Data 395Display Orders 396Summary 399Chapter 20: Neo4j AuraDB IN PYTHON 401Install Neo4j AuraDB 402Nodes and Relationships 404Cypher 404Create the Program 405Install the Neo4j Database Adapter 405Action Methods 405delete_all_nodes 406make_node 407make_link 407execute_node_query 408find_path 409Org Chart Methods 410build_org_chart 410query_org_chart 411Main Program 412Summary 414Chapter 21: Neo4j AuraDB IN C# 417Create the Program 418Install the Neo4j Driver 418Action Methods 419DeleteAllNodes 419MakeNode 420MakeLink 421ExecuteNodeQuery 422FindPath 422Org Chart Methods 423BuildOrgChart 424QueryOrgChart 424Main 426Summary 428Chapter 22: MongoDB ATLAS IN PYTHON 431Not Normal but Not Abnormal 432XML, JSON, and BSON 432Install MongoDB Atlas 434Find the Connection Code 436Create the Program 439Install the PyMongo Database Adapter 439Helper Methods 440person_string 440connect_to_db 441delete_old_data 442create_data 442query_data 444Main Program 449Summary 450Chapter 23: MongoDB ATLAS IN C# 453Create the Program 454Install the MongoDB Database Adapter 454Helper Methods 454PersonString 455DeleteOldData 456CreateData 457QueryData 458Main Program 462Summary 465Chapter 24: Apache Ignite in Python 467Install Apache Ignite 468Start a Node 468Without Persistence 469With Persistence 470Create the Program 470Install the pyignite Database Adapter 471Define the Building Class 471Save Data 471Read Data 473Demonstrate Volatile Data 473Demonstrate Persistent Data 474Summary 474Chapter 25: Apache Ignite in C# 477Create the Program 477Install the Ignite Database Adapter 478The Main Program 479The Building Class 480The WriteData Method 480The ReadData Method 482Demonstrate Volatile Data 483Demonstrate Persistent Data 483Summary 483Part 5: Advanced TopicsChapter 26: Introduction to Sql 489Background 491Finding More Information 491Standards 492Multistatement Commands 493Basic Syntax 495Command Overview 495Create Table 498Create Index 503Drop 504Insert 504Select 506SELECT Clause 506FROM Clause 507WHERE Clause 511GROUP BY Clause 511ORDER BY Clause 512Update 513Delete 514Summary 515Chapter 27: Building Databases with Sql Scripts 519Why Bother with Scripts? 519Script Categories 520Database Creation Scripts 520Basic Initialization Scripts 520Data Initialization Scripts 520Cleanup Scripts 521Saving Scripts 521Ordering SQL Commands 522Summary 531Chapter 28: Database Maintenance 533Backups 533Data Warehousing 537Repairing the Database 538Compacting the Database 538Performance Tuning 538Summary 542Chapter 29: Database Security 545The Right Level of Security 545Passwords 546Single- Password Databases 546Individual Passwords 546Operating System Passwords 547Good Passwords 547Privileges 548Initial Configuration and Privileges 553Too Much Security 553Physical Security 554Summary 555Appendix A: Exercise Solutions 557Appendix B: Sample Relational Designs 649Glossary 671Index 683

O autorze

ROD STEPHENS is a professional programmer, sought-after technical speaker, instructor, and author. He's written 37 books and over 250 magazine articles on C#, Java, and other programming languages. He runs the popular C# Helper and VB Helper websites which have helped millions of programmers with tips, tricks, and useful example code.