Database Schema Types: Breaking Down- Physical, Logical, and View Schemas

Types of Schema:Think of “types of schema” as different ways of looking at and organizing a database:

• Physical Schema: This is about how data is physically stored on the computer’s storage devices. It deals with details like how data files are organized, how they are indexed for quick access, and how they are stored on hard drives or other storage media. It’s like understanding how files are stored in folders on your computer.
• Logical Schema: This is about the overall structure of the data in the database. It defines what data is stored, how it is organized, and how different pieces of data are related to each other. It’s like understanding the structure of a book, with chapters, sections, and subsections.
• View or External Schema: This is about how specific users or groups of users see and interact with the data in the database. It defines what information is visible to different users and how they can access and use that information. It’s like different people having access to different parts of the same book based on their interests or needs.

Lets try to understand these schema types with simple analogies and descriptions to have a clear picture.

1. Physical Schema:
Imagine a library storing books on its shelves. The physical schema would represent how the books are physically organized in the library — the arrangement of shelves, the order of books on each shelf, and how the library staff index the books to find them quickly. It’s concerned with the low-level details of data storage on the computer’s storage devices.

2. Logical Schema:
Continuing with the library analogy, the logical schema would be the overall structure of the library’s collection. It defines how the books are categorized into different sections, such as fiction, non-fiction, science, etc. It also describes the relationships between different sections and how they are organized within each section, like having subsections for different authors or topics. The logical schema focuses on the higher-level organization and structure of the data.

3. View or External Schema:
Now, consider library users with varying interests. Each user might have access to a different catalog or subset of books based on their preferences or roles. For example, students might have access to the academic section, while children might have access to the children’s books. The external schema determines what data each user can see and interact with, and how they can access and use that information based on their specific needs.