Keys (primary, candidate, foreign, super) in DBMS Theory - Practice Problems & Coding Challenges

Challenge - 5 Problems

🧠 Conceptual

intermediate

Understanding Primary Keys

Which of the following best describes the role of a primary key in a database table?

A It uniquely identifies each record and cannot contain NULL values.

B It is a key that can have duplicate values and may contain NULLs.

C It is used to link two tables but does not uniquely identify records.

D It is any attribute that can be used to identify records but is not necessarily unique.

💡 Hint

Think about what makes each row in a table unique and mandatory.

A primary key uniquely identifies each record in a table and cannot have NULL values, ensuring each row is distinct.

📋 Factual

intermediate

Candidate Keys Characteristics

Which statement correctly defines a candidate key?

A A candidate key is a key that is chosen as the primary key.

B A candidate key is a key that can contain duplicate values.

C A candidate key is a key that references another table's primary key.

D A candidate key is any attribute or set of attributes that can uniquely identify a record.

💡 Hint

Think about keys that could potentially be primary keys.

Candidate keys are all possible keys that can uniquely identify records; one of them is selected as the primary key.

🔍 Analysis

advanced

Foreign Key Purpose

What is the main purpose of a foreign key in a relational database?

A To serve as an alternate unique identifier within the same table.

B To enforce referential integrity by linking to a primary key in another table.

C To uniquely identify each record within its own table.

D To allow duplicate values within a table for faster searches.

💡 Hint

Consider how tables relate to each other in a database.

A foreign key links a record in one table to a primary key in another, ensuring data consistency across tables.

❓ Comparison

advanced

Super Key vs Candidate Key

Which of the following statements correctly distinguishes a super key from a candidate key?

A A super key is any key that uniquely identifies records; a candidate key is a minimal super key with no extra attributes.

B A super key must be chosen as the primary key; a candidate key cannot be primary.

C A candidate key can contain duplicate values; a super key cannot.

D A super key is always a foreign key; a candidate key is always a primary key.

💡 Hint

Think about minimality and uniqueness in keys.

A super key uniquely identifies records but may have extra attributes; a candidate key is a minimal super key without unnecessary attributes.

❓ Reasoning

expert

Identifying Keys in a Table

Consider a table Employees with columns: EmployeeID, Email, PhoneNumber, and DepartmentID. Which of the following is not a valid candidate key?

A <code>EmployeeID</code> because it uniquely identifies each employee.

B <code>Email</code> assuming each employee has a unique email address.

C <code>PhoneNumber</code> assuming some employees share the same phone number.

D <code>EmployeeID</code> and <code>Email</code> combined as a composite key.

💡 Hint

Candidate keys must uniquely identify records without duplicates.

If PhoneNumber is shared by multiple employees, it cannot uniquely identify records and thus is not a candidate key.

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