Understanding Relational Database Query Languages

About

Features

• Extensive examples.

  • Provide students with hands-on applications and practice of concepts. Ex.___

• Case studies.

  • Enable students to examine realistic examples from requirements description, to conceptual design and implementation. Shows students side-by-side comparison of the four query languages. Ex.___

• Win RDBI software—Available on the author's website. Allows students to interactively experiment with Relational Algebra, Domain Relational Calculus, Tuple Relational Calculus, and Structured Query Language expressions.

  • Provides students with an “under the hood” look at how databases function, giving them insights about how to build better databases. Ex.___

• Relational algebra covered—Fundamental relational algebra operators are defined using a combination of set theory and diagrams; additional operators defined in terms of the fundamental operators.

  • Teaches students about this procedural query language. Ex.___

• Relational calculus—Covers both the domain (DRC) and tuple (TRC) relational calculus languages. Truth tables help to illustrate the correct declarative specification of the division operator.

  • Teaches students about these declarative query languages. Ex.___

• Emphasis on SQL.

  • Introduces students to industry standard query language in terms of its mathematical functions and the formal query languages on which SQL is based. Ex.___

• Discussion of the query languages—Provided by the popular, PC-based database product Microsoft® Access™.

  • Enables students to relate theory to practice. Ex.___

Description

This text functions as a dynamic supplementary text in Introductory Database Systems courses as well as a stand-alone reference for professionals. This invaluable learning tool provides an understanding of the industry-standard query language SQL. Using an appropriate mix of underlying theory, concepts, and hands-on activities with numerous examples, this text is designed to help students or professionals understand how relational database query languages work.

FEATURES/BENEFITS

• Hands-on Approach
• Win RDBI software available on the author's web site allows readers to interactively experiment with Relational Algebra, Domain Relational Calculus, Tuple Relational Calculus, and Structured Query Language expressions appearing in the book.
• SQL Foundations
• Introduces readers to the industry standard query language in terms of its mathematical foundations and the formal query languages on which SQL is based.
• Case Studies
• Enable students and professionals to examine realistic examples from a requirement description to conceptual design and implementation. Provides a side-by-side comparison of the four query languages.
• Relates Theory to Practice
• Examines the query languages of the popular PC-based database product Microsoft® Access™, including Access QBE (Query By Example) and Access SQL.


Sample Content

Table of Contents

 1. The Relational Data Model.


 2. Relational Algebra.


 3. Domain Relational Calculus.


 4. Tuple Relational Calculus.


 5. SQL.


 6. Case Studies.


 7. Microsoft Access.


Appendix: WinRDBI

Preface

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

1. 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.
2. 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.
3. 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.
4. 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.
5. 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.
6. 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.
7. 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:
http://www.prenhall.com/dietrich

The WinRDBI educational tool and the author's website for the book is at the URL:
http://www.eas.asu.edu/~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.edu

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.

Updates

Submit Errata