Keys (primary, candidate, foreign, super) in DBMS Theory - Step-by-Step Execution

Concept Flow - Keys (primary, candidate, foreign, super)

Start: Table with multiple columns

↓

Identify Candidate Keys

↓

Select one Candidate Key as Primary Key

↓

Define Super Key as Candidate Key + extra columns

↓

Use Primary Key in another table as Foreign Key

↓

Establish Relationships between tables

This flow shows how keys are identified and used to link tables in a database.

Execution Sample

DBMS Theory

Table: Student(ID, Name, Email, Phone)
Candidate Keys: ID, Email
Primary Key: ID
Super Key: ID + Name
Foreign Key: StudentID in Enrollment table

Shows how keys are chosen and related between Student and Enrollment tables.

Analysis Table

Step	Action	Key Type Identified	Columns Involved	Purpose/Result
1	Analyze table columns	Candidate Key	ID	Unique identifier for students
2	Analyze table columns	Candidate Key	Email	Unique email for students
3	Choose one candidate key	Primary Key	ID	Main unique identifier for table
4	Add extra columns to candidate key	Super Key	ID + Name	Still uniquely identifies rows
5	Use primary key in another table	Foreign Key	StudentID in Enrollment	Links Enrollment to Student
6	End			All keys identified and relationships set

💡 All keys identified and foreign key relationships established

State Tracker

Key Type	Start	After Step 1	After Step 2	After Step 3	Final
Candidate Keys	None	ID	ID, Email	ID, Email	ID, Email
Primary Key	None	None	None	ID	ID
Super Key	None	None	None	ID + Name	ID + Name
Foreign Key	None	None	None	StudentID in Enrollment	StudentID in Enrollment

Key Insights - 3 Insights

Why is only one candidate key chosen as the primary key?

Because the primary key uniquely identifies each row and is used as the main reference; other candidate keys remain as alternatives but only one is selected for consistency, as shown in execution_table step 3.

How is a super key different from a candidate key?

A super key includes the candidate key plus extra columns that still uniquely identify rows, as seen in execution_table step 4 where ID + Name is a super key but not minimal.

What is the role of a foreign key?

A foreign key links one table to another by referencing the primary key of the related table, enabling relationships between tables, demonstrated in execution_table step 5.

Visual Quiz - 3 Questions

Look at the execution table, which columns form the candidate keys after step 2?

A ID only

B ID and Email

C Email only

D ID and Name

💡 Hint

Check the 'Key Type Identified' and 'Columns Involved' columns at step 2 in the execution_table.

At which step is the primary key chosen?

A Step 3

B Step 1

C Step 4

D Step 5

💡 Hint

Look for the row where 'Primary Key' appears in the 'Key Type Identified' column.

If we add 'Phone' to the primary key, what key type would it become?

A Candidate Key

B Foreign Key

C Super Key

D Primary Key

💡 Hint

Refer to the explanation of super keys in the key_moments and execution_table step 4.

Concept Snapshot

Keys in DBMS:
- Candidate Key: Columns that uniquely identify rows
- Primary Key: One chosen candidate key as main identifier
- Super Key: Candidate key plus extra columns
- Foreign Key: Column in one table referencing primary key in another
Used to ensure uniqueness and link tables

Full Transcript

In a database table, candidate keys are columns or sets of columns that can uniquely identify each row. From these, one candidate key is selected as the primary key, which serves as the main unique identifier. A super key is any candidate key plus additional columns that still uniquely identify rows but are not minimal. Foreign keys are columns in other tables that reference the primary key to create relationships between tables. This process ensures data integrity and links data across tables effectively.