Question 21

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)

Sales people often want to see all the customers in a region. Region values are EA, WE, CE, SO, FR. They run a query with the following WHERE clause. (The code for the region changes.)

SELECT * FROM Customer WHERE Region = "EA";

or a query like the following:

SELECT * FROM Customer WHERE Region = "CE";

The queries are slow. A normal index is not appropriate here because there are only a few possible data values for Region. What type of index on the Region column would likely improve the speed of these queries?

bitmap index

clustered index

function-based index

covering index

Question 22

Below is the relational design for the Sales table. The primary key is underlined. Foreign key columns are italicized.

Sales (SalespersonID, ProductID, CustomerID, SalesDate, Amount)

Access (SELECT, INSERT, UPDATE, DELETE) to the Sales table has always been slow. What could be done to improve this situation?

Add an index to the Amount column.

Cluster the Sales table with every table that is joined to via a foreign key.

Add a surrogate primary key replacing the current primary key that has 4 columns with a primary key that has 1 column.

Put each SQL statement in a SQL transaction.

Question 23 2

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 would like to add a photo to the Salesperson table to identify each salesperson. In testing, adding a an image column to this table slows down all processing against the Salesperson table. What could be done to reduce the impact of adding the picture?

Horizontally partition the Salesperson table.

Vertically partition the Salesperson table. One partition would be the primary key and the image. The other partition would be the primary key plus all the other columns (except the image).

Create a bitmap index on the image column.

Create a function based index on the image column.

Question 24 2 pts

Below are the relational designs for the Order and OrderProduct tables. The primary key is underlined. The foreign keys are italicized.

Order (OrderID, CustomerID, DateOrdered) OrderProduct (ProductId, OrderID, Quantity)

Whenever the first product on an order is created, both the Order row and the OrderProduct row must be created. This sometimes causes a problem if the system crashes after the Order row is created but before the OrderProduct row is created. Which of the following would eliminate this problem?

Cluster the Order and OrderProduct tables.

Horizontally partition the Order and OrderProduct tables.

Put both INSERT statements in a single SQL transaction.

Increase the number of checkpoints.

Flag this Question

Question 25

The relational design for the original Vendor table is displayed below. The primary key is underlined. There is no foreign key.

Vendor (VendorID, VendorName, Address, City, State, Zip, PhoneNum)

You want to add multiple phone numbers for a vendor. Initially you plan to modify the Vendor table and add a new table named VendorPhone defined as follows:

Vendor (VendorID, VendorName, Address, City, State, Zip) VendorPhone(VendorID, VendorPhone, ContactName)

The new table (VendorPhone) is based on the following ERD.

This new design will enable you to add any number of additional phones for each vendor. You added an index to all the primary key columns. You added an index to the foreign key column in VendorPhone. Processing was still very slow when you tried to list vendor names and their phone numbers in a test database. Which of the following would you try?

Combine the tables since they are in a 1:1 relationship.

Vertically partition the Vendor and VendorPhone tables.

Horizontally partition the Vendor and VendorPhone tables.

Cluster the Vendor and VendorPhone tables.

Vendor VendorPhone VendorlD VendorPhoneNum ContactName VendorName Address City State