Term: 1^st Term

Week: 3

Class: Senior Secondary School 3

Age: 17 years

Duration: 40 minutes of 2 periods each

Date:

Subject: Data Processing

Topic:- Indexes; Definition, clustered and unclustered

SPECIFIC OBJECTIVES: At the end of the lesson, pupils should be able to

Define indexes and data entries
Explain clustered and unclustered index

INSTRUCTIONAL TECHNIQUES: Identification, explanation, questions and answers, demonstration, videos from source

INSTRUCTIONAL MATERIALS: Videos, loud speaker, pictures, Data Processing for senior Secondary Education by Hiit Plc, WAPB Computer Studies for Senior Secondary I by Adekunle et al, On-line Materials.

INSTRUCTIONAL PROCEDURES

PERIOD 1-2

PRESENTATION	TEACHER’S ACTIVITY	STUDENT’S ACTIVITY
STEP 1 INTRODUCTION	The teacher reviews the previous lesson on the concept of data modeling	Students pay attention
STEP 2 EXPLANATION	He defines indexes and data entries	Students pay attention and participates
STEP 3 DEMONSTRATION	He further differentiates between clustered and unclustered indexes	Students pay attention and participate
STEP 4 NOTE TAKING	The teacher writes a summarized note on the board	The students copy the note in their books

NOTE

INDEX

An Index is a copy of database table that has been reduced to certain fields and the copy is always in sorted form.

The index also contains a pointer to the corresponding record of the actual table so that the fields not contained in the index can also be read. Index contains a value and a pointer to first record that contains data value.

A Database Index is a data structure that speeds up certain operation on a file. The Operation involves a search key which is the set of record files( in most cases a single field). The elements of an index are called data entries. Data entries can be actual data record. A given file of data records can have several indexes, each with different search keys as showed in the table below.

Customer ID	Name	Address	City	State	Zip
001	Mr Daniel	10,Bale str.	Maryland	Lagos	1011
002	Mrs Okon	5, Oju-ile	Ota	Ogun	1021
003	Mr David	26, Dalemo str.	Ikeja	Lagos	1023

The search engine searches for a value in table or file in two ways. The table scan which is sequential and index which is random.

Indexes are special lookup tables that the database engine uses to speed up data retrieval. An index in a database is similar to an index in the back of a book.

An index table or file consists of records called index entries. It is of the form

Search- key

Pointer

The search key field is used to sort the rows (in the index column) and the pointer field (in index column) indicates where the actual data in the table will be retrieved. When a table has an index it simply means the records in that table has been sorted in one way or the other.

Indexes are automatically created when primary key and unique constraints are defined on table columns.

INDEX CLASSIFICATION

Index can be classified as either clustered or unclustered.

CLUSTERED

Clustered index is an index whose sorting order determines the order of how the rows/records in a table are stored. There could be only one clustered index in a table because there could always be one way of arranging the records in a table at a given time. For example, if you are asked to arrange some tables in a room, you could arrange them in a round form, row form or packed them close together, only one way at a time.

Clustered index also means that related values in a table are stored close to each other according to the order of the index.

A Clustered index is when a file is organized so that the ordering of data records is the same as or close to the ordering of data entries. A clustered index can take place only if the data records are sorted on the search key field. For example, suppose that student’s records are sorted by age; an index on age that stores data entries in sorted order by age is a clustered index.

Indexes that maintain data entries in sorted order by search key use a collection of index entries, organized into a tree structure to guide searches for data entries. Thus, clustered indexes are relatively expensive to maintain when the file is updated, when data entries are to be moved across pages, and if records are identified by a combination of page id and slot as is often the case, all places in the database that point to a moved record must also be updated to point to the new location. These additional updates can be time consuming.

The table below illustrates a clustered index file:

Student ID	Name	Age
00231364OJ	Olu Jacob	12
00241265AF	Agu Faith	13
00251057AJ	Abiola Joseph	13
00211362MS	Mathew Stephen	14
00251302TB	Thomas Bintu	15

UNCLUSTERED INDEX

This is an index whose sorting order does not determine the order of how the rows/records in a table are stored. This means that the search keys in the index column is sorted in one order while the actual records or rows are sorted in another order or are not sorted at all.

This is an index that is not clustered. A data file can contain several unclustered index. For example, supposing that student’s records are sorted by age; and if additional index on gpa field is included, it is called unclustered index.

EVALUATION: 1. What is an index?

What are data entries?
Explain a database index
Explain clustered index versus unclustered index.
State two reasons why clustered index is expensive to maintain.

CLASSWORK: As in evaluation

CONCLUSION: The teacher commends the students positively

Lesson Notes By Weeks and Term - Senior Secondary 3