MIS 431 - EXAM #3 STUDY GUIDE
*INDEXES* - Answer ---------------------------------------------------------
Index - Answer --*copy of selected columns of data* from a table that can be searched
very *efficiently*
-makes queries run *faster*
-primary keys are always indexed
Introduction to Indexes - Answer --most queries require locating a certain row (or set of
rows) that satisfy some condition specified in a WHERE clause
-*scanning every row* in a table looking for the desired rows can be unacceptably
*slow*
-using *indexes* greatly *speeds up* the search process
-defining appropriate indexes is an important aspect of implementing large databases
Indexes Example - Answer --phone book - large table with ~1,000,000 rows
-data is not physically stored in any particular order
-without indexes, if you want to find the phone number for a particular person the
DBMS would need to search through *all* 1 million rows of data
--> NOTE: it would have to keep searching after it finds a match because there could be
additional matches
Pointers - Answer --When data is stored on drive (or Random Access Memory - RAM)
-Data is stored all over
-Operating System allocates space with a function call
-Provides location/address
--> Physical address
--> Virtual address (VSAM)
--> Relative address
What are Table Indexes? - Answer --In a relational database, the physical storage
location for a particular row doesn't matter
--> *unless you're the DBMS and are trying to find it*
-An index structure provides rapid access to attribute values and their corresponding
table rows
-Indexes contain information about the physical storage location of data
Benefit of Table Indexes - Answer --Note that entity data generally must be read in one
full row at a time, even if just one attribute is needed
--> BUT, if an attribute value can be found in an index, then the DBMS can *avoid
having to read full rows*
How Indexes are Represented - Answer --Indexes contain an "ordered list* of entires
, --> typically *takes the form of a "Balanced-tree" (B-tree)* rather than a simple linear list
-A tree structure is an efficient *structure for holding an ordered list*
-Can be quickly traversed to find a particular value of the attribute(s) indexed upon
How Indexes are Represented (cont) - Answer --The tree is traversed from its *root
node* to the desired *leaf node*
--> For each node, find the highest key value less than or equal to the search value and
follow the pointer to the next node
-Each leaf node contains *two pieces* of information
--> an attribute value
--> information concerning the physical storage location of the entity (or entities) having
that precise attribute value
--> Ex: In Oracle, a Row_ID value provides the required physical storage location
information
Problems with Indexes - Answer --*Each index must be updated when rows are
inserted, deleted or modified*
-Changing one row of data in a table with many indexes can result in considerable time
and resources to update all of the indexes
**don't index things that change frequently (ex: quantity)
*TRANSACTION MANAGEMENT AND CONCURRENCY CONTROL* - Answer ---------
------------------------------------------------
Steps to Improve Index Performance - Answer -1. Index primary keys
2. Index common join columns (usually primary keys)
3. Index columns that are searched regularly
4. Use a performance analyzer (tells you what to index)
What is a Transaction - Answer --logical unit of work
-must be either *entirely completed or aborted*
--> can't be anything in between
-no intermediate states are acceptable
*multiple SQL statements
*relational database = always accurate, constant state
Example Transaction - Answer --Register credit sale of 100 units of part X to customer
Y for $500
-Consistent state only if both transactions are fully completed
-DBMS doesn't guarantee transaction represents real-world event
Transaction Properties - Answer -*ACID*
*INDEXES* - Answer ---------------------------------------------------------
Index - Answer --*copy of selected columns of data* from a table that can be searched
very *efficiently*
-makes queries run *faster*
-primary keys are always indexed
Introduction to Indexes - Answer --most queries require locating a certain row (or set of
rows) that satisfy some condition specified in a WHERE clause
-*scanning every row* in a table looking for the desired rows can be unacceptably
*slow*
-using *indexes* greatly *speeds up* the search process
-defining appropriate indexes is an important aspect of implementing large databases
Indexes Example - Answer --phone book - large table with ~1,000,000 rows
-data is not physically stored in any particular order
-without indexes, if you want to find the phone number for a particular person the
DBMS would need to search through *all* 1 million rows of data
--> NOTE: it would have to keep searching after it finds a match because there could be
additional matches
Pointers - Answer --When data is stored on drive (or Random Access Memory - RAM)
-Data is stored all over
-Operating System allocates space with a function call
-Provides location/address
--> Physical address
--> Virtual address (VSAM)
--> Relative address
What are Table Indexes? - Answer --In a relational database, the physical storage
location for a particular row doesn't matter
--> *unless you're the DBMS and are trying to find it*
-An index structure provides rapid access to attribute values and their corresponding
table rows
-Indexes contain information about the physical storage location of data
Benefit of Table Indexes - Answer --Note that entity data generally must be read in one
full row at a time, even if just one attribute is needed
--> BUT, if an attribute value can be found in an index, then the DBMS can *avoid
having to read full rows*
How Indexes are Represented - Answer --Indexes contain an "ordered list* of entires
, --> typically *takes the form of a "Balanced-tree" (B-tree)* rather than a simple linear list
-A tree structure is an efficient *structure for holding an ordered list*
-Can be quickly traversed to find a particular value of the attribute(s) indexed upon
How Indexes are Represented (cont) - Answer --The tree is traversed from its *root
node* to the desired *leaf node*
--> For each node, find the highest key value less than or equal to the search value and
follow the pointer to the next node
-Each leaf node contains *two pieces* of information
--> an attribute value
--> information concerning the physical storage location of the entity (or entities) having
that precise attribute value
--> Ex: In Oracle, a Row_ID value provides the required physical storage location
information
Problems with Indexes - Answer --*Each index must be updated when rows are
inserted, deleted or modified*
-Changing one row of data in a table with many indexes can result in considerable time
and resources to update all of the indexes
**don't index things that change frequently (ex: quantity)
*TRANSACTION MANAGEMENT AND CONCURRENCY CONTROL* - Answer ---------
------------------------------------------------
Steps to Improve Index Performance - Answer -1. Index primary keys
2. Index common join columns (usually primary keys)
3. Index columns that are searched regularly
4. Use a performance analyzer (tells you what to index)
What is a Transaction - Answer --logical unit of work
-must be either *entirely completed or aborted*
--> can't be anything in between
-no intermediate states are acceptable
*multiple SQL statements
*relational database = always accurate, constant state
Example Transaction - Answer --Register credit sale of 100 units of part X to customer
Y for $500
-Consistent state only if both transactions are fully completed
-DBMS doesn't guarantee transaction represents real-world event
Transaction Properties - Answer -*ACID*
