Transcript DBC-e03-Access-Workbench-Section-05-PP

DAVID M. KROENKE and DAVID J. AUER
DATABASE CONCEPTS, 3rd Edition
The Access Workbench: Section Five
Relationships in Microsoft Access
Section Objectives
• Understand 1:1 relationships in Access
• Understand N:M relationships in
Access
KROENKE and AUER - DATABASE CONCEPTS (3rd Edition)
© 2008 Pearson Prentice Hall
AW-5-2
Relationships in Access
• All the tables we’ve used so far in
Access have had 1:N relationships
• How are 1:1 and N:M relationships
managed in Access?
KROENKE and AUER - DATABASE CONCEPTS (3rd Edition)
© 2008 Pearson Prentice Hall
AW-5-3
N:M Relationships in Access
• Pure N:M relationships occur in data modeling
• When a data model is transformed into a
database design, an N:M relationship is broken
down into two 1:N relationships
• Each 1:N relationship is between a table resulting
from one of the original entities in the N:M
relationship and a new intersection table.
• Since databases are built in Access from the
database design, Access only deals with the
resulting 1:N relationships—as far as Access is
concerned there are no N:M relationships!
KROENKE and AUER - DATABASE CONCEPTS (3rd Edition)
© 2008 Pearson Prentice Hall
AW-5-4
1:1 Relationships in Access:
The 1:1 Relationship Between SALESPERSON and VEHICLE
KROENKE and AUER - DATABASE CONCEPTS (3rd Edition)
© 2008 Pearson Prentice Hall
AW-5-5
Database Column Characteristics:
The VEHICLE Table
VEHICLE
Column Name
Type
Key
Required
Remarks
InventoryID
AutoNumber
Primary Key
Yes
Surrogate Key
Model
Text (25)
No
Yes
VIN
Text (35)
No
Yes
KROENKE and AUER - DATABASE CONCEPTS (3rd Edition)
© 2008 Pearson Prentice Hall
AW-5-6
VEHICLE Data
InventoryID
Model
VIN
[AutoNumber]
HiStandard
G06HS123400001
[AutoNumber]
HiStandard
G06HS123400002
[AutoNumber]
HiStandard
G06HS123400003
[AutoNumber]
HiLuxury
G06HL234500001
[AutoNumber]
HiLuxury
G06HL234500002
[AutoNumber]
HiLuxury
G06HL234500003
[AutoNumber]
SUHi
G06HU345600001
[AutoNumber]
SUHi
G06HU345600002
[AutoNumber]
SUHi
G06HU345600003
[AutoNumber]
HiElectra
G06HE456700001
KROENKE and AUER - DATABASE CONCEPTS (3rd Edition)
© 2008 Pearson Prentice Hall
AW-5-7
SALESPERSON.InventoryID Column Characteristics
SALESPERSON
Column Name
Type
Key
Required
InventoryID
Int
Foreign Key
No
KROENKE and AUER - DATABASE CONCEPTS (3rd Edition)
© 2008 Pearson Prentice Hall
Remarks
AW-5-8
SALESPERSON.InventoryID Data
NickName
LastName
FirstName
...
InventoryID
Tina
Smith
TIna
...
4
Big Bill
Jones
William
...
5
Billy
Jones
Bill
...
7
KROENKE and AUER - DATABASE CONCEPTS (3rd Edition)
© 2008 Pearson Prentice Hall
AW-5-9
1:1 Relationships in Access:
A 1:N Relationship is Created by Default
The VEHICLE to SALEPERSON
relationship
The relationship is 1:N—this is not the
1:1 relationship we wanted!
KROENKE and AUER - DATABASE CONCEPTS (3rd Edition)
© 2008 Pearson Prentice Hall
AW-5-10
1:1 Relationships in Access:
Setting the Indexed Property Value in the SALESPERSON Table
Select the InventoryID field
Set the Indexed property to Yes (No
Duplicates)
KROENKE and AUER - DATABASE CONCEPTS (3rd Edition)
© 2008 Pearson Prentice Hall
AW-5-11
1:1 Relationships in Access:
The Correct 1:1 VEHICLE to SALESPERSON Relationship
The VEHICLE to SALEPERSON
relationship
The relationship is now the correct
1:1 relationship we wanted!
KROENKE and AUER - DATABASE CONCEPTS (3rd Edition)
© 2008 Pearson Prentice Hall
AW-5-12
DAVID M. KROENKE and DAVID J. AUER
DATABASE CONCEPTS, 3rd Edition
End of Presentation on The Access Workbench: Section Five
Relationships in Microsoft Access