Index Architecture, Primary, Secondary and Composite-Key Indexes for SS 3

Class: SS 3
Term: First Term
Week: 1
Age: 16 years
Duration: 45 minutes
Subject: Data Processing
Topic: Indexes
Subject Matter: index architecture: types of indexes, primary and secondary indexes, and indexes using composite search keys
Previous Lesson:

Specific Objectives

By the end of the lesson, pupils/students should be able to:

Cognitive Domain

• Define what an index is in a database context.
• Explain the architecture of indexes, including clustered and non-clustered types.
• List and explain various types of indexes, such as bitmap and dense indexes, and state their differences.
• Explain how data entries are organised to support efficient data retrieval.
• Describe the key features of primary and secondary indexes.
• Explain the concept of indexes using composite search keys.

Affective Domain

• Participate actively in class discussions about database indexing.
• Appreciate the importance of indexes for efficient data management.

Psychomotor Domain

• Identify examples of different index types when presented with scenarios.
• Differentiate between clustered and non-clustered index architectures.

Reference Materials

The following resources were used in planning this lesson:

• 2025 Revised 9 Years Basic Education Curriculum
• Relevant State Unified Scheme of Work
• A suitable Data Processing textbook for SS 3
• The HeadTeacher Scheme of work

Instructional Materials

The teacher will teach this lesson with the aid of:

• Computer sets
• Library card (as an analogy for indexing)
• Whiteboard and markers
• Charts illustrating index structures

Rationale for the Lesson

Understanding indexes is fundamental for any student of Data Processing as it directly impacts database performance and efficiency. This lesson provides the knowledge required to design and manage databases effectively, ensuring fast data retrieval and optimal system operation. It lays the groundwork for more advanced database management concepts.

Prerequisite/Previous Knowledge

Students should have a basic understanding of databases, tables, records, fields, and the general concept of data storage and retrieval.

Lesson Content/Board Summary

Indexes

Definition of Indexes

An index in a database is a special lookup table that the database search engine can use to speed up data retrieval. It is similar to an index in a book, which helps you quickly find information without reading the entire book.

Index Architecture: Clustered and Non-Clustered Indexes

Index architecture refers to how the index is stored and organised within the database.

1. Clustered Index:
   1. A clustered index determines the physical order of data in a table.
   2. There can only be one clustered index per table because the data rows themselves can only be stored in one physical order.
   3. It stores the actual data rows at the leaf level of the index structure.
   4. Data retrieval is very fast for range queries and ordered data.
2. Non-Clustered Index:
   1. A non-clustered index does not change the physical order of the data.
   2. It creates a separate structure that contains the indexed columns and pointers to the actual data rows.
   3. A table can have multiple non-clustered indexes.
   4. It is useful for speeding up searches on columns that are not used for physical ordering.

Types of Indexes

Indexes come in various forms, each suited for different data types and query patterns.

1. Dense Index:
   1. A dense index has an index entry for every search key value in the data file.
   2. It contains the search key value and a pointer to the record in the data file.
   3. It allows for faster searching but requires more storage space.
2. Sparse Index:
   1. A sparse index contains index entries for only some of the search key values.
   2. It is typically used with clustered indexes where data is physically ordered.
   3. It requires less storage space but may involve more disk reads to find the exact record.
3. Bitmap Index:
   1. A bitmap index is used for columns with a low cardinality (few distinct values).
   2. It stores a bitmap (a sequence of bits) for each distinct value, where each bit corresponds to a row in the table.
   3. ‘1’ indicates the row has that value, and ‘0’ indicates it does not.
   4. They are very efficient for queries involving multiple conditions on low-cardinality columns.
4. B-Tree Index:
   1. This is the most common type of index.
   2. It organises data in a tree structure, allowing for efficient searching, insertion, and deletion of records.
   3. Each node in the B-tree contains key values and pointers to child nodes or data records.

Data Entry Organisation for Efficient Retrieval

Indexes organise data entries to support efficient retrieval by creating a sorted structure (like a tree or hash table) based on one or more columns. When a query is executed, the database system can quickly traverse this sorted structure to locate the relevant data rows, rather than scanning the entire table. This significantly reduces the input/output operations and improves query performance.

Features of Primary and Secondary Indexes

Indexes are often categorised based on their relationship with the primary key of a table.

1. Primary Index:
   1. It is built on the primary key of a table.
   2. The primary key must be unique and cannot contain NULL values.
   3. It often dictates the physical storage order of the data (making it a clustered index in many systems).
   4. It ensures the uniqueness of records and provides the fastest access to individual records.
2. Secondary Index:
   1. It is built on non-primary key columns.
   2. These columns may or may not be unique and can contain NULL values.
   3. A table can have multiple secondary indexes.
   4. They are used to speed up queries that search on non-primary key columns.
   5. Secondary indexes are always non-clustered.

Indexes Using Composite Search Keys

A composite search key (or composite index) is an index created on two or more columns of a table. When searching for data, the database uses the combination of these columns to locate records. For example, an index on (LastName, FirstName) would be a composite index. It is particularly useful for queries that filter or sort data based on multiple columns simultaneously, improving performance for complex searches.

Teaching Methods/Instructional Techniques

Discussion, Explanation, Question and Answer, Demonstration, Guided Practice, Analogy

Instructional Procedures

Step 1: Introduction

Time: 5 minutes

Teaching Skill: Activating Prior Knowledge

Teacher’s Activity: The teacher asks students how they quickly find a specific topic in a large textbook or a specific book in a library. The teacher then relates this to finding data in a large database.

