MIS 431 Exam 4 #1 Questions with Correct Answers
Indexes - ✔✔speeds up search processes for desired rows
-important aspect of implementing (as opposed to designing) large databases
-points to where things are
-provides rapid access to attribute values and their corresponding table rows
-contain info about the physical storage location of data
-if an attribute value can be found in an index, then the DBMS can avoid having to
read full rows
Pointers - ✔✔provides location/address (where its stored on the hard drive)
Types of Addresses - ✔✔Physical address
Virtual address
Relative address
Physical address - ✔✔An actual location in the main memory device
Virtual address (VSAM) - ✔✔imaginary drive values mapped to physical locations
Relative address - ✔✔-distance from start of file
-other reference point
in a relational database, what storage address/location does not matter (unless
you're the DBMS)? - ✔✔Physical
,what form does an indexes's ordered list typically take? - ✔✔Balanced tree (b-
tree) rather than a simple linear list
How are indexes represented? - ✔✔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
What two pieces of information does each leaf node contain? - ✔✔-an attribute
value
-information concerning the physical storage location of the entity (or entities)
having that precise attribute value
problems with indexes - ✔✔-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)
Steps to Improve Index Performance - ✔✔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? - ✔✔-logical unit of work
-must be either entirely completed or aborted (can't be anything in between)
-no intermediate states are acceptable
-smallest unit of work in a relational database (everything you do is a transaction)
-usually more than one SQL statement
Transaction Properties (every single transaction has to follow these)
aka ACID properties - ✔✔-Atomic
-Consistency
-Isolation
-Durability
Atomic - ✔✔-transaction not divisible
-all transaction operations must be completed
-incomplete transactions aborted
Consistency - ✔✔-any transaction will take the database from one consistent
state to another with no broken pre-set constraints (referential integrity) - no
issues
Isolation - ✔✔-other operations cannot access (see) data that has been modified
during a transaction that has not yet completed
Durability - ✔✔-permanence of consistent database (cannot be undone, should
always be able to get back to it)
Indexes - ✔✔speeds up search processes for desired rows
-important aspect of implementing (as opposed to designing) large databases
-points to where things are
-provides rapid access to attribute values and their corresponding table rows
-contain info about the physical storage location of data
-if an attribute value can be found in an index, then the DBMS can avoid having to
read full rows
Pointers - ✔✔provides location/address (where its stored on the hard drive)
Types of Addresses - ✔✔Physical address
Virtual address
Relative address
Physical address - ✔✔An actual location in the main memory device
Virtual address (VSAM) - ✔✔imaginary drive values mapped to physical locations
Relative address - ✔✔-distance from start of file
-other reference point
in a relational database, what storage address/location does not matter (unless
you're the DBMS)? - ✔✔Physical
,what form does an indexes's ordered list typically take? - ✔✔Balanced tree (b-
tree) rather than a simple linear list
How are indexes represented? - ✔✔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
What two pieces of information does each leaf node contain? - ✔✔-an attribute
value
-information concerning the physical storage location of the entity (or entities)
having that precise attribute value
problems with indexes - ✔✔-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)
Steps to Improve Index Performance - ✔✔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? - ✔✔-logical unit of work
-must be either entirely completed or aborted (can't be anything in between)
-no intermediate states are acceptable
-smallest unit of work in a relational database (everything you do is a transaction)
-usually more than one SQL statement
Transaction Properties (every single transaction has to follow these)
aka ACID properties - ✔✔-Atomic
-Consistency
-Isolation
-Durability
Atomic - ✔✔-transaction not divisible
-all transaction operations must be completed
-incomplete transactions aborted
Consistency - ✔✔-any transaction will take the database from one consistent
state to another with no broken pre-set constraints (referential integrity) - no
issues
Isolation - ✔✔-other operations cannot access (see) data that has been modified
during a transaction that has not yet completed
Durability - ✔✔-permanence of consistent database (cannot be undone, should
always be able to get back to it)
