External Schema

Definition

The External Schema (also called the view level) is the level of the three-schema architecture closest to individual users and applications. It defines a subset (or a reshaped, derived version) of the conceptual schema tailored to what a specific user group or program actually needs to see. It is typically implemented using database views.

How It Works — Multiple Views Over One Design

Take a conceptual schema Student(StudentID, Name, DOB, Marks, FeeBalance).

• The Result Portal application is given an external schema exposing only (StudentID, Name, Marks) — it never sees fee information.
• The Accounts Portal is given a different external schema exposing (StudentID, Name, FeeBalance) — it never sees marks.
• A Class Teacher Portal might get an external schema that joins Student with Enrollment to expose (StudentID, Name, CourseName, Marks) — combining more than one conceptual entity into a single simplified view.

All three external schemas sit over the same conceptual schema at the same time. Each restricts columns, restricts rows (e.g., only students in the teacher's own department), or reshapes data (joins, renamed fields, computed fields) — without ever letting that user group see the full conceptual design.

Edge Cases and Pitfalls

• If a column an external schema depends on (say Marks) is later dropped or renamed in the conceptual schema, that external view breaks and must be updated — this is precisely a failure of logical data independence, not a normal feature of it.
• An external schema can be built by joining multiple conceptual entities (like Student + Enrollment above); it is not limited to a single table.
• Two external schemas can expose the same underlying data with different row-level restrictions (row-level security), which is easy to confuse with column-level restriction — they solve different problems.

Interview Takeaways

• Q: How is an external schema different from a plain database view? Conceptually they are the same idea — a view is the concrete SQL mechanism typically used to implement the abstract "external schema" concept from the ANSI-SPARC model.
• Q: Can two external schemas overlap? Yes — many external schemas can be defined over one conceptual schema simultaneously, and their column/row sets can overlap freely.