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.

Practice

1. Which key uniquely identifies each record in a database table and cannot have NULL values?

easy

A. Primary Key

B. Foreign Key

C. Candidate Key

D. Super Key

Solution

1. Step 1: Understand the role of Primary Key

   The primary key uniquely identifies each record and must not be NULL.

2. Step 2: Compare with other keys

   Foreign keys link tables, candidate keys are possible unique keys, super keys may have extra columns.

3. Final Answer:

   Primary Key -> Option A

4. Quick Check:

   Unique and not NULL = Primary Key [OK]

Hint: Primary key = unique + no NULL [OK]

Common Mistakes:

• Confusing foreign key with primary key
• Thinking candidate key is always primary
• Assuming super key must be minimal

2. Which of the following is the correct syntax to declare a primary key on column id in SQL?

easy

A. CREATE TABLE t (id INT SUPER KEY);

B. CREATE TABLE t (id INT FOREIGN KEY);

C. CREATE TABLE t (id INT CANDIDATE KEY);

D. CREATE TABLE t (id INT PRIMARY KEY);

Solution

1. Step 1: Recall SQL syntax for primary key

   Primary key is declared using PRIMARY KEY after the column definition.

2. Step 2: Check other options

   Foreign key requires reference clause, candidate and super key are not SQL keywords.

3. Final Answer:

   CREATE TABLE t (id INT PRIMARY KEY); -> Option D

4. Quick Check:

   Primary key syntax = column INT PRIMARY KEY [OK]

Hint: Primary key uses PRIMARY KEY keyword in SQL [OK]

Common Mistakes:

• Using FOREIGN KEY without REFERENCES
• Trying to declare candidate or super key directly
• Missing PRIMARY KEY keyword

3. Consider a table Orders with columns OrderID, CustomerID, and ProductID. If OrderID is the primary key and CustomerID is a foreign key referencing Customers table, what does this imply?

medium

A. OrderID uniquely identifies each order; CustomerID links to Customers table

B. CustomerID uniquely identifies each order; OrderID links to Customers table

C. ProductID is the primary key; CustomerID is a candidate key

D. OrderID and CustomerID together form a super key

Solution

1. Step 1: Understand primary key role

   OrderID uniquely identifies each order record.

2. Step 2: Understand foreign key role

   CustomerID links Orders to Customers table to maintain data consistency.

3. Final Answer:

   OrderID uniquely identifies each order; CustomerID links to Customers table -> Option A

4. Quick Check:

   Primary key = unique ID, foreign key = link [OK]

Hint: Primary key = unique; foreign key = link to another table [OK]

Common Mistakes:

• Mixing primary and foreign key roles
• Assuming foreign key is unique
• Confusing candidate key with foreign key

4. A table has columns EmpID, Email, and Phone. The EmpID is set as primary key, but duplicate emails exist. What is the problem and how to fix it?

medium

A. Phone should be primary key instead of EmpID

B. Duplicate emails violate candidate key uniqueness; add UNIQUE constraint on Email

C. Primary key allows duplicates; change EmpID to foreign key

D. No problem; duplicates in Email are allowed with primary key on EmpID

Solution

1. Step 1: Identify uniqueness requirements

   Primary key ensures EmpID is unique, but Email duplicates violate candidate key uniqueness if Email should be unique.

2. Step 2: Fix by adding UNIQUE constraint

   To prevent duplicate emails, add UNIQUE constraint on Email column.

3. Final Answer:

   Duplicate emails violate candidate key uniqueness; add UNIQUE constraint on Email -> Option B

4. Quick Check:

   Unique candidate keys need UNIQUE constraint [OK]

Hint: Add UNIQUE constraint to enforce candidate key uniqueness [OK]

Common Mistakes:

• Thinking primary key allows duplicates
• Changing primary key without reason
• Ignoring uniqueness of candidate keys

5. Given a table Students with columns StudentID, Email, and PhoneNumber, which of the following sets can be a super key?

hard

A. {StudentID, Email}

B. {Email, PhoneNumber}

C. All of the above

D. {StudentID}

Solution

1. Step 1: Define super key

   A super key is any set of columns that uniquely identifies a record, even if it has extra columns.

2. Step 2: Analyze each option

   StudentID alone uniquely identifies records; Email and PhoneNumber together can also uniquely identify; adding Email to StudentID still uniquely identifies (extra columns allowed).

3. Final Answer:

   All of the above -> Option C

4. Quick Check:

   Super key = unique set, extra columns allowed [OK]

Hint: Super key = unique columns, can include extras [OK]

Common Mistakes:

• Thinking super key must be minimal
• Confusing super key with candidate key
• Assuming single column only