database design case study

  [email protected]   +592 697 0080.   +592 697 0080.

Case Study 1

This is the first of three case studies that are used to guide the reader through the six steps outlined in Six-Step Relational Database Design™ . The most important outputs of the six step database design process are depicted here, but the details of each of the steps and intermediary outputs are detailed in the book.

Below is the scenario for Case Study 1 as described in the Six-Step Relational Database Design™ book:

A small accounting firm wants a simple HR application that will help it to keep track of its employees, their positions, allowances, salary scales, and which company vehicles their employees drive. The application must keep track of all the positions at the firm, the employees filling these positions, the allowances for these positions, the salary scales for these positions, and the company vehicles assigned to these positions.

List of entities and their corresponding attributes

Below is the list of entities and their corresponding attributes that is output from Step 1 of the six step database design process as described in the Six-Step Relational Database Design™ book:

Entity-Relationship diagrams

Below is the Simplified Entity-Relationship diagram that is output from Step 3 of the six step database design process as described in the Six-Step Relational Database Design™ book:

Below is the Detailed Entity-Relationship diagram that is output from Step 5 of the six step database design process as described in the Six-Step Relational Database Design™ book:

Relational-Model diagram

Below is the Relational-Model diagram that is output from Step 6 of the six step database design process as described in the Six-Step Relational Database Design™ book:

SQL commands

The SQL commands below can be used to implement the design depicted above in a MySQL database. Some modifications will be necessary to execute these commands on MS SQL Server, Oracle, or any other RDBMS. Detailed implementation considerations can be found in the Six-Step Relational Database Design™ book.

Complete Database Design Course for Beginners

This database design course will give you a deeper grasp of database design. Caleb Curry teaches the equivalent of an entire college course during this eight hour video.

Databases are a key part of most developer jobs and this course will help you understand the database concepts you need to know.

Learn more about this course by reading Caleb's blog post about the course.

You can watch the full video on the freeCodeCamp.org YouTube channel (8 hour watch).‌

I'm a teacher and developer with freeCodeCamp.org. I run the freeCodeCamp.org YouTube channel.

If you read this far, thank the author to show them you care. Say Thanks

Learn to code for free. freeCodeCamp's open source curriculum has helped more than 40,000 people get jobs as developers. Get started

Relational Database Design and Implementation, 4th Edition by Jan L. Harrington

Get full access to Relational Database Design and Implementation, 4th Edition and 60K+ other titles, with a free 10-day trial of O'Reilly.

There are also live events, courses curated by job role, and more.

Database Design Case Study #2: East Coast Aquarium

This chapter presents the second of three major case studies in the book. It presents a database environment for which two distinct databases that do not share data are an appropriate solution. The larger of the two databases include several many-to-many relationships that must be handled by the design. The case study also includes the use of a CASE tool to provide a prototype user interface for database applications.

Get Relational Database Design and Implementation, 4th Edition now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Don’t leave empty-handed

Get Mark Richards’s Software Architecture Patterns ebook to better understand how to design components—and how they should interact.

It’s yours, free.

Check it out now on O’Reilly

Dive in for free with a 10-day trial of the O’Reilly learning platform—then explore all the other resources our members count on to build skills and solve problems every day.

Home » Data Modeling / Database » ERD and Database Implementation: Bridging the Gap Between Concept and Reality

ERD and Database Implementation: Bridging the Gap Between Concept and Reality

• Posted on September 15, 2023
• / Under Data Modeling / Database

In the world of database design, translating abstract concepts into tangible structures is a crucial step toward building functional and efficient database systems. This transformation from Entity-Relationship Diagrams (ERDs) to actual database schemas, including SQL table creation, is a fundamental process in the database development lifecycle. In this article, we will explore how ERDs serve as a bridge between the conceptualization of data and its practical implementation within a database.

Understanding the ERD

Before delving into the intricacies of database implementation, it is essential to comprehend the purpose and components of an ERD. An Entity-Relationship Diagram is a visual representation of the data model, capturing the entities, their attributes, and the relationships between them. The ERD serves as a blueprint for designing the database structure, helping database developers, administrators, and stakeholders to visualize and plan the data organization effectively.

Components of an ERD

• Entities : These are objects or concepts represented within the database, often corresponding to real-world entities like customers, products, or employees. Entities are depicted as rectangles in an ERD.
• Attributes : Attributes define the characteristics or properties of entities. For instance, for a “Customer” entity, attributes might include “CustomerID,” “FirstName,” “LastName,” and “Email.” Attributes are typically shown as ovals in an ERD, connected to their respective entities.
• Relationships : Relationships indicate how entities are connected or associated with one another. They clarify dependencies between entities and can be one-to-one, one-to-many, or many-to-many. Relationship lines between entities specify these associations, and they often come with cardinality indicators that show the allowed quantity of related entities.

