hoff fm pgi-xviii 9/24/02 8:24 AM Page I EFFECTIVE DATABASE DESIGN for Geoscience Professionals hoff fm pgi-xviii 9/24/02 8:24 AM Page II hoff fm pgi-xviii 9/24/02 8:24 AM Page III EFFECTIVE DATABASE DESIGN for Geoscience Professionals D a v i d R . H o f f m a n hoff fm pgi-xviii 9/24/02 8:24 AM Page IV Copyright ©2002 by PennWell Corporation 1421 S. Sheridan Rd./P.O. Box 1260 Tulsa, Oklahoma 74112/74101 800-752-9764 [email protected] www.pennwell-store.com www.pennwell.com Managing Editor: Marla Patterson Cover designed by Amy Spehar Book designed by Amy Spehar This is an uncorrected manuscript ISBN 0-87814-828-0 All rights reserved. No part of this book may be reproduced, stored in a retrieval system, or transcribed in any form or by any means, electronic or mechanical, including photocopy- ing and recording, without the prior written permission of the publisher Printed in the United States of America 1 2 3 4 5 06 05 04 03 02 hoff fm pgi-xviii 9/24/02 8:24 AM Page V C o n t e n t s List of Figures _____________________________________________________XI Acknowledgments_______________________________________________XV Dedication ________________________________________________________XVII 1 Overview and Introduction_______________________________1 Objectives ________________________________________________________2 Conventions and Nomenclature ____________________________________3 Database Misconceptions__________________________________________4 Case History: Simple Databases_________________________________4 2 Key Terms and Concepts__________________________________7 Key Terms ________________________________________________________7 Database ______________________________________________________8 Database Management System _________________________________8 Tables_________________________________________________________9 Records ______________________________________________________10 Fields ________________________________________________________10 Important Concepts_______________________________________________12 Relational vs. Flat-File Databases_______________________________12 Organization of Flat-File Databases_____________________________12 Relational Database Concepts__________________________________13 3 The Exploration-Development Data Life Cycle _______21 Overview of Exploration and Development Cycle Phases____________22 Reconnaissance Phase____________________________________________23 Reconnaissance Phase Data Types and Issues___________________26 Exploration Drilling Phase_________________________________________31 Acquisition and Interpretation of Exploration Drilling Data_______31 Shift from Surface to Well-Centric Subsurface Data______________33 Geophysical Data Acquisition and Interpretation ________________34 Backward Integration__________________________________________35 Forward Modeling ____________________________________________35 Field Delineation Phase___________________________________________36 Impact on Data Management __________________________________36 Shift to Production and Engineering Data_______________________37 Data Volume Impacts__________________________________________37 hoff fm pgi-xviii 9/24/02 8:24 AM Page VI VI EFFECTIVE DATABASE DESIGN for Geoscience Professionals Development Phase ______________________________________________38 Data Interpretation____________________________________________38 Exploitation Phase________________________________________________39 Hardware Requirements_______________________________________40 Software Considerations_______________________________________40 Abandonment and Remediation Phase_____________________________41 Data Storage Considerations___________________________________42 Special Cases: Acquisitions and Disposals _________________________42 Acquisitions __________________________________________________42 Disposals_____________________________________________________43 Data Formats and Delivery Methods____________________________43 Summary________________________________________________________44 4 Planning Database Projects_____________________________47 Defining Project Objectives________________________________________48 Defining Data Management Objectives_____________________________48 Defining the Function of the Database__________________________48 Defining the User Interface ____________________________________49 Life Expectancy of the DBMS __________________________________49 Defining Specific End-User Needs _________________________________50 Case History: Scaled Application Development__________________50 Involving the User in the Process_______________________________51 Keeping the User in the Loop__________________________________53 Continuous Improvement Processes____________________________54 Tailoring the Database to the Data_________________________________55 Other Considerations_____________________________________________57 Support ______________________________________________________57 Hardware Considerations______________________________________59 The Database Management System (DBMS)________________________63 Selection Criteria______________________________________________64 Technical Considerations ______________________________________65 Nontechnical Considerations___________________________________67 General DBMS Types_____________________________________________70 Database Applications____________________________________________72 Flexibility in Modification and Customization____________________73 Ability to Link to Interpretive Applications ______________________74 Selecting a Data Model________________________________________75 Case History: DBMS Selection and Customization_______________76 Developing a Proprietary Data Model___________________________77 Selecting a Computing Platform___________________________________78 Application-Driven Databases__________________________________79 Application-Independent Databases____________________________79 Importance of Standardization_____________________________________80 hoff fm pgi-xviii 9/24/02 8:24 AM Page VII Contents VII Upgrade and Scalability Issues_________________________________80 Accuracy Issues_______________________________________________82 Data Transfer Issues___________________________________________82 Role of the Database Manager (DBM)______________________________83 Managers and Administrators__________________________________83 General Roles and Responsibilities_____________________________84 Integration and Coordination Functions_________________________85 User Communications Functions_______________________________86 5 Data Types and Formats_________________________________87 Introduction______________________________________________________87 Scalability and Portability Considerations_______________________88 Modifications to Commercial Data Type Definitions______________88 Data Validation and Exceptions____________________________________89 Use Validation Rules __________________________________________89 Duplication or Redundant Data_________________________________90 Storing Derived Data__________________________________________90 Common Geotechnical Data Types and Formats____________________91 Character-Based (Text) Data____________________________________91 Numerical Data_______________________________________________96 Date and Time Data ___________________________________________97 Logical Data__________________________________________________99 Binary Data___________________________________________________99 6 Designing the Database_________________________________103 Data Dictionaries________________________________________________103 Importance of Data Dictionaries_______________________________104 Original vs. Derived Data ________________________________________105 What Data to Store___________________________________________105 What Data not To Store_______________________________________106 History Files and Deleted Records Files ___________________________107 CASE Tools _____________________________________________________108 Definition and Application____________________________________109 Use during Development_____________________________________109 Other Database Tools____________________________________________109 Customizing Commercial Products _______________________________110 Data Model Extensions_______________________________________112 Generic Data Tables _____________________________________________113 7 Geotechnical Data________________________________________119 Introduction_____________________________________________________119 Coordinate Data_________________________________________________120 hoff fm pgi-xviii 9/24/02 8:24 AM Page VIII VIII EFFECTIVE DATABASE DESIGN for Geoscience Professionals Latitude and Longitude_______________________________________120 Universal Transverse Mercator Projection Method______________122 Meets and Bounds___________________________________________123 Other Coordinate Systems____________________________________123 Directional Survey Data__________________________________________123 Observed vs. Computed Data_________________________________124 Computed Data______________________________________________128 Computational Methods______________________________________129 Depth-Related Data______________________________________________131 Stratigraphic Tops, Zones, and Markers___________________________132 Tops and Markers____________________________________________132 Zones and Layers____________________________________________133 Stratigraphic Exception Codes________________________________133 Stratigraphic Nomenclature___________________________________134 Time-Related Data_______________________________________________135 Geophysical Data____________________________________________136 Geological Age Data _________________________________________138 Log and Borehole Data __________________________________________138 General Organization of Log Data_____________________________139 Log Data Storage and Transfer________________________________140 Data Editing Considerations __________________________________144 Problems of Log Data Management___________________________145 Other Wireline Data__________________________________________146 Petrophysical Data ______________________________________________147 Petrophysical Data from Cores________________________________147 Indirect (Computed) Petrophysical Data________________________148 Derived Petrophysical Data___________________________________149 Data Management Problems of Petrophysical Data_____________150 Spatial Data and GIS Systems____________________________________151 Geotechnical Data and GIS Applications_______________________151 Digital Document Storage________________________________________152 Objectives of Digital Document Storage _______________________153 8 Data Reformatting _______________________________________159 Goals and Objectives of Data Reformatting________________________159 Standardization Goals________________________________________160 Application Integration Objectives_____________________________160 Types of Reformatting Problems _________________________________161 Simple Data Manipulation____________________________________161 Complex Reformatting Problems______________________________162 Data Conversion with Reformatting ___________________________163 Data Formatting Strategies_______________________________________164