Using the Northwind Traders Sample Database

One fundamental problem with books about database management applications is the usual method of demonstrating how to create a "typical" database. You are asked to type fictitious names, addresses, and telephone numbers into a Customers table. Next, you must create additional tables that relate these fictitious customers to their purchases of various widgets in assorted sizes and quantities. This process is unrewarding for readers and authors, and few readers ever complete the exercises.

Therefore, this book takes a different tack. Access includes a comprehensive and interesting sample database, Northwind Traders. Rather than create a new database at this point, you create a new table as an addition to the Northwind Traders database. Adding a new table minimizes the amount of typing required and requires just a few entries to make the table functional. The new Personnel Actions table demonstrates many elements of relational database design. Before you proceed to create the Personnel Actions table, try the quick example of adding a new table to the Northwind Traders sample database in the following section.

Using the Table Wizard to Create New Tables

Access includes a variety of wizards that simplify the creation of new database objects. Wizards lead you through a predetermined set of steps that determine the characteristics of the object that you want to create. Access 97 includes a Table Wizard that you can use to create new tables based on prefabricated designs for 77 business-oriented and 44 personal-type tables. Many of the business-oriented table designs are based on tables contained in Northwind.mdb.

The Table Wizard serves as an excellent introduction to the use of Access wizards in general. Follow these steps to create a new Access table that catalogs a video collection:

