What is the difference between logical schema and physical schema? [closed]

Question

While going through Database architecture I came across Schema's . I am very much confused with:

Logical schema

Physical schema

How these two schema exist in the Database?

How can these be manipulated according to need?

How Logical schema and physical schema are related?

Answer 1

A logical schema is a conceptual model of the data. In relational databases, it's often platform-agnostic - i.e. the logical schema can, in principle, be implemented on any SQL database. The logical schema is primarily concerned with understanding the business entities, their attributes and their relationships.

The logical schema is often captured as an entity-relationship diagram (ERD), using tools like ErWin or ERStudio.

The physical schema converts the logical schema into an implementation that works on a particular database platform. Sometimes, this is a largely mechanical exercise, applying the right datatypes to the attributes, but it can also involve performance optimizations such as denormalization, platform-specific features such as triggers, and sizing/performance decisions like physical disk distribution.

Many ERD tools can "automagically" convert a logical design into a physical schema.

The physical schema is converted into an actual database using DDL SQL statements.

Answer 2

Physical schema represents the actual connection to the data source or data target. Logical schema represents the logical name associated to that source or target. One logical schema can be associated with multiple physical schema along with context, i.e. one logical schema is associated with different physical schema using different context.

It can be understood with following example: You have 3 environments: Dev, QA, Prod, each having different database servers as DB1, DB2, DB3, respectively. Similarly we have 3 context corresponding to Dev, QA and Prod. You create logical schema with name DB_source Now you associate physical DB servers to logical schema (DB_source) for each context: DEV: DB1 QA: DB2 PROD: DB3 Now when u develop ODI interfaces, you use one context DEV which associates DB_source to DB1. While mentioning context for execution, keep it as "Execution". This means, whatever context you choose during execution, corresponding physical DBs will be used. Thus if you change the execution context, corresponding physical schema will be used during execution.

Reference: Physical Schema and logical schema