Archive for the ‘Database Interview Questions’ Category
What is a lock? How does it work?
Monday, February 18th, 2008
A lock gives a transaction exclusive use of a data item for the duration of the transaction. This is important so you do not run into concurrency control issues where two or more users are trying to perform queries on the same set of data. There are several levels of locks known as lock granularity starting with the database-level lock that prevents access to the entire database, this is followed by the table level lock that locks an entire table, a page level lock locks a disk page and is the most frequently used DBMS locking device. A row level lock locks the row, while the field level lock allows transactions to access the same row.
Describe the 3 common problems encountered while maintaining concurrency control.
Monday, February 18th, 2008
Concurrence control ensures Serializabilty in a multiple user database. The first common problem is lost updates which occurs when data an operation from T2 uses un committed date from the same table of transaction T1. The uncommitted date problem arises when two transactions T1 and T2 are executed concurrently but the T1 is rolled back after T2 used data from T1. Inconsistent retrievals occur when a transaction calculates aggregate functions while a second transaction updates those functions in real time.
Describe the characteristics of a database transaction
Monday, February 18th, 2008Atomicity, Consistency, Isolation, Durability, and Serializability(ACIDS).
Describe the importance of a transaction log to DR.
Monday, February 18th, 2008Transaction logs are crucial to disaster and data recover efforts as they store the records for the beginning of a transaction, the type of operation being performed, the names of objects affected, before and after values of the tables, transaction pointers, and the ending of a transaction(COMMIT). Best practices states that logs should be stored on several different drives, possible of tapes and in multiple locations to ensure proper DR.
How many different flavors of SQL are there?
Saturday, January 26th, 2008
A database programming language usually involves DBMS specific functions/syntax. If we use it instead of a standard SQL, the procedure code might not work in other DBMS, mostly due to the differences in the feature sets of the different database programming languages. So there are as many falvors as there are different RDBMS’s.
Please describe how a cross join (also known as the Cartesian PRODUCT) works.
Saturday, January 26th, 2008
A cross join is when we join every row of one table to every row of another table, which yields all possible pairs. Note A cross join can be specified in two ways: using the JOIN syntax or by listing the tables in the FROM clause separated by commas without using a WHERE clause to supply join criteria.
What is the recommended normalization state for business applications?
Tuesday, January 8th, 2008Describe an entity cluster.
Monday, December 17th, 2007The key to this answer is understanding that an ENTITY CLUSTER is actually a “virtual” entity type and that the entity cluster is formed by combining into a single abstract object multiple entities that are related to one another. This abstract entity object is not a single entity as documented in the final ERD.
When is denormalization preferred to normalization?
Monday, December 17th, 2007When performance issues outweigh design considerations. Sometimes too much normalization can slow down performance.
If the candidate understands this concept it’s a good thing… if not, you have an issue.
