Data Abstraction and Data Independence

DSA, Database System & Operating System — Data Models, Normalization, and SQL, NEC licence examination syllabus (Nepal Engineering Council).

Data Abstraction and Data Independence

Three levels of describing the same data — so that changing storage never breaks an application.

🌍 Where this lives: data independence is why your application survives the database team adding an index at 2 a.m. You wrote SELECT name FROM students WHERE roll = 101; they changed the physical storage from a heap to a B-tree, moved the table to a different disk, and partitioned it by year. Your query is untouched. Before this idea, programs contained the record layout in their source code, so adding a field to a file meant recompiling every program that read it — which is exactly the crisis that killed pre-relational systems and is described in Codd's 1970 paper. Search "Codd A Relational Model of Data for Large Shared Data Banks"; it is 11 pages and it created the industry.

The three-level ANSI/SPARC architecture

┌──────────────────────────────────────┐ │ EXTERNAL LEVEL (view level) │ │ many views, one per user group │ │ "what THIS user is allowed to see" │ └──────────────────────────────────────┘ ↕ logical mapping ┌──────────────────────────────────────┐ │ CONCEPTUAL LEVEL (logical level) │ │ ONE community schema │ │ entities, attributes, relationships,│ │ constraints — no storage detail │ └──────────────────────────────────────┘ ↕ physical mapping ┌──────────────────────────────────────┐ │ INTERNAL LEVEL (physical level) │ │ files, pages, indexes, compression, │ │ record layout, disk placement │ └──────────────────────────────────────┘ EXTERNAL — what a user sees. A payroll clerk sees name and salary; an admissions officer sees name and marks but not salary. Both are views of ONE conceptual schema. CONCEPTUAL — the whole database described once, in terms of what the data MEANS. Which entities exist, which attributes they have, which relationships and constraints hold. INTERNAL — how the bytes are actually stored. Which file, which page, what index structure, whether it is compressed, which disk. WHY THREE AND NOT TWO: the middle level is what makes both kinds of independence possible. Without it, every user view would map directly onto storage, and any storage change would break every view.

The two kinds of independence

PHYSICAL DATA INDEPENDENCE change the INTERNAL level without touching the CONCEPTUAL level (and therefore without touching any application). Examples of changes that must be invisible: · adding or dropping an index · switching from a heap file to a hash file · moving a table to a different tablespace or disk · enabling compression · changing the page size · partitioning a table Achieved almost completely in real systems. This is the EASIER of the two. LOGICAL DATA INDEPENDENCE change the CONCEPTUAL level without touching the EXTERNAL level (existing views and applications keep working). Examples: · adding a new attribute to a table · adding a new entity type · splitting one table into two (with a view to restore the old shape) · widening a column Only PARTIALLY achieved. Adding a column is safe; REMOVING one that a view references cannot be hidden. That is why logical independence is the harder problem. THE ASYMMETRY, stated for exams: physical independence — largely solved logical independence — harder, because the conceptual schema is what views are DEFINED in terms of. You can add, but you cannot silently take away.

Worked example — the same data at three levels

three_levels.sql

-- ============ CONCEPTUAL LEVEL ============
-- What the data MEANS. No storage decisions appear here.
CREATE TABLE student (
    roll        INTEGER      PRIMARY KEY,
    name        VARCHAR(60)  NOT NULL,
    dept_code   CHAR(6)      NOT NULL,
    marks       NUMERIC(5,2) CHECK (marks BETWEEN 0 AND 100),
    salary      NUMERIC(10,2),
    admitted_on DATE         NOT NULL,
    FOREIGN KEY (dept_code) REFERENCES department(code)
);

-- ============ EXTERNAL LEVEL ============
-- Three different windows onto the SAME table.
CREATE VIEW v_admissions AS
    SELECT roll, name, dept_code, marks
    FROM   student;                    -- salary HIDDEN

CREATE VIEW v_payroll AS
    SELECT roll, name, salary
    FROM   student
    WHERE  salary IS NOT NULL;         -- marks HIDDEN

CREATE VIEW v_public AS
    SELECT name, dept_code
    FROM   student;                    -- everything else hidden

-- an aggregate view: the base rows are not visible at all
CREATE VIEW v_dept_summary AS
    SELECT dept_code,
           COUNT(*)    AS student_count,
           AVG(marks)  AS avg_marks,
           MAX(marks)  AS top_marks
    FROM   student
    GROUP  BY dept_code;

-- ============ INTERNAL LEVEL ============
-- Pure storage decisions. NONE of these change any query
-- above, which is exactly physical data independence.
CREATE INDEX idx_student_dept  ON student(dept_code);
CREATE INDEX idx_student_marks ON student(marks DESC);
CREATE INDEX idx_student_name  ON student(name);

-- cluster the physical rows in index order (PostgreSQL)
CLUSTER student USING idx_student_dept;

-- move the table to a different physical location
ALTER TABLE student SET TABLESPACE fast_ssd;

-- change the storage strategy for a column
ALTER TABLE student ALTER COLUMN name SET STORAGE EXTERNAL;

