Keys (primary, candidate, foreign, super) in DBMS Theory - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is a Primary Key in a database?

A Primary Key is a unique identifier for each record in a database table. It ensures that no two rows have the same key value and cannot be null.

beginner

Define Candidate Key.

A Candidate Key is a column or a set of columns that can uniquely identify any record in a table. One of these candidate keys is chosen as the Primary Key.

beginner

What is a Foreign Key?

A Foreign Key is a column or set of columns in one table that refers to the Primary Key in another table. It creates a link between the two tables.

intermediate

Explain Super Key.

A Super Key is any set of columns that uniquely identifies a record in a table. It may contain extra columns beyond what is needed for uniqueness.

intermediate

How does a Candidate Key differ from a Super Key?

A Candidate Key is a minimal Super Key, meaning it has no unnecessary columns. A Super Key can have extra columns that are not needed for uniqueness.

Which key uniquely identifies each record in a table and cannot have null values?

A Primary Key

B Foreign Key

C Candidate Key

D Super Key

The Primary Key uniquely identifies each record and cannot be null.

What type of key is used to link two tables together?

A Candidate Key

B Primary Key

C Foreign Key

D Super Key

A Foreign Key in one table refers to the Primary Key in another, linking the tables.

Which key is a minimal set of columns that can uniquely identify a record?

A Foreign Key

B Candidate Key

C Super Key

D Primary Key

Candidate Keys are minimal unique identifiers; one is chosen as the Primary Key.

A Super Key may contain:

A Extra columns beyond what is needed for uniqueness

B Only one column

C Only foreign keys

D No columns

Super Keys can have extra columns that are not necessary for uniqueness.

Which key is chosen from candidate keys to uniquely identify records?

A Foreign Key

B Super Key

C Composite Key

D Primary Key

The Primary Key is selected from candidate keys to uniquely identify records.

Explain the differences between Primary Key, Candidate Key, Foreign Key, and Super Key.

Think about uniqueness and relationships between tables.

Why is it important to have a Primary Key in a database table?

Consider how databases organize and connect data.

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