CCNC/CCNC Module 5/Designing and creating tables/Defining Keys/Indexes

From WikiEducator
Jump to: navigation, search
Tutorial.png Defining Keys and Indexes 

Primary Keys | Indexes | Self Assessment | Summary & FAQs

Creating Indexes

Icon objectives.jpg


Upon completion of this tutorial the learner will be able to:
  • Describe the different kinds of Indexes
  • Create and rename an Index
  • Delete an Index
  • Save an Index
  • Reset an Index

What is an Index

Figure 1. Designing an Index

An index improves performance by providing an alternate path to access data and speeds up searches, sorting, queries etc. An index can be applied to any data type. Indexes are appled to fields that are commonly used in searching for data (like a last name) or where data needs to be grouped together for a report. Creating and using indexes incurs a cost to the database so it is not a good idea to apply indexes to every field in a table.

To open the indexing dialog select Tools -> Index Design from the available menus.

Creating an Index

Figure 2. Designing an Index

Notice that there is an option to make this a unique index. In other words if you do not want duplicate entries here you would click on Unique. This would not work here as there are going to be instances where many people have the same surname. However, if you were creating the index on a field like an account number you would then choose not to allow duplicates by activating the Unique option.

Index Actions

Figure 3. New Index

In the upper left of the Indexes dialog are five buttons.

Create a New Index


This button creates a new index by prompting you for the field names you would like indexed. Give this some thought and think about the discussion of table scans covered previously.

Remember to save the index by clicking on the save button. If you have not saved and you click on the close button you will get the following prompt “Do you want to save the changes made to the current index? At this point you could save.

Delete an Index


This button deletes the highlighted index.

Rename an Index


This button renames the highlighted index.

Save an Index


This button saves the new, renamed and changed indexes.

Reset an Index


This button resets the index.

Test your knowledge

Field Name Data Type Required Description
address_id Integer Yes An AutoValue field for uniqueness.
address_line1 Text Yes First line describing an address. Could be street name or suite number.
address_line2 Text No Second line describing an address.
city_name Text Yes The name of the city
region Text Yes The name of the region. Could be province, state, etc.
country Text Yes The name of the country
postal_code Text Yes The localized postal code or equivalent

Icon qmark.gif

Defining Indexes


1. Indexes can only be applied to text fields?

Incorrect, they can be applied to any data type. Click here to review the topic.
Absolutely Correct!

2. From the Addresses table design above which fields would you apply indexes?

(a) address_id, country_name
Incorrect, address_id does not need to be indexed. Click here to review the topic.
(b) city_name, country_name, postal_code
Incorrect, region should be included. Click here to review the topic.
(c) address_id, address_line1, postal_code
Incorrect, click here to review the topic.
(d) city_name, region, country_name, postal_code
Absolutely correct!
(e) address_id, region, country_name, postal_code
Incorrect, click here to review the topic.

3. Indexes can be applied to enforce uniqueness?

Absolutely Correct!
Incorrect, they can be applied to enforce uniqueness. Click here to review the topic.

Your score is 0 / 0