Translating ERDs into Database Schemas

The process of moving from ERDs to actual database schemas involves several key steps:

1. Entity to Table Mapping

Entities in the ERD are transformed into database tables. Each attribute within an entity becomes a column in the corresponding table. For instance, if we have a “Customer” entity with attributes “CustomerID,” “FirstName,” “LastName,” and “Email,” we would create a “Customers” table with columns for each of these attributes.

2. Relationship Implementation

Relationships between entities in the ERD are realized through various mechanisms in SQL:

• One-to-One Relationship : In this case, one entity’s primary key becomes a foreign key in the other entity’s table.
• One-to-Many Relationship : The table on the “one” side of the relationship contains a foreign key that references the primary key of the table on the “many” side.
• Many-to-Many Relationship : Typically, this is implemented using a junction table or associative entity that contains foreign keys referencing the tables involved in the relationship.

3. Key Constraints and Data Types

For each column in the database table, data types are specified to define what kind of data can be stored. Additionally, key constraints such as primary keys and foreign keys are defined to enforce data integrity and relationships between tables.

4. Indexing

To improve query performance, indexes are created on columns that are frequently used in search conditions. Indexes provide a quicker way to access data.

5. Data Integrity Rules

Database designers enforce data integrity through constraints. For example, “NOT NULL” constraints ensure that a column cannot contain NULL values, while “UNIQUE” constraints guarantee that values in a column are unique.

SQL Table Creation Example

Let’s illustrate this process with a simple example:

Suppose we have an ERD representing a library system with entities “Book” and “Author” connected by a many-to-many relationship “Author Wrote Book.” Here’s how we would translate this into SQL table creation:

• Create a “Books” table with columns for book attributes (e.g., BookID, Title, PublicationYear).
• Create an “Authors” table with author attributes (e.g., AuthorID, FirstName, LastName).
• Create a “AuthorBook” table to represent the many-to-many relationship. This table would typically include two columns, “AuthorID” and “BookID,” both of which serve as foreign keys referencing the “Authors” and “Books” tables, respectively.

By following these steps, we have successfully translated the ERD into an actual database schema with the necessary tables, relationships, and constraints.

A Case Study on ERD: Online Bookstore

Imagine you are tasked with designing the database for an online bookstore. The system should allow customers to browse books, make purchases, and manage their accounts. Authors and publishers will also have accounts to add and manage books, while administrators will oversee the entire system.

Step 1: Identify Entities

The first step in ERD modeling is identifying the entities relevant to the system. In this case, we can identify the following entities:

• Customer : Represents the individuals who use the online bookstore. Attributes might include CustomerID, FirstName, LastName, Email, and Password.
• Book : Represents the books available for purchase. Attributes might include BookID, Title, Author(s), ISBN, Price, and PublicationYear.
• Author : Represents the authors of the books. Attributes might include AuthorID, FirstName, LastName, and Biography.
• Publisher : Represents the publishers of the books. Attributes might include PublisherID, Name, and Address.
• Order : Represents customer orders. Attributes might include OrderID, OrderDate, TotalAmount, and Status.
• OrderItem : Represents individual items within an order. Attributes might include OrderItemID, BookID, Quantity, and Subtotal.
• Administrator : Represents system administrators. Attributes might include AdminID, FirstName, LastName, Email, and Password.

Step 2: Define Relationships

Next, we determine how these entities are related to each other:

• A Customer can place multiple Orders (one-to-many relationship).
• An Order can contain multiple OrderItems (one-to-many relationship).
• A Book can be written by multiple Authors , and an Author can write multiple Books (many-to-many relationship).
• A Book can have only one Publisher , but a Publisher can publish multiple Books (many-to-one relationship).
• An Administrator oversees the entire system but is not directly related to other entities in this simplified model.

Step 3: Create the ERD

Now, we create the ERD to visually represent these entities and their relationships. Here’s a simplified version of the ERD for our online bookstore:

Step 4: Define Attributes

For each entity in the ERD, we define its attributes. For example:

• Customer : CustomerID (Primary Key), FirstName, LastName, Email, Password.
• Book : BookID (Primary Key), Title, ISBN, Price, PublicationYear.
• Author : AuthorID (Primary Key), FirstName, LastName, Biography.
• Publisher : PublisherID (Primary Key), Name, Address.
• Order : OrderID (Primary Key), OrderDate, TotalAmount, Status.
• OrderItem : OrderItemID (Primary Key), BookID (Foreign Key), Quantity, Subtotal.

