Answer the following questions for the View Ridge Gallery VR

Answer the following questions for the View Ridge Gallery VRG database discussed in Chapter 7 with the tables shown in Figures 7-13and 7-14 and the data shown in Figure 7-15.

Suppose that you are developing a stored procedure to record an artist who has never been in the gallery before, a work for that artist, and a row in the TRANS table to record the date acquired and the acquisition price. How will you declare the boundaries of the transaction? What transaction isolation level will you use?

Suppose that you are writing a stored procedure to change values in the CUSTOMER table. What transaction isolation level will you use?

Suppose that you are writing a stored procedure to record a customer’s purchase. Assume that the customer’s data are new. How will you declare the boundaries of the transaction? What isolation level will you use?

Suppose that you are writing a stored procedure to check the validity of the intersection table. Specifically, for each customer, your procedure should read the customer’s transaction and determine the artist of that work. Given the artist, your procedure should then check to ensure that an interest has been declared for that artist in the intersection table. If there is no such intersection row, your procedure should create one. How will you set the boundaries of your transaction? What isolation level will you use? What cursor types (if any) will you use?

FIG 7-14

CREATE TABLE ARTIST ( LastName FirstName Nationality DateofBirth DateDeceased CONSTRAINT CONSTRAINT CONSTRAINT NOT NULL IDENTITY (1,1), NOT NULL NOT NULL NULL NULL NULL Numeric(4,0) Numeric(4,0) ArtistPK ArtistAK1 NationalityValues CHEC PRIMARY KEY (ArtistID), UNIQUE (LastName, FirstName) (Nationality IN (\'Canadian\', \"English\', \'French\', German\' , \'Mexic an\', \'Russian\', \'Spanish United States CONSTRAINT CONSTRAINT BirthValuesCheck ValidBirthYear CHECK(DateOfBirth

Solution

We can declare the transaction boundaries by using the automatic transaction, because it

manages the transaction boundaries by itself, based on a declarative attribute set for each transactions.

Here is the eg:

SELECT TRANS.TransactionId,TRANS.CustomerId
FROM TRANS
WHERE TRANS.TransactionId=(required parameter)
SET NewcustomerId=...
LOCK TRANS
Update...
....
UNLOCK TRANS

STEP 2:
To make changes in the customer table we have to update the table.
The update creates a new version of the row and any other select statements use
the old version until this one has committed.
For this functionality READ_COMMITTED_SNAPSHOT mode of isolation is used.
It removes UPDATE/SELECT deadlocks.

STEP 3:
For new data, a new row(record has to be inserted).
For this we have to perform insert statement in the customer table. The safest option is to use serializable transaction here:
Places a range lock on the data set, preventing other users from updating or inserting rows into the data set until the transaction is complete.
This is the most restrictive of the four isolation levels

Step 4:

For this whole purpose we need to add two transactions
separtely one for reading data and another for inserting a new record.

For reading purpose we can use READ COMMITTED transaction and nolock on table:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

select * from TRANS with (nolock)

END TRANSACTION

For inserting a new record in the intersection table, we have to again use serializable transaction.
It will prevent all other read statements from intefering into the bulk insert statements.