Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

This question is about teaching: When I learned databases, we talked about indexes being like the card catalog in the town library. We had at least an author index, a title index, then the Dewey decimal number was the access path to the book, which is like the whole record in the database.

Books? What, on paper? Index cards in drawers?

The youngsters don't really know what I'm talking about, so how do we best explain it nowadays?

(Feel free to enjoy my lawn, just please recognize the difference between the grass and the astroturf at the prep school, ok?)

share|improve this question
    
Maybe the index at the back of a book? You could even hold up a textbook as a demonstration. –  Kenneth Fisher Aug 8 '13 at 18:14
2  
Explain the principles of binary searching and binary trees so that the index isn't seen as something magic but as a "much quicker way of finding something" but still having some cost. –  Colin 't Hart Aug 8 '13 at 18:33
    
The textbook helps a bit, but it's just one level. I'm fishing for a 30-second to 5-minute metaphor aimed at non-technical folks, including managers who will never care to learn. I even include beginning programmers who don't yet understand the difference between registers, on-die cache, shared memory modules, and glacially slow spinning disk. –  ndye Aug 8 '13 at 19:18
    
Binary search and index seeks have a similarity with the 20-questions game. Are the kids/students familiar with that game? –  ypercube Aug 9 '13 at 7:25
    
Cool, the 20-questions game helps, too. –  ndye Aug 12 '13 at 14:53
add comment

4 Answers 4

Best is to refer : Stairway to SQL Server Indexes

You leave your house to run a few errands. When you return, you find a message from your daughter’s softball coach waiting for you. Three of the girls, Tracy, Rebecca, and Amy have lost their team caps. Could you please swing by the Athletic Products Store and buy caps for the girls. Their parents will reimburse you at the next game.

You know the girls and you know their parents. But you do not know their hat sizes. Somewhere in your town are three residences, each containing a piece of information that you need. No problem, you’ll just call the parents and get the hat sizes. You reach for your phone, and you reach for an index – the white pages of your telephone directory.

The first residence that you need to reach is that of Helen Meyer. Estimating that “Meyer” will be located near the middle of the population, you jump to the middle of the white pages; only to discover that you are at the page whose heading says “Kline-Koerber”. You make a smaller jump forward and reach the “Nagle-Nyeong” page. One even smaller jump backwards puts you at the “Maldonado-Nagle” page. Realizing that you are now at the correct page, you scan down the page till you reach the “Meyer, Helen” line and obtain the telephone number. Using the phone number, you reach the Meyer residence and obtain the information you need.

You repeat the process two more times, reach two other residences, and obtain two more hat sizes.

You have just used an index, and you have used it in much the same way that SQL Server uses an index.

For more reading, you can refer to

share|improve this answer
    
Thank you, I didn't think of the white pages ... although I haven't used them in a decade. –  ndye Aug 8 '13 at 19:20
    
I need more time to delve into the Stairway: I'm not a SQL Server person, so I wasn't registered for that site's ... freewall? –  ndye Aug 12 '13 at 15:02
    
@ndye Yes .. it is free and one of the best sites for learning SQL Server. –  Kin Aug 12 '13 at 17:26
add comment

If you're trying to get to the youngsters who've never searched for anything in their life without using Google, then why not try something like this:

Imagine iTunes messed up your music library and every song on your iPod was mixed up in random order but given a sequential name like "track1234". If you wanted to find a certain song, all you could do is start listening to each song, one at a time to see what song it is. Let's say iTunes wouldn't let you rename your tracks. What could you do to figure out how to be able to (a) find the song you're looking for and (b) find out the real name of a song based on its messed up track name?

You could listen to each song and figure out what it is, and then keep a list of the track names and the real song names in an Excel spreadsheet.

Once you've finished making your list in Excel, you could make a copy. One copy you sort by the messed up track name. The other copy you sort by the real song name (or artist + song if you like).

Each of the two Excel lists is like an index. Its a list of the contents, not the actual contents. It tells you where to find the thing you're looking for and its sorted in a convenient order to help you quickly jump to the part of the list that has the thing you want to find. Once you find that thing, it will tell you where to find the actual data (i.e. the music on your iPod) that you are looking for.

If you still have their attention by this point, you could try to show them the power of binary searching. For this you might be better finding a YouTube clip of the game show "Price is Right" and their "high/low game" which smart players play using binary searching.

share|improve this answer
    
And this story shows the index costs storage to maintain copies of record attributes ... very slightly compared to the audio file, and that difference has a point. –  ndye Aug 12 '13 at 15:07
add comment

I'd suggest you go with the index in the back of a book...but it sounds as if books aren't something they use for reference material.

So, maybe try something along the lines of hyperlinks.

Have them imagine if they had a hyperlink that took them to a specific blog post and they didn't have to use Google to search the internet.

You could even talk to them about a page of hyperlinks, or searching the page of hyperlinks using the 'find on page' feature of a browser, etc.

HTH

share|improve this answer
    
OK, and maybe the bookmarks in a desktop browser . . . . –  ndye Aug 8 '13 at 19:22
add comment

Depending on how your students are it might be useful to highlight fragmentation when discussing indexes. The below is something I used to describe to some (non-DBA) data owners indexes and why regular index maintenance was important to keep them being useful -

With index maintenance the levels of fragmentation are what matters with regard to how well they work. Think of a cookery book with an index, if you kept adding new recipes to the book, any new entries would be added to the end of the index rather than in the correct alphabetical position. Continually adding to the end of the index would make it difficult to use; that is what fragmentation is. A disorganised messy indexing structure. It happens when data is added, deleted or updated. It's normal within databases. There are two ways to clean it up; rebuild the index or reorganise. The difference between and rebuild and an reorganise are effort and suitability

* A rebuild will drop the existing index and recreate it refresh. In instances where the fragmentation is over 30% a rebuild makes more sense to SQL, its less effort to re-make it than it is to shuffle what it’s already got. A rebuild will also resample the statistics, the information SQL uses to make better decisions about best to get data out of the database.

* A reorganise will shuffle around the leaf nodes. When the fragmentation is lower it would be over kill to drop it and re-make it.

*No or tiny fragmentation, nothing is needed. The current process for index maintenance completely rebuilds all indexes on all tables in the database.

Hope this helps

share|improve this answer
    
Illustrating another aspect, thanks! –  ndye Aug 12 '13 at 15:08
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.