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
