KIMBERLY A. WEISS is Senior Manager of Curriculum Operations for Wiley Edge. She has extensive experience developing interactive instructional content for a wide variety of learners.

HAYTHEM BALTI, PhD, is Associate Dean at Wiley Edge (formerly mthree), a software development and data science education platform.

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...