Schema vs instance; three-schema architecture; logical and physical data independence

0 · Recall Warm-up

Answer from memory first. Answers are in the Answer Vault at the bottom.

1. Where does a DBMS keep metadata, and what does that make the database? from Lesson 0.1.2
2. Which language defines structure and which one reads/changes data? from Lesson 0.1.2
3. Name the problem when ₹500 leaves account X but never reaches Y after a crash. from Lesson 0.1.1

1 · The Hook

A college database runs fine for years. Then the DBA buys faster disks and moves the student data to them in a new storage format. On Monday morning, would every app the college has (exam results, library, hostel) need to be rewritten?

If the answer were yes, nobody could ever improve how data is stored. A DBMS avoids this by describing the data at three separate levels, so that a change at one level stays at that level.

2 · Predict First

The DBA moves the student data to new disks and adds an index to make searches faster. The student records themselves (roll, name, dept, CGPA) do not change. Should the exam-result app need any change? Then: the DBA adds a new field "email" to every student record. Should the exam-result app need any change?

Make a guess in your head. The answer opens Section 3.

3 · Core Concept

Predict First — the answer

First change: no. Only how data is stored changed, not what is stored. This is physical data independence.

Second change: also no, as long as the app does not use email. The overall description of the data changed, but the part the app uses did not. This is logical data independence, and it is harder to achieve.

Schema vs instance

• The schema is the description of the database: what record types exist, their fields and types, and the rules. It is fixed when the database is designed and changes rarely. It is also called the intension.
• An instance (or database state, or extension) is the actual data in the database at one moment. It changes with every insert, delete or update.

Analogy: the schema is the printed admission form; an instance is the pile of filled forms in the office today.

Three-schema architecture

Between the levels the DBMS keeps two mappings: external ↔ conceptual and conceptual ↔ internal. A request at the external level is translated through these mappings down to stored data.

Data independence

Data independence is the ability to change the schema at one level without having to change the schema at the next higher level.

• Physical data independence: change the internal schema (new disks, new file organisation, a new index) without changing the conceptual schema. Only the conceptual–internal mapping changes. Easier to achieve; almost every DBMS provides it.
• Logical data independence: change the conceptual schema (add a field, add a record type, split a record type into two) without changing the external schemas or the application programs. Only the external–conceptual mapping changes. Harder to achieve, because apps depend closely on the logical structure.

Removing a field that an external schema uses cannot be hidden: that view must change. Logical data independence protects only the views that do not depend on what changed.

4 · Visual

Three levels, two mappings. A change below a mapping is absorbed by changing that mapping only.

5 · Worked Examples

Example 1 Easy

Schema or instance? (a) "STUDENT has fields roll, name, dept, cgpa"; (b) "Today there are 1,240 student records"; (c) "Asha's CGPA is 9.1".

Solution

(a) schema. (b) and (c) describe the data at a moment: instance.

Example 2 Medium

Which level does each change touch, and which independence keeps apps unchanged? (a) Student records are moved from one disk to another. (b) A new record type HOSTEL_ROOM is added. (c) An index is created on roll number.

Solution

(a) internal → physical data independence. (b) conceptual → logical data independence. (c) internal → physical data independence.

Example 3 GATE

Which statement is false? (A) There is exactly one conceptual schema. (B) Physical data independence means changes to the internal schema do not require changes to the conceptual schema. (C) Logical data independence is easier to achieve than physical data independence. (D) Each user group may have its own external schema.

Solution

(C) is false. Logical data independence is harder, because application programs depend on the logical structure of the data far more than on how it is stored.

8 · Connect the Dots

Lesson 0.1.2 said the DBMS keeps a description of the data in its catalog; this lesson split that description into three levels and named the two kinds of data independence. Next: what language of description to use at each level, the data models (ER for design, relational for the conceptual level).

9 · Self-Test

GATE format. Try every question before opening the vault.

Q1 · MCQ · 1 mark(s) — Schema vs instance

The data stored in a database at a particular moment is called:

1. (A) the catalog
2. (B) the schema
3. (C) the instance (database state)
4. (D) the intension

Q2 · MCQ · 1 mark(s) — Independence

Adding an index on roll number without changing any application is an example of:

1. (A) physical data independence
2. (B) a change to the instance
3. (C) logical data independence
4. (D) external schema change

Q3 · MCQ · 1 mark(s) — Levels

Which level describes the entire database for all users, hiding storage details?

1. (A) Physical
2. (B) Internal
3. (C) External
4. (D) Conceptual

Q4 · MSQ · 2 mark(s) — Independence

Which statements are true?

1. (A) Logical data independence is harder to achieve than physical data independence.
2. (B) Physical data independence is achieved by changing only the conceptual–internal mapping.
3. (C) Removing a field used by a view can always be hidden from that view.
4. (D) There can be several external schemas.

Q5 · NAT · 1 mark(s) — Counting

A database has 7 user views. How many conceptual schemas and internal schemas does it have in total?

Numerical answer: ________

11 · Concept Ledger

Unlocked in this lesson. From here on, these terms may be used.

schema (intension)instance (state, extension)external schema (view)conceptual schemainternal schemamappingdata independencephysical data independencelogical data independenceindex (a structure for faster search)