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: Time, Geography, Drilling fluid type, Formation, Complex circs, and Well type. For each of the underlying factors, the following attributes and hierarchies have been identified as under:

• Time: Date

• Geography: Country, Oil field, Block, Well

• Drilling fluid type: This factor can be further divided into classes and each class has subclasses as well. The classes are water-based, oil-based, and synthetic; subclasses are dispersed, polymer, and calcium treated.

• 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. Identify the grain of the fact table.0

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

d. Can the schema be changed into the snowflake schema? If so, which dimensions should be normalized, and how?

e. Assuming that various characteristics of geography, formation, and well type change over time, how would you design the star schema to include these changes? Why?