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