Primary vs secondary indexes in DBMS Theory - Hands-On Comparison

Understanding Primary vs Secondary Indexes

📖 Scenario: You are managing a small library database. You want to organize the books so you can find them quickly by their unique ID and also by their author names.

🎯 Goal: Build a simple example that shows the difference between a primary index (on book ID) and a secondary index (on author name).

📋 What You'll Learn

Create a data structure to hold book records with IDs and authors

Define a primary index on the book ID

Define a secondary index on the author name

Show how to use both indexes to find books

💡 Why This Matters

🌍 Real World

Databases use primary and secondary indexes to quickly find data without scanning everything.

💼 Career

Understanding indexes is important for database administrators, developers, and data analysts to optimize data retrieval.

1

Create the book records data structure

Create a dictionary called books with these exact entries: 101: {'title': 'Python Basics', 'author': 'Alice'}, 102: {'title': 'Data Science', 'author': 'Bob'}, and 103: {'title': 'Web Development', 'author': 'Alice'}.

DBMS Theory

# Create the books dictionary with IDs as keys and title and author as values
# Your code here

Need a hint?

Use a dictionary with book IDs as keys and another dictionary for title and author as values.

2

Define the primary index on book ID

Create a variable called primary_index that holds the list of book IDs from the books dictionary keys.

DBMS Theory

books = {
    101: {'title': 'Python Basics', 'author': 'Alice'},
    102: {'title': 'Data Science', 'author': 'Bob'},
    103: {'title': 'Web Development', 'author': 'Alice'}
}
# Create primary_index as a list of book IDs
# Your code here

Need a hint?

Use the keys() method of the dictionary and convert it to a list.

3

Define the secondary index on author name

Create a dictionary called secondary_index where each author name maps to a list of book IDs they wrote, using the books dictionary.

DBMS Theory

books = {
    101: {'title': 'Python Basics', 'author': 'Alice'},
    102: {'title': 'Data Science', 'author': 'Bob'},
    103: {'title': 'Web Development', 'author': 'Alice'}
}
primary_index = list(books.keys())
# Create secondary_index mapping authors to list of book IDs
# Your code here

Need a hint?

Loop over books.items(), get the author, and add the book ID to the list for that author in secondary_index.

4

Use both indexes to find books

Create a variable book_by_id_102 that gets the book info for ID 102 using books. Then create a variable books_by_alice that gets the list of book IDs for author 'Alice' from secondary_index.

DBMS Theory

books = {
    101: {'title': 'Python Basics', 'author': 'Alice'},
    102: {'title': 'Data Science', 'author': 'Bob'},
    103: {'title': 'Web Development', 'author': 'Alice'}
}
primary_index = list(books.keys())
secondary_index = {}
for book_id, info in books.items():
    author = info['author']
    if author not in secondary_index:
        secondary_index[author] = []
    secondary_index[author].append(book_id)
# Get book info for ID 102
# Get list of book IDs for author 'Alice'
# Your code here

Need a hint?

Use dictionary access with the key 102 for books and key 'Alice' for secondary_index.