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:
- Physical Schema (Internal Level) – Storage details
- Logical Schema (Conceptual Level) – Data structure & relationships
- 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.
