5

I'm doing a work about database, and now I need to show three different images, one image with the conceptual model, other with logical model and other with physical model of a database.

But Im here with some difficults to understand which image represents each model.

I'm looking for reliable information about this, but I find different answers and I'm a bit confused.

So I came here to see if you can help me.

I have below my three images, do you think I have the correct title for each image?

Conceptual model:

In conceptual model, I think that I neeed to put my tables with atributes but without relationships.

enter image description here

Logical Model:

In logical model, I think I need to put my tables with atributes, but now with my relationships.

enter image description here

Physical Model:

In physical model, I think I need to put my tables with atributes, but now with my relationships and also with foreign keys

enter image description here

UserX
  • 1,295
  • 7
  • 25
  • 39

3 Answers3

7

The conceptual/logical/physical layers have changed somewhat over the years, and also vary according to different schools of thought. The way I learned it, back in the 1980s was this:

The conceptual model summarizes the semantics of the data with reference to the subject matter. It is not bound to a relational implementation. The implementation could be in some sort of prerelational database, or even in classical files of records. You have entities, relationships, attributes, and domains. You also have business rules. That's about it. Like your summary, it's primarily for communication with users and other stakeholders. The idea is to pin down the requirements during the analysis phase.

The logical model is a preliminary design. It's bound to the relational model, but not to a specific DBMS. You have relations, tuples, attributes, and constraints. Relationships are implemented as foreign keys, sometimes requiring junction relations. I tended to use the terminology of tables, rows, and columns, instead of relations, tuples, and attributes, but that's mostly nomenclature. Normalization is relevant here.

The physical model is a detailed design. It's DBMS specific, and takes into account data volume, expected traffic, and performance. Denormalization is relevant here. This leads directly to a creation script.

This is by no means the majority view, let alone a general consensus. You need to understand your audience to see if this framewok works.

Walter Mitty
  • 18,205
  • 2
  • 28
  • 58
6

A Conceptual Model (CM) is an informal representation of the business represented in a manner that is understood by users. It will consist of classes of entities with attributes and the business rules regarding these. It is often presented as Entity-Relationship Diagrams.

A Logical Model (LM) formalizes the CM into data structures and integrity constraints. it should include all the data structures and integrity constraints for the data (this is all constraints, not just that subset of constraints that are easily defined in most available database management systems). It is database management system agnostic.

The LM may be presented as a Relational Data Model (RDM). In which case all the data structures and integrity constraints will be formally represented only using mathematical relations.

A Physical Model (PM) is a representation of the LM on specific hardware and database management system. It may consist of information such as storage sizing and placement; access methods such as indexing; and distribution such as clustering or partitioning.

Using these definitions I would say that all you diagrams are versions of Conceptual Models; as they do not include all the integrity constraints for the data being managed and do not include any information regarding an implementation on specific hardware or database management system.

DrabJay
  • 2,989
  • 2
  • 13
  • 12
  • I don't agree that the CM is informal. ER modeling, as developed in 1975, is a fairly formal model. It just isn't a design model. – Walter Mitty Jul 30 '14 at 12:14
  • 2
    @WalterMitty by informal I mean the CM is designed to be understood semantically by users and is not a mathematically/logically precise representation. Business rules may be expressed in natural language, which can be prone to interpretation. The LM is designed to be understood algorithmically by the database management system and is mathematically/logically precise. Integrity constraints are expressed in logical sentences. – DrabJay Jul 30 '14 at 12:45
  • Thanks for your answer. From what I've studied, I agree with all your definitions. This site have 3 images for each model. (http://www.1keydata.com/datawarehousing/data-modeling-levels.html), In the conceptual model is based only on the entities/tables and the relations between them, the logical model already has tables, relations between tables and attributes of each table. – UserX Jul 30 '14 at 14:14
  • The physical model already has tables, relations, attributes but also the type and size of the fields, which also corresponds to what you said in your answer. However, I am a bit confused because in wikipedia I saw that the physical model already matches the code that will be interpreted by MysQL (for example). That would be something like this: CREATE TABLE products ( Product_no Text (1) PRIMARY KEY, MODEL Text (1) DESCRIPTION Text (1) Text COLOR (1) CATEGORY Text (1) QUANT_PROD Text (1)........... – UserX Jul 30 '14 at 14:15
  • The PM may be presented as a series of Data Definition Language statements. If these are then used to actually create the database then the PM and actual database are in step; this may well be useful when it comes to systems management. However, the PM may also be presented in other formats. For instance some database management systems allow XML to be submitted to manage the database structures. This XML would be a presentation of the PM. – DrabJay Jul 30 '14 at 15:23
  • With regards to the site you highlighted, based on these definitions I would say all these are versions of Conceptual Models; as they do not appear to include **all** the integrity constraints and do not include any specific information regarding implementation on a specific database management system. – DrabJay Jul 30 '14 at 15:37
2

Is it a homework or what? The question seems so artificial...

The 3rd one is Physical because the data types are closer to actual DBMS data types.

Between the 1st, and 2nd ones... I'm stuck. The only difference is the crow-feet relationship. If there's a progression between the three images, I'd guess this would make the 2nd one the Conceptual.

But it is difficult because, with PowerDesigner, you could still represent the relationship with crow-feet in the Logical model. But anyway, there should be evidence of the migration of the "foreign key" attribute id_cat in the News entity, which is missing here. Nope. I was reading my example diagrams too fast, there's no migration in Logical model.

So, just by elimination, I'd make the 1st one the Logical.

pascal
  • 3,287
  • 1
  • 17
  • 35