[eBook Code] Introductory Relational Database Design for Business, with Microsoft Access (eBook Code, 1st)

Preface ix

1 Basic Definitions and Concepts 1

Basic Terms and Definitions 1

Types of Information Systems 3

2 Beginning Fundamentals of Relational Databases and MS Access 7

Beginning Fundamentals of MS Access 8

A “Hands‐On” Example 9

Introduction to Forms 15

Another Method to Create Forms 18

Introduction to Reports 22

Introduction to Queries 26

Common Datatypes in MS Access 32

Exercises 34

3 Introduction to Data Management and Database Design 43

Introduction to Data Management 43

General Data Management Issues 43

Classifying Information Systems Tasks: Transaction and Analytical Processing 45

What is Wrong with Just One Table? 46

Repeating Groups 47

An Illustration of Multiple Tables and Foreign Keys 48

4 Basic Relational Database Theory 53

Tables and Their Characteristics 53

Primary Keys and Composite Keys 55

Foreign Keys and Outline Notation 57

Creating Entity‐Relationship (ER) Diagrams 59

Functional Dependency 60

Dependency Diagrams 61

Partial Dependency 62

Transitive Dependency 63

Database Anomalies 63

What Causes Anomalies? 64

How to Fix Anomalies 65

Good Database Design Principles 66

Normalization and Zip Codes 67

Expanding the Customer Loans Database 68

DVD Lending Library Example without Loan History 71

The DVD Lending Library Example with Loan History 75

Subtypes 78

Exercises 85

5 Multiple Tables in Access 95

The Relationships Window and Referential Integrity 95

Nested Table View 100

Nested Forms 101

Queries with Multiple Tables 103

Multiple Joins and Aggregation 108

Personnel: Database Design with Multiple Paths between Tables 115

Creating the Database in Access using Autonumber Keys 119

A Simple Query and a Different Way to Express Joins in SQL 120

Exercises 123

6 More about Forms and Navigation 127

More Capabilities of Forms 127

Packaging it Up – Navigation 132

Exercises 135

7 Many‐to‐Many Relationships 139

Focus Groups Example 139

The Plumbing Store: Many‐to‐Many with an Additional Quantity Field 143

Hands‐ On Exercise and More About Queries and SQL 146

Project Teams: Many‐to‐Many with “Flavors” of Membership 154

The Library 159

Exercises 163

8 Multiple Relationships between the Same Pair of Tables 171

Commuter Airline Example 171

The College 177

Sports League Example 181

Multiple Relationships in Access 183

Exercises 184

9 Normalization 189

First Normal Form 189

Second Normal Form 192

Third Normal Form 194

More Normal Forms 197

Key Factors to Recognize 3NF 198

Example with Multiple Candidate Keys 198

Normalizing an Office Supplies Database 198

Summary of Guidelines for Database Design 202

Exercises 203

10 Basic Structured Query Language (SQL) 215

Using SQL in Access 215

The SELECT … FROM Statement 215

WHERE Conditions 217

Inner Joins 218

Cartesian Joins and a Different Way to Express Inner Joins 221

Aggregation 228

GROUP BY 231

HAVING 237

ORDER BY 238

The Overall Conceptual Structure of Queries 240

Exercises 243

11 Advanced Query Techniques 253

Outer Joins 253

Outer Joins and Aggregation 256

Joining Multiple Records from the Same Table: AS in the FROM Clause 260

Another Use for AS in the FROM Clause 262

An Introduction to Query Chaining and Nesting 262

A More Complicated Example of Query Chaining: The League Standings 265

Subqueries and Back to the Plumbing Store Database 270

Practical Considerations and “Bending the Rules” Against Redundancy 274

Exercises 275

12 Unary Relationships 279

Employee Database 279

Setting Up and Querying a Unary Relationship in Access 283

The Course Catalog Database 291

Exercises 294

Further Reading 301

Index 303