Question 27

Below is relational design for the Customer table. The primary key is underlined. The foreign key is italicized.

Customer (CustomerID, LastName, FirstName, Address, City, State, PostalCode, SalespersonID, Region)

Data for customers from the west region (Region = "WS") must be accessed more often and much more quickly than data for customers from any other region. The query used is:

SELECT * FROM Customer WHERE Region = "WS";

What should be done to improve response time when accessing these rows?

Vertically partition the Customer table.

Horizontally partition the Customer table by placing all rows the have a value of "WS" for the Region in one partition. Place all the other rows in a second partition.

Add a view showing only data from the WS region.

Cluster the Customer table.

Question 28

Overall processing in the database is slow. The DBA originally set the isolation level to repeatable read because he wanted to prevent dirty reads. Which of the following would enable him to still prevent dirty reads but would improve the overall performance of the database?

Change the isolation level to read uncommitted

Increase the frequency of checkpoints.

Change the isolation level to serializable.

Change the isolation level to read committed.

Question 29

It is often necessary to quickly find the total number of times a product has been ordered. For example, if a customer orders 100 and then orders 350 on a later date, the item was ordered 450 times. This information can be retrieved with the following SQL query:

SELECT ProductID, SUM(Quantity) AS [Total Times Ordered] FROM OrderProduct GROUP BY ProductID

But this query is very time consuming because every OrderProduct row must be accessed and then the quantity added to a total. What can be done to speed up this process?

Horizontally partition the OrderProduct table.

Vertically partition the OrderProduct table.

Store derived data in the Product table. Add a new column to the Product table named something like TotalOrdered. Increase the value in this column by the amount ordered every time a product is ordered.

Put the SELECT statement inside a SQL transaction.

Question 30

The relational design for the Salesperson table is listed below. the primary key column is underlined. There are no foreign keys.

Salesperson (SalespersonID, LastName, FirstName, Address, City, State, PostalCode, Country, StartDate, EndDate)

The Human Resources Department often needs to find employees who started in a specific year. Therefore they run the following query and change the year to whatever year they wish:

SELECT FROM salesperson WHERE EXTRACT(year FROM StartDate) = 2015;

This query is slow. What can be done to speed up the query?

Create a non-clustered index on StartDate

Create a covering index on StartDate

Create a function-based index on the expression: EXTRACT(year FROM StartDate)

Create a clustered index on StartDate