Skip to main content
Back to path

Welcome to a Grasp lesson

Create a free account to keep studying and create your own study path.

Lesson 23

Composite Keys in Composite Entities

Recommended resources

Hello! In our last lesson, we defined what a composite entity is and established its crucial purpose: to resolve unworkable many-to-many (M:N) relationships. You learned that by placing a "bridge" entity between two entities in an M:N relationship, we transform it into two clean one-to-many (1:M) relationships.

This lesson drills down into the next essential detail. Now that we have this new composite entity, how do we uniquely identify each row within it? Your learning outcome is to identify the primary key of a composite entity, which is typically a composite key formed from the foreign keys of the parent entities. We'll explore why this is the standard approach and what it means for the structure of our database.

1. The Anatomy of a Composite Entity's Key

Let's recall our STUDENT-ENROLLMENT-COURSE example. The ENROLLMENT table is our composite entity. Each row in this table represents a single, unique event: one specific student enrolling in one specific course.

How can we guarantee that each row is unique?

  • We can't use StudentID as the primary key, because one student can enroll in many courses.
  • We can't use CourseID as the primary key, because one course has many students enrolled.

The solution is to combine them. A primary key that consists of two or more attributes is called a composite primary key. For composite entities, the standard practice is to form this key by combining the primary keys of the parent entities it links.

In our example, the primary key for ENROLLMENT would be the combination of StudentID and CourseID. This combination is unique because a student can only enroll in the same course once. The StudentID and CourseID attributes in the ENROLLMENT table are foreign keys referencing the STUDENT and COURSE tables, respectively. When used together to form the primary key of ENROLLMENT, they also enforce the uniqueness of the relationship.

This visual illustrates the concept perfectly.

Associative Entities & Compound Keys
In this diagram, the M:N relationship between 'Event' and 'Artist' is resolved by the 'Event_Artist' composite entity. The primary key of 'Event_Artist' is a **compound key** (another term for composite key) made up of the foreign keys 'Event_ID' and 'Artist_ID'.

This rule is a cornerstone of ERD design. The following resource formalizes this practice.

What is an associative entity in an ERD?

This article from Gleek.io outlines best practices for creating associative entities.

Please read the section "Rules and Best practices for Associative Entities", focusing specifically on rule #3, "Attributes". It clearly states that the primary key of an associative entity should be a composite key formed from the foreign keys of the linked entities.

2. Identifying the Composite Key in Practice

Understanding the theory is one thing; seeing it applied makes it concrete. Let's watch how this works in a practical example.

The first video explains the two main options for creating an identifier for an associative entity.

ERD: Associative Entities

This segment from Dustin Ormond's "ERD: Associative Entities" video explains how to create an identifier for a new associative entity.

Watch from this section. The video presents two options: combining the identifiers of the parent entities to form a composite identifier, or creating a new surrogate identifier. We will focus on the composite identifier first.

Now, let's see this applied to a new scenario in the same video.

ERD: Associative Entities

This part of the video is a hands-on exercise, resolving an M:N relationship between 'PLAYER' and 'GAME'.

Watch the segment where the presenter builds the player_game associative entity, from this point. Notice how he explicitly identifies game_id and player_id as the foreign keys and then combines them to become the composite primary key for the new entity.

This method is so fundamental that it connects to another important database concept: weak entities. A composite entity is considered a "weak" entity because it is existence-dependent on its "strong" parent entities. An enrollment record can't exist without a student and a course. A key characteristic of a weak entity is that its primary key is partially or fully derived from its parent entities. This is exactly what happens when we create a composite primary key.

The next video explains this connection clearly.

CSCI 240 - Chapter 4

This video from CVSimpson's "CSCI 240" series discusses composite entities in detail.

Please watch the segment from the discussion on composite entities. Pay close attention to the statement around 00:32:02, where the presenter states that an associative table is always a weak entity and will almost always include the primary keys from both parents as their composite key.

3. Alternative Options for the Primary Key

While the composite key is the standard, it's not the only option. The learning outcome states it is typically the primary key, which implies alternatives exist. Let's briefly cover them for a complete understanding.

Kinds of Entities

This text from the "Database Design" open textbook describes dependent entities, which is the formal classification for composite/associative entities.

Find the heading "Dependent entities" and read the bulleted list of characteristics. The final bullet point presents three distinct options for the primary key.

Let's break down the three options presented in the reading:

  1. Use a composite of foreign keys of associated tables if unique.
    This is the standard method we've focused on so far (StudentID + CourseID). It's preferred because it's natural and self-enforcing.

  2. Use a composite of foreign keys and a qualifying column.
    This is useful when the combination of parent keys alone isn't unique. For example, what if a student can re-take a course to get a better grade? Then (StudentID, CourseID) is no longer a unique combination. We could add a Semester attribute to the key, making the PK (StudentID, CourseID, Semester). This new combination would be unique.

  3. Create a new simple primary key.
    This is the "surrogate key" approach mentioned in the first video. Instead of using the parent keys, we just create a new, simple, auto-incrementing number for our ENROLLMENT table, like EnrollmentID. The StudentID and CourseID would still be present as foreign keys, but they wouldn't be part of the primary key. This approach is also common, but the composite key method is often favored from a pure data modeling perspective as it makes the identifying relationship more explicit.

Conclusion

In this lesson, you have learned the crucial rule for defining the primary key of a composite entity. This step is vital for ensuring that every instance of a relationship in your database is uniquely identifiable and free from ambiguity.

Key Takeaways:

  • The primary key of a composite entity must uniquely identify each instance of the relationship it represents.
  • The most common and standard method is to create a composite primary key, which is formed by combining the foreign keys that point to the parent entities.
  • This composite key naturally enforces the business rule that the relationship between the two parent instances must be unique (e.g., a student can enroll in a specific course only once).
  • Alternative approaches exist, such as adding a qualifying attribute to the composite key or creating a new, simple surrogate key.

You now understand what a composite entity is, why it's used, and how to define its key. In our next lesson, we will put all these pieces together. You will learn to perform the complete process of converting a conceptual M:N relationship into a logically sound structure with a composite entity and correctly defined keys on an ER diagram.