Transcript Build a database III: Build relationships for a new Access database

ICT Staff Development presents:
®
Microsoft Office
®
Access 2007 Training
Build a database III:
Build relationships for a new
Access database
Course contents
• Overview: You can relate
• Lesson: Create relationships
The lesson includes a list of suggested tasks and a set of
test questions.
Build a database III: Build relationships
for a new Access database
Overview: You can relate
This course teaches you how to build
relationships between your new
tables.
Why are relationships essential? They
bring the data in your tables together
so you can extract meaningful
information.
This course explains the different
types of relationships you can use in a
database, and shows you how to
create the most common type: a oneto-many relationship.
Build a database III: Build relationships
for a new Access database
Course goals
• Create one-to-many relationships between database
tables.
• Set referential integrity for each relationship and prevent
accidental data removal.
• Set cascading updates and deletes, a move that walks any
changes through your data.
• Turn indexing on or off for primary and foreign key fields, a
move that helps speed your database and create a type of
relationship called a one-to-one relationship.
Build a database III: Build relationships
for a new Access database
Lesson
Create relationships
Create relationships
In a database,
relationships are how
your tables “talk” to
each other.
If your tables don’t
talk, you can’t get
answers from your
data.
You create relationships by including a field from one
table as a field in a related table.
Build a database III: Build relationships
for a new Access database
Create relationships
In a database,
relationships are how
your tables “talk” to
each other.
If your tables don’t
talk, you can’t get
answers from your
data.
1
The field you include is called a primary key. Each of
the tables in your database has a primary key, and you
include it as a new field in another table to create a
relationship with that other table.
2
When you share a primary key, the new field in the
second table is called a foreign key.
Build a database III: Build relationships
for a new Access database
Create relationships
In a database,
relationships are how
your tables “talk” to
each other.
If your tables don’t
talk, you can’t get
answers from your
data.
3
The picture illustrates one way this can work. In order to
associate assets with suppliers, the foreign key field in
the Assets table provides a list of company names.
Behind the scenes, Access uses the values in the
primary and foreign keys to synchronize the two fields.
Build a database III: Build relationships
for a new Access database
Types of relationships
Your tables can
participate in three
types of relationships,
shown in the picture.
1
You have a one-to-one relationship when a single
record in one table is related to a single record in
another table, and vice versa.
2
You have a one-to-many relationship when one record
in a table is related to many records in another table.
Build a database III: Build relationships
for a new Access database
Types of relationships
Your tables can
participate in three
types of relationships,
shown in the picture.
3
You have a many-to-many relationship when several
records in one table are related to several records in
another table.
Build a database III: Build relationships
for a new Access database
Create a relationship
So let’s create a oneto-many relationship
between the Suppliers
and Assets tables.
Before you start, think of the relationship as having
“sides.”
In this case, one supplier can provide many assets, so
the Suppliers table resides on the “one” side of the
relationship, while the Assets table sits on the “many”
side.
Build a database III: Build relationships
for a new Access database
Create a relationship
Next, remember this
rule: To create a oneto-many relationship,
you use the primary
key from the “one”
side as a foreign key
on the “many” side.
Animation: Right-click, and click Play.
One of the easiest ways to do this is to run a tool called
the Lookup Wizard, which creates the relationship and
the foreign key field.
Build a database III: Build relationships
for a new Access database
Create a relationship
Next, remember this
rule: To create a oneto-many relationship,
you use the primary
key from the “one”
side as a foreign key
on the “many” side.
One of the easiest ways to do this is to run a tool called
the Lookup Wizard, which creates the relationship and
the foreign key field.
What’s more, the foreign key works as a drop-down list,
so you can select data from the table on the “one” side
quickly and accurately.
Build a database III: Build relationships
for a new Access database
Set referential integrity
The relationship you
created in the
previous section
doesn’t become a
one-to-many
relationship until you
set referential integrity.
Referential integrity ensures that your primary and
foreign key fields stay in synch whenever you add,
change, or remove data. In turn, that keeps your data
accurate.
Build a database III: Build relationships
for a new Access database
Set referential integrity
You set referential
integrity by editing a
relationship.
1
Close any open tables, click the Database Tools tab,
and in the Show/Hide group, click Relationships.
2
Click the line that represents the relationship you want to
edit. The line becomes thicker.
Build a database III: Build relationships
for a new Access database
Set referential integrity
You set referential
integrity by editing a
relationship.
3
Right-click the line and click Edit Relationship. The Edit
Relationship dialog box appears.
4
Select the Enforce Referential Integrity check box.
Build a database III: Build relationships
for a new Access database
Set referential integrity
You set referential
integrity by editing a
relationship.
5
Optionally, you can change data in one place and have
that change appear in other related data. To do so,
select the Cascade Update Related Fields and
Cascade Delete Related Records check boxes. When
you finish, click OK.
Build a database III: Build relationships
for a new Access database
A look at one-to-one relationships
You may not use oneto-one relationships
very often, but you
should know how to
create them.
One-to-one relationships use a primary key and a
foreign key, just like one-to-many relationships.
The difference lies in how you set indexes for each key
field. Indexing a field allows Access to search for data
faster. It can also prevent users from adding duplicate
records.
Build a database III: Build relationships
for a new Access database
A look at one-to-one relationships
You use Design View
to turn on indexing for
your foreign key fields.
It’s easiest to do this
with blank tables.
The picture shows the
process.
1
In the Navigation Pane, right-click the table that contains
the foreign key field, then click Design View.
2
In the designer, click the foreign key field.
3
Under Field Properties, on the General tab, click the
list next to Indexed and select Yes (No Duplicates).
Build a database III: Build relationships
for a new Access database
A look at many-to-many relationships
You have a many-tomany relationship
when a single record
in one table can relate
to many records in
another, and a single
record in that second
table can also relate to
many in the first.
For example, say your company has several types of
computers and several technicians, with each technician
certified to work on some, but not all, of the computers.
Each technician can be related to more than one
computer, and in turn, each computer can be related to
more than one technician.
Build a database III: Build relationships
for a new Access database
A look at many-to-many relationships
To track who can work
on a given machine,
you create a many-tomany relationship by
adding the primary
keys from both sides
of the relationship to a
third table, called a
junction or link table.
The picture shows a typical junction table, and you can
see that its structure is relatively simple — a pair of
primary key fields and some related information.
Now your data can answer questions such as, “Which
computers did a given technician work on last month?”
Build a database III: Build relationships
for a new Access database
Suggestions for practice
1. Create a relationship.
2. Set referential integrity and cascading changes.
Online practice (requires Access 2007)
Build a database III: Build relationships
for a new Access database
Test question 1
It’s a good idea to think of a relationship as having “sides.”
(Pick one answer.)
1. True.
2. False.
Build a database III: Build relationships
for a new Access database
Test question 1: Answer
True.
Knowing which side a table is on tells you whether or not it uses a
foreign key field.
Build a database III: Build relationships
for a new Access database
Test question 2
To create a one-to-many relationship, you do which of the
following? (Pick one answer.)
1. Use the primary key from the table on the “one” side as a
foreign key in the table on the “many” side.
2. Use a junction table with at least two foreign key fields.
3. Turn off indexing for the primary key field.
Build a database III: Build relationships
for a new Access database
Test question 2: Answer
Use the primary key from the table on the “one” side as a foreign key in
the table on the “many” side.
In the asset-tracking database, you can now answer questions such as,
“Which company supplies most of our computers?”
Build a database III: Build relationships
for a new Access database
Test question 3
Referential integrity is useful because it does which of the
following? (Pick one answer.)
1. Takes the place of an index.
2. Keeps primary and foreign key values in sync.
3. Allows you to “orphan” data safely.
Build a database III: Build relationships
for a new Access database
Test question 3: Answer
Keeps primary and foreign key values in sync.
Referential integrity can also make it easy to update and delete data.
Build a database III: Build relationships
for a new Access database
Quick Reference Card
For a summary of the tasks covered in this course, view the
Quick Reference Card.
Build a database III: Build relationships
for a new Access database