Logical Data Model Explained with Examples

On approval of the conceptual model by a company's functional team, a logical data model is created. This is when the physical design is created.

Conceptual, physical, and logical are the three data models for data warehousing. A logical data model, as the name suggests, represents the logical relationships between the data of any organization. The data is placed in an entity.

Each entity has various attributes, and each attribute is housed in an individual field. This model consists of primary and secondary keys, which indicate the relationships between the attributes.

The data of any organization should be such that the transactions on it are consistent. Thus, data models are made use of to avoid any errors. The function of a primary key involves preventing data redundancy.

In a logical data model, the entities and attributes used have business names assigned to them. Also, the way data gets stored in the database is not directly related to its placement in a logical data model.

► Representation of the data of an organization in terms of entities and attributes.
► Depicts relationships between attributes and entities.
► Avoiding data redundancy by defining a unique identifier or a primary key for each entity.
► Depicts the dependencies between entities using foreign key.

Entity - It can be anything such as a person, place, or an object. A rectangular box represents an entity. Each entity has a unique ID, called its primary ID. This uniquely identifies the entity.

Attribute - It is the characteristics of an entity. For an entity 'user', its name, address, contact number, etc., are its attributes. They are represented within the entity rectangle.

Relationship - It defines the interaction between the entities. In a logical data model, relationships are of four types: one-to-one, one-to-many, and many-to-many. They are depicted by using a solid line. Arrows are included or not, depending on the type of the relationship.

► Identify and list the data that needs to be modeled.
► Determine the primary keys for all the entities.
► Identify the attributes and the grouping for each of them.
► Determine the many-to-many relationships between entities.
► Normalize the existing data.

► This structure represents a generic example of a logical model used for organizing data.

► All the entities in this diagram consist of attributes, one of which named 'ID' has been displayed. The relationship is one of the types that has been mentioned.

► The entities are connected to each other through their primary keys. The primary key of the entity A(A_id) becomes the foreign key of 'B'. Similarly, the primary key of entity C(C_id) becomes the foreign key of the table 'B'. Thus, the foreign keys of table B are A_id and C_id, which are called the attributes of table B.

This logical data model diagram consists of four entities: User, Book, Group, and Page. Each of these entities have unique identifiers, called User ID, Group ID, Book ID, and Page ID, respectively.

► One user can be associated with more than one group, whereas, a group can have many users. This is a many-to-many relationship between Group-User, and is represented by arrows on both sides of the solid line, representing their relationship.

► One user can have many books. One book can have one user. Thus, the relationship between User-Book is one-to-many.

► Each book will have multiple pages. One page will belong to only a single book. Thus, the relationship between Book-Pages is one-to-many.

Since the tables 'User' and 'Book' are related, deleting an entry from the 'User' table will lead to deletion of the corresponding entries in the 'Book' table. If a user named 'John' has three books, namely 'A', 'B', and 'C', deleting 'John' from the 'User' table will lead to deletion of 'A', 'B', and 'C' from the 'Book' table.

► In a logical data model, all the attributes are specified within an entity.
► It uses primary and foreign keys in order to define the relationships between various entities.
► This model provides the most detailed explanation/analysis of the organization's data.
► It is database-independent, as well as platform-independent.

Most organizations construct such prototypes to build a logical representation of their data. It is after this model is designed that the organization can proceed to create the physical data model. A logical data model can be created using various existing tools, few of which are MySQL Workbench, Oracle SQL Developer Data Modeler, and Enterprise Architect.