Introductory Relational Database Design for Business, with Microsoft Access

ISBN-13: 9781119329411 / Angielski / Twarda / 2018 / 328 str.

Eckstein, Jonathan; Schultz, Bonnie R.

Opis

Introductory Relational Database Design for Business with Microsoft Access takes a different approach to relational database design in that it develops understanding step-by-step, through numerous compact but realistic examples which build gradually in complexity. Readers will be able to achieve a thorough grounding in the technology and its applications and at every stage, the technology is presented through applications examples from business, as well as other fields, giving the reader a chance to concretely think through the details and issues that often arise.

By using this textbook, the student will be able to achieve:

• Lasting hands-on knowledge of a pervasive and useful technology
• Acquisition of immediately marketable skills
• Development of analytical thinking and problem solving.

Designing a database is a highly analytical skill, involving breaking down a situation into its critical components and clearly elucidating the relationships between these components. Learning such a skill develops the mind generally, fostering abilities in critical thinking and problem solving. Developing such abilities is an important component of any college education, regardless of students' fields of study.

Spis treści

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

O autorze

Jonathan Eckstein is a Professor in the MSIS (Management Science and Information Systems) department at Rutgers Business School, Rutgers University, USA. He has taught at Harvard and Princeton Universities and has a strong profile on Google Scholar with over 9000 citations.

Bonnie R. Schultz is a freelance writer and editor based in Princeton, New Jersey. She has worked as a technical writer in the software industry as well as a freelance journalist for various news publications.

Blurb

INTRODUCTORY RELATIONAL DATABASE DESIGN FOR BUSINESS
WITH MICROSOFT ACCESS

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.

Introductory 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 of 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 Microsoft Access 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.