Foreign Key Explained
A foreign key is a data element that provides a link to another entity. A foreign key allows a A foreign key is a data element that provides a link to another entity. A foreign key allows a database management system to navigate from one entity to another. For example, we need to database management system to navigate from one entity to another. For example, we need to know who owns an Account, so we would want to include the identifier of the customer to whom know who owns an Account, so we would want to include the identifier of the customer to whom it belongs in the entity. The
it belongs in the entity. The Customer IdCustomer Id in Account is the primary key in Account is the primary key of that Customer in theof that Customer in the Customer entity. Using this foreign key back
Customer entity. Using this foreign key back to Customer enables the database managementto Customer enables the database management system to navigate from a particular account o
system to navigate from a particular account or accounts, to the customer or cur accounts, to the customer or customers that ownstomers that own each account. Likewise, the database can navigate from a particular customer or customers, to each account. Likewise, the database can navigate from a particular customer or customers, to find all of their accounts.
find all of their accounts.
A foreign key is automatically created when
A foreign key is automatically created when we define a relationship between two we define a relationship between two entities. Whenentities. When a relationship is created between two entities, the entity on the "many" side of the relationship a relationship is created between two entities, the entity on the "many" side of the relationship
In
In Figure 7.3Figure 7.3, there are two foreign keys in the Attendance entity. The, there are two foreign keys in the Attendance entity. The Student NumberStudent Number foreign foreign key points back to a particular student in the Student entity. The
key points back to a particular student in the Student entity. The Class IdClass Id foreign key points back foreign key points back to a particular Class in the Class entity. Table 7.3 contains a few Attendance entity instances.
to a particular Class in the Class entity. Table 7.3 contains a few Attendance entity instances.
Table
Table 7.3:7.3: AttendanceAttendance entityentity instancesinstances Open
Opentabletableasasspreadsheetspreadsheet
Student Number Class Id Attendance Date Student Number Class Id Attendance Date SM385932
SM385932 1 1 5/10/20095/10/2009 EM584926
EM584926 1 1 5/10/20095/10/2009 EM584926
EM584926 2 2 6/10/20096/10/2009 MM481526
MM481526 2 2 6/10/20096/10/2009 MM573483
MM573483 2 2 6/10/20096/10/2009
LR731511 3 7/10/2009
LR731511 3 7/10/2009
By looking at these values and recalling the sample values from
By looking at these values and recalling the sample values from Tables 7.1Tables 7.1 and and 7.27.2, we learn that, we learn that Steve Martin and Eddie Murphy both attended the Data Modeling Fundamentals class on
Steve Martin and Eddie Murphy both attended the Data Modeling Fundamentals class on 5/10/2009. Eddie Murphy also attended the Advanced Data Modeling Class with Mickey and 5/10/2009. Eddie Murphy also attended the Advanced Data Modeling Class with Mickey and Minnie Mouse on 6/10/2009.
Minnie Mouse on 6/10/2009. Lone Ranger took Tennis Basics (by Lone Ranger took Tennis Basics (by himself as usual) onhimself as usual) on 7/10/2009.
7/10/2009.
Exercise 7: Clarifying Customer Id Exercise 7: Clarifying Customer Id
1.
1. I was showing examples of both complete I was showing examples of both complete and incomplete definitions during a recentand incomplete definitions during a recent training class, when I shared the following incomplete
training class, when I shared the following incomplete definition for adefinition for a Customer IdCustomer Id::
•
• A ACustomer Id Customer Id is the unique identifier for a Customer.is the unique identifier for a Customer.
"What else can you say about
"What else can you say about Customer IdCustomer Id anyway?" a participant asked. anyway?" a participant asked.
What else can you say about
What else can you say about Customer IdCustomer Id (or any identifier) to add more (or any identifier) to add more meaning tomeaning to its definition?
its definition?
Answers Answers 1.
1. There are three terms within this definition that requirThere are three terms within this definition that require an explanation: 'unique', 'identifier',e an explanation: 'unique', 'identifier', and 'Customer'.
and 'Customer'.
DOCUMENT UNIQUENESS PROPERTIES DOCUMENT UNIQUENESS PROPERTIES
The term 'unique' is ambiguous and could easily be interpreted differently by readers of this The term 'unique' is ambiguous and could easily be interpreted differently by readers of this definition. To maintain clarity and correctness, these questions should be answered within the definition. To maintain clarity and correctness, these questions should be answered within the
definition:
definition:
•
• Are identifier values ever reused?Are identifier values ever reused?
•
• What is the scope of uniqueness?What is the scope of uniqueness?
•
• How is the identifier validated?How is the identifier validated?
DOCUMENT THE CHARACTERISTICS OF THE IDENTIFIER DOCUMENT THE CHARACTERISTICS OF THE IDENTIFIER We can describe the actual ide
We can describe the actual identifier in more detail including addressing these areas:ntifier in more detail including addressing these areas:
•
• Purpose.Purpose. For example, perhaps the identifier is needed because there are multiple For example, perhaps the identifier is needed because there are multiple source systems for Customer data, each with their own
source systems for Customer data, each with their own Id. To enable a common Id. To enable a common set ofset of data to be held abo
data to be held about them, this identifier needed to be cut them, this identifier needed to be created to facilitate integrationreated to facilitate integration and guarantee uniqueness across all customers.
and guarantee uniqueness across all customers.
•
• Business or surrogate key.Business or surrogate key. Document whether the identifier is meaningful to the Document whether the identifier is meaningful to the business (i.e. the business or natural key) or whether it is a meaningless integer business (i.e. the business or natural key) or whether it is a meaningless integer
counter (i.e. the surrogate key).
counter (i.e. the surrogate key).
•
• Assignment.Assignment. Document how a new customer identifier is assigned. The party that is Document how a new customer identifier is assigned. The party that is responsible for creating new identifiers should also be mentioned.
responsible for creating new identifiers should also be mentioned.
DEFINE THE CUSTOMER DEFINE THE CUSTOMER
Because definitions should stand on their own,
Because definitions should stand on their own, we also can define cwe also can define customer within thisustomer within this definition. We can reference the subject area definition of customer.
definition. We can reference the subject area definition of customer.
See the Appendix for my answers.
See the Appendix for my answers.
Key Points Key Points
•
• A key is a data element or set of data elements that helps us find entity instances.A key is a data element or set of data elements that helps us find entity instances.
•
• A candidate key is a set of one or more data elements that uniquely identify an entityA candidate key is a set of one or more data elements that uniquely identify an entity instance.
instance.
•
• A candidate key becomes either a primary or alternate key.A candidate key becomes either a primary or alternate key.
•
• A primary key represents the one or more data elements that uniquely identify an instanceA primary key represents the one or more data elements that uniquely identify an instance of an entity and that is chosen to be
of an entity and that is chosen to bethethe unique identifier everyone should use. In contrast, unique identifier everyone should use. In contrast, an alternate key also uniquely identifies entity occurrences, but is not chosen as
an alternate key also uniquely identifies entity occurrences, but is not chosen as thethe unique key.
unique key.
•
• If a key contains more than one data element, it is known as a composite key.If a key contains more than one data element, it is known as a composite key.
•
• A surrogate key is a primary key A surrogate key is a primary key with no embedded intelligence with no embedded intelligence that is a substitute for athat is a substitute for a natural key. It is used by
natural key. It is used by IT to facilitate integration and introduce database efficiencies.IT to facilitate integration and introduce database efficiencies.
•
• A foreign key points from one entity instance to another.A foreign key points from one entity instance to another.