Creating a Database

In this lesson, you learn about designing a database application. You create a new database and database tables, and learn about defining data fields.

Contents

• Designing a Database Application

• Alpha Five File Types

• Creating a New Database

• Creating Tables

• Using the Table Editor

• Defining Data Fields

• Indexing Data Fields

• Creating Tables for AlphaMovies

• Defining Field Rules

• Summary

Designing a Database Application

In previous tutorial lessons, you viewed the sample AlphaSports database, tables, and Forms. In the following lessons, you learn how to create a new database, and define tables for it.

In this lesson, you start building a database application to support a film rental and sales business, called AlphaMovies.

To manage your inventory, you need to store and keep track of the following information about each film:

• Tape_ID

• Title

• Tape_Description

• Genre

• Language

• Rating

• Runtime

• Availability

• Sale_Price

• Last_Rented

In the AlphaMovies database, you create tables or Forms for managing film inventory, customers, orders, and such.

Alpha Five File Types

Alpha Five database objects, such as tables and indexes, are stored in associated files. Refer to Alpha Five File Types for a list and description of Alpha Five file types.

Creating a New Database

In the next procedure, you create a new database.

To create a new database:

1. Start Alpha Five.

2. Select File > New Database from the menu bar. The Create New Database dialog box appears.

3. Use the drop down list box at the top of the dialog to navigate to the location where you want to create the AlphaMovies folder.

4. Click to create a new folder.

5. Name the new folder "AlphaMovies".

6. Double click the folder to enter it.

7. Enter the name "AlphaMovies.adb" into the File name field.

8. Click Save. An empty, AlphaMovies Control Panel appears.

In the following procedures, you create tables for AlphaMovies and define their fields.

Creating Tables

Alpha Five tables store and arrange information in a database. Methods for creating tables include:

• Using the Table Editor in Design Mode

• Using the Set Editor in Design Mode

• Entering field definitions in Text Mode (version 6 and above)

• Entering sample data with Sample Data (version 6 and above)

• Using the New Table/Set Genie.

• Importing text and database files from other programs, such as Microsoft Access, Excel, Outlook, and Visual FoxPro.

To take a quick look at the New Table/Set Genie:

1. Click the New button in the AlphaMovies Control Panel, and select Create Using Genie.

2. Select a template and click Preview to see the field definitions and indexes.

3. Click Cancel to exit.

Using the Table Editor

To create a new table using the Table Editor:

1. In the AlphaMovies Control Panel, click the New button, and select New Table... (version 5) or New Table (Design Mode) (version 6 and above). The Table Editor window appears. Additionally, Alpha Five displays the Table Design toolbar. [Picture]

The following table lists the table design toolbar buttons and describes their functions.

You use the Table Editor to describe table data, not for entering table data. In the Table Editor, each row creates one data field and defines its properties.

Defining Data Fields

Each Alpha Five table can contain an almost unlimited number of records while each record can contain up to 1,024 fields storing individual bits of data. For more information, refer to Database Maximums.

A data field can contain up to 32 characters. Each field name must be unique, begin with a letter, and cannot contain spaces. If you enter a space in the field name, Alpha Five converts the space to an underscore.

In addition to assigning field names, data types, width, decimal places, and indexing, you can define other field properties using the Field Rules Editor, which is explained later in the lesson.

Alpha Five supports thirteen field types. For more information on these types, refer to Create a New Table.

Numeric Data Fields

When you create a numeric field that contains decimal places, the width must include the numbers on both sides of the decimal point, and the decimal point, itself. For example, if the largest value you want to store is 99.99, you enter five in the Width column, and two in the Decimal column.

Store numeric information that is not used for numerical calculations, such as, zip codes and phone numbers, in a character field instead of a numeric field. A character field accepts zeros and formatting characters. For example, you could not enter a phone number, such as (617) 555-1212, in a numeric field, because it includes non-numeric characters. A zip code, such as 01803, would get stored as 1803 in a numeric field, because the first digit, zero, would be removed.

Indexing Data Fields

In the Table Editor, use the Indexed? column to specify whether to index a particular field. If you are modifying a table, or when defining an index expression or filters, use the Index Builder.

Creating Tables for AlphaMovies

In the next procedure, you create tables for AlphaMovies. For the first table, you use the Table Editor to define the data fields for your film inventory information.

You opened the Table Editor previously. In the Table Editor:

• Enter the following information in the blank Table Editor window.

2. Click the Save button on the toolbar. The Specify Table Name dialog box appears. [Picture]

3. Enter "Inventory" in the Table Name field, and click OK. Alpha Five saves the table in the AlphaMovies directory. The Inventory table object appears in the AlphaMovies Control Panel.

   To create a customer information table, you use the New Table Genie.