Step 5: Normalize the Database (Optional)

Normalization is the process of organizing data in a database to reduce redundancy and improve data integrity. Depending on the complexity of your system, you may need to apply normalization rules to the tables.

Step 6: Implement the Database

Finally, the ERD serves as a guide for creating the actual database tables, defining relationships, constraints, and data types using SQL or a database management tool. This step involves translating the ERD into SQL statements for table creation.

In this case study, we’ve illustrated the process of ERD modeling for an online bookstore. ERDs play a crucial role in designing effective database systems, ensuring that data is organized logically, and relationships are well-defined to support the functionality of the application.

Entity-Relationship Diagrams (ERDs) are invaluable tools for designing and visualizing database structures. They serve as a blueprint for database implementation, guiding the transformation of abstract concepts into concrete database schemas. Through the mapping of entities to tables, the creation of relationships, and the definition of data types and constraints, ERDs bridge the gap between data modeling and real-world database systems. This process, though intricate, is essential for building robust and efficient databases that meet the needs of organizations and applications.

Leave a Comment Cancel reply

Your email address will not be published. Required fields are marked *

Save my name, email, and website in this browser for the next time I comment.

• Visual Paradigm Online
• Request Help
• Customer Service
• Community Circle
• Demo Videos
• Visual Paradigm
• YouTube Channel
• Academic Partnership

• Corpus ID: 60612693

A Suite of Case Studies in Relational Database Design

• Weiguang Zhang
• Published 1 April 2012
• Computer Science

Figures from this paper

3 Citations

Teaching database querying in the cloud, new rules for deriving formal models from text, teaching conceptual design capture, 7 references, a relational model of data large shared data banks, a relational model of data for large shared data banks.

• Highly Influential

The Entity-Relationship model

Related papers.

Showing 1 through 3 of 0 Related Papers

• Computer Science and Engineering
• Database Design (Video) 
• Co-ordinated by : IIT Madras
• Available from : 2009-12-31
• Introduction to Database Management System
• Conceptual Designs
• Relational Model
• Structured Query Language
• Structured Query Language II
• ER Model to Relational Mapping
• Functional Dependencies and Normal Form
• ER Model to Relational Model Mapping
• Storage Structures
• Indexing Techniques Single Level
• Indexing Techniques Multi-Level
• Constraints and Triggers
• Query Processing and Optimization
• Query Processing and Optimization II
• Query Processing and Optimization - III
• Transaction Processing Concepts
• Transaction Processing and Database Manager
• Foundation for Concurrency Control
• Concurrency Control Part - I
• Concurrency Control Part - 2
• Concurrency Control Part - 3
• Concurrency Control Part - 4
• Distributed Transaction Models
• Basic 2-Phase and 3-phase commit protocol
• Concurrency Control for Distributed Transaction
• Introduction to Transaction Recovery
• Recovery Mechanisms II
• Recovery Mechanisms III
• Introduction to Data Warehousing and OLAP
• Case Study : MYSQL
• Case Study ORACLE and Microsoft Access
• Data Mining and Knowledge Discovery
• Data Mining and Knowledge Discovery Part II
• Object Oriented Databases
• Object Oriented Databases II
• XML - Introductory Concepts
• XML Advanced Concepts
• XML Databases
• Case Study - Part One Database Design
• Case Study - Part 2 Database Design
• Watch on YouTube
• Assignments
• Transcripts

Case Studies Examples Scenarios Database System DBMS

Most of the time you see the case studies and scenario-based questions in the Database System (DBMS) paper. Keeping in view, I am sharing with you some of the case study base questions of the database course.

Examples of Case Studies and Scenarios questions from DBMS

• Examples of Case Studies and scenarios from the Database System.
• How you can make a database from the scenario mentioned below.
• How you can normalize the database tables from the case studies mentioned below.
• How to draw the Entity-relationship diagram from the given case study.
• How to draw the Data flow diagram from the case studies mentioned below.
• What database model is suitable for the case studies mentioned below.
• What kind of database users are suitable for the given case study.
• What kind of database redundancies and inconsistencies are possible in the given scenario.
• How You can write SQL Queries on the tables of the mentioned case study.
• Find the possible database keys from the tables of these case studies.
• Suggest the relationships among the tables of the given scenarios.

