MIS 431 FINAL EXAM STUDY GUIDE
Indexes - Answer -Speeds up search process in large databases
Index structure provides rapid access to attribute values and their corresponding table
rows.
Contains information about the physical storage location of data
Pointers - Answer -Provides the location/ address
- Virtual address --> Imaginary drive values mapped . to physical image
- Relative Address --> Distance from start of file; other reference point
Order List of Entries - Answer -Index has a "List" that takes the form of a "Balance
Tree" (B tree) rather than a simple linear list
A tree structure is an efficient structure for holding an ordered list, and can quickly be
traversed to find a particular value of the attribute indexed
Index Represented - Answer -Tree is traversed from the root node to desired leaf node
Each leaf node contains two pieces of information
- attribute value
-information concerning the physical storage location of the entity with a precise
attribute value
Problems with Indexes - Answer -Each index must be updated when rows are inserted,
deleted, or modified
Changing a row of data can cost time and resources to update the index
Steps to improve Index performance: - Answer --index primary key
- index common join columns
- index columns that are searched regularly
- use a performance analyzer
Transaction - Answer -Smallest step in a database
- logical unit of work
- must be either entirely completed or aborted
no intermediate states are acceptable
All steps in transaction must be complete
Transaction Properties - Answer --Atomic
-Consistency
, -Isolation
-Durability
Atomic - Answer -all transaction operations must be completed
Incomplete transactions must be aborted
Consistency - Answer -any transaction will take the db from one consistent state to
another with no broken constraints
- referential integrity
Isolation - Answer -other operations cannot access data that has been modified during
a transaction that has not yet completed
- the transaction should not be seen until complete
Durability - Answer -Permanence of consistent database state
ability to recover the committed transaction updates against any kind of jsystem failure
- once the consistent state has been reached, you should be able to return to that state
Transaction management in SQL - Answer -commit --> save
rollback --> undo
The user initiated transaction sequence must continue until commit or rollback is
complete. The program has reached the end, or the program reaches an abnormal
termination
Transaction Log - Answer -Log stores
- record for beginning of transaction
-each sql statement has:
_operation
_name of objects
_before and after values for updated fields
_pointers (addresses) to previous and next entries
-commit statement
concurrency control - Answer -coordinates simultaneous transaction execution in
multiple databases
-ensures serializability of transactions in multi user db environment
Potential problems in multi user environment - Answer --lost updates
-uncommitted data
Indexes - Answer -Speeds up search process in large databases
Index structure provides rapid access to attribute values and their corresponding table
rows.
Contains information about the physical storage location of data
Pointers - Answer -Provides the location/ address
- Virtual address --> Imaginary drive values mapped . to physical image
- Relative Address --> Distance from start of file; other reference point
Order List of Entries - Answer -Index has a "List" that takes the form of a "Balance
Tree" (B tree) rather than a simple linear list
A tree structure is an efficient structure for holding an ordered list, and can quickly be
traversed to find a particular value of the attribute indexed
Index Represented - Answer -Tree is traversed from the root node to desired leaf node
Each leaf node contains two pieces of information
- attribute value
-information concerning the physical storage location of the entity with a precise
attribute value
Problems with Indexes - Answer -Each index must be updated when rows are inserted,
deleted, or modified
Changing a row of data can cost time and resources to update the index
Steps to improve Index performance: - Answer --index primary key
- index common join columns
- index columns that are searched regularly
- use a performance analyzer
Transaction - Answer -Smallest step in a database
- logical unit of work
- must be either entirely completed or aborted
no intermediate states are acceptable
All steps in transaction must be complete
Transaction Properties - Answer --Atomic
-Consistency
, -Isolation
-Durability
Atomic - Answer -all transaction operations must be completed
Incomplete transactions must be aborted
Consistency - Answer -any transaction will take the db from one consistent state to
another with no broken constraints
- referential integrity
Isolation - Answer -other operations cannot access data that has been modified during
a transaction that has not yet completed
- the transaction should not be seen until complete
Durability - Answer -Permanence of consistent database state
ability to recover the committed transaction updates against any kind of jsystem failure
- once the consistent state has been reached, you should be able to return to that state
Transaction management in SQL - Answer -commit --> save
rollback --> undo
The user initiated transaction sequence must continue until commit or rollback is
complete. The program has reached the end, or the program reaches an abnormal
termination
Transaction Log - Answer -Log stores
- record for beginning of transaction
-each sql statement has:
_operation
_name of objects
_before and after values for updated fields
_pointers (addresses) to previous and next entries
-commit statement
concurrency control - Answer -coordinates simultaneous transaction execution in
multiple databases
-ensures serializability of transactions in multi user db environment
Potential problems in multi user environment - Answer --lost updates
-uncommitted data
