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.