Overview of Database Concepts

3 4

Before you start designing your database, it’s important to understand some fundamentals. This section introduces some of the basic concepts of databases.

Database Elements

A database is a collection of information stored in an organized manner. Two common database types are relational databases and flat-file databases. In a relational database such as Access, data can be subdivided into containers called tables. Tables organize data into rows and columns. (Rows are also called records, and columns are also called fields.) A record contains information about a particular item, such as a customer, while fields identify the category of information, such as the contact name or address. Figure 3-1 shows these database elements.

figure 3-1. a database table has rows and columns (records and fields).

Figure 3-1. A database table has rows and columns (records and fields).

In a flat-file database, different types of information are not broken out into separate tables but are included in one table. The same data might (and generally does) appear in multiple records in the table. For example, a customer’s full name and address might be stored in many records of an Orders table. If that customer’s address changes, it must be changed in all those records. Flat-file databases were used in early database systems, and many mainframe databases still have a flat-file format, as do some low-end PC databases intended for personal use, such as DBMaker.

InsideOut

If you need to save an Access table for use in a flat-file database and the format you need isn’t available as one of the export formats in the Export Table tablename To dialog box, select the Text Files export type, and give the text file the .csv extension (.csv stands for comma-delimited). Comma-delimited files can be imported by almost any database program, whether flat-file or relational.

Relationships

In a relational database, you can create relationships between tables. This means, in effect, that two or more tables can share information. Relationships are established by linking a unique field (called a primary key) in the first table to a corresponding field (called a foreign key) in the second table.

When a field in a table is set as the primary key, each record in the table must have a value in that field and each record’s value must be unique. Sometimes a table already has a field with unique data that can be set as the primary key field, such as an EmployeeID field in an Employees table. Otherwise, you can add a field of the AutoNumber type to a table and use it as the primary key field, providing a unique ID number for each record in the table. A foreign key field doesn’t need to have unique values in its table.

In a one-to-one relationship, a record in one table can have only one related record in the other table. Figure 3-2 shows an example of this type of relationship.

figure 3-2. the one-to-one relationship shown here links two tables so that one record in tblemployees matches one record in tblemployeesalary.

Figure 3-2. The one-to-one relationship shown here links two tables so that one record in tblEmployees matches one record in tblEmployeeSalary.

In a one-to-many relationship, a record in one table can have multiple matching records in another table, as shown in Figure 3-3.

figure 3-3. in this one-to-many relationship, many records in the tblorders table match one record in the tblcustomers table.

Figure 3-3. In this one-to-many relationship, many records in the tblOrders table match one record in the tblCustomers table.

In a many-to-many relationship, many records in one table can have many matching records in another table. Access doesn’t provide an explicit many-to-many relationship type, but a many-to-many relationship can be set up indirectly by using a linking table containing just the key fieldsof the two tables to be linked, as shown in Figure 3-4.

figure 3-4. a many-to-many relationship allows one record in the tblstudents table to link to multiple records in the tblclasses table, and vice versa, using tblstudentsclasses as an intermediary.

Figure 3-4. A many-to-many relationship allows one record in the tblStudents table to link to multiple records in the tblClasses table, and vice versa, using tblStudentsClasses as an intermediary.

note


The 1s and infinity symbols (∞) at the ends of the join lines represent the "one" and "many" sides of a relationship.

For information about creating relationships, see Chapter 4, "Creating a Database."

Avoiding Data Duplication

The primary advantage of relational databases over flat-file databases is that information isn’t duplicated in different tables, or in multiple records in a single table. In a proper ly designed relational database with no duplicated data, when you change a customer’s address in one table (for example), any table that links to the address information will automatically pick up the updated data through its linking field.

For simple information storage, flat-file databases might still be acceptable, and even when you’re working in a relational database, you might need to exchange data with a flat-file database, requiring you to create a table including data from different related tables. But if you’re designing an Access database, it should be normalized to avoid duplicate data and to prevent data errors. See "Normalizing and Refining," for more information about normalizing a database.

For a list of database definitions, see "Database Terminology."



Microsoft Access Version 2002 Inside Out
Microsoft Access Version 2002 Inside Out (Inside Out (Microsoft))
ISBN: 0735612838
EAN: 2147483647
Year: 2005
Pages: 172
Authors: Helen Feddema

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net