Database and Information Systems Development: Data Models — an introduction to 3 essential models
By Ron McFarland, PhD
Introduction
In this series of database and systems development articles, I’ll present several salient topics that can support good development processes. When developing data bases or when migrating data from one database platform to another, it is essential to map out the data in a logical stepwise manner to fully understand the organization’s business rules. The use of three successively applied models, at a minimum, can assist the developer in understanding the semantics (e.g., the meaning) that is necessary between business rules and the data structures that support the user’s requirements.
The primary driver for the understanding of data cross-referenced to business requirements is typically a spreadsheet that contains the fundamental mapping of rows and columns of information that cross-references business data to the data structures that support the user’s requirements. In a prior MEDIUM article, I discussed the use of a database dictionary, which is often viewed as a data source in the migration of data from one system to another target system during the migration process (link to the Medium article about data dictionaries can be found here: https://highervista.medium.com/database-and-information-systems-development-data-dictionaries-5ecae154d861 ).
Whether the project is a new effort to develop a database or a migration project that moves data from a source database to a new target database, the understanding of how the data is used in the target (new system or migrated system) is essential. The data source (the source spreadsheet for a new system or the source data dictionary from a prior system) provides an understanding of the bidirectional nature of relationships, in terms of the parent/child relationships that exist between data. The use of three fundamental models, in succession, help the design team understand the proper relationships so that the user requirements for the database can be fully realized. The three successive models are the (a) conceptual model, (b) logical model, and © physical model.
Conceptual Model
For a new system, the conceptual model (aka business conceptual model) is an important launching point to fully understand the business requirements. For projects that require a migration from a prior development effort to a newer technology, an existing conceptual model will assist the developer to comprehend the prior user requirements of the old data source. Whether a new development project or a migration to a new technology, the conceptual model provides the semantic understanding of the business logic and rules, expressed in the conceptual model as relationships. The diagram below is a conceptual model based on railroad travel. The train is associated to a locomotive in a M:N (many to many) relationship, while the train is additionally associated to many train cars in a one (train) to many (train car) relationship. This conceptual model expresses the business requirements and rules that embrace (a) that a train is associated with a (or many) locomotives and (b) a train has one or many train cars, as an example of two business rules. See Figure 1 below:
Figure 1: Example Conceptual Model (DataManagementU, 2021)
Conceptual models represent data assembled from business requirements, identify key business objects (such as locomotive or train, in the example conceptual model), and the relationship between business objects, which will enstatite the business rules. The relationship connections displayed in the conceptual model are, again, characterized around the business needs. In the conceptually model, logic is grouped around major elements or objects derived from the requirement analysis and are placed in a modeling tool that supports the use of basic entities that represent the intention of the requirements analysis. Entities and relationships are further developed around the business needs. The development of a conceptual model requires user input (or the input of a business analyst) so that the database developer can fully understand and implement the technical aspects of the business requirement for the new database or the migrated database effort.
In general, the conceptual model can be viewed as a map of business concepts and their relationships for a database. The conceptual model provides a business world perspective of how the user uses the information. In a conceptual model, the non-critical technical details are suppressed to allow an emphasis on the business rules and user objects (e.g., entities) relevant to the business perspective of the database.
Logical Model
As noted in the prior paragraph, a fully attributed population of data is not necessary in the conceptual model. The logical model adds further understand and structure to the conceptual model. The logical model supports the continued development of the database design.
Further, the logical model can be defined as graphical representation of organization business requirements and consists of a deeper cut of the data population that will be represented in the source database.
At this stage of the database development project, the following list of tasks are completed including:
a. Conceptual Model Mapping to a Logical Model: The mapping of the conceptual model to the more detailed logical model components
b. Transition of Objects in the Conceptual Model to more detail in the Logical Model: A transition of the conceptual model objects/entities to a set of related objects (such as relations or tables, columns, relationships, and constraints). This often includes the following sub-set tasks:
1. Identification and mapping of strong entities in the logical model: A strong entity is one whose existence is not reliant on any other entity in the model or schema. It is essential that the data modeler works with the user community to identify these key entities in the model.
2. Identification and mapping of supertype/subtype relationships in the logical model: As a reminder, the supertype is a generic entity type that contains one or more subtypes. The supertype/subtype entity descriptors provide meaningful organization in the logical model.
3. Identification and mapping of weak entities: A weak entity is an entity that cannot be uniquely identified by its attributes alone and typically contains a primary key and foreign key to relate the entity value to another entity in the data model.
4. Identification and mapping of binary relationships: Within the logical model, the binary bi-directional relationships are mapped and support the understanding of the functionality of the data model.
c. Transition Logical Model into Third-Normal-Form (3NF): At this stage, a designer should begin working to reach 3NF. While there are six normal forms ranging from 1NF, 2NF, 3NF, EKNF (Elementary Key Normal Form), BCNF (Boyce-Codd Normal Form), 4NF, and ETNF (Essential Tuple Normal Form), it is generally accepted that 3NF is considered adequate for relational design, since tables are free of insertion, update, and deletion animalities. Additional normal forms beyond 3NF can introduce additional database overhead (which may or may not be desirable).
d. Review of the Logical Model for Integrity Constraints: At this step in the process, designers examine the logical model to ensure that integrity constraints are upheld. Integrity constraints are a set of rules that are applied to ensure the overall validity and consistency (e.g., quality) of the data.
During the iterative process of converting conceptual model to logical model, it’s common that new attributes and tables will be added to the logical model. A logical model represents business data assembled from business requirements in a manner that can be ultimately implemented in a database construct. From my experience, the logical model is the aspect of the project that takes the longest, as users need to be consulted, agreement on the model must be confirmed, as the logical model is ultimately transitioned to the physical model (or physical implementation) for the project.
Physical Model
The physical model is database software specific. In projects that I’ve worked on, the Physical Model (or physical implementation) is typically handled by a Database Administrator who has more intimate knowledge of the organization’s operating environment, physical database environment (including the database tools), the allocation and placement of storage in the organization’s production environment relevant to the database being implemented, and the processes/procedures to maintain the database within the organization’s operating environment. The physical model is described by a specific database management tool. As an example, tools such as Oracle, Sybase, or Informix uniquely have their unique physical limitations and constraints that must be described in the context of how the organization has implemented the specific database environment. During the transition between the logical model to the physical model (or physical implementation), the DBA may subject the logical model to additional modifications during the transitioning to the physical environment. Ultimately, the physical model portrays the blueprint of the database as it is physically implemented in the database.
Summary
In summary, a conceptual model is to establish the entities, attributes, and relationships. The logical model is the structure of the data elements and set connections between them, and the Physical model explains the database specific implementation of the data model. The table below highlights the distinction between the conceptual, logical, and physical models.
References
DataManagementU. (2021, April 7). Data models ensure successful migrations. Data Models Ensure Successful Migrations. Retrieved December 10, 2021, from https://www.ewsolutions.com/data-models-ensure-successful-migrations/.
Data Modeling: Conceptual vs. Logical vs Physical Data Model. (n.d.). Online.visual-Paradigm.com. https://online.visual-paradigm.com/knowledge/visual-modeling/conceptual-vs-logical-vs-physical-data-model/
Eng, N., & Watt, A. (2014, October 24). Chapter 5 data modelling. Database Design 2nd Edition. https://opentextbc.ca/dbdesign01/chapter/chapter-5-data-modelling/.
Hauck, T. (2020, August 23). Three Schema Architecture. RelationalDBDesign. https://www.relationaldbdesign.com/database-design/module3/three-schema-architecture.php.
Visual Paramdigm. (n.d.). Conceptual, logical and physical data model. Visual Paradigm. https://www.visual-paradigm.com/support/documents/vpuserguide/3563/3564/85378_conceptual,l.html.
About the Author
Ron McFarland, PhD, CISSP is a Senior Cybersecurity Consultant at CMTC (California Manufacturing Technology Consulting) in Torrance, CA. He received his doctorate from NSU’s School of Engineering and Computer Science, MSc in Computer Science from Arizona State University, and a Post-Doc graduate research program in Cyber Security Technologies from the University of Maryland. He taught Cisco CCNA (Cisco Certified Network Associate), CCNP (Cisco Certified Network Professional), CCDA (Design), CCNA-Security and other Cisco courses and was honored with the Cisco Academy Instructor (CAI) Excellence Award in 2010, 2011, and 2012 for excellence in teaching. He also holds multiple security certifications including the prestigious Certified Information Systems Security Professional (CISSP) certification and several Cisco certifications.
CONTACT Dr. Ron McFarland, MSc, PhD, CDNA, CISSP
CMTC Email: rmcfarland@cmtc.com
Email: highervista@gmail.com
LinkedIn: https://www.linkedin.com/in/highervista/
Website: https://www.highervista.com
YouTube Channel: https://www.youtube.com/channel/UCJ57_1OgZ5H1nMVdGElcvrw