Pupils’ Activity: Students share their methods (e.g., using the index, table of contents, library card catalogue). They listen and connect the ideas.

Learning Point: Introduction to indexes

Step 2: Defining Indexes and Architecture Overview

Time: 7 minutes

Teaching Skill: Explanation/Definition

Teacher’s Activity: The teacher defines what an index is in a database context and introduces the concept of index architecture. The teacher explains that indexes help databases find data quickly.

Pupils’ Activity: Students listen, ask questions, and attempt to define indexes in their own words.

Learning Point: Definition of indexes

Step 3: Clustered vs. Non-Clustered Indexes

Time: 7 minutes

Teaching Skill: Comparison/Demonstration

Teacher’s Activity: The teacher explains and differentiates between clustered and non-clustered index architectures, using diagrams or analogies (e.g., a phone book ordered by name vs. a separate list of phone numbers ordered by street address). The teacher highlights that a table can have only one clustered index but many non-clustered indexes.

Pupils’ Activity: Students observe diagrams, listen to explanations, and discuss the differences between the two architectures.

Learning Point: Clustered vs. non-clustered

Step 4: Types of Indexes

Time: 7 minutes

Teaching Skill: Listing/Explanation

Teacher’s Activity: The teacher lists and explains various types of indexes, such as dense, sparse, and bitmap indexes, providing simple examples for each and highlighting their differences and use cases.

Pupils’ Activity: Students listen, take notes, and ask clarifying questions about the different index types.

Learning Point: Various index types

Step 5: Data Entry Organisation for Retrieval

Time: 5 minutes

Teaching Skill: Explanation

Teacher’s Activity: The teacher explains how data entries are organised within an index structure (e.g., B-tree) to ensure efficient retrieval of data, emphasising that this organisation avoids full table scans.

Pupils’ Activity: Students listen and understand the mechanism behind efficient data retrieval using indexes.

Learning Point: Efficient data retrieval

Step 6: Primary, Secondary, and Composite-Key Indexes

Time: 5 minutes

Teaching Skill: Elaboration

Teacher’s Activity: The teacher explains the features of primary and secondary indexes, distinguishing them by the columns they are built on. The teacher then introduces indexes using composite search keys, explaining their purpose and benefits.

Pupils’ Activity: Students listen, compare the different index categories, and understand the concept of composite keys.

Learning Point: Key index types

Step 7: Evaluation/Review

Time: 5 minutes

Teaching Skill: Questioning/Assessment

Teacher’s Activity: The teacher evaluates the learning by asking the following questions:

1. What is a database index?
2. Differentiate between a clustered and a non-clustered index.
3. Mention two types of indexes and explain one.
4. What is a composite search key?

Pupils’ Activity: Pupils answer orally and in writing.

Learning Point: Index concepts understanding

Step 8: Note-Taking

Time: 4 minutes

Teaching Skill: Guided Writing

Teacher’s Activity: The teacher guides pupils/students to copy the essential Board Summary notes on index architecture, types, and primary/secondary/composite indexes into their notebooks.

Pupils’ Activity: Pupils/students copy the notes carefully into their notebooks.

Learning Point: Index information recording

Step 9: Conclusion

Time: 2 minutes

Teaching Skill: Consolidation

Teacher’s Activity: The teacher summarises the key points of the lesson, reiterating the importance of indexes for database performance and efficient data retrieval. The teacher encourages students to explore how indexes are used in real-world database systems.

Pupils’ Activity: Students listen and reflect on the lesson’s main concepts.

Learning Point: Lesson summary and reinforcement

Continuous Assessment/Further Study

Type: Homework

Instruction: Research and write a short paragraph on the advantages and disadvantages of using indexes in a database. Provide an example of where a bitmap index would be most effective.

1. Explain the main purpose of a database index.
2. List two advantages of using indexes.
3. List two disadvantages of using indexes.
4. In what scenario would a bitmap index be the most suitable choice?

Lesson Keywords

• Index – A data structure that improves the speed of data retrieval operations on a database table.
• Clustered Index – An index that dictates the physical storage order of data rows in a table.
• Non-Clustered Index – An index that does not alter the physical order of data but contains pointers to the data rows.
• Dense Index – An index with an entry for every search key value in the data file.
• Sparse Index – An index with entries for only some search key values, typically used with clustered indexes.
• Bitmap Index – An index used for low-cardinality columns, storing a bitmap for each distinct value.
• Primary Index – An index built on the primary key of a table.
• Secondary Index – An index built on non-primary key columns.
• Composite Key Index – An index created on two or more columns of a table.

Differentiation

For weaker learners, provide simplified diagrams of index structures and focus on defining key terms. For stronger learners, encourage them to research and discuss the performance implications of having too many indexes or poorly designed indexes.

Suggested Lesson Videos

For further understanding, search on YouTube for: “Database Indexes Explained SS3” or “Clustered vs Non-Clustered Index Data Processing”.

Teacher Guide for Using This Lesson Plan

Before the lesson, ensure you have access to a computer with a simple database demonstration tool or clear diagrams of index structures. Begin by using relatable analogies, like a book’s index or a library card catalogue, to introduce the concept of indexes. Clearly explain the distinction between clustered and non-clustered indexes, as this is a core concept. When discussing different types of indexes, provide practical examples where each type would be beneficial. Guide students through the explanation of primary, secondary, and composite-key indexes, ensuring they grasp their specific roles. Allow ample time for questions and answers throughout the lesson to check for understanding. Students should copy the Board Summary notes during the designated note-taking step to consolidate their learning. Encourage active participation and critical thinking about how indexes optimise database operations.