This invaluable learning tool provides an understanding of the industry-standard query language SQL. Using an appropriate mix of underlying mathematical formalism and hands-on activities with numerous examples, the book is designed to help users grasp the essential concepts of relational database query languages. The book provides a complete presentation of the relational data model, relational algebra, domain and tuple relational calculus and SQL, with case studies and Microsoft assess. For individuals in computer science, information services and industrial engineering interested in gaining an understanding of the foundations of industry SQL.
Preface
The goal of this book is to help the reader understand relational database query languages, including the industry-standard structured query language (SQL) and the query languages of the popular PC-based database product Microsoft Access. The book also covers the formal languages upon which SQL is based, such as the relational algebra and relational calculus languages. The relational algebra is a procedural language having specific operators to apply to relations for the retrieval of data. Most educators agree that an understanding of relational algebra is critical to understanding SQL. The relational calculus languages, like SQL, are declarative query languages, indicating the properties of the data to be retrieved. There are two types of relational calculus query languages: domain relational calculus (DRC), where a variable ranges over a domain or column of a table, and tuple relational calculus (TRC), where a variable ranges over a tuple or row of a table. This book covers the DRC language, because of the use of DRC-like languages for the specification and optimization of data mining techniques and related technologies that integrate database and artificial intelligence. The book covers the TRC language, because the basic SQL query expression is a syntactic variation of an expression in TRC. Again, an understanding of TRC provides a strong foundation for understanding the industry-standard query language SQL.
The book is based on the premise that understanding comes from a wealth of examples and a hands-on approach. The various query languages are explored through not only detailed examples, but also side-by-side comparisons of the languages on various types of queries over several database enterprises. This example-based approach is reinforced by the inclusion of an appendix that describes the WinRDBI educational tool, which is available on the Web. (See Appendix A.) The WinRDBI educational tool provides a graphical, interactive environment for a hands-on approach to understanding the query languages. The queries marked with a check in the book are provided electronically on the companion website so that the reader can check the results of the queries using the tool.
The targeted audience of the book ranges from a student that is enrolled in an introductory database management course to the professional engineer that wants to understand the foundations of SQL. Students in an introductory database management course, which is offered in many disciplines including computer science, computer information systems and industrial engineering, will find this book useful as a supplemental text. However, the book is self-contained, so that it is accessible to the professional engineer faced with using relational database technology. For example, the first chapter of the book includes an introduction to the relational data model, an overview of the conceptual design of a database based on the requirements of an enterprise, and a description of mapping the conceptual design to the relational data model. The assumed prerequisites include the knowledge of set theory and the desire to understand relational database query languages. CHAPTER GUIDE THE RELATIONAL DATA MODEL
This chapter is useful to all readers, since it introduces the terminology and notation of the text, and the example enterprise used throughout the book. RELATIONAL ALGEBRA
Most educators agree that understanding relational algebra is critical to understanding any relational database query language. The examples presented in this chapter form the basis of the exposition of the other query languages. DOMAIN RELATIONAL CALCULUS
The relational calculus languages provide the foundation for declarative query languages, such as SQL, that state the properties of the data to be retrieved. The DRC language also forms the foundation of declarative languages used for the integration of database and artificial intelligence technologies. The DRC language uses variables that range over a column of a table. This approach provides practical shortcuts that are not available in its sibling TRC language, where a variable ranges over the row of a table. Most students introduced to the DRC and TRC languages prefer the paradigm offered by DRC, although there are always a few students who prefer the procedural paradigm offered by relational algebra. You are cheerfully invited to read this chapter and the TRC chapter that follows and make up your own mind. TUPLE RELATIONAL CALCULUS
Since the basic SQL query expression is a syntactic variation of a TRC expression, it is recommended to cover the TRC language before SQL. The TRC chapter introduces tuple variables, which are variables that range over the tuples or rows of a relation. In SQL, these tuple variables are called table aliases, or range variables. SQL
This chapter explores SQL in the context of its mathematical foundations. The basic query expression in SQL is mapped to its equivalent relational algebra expression and its similarity to an expression of the tuple relational calculus language is illustrated. The examples over the working enterprise use basic features of the SQL language to reinforce the relationship between SQL and the formal query languages. For pragmatic reasons, SQL is more powerful than its formal counterparts on which it is based. These additional features of SQL are illustrated by example. Since the SQL standard evolved to provide more than a query language, this chapter also covers the standard's data definition language, data manipulation language, and data control language for integrity and security. CASE STUDIES
This chapter provides the opportunity for the reader to work through three case studies starting from a requirements description, to its conceptual design, its relational data model implementation, and then a side-by-side comparison of the four query languages using relevant queries over the case study. MICROSOFT ACCESS
The goal of this chapter is to relate theory to practice by covering the query languages provided by the popular, PC-based database product Microsoft Access.
APPENDIX A. WINRDBI EDUCATIONAL TOOL
This appendix chapter provides an overview of the WinRDBI educational tool, describing the assumptions of the query languages recognized by the educational tool (relational algebra, domain relational calculus, tuple relational calculus, and SQL). FOR MORE INFORMATION
The Prentice Hall companion website for this book is at the URL:
prenhall/dietrich
The WinRDBI educational tool and the author's website for the book is at the URL:
eas.asu/~winrdbi
An instructor resource CD is available, which contains supporting material for the book including solutions to chapter exercises for WinRDBI.
The author's preferred email address for correspondence regarding the book is:
swdietrich@asu ACKNOWLEDGMENTS
There are many people who are involved in the process of writing and publishing a book. I appreciate the thoughtful guidance in the writing of the book that was provided by the reviewers: Chris Eagle, Ibrahim Imam, William Perrizo, Munindar Singh, Jennifer Widom, and Dawn Wilkins. My thanks to those at Prentice Hall who warmly welcomed me as a member of their family of authors: Alan Apt, Toni Holm, Amy Todd, Lakshmi Balasubramanian, Jennie Burger, Jayne Conte, David George, Vince O'Brien, Cindy Szollose and Jake Warde.
My involvement in the design and development of the WinRDBI educational tool provided the motivation for writing this book. The following students contributed to the development of WinRDBI: Eric Eckert, Changguan Fan, Chien-Ho Ho, Ana Hun, Kevin Piscator, Rick Rankin and Sarah Simons. Rick deserves a special acknowledgment since he maintained the software for some time after he graduated. I also want to thank Amzi!, inc. for their assistance, since WinRDBI utilizes the Amzi! Prolog and Logic Server as the basis of its underlying implementation.
The task of writing a book requires a long-term commitment of time and energy. I want to express my sincere gratitude to my family and friends for the encouragement and understanding.
