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

Cover
Title Page
Copyright Page
About The Author
Contents
Introduction
Who This Book Is For
What This Book Covers
What You Need to Use This Book
How This Book Is Structured
Part I: Introduction to Databases and Database Design
Part II: Database Design Process and Techniques
Part III: A Detailed Case Study
Part IV: Example Programs
Part V: Advanced Topics
Appendixes
How to Use This Book
Note to Instructors
Note to Students
Conventions
Source Code
Contacting the Author
Disclaimer
Part 1 Introduction to Databases and Database Design
Chapter 1 Database Design Goals
The Importance of Design
Space Spat
Information Containers
Strengths and Weaknesses of Information Containers
Desirable Database Features
CRUD
Retrieval
Consistency
Validity
Easy Error Correction
Speed
Atomic Transactions
ACID
BASE
NewSQL
Persistence and Backups
Low Cost and Extensibility
Ease of Use
Portability
Security
Sharing
Ability to Perform Complex Calculations
CAP Theorem
Cloud Considerations
Legal and Security Considerations
Consequences of Good and Bad Design
Summary
Chapter 2 Relational Overview
Picking a Database
Relational Points of View
Table, Rows, and Columns
Relations, Attributes, and Tuples
Keys
Indexes
Constraints
Domain Constraints
Check Constraints
Primary Key Constraints
Unique Constraints
Foreign Key Constraints
Database Operations
Popular RDBs
Spreadsheets
Summary
Chapter 3 NoSQL Overview
The Cloud
Picking a Database
NoSQL Philosophy
NoSQL Databases
Document Databases
Key-Value Database
Column-Oriented Databases
Graph Databases
Street Networks
Communication Networks
Social Media Apps
E-Commerce Programs
Algorithms
Hierarchical Databases
Less Exotic Options
Flat Files
XML Files
XML Basics
XML Practices
XML Summary
JSON Files
Spreadsheets
More Exotic Options
Object
Deductive
Dimensional
Temporal
Database Pros and Cons
Relational
General NoSQL
Quick Guidelines
Summary
Part 2 Database Design Process and Techniques
Chapter 4 Understanding User Needs
Make a Plan
Bring a List of Questions
Functionality
Data Needs
Data Integrity
Security
Environment
Meet the Customers
Learn Who’s Who
Pick the Customers’ Brains
Walk a Mile in the User’s Shoes
Study Current Operations
Brainstorm
Look to the Future
Understand the Customers’ Reasoning
Learn What the Customers Really Need
Prioritize
Verify Your Understanding
Create the Requirements Document
Make Use Cases
Decide Feasibility
Summary
Chapter 5 Translating User Needs into Data Models
What Are Data Models?
User Interface Models
Semantic Object Models
Classes and Objects
Cardinality
Identifiers
Putting It Together
Semantic Views
Class Types
Simple Objects
Composite Objects
Compound Objects
Hybrid Objects
Association Objects
Inherited Objects
Comments and Notes
Entity-Relationship Models
Entities, Attributes, and Identifiers
Relationships
Cardinality
Inheritance
Additional Conventions
Comments and Notes
Relational Models
Converting Semantic Object Models
Converting ER Diagrams
Summary
Chapter 6 Extracting Business Rules
What Are Business Rules?
Identifying Key Business Rules
Extracting Key Business Rules
Multi-Tier Applications
Summary
Chapter 7 Normalizing Data
What Is Normalization?
First Normal Form (1NF)
Second Normal Form (2NF)
Third Normal Form (3NF)
Stopping at Third Normal Form
Boyce-Codd Normal Form (BCNF)
Fourth Normal Form (4NF)
Fifth Normal Form (5NF)
Domain/Key Normal Form (DKNF)
Essential Redundancy
The Best Level of Normalization
NoSQL Normalization
Summary
Chapter 8 Designing Databases to Support Software
Plan Ahead
Document Everything
Consider Multi-tier Architecture
Convert Domains into Tables
Keep Tables Focused
Use Three Kinds of Tables
Use Naming Conventions
Allow Some Redundant Data
Don’t Squeeze in Everything
Summary
Chapter 9 Using Common Design Patterns
Associations
Many-to-Many Associations
Multiple Many-to-Many Associations
Multiple-Object Associations
Repeated Attribute Associations
Reflexive Associations
One-to-One Reflexive Associations
One-to-Many Reflexive Associations
Hierarchical Data
Hierarchical Data with NoSQL
Network Data
Network Data with NoSQL
Temporal Data
Effective Dates
Deleted Objects
Deciding What to Temporalize
Logging and Locking
Audit Trails
Turnkey Records
Summary
Chapter 10 Avoiding Common Design Pitfalls
Lack of Preparation
Poor Documentation
Poor Naming Standards
Thinking Too Small
Not Planning for Change
Too Much Normalization
Insufficient Normalization
Insufficient Testing
Performance Anxiety
Mishmash Tables
Not Enforcing Constraints
Obsession with IDs
Not Defining Natural Keys
Summary
Part 3 A Detailed Case Study
Chapter 11 Defining User Needs and Requirements
Meet the Customers
Pick the Customers’ Brains
Determining What the System Should Do
Determining How the Project Should Look
Determining What Data Is Needed for the User Interface
Determining Where the Data Should Come From
Determining How the Pieces of Data Are Related
Determining Performance Needs
Determining Security Needs
Determining Data Integrity Needs
Write Use Cases
Write the Requirements Document
Demand Feedback
Summary
Chapter 12 Building a Data Model
Semantic Object Modeling
Building an Initial Semantic Object Model
Improving the Semantic Object Model
Entity-Relationship Modeling
Building an ER Diagram
Building a Combined ER Diagram
Improving the Entity-Relationship Diagram
Relational Modeling
Putting It All Together
Summary
Chapter 13 Extracting Business Rules
Identifying Business Rules
Courses
CustomerCourses
Customers
Pets
Employees
Orders
OrderItems
InventoryItems
TimeEntries
Shifts
Persons
Phones
Vendors
Drawing a New Relational Model
Summary
Exercises
Chapter 14 Normalizing and Refining
Improving Flexibility
Verifying First Normal Form
Verifying Second Normal Form
Pets
TimeEntries
Verifying Third Normal Form
Summary
Part 4 Example Programs
Chapter 15 Example Overview
Tool Choices
Jupyter Notebook
Visual Studio
Database Adapters
Packages in Jupyter Notebook
Packages in Visual Studio
Program Passwords
Summary
Chapter 16 MariaDB in Python
Install MariaDB
Run HeidiSQL
Create the Program
Install pymysql
Create the Database
Define Tables
Create Data
Fetch Data
Summary
Exercises
Chapter 17 MariaDB in C#
Create the Program
Install MySqlConnector
Create the Database
Define Tables
Create Data
Fetch Data
Summary
Exercises
Chapter 18 PostgreSQL in Python
Install PostgreSQL
Run pgAdmin
Design the Database
Create a User
Create the Database
Define the Tables
Define the customers Table
Define the orders Table
Define the order_items Table
Create the Program
Install Psycopg
Connect to the Database
Delete Old Data
Create Customer Data
Create Order Data
Create Order Item Data
Close the Connection
Perform Queries
Summary
Chapter 19 PostgreSQL in C#
Create the Program
Install Npgsql
Connect to the Database
Delete Old Data
Create Customer Data
Create Order Data
Create Order Item Data
Display Orders
Summary
Exercises
Chapter 20 Neo4j AuraDB in Python
Install Neo4j AuraDB
Nodes and Relationships
Cypher
Create the Program
Install the Neo4j Database Adapter
Action Methods
delete_all_nodes
make_node
make_link
execute_node_query
find_path
Org Chart Methods
build_org_chart
query_org_chart
Main Program
Summary
Chapter 21 Neo4j AuraDB in C#
Create the Program
Install the Neo4j Driver
Action Methods
DeleteAllNodes
MakeNode
MakeLink
ExecuteNodeQuery
FindPath
Org Chart Methods
BuildOrgChart
QueryOrgChart
Main
Summary
Chapter 22 MongoDB Atlas in Python
Not Normal but Not Abnormal
XML, JSON, and BSON
Install MongoDB Atlas
Find the Connection Code
Create the Program
Install the PyMongo Database Adapter
Helper Methods
person_string
connect_to_db
delete_old_data
create_data
query_data
Main Program
Summary
Chapter 23 MongoDB Atlas in C#
Create the Program
Install the MongoDB Database Adapter
Helper Methods
PersonString
DeleteOldData
CreateData
QueryData
Main Program
Summary
Chapter 24 Apache Ignite in Python
Install Apache Ignite
Start a Node
Without Persistence
With Persistence
Create the Program
Install the pyignite Database Adapter
Define the Building Class
Save Data
Read Data
Demonstrate Volatile Data
Demonstrate Persistent Data
Summary
Chapter 25 Apache Ignite in C#
Create the Program
Install the Ignite Database Adapter
The Main Program
The Building Class
The WriteData Method
The ReadData Method
Demonstrate Volatile Data
Demonstrate Persistent Data
Summary
Part 5 Advanced Topics
Chapter 26 Introduction to SQL
Background
Finding More Information
Standards
Multistatement Commands
Basic Syntax
Command Overview
Create Table
Create Index
Drop
Insert
Select
SELECT Clause
FROM Clause
WHERE Clause
GROUP BY Clause
ORDER BY Clause
Update
Delete
Summary
Chapter 27 Building Databases with SQL Scripts
Why Bother with Scripts?
Script Categories
Database Creation Scripts
Basic Initialization Scripts
Data Initialization Scripts
Cleanup Scripts
Saving Scripts
Ordering SQL Commands
Summary
Chapter 28 Database Maintenance
Backups
Data Warehousing
Repairing the Database
Compacting the Database
Performance Tuning
Summary
Chapter 29 Database Security
The Right Level of Security
Passwords
Single-Password Databases
Individual Passwords
Operating System Passwords
Good Passwords
Privileges
Initial Configuration and Privileges
Too Much Security
Physical Security
Summary
Appendix A: Exercise Solutions
Chapter 1: Database Design Goals
Exercise 1 Solution
Exercise 2 Solution
Exercise 3 Solution
Exercise 4 Solution
Exercise 5 Solution
Exercise 6 Solution
Exercise 7 Solution
Exercise 8 Solution
Exercise 9 Solution
Exercise 10 Solution
Chapter 2: Relational Overview
Exercise 1 Solution
Exercise 2 Solution
Exercise 3 Solution
Exercise 4 Solution
Exercise 5 Solution
Exercise 6 Solution
Exercise 7 Solution
Exercise 8 Solution
Exercise 9 Solution
Exercise 10 Solution
Chapter 3: NoSQL Overview
Exercise 1 Solution
Exercise 2 Solution
Exercise 3 Solution
Exercise 4 Solution
Exercise 5 Solution
Exercise 6 Solution
Exercise 7 Solution
Exercise 8 Solution
Exercise 9 Solution
Exercise 10 Solution
Exercise 11 Solution
Exercise 12 Solution
Exercise 13 Solution
Exercise 14 Solution
Chapter 4: Understanding User Needs
Exercise 1 Solution
Exercise 2 Solution
Exercise 3 Solution
Exercise 4 Solution
Exercise 5 Solution
Exercise 6 Solution
Exercise 7 Solution
Exercise 8 Solution
Exercise 9 Solution
Exercise 10 Solution
Exercise 11 Solution
Chapter 5: Translating User Needs into Data Models
Exercise 1 Solution
Exercise 2 Solution
Exercise 3 Solution
Exercise 4 Solution
Exercise 5 Solution
Exercise 6 Solution
Exercise 7 Solution
Exercise 8 Solution
Chapter 6: Extracting Business Rules
Exercise 1 Solution
Exercise 2 Solution
Exercise 3 Solution
Exercise 4 Solution
Chapter 7: Normalizing Data
Exercise 1 Solution
Exercise 2 Solution
Exercise 3 Solution
Exercise 4 Solution
Exercise 5 Solution
Exercise 6 Solution
Chapter 8: Designing Databases to Support Software
Exercise 1 Solution
Exercise 2 Solution
Exercise 3 Solution
Exercise 4 Solution
Chapter 9: Using Common Design Patterns
Exercise 1 Solution
Exercise 2 Solution
Exercise 3 Solution
Exercise 4 Solution
Exercise 5 Solution
Exercise 6 Solution
Exercise 7 Solution
Exercise 8 Solution
Exercise 9 Solution
Chapter 10: Avoiding Common Design Pitfalls
Exercise 1 Solution
Exercise 2 Solution
Exercise 3 Solution
Exercise 4 Solution
Chapter 11: Defining User Needs and Requirements
Exercise 1 Solution
Exercise 2 Solution
Exercise 3 Solution
Exercise 4 Solution
Exercise 5 Solution
Exercise 6 Solution
Chapter 12: Building a Data Model
Exercise 1 Solution
Exercise 2 Solution
Exercise 3 Solution
Exercise 4 Solution
Exercise 5 Solution
Exercise 6 Solution
Exercise 7 Solution
Exercise 8 Solution
Exercise 9 Solution
Exercise 10 Solution
Exercise 11 Solution
Exercise 12 Solution
Chapter 13: Extracting Business Rules
Exercise 1 Solution
Exercise 2 Solution
Exercise 3 Solution
Exercise 4 Solution
Exercise 5 Solution
Chapter 14: Normalizing and Refining
Exercise 1 Solution
Exercise 2 Solution
Exercise 3 Solution
Exercise 4 Solution
Chapter 15: Example Overview
Exercise 1 Solution
Exercise 2 Solution
Exercise 3 Solution
Exercise 4 Solution
Exercise 5 Solution
Exercise 6 Solution
Chapter 16: MariaDB in Python
Exercise 1 Solution
Exercise 2 Solution
Exercise 3 Solution
Exercise 4 Solution
Exercise 5 Solution
Exercise 6 Solution
Chapter 17: MariaDB in C#
Exercise 1 Solution
Exercise 2 Solution
Exercise 3 Solution
Exercise 4 Solution
Exercise 5 Solution
Exercise 6 Solution
Chapter 18: PostgreSQL in Python
Exercise 1 Solution
Exercise 2 Solution
Exercise 3 Solution
Exercise 4 Solution
Exercise 5 Solution
Exercise 6 Solution
Exercise 7 Solution
Chapter 19: PostgreSQL in C#
Exercise 1 Solution
Exercise 2 Solution
Exercise 3 Solution
Exercise 4 Solution
Exercise 5 Solution
Exercise 6 Solution
Exercise 7 Solution
Chapter 20: Neo4j AuraDB in Python
Exercise 1 Solution
Exercise 2 Solution
Exercise 3 Solution
Exercise 4 Solution
Exercise 5 Solution
Chapter 21: Neo4j AuraDB in C#
Exercise 1 Solution
Exercise 2 Solution
Exercise 3 Solution
Exercise 4 Solution
Exercise 5 Solution
Exercise 6 Solution
Exercise 7 Solution
Chapter 22: MongoDB Atlas in Python
Exercise 1 Solution
Exercise 2 Solution
Exercise 3 Solution
Exercise 4 Solution
Chapter 23: MongoDB Atlas in C#
Exercise 1 Solution
Exercise 2 Solution
Exercise 3 Solution
Exercise 4 Solution
Chapter 24: Apache Ignite in Python
Exercise 1 Solution
Exercise 2 Solution
Exercise 3 Solution
Exercise 4 Solution
Chapter 25: Apache Ignite in C#
Exercise 1 Solution
Exercise 2 Solution
Exercise 3 Solution
Exercise 4 Solution
Chapter 26: Introduction to SQL
Exercise 1 Solution
Exercise 2 Solution
Exercise 3 Solution
Exercise 4 Solution
Exercise 5 Solution
Chapter 27: Building Databases with SQL Scripts
Exercise 1 Solution
Exercise 2 Solution
Chapter 28: Database Maintenance
Exercise 1 Solution
Exercise 2 Solution
Exercise 3 Solution
Chapter 29: Database Security
Exercise 1 Solution
Exercise 2 Solution
Exercise 3 Solution
Appendix B: Sample Relational Designs
Books
Movies
Music
Document Management
Customer Orders
Employee Shifts and Timesheets
Employees, Projects, and Departments
Employee Skills and Qualifications
Identical Object Rental
Distinct Object Rental
Students, Courses, and Grades
Teams
Individual Sports
Vehicle Fleets
Contacts
Passengers
Recipes
Glossary
Index
EULA