Index selection guidelines in DBMS Theory - Mini Project: Build & Apply

Index Selection Guidelines

📖 Scenario: You are managing a small online bookstore database. You want to improve the speed of searching books by their title and author. To do this, you will create indexes on the appropriate columns.

🎯 Goal: Create a table called books with columns for id, title, and author. Then, add indexes on the title and author columns to speed up search queries.

📋 What You'll Learn

Create a table named books with columns id (integer primary key), title (text), and author (text).

Create an index named idx_title on the title column.

Create an index named idx_author on the author column.

💡 Why This Matters

🌍 Real World

Indexes help databases find data faster, just like an index in a book helps you find a topic quickly.

💼 Career

Database administrators and developers use indexes to optimize query performance in real applications.

1

Create the books table

Write a SQL statement to create a table called books with three columns: id as an integer primary key, title as text, and author as text.

DBMS Theory

-- Write the CREATE TABLE statement for books
-- Your code here

Need a hint?

Use CREATE TABLE books (id INTEGER PRIMARY KEY, title TEXT, author TEXT);

2

Add an index on the title column

Write a SQL statement to create an index named idx_title on the title column of the books table.

DBMS Theory

CREATE TABLE books (
    id INTEGER PRIMARY KEY,
    title TEXT,
    author TEXT
);
-- Write the CREATE INDEX statement for idx_title
-- Your code here

Need a hint?

Use CREATE INDEX idx_title ON books(title);

3

Add an index on the author column

Write a SQL statement to create an index named idx_author on the author column of the books table.

DBMS Theory

CREATE TABLE books (
    id INTEGER PRIMARY KEY,
    title TEXT,
    author TEXT
);

CREATE INDEX idx_title ON books(title);
-- Write the CREATE INDEX statement for idx_author
-- Your code here

Need a hint?

Use CREATE INDEX idx_author ON books(author);

4

Verify the indexes exist

Write a SQL query to list all indexes on the books table. Use the sqlite_master table and filter by type = 'index' and tbl_name = 'books'.

DBMS Theory

CREATE TABLE books (
    id INTEGER PRIMARY KEY,
    title TEXT,
    author TEXT
);

CREATE INDEX idx_title ON books(title);

CREATE INDEX idx_author ON books(author);
-- Write a SELECT query to list indexes on books
-- Your code here

Need a hint?

Use SELECT name FROM sqlite_master WHERE type = 'index' AND tbl_name = 'books';