Vehicle information dissemination system for Cloud  Android Project for BCS BSIT MCS BSSE

Gym and Fitness Management System Project IN C# for BCS BSIT MCS BSSE

HR Management System Project in C# and VB.NET for BCS BSIT MCS BSSE

Employees Attendance System via Fingerprint  in C# and VB.NET for BCS BSIT MCS BSSE

Pharmacy Record Management System Project in PHP, ASP or C#.NET

Car information System using Android and Arduino final year Project for BSCS BSIT MCS BSSE

Agile File Master App final year project for BSCS BSIT MCS BSSE

Android Messenger App final year project for BSCS BSIT MCS BSSE

Android Call Recorder App final year project for BSCS BSIT MCS BSSE

Music Listening App final year project for BSCS BSIT MCS BSSE

Like mind matches Android application – Final year project for MCS

Financial Helper Using QR/Barcode Scanner Android Final year project for MCS BSCS BSSE

My Grocery List Mobile Application Project in  Android

If you are still in reading the more case studies, then you can read 100+ case studies .

Related Posts:

• Case Studies Examples Scenarios OOP
• History of Database System (DBMS)
• Leadership Case Studies MCQs
• Ethical Dilemmas and Case Studies MCQs
• Data Independence in DBMS (Database)
• 3 Tier Database Architecture in DBMS

You must be logged in to post a comment.

On the Use and Construction of Wi-Fi Fingerprint Databases for Large-Scale Multi-Building and Multi-Floor Indoor Localization: A Case Study of the UJIIndoorLoc Database

• Kim, Kyeong Soo
• Smith, Jeremy S.

Large-scale multi-building and multi-floor indoor localization has recently been the focus of intense research in indoor localization based on Wi-Fi fingerprinting. Although significant progress has been made in developing indoor localization algorithms, few studies are dedicated to the critical issues of using existing and constructing new Wi-Fi fingerprint databases, especially for large-scale multi-building and multi-floor indoor localization. In this paper, we first identify the challenges in using and constructing Wi-Fi fingerprint databases for large-scale multi-building and multi-floor indoor localization and then provide our recommendations for those challenges based on a case study of the UJIIndoorLoc database, which is the most popular publicly available Wi-Fi fingerprint multi-building and multi-floor database. Through the case study, we investigate its statistical characteristics with a focus on the three aspects of (1) the properties of detected wireless access points, (2) the number, distribution and quality of labels, and (3) the composition of the database records. We then identify potential issues and ways to address them using the UJIIndoorLoc database. Based on the results from the case study, we not only provide valuable insights on the use of existing databases but also give important directions for the design and construction of new databases for large-scale multi-building and multi-floor indoor localization in the future.

• indoor localization;
• Wi-Fi fingerprint database;
• UJIIndoorLoc

Integrated framework for geological modeling: integration of data, knowledge, and methods

• Original Paper
• Published: 08 July 2024
• Volume 83 , article number  303 , ( 2024 )

Cite this article

• Hong Li 1 ,
• Deping Chu 1 ,
• Run Wang 3 ,
• Guoxi Ma 4 ,
• Chuanyang Lei 5 &
• Shengyong Pan 4  

83 Accesses

Explore all metrics

Three-dimensional (3D) geological modeling from limited and scattered information is essential for engineering geological investigation and design. Previous studies have encountered limitations when using a single modeling approach in complex tasks involving diverse geological structures, due to difficulties in accommodating the heterogeneity of geological structures and data imbalances. In response to this situation, this work presented an integrated geological modeling framework enabling the fusion of multi-source data, the integration of data and knowledge, and the combination of multiple modeling methods. Initially, multi-source data were merged into a unified format and integrated with knowledge extracted from geological texts to create a geological knowledge graph and a geospatial database for modeling. The complexity of the geological setting was then quantified by constructing a joint influence function, which informed the division of the modeling area into several subregions with geological significance. According to the geological characteristics and data conditions, the appropriate method for each subregion was automatically matched for independent modeling and finally integrated into a complete 3D geological model. The results indicated that the proposed integrated framework provided a flexible solution for complex modeling tasks, simplifying the process by addressing simpler subtasks while retaining the ability to capture structural information in geological domains with diverse characteristics. Moreover, the integration of geological data and knowledge promoted the structured representation and utilization of geological knowledge, promising to provide richer information for model construction and validation. This is crucial for the developed model to be able to effectively support engineering geological exploration.

This is a preview of subscription content, log in via an institution to check access.

Access this article

Subscribe and save.

