Peering into Data Management - 5/5

Part - 4 is here.

Surprise Quiz, folks.

Below are five questions to test your DBMS knowledge. DBAs and anyone developing DBMS software should be able to answer at least three. The average dev should be able to answer at least two.

Questions:

1. Explain ACID properties.
2. Explain BASE properties.
3. Explain the CAP theorem.
4. What is implied by Referential Integrity?
5. How many clustered indexes can a table have - one OR more than one? Both answers are correct with proper reasoning.

Answer - 1:

ACID properties are guarantees that a DBMS can provide.

Atomicity - If I'm starting a transaction, all transaction steps will be complete, or none will.

Consistency - implies that finishing a transaction should take the database from one consistent state to another. Hence, appropriate constraints, cascading ops, and triggers should kick in when executing a transaction.

Isolation - guarantees that when multiple transactions are going concurrently, then at the end of all the transactions, the DBMS should be in a state that it would be in if the transactions had happened serially. The order of serialization doesn't matter. {T2 -> T1 -> T3} is an acceptable order, even if T1 was started before T2 and T2 was started before T3.

Durability - implies that after a transaction is finished, if the DBMS crashes even one millisecond later, the transaction should be preserved when the DBMS is revived.

Answer - 2:

BASE acronym exists because it is easier to remember that it is the opposite of ACID.

Basically Available - The reads in a distributed system will be available, even if not consistent.

Soft State - The update to a value may not have propagated throughout the system, so a value's read will be softly correct, sometimes returning a stale value. It's essential that while you may be returned a stale value, you will never be returned a wrong or garbage value.

Eventually Consistent - If you stop issuing any new transactions, your entire distributed system will become consistent in some time, and all reads will return the same (correct) value.

Answer - 3:

CAP Theorem (also called Brewer's Theorem) says that out of the following three, only two can be guaranteed in a Distributed System:

1. Consistency: When a value is written, all reads will return the same latest version.
2. Availability: A non-failing node will give a response in a reasonable time. No infinite hanging and no error are acceptable.
3. Partition Tolerance: A distributed system will continue functioning in the face of a network partition.

Eventual Consistency implies prioritizing Availability over Consistency so that the system is more responsive, even if returning stale values.

Answer - 4:

In DBMS, where data is referred between tables (using foreign keys), the idea that the data is not valid in just one row or table but across the entire database is called Referential Integrity.

Answer - 5:

The clustered index follows a table's physical order of data; therefore, only one clustered index for a table is possible.

However, you can always create a view on a table. Some DBMSs allow you to create a clustered index on a view. So you can leverage that to create a view over the table as 'SELECT * FROM MyTable;' and then create a clustered index over this view.

This way, you get another clustered index of a table by creating a full copy of the table. So more than one clustered index of a table is possible by paying O(n) space costs.

The end.