Microsoft Office Tutorials and References
In Depth Information
• Create a table and add the records.
• Preview and print the contents of a table.
• Create a second table and add the records.
• Create four reports.
• Create a form.
Database design guidelines.
Database design refers to the arrangement of data into tables and ﬁ elds. In the example in
this chapter the design is speciﬁ ed, but in many cases, you will have to determine the design
based on what you want the system to accomplish.
When designing a database, the actions you take and the decisions you make will determine
the tables and ﬁ elds that will be included in the database. As you create a database, such as the
project shown in Figure 1–1 on page AC 3, you should follow these general guidelines:
1. Identify the tables. Examine the requirements for the database in order to identify the
main objects that are involved. There will be a table for each object you identiﬁ ed.
In one database, for example, the main objects might be departments and employees.
Thus, there would be two tables: one for departments and the other for employees. In
another database, the main objects might be clients and recruiters. In this case, there
would also be two tables: one for clients and the other for recruiters. In still another
database, the main objects might be books, publishers, and authors. Here there would be
three tables: one for books, a second for publishers, and a third for authors.
2. Determine the primary keys. Recall that the primary key is the unique identiﬁ er for
records in the table. For each table, determine the unique identiﬁ er, if there is one. For a
Department table, for example, the unique identiﬁ er might be the Department Code. For
a Book table, the unique identiﬁ er might be the ISBN number.
3. Determine the additional ﬁ elds. The primary key will be a ﬁ eld or combination of ﬁ elds
in a table. There typically will be many additional ﬁ elds, each of which contains a type of
data. Examine the project requirements to determine these additional ﬁ elds. For example,
in an Employee table, the additional ﬁ elds might include such ﬁ elds as Employee Name,
Street Address, City, State, Postal Code, Date Hired, Salary, and so on.
4. Determine relationships among the tables. Examine the list of tables you have created
to see which tables are related. When you determine two tables are related, include
matching ﬁ elds in the two tables. For example, in a database containing employees and
departments, there is a relationship between the two tables because one department can
have many employees assigned to it. Department Code could be the matching ﬁ eld in the
5. Determine data types for the ﬁ elds. For each ﬁ eld, determine the type of data the ﬁ eld
can contain. One ﬁ eld, for example, might contain only numbers. Another ﬁ eld might
contain currency amounts, while a third ﬁ eld might contain only dates. Some ﬁ elds
contain text data, meaning any combination of letters, numbers and special characters
(!, ;, ‘, &, and so on). For example, in an Employee table, the Date Hired ﬁ eld would
contain dates, the Salary ﬁ eld would contain currency amounts, and the Hours Worked ﬁ eld
would contain numbers. The other ﬁ elds in the Employee table would contain text data,
such as Employee Name and Department Code.
6. Identify and remove any unwanted redundancy. Redundancy is the storing of a piece of
data in more than one place. Redundancy usually, but not always, causes problems, such as
wasted space, difﬁ culties with update, and possible data inconsistency. Examine each table
you have created to see if it contains redundancy and, if so, determine whether the
redundancy causes these problems. If it does, remove the redundancy by splitting the table into
two tables. For example, you may have a single table of employees. In addition to typical
employee data (name, address, earnings, and so on), the table might contain Department
Number and Department Name. If so, the Department Name could repeat multiple times.