Three-schema architecture (external, conceptual, internal) in DBMS Theory - Step-by-Step Execution

Concept Flow - Three-schema architecture (external, conceptual, internal)

User View (External Schema)

↓

Community View (Conceptual Schema)

↓

Physical Storage (Internal Schema)

↓

Data Files

Data flows from what users see, through a shared community view, down to how data is physically stored.

Execution Sample

DBMS Theory

User queries -> External schema
External schema maps -> Conceptual schema
Conceptual schema maps -> Internal schema
Internal schema accesses -> Physical storage

Shows how a user query moves through the three layers to access stored data.

Analysis Table

Step	Layer	Action	Result
1	External Schema	User sends query based on personal view	Query received with user-specific format
2	External to Conceptual	Map user query to community data model	Unified query for all users
3	Conceptual to Internal	Translate community model to physical storage details	Access plan for data retrieval
4	Internal Schema	Retrieve data from physical storage	Raw data fetched from disk
5	Internal to Conceptual	Convert raw data to community model format	Structured data ready
6	Conceptual to External	Convert structured data to user view	User-friendly result prepared
7	External Schema	Send result to user	User receives data in expected format

💡 Data successfully retrieved and presented to user through all three schema layers

State Tracker

Variable	Start	After Step 2	After Step 4	After Step 6	Final
Query/Data Format	User format	Community format	Physical storage format	Community format	User format
Data Location	User device	Conceptual model	Disk storage	Conceptual model	User device

Key Insights - 3 Insights

Why do we need three different schemas instead of just one?

Because users see data differently (external), the organization needs a unified model (conceptual), and data must be stored efficiently (internal). See execution_table steps 1, 2, and 3.

How does the system keep user views separate from physical storage details?

By mapping queries and data through the external and conceptual schemas before reaching the internal schema, as shown in execution_table steps 2 and 3.

What happens if the physical storage changes?

Only the internal schema changes; the conceptual and external schemas stay the same, so users are unaffected. This is implied in the separation shown in the concept_flow.

Visual Quiz - 3 Questions

Look at the execution_table, at which step is the user query translated into a community-wide data model?

A Step 1

B Step 2

C Step 3

D Step 4

💡 Hint

Check the 'Action' column in execution_table row for Step 2.

According to variable_tracker, what is the data format after Step 4?

A Physical storage format

B Community format

C User format

D Unchanged

💡 Hint

Look at 'Query/Data Format' variable after Step 4 in variable_tracker.

If the physical storage changes, which schema layer must be updated to keep user views unchanged?

A External schema

B Conceptual schema

C Internal schema

D All three schemas

💡 Hint

Refer to key_moments explanation about physical storage changes.

Concept Snapshot

Three-schema architecture separates DBMS into:
- External schema: user views
- Conceptual schema: unified community model
- Internal schema: physical storage details
This separation allows flexibility and data independence.

Full Transcript

The three-schema architecture organizes database systems into three layers. The external schema is what each user sees, tailored to their needs. The conceptual schema is a unified model representing the entire database for the organization. The internal schema deals with how data is physically stored on disks. When a user sends a query, it passes from the external schema to the conceptual schema, then to the internal schema to access data. The data then flows back through these layers, converting formats at each step, so the user receives data in their expected view. This separation helps keep user views independent from physical storage changes, making the system flexible and easier to maintain.

Practice

1. Which schema in the three-schema architecture represents the user's view of the database?

easy

A. Physical schema

B. Conceptual schema

C. External schema

D. Internal schema

Solution

1. Step 1: Understand the role of external schema

   The external schema defines how individual users or user groups see the data, focusing on their specific views.

2. Step 2: Differentiate from other schemas

   The conceptual schema is the overall logical structure, and the internal schema deals with physical storage, so they do not represent user views.

3. Final Answer:

   External schema -> Option C

4. Quick Check:

   User view = External schema [OK]

Hint: User views belong to external schema [OK]

Common Mistakes:

• Confusing conceptual schema with user view
• Thinking internal schema shows user data
• Mixing physical storage with user perspective

2. Which of the following correctly describes the internal schema in the three-schema architecture?

easy

A. It defines the physical storage of data

B. It shows the user's customized view

C. It represents the overall logical structure

D. It is the backup schema for recovery

Solution

1. Step 1: Identify the internal schema's purpose

   The internal schema describes how data is physically stored on hardware like disks and indexes.

2. Step 2: Eliminate incorrect options

   User views belong to external schema, logical structure to conceptual schema, and backup is unrelated to schema layers.

3. Final Answer:

   It defines the physical storage of data -> Option A

4. Quick Check:

   Internal schema = Physical storage [OK]

Hint: Internal schema = physical storage details [OK]

Common Mistakes:

• Confusing internal schema with user views
• Mixing conceptual schema with physical storage
• Assuming internal schema handles backups

3. Consider the following statements about the three-schema architecture:
1. The conceptual schema hides physical storage details.
2. The external schema is the complete database design.
3. The internal schema is concerned with data security.
Which of these statements are true?

medium

A. Only 2 is true

B. Only 1 is true

C. Only 3 is true

D. 1 and 3 are true

Solution

1. Step 1: Analyze statement 1

   The conceptual schema provides a logical view and hides physical storage details, so statement 1 is true.

2. Step 2: Analyze statements 2 and 3

   The external schema is a user-specific view, not the complete design, so statement 2 is false. The internal schema deals with physical storage, not directly with security, so statement 3 is false.

3. Final Answer:

   Only 1 is true -> Option B

4. Quick Check:

   Conceptual hides storage = True [OK]

Hint: Conceptual hides storage; external is user view [OK]

Common Mistakes:

• Assuming external schema is full design
• Thinking internal schema manages security
• Mixing conceptual and external roles

4. A database administrator wrote the following description:
'The conceptual schema defines how data is physically stored on disk.'
What is the error in this statement?

medium

A. Conceptual schema is unrelated to database design

B. Conceptual schema defines user views, not storage

C. Conceptual schema is the physical storage layout

D. Conceptual schema defines logical design, not physical storage

Solution

1. Step 1: Understand the conceptual schema role

   The conceptual schema defines the logical structure of the entire database, not how data is physically stored.

2. Step 2: Identify the correct description

   The internal schema defines physical storage details, so the statement incorrectly assigns this to the conceptual schema.

3. Final Answer:

   Conceptual schema defines logical design, not physical storage -> Option D

4. Quick Check:

   Conceptual = Logical design, not physical [OK]

Hint: Conceptual = logical design, internal = physical storage [OK]

Common Mistakes:

• Confusing conceptual with internal schema
• Thinking conceptual handles physical storage
• Ignoring schema layer definitions

5. A company wants to allow different departments to see only their relevant data without exposing the full database structure. Which schema layer should be customized to achieve this, and why?

hard

A. External schema, because it provides tailored user views

B. Conceptual schema, because it controls physical storage

C. Internal schema, because it manages data security

D. Physical schema, because it defines hardware layout

Solution

1. Step 1: Identify the schema controlling user views

   The external schema customizes views for different users or departments, showing only relevant data.

2. Step 2: Understand why other schemas are unsuitable

   The conceptual schema defines overall logical design, internal schema handles physical storage, and physical schema relates to hardware, none control user-specific views.

3. Final Answer:

   External schema, because it provides tailored user views -> Option A

4. Quick Check:

   Department views = External schema [OK]

Hint: Customize external schema for user-specific views [OK]

Common Mistakes:

• Choosing conceptual schema for user views
• Confusing internal schema with security control
• Assuming physical schema controls data access