Essential Database Concepts

Chapter 9 - Working with Multiple Tables
Mastering Crystal Reports 9
by Cate McCoy and Gord Maric
Sybex 2003

Crystal Reports can retrieve data from a variety of data sources. The most popular is a relational database, depicted in Figure 9.1, and indeed, even when Crystal is not interacting with a relational database, it treats most data sources as if they were relational.

Figure 9.1. Database components

The most universal definition of a database is that it is a collection of related or themed data. In non-technical terms, the phone book found in most homes can be considered a database because all the information in it shares a theme; the theme is the name, address, and phone number of an individual or a business. In electronic terms, a database is a collection of related information that is stored in a file on a computer.

Tables are used to store data about major topics, and the majority of databases consist of multiple tables. The tables themselves share a context and are therefore related. Business rules defined by database designers specify these relationships. For instance, in a database that tracks customer information, a business rule would define how many phone numbers would be tracked for each customer and whether a customer would be allowed to be added if their phone number was not known. These business rules form the basis for understanding how the database should be used for reporting.

Let’s take a brief look at the components that are important in understanding the database world:

DBMS Database management system. The term RDBMS is also common, where the R stands for relational. A DBMS can consist of many databases as well as the security and system information required to manage the data.

Database A container for storing tables that are related to one another. The container is stored as a single operating system file or set of files in a proprietary format specific to the database vendor. For example, a Microsoft Access database is stored in a file that has an .mdb file extension; these types of files can only be opened in Microsoft Access or using an access mechanism that Microsoft has provided.

Table A table is a two-dimensional (row and column) conceptual representation of how data is stored. Each table represents data elements that are closely related. For instance, in a table of customers, all the information in the table relates to customers; extraneous information about today’s weather in Hawaii would not be included.

Record A record is an individual row of data within a table. In the customer example mentioned earlier, in a table of all customers, each customer would be represented as a row of data; if there were five customers, there would be five records in the table, one for each customer.

Field A field is a column or attribute of data in a database record. In the customer example, for each customer, attributes such as first name, last name, and phone number would each be stored in columns and represent attributes of the customer.

Index An index is an optimized access path used to quickly retrieve information from a table. For instance, if the customer number is always retrieved when you access customer data, an index that sorts and manages the customer number will help retrieve the data in the most efficient manner.

OLAP Online Analytical Processing servers organize stored data into multidimensional hierarchies for real-time, high-speed data analysis. Key features of an OLAP approach to data include being able to slice and dice information by comparing one parameter to another in real time with drill-down and “what if?” analysis. The hierarchy, or cube, provides top-down analysis of data.

A database must ultimately store its data in an operating system file; however, each type of DBMS stores its information differently. Some databases store an entire database that consists of many tables as a single operating system file; Microsoft Access databases were mentioned earlier as an example of this. In contrast, a dBASE database is stored as a set of multiple operating system files where each table is stored in an individual file.

Use of content on this site is expressly subject to the restrictions set forth in the Membership Agreement

Conello © 2000-2003     Feedback