Database Terminology

3 4

To complete this chapter, this section provides a list of some of the more important database terms, along with a brief description of each one. Some definitions also include a figure to help explain the term. These terms will appear in later chapters. If you're familiar with basic database terminology, feel free to skim this section and move on to the next chapter.

Application Generally speaking, an application is a program that performs a specific task or set of related tasks, such as Microsoft Word or Access. A database application is a complete set of database objects (tables, queries, forms, reports, macros, modules, and data access pages) that are linked so as to automate a task or set of tasks. The main difference between a simple database (containing many of these same objects) and a true database application is in the programming that automates the application to make it easier to use.

Automation Formerly OLE Automation. A Microsoft technology based on the Component Object Model (COM) that allows one application to work with objects belonging to another application.

Automation client An application that works with other applications' objects using Automation.

Automation server An application that lets other applications work with its components through Automation. Since most of the applications included in the current versions of Microsoft Office are both Automation clients and Automation servers, you can easily control these applications programmatically. For example, you can write code to create Word documents from Access, save Outlook tasks to an Access database, and so on.

Bound object An object, such as a form, report, or control, that is bound to data from a table, query, SQL statement, or recordset. Users can add, delete, and edit data in most bound objects.

Client-server application An application divided into a client component, containing an interface that's typically presented to the user, and a server component, which performs some type of processing on a server. For Access, a client-server application generally has SQL Server on the server side, with an Access front end as the client component. Splitting a database into a front end and a back end doesn't make it a true client-server database (see "Front-end/back-end database"). To be considered a true client-server database, a substantial amount of processing needs to be done on the server, such as creating a set of results from a complex query to send back to the client.

Column Database tables are divided into rows and columns. A column contains all the data in the table for a particular field.

Control An interface object on a form-for example, a text box or label.

Database A collection of information stored in an organized manner. An Access database is a collection of database objects (tables, forms, queries, reports, macros, modules, and data access pages) related to a specific topic, stored as a file with an MDB extension. Note that the term database might be used differently in other database applications (such as dBASE), typically to reference just a table or a collection of tables.

Database Management System (DBMS) A layer of software that manages the interface to the actual data.

Event Any significant action that can be detected by a system. Example events include clicking a button, moving the mouse, or pressing a key on the keyboard. You can write code to run when an event occurs. This code is called an event handler or event procedure.

Field A category of information in a database record. For example, in a database table that contains customer information, each record contains information about a single customer. The categories of information in the customer record are fields such as ContactName, ContactTitle, StreetAddress, and so on.

Flat-file database A simple type of database that places related information in a single table. Unlike a relational database, a flat-file database doesn't support relationships to link tables, and thus duplicate data (such as customer names and addresses) is typically stored in multiple tables or in multiple records in the same table.

Foreign key In a one-to-many relationship between tables, the field in the "many" table that links to the primary key field in the "one" table.

Front-end/back-end database Division of a database into a back-end database, containing only the data tables, and a front-end database, containing the interface elements (forms, reports, data access pages, and other supporting components) to allow updating the database's interface without disturbing the data. An Access database that has been split into a front-end/back-end database is not necessarily a client-server database.

Inner join Also called an equi-join. A join that combines records from linked tables that have matching values in the linking field in both tables-for example, records in the Customers table and the Orders tablethat have a CustomerID field equal to 55.

Interface object Database objects (such as forms and reports) that let users view and modify data in tables. Figure 3-13 shows a form and a report from the Northwind sample database.

Figure 3-13. A form and report are examples of interface objects in the sample Northwind database.

Join A query operation that combines records from two or more tables. Joins can be inner, outer, or self-joins.

Key field Also called a primary key. A table field that uniquely identifies each record, such as a customer ID or a Social Security number.

Linked table A table that links to data stored outside the current Access database, either in another Access database or in some other data source, such as an Excel worksheet or a comma-delimited text file.

Local table A table whose data is stored in the current Access database.

Lookup field A field that displays and stores values from a linked field in another table, generally using a combo box for selecting the value. Figure 3-14 shows a lookup field in the Order Details table in the Northwind database.

Figure 3-14. A lookup field in the Northwind database is used to select a value from another table.

Many-to-many relationship A relationship between two tables in which a record in each table can be linked to multiple records in another table, such as the relationship between students and classes-a student has many classes, and a class has many students. A many-to-many relationship is set up by linking the key fields in the two tables to be linked to a third table containing only the key fields.

Normalization Designing a database so that there's no duplication of data in different tables, other than linking fields. There are five levels (normal forms) of normalization; Access databases generally are normalized to the third normal form.

Object A component of an application, especially as represented in the application's object model for use in Automation code.

Object model A group of objects organized in a logical or hierarchical fashion that lets you work with the objects programmatically. Figure 3-15 shows some of the top-level objects in the Access object model diagram.

Figure 3-15. The Access object model shows Access components you can work with in Automation code.

OLE DB A technology based on COM for connecting to data in various sources, including nonrelational data stores such as Microsoft Exchange folders.

One-to-many relationship A relationship between two tables in which a single record in the "one" table can be linked to multiple records in the "many" table, such as customers and orders. The tables are linked on the primary key field in the "one" table and the foreign key in the "many" table.

One-to-one relationship A relationship between two tables in which a record in one table can have only one related record in the other table. One-to-one relationships are typically used when certain information needs to be kept more secure than other information, or is only rarely needed on forms or reports. For example, a table of employee name and job description data could be linked one-to-one with a more sensitive employee salary data table.

Open Database Connectivity (ODBC) A standard for connecting to data in various formats. This format is not used as much as it used to be because of the increasing use of Automation and OLE DB.

Outer join A join that returns records from both tables. Access offers two outer join types in the interface: a left outer join will return all the records in the table referenced on the left, and a right outer join will return all the records in the table referenced on the right. Available only in SQL, a full outer join will return all the records from both tables.

Primary key A field containing a unique value for each record, such as a customer ID or a Social Security number. The primary key field is used to link to other tables in one-to-many, many-to-many, and one-to-one relationships.

Project In Access, a collection of database objects (forms, reports, and data access pages) used as a front end to data in SQL Server databases and stored as an ADP file.

Record A set of related data (fields) making up a row in a table—for example, information about a customer.

Referential integrity A set of rules that prevent the addition, deletion, or modification of data in a table when the changes would cause a problem with a relationship. For example, adding an order record without a matching customer record might generate a referential integrity error.

Relational Database Management System (RDBMS) A database management system in which data is organized into tables and relationships can be established between tables.

Row All the information about a particular entry in a table, such as a customer or book.

Self-join A join that returns records based on other records in the same table.

Structured Query Language (SQL) A standardized data query language. As you create a query in Design view, Access automatically creates a corresponding SQL statement. Figure 3-16 shows a query in Design view, along with the corresponding SQL statement in SQL view.

Figure 3-16. Access creates a SQL statement automatically as you design a query in Design view.

Table An Access (or other) database object that contains data. A table is arranged in rows (records) and columns (fields). A Customers table, for example, stores information about customers, one customer per row; customer fields (columns) contain information such as customer ID, contact name, contact title, and address. In Access databases, a table can be local or linked.

Unbound object An object, such as a form or control, that is not bound to data.

Validation rule A rule that defines whether data can be stored in a table field or accepted into a form control.

Visual Basic for Applications (VBA) A subset of the Microsoft Visual Basic programming language used in Microsoft Office and other applications.