MIS 431 Exam 3 Riley PSU Exam Questions and Answers Already Passed
Indexes - Answers 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 - Answers provides location/address (where its stored on the hard drive)
Types of Addresses - Answers Physical address
Virtual address
Relative address
Physical address - Answers An actual location in the main memory device
Virtual address (VSAM) - Answers imaginary drive values mapped to physical locations
Relative address - Answers -distance from start of file
-other reference point
in a relational database, what storage address/location does not matter (unless you're the DBMS)? -
Answers Physical
what form does an indexes's ordered list typically take? - Answers Balanced tree (b-tree) rather than a
simple linear list
How are indexes represented? - Answers 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? - Answers -an attribute value
-information concerning the physical storage location of the entity (or entities) having that precise
attribute value
problems with indexes - Answers -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 - Answers 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? - Answers -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 - Answers -Atomic
-Consistency
-Isolation
-Durability
Atomic - Answers -transaction not divisible
-all transaction operations must be completed
-incomplete transactions aborted
Consistency - Answers -any transaction will take the database from one consistent state to another with
no broken pre-set constraints (referential integrity) - no issues
Isolation - Answers -other operations cannot access (see) data that has been modified during a
transaction that has not yet completed
Durability - Answers -permanence of consistent database (cannot be undone, should always be able to
get back to it)
Indexes - Answers 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 - Answers provides location/address (where its stored on the hard drive)
Types of Addresses - Answers Physical address
Virtual address
Relative address
Physical address - Answers An actual location in the main memory device
Virtual address (VSAM) - Answers imaginary drive values mapped to physical locations
Relative address - Answers -distance from start of file
-other reference point
in a relational database, what storage address/location does not matter (unless you're the DBMS)? -
Answers Physical
what form does an indexes's ordered list typically take? - Answers Balanced tree (b-tree) rather than a
simple linear list
How are indexes represented? - Answers 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? - Answers -an attribute value
-information concerning the physical storage location of the entity (or entities) having that precise
attribute value
problems with indexes - Answers -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 - Answers 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? - Answers -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 - Answers -Atomic
-Consistency
-Isolation
-Durability
Atomic - Answers -transaction not divisible
-all transaction operations must be completed
-incomplete transactions aborted
Consistency - Answers -any transaction will take the database from one consistent state to another with
no broken pre-set constraints (referential integrity) - no issues
Isolation - Answers -other operations cannot access (see) data that has been modified during a
transaction that has not yet completed
Durability - Answers -permanence of consistent database (cannot be undone, should always be able to
get back to it)
