Difference between conceptual, logical and physical design of a database

The design of a database is generally divided into three phases:

1. Conceptual design. It translates real problems into a conceptual framework that is easy to understand, without dealing with how the database will be built. The Entity-Relationship (ER) model is a solitude used during this type of design to define the static aspects of the system, ie the data. It is a diagrammatic model that describes the entities to be modeled, the relationships between them and the cardinality of the relationships.
2. Logical design. The objective of the logical design phase is to reach, starting from the conceptual scheme, a logical scheme that represents it faithfully, “efficiently” and independent of the particular DBMS (Data Base Management System) adopted. To this end, this design phase can be divided into 2 steps:
   1. Restructuring of the Entity-Relationship scheme: it is a phase independent of the logical model and is based on optimization criteria of the scheme;
   2. Translation towards the logical model: it refers to a specific logical model, in our case the relational model.
3. Physical design. It translates the logical schema in terms of tables and relationships that will constitute the actual physical structure of the database. This last phase will be taken into consideration in this thesis in a marginal way.

Conceptual design

Conceptually designing a database means identifying the objects (or entities) that constitute it and the relationships (operations or associations) between one object and another. This is the highest level of database design, the one closest to humans and farthest from the machine (hardware). It must therefore be implemented with tools and languages ​​that can be understood by everyone, not just specialists, and independent of the database system. Finally, the conceptual scheme shows the entities of our system with the relative dependencies or associations between the corresponding entities.

Logical design

As for the object model, also for the logical or relational model it is possible to define a graphic formalism in which only the relation schemes and their associations, or more precisely the foreign keys, are presented. It is realized through a transformation of the classes and their associations.

Physical design

The last phase of database design is physical design. Before starting the project it is necessary to choose a DBMS (Data Base Management System) on which the data model of the logic scheme will be implemented. Physical design consists of the following activities:

1. choice of storage structures of tables and auxiliary data access structures (indexes). These are used to make the access to data contained in frequently used tables more efficient. The storage and access facilities are evaluated among those made available by the selected DBMS;
2. translation of the logical schema of the data into a physical data schema containing the definitions of the tables, the relative integrity constraints and the views expressed in SQL.

At the end of this phase, the Database was completely designed and then moved on to its realization, that is to the physical construction of the tables and the implementation of the applications that will exploit the data base. Applications are written in high-level programming languages ​​and can reuse the previously written SQL code. The implementation phase is often followed by an optimization phase, in which the performance of the DBMS on the specific data base is evaluated; on the other hand, changes in the parameters decided during physical design are possible (for example, the addition of a new index).