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

Need a 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

Need a 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

Need a 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

Need a hint?

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