Physical Data Warehouse Design - Data Warehouse Systems: Design and Implementation - page 279

7.12 Given the Department table below and the Employee table from Ex. 7.10 ,
show how a bitmap join index on attribute DeptKey would look like.

Department
Key

Department
Name

Location

d1 Management Brussels

d2 Production Paris

d3 Marketing London

d4 HumanResources Brussels

d5 Research Paris

7.13 Consider the tables Sales in Ex. 7.11 , Employee in Ex. 7.10 ,and
Department in Ex. 7.12 .

(a) Propose an indexing scheme for the tables, including any kind
of index you consider it necessary. Discuss possible alternatives
according to several query scenarios. Discuss the advantages and
disadvantages of creating the indexes.
(b) Consider the query:

SELECT E.EmployeeName, SUM(S.SalesAmount)
FROM Sales S, Employee E, Department D
WHERE S.EmployeeKey = E.EmployeeKey AND
E.DepartmentKey = D.DepartmentKey AND
(D.Location= ' Brussels ' OR D.Location = ' Paris ' )
GROUP BY E.EmployeeName

Explain a possible query plan that makes use of the indexes defined
in (a).