• Get 10 units per month
• Download Article/Chapter or Ebook
• 1 Unit = 1 Article or 1 Chapter
• Cancel anytime

Price includes VAT (Russian Federation)

Instant access to the full article PDF.

Rent this article via DeepDyve

Institutional subscriptions

Similar content being viewed by others

Construction of knowledge constraints: a case study of 3D structural modeling

Research on urban 3D geological modeling based on multi-modal data fusion: a case study in Jinan, China

Data Mining and Data-Driven Modelling in Engineering Geology Applications

Admiraal H, Cornaro A (2016) Why underground space should be included in urban planning policy–and how this will enhance an urban underground future. Tunn Undergr Space Technol 55(5):214–220

Article   Google Scholar  

Antonielli B, Iannucci R, Ciampi P et al (2023) Engineering–geological modeling for supporting local seismic response studies: insights from the 3D model of the subsoil of Rieti (Italy). Bull Eng Geol Environ 82(6):235

Bai T, Tahmasebi P (2020) Hybrid geological modeling: combining machine learning and multiple-point statistics. Comput Geosci 142:104519

Bianchi M, Kearsey T, Kingdon A (2015) Integrating deterministic lithostratigraphic models in stochastic realizations of subsurface heterogeneity impact on predictions of lithology hydraulic heads and groundwater fluxes. J Hydrol 531:557–573

Boisvert JB, Deutsch CV (2011) Programs for kriging and sequential gaussian simulation with locally varying anisotropy using non-euclidean distances. Comput Geosci 37(4):495–510

Calcagno P, Chilès JP, Courrioux G et al (2008) Geological modelling from field data and geological knowledge part I: modelling method coupling 3D potential-field interpolation and geological rules. Phys Earth Planet Inter 171:147–157

Caumon G, Collon-Drouaillet P, de Veslud CL et al (2009) Surface-based 3D modeling of geological structures. Math Geosci 41(8):927–945

Article   CAS   Google Scholar  

Chen DZ, Huang Z, Liu Y et al (2013) On Clustering Induced Voronoi Diagrams IEEE Symposium on Foundations of Computer Science. IEEE 54th Annual Symposium on Foundations of Computer Science, Microsoft Res New England, Berkeley, CA

Chen GX, Zhu J, Qiang MY et al (2018a) Three-dimensional site characterization with borehole data – a case study of Suzhou area. Eng Geol 234:65–82

Chen QY, Mariethoz G, Liu G et al (2018b) Locality-based 3-D multiple-point statistics reconstruction using 2-D geological cross sections. Hydrol Earth Syst Sci 22(12):6547–6566

Cherpeau N, Caumon G, Levy B (2010) Stochastic simulations of fault networks in 3D structural modeling. C R Geosci 342(9):687–694

Chicco JM, Fonte L, Mandrone G et al (2023) Hybrid (gas and geothermal) greenhouse simulations aimed at optimizing investment and operative costs: a Case Study in NW Italy. Energies 16(9):3931

Chu DP, Wan B, Li H et al (2021) Geological entity Recognition based on ELMO-CNN-BiLSTM-CRF. Model Earth Sci 46(8):3039–3048 (in Chinese)

Google Scholar  

Chu DP, Wan B, Li H et al (2022) A machine learning approach to extracting spatial information from geological texts in Chinese. Int J Geogr Inf Sci 36(11):1–25

de la Varga M, Schaaf A, Wellmann F (2019) GemPy 10: open-source stochastic geological modeling and inversion. Geosci Model Dev 12(1):1–32

de Rienzo F, Oreste P, Pelizza S (2008) Subsurface geological-geotechnical modelling to sustain underground civil planning. Eng Geol 96(3–4):187–204

Dou FF, Li XH, Xing HX et al (2021) 3D geological suitability evaluation for urban underground space development – a case study of Qianjiang Newtown in Hangzhou Eastern China. Tunn Undergr Space Technol 115:104052

Ferrer R, Emery X, Maleki M et al (2021) Modeling the uncertainty in the layout of geological units by Implicit Boundary Simulation Accounting for a Preexisting Interpretive Geological Model. Nat Resour Res 30(6):4123–4145

Frank T, Tertois AL, Mallet JL (2007) 3D-reconstruction of complex geological interfaces from irregularly distributed and noisy point data. Comput Geosci 33(7):932–943

Fu GY, Su XN, She YW et al (2019) Evidence for normal and deep-buried features of the Longquan Shan fault zone at the eastern margin of the tibetan plateau. J Asian Earth Sci 179:56–64

