Modern Database Management (12th Edition)

9-27

An investment in drilling often occupies one-third to two third of the total cost while exploring for fluid. Advances in drilling technology can reduce these costs substantially. They key point is redesigning the scheme of drilling fluid. A research study identifies the following factors which impact the 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 factor, 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. Classes are water based, oil based, synthetic; while 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.

Design a star schema for this problem. See Figure 9-10 for the format you should follow.

Identify the grain of the fact table.

Can you think of any other facts to be included in the fact table? Which one and why?

Is there a probability of changing the schema into snowflake schema? If yes, which dimensions should be normalized and how?

Assuming that various characteristics of geography, formation and well type change over time. How do you propose designing