4. In the AlphaMovies Control Panel, click the New button, and select Create Using Genie. The New Table/Set Genie appears. [Picture]

5. Select the "Contacts-Customers" template, check Include sample Layouts and Field Rules and Include sample data, and click Next. The Genie displays the Table Name, whether to save the table in the same directory as the database, and the Existing Tables in the Database. [Picture]

6. Change the Table Name to "Customers", and click Finish. The table structure closes and the Customers table appears in the AlphaMovies Control Panel. Note that the Customers table has forms, reports, and labels.

7. Select the Customers table in the Control Panel, click the Design button, and select Table Structure. The Customers table field data structure appears. [Picture]



8. Click the Indexes button on the toolbar to see the indexed Customers table fields.

9. Click the Field Rules button on the toolbar. The Field Rules Editor appears.

In the next section, you learn about field rules and define some for the Inventory table.

Defining Field Rules

Field rules govern how you enter data in a table, and help ensure that data entry is accurate, consistent, and complete. You use field rules to control the information that you can enter in a field.

You can use field rules to perform the following tasks:

• Insert calculations or incrementing values

• Validate, format, and insert field values

• Change field entry case, and define auto-expanding abbreviations

• Ensure reasonable and accurate values

• Create table lookups for filling-in data

• Post data from one table to another

• Define Xbasic scripts for different events

For more information on field rules, refer to Types of Field Rules.

To define field rules:

1. First, look at the field rules defined for the Customers table in the Field Rules Editor. [Picture]

You use the drop-down list box at the top, to switch between data fields, and the tabs, to switch between field rules. Data fields with check marks have field rules associated with them.

In the Customers table, the Customer_ID field employs the auto-increment field rule. The Company_Name field employs a transformation rule, which converts all letters to lowercase, and gives words initial capital letters.

2. Close the Field Rules Editor.

3. Right-click the Inventory table in the Control Panel, and select Edit Field Rules to open the Field Rules Editor. The Inventory table's  Tape_ID field appears in the Field Rules Editor.

4. In Extended Field Types select the Auto-increment option. [Picture]

When you define a Character, Numeric, or Date field with the Auto-Increment rule, Alpha Five supplies an incremental and editable field value when you create a new record. If you overwrite the default value, successive records are incremented from the value you enter.

Auto-Increment fields are useful for assigning unique identification numbers for invoices, parts, products, and customers. By default, Character fields are incremented using numbers, for example 0001, 0002.

Tip: Use the Default Value Rule on the Data Entry tab to specify the starting value for an empty table.

1. Enter a Default Descriptive Name, such as "Tape ID", and a Field Description.

2. Click the Data Entry tab. [Picture]

3. Select "Simple default expression" from the Default Mode drop-down list box.

4. Enter "00001" in the Default Value text box.

5. Check the Set default value at start of data entry check box.

6. Select the Title data field, click the Transformations tab, and select "Low&Word" from the Case Convert list. [Picture]

7. Select the Genre data field, and click the Lookups tab. [Picture]

8. Select "Simple list" in the Lookup styles drop-down list box, and enter the following list values: "Action", "Comedy", "Drama", "Mystery", and "Sci-Fi".

9. Keep the default values for Display as Drop-down list box, Allow exceptions, and Auto popup.

10. Repeat steps 7, 8, and 9 for the Rating field, but enter the following simple list values: "G", "PG", "PG-13", "R", "NC-17".

11. Select the Language data field, and click the Lookups tab.

12. Select "Simple list" in the Lookup styles drop-down list box, and enter the following list values: "English", "French", "Spanish", "Italian", and "Other". [Picture]

13. Keep the default value for Display as Drop-down list box, but select "Force closest match" in Allow Exceptions, and "Pop-up on entrance" in Auto popup.

14. Click the Save button on the toolbar, and close the Field Rules Editor.

Now, you can open a Default Browse or Default Form for the Inventory and Customers tables, and enter new data records. Double-click on the Inventory table, and enter the following data:

Examine how the field rules govern data entry. Try applying some of the following field rules to see their effect.

15. Apply a simple mask to the Customers table phone_number data field or postal_code field. [Picture]

16. Make data entry required for some data fields.

17. Apply a skip field rule to the Tape_ID data field, to keep the value unchanged and unavailable for editing. [Picture]

Summary

In this lesson, you created a new database for the AlphaMovies database application, and made some tables, and other database objects. You learned about defining data fields, creating an index, and defining field rules for data entry.

What's Next?

See Linking Tables in a Set, to learn about sets, and create a set for AlphaMovies data.