Microsoft Office Tutorials and References
In Depth Information
Tutorial A: Database Design
This tutorial has three sections. The first section briefly reviews basic database terminology. The second
section teaches database design. The third section features a practice database design problem.
REVIEW OF TERMINOLOGY
You will begin by reviewing some basic terms that will be used throughout this textbook. In Access, a
database is a group of related objects that are saved in one file. An Access object can be a table, form, query,
or report. You can identify an Access database file by its suffix, .accdb.
A table consists of data that is arrayed in rows and columns. A row of data is called a record.Acolumn
of data is called a field. Thus, a record is a set of related fields. The fields in a table should be related to one
another in some way. For example, a company might want to keep its employee data together by creating
a database table called Employee. That table would contain data fields about employees, such as their
names and addresses. It would not have data fields about the company
s customers; that data would go in a
s values have a data type that is declared when a table is defined. Thus, when data is entered into
the database, the software knows how to interpret each entry. Data types in Access include the following:
Text for words
Integer for whole numbers
Double for numbers that have a decimal value
Currency for numbers that should be treated as dollars and cents
Yes/No for variables that have only two values (such as 1/0, on/off, yes/no, and true/false)
Date/Time for variables that are dates or times
Each database table should have a primary key field, a field in which each record has a unique value.
For example, in an Employee table, a field called SSN (for Social Security number) could serve as a primary
key, because each record
s SSN value would be different. Sometimes a table does not have a single field
whose values are all different. In that case, two or more fields are combined into a compound primary key.
The combination of the fields
values is unique.
Database tables should be logically related to one another. For example, suppose a company has an
Employee table with fields for SSN, Name, Address, and Telephone Number. For payroll purposes, the
company has an Hours Worked table with a field that summarizes Labor Hours for individual employees. The
relationship between the Employee table and Hours Worked table needs to be established in the database so
you can determine the number of hours worked by any employee. To create this relationship, you include
the primary key field from the Employee table (SSN) as a field in the Hours Worked table. In the Hours
Worked table, the SSN field is then called a foreign key.
In Access, data can be entered directly into a table or it can be entered into a form, which then inserts
the data into a table. A form is a database object that is created from an existing table to make the process of
entering data more user-friendly.
A query is the database equivalent of a question that is posed about data in a table (or tables). For
example, suppose a manager wants to know the names of employees who have worked for the company for
more than five years. A query could be designed so that it interrogates the Employee table to search for the
information. The query would be run, and its output would answer the question.
Because a query may need to pull data from more than one table, queries can be designed to interrogate
multiple tables at a time. In that case, the tables must be connected by a join operation, which links tables
on the values in a field that they have in common. The common field acts as a
for the joined tables;
when the query is run, the query generator treats the joined tables as one large table.