Nothing in the INTERNAL block required a single change to the conceptual schema or to any of the four views. That is physical data independence, demonstrated. Now consider LOGICAL changes and what survives: ADD a column → SAFE ALTER TABLE student ADD email VARCHAR(120); All four views still work — they name their columns explicitly, so a new one is simply not selected. (This is exactly why SELECT * in a view is bad practice: with * the view's column list would silently change.) WIDEN a column → SAFE ALTER TABLE student ALTER COLUMN name TYPE VARCHAR(120); SPLIT a table → SAFE, WITH WORK CREATE TABLE student_core (roll, name, dept_code, ...); CREATE TABLE student_pay (roll, salary); then redefine the view to restore the old shape: CREATE VIEW student AS SELECT c.roll, c.name, c.dept_code, c.marks, p.salary, c.admitted_on FROM student_core c LEFT JOIN student_pay p ON p.roll = c.roll; Applications querying `student` never notice. This is the strongest demonstration of logical independence. DROP a column a view references → BREAKS ALTER TABLE student DROP COLUMN marks; → ERROR: cannot drop column marks because other objects depend on it (view v_admissions) No mapping can invent data that no longer exists. That final case is why logical independence is only PARTIALLY achievable, and it is the discriminating point in an exam answer.

The SELECT * detail is worth internalising because it is a real engineering rule, not trivia. A view defined with SELECT * captures the column list at creation time in some systems and re-expands it in others — so adding a column either silently does nothing or silently changes every consumer's result shape. Naming columns explicitly is what makes "add a column" a safe operation.

Data abstraction — hiding complexity at each level

ABSTRACTION is what each level hides from the one above. PHYSICAL LEVEL hides nothing — it IS the detail: "record 4182 begins at byte 96 of page 517 in students.dbf, fields packed with 2 bytes of padding, compressed with LZ4" LOGICAL LEVEL hides all of that and says: "a student has a roll number, a name, and marks" VIEW LEVEL hides parts of the logical schema and says: "you may see names and marks" THE THREE THINGS ABSTRACTION BUYS: 1. SIMPLICITY — a query writer thinks about students, not about pages and byte offsets. 2. SECURITY — a view is a permission boundary. Grant access to v_admissions and the salary column is unreachable, not merely unqueried: GRANT SELECT ON v_admissions TO admissions_role; REVOKE ALL ON student FROM admissions_role; The user CANNOT reach salary even with a crafted query, because they have no privilege on the base table. 3. INDEPENDENCE — the mappings absorb change, as shown above. WHO MAINTAINS WHAT: · the DBA owns the internal schema and the physical mapping · the data architect owns the conceptual schema · application teams own their external views That separation of ownership is only workable BECAUSE of the independence the architecture provides. If storage changes broke applications, no DBA could ever tune anything.

independence_demo.sql

-- A concrete before/after showing what an application sees.

-- === APPLICATION CODE, written once, never changed ===
SELECT name, marks
FROM   v_admissions
WHERE  dept_code = 'ACtE07'
ORDER  BY marks DESC
LIMIT  5;

-- === DAY 1: no index. The planner must scan. ===
-- EXPLAIN output (abridged):
--   Limit
--     -> Sort  (cost=248.5..248.6)
--          Sort Key: marks DESC
--          -> Seq Scan on student  (cost=0.00..235.0 rows=50)
--                Filter: (dept_code = 'ACtE07')
--   ~ 5000 rows read from disk

-- === DAY 2: the DBA adds an index. Zero application change. ===
CREATE INDEX idx_dept_marks ON student(dept_code, marks DESC);

-- EXPLAIN output now:
--   Limit
--     -> Index Scan using idx_dept_marks on student
--          (cost=0.29..8.4 rows=50)
--          Index Cond: (dept_code = 'ACtE07')
--   ~ 5 rows read. Already in marks order, so NO sort.

-- Same SQL. Same result. ~30x less cost.
-- THAT is physical data independence paying for itself.

-- === DAY 3: the table is split for archival. ===
CREATE TABLE student_current AS
    SELECT * FROM student WHERE admitted_on >= '2024-01-01';
CREATE TABLE student_archive AS
    SELECT * FROM student WHERE admitted_on <  '2024-01-01';

-- redefine the VIEW to hide the split
CREATE OR REPLACE VIEW v_admissions AS
    SELECT roll, name, dept_code, marks FROM student_current
    UNION ALL
    SELECT roll, name, dept_code, marks FROM student_archive;

-- The application query STILL works, unchanged.
-- Logical data independence, achieved by the view mapping.

🌍 Go further: the three-level model was designed for one machine, and distributed systems added a fourth concern the ANSI/SPARC committee never considered: where the data lives. Distribution transparency means a query works the same whether a table sits on one node or is sharded across fifty — and it is genuinely harder than physical independence, because network partitions cannot be hidden. That limit is formalised in the CAP theorem. Search "CAP theorem explained" and "data independence in distributed databases" to see where the classical model stops being sufficient.

💡 Exam angle: draw the three-level architecture with both mappings — that diagram alone is often worth several marks. Define physical data independence (internal changes do not affect the conceptual schema) and logical data independence (conceptual changes do not affect external views), and state clearly that physical is largely achieved while logical is only partial — with "you can add a column but not drop one a view uses" as the reason. Give concrete examples at each level; abstract definitions alone score poorly here.

Syllabus points

Levels of abstraction
Logical & physical data independence

Create a free account to tick topics off, take notes as you read, watch the video lessons and get a day-by-day study plan built around your exam date.