Best practices for reliable, platform-agnost
Table of Contents
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 the Neo4j Database Adapter 405
Action Methods 405
delete_all_nodes 406
make_node 407
make_link 407
execute_node_query 408
find_path 409
Org Chart Methods 410
build_org_chart 410
query_org_chart 411
Main Program 412
Summary 414
Chapter 21: Neo4j AuraDB IN C# 417
Create the Program 418
Install the Neo4j Driver 418
Action Methods 419
DeleteAllNodes 419
MakeNode 420
MakeLink 421
ExecuteNodeQuery 422
FindPath 422
Org Chart Methods 423
BuildOrgChart 424
QueryOrgChart 424
Main 426
Summary 428
Chapter 22: MongoDB ATLAS IN PYTHON 431
Not Normal but Not Abnormal 432
XML, JSON, and BSON 432
Install MongoDB Atlas 434
Find the Connection Code 436
Create the Program 439
Install the PyMongo Database Adapter 439
Helper Methods 440
person_string 440
connect_to_db 441
delete_old_data 442
create_data 442
query_data 444
Main Program 449
Summary 450
Chapter 23: MongoDB ATLAS IN C# 453
Create the Program 454
Install the MongoDB Database Adapter 454
Helper Methods 454
PersonString 455
DeleteOldData 456
CreateData 457
QueryData 458
Main Program 462
Summary 465
Chapter 24: Apache Ignite in Python 467
Install Apache Ignite 468
Start a Node 468
Without Persistence 469
With Persistence 470
Create the Program 470
Install the pyignite Database Adapter 471
Define the Building Class 471
Save Data 471
Read Data 473
Demonstrate Volatile Data 473
Demonstrate Persistent Data 474
Summary 474
Chapter 25: Apache Ignite in C# 477
Create the Program 477
Install the Ignite Database Adapter 478
The Main Program 479
The Building Class 480
The WriteData Method 480
The ReadData Method 482
Demonstrate Volatile Data 483
Demonstrate Persistent Data 483
Summary 483
Part 5: Advanced Topics
Chapter 26: Introduction to Sql 489
Background 491
Finding More Information 491
Standards 492
Multistatement Commands 493
Basic Syntax 495
Command Overview 495
Create Table 498
Create Index 503
Drop 504
Insert 504
Select 506
SELECT Clause 506
FROM Clause 507
WHERE Clause 511
GROUP BY Clause 511
ORDER BY Clause 512
Update 513
Delete 514
Summary 515
Chapter 27: Building Databases with Sql Scripts 519
Why Bother with Scripts? 519
Script Categories 520
Database Creation Scripts 520
Basic Initialization Scripts 520
Data Initialization Scripts 520
Cleanup Scripts 521
Saving Scripts 521
Ordering SQL Commands 522
Summary 531
Chapter 28: Database Maintenance 533
Backups 533
Data Warehousing 537
Repairing the Database 538
Compacting the Database 538
Performance Tuning 538
Summary 542
Chapter 29: Database Security 545
The Right Level of Security 545
Passwords 546
Single- Password Databases 546
Individual Passwords 546
Operating System Passwords 547
Good Passwords 547
Privileges 548
Initial Configuration and Privileges 553
Too Much Security 553
Physical Security 554
Summary 555
Appendix A: Exercise Solutions 557
Appendix B: Sample Relational Designs 649
Glossary 671
Index 683
