MIS 431 - EXAM #3
*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*
A = Atomic
C = Consistency
I = Isolation
D = Durability
Atomic - Answer- *ACID*
-All transaction operations must be *completed*
-Incomplete transactions aborted
-Whole can't be divisible
Consistency - 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*
A = Atomic
C = Consistency
I = Isolation
D = Durability
Atomic - Answer- *ACID*
-All transaction operations must be *completed*
-Incomplete transactions aborted
-Whole can't be divisible
Consistency - Answer- *ACID*