Gaillard C, Kasperski J (2015) Improving geological models of projects by risk management: the ponserand tunnel. Bull Eng Geol Environ 74(3):803–813

Giraud J, Ogarko V, Martin R et al (2021) Structural petrophysical and geological constraints in potential field inversion using the Tomofast-x v10 open-source code. Geosci Model Dev 14(11):6681–6709

Gonçalves IG, Kumaira S, Guadagnin F (2017) A machine learning approach to the potential-field method for implicit modeling of geological structures. Comput Geosci 103:173–182

Grose L, Laurent G, Ailleres L et al (2017) Structural data constraints for implicit modeling of folds. J Struct Geol 104:80–92

Grose L, Ailleres L, Laurent G et al (2021a) Modelling of faults in LoopStructural 1.0. Geosci Model Dev 14(10):6197–6213

Grose L, Ailleres L, Laurent GJ et al (2021b) LoopStructural 1.0: time-aware geological modelling. Geosci Model Dev 14:3915–3937

Guo JT, Wang JM, Wu LX et al (2020) Explicit-implicit-integrated 3-D geological modelling approach: a case study of the Xianyan demolition volcano (Fujian China). Tectonophysics 795:228648

Guo JT, Wang XL, Wang JM et al (2021) Three-dimensional geological modeling and spatial analysis from geotechnical borehole data using an implicit surface and marching tetrahedra algorithm. Eng Geol 284:106047

Hatcher RD, Bailey CM (2020) Structural Geology: Principles Concepts and Problems (3rd edition). New York: Oxford University Press

Hillier M, de Kemp E, Schetselaar E (2013) 3D form line construction by structural field interpolation (SFI) of geologic strike and dip observations. J Struct Geol 51:167–179

Hillier MJ, Schetselaar EM, de Kemp EA et al (2014) Three-dimensional modelling of Geological surfaces using generalized interpolation with radial basis functions. Math Geosci 46:931–953

Irakarama M, Laurent G, Renaudeau J et al (2021) Finite Difference Implicit Structural modeling of geological structures. Math Geosci 53:785–808

Jacquemyn C, Jackson MD, Hampson GJ (2019) Surface-based geological reservoir modelling using grid-free NURBS curves and surfaces. Math Geosci 51:1–28

Jessell M, Ogarko V, de Rose et al (2021) Automated geological map deconstruction for 3D model construction using map2loop 10 and map2model 1.0. Geosci Model Dev 14(8):5063–5092

Ji GJ, Wang Q, Zhou XY et al (2023) An automated method to build 3D multi-scale geological models for engineering sedimentary layers with stratum lenses. Eng Geol 317:107077

Jorgensen F, Hoyer AS, Sandersen PBE et al (2015) Combining 3D geological modelling techniques to address variations in geology data type and density - an example from Southern Denmark. Comput Geosci 81:53–63

Kaufmann O, Martin T (2008) 3D geological modelling from boreholes cross-sections and geological maps application over former natural gas storages in coal mines. Comput Geosci 34(3):278–290

Kemp E (2021) Spatial agents for geological surface modelling. Geosci Model Dev 14:6661–6680

Kentwell DJ (2019) Destroying the Distinction Between Explicit and Implicit Geological Modelling. Mining Geology 2019 SRK Consulting. https://www.kz.srk.com/en/publication/ap-destroying-distinction-between-explicit-and-implicit-geological-modelling

Lajaunie C, Courrioux G, Manuel L (1997) Foliation fields and 3D cartography in geology: principles of a method based on potential interpolation. Math Geol 29(4):571–584

Laurent G, Caumon G, Bouziat A (2013) A parametric method to model 3D displacements around faults with volumetric vector fields. Tectonophysics 590:83–93

Laurent G, Ailleres L, Grose L (2016) Implicit modeling of folds and overprinting deformation. Earth Planet Sci Lett 456:26–38

Lei JS, Zhao DP (2009) Structural heterogeneity of the Longmenshan fault zone and the mechanism of the 2008 Wenchuan earthquake (Ms 80). Geochem Geophys Geosyst 10(10):1–17

Li H, Wan B, Chu DP et al (2023) Progressive Geological Modeling and uncertainty analysis using machine learning. ISPRS Int J Geo-Inf 12:97

Liu H, Chen SZ, He L et al (2020) Generalized triangular prism interpolation method for geotechnical parameter characterization. Bull Eng Geol Environ 79(7):3417–3435

Liu XY, Li AB, Chen H et al (2022) 3D modeling method for Dome structure using Digital Geological Map and DEM. ISPRS Int J Geo-Inf 11(6):1–26

