Keys (primary, candidate, foreign, super) in DBMS Theory - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the sentence to identify the key that uniquely identifies each record in a table.

DBMS Theory

The [1] key uniquely identifies each record in a database table.

Drag options to blanks, or click blank then click option'

Aforeign

Bsuper

Ccandidate

Dprimary

Attempts:

3 left

💡 Hint

Common Mistakes

Confusing primary key with foreign key.

Thinking candidate key is always used as the main key.

The primary key is a unique identifier for each record in a table. It cannot be null and must be unique.

2fill in blank

medium

Complete the sentence to identify the key that can potentially be chosen as a primary key.

DBMS Theory

A [1] key is a minimal set of attributes that can uniquely identify a record.

Drag options to blanks, or click blank then click option'

Asuper

Bcandidate

Cforeign

Dprimary

Attempts:

3 left

💡 Hint

Common Mistakes

Mixing candidate key with foreign key.

Assuming candidate key is always the primary key.

A candidate key is a column or set of columns that can uniquely identify rows in a table. One candidate key is chosen as the primary key.

3fill in blank

hard

Complete the sentence to identify the key that links two tables.

DBMS Theory

A [1] key is used to link two tables.

Drag options to blanks, or click blank then click option'

Aforeign

Bprimary

Ccandidate

Dsuper

Attempts:

3 left

💡 Hint

Common Mistakes

Confusing foreign key with primary key.

Thinking foreign key must be unique.

A foreign key is a field in one table that refers to the primary key in another table, creating a link between the two tables.

4fill in blank

hard

Fill both blanks to complete the definition of a super key.

DBMS Theory

A [1] key is a set of one or more attributes that [2] uniquely identify a record in a table.

Drag options to blanks, or click blank then click option'

Asuper

Bcandidate

Ccan

Dcannot

Attempts:

3 left

💡 Hint

Common Mistakes

Thinking super key must be minimal.

Confusing super key with foreign key.

A super key is any combination of columns that can uniquely identify a record. It can include extra attributes beyond what is necessary.

5fill in blank

hard

Fill all three blanks to complete the dictionary comprehension that selects candidate keys from a list.

DBMS Theory

candidate_keys = { [1]: [2] for [3] in keys if is_candidate([3]) }

Drag options to blanks, or click blank then click option'

Ak

Bk.upper()

Dkeys

Attempts:

3 left

💡 Hint

Common Mistakes

Using the wrong variable name in the comprehension.

Mixing up keys and values in the dictionary.

This comprehension creates a dictionary where keys are candidate keys in uppercase, and values are the original keys. The variable k iterates over keys.

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