Note: In "Setting Matters Straight" posts I debunk online pronouncements that involve fundamentals which I first post on LinkedIn. The purpose is to induce practitioners to test their foundation knowledge against our debunking, where we explain what is correct and what is fallacious. For in-depth treatments check out the POSTS and our PAPERS, LINKS and BOOKS (or organize one of our on-site/online SEMINARS, which can be customized to specific needs). Questions and comments are welcome here and on LinkedIn.

“A conceptual data model usually just includes the main concepts (entities) required to store information and the relationships that exist between these entities. We don’t usually include any details about each piece of information. We can consider the conceptual stage as an initial model, without all the details required to create a database.

A logical data model is probably the most-used data model. It goes beyond the conceptual model; it includes entities, relationships, details on entities’ different attributes, and unique ways to identify entities (primary keys) and establish the relationships between them (foreign keys).

A physical data model is usually derived from a logical data model for a particular relational database management system (RDBMS), thus taking into account all technology-specific details. One big difference between logical and physical data models is that we now need to use table and column names rather than specifying entity and attribute names. This allows us to adapt to the limits and conventions of the desired database engine. We also provide the actual data types and constraints that allows us to store the desired information.”

--Vertabelo.com

------------------------------------------------------------------------------------------------------------------

SUPPORT THIS SITE
DBDebunk was maintained and kept free with the proceeds from my @AllAnalitics column. The site was discontinued in 2018. The content here is not available anywhere else, so if you deem it useful, particularly if you are a regular reader, please help upkeep it by purchasing publications, or donating. On-site seminars and consulting are available.Thank you.

LATEST POSTS

01/22 CONCEPTUAL BUSINESS RULES AND LOGICAL CONSTRAINTS (sms)

01/02 NEW "DATA MODELS" 5.2 (t&n)

12/13 NEW "DATA MODELS" 5.1 (t&n)

UPDATES

12/22 Added Finitary Relation to LINKS page

08/20 Added Logic and databases course to LINKS page.

LATEST PUBLICATIONS (order from PAPERS and BOOKS pages)
- 08/19 Logical Symmetric Access, Data Sub-language, Kinds of Relations, Database Redundancy and Consistency, paper #2 in the new UNDERSTANDING THE REAL RDM series.
- 02/18 The Key to Relational Keys: A New Understanding, a new edition of paper #4 in the PRACTICAL DATABASE FOUNDATIONS series.
- 04/17 Interpretation and Representation of Database Relations, paper #1 in the new UNDERSTANDING THE REAL RDM series.
- 10/16 THE DBDEBUNK GUIDE TO MISCONCEPTIONS ABOUT DATA FUNDAMENTALS, my latest book (reviewed by Craig Mullins, Todd Everett, Toon Koppelaars, Davide Mauri).

USING THIS SITE
- To work around Blogger limitations, the labels are mostly abbreviations or acronyms of the terms listed on the FUNDAMENTALS page. For detailed instructions on how to understand and use the labels in conjunction with that page, see the ABOUT page. The 2017 and 2016 posts, including earlier posts rewritten in 2017 were relabeled accordingly. As other older posts are rewritten, they will also be relabeled. For all other older posts use Blogger search.
- The links to my AllAnalytics columns no longer work. I re-published only the 2017 columns @dbdebunk, and within them links to sources external to AllAnalytics may or may not work.

SOCIAL MEDIA
I deleted my Facebook account. You can follow me @DBDdebunk on Twitter: will link to new posts to this site, as well as To Laugh or Cry? and What's Wrong with This Picture? posts, and my exchanges on LinkedIn.
------------------------------------------------------------------------------------------------------------------

Fallacies

A conceptual model:

• Is not a data model;

A logical model:

• Does not "go beyond conceptual model", nor is it "the most used";
• Does not "include entities, relationships and details on entities";
• Does not represent only relationships expressible via PKs and FKs;

A physical model:

• Does not include tables and columns, which do not differentiate it from a logical model (which does not include entities either);
• Data types are a programming, not database concept (and not physical);
• Constraints are logical, not physical.

Fundamentals

A data model (as introduced by Codd) is an abstract theory of data used to formalize conceptual models of reality as logical models for database representation and manipulation that can be implemented physically in hardware by DBMS software.

Note:: Other than RDM I do not know  of other data models fully specified normally that satisfy the definition. Among other advantages, RDM supports soundness (by-design semantic consistency and system-guaranteed logical validity) and physical independence (insulation of queries and applications from physical implementation details and changes thereof). Being abstract,  a data model is not enterprise-specific..

A conceptual model is a collection of business rules that organizes reality of interest as a multigroup -- a collection of related groups of entities. Entities are group members by virtue of sharing properties and intra-group relationships, which are properties of the group. Groups are multigroup members by virtue of sharing inter-group relationships, which are the properties of the multigroup.

Conceptual models:

• Are enterprise-specific..
• Are models of reality, not of data.
• Include entities, properties, relationships, entity groups and multigroup;

- an entity is a recurring collection of observable properties;
- there are relationships among entity properties, entities within a group and groups within a multigroup.

A logical model is a formal symbolic database representation of a conceptual model. It includes domains (represent properties); relations (represent entity groups):, tuples (represent entities); attributes (represent properties of entities of a type); constraints (represent intra- and inter-group relationships).

Logical models:

• Are data model- and enterprise-specific;
• Represent symbolically (which is not "going beyond") conceptual models;
• Do not include, but represent not just entities and details on entities" (by tuples and attributes), but also groups by relations and relationships and:

- intra-group, including uniqueness by PK constraints;
- inter-group, including referential by FK constraints.

A physical model is an implementation by DBMS software of a logical model in hardware.

Physical models:

• Are enterprise- and DBMS-specific;
• Include storage and access methods. (files, indexes and so on), not tables and attributes;
• Data types are a programming, not database feature and constraints are a logical feature.

Setting Matters Straight

We revise as follows:

A conceptual model is an informal model of reality that includes entities, properties, entity groups and relationships among properties, entities and groups that constitute a multigroup to be recorded in a database, without any implementation details.

A logical model is a formal symbolic representation of a conceptual model that is computerizable for database representation and manipulation that includes domains, relations (attributes and tuples) and constraints, including, but not only, uniqueness (PK) and referential (FK).

A physical data model is an implementation of a logical model by a specific DBMS software in hardware that includes storage and access methods. The difference from logical model are the storage and access methods implementation details such as files, indexes, hashes and so on, instead of logical domains, relations and constraints.