Introduction xxv Part 1: Introduction to Databases and Database Design Chapter 1: Database Design Goals 3 The Importance of Design 4 Information Containers 6 Strengths and Weaknesses of Information Containers 8 Desirable Database Features 9 Crud 10 Retrieval 10 Consistency 11 Validity 11 Easy Error Correction 12 Speed 13 Atomic Transactions 13 Acid 14 Base 16 NewSQL 17 Persistence and Backups 17 Low Cost and Extensibility 18 Ease of Use 19 Portability 19 Security 20 Sharing 21 Ability to Perform Complex Calculations 21 CAP Theorem 22 Cloud Considerations 22 Legal and Security Considerations 23 Consequences of Good and Bad Design 24 Summary 26 Chapter 2: Relational Overview 29 Picking a Database 30 Relational Points of View 31 Table, Rows, and Columns 32 Relations, Attributes, and Tuples 34 Keys 34 Indexes 36 Constraints 37 Domain Constraints 37 Check Constraints 37 Primary Key Constraints 38 Unique Constraints 38 Foreign Key Constraints 38 Database Operations 40 Popular RDBs 41 Spreadsheets 43 Summary 44 Chapter 3: NoSQL OVERVIEW 47 The Cloud 47 Picking a Database 50 NoSQL Philosophy 50 NoSQL Databases 50 Document Databases 51 Key- Value Database 52 Column- Oriented Databases 53 Graph Databases 53 Street Networks 54 Communication Networks 55 Social Media Apps 55 E- Commerce Programs 55 Algorithms 56 Hierarchical Databases 56 Less Exotic Options 59 Flat Files 59 XML Files 60 XML Basics 61 XML Practices 64 XML Summary 66 JSON Files 67 Spreadsheets 69 More Exotic Options 70 Object 70 Deductive 70 Dimensional 70 Temporal 71 Database Pros and Cons 72 Relational 72 General NoSQL 73 Quick Guidelines 74 Summary 76 Part 2: Database Design Process and Techniques Chapter 4: Understanding User Needs 83 Make a Plan 84 Bring a List of Questions 85 Functionality 85 Data Needs 86 Data Integrity 86 Security 87 Environment 88 Meet the Customers 88 Learn Who''s Who 89 Pick the Customers'' Brains 93 Walk a Mile in the User''s Shoes 93 Study Current Operations 94 Brainstorm 94 Look to the Future 95 Understand the Customers'' Reasoning 96 Learn What the Customers Really Need 97 Prioritize 98 Verify Your Understanding 99 Create the Requirements Document 101 Make Use Cases 102 Decide Feasibility 106 Summary 106 Chapter 5: Translating User Needs Into Data Models 111 What Are Data Models? 112 User Interface Models 114 Semantic Object Models 118 Classes and Objects 119 Cardinality 120 Identifiers 120 Putting It Together 121 Semantic Views 122 Class Types 124 Simple Objects 124 Composite Objects 124 Compound Objects 125 Hybrid Objects 125 Association Objects 126 Inherited Objects 128 Comments and Notes 129 Entity- Relationship Models 130 Entities, Attributes, and Identifiers 131 Relationships 132 Cardinality 133 Inheritance 134 Additional Conventions 136 Comments and Notes 137 Relational Models 137 Converting Semantic Object Models 138 Converting ER Diagrams 140 Summary 142 Chapter 6: Extracting Business Rules 145 What Are Business Rules? 145 Identifying Key Business Rules 147 Extracting Key Business Rules 152 Multi- Tier Applications 154 Summary 158 Chapter 7: Normalizing Data 163 What Is Normalization? 163 First Normal Form (1NF) 164 Second Normal Form (2NF) 173 Third Normal Form (3NF) 177 Stopping at Third Normal Form 181 Boyce- Codd Normal Form (BCNF) 181 Fourth Normal Form (4NF) 185 Fifth Normal Form (5NF) 190 Domain/Key Normal Form (DKNF) 193 Essential Redundancy 195 The Best Level of Normalization 197 NoSQL Normalization 197 Summary 199 Chapter 8: Designing Databases to Support Software 203 Plan Ahead 204 Document Everything 204 Consider Multi- Tier Architecture 205 Convert Domains into Tables 205 Keep Tables Focused 206 Use Three Kinds of Tables 207 Use Naming Conventions 209 Allow Some Redundant Data 210 Don''t Squeeze in Everything 211 Summary 212 Chapter 9: Using Common Design Patterns 215 Associations 216 Many- to- Many Associations 216 Multiple Many- to- Many Associations 216 Multiple- Object Associations 218 Repeated Attribute Associations 221 Reflexive Associations 222 One- to- One Reflexive Associations 223 One- to- Many Reflexive Associations 224 Hierarchical Data 225 Hierarchical Data with NoSQL 228 Network Data 229 Network Data with NoSQL 231 Temporal Data 232 Effective Dates 232 Deleted Objects 233 Deciding What to Temporalize 234 Logging and Locking 236 Audit Trails 236 Turnkey Records 237 Summary 238 Chapter 10: Avoiding Common Design Pitfalls 241 Lack of Preparation 241 Poor Documentation 242 Poor Naming Standards 242 Thinking Too Small 244 Not Planning for Change 245 Too Much Normalization 248 Insufficient Normalization 248 Insufficient Testing 249 Performance Anxiety 249 Mishmash Tables 250 Not Enforcing Constraints 253 Obsession with IDs 253 Not Defining Natural Keys 256 Summary 257 Part 3: a Detailed Case Study Chapter 11: Defining User Needs and Requirements 263 Meet the Customers 263 Pick the Customers'' Brains 265 Determining What the System Should Do 265 Determining How the Project Should Look 267 Determining What Data Is Needed for the User Interface 268 Determining Where the Data Should Come From 269 Determining How the Pieces of Data Are Related 269 Determining Performance Needs 271 Determining Security Needs 272 Determining Data Integrity Needs 273 Write Use Cases 275 Write the Requirements Document 279 Demand Feedback 280 Summary 281 Chapter 12: Building a Data Model 283 Semantic Object Modeling 283 Building an Initial Semantic Object Model 283 Improving the Semantic Object Model 286 Entity- Relationship Modeling 289 Building an ER Diagram 289 Building a Combined ER Diagram 291 Improving the Entity- Relationship Diagram 293 Relational Modeling 294 Putting It All Together 298 Summary 299 Chapter 13: Extracting Business Rules 303 Identifying Business Rules 303 Courses 304 CustomerCourses 306 Customers 307 Pets 307 Employees 307 Orders 307 OrderItems 308 InventoryItems 308 TimeEntries 308 Shifts 309 Persons 309 Phones 309 Vendors 309 Drawing a New Relational Model 310 Summary 310 Chapter 14: Normalizing and Refining 313 Improving Flexibility 313 Verifying First Normal Form 315 Verifying Second Normal Form 318 Pets 319 TimeEntries 320 Verifying Third Normal Form 321 Summary 323 Part 4: Example Programs Chapter 15: Example Overview 327 Tool Choices 327 Jupyter Notebook 329 Visual Studio 331 Database Adapters 332 Packages in Jupyter Notebook 333 Packages in Visual Studio 334 Program Passwords 336 Summary 336 Chapter 16: MariaDB IN PYTHON 339 Install MariaDB 340 Run HeidiSQL 340 Create the Program 343 Install pymysql 344 Create the Database 344 Define Tables 346 Create Data 348 Fetch Data 350 Summary 352 Chapter 17: MariaDB IN C# 355 Create the Program 355 Install MySqlConnector 356 Create the Database 356 Define Tables 358 Create Data 360 Fetch Data 364 Summary 366 Chapter 18: PostgreSQL IN PYTHON 369 Install PostgreSQL 370 Run pgAdmin 371 Design the Database 371 Create a User 371 Create the Database 373 Define the Tables 374 Define the customers Table 374 Define the orders Table 376 Define the order_items Table 377 Create the Program 378 Install Psycopg 379 Connect to the Database 379 Delete Old Data 380 Create Customer Data 380 Create Order Data 382 Create Order Item Data 383 Close the Connection 384 Perform Queries 384 Summary 386 Chapter 19: PostgreSQL IN C# 389 Create the Program 389 Install Npgsql 389 Connect to the Database 390 Delete Old Data 391 Create Customer Data 392 Create Order Data 393 Create Order Item Data 395 Display Orders 396 Summary 399 Chapter 20: Neo4j AuraDB IN PYTHON 401 Install Neo4j AuraDB 402 Nodes and Relationships 404 Cypher 404 Create the Program 405 Install t.