Three Levels of Abstraction in DBMS (Three Schema Architecture)

Introduction

• Topic: Three Schema Architecture in DBMS
• Purpose: To explain the concept of three schema architecture and its importance.
• Request to subscribe, share, and press the bell for notifications.

What is Schema?

• Definition of Schema: Refers to the structure of data being stored.
  • Examples: Student data, faculty data, data from services like Flipkart and Amazon.
• Data Representation:
  • Data examples: Roll number, name, age (e.g., 1, A, 20).
  • Stored in a table (2-dimensional structure with rows and columns).
  • Columns: Attributes such as roll number, name, age.
  • Data Types: E.g., roll number as an integer (2 bytes or 4 bytes).

Three Levels of Schema

1. External Schema (View Level)

   • Purpose: How data is presented to the user.
   • Example: University management system showing different views for students and faculty.
     • Students see their marks, attendance, and fee structure.
     • Faculty see attendance, marks, and can enter data.
   • Security: Different views for different user roles based on authorization.
   • Real-World Applications: Similar concept applied in web applications (e.g., Flipkart, Amazon).

2. Conceptual Schema

   • Definition: Represents how data is structured and stored.
   • Example: Columns in the student's data (roll number, name, age, address).
   • ER Model: Used to represent entities and their relationships in a conceptual way.
     • Tables for students, faculty, courses, etc.
     • Relationships between tables (e.g., students and courses).
     • Acts as a blueprint for database design.

3. Physical Schema

   • Definition: Where the data is physically stored.
   • Roles: Determined by the Database Administrator (DBA).
   • Data Storage: Data can be stored in centralized or distributed locations.
   • File Storage: Data is stored in files, not in table formats on hard disks.
   • End Result: Users see data as tables through a DBMS layer, which abstracts the physical storage.

Importance of Data Independence

• Data Independence: Users do not need to know how or where data is stored.
• Abstraction: Users interact with data without needing to understand its physical representation (e.g., using Gmail without knowing where emails are stored).
• Mapping: Future discussions may include how mapping works in relation to these schemas.

Conclusion

• The main goal of three schema architecture is to provide data independence and abstraction.
• This lecture provides a foundational understanding of how DBMS organizes data at different levels.