Activity 6.2: Optimizing a Physical
Data Design
This activity requires the use of the optimization techniques discussed in this
module. You will consider ways to optimize a physical data design for
insertions, updates, and deletions, as well as any nonfunctional requirements
specified by the activity’s scenario.
Objectives
After completing this activity, you will be able to:
!
Identify where data designs can potentially be optimized.
!
Determine the appropriate optimization technique.

Before You Begin
This activity is completed individually, with a class discussion to follow.

Time to complete this activity: 25 minutes
36 Activity 6.2: Optimizing a Physical Data Design

Exercise 1:
Determining Areas for Optimization
In this exercise, you will evaluate the logical data design presented in the
following illustration, and determine areas in which the physical data design
could potentially be optimized.
Bitmap found in file v06_1609a_act61-answer.vsd or .bmp
TypeID (FK) (N)Int
FirstName VChar 25

Invoice
Name VChar 60
Address1 VChar 40
Address2 VChar 40
City VChar 30
State VChar 20
Zip VChar 10
ClientID (PK) DInt
Client
EmployeeID (FK) Int
Make VChar 100
Model VChar 100
VIN VChar 30
Year Date
BeginMileage Int
EndMileage Int
VehicleID (PK) Int
Vehicle
Is Issued
1
Completes
1
1
∞
∞∞
∞
Is Sent To
1
0
∞

∞
∞∞
∞
TypeDesc VChar 255
TypeID (PK) Int
EmployeeType
Is Classified As
1
1
1
∞
∞∞
∞
1
1
∞
∞∞
∞
ClientID(FK) DInt
EmployeeID(FK) Int
StartDate Date
EndDate Date
Description VChar 255
ContractID(PK) DInt
Contract
Purchases
1
1
0
∞

∞
1

Activity 6.2: Optimizing a Physical Data Design 37
Scenario
Ferguson and Bardell, Inc. has identified the following issues:
1. For ease of reporting, year-to-date (YTD) hours must be tracked for each
consultant.
Create a new field in the Employee Table, YTDHours Float
2. Management runs tax reports that require frequent location of employee
records by Social Security number (SSN). The database administrators have
noticed that running these reports drastically reduces the performance of the
system during the day. You have been requested to fix this performance
issue as soon as possible.
Solutions can vary. Some solutions may include:
Create an index on SSN field.
Use SSN as the primary key for the Employee table; then store the
records in SSN order.
3. Currently timesheets are queried by last name to run several reports and
queries. This situation has been identified as a performance problem by the
database administrators, and they are requesting a fix as soon as possible.
(The field is already indexed.)
Solutions can vary.
Denormalize the Timesheet table to include, as duplicates from the
Employee table, the FirstName, MiddleIntial, and LastName fields.
Create an index on the LastName field in the Timesheet table.
4. Consultants must be able to enter timesheet information even if the rest of

3. Write these optimizations in the space provided below. Be sure to include
any additional entities that might be required to resolve the issues identified
by Ferguson and Bardell, Inc.

Next, you will present your findings to the class.