Question5
Below are the SQL commands for three transactions pseudocode is used to represent database agnostic variable declarations and use. Imagine that these three transactions are presented to a single modern relational database instance at the same time, that is, within the same few nanoseconds, and so have the potential of being executed concurrently. The transactions all operate on the following person table.
Person Table
personid DECIMAL12 NOT NULL PRIMARY KEY
firstname VARCHAR64 NOT NULL
lastname VARCHAR64 NOT NULL
Review the transactions then answer the subsequent questions.
Transaction 1 Start
UPDATE Person
SET firstname Bob
WHERE personid 1;
UPDATE Person
SET firstname Elaina
WHERE personid 2;
UPDATE Person
SET firstname Qin
WHERE personid 3;
Transaction 1 Commit
Transaction 2 Start
DECLARE Variable vfirstname AS DECIMAL12;
SELECT personid
INTO vfirstname
FROM Person
WHERE personid 2;
UPDATE Person
SET firstname vfirstname
WHERE personid 1;
UPDATE Person
SET firstname Wei
WHERE personid 3;
Transaction 2 Commit
Transaction 3 Start
DECLARE Variable vfirstname AS DECIMAL12;
SELECT personid
INTO vfirstname
FROM Person
WHERE personid 3;
UPDATE Person
SET firstname vfirstname
WHERE personid 2;
UPDATE Person
SET firstname Jack
WHERE personid 1;
Transaction 3 Commit
a. Identify two issues that could occur as a result of these transactions if the database were to use no concurrency control mechanisms, that is, no locking, no timestampingmultiversioning, and no other optimistic locking methods are used. Make sure to tie in the issues to this scenario specifically.
b. Now imagine that the database exclusively uses sharedexclusive locking for concurrency control, and that its locking granularity is rowlevel, that is, individual table rows are locked, but not pages or tables. Describe what could happen with these transactions in terms of successful completion, deadlocks, and waits. Make sure to consider that various schedules could be used by the database depending upon many factors. Recall that shared locks are required when a read occurs, and exclusive locks are required when a write update or delete occurs.
c. Last, imagine that the database uses a combination of locking and timestampingmultiversioning for concurrency control, and that the locking granularity is rowlevel. Describe how the use of timestampingmultiversioning changes what could happen with the transactions in terms of successful completion, deadlocks, and waits when compared to your answer in b. Recall that when a database uses timestampingmultiversioning, it records a history of values so that transactions can pull back the correct value regardless of the number of updates to that value, thus making shared locks unnecessary.
Question8
a. Imagine that the organization has a great need to gather various statistics about addresses. With the current operational subschema, they are able to obtain basic statistics such as counts by state, counts by type, averages, and the like, with good performance. However, the performance suffers significantly when they attempt more complex filtering and statistics, especially for adhoc questions. Identify and explain two advantages of populating a data mart or data warehouse with this same data for the organization, for purposes of this statistics gathering. Identify and explain two costs or disadvantages of doing the same.
b. Imagine that the organization is pondering migration of this operational data from a relational database to a NoSQL database either keyvalue or documentoriented. Identify and explain two reasons why the organization would want to make this switch. Conversely, Identify and explain two reasons why the organization would want to keep this operational data in a relational database.