What is the likely data sparsity of the EMP_SEX column?
computer science
Description
1. What is
the likely data sparsity of the EMP_SEX column?
|
SELECT |
EMP_LNAME, EMP_FNAME, EMP_AREACODE, EMP_SEX |
|
FROM |
EMPLOYEE |
|
WHERE |
EMP_SEX = 'F' AND EMP_AREACODE = '615' |
|
ORDER BY |
EMP_LNAME, EMP_FNAME; |
2. What
indexes should you create? Write the required SQL commands.
|
SELECT |
EMP_LNAME, EMP_FNAME, EMP_AREACODE, EMP_SEX |
|
FROM |
EMPLOYEE |
|
WHERE |
EMP_SEX = 'F' AND EMP_AREACODE = '615' |
|
ORDER BY |
EMP_LNAME, EMP_FNAME; |
3.
Using Table 11.4 as an example, create two
alternative access plans. Use the following assumptions:
a. There are 8,000 employees.
b. There are 4,150 female employees.
c. There are 370 employees in area code 615.
d. There are 190 female employees in area code 615.
4. What is
the likely data sparsity of the EMP_DOB column?
|
SELECT |
EMP_LNAME, EMP_FNAME, EMP_DOB, YEAR(EMP_DOB) AS YEAR |
|
FROM |
EMPLOYEE |
|
WHERE |
YEAR(EMP_DOB) = 1976; |
5. Should
you create an index on EMP_DOB? Why or why not?
|
SELECT |
EMP_LNAME, EMP_FNAME, EMP_DOB, YEAR(EMP_DOB) AS YEAR |
|
FROM |
EMPLOYEE |
|
WHERE |
YEAR(EMP_DOB) = 1976; |
6. What
type of database I/O operations will likely be used by the query? (See Table
11.3.)
|
SELECT |
EMP_LNAME, EMP_FNAME, EMP_DOB, YEAR(EMP_DOB) AS YEAR |
|
FROM |
EMPLOYEE |
|
WHERE |
YEAR(EMP_DOB) = 1976; |