Liu H, Li WT, Gu SX et al (2023) Three–dimensional modeling of fault geological structure using generalized triangular prism element reconstruction. Bull Eng Geol Environ 82:118

Lyu M, Ren B, Wu B et al (2021) A parametric 3D geological modeling method considering stratigraphic interface topology optimization and coding expert knowledge. Eng Geol 293:106300

MacCormack KE, Berg RC, Kessler H et al (2019) 2019 Synopsis of Current Three-Dimensional Geological Mapping and Modelling in Geological Survey Organizations. Alberta Energy Regulator/Alberta Geological Survey, AER/AGS Special Report 112, 307p

Madsen RB, Høyer A, Andersen LT et al (2022) Geology-driven modeling: a new probabilistic approach for incorporating uncertain geological interpretations in 3D geological modeling. Eng Geol 309:106833

Mallet J-L (1992) Discrete smooth interpolation in geometric modelling. Comput-Aided Des 24(4):178–191

Mallet J-L (2002) Geomodeling. Oxford University Press

Manchuk JG, Deutsch CV (2019) Boundary modeling with moving least squares. Comput Geosci 126:96–106

Menegoni N, Giordan D, Perotti CD et al (2019) Detection and geometric characterization of rock mass discontinuities using a 3D high-resolution digital outcrop model generated from RPAS imagery – ormea rock slope Italy. Eng Geol 252:145–163

Ming J, Pan M (2009) An Improved Horizons Method for 3D Geological Modeling from Boreholes. International Conference on Environmental Science & Information Application Technology IEEE 369–374

Ming J, Pan M, Qu HG et al (2010) GSIS: a 3D geological multi-body modeling system from netty cross-sections with topology. Comput Geosci 36(6):756–767

Perrin M, Rainaud J-F (2013) Shared earth modeling: knowledge driven solutions for building and managing subsurface 3D geological models. IFP, Paris, p 390

Qiu QJ, Xie Z, Wu L et al (2020) Automatic spatiotemporal and semantic information extraction from unstructured geoscience reports using text mining techniques. Earth Sci Inf 13(4):1393–1410

Qiu QJ, Xie Z, Ma K et al (2022) Spatially oriented convolutional neural network for spatial relation extraction from natural language texts. Trans GIS 26(2):839–866

Qiu QJ, Wang B, Ma K et al (2023) Geological profile-text information association model of mineral exploration reports for fast analysis of geological content. Ore Geol Rev 153:105278

Shen Y, Li A, Huang J et al (2022) Three-dimensional modeling of loose layers based on stratum development law. Open Geosci 14:1480–1500

Shi C, Wang Y (2022) Machine learning of three–dimensional subsurface geological model for a reclamation site in Hong Kong. Bull Eng Geol Environ 81(12):504

Silverman BW (1986) Density Estimation for Statistics and Data Analysis. Chapman and Hall, New York

Song RB, Qin XQ, Tao YQ et al (2019) A semi-automatic method for 3D modeling and visualizing complex geological bodies. Bull Eng Geol Environ 78:1371–1383

Song GH, Wang M, Jiang DQ et al (2020) Along-strike structural linkage and interaction in an active thrust fault system: a case study from the western Sichuan foreland basin China. Basin Res 33(1):210–226

Sun HY, He HL, Shi F et al (2017) Seismogenic capability of the northeastern segment of the Longmenshan Thrust Zone and its tectonic role at the Eastern Tibetan Plateau. Acta Geol Sin-(Engl Ed) 91(5):1930–1931

Tang BY, Wu CL, Li XC et al (2015) A fast progressive 3D geological modeling method based on borehole data. Rock Soil Mech 36(12):3633–3838 (in Chinese)

Wan B, Dong S, Chu DP et al (2023) A deep neural network model for coreference resolution in geological domain. Inf Process Manage 60:103268

Wang CB, Ma XG, Chen JG et al (2018) Information extraction and knowledge graph construction from geoscience literature. Comput Geosci 112:112–120

Wang B, Lei CY, Liu ZX et al (2021a) A geological 3D modeling method of comprehensive geological section for Chengdu. Sediment Geol Tethyan Geol 40(1):1009–3850 (in Chinese)

Wang B, Wu L, Li WJ et al (2021b) A semi-automatic approach for generating geological profiles by integrating multi-source data. Ore Geol Rev 134:104190

Wang Q, Li H, Xia SB et al (2022) Geometry of the quaternary strata along the middle segment of the Longmen Shan and its formation mechanism: insights from AMT ERT and borehole data. Tectonophysics 826:229226

