Exploring Database Management Systems (DBMSs)

Most databases utilize a database management system (DBMS), which is software that allows users controlled access to the data. DBMSs range from small systems that run on personal computers to huge systems that run on mainframe computers. Computerized library systems, flight reservation systems, and any of the ERP packages mentioned in the introduction are all examples of DBMSs.

Users most often communicate with a DBMS by first logging into the system using an appropriate username and password. After successful logon, the user can then store, modify, or extract data from a database, usually through a graphical user interface (GUI).

A graphical user interface is a visual computer environment and consists of components such as a pointing device (like a cursor), icons, a display screen, and menus. Graphical user interfaces allow users to easily work with their database systems. Without a GUI, database users would have to use a command language (such as SQL) to work with their database information.

Figure 1-1 summarizes the workings of a database management system.

Figure 1-1: A model of a database management system (DBMS)

The Workings of a Relational Database

As mentioned previously, most DBMSs today utilize relational databases. Thus, relational databases are the type of data source that you'll most likely encounter in your report development.

In relational databases, all information is contained within two-dimensional data structures commonly referred to as a tables. The function of a table is similar to that of a folder within a filing cabinet.

Relational tables consist of columns (fields) and rows (records). We learn more about columns and rows in a moment.

Note

Fields are sometimes referred to as tuplets, and records are sometimes referred to as attributes. The terms are used interchangeably. This book sticks to "fields" and "records."

There are two main restrictions to consider with relational tables, which are as follows:

• A single table cannot contain duplicate column names. Each column name within a single table must be unique. There is no restriction involving two columns in two different tables having identical names.

• Tables may contain no rows (an empty table) but must contain at least one column.

Most database manufacturers also impose some maximum limit on the number of columns that a relational table may have, but usually not on the number of rows.

Note

These restrictions are part of the relational database standards created by the International Organization for Standardization (ISO) and the American National Standards Institute (ANSI).

In a relational database, tables are linked together through keys. Keys are database fields that provide the basic mechanism for matching records that reside in two or more tables. We learn more about keys in Chapter 6, "Linking Tables."

Database Records

A table's rows (better known as records) are a complete set of information and composed of multiple pieces of information (fields). Using the filing cabinet analogy, if a table is similar to a folder, then a record is one of the paper forms within that folder.

Figure 1-2 displays an example of a database record.

Figure 1-2: A database record

Tip

A record is similar to a row in a spreadsheet.

In Figure 1-2, the fourth record contains information about employee Burris (i.e., the employee's ID, name, department, and title). All these pieces of information together make up a record.

Database Fields

A table's columns (better known as fields) contain one unit of information for a particular record. For example, an employee record might contain information such as the employee's name, department, and title. Each of these pieces of information is considered a field.

Figure 1-3 displays examples of fields within a table.

Figure 1-3: Examples of fields within a table

Most database fields have certain attributes associated with them that define the type of data that is allowed within the field. Some fields might contain only numeric information, while other fields may contain only textual information.

For example, a database might contain customer billing amount data, which exists as a currency data type. Customer name information might also reside within a database; however, this data might exist as a text data type.

Tip

The implementation of data types often differs from database vendor to vendor. For example, Oracle, Microsoft, and Paradox all tweak the characteristics of their data types so that their product is unique in comparison to a competitor's product.

Check out the appropriate reference material provided with your database to verify the characteristics of your database's data types.

Data type comes into play when designing reports. For example, you can create summaries and totals from data that uses calculable numbers only (fields that consist of either a currency or number data type). Table 1-1 summarizes some of the basic data types recognized by Microsoft Access, the database you utilize in this book.

Table 1-1: Data types recognized by Microsoft Access

Data Type	Definition	Example
Text	Text and/or combinations of text and numbers, such as addresses. Any numbers that are a text data type do not require calculations (such as phone numbers or zip codes).	1600 Millhouse Drive
Memo	Lengthy text and numbers of virtually any length (often used for notes or descriptions).	Call only after 6 P.M.
Number	Numeric data used for mathematical calculations, except calculations involving money (see Currency type).	123
Date/Time	Displays both date and time information.	12/25/1997 02:31:00
Currency	Displays currency (monetary) values. This data type prevents the rounding off of values during calculations.	$5000.32
AutoNumber	Displays the unique sequential (incrementing by 1) number automatically inserted when a record is added.	6
Logical data (sometimes referred to as Boolean data)	Contains only one of two values, such as Yes/No, True/False, or On/Off.	Yes

We deal more with data types when working with summaries and formulas in Chapters 9 and 10.