Q.3 Read the following case and write the answer of (a, b) questions. (20 points)

Drilling often accounts for one-third to two-thirds of the total cost in the search for fluid. Advances in drilling technology can reduce these costs substantially. The key point is redesigning the scheme of drilling fluid.

A research study identifies the following factors which impact drilling fluid efficiency:

Time: Date;

Geography: Country, Oil field, Block, Well

Drilling fluid type: Divided into classes (water-based, oil-based, synthetic) and subclasses (dispersed, polymer, and calcium treated and subclasses)

Formation: Oil and Gas, Salt and Gypsum, Salt, Gypsum

Well type: Vertical, Directional and Horizontal

The primary idea is to increase the drilling speed and reduce drilling fluid costs. The researchers want to investigate how each factor and its attributes can make an impact on drilling fluid cost and speed.

a. Design a star schema for this problem.

b. What other facts could be included in the fact table? Why?