Schema in DBMS

In a Database Management System (DBMS), a schema is the logical structure that defines how data is organized in the database. It serves as a blueprint of the database and describes:

  • Tables (relations)
  • Fields/Attributes (columns)
  • Relationships (between tables)
  • Constraints (keys, rules)
  • Views
  • Indexes
Important !!
Point 01: In the 3-Schema Architecture, the hierarchy of abstraction starts with the External Schema at the top, then moves to the Conceptual Schema, and ends with the Internal Schema at the lowest level. Point 02: A schema does not describe how the data is stored physically on disk. It only shows the logical design.

According to the three-schema architecture in DBMS, There are three types of schema in DBMS

1. View Schema (External Schema)

The View Schema (also called External Schema) defines how individual users or applications interact with the data in a database. It describes what specific data users can access and how they see it. This schema provides a customized view of the data, tailored to the needs of different users or applications, without exposing the underlying complexities of the database.

View Schema from same database can be more than one as give below for student and admin.

Schema in DBMS - View Schema Example, Admin View Schema in DBMS - View Schema Example, student View

Key Points of View Schema:

  • User-specific views: Different users or groups can have different views of the database, showing only the data that is relevant to them.

  • Data abstraction: It hides the details of the physical schema and the logical schema, presenting only the necessary information to users.

  • Security and privacy: Certain users can be restricted from accessing sensitive data, and they can only see what’s authorized for them.

2. Logical Schema (Conceptual Schema)

The Logical Schema (also called Conceptual Schema) defines how data is logically organized in a database. It describes the structure of the data, its relationships, and the rules for how the data should be stored, but without focusing on how it’s physically stored. It’s like a map that shows how everything should be structured, but without getting into technical details like storage or memory usage.

Logical schema in RDBMS is given below 

Key Points of Logical Schema:

  • What data is stored: The logical schema defines what information is kept in the database and how it’s organized.
  • How data is related: It explains how different pieces of data (tables, fields, etc.) are connected.
  • No focus on physical storage: Unlike the physical schema, the logical schema doesn’t care about file systems, hardware, or how the data is physically saved.

Components of the Logical Schema:

I. Entities and Relationships:

Defines entities (e.g., customers, products) and how they relate to each other (e.g., customers buy products).

  • Example: Customer and Order could be two entities, and they might have a relationship where “a customer can place many orders.”

II. Data Models:

  • Relational Model: Data is organized into tables with rows and columns, and relationships are created between tables using keys.

  • Entity-Relationship (ER) Model: Uses diagrams to represent data and its relationships visually.

III. Normalization:

  • The process of organizing data to avoid redundancy (duplicate data) and ensure consistency.

  • Example: Instead of storing the same customer address multiple times, you might store it once in a separate table and link it to other tables.

IV. Constraints:

  • Primary Key: A unique identifier for each record in a table (e.g., Customer ID).

  • Foreign Key: A link between tables, ensuring that records are connected properly.

  • Check Constraints: Ensures data follows specific rules (e.g., Age must be a positive number).

V. Data Types:

  • Defines the types of data that each field can hold, such as text, numbers, dates, etc.

3. Physical Schema (Internal Schema)

The Physical Schema (also called Internal Schema) describes how data is physically stored in the database.It deals with low-level storage details, such as:

  • File organization
  • Data blocks and pages
  • Indexing methods
  • Access paths
  • Compression and partitioning

It is hidden from end-users and application developers — only the DBMS and DBA (Database Administrator) handle this level.

Schema in DBMS - Physical Schema Example, B Tree

Types of Physical Schema Techniques

Category Techniques / Methods Description
File Organization Methods Heap (Unordered) Files Records stored randomly as they arrive.
Sequential (Ordered) Files Records stored in sorted order (e.g., by key).
Clustered Files Related records stored close together to reduce I/O.
Indexing Structures B-Tree Indexes Balanced tree for fast range queries.
Hash Indexes Fast lookup for equality searches.
Bitmap Indexes Useful in analytical DBs (e.g., gender = M/F).
Access Paths Primary Access Path Based on primary key.
Secondary Access Path Based on non-key attributes (e.g., Name, Department).
Data Partitioning & Storage Horizontal Partitioning Dividing rows across multiple storage units.
Vertical Partitioning Dividing columns across storage units.
Tablespaces & Segments Logical mapping to physical disk blocks.
Other Physical Design Aspects Data Compression Reduces storage size.
File Formats Row-oriented vs column-oriented storage.
Buffer Management How pages/blocks are cached in memory.

Schema in DBMS Example

Let explain all three schema with short example

View Schema: Specifies what data different users can see, abstracting away unnecessary details.

  • Example: A student can only see their own info (via StudentPortal view), while an admin can see all students’ basic details (via AdminPortal view).

Logical Schema: Defines the structure and attributes of the Student table (what data is stored).

  • Example: Student(Student_ID, Name, Age, Department, Address, Email)

Physical Schema: Describes how the data is physically stored, indexed, and optimized.

  • Example: Heap file storage, B-Tree index on Student_ID, Hash index on Department, Columnar storage with compression.

Schema vs Instance

  • Schema → The structure/blueprint of the database (static).

  • Instance → The actual data stored in the database at a given point in time (dynamic).

Example:

  • Schema: Student (Student_ID, Name, Age, Course)

  • Instance: { (1, Ali, 20, CS), (2, Sara, 22, IT) }

In Database Management System (DBMS), a schema is the logical structure that defines how data is organized in the database. It acts as a blueprint, describing tables, fields, relationships, views, and indexes without detailing how the data is physically stored.