What is the difference between conceptual, logical and physical modeling?

Question

In a relational database, what is the difference between conceptual, logical and physical modeling?

Could you give an example?

Answer 1

Conceptual Modeling

The goal here is to create a model in a graphic form, this being called Entity and Relationship Diagram (ERI), which will identify all entities and relationships in a global way.
Here any specific details of the BD model are avoided.
Its main purpose is to capture the information requirements and business rules from the business point of view.
In the development of solutions is the first model to be developed.
At the requirement lifting stage.
Usually done by the Business Data Manager or other professional accompanied by their supervision/guidance.
It is hardware or software independent, ie it does not depend on any type of database server [Sql Server, My Sql, Oracle, Postgresql, etc].
Therefore, any change in software or hardware will have no effect on the conceptual level.

Logical modeling

Logical modeling is needed to compile business requirements and represent requirements as a model.
It is primarily associated with the collection of business needs, not with database design.
The information that needs to be collected is about organizational units, business entities and business processes.
Describes how data will be stored in the database and also your relationships.
This model adopts some technology, can be: relational, object-oriented, column-oriented, among others.

Logic models basically determine whether all business requirements have been met.
It is reviewed by developers, management and ultimately end-users to see if it is necessary to collect more information before physical modeling begins.

The logical ERI also models the information collected from business requirements.
It is more complex than the conceptual model in which column types are defined.
Note that column type configuration is optional, and if you do this you should do this to assist in business analysis.
It has nothing to do with database creation yet.

Physical modeling

Physical modeling deals with the design of the actual database based on the requirements gathered during the logical modeling of the database.
All information collected is converted into relational models and business models.
During physical modeling, objects are defined at a level called the schema level.
A schema is considered a group of objects that are related to each other in a database.
Tables and columns are made according to the information provided during the logical modeling.
Primary keys, unique keys and foreign keys are set to provide restrictions.
Indices are defined.

Physical modeling depends on the software that is already being used in the organization.
It is software specific. [Sql Server, Oracle, Mysql, Postgresql, etc]

Summary:

1. Among the components of a conceptual model, we can relate:

   • Entities;
   • Attributes;
   • Relationships;

2. Logical modeling is primarily for collecting information about business needs and does not involve designing a database.
   While physical modeling is primarily required for actual database design.

3. Logical modeling does not include indices and constraints.
   The logical model for an application can be used in multiple databases [Sql Server, Mysql, Oracle, Postgresql, etc].
   In logic modeling there may be primary and foreign keys, while physical modeling is software and hardware specific and has indexes and constraints.

4. Logical modeling includes; ERD, business process diagrams and user feedback documentation; whereas physical modeling includes; server model diagram, database design documentation and user feedback documentation.

---

Translated from: http://www.differencebetween.net/technology/software-technology/difference-between-logical-and-physical-database-model/

---

Comparative table of model characteristics

In this table you can see the difference between each model:

---

Detailed Example of a Conceptual Model

Example of an ERI - Entity and Relationship Diagram

Of: http://spaceprogrammer.com/bd/introducao-ao-modelo-de-dados-e-seus-niveis-de-abstracao/

---

Comparison of several ways to represent a Conceptual Model

---

Model example of a system with 3 models

conceptual model

logical model

physical model

Images of: http://www.fabiodomingues.com.br/modelagem-de-banco-de-dados/

---

Another way to model a system with the 3 models

Translated from: https://www.visual-paradigm.com/support/documents/vpuserguide/3563/3564/85378_conceptual,l. html

DER - conceptual model

ERI - logical model

DER - physical model

---

One more example comparing the 3 models

---

Example of a Physical Modeling Mode

A physical model can consist of SQL code for creating objects in the database

CREATE TABLE turma (
  idturma INTEGER(4) NOT NULL AUTO_INCREMENT,
  capacidade INTEGER(2) NOT NULL,
  idProfessor INTEGER(4) NOT NULL,
  PRIMARY KEY (idturma),
  FOREIGN KEY (idProfessor) REFERENCES professor(idProfessor),
  UNIQUE KEY idturma (idturma)
)

CREATE TABLE professor (
  idProfessor INTEGER(4) NOT NULL AUTO_INCREMENT,
  telefone INTEGER(10) NOT NULL,
  nome CHAR(80) COLLATE NOT NULL DEFAULT '',
  PRIMARY KEY (idProfessor),
  FOREIGN KEY(idTurma) REFERENCES turma(idturma),
  UNIQUE KEY idProfessor (idProfessor)
)

---

Links [origins]:

http://spaceprogrammer.com/bd/introducao-ao-modelo-de-dados-e-seus-niveis-de-abstracao/

http://www.blrdata.com.br/single-post/2016/03/19/Modelagem-Conceitual-de-Dados-Conhe%C3%A7a-os-principais-conceitos-e-pr%C3%A1ticas

https://www.luis.blog.br/modelagem-conceitual-modelo-conceitual-de-dados

https://en.wikipedia.org/wiki/Data_modeling

http://www.diegomacedo.com.br/modelagem-conceitual-logica-e-fisica-de-dados