Wellmann F, Caumon G (2018) 3-D structural geological models: concepts methods and uncertainties. Adv Geophys 59:1–121

Wellmann JF, Thiele ST, Lindsay MD et al (2016) Pynoddy 1.0: an experimental platform for automated 3-D kinematic and potential field modelling. Geosci Model Dev 8(3):1019–1035

Wu ZC, Guo FS, Li JT (2019) The 3D modelling techniques of digital geological mapping. Arab J Geosci 12(15):467

Wu XC, Liu G, Weng ZP et al (2021) Constructing 3D geological models based on large-scale geological maps. Open Geosci 13:851–866

Yamamoto K, Nishiwaki-Nakajima N (1993) Automatic analysis of geologic structure from dip-strike data. Math Geol 27(5):819–832

Yuan Y, Shao CF, Ji X et al (2016) True 3D Surface Feature Visualization Design and Realization with MapGIS K9. In Proceedings of the 7th International Conference on Green Intelligent Transportation System and Safety, Nanjing, China, 1–4 July 2016; V olume-419, pp. 13–27

Zhang Q, Zhu HH (2018) Collaborative 3D geological modeling analysis based on multi-source data standard. Eng Geol 246:233–244

Zhang C, Wang FT, Bai QS (2021) Underground space utilization of coalmines in China: a review of underground water reservoir construction. Tunn Undergr Space Technol 107:103657

Zhang XH, Wang XW, Xu YS (2022) Groundwater environment and related potential engineering disasters of deep underground space in Shanghai. Bull Eng Geol Environ 81(5):203

Zhao YB, Hua WH, Chen GX et al (2021) New method for estimating strike and dip based on structural expansion orientation for 3D geological modeling. Front Earth Sci 15(3):676–691

Zhu LF, Zhang CJ, Li MJ et al (2012) Building 3D solid models of sedimentary stratigraphic systems from borehole data: an automatic method and case studies. Eng Geol 127:1–13

Sides EJ (1997) Geological modelling of mineral deposits for prediction in mining. Geol Rundsch 86: 342-353

Download references

Acknowledgements

This research was supported by the Chengdu Municipal Bureau of Planning and Natural Resources (Project Number. 5101012018002703). The authors would like to thank all members of the Chengdu project for their great support. Lastly, special thanks to the anonymous reviewers for their constructive comments and suggestions, which helped improve our paper.

Author information

Authors and affiliations.

School of Geography and Information Engineering, China University of Geosciences, Wuhan, 430078, Hubei, China

Hong Li & Deping Chu

School of Computer Science, China University of Geosciences, Wuhan, 430078, Hubei, China

Geological Environmental Center of Hubei Province, Wuhan, 430034, Hubei, China

Wuhan Zondy Cyber Science & Technology Co., Ltd, Wuhan, 430073, Hubei, China

Guoxi Ma & Shengyong Pan

Sichuan Geological Big Data Center, Chengdu, 610072, Sichuan, China

Chuanyang Lei

You can also search for this author in PubMed   Google Scholar

Contributions

Hong Li : Conceptualization, Methodology, Software, Validation, Formal analysis, Writing. Bo Wan : Conceptualization, Supervision, Project administration, Funding acquisition. Deping Chu : Formal analysis, Validation. Run Wang : Methodology, Supervision. Guoxi Ma : Software, Visualization. Chuanyang Lei : Data curation, Investigation. Shengyong Pan : Resources, Project administration.

Corresponding author

Correspondence to Bo Wan .

Ethics declarations

The authors declare that they have no known competing financial interests or personal relationships that could have appeared to influence the work reported in this paper.

Additional information

Publisher’s note.

Springer Nature remains neutral with regard to jurisdictional claims in published maps and institutional affiliations.

Rights and permissions

Reprints and permissions

About this article

Li, H., Wan, B., Chu, D. et al. Integrated framework for geological modeling: integration of data, knowledge, and methods. Bull Eng Geol Environ 83 , 303 (2024). https://doi.org/10.1007/s10064-024-03794-8

Download citation

Received : 01 August 2023

Accepted : 19 June 2024

Published : 08 July 2024

DOI : https://doi.org/10.1007/s10064-024-03794-8

Share this article

Anyone you share the following link with will be able to read this content:

Sorry, a shareable link is not currently available for this article.

Provided by the Springer Nature SharedIt content-sharing initiative

• Geological modeling
• Integrated framework
• Geological knowledge
• Data fusion
• Geological complexity
• Find a journal
• Publish with us
• Track your research