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

A concise introduction to database design concepts, methods, and techniques in and out of the cloud

In the newly revised second edition of Beginning Database Design Solutions: Understanding and Implementing Database Design Concepts for the Cloud and Beyond, Second Edition, award-winning programming instructor and mathematician Rod Stephens delivers an easy-to-understand guide to designing and implementing databases both in and out of the cloud. Without assuming any prior database design knowledge, the author walks you through the steps you’ll need to take to understand, analyze, design, and build databases.

In the book, you’ll find clear coverage of foundational database concepts along with hands-on examples that help you practice important techniques so you can apply them to your own database designs, as well as:

• Downloadable source code that illustrates the concepts discussed in the book
• Best practices for reliable, platform-agnostic database design
• Strategies for digital transformation driven by universally accessible database design

An essential resource for database administrators, data management specialists, and database developers seeking expertise in relational, NoSQL, and hybrid database design both in and out of the cloud, Beginning Database Design Solutions is a hands-on guide ideal for students and practicing professionals alike.

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.

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

The New copy of this book will include any supplemental materials advertised. Please check the title of the book to determine if it should include any access cards, study guides, lab manuals, CDs, etc.

The Used, Rental and eBook copies of this book are not guaranteed to include any supplemental materials. Typically, only the book itself is included. This is true even if the title states it includes any access cards, study guides, lab manuals, CDs, etc.