Logical and Physical Data Independence

Concept

Data independence is the capacity to change the schema at one level of the three level architecture without being forced to change the schema at the level above it.

It comes in two kinds, named after the level being changed.

Why logical independence is harder

This is the question that separates a memorised answer from an understood one.

Applications are written against the logical structure. They name tables and columns. So a logical change has a real chance of touching something an application actually mentions. A physical change touches nothing an application ever names — no program says use the B+ tree or read block 4,096.

PHYSICAL CHANGE                    LOGICAL CHANGE

add an index on dept_code          split employees into
                                   employees + salaries

application code:                  application code:
  unchanged                          SELECT salary FROM employees
                                     now refers to a column
queries:                             that has moved
  unchanged, just faster
                                   a view named employees can
nothing above the internal         restore the old shape, and
level mentions an index,           THAT is how logical
so nothing above it breaks         independence is achieved

How each is achieved in practice

• Physical independence comes almost free from the architecture. The DBMS chooses access paths at query time, so changing them changes only performance.
• Logical independence has to be worked for. The usual technique is to let applications read views rather than base tables. When a table is split or renamed, the view is rewritten to present the old shape, and the applications never notice.

Example

-- Illustration only.

-- Original conceptual schema
employees ( emp_id, name, dept_code, salary )

-- Logical change: salary moves to its own table
employees ( emp_id, name, dept_code )
salaries  ( emp_id, amount, effective_from )

-- Logical independence restored with a view, so every
-- application that referred to employees.salary keeps working
CREATE VIEW employees_full AS
SELECT e.emp_id, e.name, e.dept_code, s.amount AS salary
FROM   employees e
JOIN   salaries  s ON s.emp_id = e.emp_id;

Important terminology

Common mistakes

• Swapping the two definitions. Remember it by the level being changed, not the level being protected. Physical independence means the physical level changed.
• Claiming logical independence is easier. It is harder, and saying so with the reason earns the mark.
• Thinking adding an index is a logical change. It is purely physical.
• Believing independence is absolute. Dropping a column an application selects will break it. Independence reduces coupling; it does not abolish it.

Exam and interview questions

1. Define physical and logical data independence with two examples each.
2. Which is harder to achieve, and why?
3. Which mapping absorbs each kind of change?
4. How do views help achieve logical data independence?
5. Give one change that no amount of data independence can hide from an application.

Practice

1. Classify each as physical or logical: adding an index, adding a column, changing the page size, renaming a table, moving a file to another disk, merging two tables.
2. A table is split in two. Write the view that preserves the original shape for existing applications.
3. Explain in three sentences why a DBMS can change its access path without telling anyone.

Conclusion

Physical data independence protects the conceptual schema from storage changes and is close to free. Logical data independence protects applications from schema changes, is harder, and is usually bought with views.