Three-schema architecture (external, conceptual, internal) in DBMS Theory - Mini Project: Build & Apply

Understanding Three-Schema Architecture in DBMS

📖 Scenario: You are working as a database assistant helping a company organize how their data is stored and accessed. To do this, you need to understand the three-schema architecture, which separates how data is viewed and stored.

🎯 Goal: Build a simple representation of the three-schema architecture by creating data structures for the external, conceptual, and internal schemas. This will help you understand how each layer works and relates to the others.

📋 What You'll Learn

Create a dictionary called external_schema with two user views and their descriptions.

Create a dictionary called conceptual_schema describing the overall logical structure.

Create a dictionary called internal_schema describing the physical storage details.

Link the schemas by adding a variable schema_mappings that shows how external views map to conceptual schema.

💡 Why This Matters

🌍 Real World

Database designers and administrators use the three-schema architecture to separate user views from physical data storage, making databases easier to manage and adapt.

💼 Career

Understanding this architecture is important for roles like database administrator, data architect, and software developer working with databases.

1

Create the External Schema

Create a dictionary called external_schema with these exact entries: 'SalesView': 'Shows sales data for sales team' and 'HRView': 'Shows employee data for HR team'.

DBMS Theory

# Create the external_schema dictionary with two views
# Your code here

Hint

The external schema defines how different users see the data. Use a dictionary with view names as keys and descriptions as values.

2

Create the Conceptual Schema

Create a dictionary called conceptual_schema with these exact entries: 'Tables': ['Employees', 'Sales'] and 'Relationships': ['EmployeeID links Employees and Sales'].

DBMS Theory

external_schema = {
    'SalesView': 'Shows sales data for sales team',
    'HRView': 'Shows employee data for HR team'
}
# Create the conceptual_schema dictionary with tables and relationships
# Your code here

Hint

The conceptual schema shows the overall logical structure of the database. Use a dictionary with keys 'Tables' and 'Relationships'.

3

Create the Internal Schema

Create a dictionary called internal_schema with these exact entries: 'Storage': 'Disk storage with indexing' and 'FileOrganization': 'Heap files'.

DBMS Theory

external_schema = {
    'SalesView': 'Shows sales data for sales team',
    'HRView': 'Shows employee data for HR team'
}

conceptual_schema = {
    'Tables': ['Employees', 'Sales'],
    'Relationships': ['EmployeeID links Employees and Sales']
}
# Create the internal_schema dictionary with storage and file organization
# Your code here

Hint

The internal schema describes how data is physically stored. Use a dictionary with keys 'Storage' and 'FileOrganization'.

4

Link Schemas with Mappings

Create a dictionary called schema_mappings with these exact entries: 'SalesView': 'Maps to Sales table in conceptual schema' and 'HRView': 'Maps to Employees table in conceptual schema'.

DBMS Theory

external_schema = {
    'SalesView': 'Shows sales data for sales team',
    'HRView': 'Shows employee data for HR team'
}

conceptual_schema = {
    'Tables': ['Employees', 'Sales'],
    'Relationships': ['EmployeeID links Employees and Sales']
}

internal_schema = {
    'Storage': 'Disk storage with indexing',
    'FileOrganization': 'Heap files'
}
# Create the schema_mappings dictionary linking external views to conceptual tables
# Your code here

Hint

The schema mappings show how each external view relates to the conceptual schema. Use a dictionary with external view names as keys and mapping descriptions as values.

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