Transcript Slide 1

Database Relationships
Objective 5.01 Understand database tables used in business
Relationships
A relationship means that two or more
tables are linked together by a common
field, called a primary key field.
Table relationships increase the power of
the database by allowing data to be stored
separately, but managed and retrieved
collectively.
Making Friends with Relations
One of the great benefits of
working with databases is the
ability to store huge quantities of
information. One company’s
database may contain multiple
tables of related information.
Relationships link data
from individual tables
and increase the
usefulness of a database.
5.01 Understand database tables used in business
When the information between
tables is linked, it is called a
relationship.
Slide 3
What does a relationship look like?
Relationship
established
between two
tables
One record in the
Student Table is
related to one
record in the
Participation
Table
Keys to the Relationship
A primary key is an essential element of a
database table. It is a data type that is set to
make each record within a database table
unique and to link tables together.
Primary key fields must be of the same
data type and size for the tables to which
they link.
The red line that graphically represents
the relationship is called a Join Line
What makes a good primary key?
Which of the following would make each
record in a database unique?






Social security number
Last name
First name
Automobile VIN number
Birth date
Product serial number
Primary and Foreign Keys
When tables relate, the primary key of one table
becomes a foreign key of the other table
For example, in the tables below,
•
ISBN appears as a primary key in the Grocery
Products table . . .…and in the Orders Table
as a foreign key
Diagram of Primary & Foreign Keys
8
5.02 Understand queries,
forms, and reports.
Function of the Junction Table
A junction table is used to join primary key
fields of multiple tables
The Junction table is like a hub or control
center
In this example, Orders
is the Junction Table. It
allows the Grocery
Products and the
Customers tables to
share information.
One-to-Many
One-to-Many

One record in Table A links to multiple
records in Table B through the
primary/foreign key fields of the two tables
Many-to-Many
Many-to-Many


Multiple records in multiple tables are linked
by their primary keys through a Junction
Table.
A many-to-many relationship is formed by
connecting two or more tables that have a
one-to-many relationship. The connection is
made by a Junction Table.
Setting up a Relationship
Add one table’s primary key to a field in
another table that has the same
properties
The primary key in
Grocery Products is ISBN
The primary key in Orders
is Order No
The primary key in
Customers is Store ID
Referential Integrity
Referential integrity protects related
data that is stored in multiple tables.
It would prevent a customer in one table
from being deleted if the customer is also
in a related table.
Relationship Discussion
A music store database contains three tables.
An Inventory table is linked by artist to a Royalty
table
which
contains
the
artist’s
contact
What
are the advantages of
information and royalty percentages
this
system?
The Inventory
table is also linked to a Sales
table by salesperson ID numbers, which
contains employee information and commission
rates
Once a purchase is made, the inventory is
Are
there
adjusted
in onedisadvantages?
table and the artist’s royalties
and employee’s commissions are calculated
from the information in the other tables