|[ LiB ]|
The range of database applications runs the complete line from simplistic to dazzling. You'll find people using them to track personal possessions and companies using them to track every nuance of their businesses. In the simplest terms, a database stores data. In this section, you'll learn the basics of operating a database application:
How to create a new database
Different database objects
How to open and close a database
How to switch between object views
How to get help when you need it
This section introduces you to the database application by defining what a database is and the objects used to store and manipulate data.
Understand what a database is.
A database is a collection of persistent data. Microsoft Access, the database software used in this module, is a relational database management system (RDBMS) . A relational database is a collection of persistent data that's stored in multiple, but related , tables.
Understand how a database is organized in terms of tables, records, fields, and with field data types, field properties.
A database stores data in tables a collection of related data stored in rows and columns . The terms row and column refer to the physical structure of the table, as illustrated in Figure 6.1. The data in a column represents a field , the smallest unit of data in the database. A row is the combination of columns for one entity, or record , where the record is the actual data. You'll often see the terms field and column used interchangeably.
Each field in the database is described by its data type (which limits the data that can be stored in that field) and its properties (such as its length or its description). You'll learn more about field data types and field properties later in this chapter.
Understand what a primary key is.
A primary key is the field or fields that uniquely identify each record. Primary keys are used to associate tables that contain related data. (You'll learn a little more about that later.)
For example, suppose your database contains a table of customers. Each customer is described by the various fields in the table: customer number, customer name, address, contact name , and so on. Because each customer has a unique customer number, the customer number field can be the primary key for the table.
The primary key field can be based on an AutoNumber field, which generates a unique value for each record automatically. Or you can use one or more fields of data. When choosing a primary key, keep the following rules in mind:
The primary key values must be unique.
A primary key value can't be null. Within this context, null means an empty field.
The primary key must exist when the record is created and stored.
Understand what an index is.
An index is an internal structure that stores key values which act as pointers to the data. The database uses indexes to search and sort data. The structure and process are similar to those for the index in a book. Instead of thumbing through the entire book hoping to find a particular topic, you refer to an index and find the exact page or pages that cover the topic.
Understand the purpose of relating tables in a database.
By definition, a relationship is an association. Within the context of a relational database, a relationship is the association of two tables based on the primary key value. That means the primary key value is stored in both tables. With the primary key table, the value is unique and defines just one record in that table. For instance, the primary key value for a customer named Alfreds Futterkiste might be ALFKI (in the Customers table in Northwind, a sample database that comes with Access). That value points only to orders for Alfreds Futterkiste in the related Orders table. Furthermore, the Customers table can't contain another primary key value of ALFKI.
How does that help you find all the order records for Alfreds Futterkiste that are stored in a completely separate table? The primary key value identifies those records. When you enter a new order record, you must associate that new record to Alfreds Futterkiste by entering that customer's primary key value, ALFKI, in the record along with the new order data in the Orders table. That's what's known as a foreign key value. In short, a foreign key is another table's primary key value. Unlike a primary key value, a foreign key can be duplicated .
For example, consider Figure 6.2, showing the Customers and Orders tables from the Northwind sample database. In the Customers table, the CustomerID field is the primary key. In the Orders table, the CustomerID field is the foreign key. You can see that if you want to know anything about the customer for an order (say, the address or contact information), you can use the CustomerID from the foreign key field to figure out which row of the Customers table contains the full info .
The third window in Figure 6.2 is a query window. The query in this case displays data from both the Customers and Orders tables. Because of the primary keyforeign key relationship, Access knows how to combine information about the two tables into a single view. (You'll learn more about queries later in this chapter.)
Understand the importance of setting rules to ensure relationships between tables are valid.
Related tables can be vulnerable if you don't apply rules to protect them. Because both tables contain the same value in the form of the primary and foreign key values, you must take measures to ensure that those values are entered appropriately. These rules protect the integrity of your data. Integrity , within the context of a relational database, is simply a set of rules that maintain the relationships and, consequently, the data. One of these integrity rules is known as referential integrity , which simply stated is as follows : A foreign key value must match a primary key value in a related table or be null (empty). Now, for the most part, you do not want a null foreign key value unless you have very specific reasons; ordinarily, we recommend against using null foreign values.
For example, suppose your database includes a table of publishers and a table of books. Your data integrity rule would be that each book belongs to a particular publisher. In this context, the primary key of the publisher's table (say, the publisher's name) would appear as a foreign key in the books table. You'll learn more about referential integrity later in this chapter.
Now that you know what a relational database is and does, it's time to launch Access and start using it.
Open (and close) a database application.
As with all Office applications, you have a number of ways to launch Access:
Once launched, Access displays a dialog box that allows you to open an existing database or create a new one. Or simply click Cancel to open just the application.
Open, log on to an existing database.
There are a number of ways to open an existing database. If it is available, you can click a shortcut icon that launches Access and opens a specific database. Or with Access open, you can click the Open button on the Database toolbar, choose Open from the File menu, or press Ctrl+O. All three of these methods display the Open dialog box shown in Figure 6.3. Using the Look in control, navigate to the appropriate folder. Then, either highlight the database in the file list or enter the database in the File Name control, and click Open.
Create a new database.
You can build a database from scratch or you can base a new database on a template. With Access open, choose New from the File menu, select Database from the General tab, and then click OK. When Access displays the File New Database dialog box, indicate the folder where you want to store the new database using the Look in control, enter a name in the File Name control, and then click Create.
Save a database to a location on a drive.
Oddly enough, saving a database to a particular drive and folder is part of the creation process. Specify where you want to store the database when you create it. However, you can move a database via the Explorer window. (See Chapter 3, "Using the Computer and Managing Files," for details.)
Use available Help functions.
Access offers an extensive Help section that can help you learn how to use the software. Choose Microsoft Access Help (or press F1) from the Help menu to display the Help window (or launch the Office Assistant) to do the following:
Close a database.
To close an open database, choose Close from the File menu, or click the Windows Close button on the Database window toolbar. To close Access, click the Windows Close button on the application window or choose Exit from the File menu.
Access is a busy program with a number of objects you can view in various modes and a fistful of toolbars and options you can apply. Knowing your way around the Access window and its many objects will make your work easier.
Change between view modes in a table, form, report.
When an actual database file is current, Access displays the Database window. If the database is new and contains no objects, the window is empty, except for the shortcut items (that launch objects and wizards). If you based the new database on a template, or if you're working with an existing database, the window contains objects, similar to those shown in Figure 6.4. Simply click the Shortcut buttons on the Objects toolbar to view the ready-to-use objects.
The Database window houses all the objects in a database. All these objects are really just user -interface elements that allow you to interact with the data. Tables store the actual data. Forms allow you to view, add, and modify data. Reports allow you to arrange data in a meaningful format, which you can then print and share.
Each object type has two viewing modes. You use design view to create and modify the object properties. To actually interact and view the data, you use one of the many object views: Datasheet (tables), Form, and Print Preview (reports).
To access a table, form, or report, click the appropriate Shortcut button on the Objects toolbar (to the left of the Database window). Doing so updates the list of objects accordingly . To open and view the object and any data it might display, simply double-click the item. Or select the item and click the Open button on the Database Window toolbar (see Figure 6.4). To open the object in design view, select the item and click the Design button on the Database window toolbar.
Display, hide built-in toolbars.
Access (normally) displays the Database toolbar and the standard menu bar when it launches. There are a number of toolbars available, and you'll notice as you use Access that the toolbar buttons and menu bar commands change according to the view and object that are current.
To display toolbars, select Toolbars from the View menu to display the list of available toolbars shown in Figure 6.5. Checked items are already displayed. To display an unchecked item, simply select the toolbar from the resulting list. To hide a toolbar, repeat the process, but select a checked item to uncheck it. The available toolbars change according to the current object and view.
|[ LiB ]|