database mangement

Problems 1-3 are based on the following query:
SELECT V_CODE, V_NAME, V_CONTACT, V_STATE
FROM VENDOR
WHERE V_STATE =’TN’
ORDER BY V_NAME;
1. What indexes should you create and why? Write the SQL command to create the indexes.
2. Assume that 10,000 vendors are distributed as shown in Table P11.18. What percentage of rows will be returned by the query?
Table P11.18 Distribution of Vendors by State

State
Number of
Vendors

State
Number of
Vendors
AK
15
MS
47
AL
55
NC
358
AZ
100
NH
25
CA
3244
NJ
645
CO
345
NV
16
FL
995
OH
821
GA
75
OK
62
HI
68
PA
425
IL
89
RI
12
IN
12
SC
65
KS
19
SD
74
KY
45
TN
113
LA
29
TX
589
MD
208
UT
36
MI
745
VA
375
MO
35
WA
258
3. What type of I/O database operations would be most likely to be used to execute that query?
4. Using Table 11.4 as an example and the information from problems 1-3, create two alternative access plans.

Plan
Step
Operation
I/O
Operations
I/O
Cost
Resulting Set Rows
Total I/O
Cost

Table 11.4