Keys (primary, candidate, foreign, super) in DBMS Theory - Mini Project: Build & Apply

Understanding Database Keys: Primary, Candidate, Foreign, and Super Keys

📖 Scenario: You are designing a simple database for a library system. You need to organize the data about books and authors so that each record is unique and relationships between tables are clear.

🎯 Goal: Build a clear understanding of different types of database keys by identifying and defining primary, candidate, foreign, and super keys in a sample library database.

📋 What You'll Learn

Create a dictionary named library_table representing a table with columns and sample data

Create a list named candidate_keys containing possible candidate keys for the table

Create a variable named primary_key selecting one candidate key as the primary key

Create a dictionary named foreign_keys mapping foreign key columns to referenced tables

Create a list named super_keys containing all super keys for the table

💡 Why This Matters

🌍 Real World

Database keys are essential for organizing data uniquely and establishing relationships between tables in real-world applications like library systems, online stores, and social networks.

💼 Career

Understanding keys is fundamental for roles like database administrators, data analysts, and software developers who design and maintain databases.

1

Create the library table data structure

Create a dictionary called library_table with these exact columns and sample data: 'BookID': [101, 102, 103], 'Title': ['Python Basics', 'Data Science', 'Databases 101'], 'AuthorID': [1, 2, 3], 'ISBN': ['978-1', '978-2', '978-3'].

DBMS Theory

# Create the library_table dictionary with columns and sample data
# Your code here

Hint

Think of the table as a dictionary where each key is a column name and the value is a list of entries for that column.

2

Define candidate keys for the library table

Create a list called candidate_keys containing the exact candidate keys: 'BookID' and 'ISBN'.

DBMS Theory

library_table = {
    'BookID': [101, 102, 103],
    'Title': ['Python Basics', 'Data Science', 'Databases 101'],
    'AuthorID': [1, 2, 3],
    'ISBN': ['978-1', '978-2', '978-3']
}
# Create candidate_keys list with 'BookID' and 'ISBN'
# Your code here

Hint

Candidate keys are columns that can uniquely identify each row. Here, both 'BookID' and 'ISBN' can do that.

3

Select the primary key from candidate keys

Create a variable called primary_key and assign it the string 'BookID' as the chosen primary key.

DBMS Theory

library_table = {
    'BookID': [101, 102, 103],
    'Title': ['Python Basics', 'Data Science', 'Databases 101'],
    'AuthorID': [1, 2, 3],
    'ISBN': ['978-1', '978-2', '978-3']
}
candidate_keys = ['BookID', 'ISBN']
# Assign 'BookID' as the primary_key
# Your code here

Hint

The primary key is the main candidate key chosen to uniquely identify records.

4

Define foreign keys and super keys

Create a dictionary called foreign_keys with the exact entry 'AuthorID': 'Authors'. Then create a list called super_keys containing these exact sets: {'BookID'}, {'ISBN'}, and {'BookID', 'Title'}.

DBMS Theory

library_table = {
    'BookID': [101, 102, 103],
    'Title': ['Python Basics', 'Data Science', 'Databases 101'],
    'AuthorID': [1, 2, 3],
    'ISBN': ['978-1', '978-2', '978-3']
}
candidate_keys = ['BookID', 'ISBN']
primary_key = 'BookID'
# Create foreign_keys dictionary with 'AuthorID' referencing 'Authors'
# Create super_keys list with {'BookID'}, {'ISBN'}, and {'BookID', 'Title'}
# Your code here

Hint

Foreign keys link to other tables. Super keys are sets of columns that uniquely identify rows, including candidate keys and their supersets.

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