A hands-on beginner’s guide to designing relational databases and managing data using Microsoft Access
Relational databases represent one of the most enduring and pervasive forms of information technology. Yet most texts covering relational database design assume an extensive, sophisticated computer science background. There are texts on relational database software tools like Microsoft Access that assume less background, but they focus primarily on details of the user interface, with inadequate coverage of the underlying design issues of how to structure databases. Growing out of Professor Jonathan Eckstein’s twenty years’ experience teaching courses on management information systems (MIS) at Rutgers Business School, this book fills this gap in the literature by providing a rigorous introduction to relational databases for readers without prior computer science or programming experience.
Relational Database Design for Business, with Microsoft Access helps readers to quickly develop a thorough, practical understanding of relational database design. It takes a step-by-step, real-world approach, using application examples from business and finance every step the way. As a result, readers learn to think concretely about database design and how to address issues that commonly arise when developing and manipulating relational databases. By the time they finish the final chapter, students will have the knowledge and skills needed to build relational databases with dozens of tables. They will also be able to build complete Microsoft Access applications around such databases. This text:
- Takes a hands-on approach using numerous real-world examples drawn from the worlds of business, finance, and more
- Gets readers up and running, fast, with the skills they need to use and develop relational databases with Microsoft Access
- Moves swiftly from conceptual fundamentals to advanced design techniques
- Leads readers step-by-step through data management and design, relational database theory, multiple tables and the possible relationships between them, Microsoft Access features such as forms and navigation, formulating queries in SQL, and normalization
Introductory Relational Database Design for Business, with MicrosoftAccess is the definitive guide for undergraduate and graduate students in business, finance, and data analysis without prior experience in database design. While Microsoft Access is its primary “hands-on” learning vehicle, most of the skills in this text are transferrable to other relational database software such as MySQL.
Table of Contents
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
