Laboratory Exercise 9

Indices in SQL

CMP 420: Database Systems
Lehman College, City University of New York
10 April 2003

Indices in SQL

An index on an attribute A of a relation is a data structure that makes it efficient to find those tuples that have a fixed value for an attribute A. Indices usually help with queries in which their attribute A is compared with some constant. When relations are very large, it becomes very costly to scan all the tuples for those with a specific value of the attributes.

Looking at the text file that created the second lab lab2tables.txt, what indices have been set up for the tables? Come up with at least two other attributes, or pair of attributes, for which an index would be appropriate. Why did you choose these attributes for indices?

To add an index to your database, the format is very similar to the previous CREATE commands we have seen: 

    CREATE INDEX nameOfIndex ON tableName(attributeName);
For the two indices you came up with above, create an index for them in the lab 2 database.

Write two queries, one for each index, that takes advantage of your new indices.

Views in SQL

In lecture, we discussed views, or relations that rely on existing tables for their values. The simplest form of a view definition is: 
    CREATE VIEW nameOfView AS viewDefinition;
where viewDefinition is an SQL query. See Section 5.8 (begins on p 294) for a long discussion of creating views. There are also more details in the Reference Manual.

Create two different views:

  1. One that contains all orders for the company "Acme Investments Ltd" in 2001.
  2. One that contains the name of the company and the total number of orders for that company.

Final Comments

Before leaving the lab, you need to log out and shut down your computer. First, you should quit all applications. Then, go to the K menu in the bottom left hand corner and select Log Out. Choose the Shut Down option, and shut off the monitor when you're done.

Remember to pick up any files you sent to the printer and to take any personal belongings. It is difficult to get back into the lab later (since it's locked when not in use), so, it's worth making sure you haven't forgotten anything.