Three Level Architecture and Data Abstraction

Concept

The three level architecture, also called the ANSI-SPARC architecture, describes a database at three separate levels of detail. Each level hides the complexity of the level below it.

The point of the design is stated in one sentence: a user should be able to see the data without knowing how it is stored, and the storage should be able to change without disturbing the user.

The three levels

   EXTERNAL LEVEL      view 1        view 2        view 3
   (view level)      student sees   teacher sees  accounts sees
                     own marks      class marks   fee status
                                       |            /
                                       |           /
                            +-----------+----------+
                                        |
   CONCEPTUAL LEVEL             the whole database:
   (logical level)          all tables, all columns, all
                            relationships and constraints
                                        |
                                        |
   INTERNAL LEVEL           files, pages, records, indexes,
   (physical level)         compression, placement on disk
                                        |
                                   +----v----+
                                   |  disk   |
                                   +---------+

Data abstraction

Data abstraction is the deliberate hiding of detail at each level. It has the same three layers, and exam questions use both names for the same idea.

• Physical level abstraction — the lowest. Describes how data is actually stored: block layout, record format, index structure.
• Logical level abstraction — describes what data is stored and what relationships exist, without any storage detail.
• View level abstraction — the highest. Describes only part of the database, tailored to one group, hiding the rest for simplicity and for security.

Mappings

Two mappings connect the levels, and they are what actually deliver independence.

When storage changes, only the conceptual/internal mapping is rewritten. Nothing above it is touched. That single sentence is the whole benefit of the architecture.

Example

-- Illustration only: SQL is used here to make the three
-- levels concrete, not because this note teaches SQL.

-- CONCEPTUAL level: the real, complete structure
employees ( emp_id, name, dept_code, salary, bank_account, joined_on )

-- EXTERNAL level: what the staff directory application sees.
-- Salary and bank account simply do not exist in this view.
CREATE VIEW staff_directory AS
SELECT emp_id, name, dept_code
FROM   employees;

-- INTERNAL level: not written by anyone. Decided by the DBMS
-- and the DBA. For example: heap file, B+ tree index on
-- emp_id, secondary index on dept_code, pages of 16 KB.

The directory application cannot leak a salary, because at its level the column does not exist. That is view level abstraction being used as a security control.

Common mistakes

• Saying there are many conceptual schemas. There is exactly one conceptual and one internal schema, and many external schemas.
• Placing indexes at the conceptual level. An index is a storage decision. It belongs to the internal level.
• Confusing the external level with the user interface. The external level is a logical description of visible data, not a screen design.
• Forgetting the mappings. Questions on data independence are really questions about which mapping absorbs the change.

Exam and interview questions

1. Draw and explain the three level ANSI-SPARC architecture.
2. How many schemas exist at each level, and why?
3. Name the two mappings and state what each translates.
4. Which level would an index belong to, and why?
5. Explain how the external level contributes to security.

Practice

1. For a hospital database, write one conceptual table and three different external views for a doctor, a pharmacist and an accountant.
2. List four decisions that belong to the internal level.
3. Explain in three sentences what would have to change if a table moved to a different disk.

Conclusion

External, conceptual and internal levels separate what users see, what exists, and how it is stored. The two mappings between them absorb change, which is exactly what makes data independence possible.