1.	If the Employees table is open, close it by clicking the Close Window button to make the Database window active. Alternatively, click the Show Database Window button of the toolbar.
2.	Click the Tables button of the Database window if it isn't selected, and then click the New button to display the New Table dialog shown in Figure 4.13. Figure 4.13. The New Table dialog.
3.	Select Table Wizard in the list, and click OK to display the opening dialog of the Table Wizard. (If you select Datasheet view, Access creates a blank table with default fields; Design view creates a blank table and displays it in design mode, ready for you to add fields. The Import Table and Link Table wizards import databases and link external tables to a database, respectively.)
4.	Click the Personal option to display a list of tables for personal use in the Sample Tables list, and then use the vertical scroll bar to display the Video Collection entry in the list.
5.	In the Sample Tables list, click the Video Collection entry to display the predetermined set of field names for the new table in the Sample Fields list.
6.	Click the >> button to add all the fields from the Sample Fields list to the Fields in My New Table list. (The > button adds a single selected field from the Sample Fields list, the < button removes a single selected field from the My New Table list, and the ≪ button deletes all the fields in the My New Table list.) The Table Wizard's dialog now appears as in Figure 4.14. Figure 4.14. Adding fields to the new Video Collection table.
7.	Click the Next button to display the second Table Wizard dialog in which you select the name for your new table, and select how to determine the table's primary-key field. Accept the default table name or enter a name of your choice, and then click the No, I'll Set the Primary Key option. The Table Wizard's second dialog appears (see Figure 4.15).
8.	Click the Next button to display the dialog shown in Figure 4.16. This dialog enables you to select the primary-key field and its data type. The VideoCollectionID field is the logical choice for a primary key, and the AutoNumber field data type, which automatically creates a sequential number for the VideoCollectionID, is appropriate in this case. (The Table Wizard's Consecutive Numbers Microsoft Access Assigns Automatically to New Records option creates an AutoNumber type field.) Thus, you can accept the default values determined by the Table Wizard. Figure 4.15. Choosing a table name and how to determine the table's primary key. Figure 4.16. The Table Wizard's primary-key dialog.
9.	Click the Next button to continue with the next stage of the table design definition process. The Table Wizard's relationships dialog (see Figure 4.17) appears only if other tables already exist in the database in which you're creating the new table. Because almost every database consists of two or more related tables, the Table Wizard gives you an opportunity to define the relationships between tables. By default, the new table has no relationship to other tables in the database. In this exercise, you don't add any table relationships to the new Video Collection table.
10.	Click the Next button to finish designing the table. Access displays the final step of the Table Wizard, shown in Figure 4.18.
11.	Click the Modify the Table Design option, and then choose the Finish button to display your new table in Design mode, as shown in Figure 4.19.
12.	After you finish reviewing the design of your new table, click the Close Window button to close the table.

If you want to delete the Video Collection table from Northwind.mdb, click the Show Database Window button of the toolbar, click the Table button if the Tables list is not open, and then click the Video Collection entry (or whatever you named your table) in the Table list to select (highlight) it. Press Delete, and then click OK when the message box asks you to confirm the deletion. (You must close a table before you can delete it.)

Creating a Table Directly in Datasheet View

If you're a complete database novice and under pressure to create database tables immediately, Access enables you to create tables directly in Datasheet view. When you create a table in Datasheet view, Access displays an empty table with a default structure of 20 fields and 30 empty records. You then enter data directly into the table. When you save the table, Access analyzes the data you have entered and chooses a field type for each field that best matches the data you have entered. To create a table in Datasheet view, follow these steps:

1.	Close any open tables to make the Database window active. Alternatively, click the Show Database Window button of the toolbar.
2.	Click the Tables button of the Database window if it isn't selected, and then click the New button to display the New Table dialog (refer to Figure 4.13).
3.	Select Datasheet View in the list and click OK. Access displays an empty table, shown in Figure 4.20. By default, the new table contains 20 fields and 30 records.
4.	Type your data into the table, pressing Enter to move to the next field. To see how creating a table in Datasheet view works, type in the following mailing address (taken from the Employees table of Northwind.mdb) for Nancy Davolio in the first record of the new table: Ms. Nancy Davolio Sales Representative 507 20th Ave. E. Seattle WA 98122 USA As you enter this address, divide the information into several fields. For example, type Ms. and press Enter to move to the next field; type Nancy and press Enter to move to the next field, and so on, until you enter the complete address. The complete address should occupy nine fields when you are finished: title of respect, first name, last name, job title, street, city, state, ZIP code, and country.
5.	After you finish entering records into the table, click the Design View toolbar button to review the table's structure. Access displays the Save As dialog (shown in Figure 4.21), prompting you to enter a name for the new table. Alternatively, you can click the Close button to close the new table; in this case, Access first asks you to confirm saving changes to the table, and then asks whether you want to create a primary key for the table before prompting you for the table's name. For this exercise, answer No if asked to create a primary key; you learn more about primary keys later in this chapter. Figure 4.20. Creating a new table in Datasheet view. Figure 4.21. The Save As dialog.
6.	Enter a suitable name for your table in the Table Name text box of the Save As dialog, and then click OK to save the table. Access saves the table, including only fields in which you have entered data and only those records you entered. If you entered only one record with nine fields, Access saves a table with nine fields and one record. After saving the table, Access displays the new table in Design view, as shown in Figure 4.22.

When you first save a table created in Datasheet view, Access analyzes the records you entered on a field-by-field basis. If all the fields in a particular column of the table contain only numbers, Access assigns a Number data type to that field column. If none of the numbers contain a decimal fraction, Access gives the Number field a Long Integer subtype; otherwise, it assigns the field a Double subtype. If the fields in a single column of the table contain text or a mixture of numbers and text (either in the same field or different fields of the same column), Access assigns that field a Text data type. Text fields in the new table have a default Field Size setting of 50.

In Figure 4.22, notice that the fields are named in the format FieldN, where N corresponds to the order of the field in the table from left to right. Although Access provided a default of 20 fields, only those fields in which you actually entered data are saved. Pay attention to the data type of Field8; this is the field in which the mailing address's ZIP code was entered. Because this field contained only digits, Access has assigned it the Number data type. Because the number entered in this field contained no decimal fraction, it has the Long Integer subtype.

If you want to delete the table you just created from Northwind.mdb, click the Show Database Window button of the toolbar, click the Tables tab if the Tables list isn't open, and then click the name of the table you just created (the name you entered in step 6 of the preceding instructions) to select it. Press Delete, and then click OK when the message box asks you to confirm the deletion. (You must close a table before you can delete it.)

Adding a New Table to an Existing Database

The Northwind Traders database includes an Employees table that provides most of the information about the firm's employees that is typical of personnel tables. This chapter explains how to add a table called Personnel Actions to the database. The Personnel Actions table is a record of hire date, salary, commission rate, bonuses, performance reviews, and other compensation-related events for employees. Because Personnel Actions is based on information in the Employees table, the first step is to review the Employees table's structure to see how you can use it with your new table. Table structure is displayed in design mode. In the next chapter, "Entering, Editing, and Validating Data in Tables," you add validation rules to the Personnel Actions table and enter records in the table.

To open the Employees table in design mode, follow these steps:

1.	Close any Access document windows that you have open, and then click the Tables tab button in the Database window to display the list of tables in the Northwind.mdb database.
2.	Click Employees in the Database window, and then click the Design button. You also can open the Employees table by double-clicking the Database window entry and then clicking the Tables toolbar's Design View button.
3.	The Design grid for the Employees table appears. Maximize the document window to the size of your Access window by clicking the document's Maximize button.
4.	Close the Properties window, if it appears, by clicking its Close Window button. Alternatively, you can choose View, Properties. The menu command toggles the visibility of the Table Properties window. The Properties icon to the left of the Properties command has a sunken appearance to indicate that the Properties window is always visible in Table Design view.

At this point, your display resembles that shown in Figure 4.23.

The Table Design window displays the field names and data types and provides a third column for an optional description of each field in the table. This display is called a grid rather than a datasheet because the display doesn't contain data from a table. A scroll bar is provided, whether or not more fields exist in the table than the window can display. The Field Properties pane enables you to set additional properties of individual fields and briefly describes the purpose of each grid column and of the Field Properties entries as you select them. You cannot resize this pane.

One field is conspicuous by its absence: the Social Security number that most firms use in databases to identify their personnel. The EmployeeID field is an adequate substitute for the Social Security number for an example table because a unique sequential number (the AutoNumber field data type) is assigned to each employee. Click the Datasheet View button to display the data in the EmployeeID field, and then return to Design mode by clicking the esign View button.

Designing the Personnel Actions Table

Rather than add fields for entries (such as salary, commission rate, and bonuses) to the Employees table, you should place employee remuneration data in a table of its own, for the following reasons:

• Multiple personnel actions are taken for individual employees over time. If you add these actions to records in the Employees table, you have to create many additional fields to hold an arbitrary number of personnel actions. If, for example, quarterly performance reviews are entered, you have to add a new field for every quarter to hold the review information. In this situation, flat-file managers encounter difficulties.

• You can categorize personnel actions by type so that any action taken can use a common set of field names and field data types. This feature makes the design of the Personnel Actions table simple.

• You can identify employees uniquely by their EmployeeID numbers. Therefore, records for entries of personnel actions can be related to the Employees table by an EmployeeID field. This feature eliminates the necessity of adding employee names and other information to the records in the Personnel Action table. You link the Employees table to the Personnel table by the EmployeeID field, and the two tables are joined; they act as though they are a single table. Minimizing information duplication to only what is required to link the tables is your reward for choosing a relational, rather than a flat-file, database management system. (In an actual business's employee database, you would probably use the employee's Social Security number as the unique identifier for each employee and as the link to the Personnel Actions table.)

• Personnel actions usually are considered confidential information and are made accessible only to a limited number of people. Although Access enables you to grant permission for others to view specific fields, restricting permission to view an entire table is simpler.

The next step is to design the Personnel Actions table. Chapter 22, "Exploring Relational Database Design and Implementation," discusses the theory of database design and the tables that make up databases. Because the Personnel Actions table has an easily discernible relationship to the Employees table, the theoretical background isn't necessary for this example.

Determining What Information the Table Should Include. Designing a table requires that you identify the type of information that the table should contain. Information associated with typical personnel actions might consist of the following items:

• Important dates. The date of hire and termination, if applicable, are important dates, but so are the dates when the employer adjusts salaries, changes commission rates, and grants bonuses. You should accompany each action with the date when it was scheduled to occur and the date when it actually occurred.

• Types of actions. Less typing is required if personnel actions are identified by a code character rather than a full-text description of the action. This feature saves valuable disk space, too. First-letter abbreviations used as codes, such as H for hired, T for terminated, and Q for quarterly review, are easy to remember.

• Initiation and approval of actions. As a rule, the employee's supervisor initiates a personnel action, and the supervisor's manager approves it. Therefore, the table should include the supervisor's and manager's EmployeeID number.

• Amounts involved. Salaries are assumed to be paid bimonthly based on a monthly amount, bonuses are paid quarterly with quarterly performance reviews, and commissions are paid on a percentage of sales made by the employee.

• Performance rating. Rating employee performance by a numerical value is a universal, but somewhat arbitrary, practice. Scales of 1 to 9 are common, with exceptional performance ranked as 9 and candidacy for termination as 1.

• Summaries and comments. The table should provide for a summary of performance, explanation of exceptionally high or low ratings, and reasons for adjusting salaries or bonuses.

If you are involved in personnel management, you probably can think of additional information that the table might include, such as accruable sick leave and vacation hours per pay period. The Personnel Actions table is just an example; it isn't meant to add full-scale human resources development capabilities to the database. The limited amount of data described serves to demonstrate several uses of the new table in this and subsequent chapters.

Assigning Information to Fields. After you determine the types of information—called data entities or just entities —to include in the table, you must assign each data entity to a field of the table. This process involves choosing a field name that must be unique within the table. Table 4.7 lists the candidate fields for the Personnel Actions table. Candidate fields are written descriptions of the fields proposed for the table. Data types have been assigned from those listed in Table 4.8 in the following section.

Creating the Personnel Actions Table

Now you can put to work what you have learned about field names, data types, and formats by adding the Personnel Actions table to the Northwind Traders database. Table 4.8 shows the field names, taken from Table 4.7, and the set of properties that you assign to the fields. The text in the Caption column substitutes for the Field Name property that is otherwise displayed in the field header buttons.

You must set the paID field's Field Size property to the Long Integer data type, although you might not expect Northwind Traders to have more than the 32,767 employees that an integer allows. You must use the Long Integer data type because the AutoNumber field data type of the Employees table's EmployeeID field is a Long Integer. Later, the section Enforcing Referential Integrity explains why paID's data type must match that of the Employees table's EmployeeID number field.

To add the new Personnel Actions table to the Northwind Traders database, complete the following steps:

1.	Close the Employees table, if it is open, by clicking the Close Window button to make the Database window active.
2.	Click the Tables tab of the Database window, if it isn't selected, and then click the New button. Select Design View in the New Table dialog and click OK. Access enters Design mode and opens a blank grid where you enter field names, data types, and optional comments. By default, Access selects the grid's first cell.
3.	Enter paID as the first field name. Press Enter to accept the field name. The caret moves to the Data Type column; Access adds the default field type, Text.
4.	Press F4 to open the Data Type list. (You use the function keys rather than the mouse because your entries are from the keyboard.)
5.	Use the arrow keys to select the Number data type and press Enter to accept your selection (see Figure 4.24). Figure 4.24. Entering the paID field's Data Type.
6.	Press F6 to move to the Field Properties window's Field Size text box. Access has already entered Long Integer as the value of the default Field Size property. To learn more about the Field Size property, press F1 for help. Whenever you create a new Number type field, Access enters Long Integer in the Field Size property as the default. Because the paID field should be a Long Integer, you don't need to set the Field Size property for this field and can skip to step 8; continue with step 7 when you enter the other fields from Table 4.8.
7.	For Number data types, press F4 to open the Field Size list. Select from the list the appropriate field size value for the field, and press Enter.
8.	Press the down arrow to select the Format text box. You can press F1 for context-sensitive help on the Format property.
9.	Press F4 to open the Format list, select General Number from the list, and press Enter (see Figure 4.25).
10.	Press the down-arrow key three times, bypassing the Decimal Places and Input Mask properties, and select the Caption text box. You skip the Decimal Places property; Long Integers cannot have decimal fractions, so Decimal Places can remain set to Auto. You skip the Input Mask property because this field doesn't need an input mask.
11.	Enter ID as the caption and press Enter. ID is used as the Caption property to minimize the column width necessary to display the paID number. Figure 4.25. Assigning the General Number format to the paID field.
12.	Press F6 to return to the Table Design grid. The caret is located in the Description column. You complete the remaining properties for each field after completing the basic properties shown in Table 4.8. Tip You use descriptions to create prompts that appear in the status bar when you are adding or editing records in Run mode's Datasheet view. Although descriptions are optional, a good database design practice is to enter the field's purpose if its use isn't obvious from its Field Name or Caption property. You can skip the Caption property entries for now. After completing the basic steps described here, refer to Table 4.8. and enter the captions as a group.
13.	Press Enter to move the caret to the first cell of the next row of the grid.
14.	Repeat steps 3 through 12, entering the values shown in Table 4.8. for each of the eight remaining fields of the Personnel Action table. N/A (not applicable) means that the entry in Table 4.8 doesn't apply to the field's data type.

Your Table Design grid should now look similar to the one shown in (Figure 4.26). You can double-check your properties entries by selecting each field name with the arrow keys and reading the values shown in the property text boxes of the Field Properties window.

Click the Datasheet toolbar button to return to Datasheet view in Run mode to view the results of your work. You see the Must save table first. Save now? message. Click OK; a Save As dialog appears, requesting that you give your table a name and suggesting the default table name, Table 1. Type Personnel Actions, as shown in (Figure 4.27), and press Enter or click OK.

At this point, Access displays a dialog informing you that the new table does not have a primary key. You add primary keys to the Personnel Actions table later in this chapter, so click No in this dialog.

Your table appears in Datasheet view, with its first default record. To view all the fields of your new table, narrow the field name header buttons by dragging to the left the right vertical bar that separates each header. When you finish adjusting your fields' display widths, the Personnel Actions table appears in Datasheet view as shown in (Figure 4.28). Only the empty tentative append record (a new record that Access will add to your table if you enter values in the cells) is present. You have more property values to add to your Personnel Actions table, so don't enter data in the tentative append record at this point.