In the ANSI-SPARC three-schema architecture, the conceptual schema describes:

The whole database's logical structure, independent of any one application and independent of physical storage

Only one user's view of the data

The physical file organisation and indexes

The network protocol used to access the database

A deductive database differs from a plain relational database chiefly in that it:

Can answer queries with facts inferred from stored logical rules, not just facts already stored

Is identical to a temporal database

A temporal database tracking both valid time and transaction time is needed chiefly because:

A correction entered today may concern a fact that was true at some earlier point in the real world

Databases cannot store dates otherwise

It eliminates the need for a conceptual schema

It is required by every relational database regardless of application

A relation split into horizontal fragments distributed across two sites means:

Disjoint subsets of ROWS are stored at each site

Disjoint subsets of COLUMNS are stored at each site

The entire relation is copied identically to both sites

No data is stored at either site

Which of the following are true of Geographic Information Systems (GIS) databases? (More than one option may be correct.)

They store spatial data such as points, lines and polygons tied to real-world coordinates

They typically need spatial indexes such as R-trees for efficient proximity queries

'Find all restaurants within 2 km' is an example of a spatial query

They store spatial data such as points, lines and polygons tied to real-world coordinates

They typically need spatial indexes such as R-trees for efficient proximity queries

A plain B+-tree index efficiently answers all spatial queries with no adaptation

'Find all restaurants within 2 km' is an example of a spatial query

Queries on genome sequence data are fundamentally about:

Approximate similarity (allowing insertions, deletions and substitutions), not exact match

Exact byte-for-byte equality only

Nothing to do with sequences at all

Standard B+-tree range queries with no adaptation

Database Architecture and Enhanced Data Models

The course's three Databases chapters cover the ER model, relational algebra and calculus, SQL, normalisation, indexing and transactions — the relational core NET's Unit 4 asks. This chapter covers what Unit 4 asks beyond that core: the three-schema architecture and data independence, which explain why a relational database is organised as it is, and the family of enhanced data models — temporal, multimedia, deductive, XML and internet, mobile and distributed databases, and geographic information systems.

Three-Schema Architecture and Data Independence

The ANSI-SPARC three-schema architecture
Level	Describes
External schema	Each user/application's own view of the data — many external schemas can exist, each showing only what that user needs.
Conceptual schema	The whole database's logical structure — all entities, relationships and constraints — independent of any one application and independent of physical storage.
Internal schema	The actual physical storage: file organisation, indexes, access paths — how the data is really stored on disk.

🎯 Data independence is defined against these exact levels

LOGICAL data independence is the capacity to change the CONCEPTUAL schema (e.g. add a new entity type) without needing to rewrite the EXTERNAL schemas/application programs built on top of it. PHYSICAL data independence is the capacity to change the INTERNAL schema (e.g. switch a file organisation, add an index) without needing to change the CONCEPTUAL schema at all. Physical data independence is easier to achieve in practice, since the conceptual level was already designed with no reference to storage details in the first place — logical independence is the harder guarantee, because application code often does depend on the conceptual schema's exact shape.

Temporal, Multimedia, Deductive and XML/Internet Databases

Temporal databases track how data changes over TIME rather than storing only the current state — VALID time (when a fact was true in the real world) and TRANSACTION time (when it was recorded in the database) are tracked as separate dimensions, since a correction entered today about a fact that was true last year needs both.
Multimedia databases store and query images, audio, video and other unstructured/binary content, typically needing CONTENT-BASED retrieval (find images similar to this one) rather than exact-match queries, since a byte-for-byte comparison of media is rarely what a query actually means.
Deductive databases augment stored FACTS with logical RULES, and answer queries by inferring new facts from the rules (a Prolog-like, logic-programming approach) — this is what distinguishes a deductive database from a plain relational one, which can only return facts already stored, never facts inferred from rules over them.
XML/Internet databases and mobile databases: XML's self-describing, hierarchical (tree-shaped) structure suits semi-structured web data poorly served by the rigid relational model; mobile databases must additionally handle intermittent connectivity and DISCONNECTED operation, replicating and later synchronising/reconciling data once connectivity resumes.
Geographic Information Systems (GIS) store spatial data (points, lines, polygons tied to real-world coordinates) alongside conventional attribute data, and need spatial operators and spatial indexes (e.g. R-trees) that a plain relational engine's B+-tree indexes are not built to answer efficiently — 'find all restaurants within 2 km' is a spatial query, not an equality or range query on an ordinary attribute.

Distributed Databases and Client-Server Architectures

A distributed database is a single logical database whose data is physically spread across multiple sites connected by a network, appearing to users as one integrated database (distribution transparency). Data can be FRAGMENTED (a relation split into horizontal fragments — disjoint subsets of rows — or vertical fragments — disjoint subsets of columns — and distributed across sites) and/or REPLICATED (multiple copies of the same data at different sites, trading extra storage and synchronisation cost for availability and faster local reads). A query optimiser for a distributed database must additionally decide WHERE each part of a query should run and how to minimise the volume of data shipped over the network — a cost dimension that a single-site optimiser never has to consider.

⚠️ Fragmentation and replication are independent decisions

A distributed design can fragment without replicating (each row lives at exactly one site, just split up), replicate without fragmenting (the whole relation copied wholesale to several sites), or do both (fragments that are themselves replicated). A question naming only one of the two is not implying the other is absent — check what the question actually states rather than assuming a design must combine them.

