Schema in DBMS: Definition, Types, and Examples

What is a Schema?

A schema in a Database Management System (DBMS) defines the logical structure of a database, including its tables, relationships, constraints, views, indexes, and permissions. It acts as a blueprint that dictates how data is organized and how different entities interact.

Key Characteristics of a Schema

✅ Logical structure – Defines how data is stored and related.
✅ Includes constraints – Specifies rules (e.g., primary keys, foreign keys).
✅ Does not store data – Acts as a framework for data storage.
✅ Can be modified – Using ALTER SCHEMA or ALTER TABLE.

---

Types of Database Schemas

A DBMS schema can be divided into different levels based on the ANSI-SPARC three-tier architecture:

1. Physical Schema (Internal Level) – Storage details
2. Logical Schema (Conceptual Level) – Data structure & relationships
3. View Schema (External Level) – User-specific views

---

1. Physical Schema (Internal Schema)

🔹 Definition: Describes how data is physically stored on hardware (disks, SSDs).
🔹 Includes: File organization, indexing methods, access paths.
🔹 Users: Database administrators (DBAs) & system engineers.

💡 Example:

• Data is stored in B-trees, hash indexes, or tablespaces.
• Indexing is used to speed up retrieval.

---

2. Logical Schema (Conceptual Schema)

🔹 Definition: Defines the structure of the database (tables, attributes, relationships).
🔹 Includes:

• Tables & fields (Employee(id, name, department))
• Relationships (One-to-Many between Employee & Department)
• Constraints (Primary key, foreign key, NOT NULL)
  🔹 Users: Database developers, architects.

Defines the Employee table structure with a foreign key linking it to the Department table.

3. View Schema (External Schema)

🔹 Definition: Defines how different users see the database.
🔹 Includes:

• User permissions (HR sees salaries, but employees don’t).
• Custom views for business intelligence and reporting.
  🔹 Users: End-users, business analysts, security teams.

• Creates a custom view that only shows salary details for a specific department.

---

Other Types of Schemas in DBMS

Schema Type	Description	Example
Star Schema	A single fact table connected to multiple dimension tables	Data warehouses, OLAP
Snowflake Schema	A normalized version of a star schema	Complex analytics
Fact Constellation	Multiple fact tables sharing common dimension tables	Large-scale BI applications
ER Model Schema	Uses Entity-Relationship diagrams to model relationships	Database design phase

---

Comparison Table: Physical vs. Logical vs. View Schema

Feature	Physical Schema	Logical Schema	View Schema
Focus	How data is stored	Data structure & relationships	User-specific views
Includes	Files, indexes, partitions	Tables, relationships, constraints	Custom reports, security filters
Users	DBAs, System Engineers	Developers, Architects	End-users, Analysts

---

Schema Modification (ALTER SCHEMA Commands)

Modify Table Structure (Logical Schema Change)

Conclusion

• The Physical Schema handles storage details.
• The Logical Schema defines tables, relationships, and constraints.
• The View Schema provides user-specific data access.
• Different schemas like Star, Snowflake, and Fact Constellation are used in data warehouses.