Examples of automatic data type detection

Laboratory work №5

MS OFFICE PROGRAMS. Database system MS Access

The definition and structure of database

Creation of a new database

Methods of creation new table

The definition and structure of database Microsoft Office Access 2007 features a number of improvements that make the process of creating a new database easier. Even if you have created databases before, it is likely that you will appreciate these features for their ability to speed up the creation process.

As stated earlier, the Navigation pane stores the objects in your database: tables, queries, forms, reports, macros, and modules. Objects always display with an icon to the right. The icon tells you the object type: table, query, form, report, macro, and module.

Create a Database

When you start Access, the Getting Started With Microsoft Office Access screen appears. You can use this screen to create a database. Within a database, you can do such things as enter data, create reports, and retrieve data. You can create a blank database or you can use one of the templates provided by Microsoft. When you use a template, Access creates forms you can use to enter data, reports you can use to retrieve data, and more. You can modify the forms, reports, and other objects to suit your needs. This tutorial will teach you how.

The following templates are included with Access: Assets, Contacts, Events, Faculty, Issues, Marketing Projects, Projects, Sales Pipeline, Students, and Tasks. Other templates are available online. Each template creates a database related to the title. For example, the Faculty template creates a faculty database that includes tables, queries, forms, and reports related to faculty. In Access, you use tables to store data, queries to retrieve data, forms to enter data, and reports to display data.

To use a template to create a database:

1. Start Access. The Getting Started With Microsoft Office Access screen appears.

2. Click Local Templates. Icons representing local templates appear in the center of the window.

3. Click the icon for the template you want to use.

4. Click the Browse button. The File New Database window appears.

5. Locate the folder in which you want to store your database.

6. Click OK.

7. Click Create. Access creates and opens your database.

8. Open the Navigation pane. Access displays the tables, queries, forms, reports and other objects related to the database you selected. You may wish to display the objects by type.

Examples of automatic data type detection

The following table shows how automatic data type detection works in Datasheet view.

Table relationships

Although each table stores data about a different subject, tables in a database usually store data about subjects that are related to each other. For example, a database might contain:

· A customers table that lists your company’s customers and their addresses.

· A products table that lists the products that you sell, including prices and pictures for each item.

· An orders table that tracks customer orders.

Because you store data about different subjects in separate tables, you need some way to tie the data together so that you can easily combine related data from those separate tables. To connect the data stored in different tables, you create relationships. A relationship is a logical connection between two tables that specifies fields that the tables have in common.

Keys

Fields that are part of a table relationship are called keys. A key usually consists of one field, but may consist of more than one field. There are two kinds of keys:

· Primary key A table can have only one primary key. A primary key consists of one or more fields that uniquely identify each record that you store in the table. Often, there is a unique identification number, such as an ID number, a serial number, or a code, that serves as a primary key. For example, you might have a Customers table where each customer has a unique customer ID number. The customer ID field is the primary key of the Customers table. When a primary key contains more than one field, it is usually composed of pre-existing fields that, taken together, provide unique values. For example, you might use a combination of last name, first name, and birth date as the primary key for a table about people.

· Foreign key A table can also have one or more foreign keys. A foreign key contains values that correspond to values in the primary key of another table. For example, you might have an Orders table in which each order has a customer ID number that corresponds to a record in a Customers table. The customer ID field is a foreign key of the Orders table.

The correspondence of values between key fields forms the basis of a table relationship. You use a table relationship to combine data from related tables. For example, suppose that you have a Customers table and an Orders table. In your Customers table, each record is identified by the primary key field, ID.

To associate each order with a customer, you add a foreign key field to the Orders table that corresponds to the ID field of the Customers table, and then create a relationship between the two keys. When you add a record to the Orders table, you use a value for customer ID that comes from the Customers table. Whenever you want to view any information about an order's customer, you use the relationship to identify which data from the Customers table corresponds to which records in the Orders table.

A table relationship, shown in the Relationships window.

1. A primary key, identified by the key icon next to the field name.

2. A foreign key — note the absence of the key icon.