Genome Data Management

Genome data management is a specialised application of database technology to biological sequence data (DNA/RNA/protein sequences), which is characterised by very high volume, sequences of variable and sometimes enormous length, and queries that are fundamentally about approximate SIMILARITY (does this sequence resemble a known one, allowing for insertions/deletions/substitutions) rather than exact match — a query shape closer to multimedia content-based retrieval than to conventional relational lookup, and one for which specialised indexing (e.g. suffix trees/arrays over sequence data) is used rather than a plain B+-tree.

Key takeaways

The three-schema architecture separates external (per-user), conceptual (whole-database logical) and internal (physical storage) levels.
Logical data independence protects against a conceptual-schema change; physical data independence (easier to achieve) protects against an internal-schema change.
Temporal databases separate valid time from transaction time; deductive databases answer queries by inferring new facts from stored rules, not just returning stored facts.
A distributed database can fragment (horizontal/vertical) and/or replicate its data across sites — these are independent design decisions.
GIS and genome databases both need specialised (spatial or sequence-similarity) indexing that a plain relational B+-tree does not efficiently support.

Practice questions (8)

Attempt each one before opening the answer. Every explanation names the tempting wrong option as well as the right one, because that is where marks are lost.

In the ANSI-SPARC three-schema architecture, the conceptual schema describes:
1. The whole database's logical structure, independent of any one application and independent of physical storage
2. Only one user's view of the data
3. The physical file organisation and indexes
4. The network protocol used to access the database
Show answer
Answer: A — The whole database's logical structure, independent of any one application and independent of physical storage
The conceptual schema is the single, application-independent logical structure (entities, relationships, constraints) that sits between the many external (per-user) schemas and the one internal (physical) schema.
Adding a new index to speed up queries, without changing the conceptual schema at all, relies on:
1. Physical data independence
2. Logical data independence
3. Network transparency
4. Deductive inference
Show answer
Answer: A — Physical data independence
An index change is an internal-schema (physical storage) change; physical data independence is precisely the property that such a change requires no change to the conceptual schema above it.
A deductive database differs from a plain relational database chiefly in that it:
1. Can answer queries with facts inferred from stored logical rules, not just facts already stored
2. Cannot store any data at all
3. Uses only spatial indexes
4. Is identical to a temporal database
Show answer
Answer: A — Can answer queries with facts inferred from stored logical rules, not just facts already stored
A deductive database augments stored facts with logical rules and infers new facts at query time, a Prolog-like capability a plain relational database, which returns only what is explicitly stored, does not have.
A temporal database tracking both valid time and transaction time is needed chiefly because:
1. A correction entered today may concern a fact that was true at some earlier point in the real world
2. Databases cannot store dates otherwise
3. It eliminates the need for a conceptual schema
4. It is required by every relational database regardless of application
Show answer
Answer: A — A correction entered today may concern a fact that was true at some earlier point in the real world
Separating valid time (when a fact held in reality) from transaction time (when it was recorded) is what lets a database correctly represent late corrections and retroactive updates without losing the history of what was believed when.
A relation split into horizontal fragments distributed across two sites means:
1. Disjoint subsets of ROWS are stored at each site
2. Disjoint subsets of COLUMNS are stored at each site
3. The entire relation is copied identically to both sites
4. No data is stored at either site
Show answer
Answer: A — Disjoint subsets of ROWS are stored at each site
Horizontal fragmentation splits a relation row-wise; vertical fragmentation would instead split it column-wise, and full copies at both sites would instead be replication rather than fragmentation.
Which of the following are true of Geographic Information Systems (GIS) databases? (More than one option may be correct.)
1. They store spatial data such as points, lines and polygons tied to real-world coordinates
2. They typically need spatial indexes such as R-trees for efficient proximity queries
3. A plain B+-tree index efficiently answers all spatial queries with no adaptation
4. 'Find all restaurants within 2 km' is an example of a spatial query
Show answer
Answer: A — They store spatial data such as points, lines and polygons tied to real-world coordinates; B — They typically need spatial indexes such as R-trees for efficient proximity queries; D — 'Find all restaurants within 2 km' is an example of a spatial query
GIS data is fundamentally spatial and needs spatial indexing structures built for proximity/containment queries; a plain B+-tree, built for ordered/equality lookups on a single attribute, does not answer such queries efficiently on its own.
Name the term for a database design choice in which the SAME data is stored as multiple copies at different sites, trading extra storage and synchronisation cost for higher availability and faster local reads.
Numerical answer — type the value.
Show answer
Answer: replication
Replication is independent of fragmentation — a relation can be replicated wholesale, fragmented without replication, or both fragmented and replicated.
Queries on genome sequence data are fundamentally about:
1. Approximate similarity (allowing insertions, deletions and substitutions), not exact match
2. Exact byte-for-byte equality only
3. Nothing to do with sequences at all
4. Standard B+-tree range queries with no adaptation
Show answer
Answer: A — Approximate similarity (allowing insertions, deletions and substitutions), not exact match
A genome query typically asks whether a sequence resembles a known one within some tolerance for insertions/deletions/substitutions, which is why specialised sequence-indexing structures such as suffix trees/arrays are used rather than a plain B+-tree built for exact or ordered lookups.