Internal Schema

Definition

The Internal Schema (the physical level) is the lowest level of the three-schema architecture. It describes exactly how data is physically stored: file organization, indexing structures, block/page sizes, compression, and low-level data representation. It is invisible to end users and applications — only the DBMS and DBA deal with it directly.

How It Works — Same Logical Data, Different Physical Storage

Given the conceptual entity Student(StudentID, Name, DOB), the internal schema might specify:

• Records stored as fixed-length 40-byte rows in a heap file, or
• Records indexed by StudentID using a B+-tree for fast lookup, or
• Data partitioned/sharded across multiple physical files by DeptID, with 8 KB page size and row compression enabled.

A DBA can switch from a heap file to a B+-tree-indexed structure, or move to a different storage engine entirely, purely at this level — the conceptual schema Student(StudentID, Name, DOB) does not change, and neither does any external schema built on it.

Distinguishing It From Its Siblings

• vs. Conceptual schema: the conceptual schema says what logical entities and constraints exist; the internal schema says how those entities are physically realized on disk/memory.
• Two databases can share an identical conceptual schema (2.3) yet have completely different internal schemas — for example, PostgreSQL and MySQL storing the same logical Student table with different file formats, index types, and page layouts — and users of either system would notice no logical difference.

Edge Cases and Pitfalls

• A poorly designed internal schema (e.g., no index on a frequently-queried column) causes real performance problems even though the conceptual and external schemas look perfectly correct — the bug is invisible at the logical level.
• Changing the internal schema (adding an index, changing storage engine, altering block size) should, if the architecture is respected, be completely invisible above the internal level — this is exactly the property called physical data independence (2.6).
• A common mistake is assuming the internal schema is fixed once designed; DBAs routinely re-tune internal schemas (adding indexes, repartitioning) throughout a system's life without touching the conceptual design.

Interview Takeaways

• Q: Give an example of a change that is purely at the internal schema level. Adding an index, changing file storage format, enabling compression, or changing block/page size.
• Q: Who is aware of the internal schema? Only the DBMS engine and the DBA — never the end user or, typically, application developers.