Database Principles (Second Edition)

Author: Dunping Translated
Publisher:
Publish Date: 2005-06-01
Features: This book is divided into two parts. Part I explains the basic concepts of databases, fundamental knowledge of the relational model, and structured query language (SQL). Part II provides a detailed introduction to data modeling, database design, and management. The content of the entire book is easy to understand and can be used as a foundational textbook for database-related majors in higher education institutions, or as a reference for software developers. Features of the Book:
● Practical and highly operational. Provides numerous examples and illustrations to help students thoroughly understand the principles of databases they have learned.
● Reinforces learned content through review questions, exercises, and practical projects at the end of each chapter, guiding students to apply their knowledge to real-world projects.
● Reorganized content based on the first edition: Chapter 2 provides four steps for normalization, making the process simpler and easier to understand. A new Chapter 7 introduces an overview of advanced topics such as Web database processing, database processing using XML, distributed database processing, OLAP, and data mining.
Colin Johnson is a product manager at a small manufacturing company in Seattle. Several years ago, Colin planned to build a database to track products in product packages. Initially, he used spreadsheet software to complete this task but couldn’t generate the reports he needed. Colin heard about Microsoft Access and hoped the software could solve his problem. After several days of trying, he found he couldn’t master it, so he bought some popular Access books and studied them diligently. However, he eventually gave up and hired a consultant who created an application that more or less met Colin’s requirements. After some time, Colin wanted to make some changes to the application, but he didn’t dare to try. Colin is a successful businessman who can take the initiative to achieve his goals. As an experienced Windows user, he can master Excel, PowerPoint, and numerous product-oriented applications through self-study. But when it comes to using Access to solve problems, he hits a wall. Colin believes, “I’m sure I can do it, but I don’t have more time.” Such events are very notable because they have occurred countless times in the past decade. Microsoft, Oracle, and other database management system (DBMS) vendors have recognized this issue and have invested millions of dollars to create better graphical interfaces, hundreds of multi-panel wizards, and numerous sample applications. Unfortunately, such efforts have not fundamentally solved the problem. In fact, many users are not clear about what functions the wizards can achieve. Once these users need to modify the data structure or components (such as forms and queries), they become overwhelmed, leaving them unable to cope. Without understanding the underlying structure, they can only struggle in vain, ultimately resulting in poorly designed databases and applications that fail to meet user requirements.
So why can users like Colin learn to use word processing software or spreadsheets but struggle to learn DBMS products? One major reason is that many people are unfamiliar with the basic concepts of databases. Everyone may know about paragraphs and margins but fail to understand the concept of relationships. Second, they might think that using a DBMS product is easier than understanding the database concepts themselves. “We just want to track some information, why is it so difficult to implement?” Without understanding the relational model, dividing a sales invoice into five separate tables before storing the data could confuse business users.
Basic Concepts
The current technological trend is that without learning the basic concepts, it is impossible to successfully utilize DBMS. Based on years of experience in developing databases for business users, I believe the basic concepts of databases mainly include the following:
● Basic concepts of the relational model
● Structured Query Language (SQL)
● Data modeling
● Database design
● Database management
Users like Colin (or students who will take similar roles) do not need to delve deeply into these topics like information systems specialists. Therefore, this book only introduces fundamental concepts that are crucial for users like Colin (those who create and use small databases). I have rewritten, simplified, and removed some content from the book Database Processing. However, in this book, I strive to discuss topics accurately without misleading readers. Even students who have taken more advanced database courses can still benefit from this book.
Concepts Independent of DBMS Products
This book assumes that students have not used any specific DBMS product. Although Appendix A introduces how to use Access, all concepts are applicable to DBMS products. In this way, students can understand the fundamentals of all databases—from small Access databases to large Oracle or DB2 databases. Moreover, this approach avoids a common problem. When introducing concepts and products simultaneously, students may confuse the concepts with product features and functions. For example, referential integrity constraints. When teaching basic theory, students know that in certain cases, a column value in one table must always be provided by a column value in another table. Students will also understand how this constraint appears in the context of a relational definition and how DBMS or applications enforce this constraint. If teaching specific DBMS, such as Access, students might learn how to select a checkbox in some cases and not in others. This can easily lead to students forgetting the basic theory of databases while learning product features. However, this does not mean that DBMS will not be used in this book. Instead, students can become familiar with these basic concepts by using enterprise-level DBMS products. This book assumes you will refer to other books or resources to learn how to use DBMS features and functions. Prentice-Hall provides extensive supplementary materials on Microsoft Access 2003, which can be used alongside this book.
Review Questions, Exercises, and Projects
It is crucial whether students can eventually apply what they have learned, so each chapter includes review questions, exercises, and three projects that run throughout the book. If students read and understand each chapter, they can find the answers to the review questions. Exercises require students to apply the concepts from each chapter to specific small problems or tasks. The project Garden Glory introduces a database for a partnership company that provides gardening services to individuals or businesses, discussing the development and use of the database. The second project James River Jewelers analyzes the database requirements for a retail store designed to support targeted buyers. The third project introduces the scheduling of experimental equipment in the Chemistry Department at Mid-Western University. All chapters and appendices of this book include these three projects. In each case, students are required to apply the knowledge learned in each chapter to the projects. Teachers will find more information about the use of these projects in the instructor’s manual, which can be obtained from the instructor’s resource center at www.prenhall.com/kroenke, where related databases and sample data are also available.
Improvements Over the First Edition
Compared to the first edition, this book has two main improvements. First, the discussion on normalization has shifted to an illustrative process for normalizing relationships. In the first edition of this book, normalization principles were introduced, but their application was left to students. The second edition’s Chapter 2 introduces four steps for students to use normalization. This change not only makes normalization simpler but also makes the principles of normalization easier to understand. Second, a new Chapter 7 introduces an overview of the following advanced topics:
● Web database processing
● Database processing using XML
● Distributed database processing
● OLAP and data mining
● Object-relational database management
The purpose of discussing these topics is only to introduce their essence to students, enhance their fundamental knowledge of databases, and encourage them to continue learning in important areas.
Main Content
This book includes 7 chapters and 1 appendix. Chapter 1 explains why databases are used, the components of databases, and methods for developing databases. Students will learn the uses of databases and their applications, the differences and advantages of databases compared to spreadsheet lists. Chapter 2 introduces the relational model, defines basic relational terms, and introduces the basic concepts of normalization, describing the normalization process. Chapter 3 covers basic SQL statements, describing fundamental SQL statements for defining data, such as SQL SELECT and data modification statements. This book does not introduce advanced SQL statements but focuses on core statements. The next three chapters discuss database design and management. Chapter 4 uses the entity-relationship (E-R) model to solve data modeling problems, including the need for data modeling, basic E-R terms and concepts, and a brief E-R modeling example application. Chapter 5 discusses database design, explaining the basic concepts of normalization. The data model from the example in Chapter 4 is converted into a relational design in Chapter 5. Chapter 6 discusses database management, covering the need for database management, an overview of concurrency control, security, and backup and recovery techniques. These topics are important for all databases, including personal single-user databases. In fact, these topics are even more important for private databases because they lack professional database administrators to ensure the execution of critical tasks. Finally, Chapter 7 introduces the advanced topics described earlier. Appendix A introduces Microsoft Access. This brief introduction helps students create simple databases and application components using Access technical documentation files.
Reflection and Outlook
Over the past 30-plus years, I have found that database and database application development is a pleasant and rewarding work. I believe that in the future, the number, size, and importance of databases will increase significantly, and greater achievements will be made in this field. I hope the concepts, knowledge, and techniques mentioned in this book will help students better develop database projects.