Lecture 3: DBMS: Schemas, Architecture & Data Independence

Schemas, Instances, and States

📚 Key Definitions

Schema: The logical structure and design of the database - it's like a blueprint that defines tables, columns, data types, and relationships.

Instance: The actual data stored in the database at any particular moment in time.

Database State: The collection of all instances of all relations in a database at a particular point in time.

🔍 Additional Important Concepts

Schema Construct: A component of the schema or an object within the schema, e.g., STUDENT, COURSE.

Initial Database State: Refers to the database state when it is initially loaded into the system.

Valid State: A state that satisfies the structure and constraints of the database.

Alternative Terminology:

• Schema is also called intension

• State is also called extension

📊 Visual Comparison: Schema vs Database State

🏗️ Database Schema

Structure and organization - remains stable

📊 Database State

Actual data content - changes frequently

-- Schema Definition (DDL)
CREATE TABLE Student (
    StudentID INT PRIMARY KEY,
    Name VARCHAR(50) NOT NULL,
    Age INT CHECK (Age > 0),
    Grade DECIMAL(3,2)
);

🔄 Database State Changes

Initial State: 3 records

After INSERT: 4 records (state changed)

After UPDATE: Same count but different data (state changed)

After DELETE: 3 records again (state changed)

Note: Schema remains constant, but instance/state changes with each operation!

Quick Check: What changes more frequently - Schema or Instance?

Schema

Instance

Three-Schema Architecture

🏗️ ANSI/SPARC Three-Schema Architecture

A framework that separates the user applications from the physical database through three levels of abstraction.

📱 EXTERNAL LEVEL (View Level)

Purpose: Individual user views

Components: User-specific schemas, customized views

Example: Student Portal View, Faculty Dashboard

↓ Mapping ↓

🧠 CONCEPTUAL LEVEL (Logical Level)

Purpose: Community view of entire database

Components: All entities, relationships, constraints

Example: Complete university database schema

↓ Mapping ↓

💾 INTERNAL LEVEL (Physical Level)

Purpose: Physical storage and access methods

Components: Storage structures, indexes, access paths

Example: B+ tree indexes, file organization

🎯 Real-World Example: University Database

-- External Schema (Student View)
CREATE VIEW StudentGrades AS 
SELECT Name, CourseName, Grade 
FROM Student S, Enrollment E, Course C 
WHERE S.StudentID = E.StudentID 
AND E.CourseID = C.CourseID;

-- Conceptual Schema (Complete Logical Design)
CREATE TABLE Student (...);
CREATE TABLE Course (...);
CREATE TABLE Enrollment (...);
-- All relationships and constraints

-- Internal Schema (Physical Implementation)
-- Index on StudentID for faster lookups
CREATE INDEX idx_student_id ON Student(StudentID);
-- Clustered storage for related records

🏗️ Three Level Schema Architecture Diagram

Visual representation of the ANSI/SPARC Three-Schema Architecture showing the separation between external, conceptual, and internal levels.

Data Independence

🔓 Freedom from Data Structure Changes

Data Independence is the ability to change the schema at one level without affecting the schema at the next higher level.

🎯 Logical Data Independence

Definition: Ability to change conceptual schema without affecting external schemas

Examples:

Adding new tables
Adding new attributes
Changing relationships

💾 Physical Data Independence

Definition: Ability to change internal schema without affecting conceptual schema

Examples:

Changing storage structures
Adding/removing indexes
Changing file organization

🎯 Logical Independence Example

-- BEFORE: Simple Student table
CREATE TABLE Student (
    StudentID INT,
    Name VARCHAR(50),
    Grade DECIMAL(3,2)
);

-- User View remains the same
SELECT Name, Grade FROM StudentView;

-- AFTER: Enhanced with new attributes
CREATE TABLE Student (
    StudentID INT,
    Name VARCHAR(50),
    Grade DECIMAL(3,2),
    Email VARCHAR(100),    -- NEW COLUMN
    Department VARCHAR(30) -- NEW COLUMN
);

-- User View STILL WORKS unchanged!
SELECT Name, Grade FROM StudentView;

User applications continue to work without modification!

💾 Physical Independence Example

-- Logical Query stays the same
SELECT * FROM Student WHERE StudentID = 101;

-- PHYSICAL CHANGES (invisible to logical level):
-- Change 1: Add index for faster access
CREATE INDEX idx_student ON Student(StudentID);

-- Change 2: Change storage from heap to clustered
ALTER TABLE Student REBUILD CLUSTERED;

-- Change 3: Partition large table
CREATE PARTITION SCHEME ps_student ...

-- The logical query STILL works exactly the same!
-- Only performance improves, no code changes needed

Logical operations remain unchanged regardless of physical optimizations!

Which type of data independence is easier to achieve?

Logical Independence

Physical Independence

Interactive Knowledge Check

1. What defines the structure of a database?

Instance

Schema

State

View

2. In Three-Schema Architecture, which level deals with storage details?

External Level

Conceptual Level

Internal Level

Application Level

3. Adding an index to improve query performance is an example of?