15:25
Lec-6: Three Schema Architecture | Three Level of Abstraction | Database Management System
Gate Smashers
Overview
This video explains the three-schema architecture in Database Management Systems, also known as the three-level of abstraction. This architecture, developed in 1970, aims to achieve data independence by separating the user's view of the data from its physical storage. It consists of three levels: the external schema (user view), the conceptual schema (logical structure), and the internal/physical schema (physical storage details). This separation allows for data abstraction, meaning users don't need to know how or where their data is stored, only how to access it through their specific view.
How was this?
Save this permanently with flashcards, quizzes, and AI chat
Chapters
- A schema defines the structure of the data being stored, including its format and organization.
- The three-schema architecture, also called three-level of abstraction, was introduced to provide data independence.
- Data independence means users do not directly interact with the physical data storage; their interaction is abstracted.
- The core goal is data abstraction: hiding the complexities of data storage and management from the user.
Understanding what a schema is and why the three-schema architecture was developed is crucial for grasping how databases manage complexity and provide user-friendly access to data.
Defining the structure of student data, such as 'roll number' being an integer of a specific size, is an example of defining a schema.
- The external schema, or view level, defines how data is presented to individual users or applications.
- Different users can have different external views of the same data, tailored to their specific needs and authorizations.
- This level provides a personalized interface, ensuring users only see the data relevant to them and can perform authorized actions.
- Web and mobile applications often present data through an external schema, acting as the user's first view.
The external schema is important because it allows for user-specific data access, enhancing security and usability by tailoring the data presentation to each user's role and requirements.
A university system showing a student their marks and attendance, but a faculty member their ability to enter marks and view student performance, illustrates different external views.
- The conceptual schema describes the overall logical structure of the entire database.
- It defines the entities, attributes, and relationships between data, serving as a blueprint for the database.
- This level focuses on what data is stored and how it's related, independent of physical storage details.
- Models like the E-R (Entity-Relationship) model are used to represent the conceptual schema.
The conceptual schema is vital for database designers as it provides a comprehensive, logical view of the data, guiding the creation of tables and defining relationships without getting bogged down in physical storage specifics.
Defining tables for 'students' and 'courses' and specifying the relationship between them (e.g., a student enrolls in a course) represents the conceptual schema.
- The internal schema, or physical schema, describes how the data is physically stored on storage devices.
- It details file structures, storage locations, indexing methods, and data fragmentation strategies.
- Database administrators (DBAs) work at this level to manage the actual storage and retrieval of data.
- Data can be stored in a centralized or distributed manner at this level.
The physical schema is essential for optimizing storage efficiency and data access performance by defining the low-level details of how data resides on hardware.
Deciding to store database files on specific hard drives, in particular directories, or how to fragment data across multiple servers is part of the physical schema.
- The three-schema architecture enables data independence by insulating users and applications from changes at other levels.
- Mappings between the levels allow the system to translate requests and data representations.
- Users interact with the external schema, which is mapped to the conceptual schema, which is then mapped to the internal schema.
- This layered approach ensures that changes in physical storage (internal schema) don't affect the conceptual or external views, and vice-versa.
Data independence is a cornerstone of modern database systems, allowing for flexibility in system design, maintenance, and evolution without disrupting users or applications.
If the physical storage location of user emails changes, the user's Gmail interface (external schema) remains the same, and the conceptual structure of email data doesn't need to be altered.
Key takeaways
- The three-schema architecture separates data into user views (external), logical structure (conceptual), and physical storage (internal) to achieve data independence.
- Data abstraction hides the complexities of data storage from users, allowing them to interact with data through simplified, relevant views.
- The external schema provides customized views for different users, enhancing security and usability.
- The conceptual schema acts as a blueprint, defining the overall logical organization and relationships of the data.
- The internal schema manages the low-level physical storage details, optimized for performance and efficiency.
- This layered approach allows changes at one level (e.g., physical storage) without impacting other levels (e.g., user applications).
- Web and mobile applications leverage these principles to present data effectively and manage underlying complexity.
Key terms
Three-Schema ArchitectureThree Level of AbstractionSchemaData IndependenceData AbstractionExternal SchemaConceptual SchemaInternal SchemaView LevelPhysical SchemaDatabase Administrator (DBA)
Test your understanding
- What is the primary goal of the three-schema architecture, and how does it achieve it?
- How does the external schema differ from the conceptual schema in terms of purpose and audience?
- What kind of information is defined within the conceptual schema, and why is it considered a 'blueprint'?
- What role does the internal schema play, and who is primarily responsible for managing it?
- Explain how the three-schema architecture supports data independence using an example.