Acknowledgments v
About the Authors vi
About the Technical Writer vii
About the Technical Editor viii
Introduction xix
Part I: Introduction to Database Concepts 1
Lesson 1: Exploring Relational Databases and SQL 3
Saving Data 4
What Is a Database? 5
Database Uses 5
Data vs. Information 6
Structured vs. Unstructured 6
Database vs. DBMS 7
Relational Database Concepts 7
ACID Compliance 9
ACID Properties 10
Atomicity 10
Consistency 10
Isolation 12
Durability 12
Databases and Log Files 12
Entity Integrity 13
Ensuring Uniqueness 13
Finding Records 14
Backup Strategies 15
Summary 16
Exercises 17
Exercise 1.1: Customers and Orders 17
Exercise 1.2: Libraries and the Books Within 17
Exercise 1.3: Your Scenario 18
Lesson 2: Applying Normalization 19
What Is Normalization? 19
Data Redundancy Is a Problem 20
Storage Reduction 21
Functional Dependencies 22
Normalizing Data 22
First Normal Form 23
Top- to- Bottom or Left- to- Right Ordering 23
Every Row Can Be Uniquely Identified 24
Every Field Contains Only One Value 24
Summary of First Normal Form 25
Second Normal Form 26
Normalize to 1NF 27
Composite Keys 28
Summary of Second Normal Form 31
Third Normal Form 33
Denormalization 35
Summary 37
Exercises 37
Exercise 2.1: Employees 38
Exercise 2.2: Libraries and the Books Within 38
Exercise 2.3: Hotels 39
Exercise 2.4: Students and Courses 39
Exercise 2.5: On the Menu 40
Lesson 3: Creating Entity- Relationship Diagrams 41
Using ERDs 42
Available Tools 43
ERD Components 45
Creating Tables 45
Adding Fields 46
Identifying Keys 47
Including Additional Tables 47
Showing Relationships 48
ERD of Database 50
What About Many- to- Many Relationships? 51
Summary 52
Exercises 53
Exercise 3.1: Customers and Orders 53
Exercise 3.2: The Relationship Between Libraries and Books 53
Exercise 3.3: Many to Many No More 53
Exercise 3.4: Diagramming the Menu 54
Exercise 3.5: Database Design Assessment 54
Lesson 4: Pulling It All Together: Normalizing a Vinyl Record
Shop Database 57
The Vinyl Record Shop Data Overview 58
Step 1: Identify the Entities and Attributes 59
Step 1 Results 60
Step 2: First Normal Form 61
Determining Primary Keys 62
Resolving Multivalued Fields 63
Normalizing the Song Entity 65
Step 2 Results 67
Step 3: Second Normal Form 69
Step 3 Results 69
Step 4: Third Normal Form 69
Step 4 Results 70
ERD in 3NF 71
Step 5: Finalize the Structure 73
Final Steps 73
Summary 75
Part II: Applying SQL 77
Lesson 5: Working with MySQL Server 79
MySQL Installation 80
Step 1: Get the Download 80
Step 2: Skipping the Login 80
Step 3: Starting the Install 81
Step 4: Tool Selection 82
Step 5: Product Configuration 83
Step 6: MySQL Router Configuration 87
MySQL Notifier 90
Command- Line Interface 91
Getting Started with MySQL Workbench 93
Use MySQL Workbench 96
Run a Test Command 101
Summary 102
Exercises 103
Exercise 5.1: Running the Tools 104
Exercise 5.2: Listing the Cities 104
Exercise 5.3: Small Cities 104
Lesson 6: Diving into SQL 105
Introduction to SQL 106
SQL Syntax 106
Semicolon 107
Line Breaks and Indents 107
Letter Case 108
Commas 109
Spaces 110
Quotation Marks 110
Spelling 111
Working with Null Values 111
Null vs. Zero 111
Nullable Fields 112
Consequences of Null Values 113
Working with Indexes 116
Primary vs. Secondary Storage 117
Indexing Fields 117
Default Indexes 118
Unique and Nonunique Indexes 119
Summary 119
Exercises 120
Exercise 6.1: Remember Your Lines 120
Exercise 6.2: Contact Questions 120
Exercise 6.3: Missing Contact 121
Lesson 7: Database Management Using DDL 123
Database Management 124
Create a New Database 124
List Existing Databases 125
Use a Database 126
Delete an Existing Database 127
MySQL Data Types 127
Data Types 128
Numeric Data Types 128
Integer Types 128
Decimal Types 129
String Types 130
Date/Time 130
Managing Tables in MySQL 131
Create a Table 131
List Tables 133
View a Table 134
Change a Table 135
Dropping a Field 135
Setting a Key Value 135
Modifying a Field 136
Adding a Field 137
Altering Tables with Existing Data 137
Delete a Table 137
Summarizing the book Table Changes 138
Managing Relationships in MySQL 139
Define a Foreign Key 139
Entity Integrity 141
Referential Integrity 141
Adding Data to a Foreign Key Field 141
Updating Data in a Primary Record 142
Deleting Data from a Primary Record 142
Work- Arounds for Referential Integrity 142
Remove the Foreign Key Constraints 142
Using ON UPDATE 142
Using ON DELETE 143
Summary 143
Exercises 144
Exercise 7.1: Books Database 144
Part 1: Define the Tables 146
Part 2: Books Database SQL Scripts 146
Part 3: Test the Script 147
Exercise 7.2: DDL Activity: Movies Database 147
Part 1: Define the Tables 148
Part 2: Create the Script 149
Part 3: Test the Script 149
Lesson 8: Pulling It All Together: Building the Vinyl Record
Shop Database 151
Step 1: Examine the Structure 152
Organize the Tables 154
Create the Script File 155
Step 2: Create the Database 155
Step 3: Create the Primary Tables 157
Column Order 158
On Your Own 159
Step 4: Create the Related Tables 160
Create the song Table 160
Create the songAlbum Table 162
Create the bandArtist Table on Your Own 164
Step 5: Finalize the Script 164
Summary 167
Part III: Data Management and Manipulation 169
Lesson 9: Applying CRUD: Basic Data Management and Manipulation 171
Data Manipulation Language 172
Create a Database 172
Create the Database 175
Check That the Database Exists 176
Insert Data 176
Adding Without Columns Identified 177
Adding Columns with Column Names 177
The Better Option 178
Inserting Multiple Rows 179
Incrementing Auto- Increment Out of Order 180
Inserting a Foreign Key 181
Update Data 182
Updating One Row 183
Preview Before You Update 184
Updating Multiple Rows 184
Disabling SQL_SAFE_UPDATES 185
Delete Data 187
Summary 191
Exercises 191
Exercise 9.1: Setting Up a Book List 192
Exercise 9.2: Updating Books 193
Exercise 9.3: Removing a Book 193
Lesson 10: Working with SELECT Queries 195
Setting Up a Database 196
Using the SELECT Keyword 199
Using Single- Table SELECT 199
Using SELECT * 201
Using the WHERE Clause 202
Filtering Numbers 205
Filtering Dates 207
Pattern Matching Text 207
NULL: The "Billion- Dollar Mistake" 209
Performing Calculations 211
Summary 213
Exercises 214
Exercise 10.1: Complaints 214
Exercise 10.2: Personal Trainer 215
Instructions 216
Activity 1 216
Activity 2 216
Activity 3 217
Activity 4 217
Activity 5 217
Activity 6 217
Activity 7 218
Activity 8 218
Activity 9 218
Activity 10 218
Activity 11 219
Activity 12 219
Activity 13 220
Activity 14 220
Activity 15 220
Activity 16 220
Activity 17 221
Activity 18 221
Activity 19 221
Lesson 11: Adding JOIN Queries 223
Starting with a Schema 224
Get Data from Multiple Tables 226
Use the JOIN Clause 228
Inner Join 228
Optional Syntax Elements 230
Omitting Table Names 230
Omitting the INNER Keyword 232
Multiple JOINs 232
INNER JOIN Limitations 235
OUTER JOIN: LEFT, RIGHT, and FULL 236
Replacing a NULL Value with Ifnull() 238
Projects Without Workers 239
Workers Without a Project 241
Self- JOIN and Aliases 243
Cross Join 246
Summary 247
Exercises 247
Exercise 11.1: User Stories 248
Exercise 11.2: Personal Trainer Activities 248
Activity 1 (64 Rows) 248
Activity 2 (9 Rows) 248
Activity 3 (9 Rows) 250
Activity 4 (35 Rows) 250
Activity 5 (25 Rows) 250
Activity 6 (78 Rows) 250
Activity 7 (200 Rows) 250
Activity 8 (0 or 1 Row) 250
Activity 9 (12 Rows) 250
Activity 10 (16 Rows) 251
Activity 11 (50 Rows) 251
Activity 12 (6 Rows, 4 Unique Rows) 251
Activity 13 (26 Workouts, 3 Goals) 251
Activity 14 (744 Rows) 251
Lesson 12: Sorting and Limiting Query Results 253
Using ORDER BY 254
Sort by a Single Column 254
Sort by Multiple Columns 256
Changing the Order of the Columns 258
Handling NULL 260
Using LIMIT 261
Using DISTINCT 263
Summary 264
Exercises 265
Getting Started: World Database 265
Generating an ERD for World 266
Guidelines 267
Exercise 12.1: What's in the World Database? 267
Exercise 12.2: Small Cities (42 rows) 267
Exercise 12.3: Cities by Region (4,079 rows) 267
Exercise 12.4: Speaking French (22 rows) 267
Exercise 12.5: No Independence (47 rows) 268
Exercise 12.6: Country Languages (990 rows) 268
Exercise 12.7: No Language (6 rows) 268
Exercise 12.8: City Population (232 rows) 268
Exercise 12.9: Average City Population (7 rows) 268
Exercise 12.10: GNP 269
Exercise 12.11: Capital Cities (4,079 rows) 269
Exercise 12.12: Country Capital Cities (239 rows) 269
Lesson 13: Grouping and Aggregates 271
Aggregate Functions 272
Using GROUP BY 273
Grouping and Multiple Columns 275
Adding DISTINCT 277
Using HAVING 279
SELECT Evaluation Order 281
Other Examples 281
Summary 283
Exercises 284
The Personal Trainer Database 284
Exercise 13.1: Number of Clients (1 row) 286
Exercise 13.2: Counting Client Birth Dates (1 row) 286
Exercise 13.3: Clients by City (20 rows) 286
Exercise 13.4: Invoice Totals (1,000 rows) 286
Exercise 13.5: Invoices More Than $500 (234 rows) 287
Exercise 13.6: Average Line Item Totals (3 rows) 287
Exercise 13.7: More Than $1, 000
Paid (146 rows) 287
Exercise 13.8: Counts by Category (13 rows) 288
Exercise 13.9: Exercises (64 rows) 288
Exercise 13.10: Client Birth Dates (26 rows) 288
Exercise 13.11: Client Goal Count (500 rows, 50 rows with no goal) 289
Exercise 13.12: Exercise Unit Value (82 rows) 289
Exercise 13.13: Categorized Exercise Unit Value (82 rows) 289
Exercise 13.14: Level Ages (4 rows) 290
Lesson 14: Pulling It All Together: Adding Data to the Vinyl Record Shop Database 291
Organize the Tables 292
Create a Script File 293
Inserting Data 294
What Is a Flat File? 294
Sql Insert 295
Inserting by Table Order 296
Adding by Field Name 297
On Your Own 298
Update Records 299
Import CSV Data 300
Set Up MySQL 301
Prepare the CSV File 303
Import the File 308
Command- Line Import 308
MySQL Workbench 310
Add Data to the Script 317
Test the Script 319
Wrap Up the Vinyl Music Shop Script 319
Summary 319
Lesson 15: Diving into Advanced SQL Topics 321
Adding Subqueries 322
Subqueries in the IN Operator 322
Subqueries for Tables 323
Subqueries for Values 325
Working with Views 326
Understanding Transactions 327
Transaction Example 328
Acid 329
Schema Optimization 331
Choosing Optimal Data Types 331
Indexing 333
B- Tree Indexes 334
Hash Indexes 335
Summary 336
Exercises 337
Exercise 15.1: Recent Tasks 337
Exercise 15.2: Before Grumps 338
Exercise 15.3: Project Due Dates 338
Exercise 15.4: The Work of Ealasaid Blinco 338
Exercise 15.5: Other Databases 339
Appendix A: Bonus Lesson on Applying SQL with Python 341
Appendix B: SQL Quick Reference 367
Index 375
