DBMS Topics
HubUnit 1 — Introduction to DBMS & Entity-Relationship ModelAdvantages and Disadvantages of DBMSAggregationCharacteristics of DBMSData IndependenceData ModelsDatabase Users and AdministratorsDBMS LanguagesER DiagramsEntity-Relationship ER ModelIntroduction to DBMSKeys in DBMSRelationships and CardinalitySchema and InstanceSpecialization and GeneralizationUnit 1 Summary — Introduction to DBMS & ER ModelThree-Level Architecture ANSI/SPARC ArchitectureWeak Entity SetUnit 2 — Relational Model & SQLConstraints in SQLCursorsDCL and TCL CommandsDDL CommandsDML CommandsDomain Relational Calculus DRCFunctions and Stored ProceduresJoins in SQLNested Queries SubqueriesRelational AlgebraRelational CalculusRelational ModelSQL IntroductionUnit 2 Summary — Relational Model & SQLTriggers in SQLTuple Relational Calculus TRCViews in SQLArmstrong's Axioms & Closure of AttributesBoyce-Codd Normal Form BCNFClosure of AttributesDependency PreservationFifth Normal Form 5NFFirst Normal Form 1NFFourth Normal Form 4NFFunctional DependencyLossless DecompositionMultivalued DependencyNormal Forms — OverviewNormalization — IntroductionSecond Normal Form 2NFUnit 3 Summary — NormalizationThird Normal Form 3NFUnit 4 — Transaction Management & Concurrency ControlACID PropertiesCheckpointsConcurrency Control & SerializabilityDeadlock in DBMSLock-Based Protocols & Two-Phase LockingLog-Based RecoveryRecovery SystemSerializabilityShadow PagingUnit 4 Summary — Transaction Management & Concurrency ControlTimestamp-Based ProtocolTransaction ManagementTransaction StatesTwo-Phase Locking 2PLUnit 5 — Storage & IndexingB+ TreeB-TreeCost EstimationDynamic HashingFile OrganizationHashing TechniquesIndexing — IntroductionMulti-Level IndexingQuery OptimizationQuery ProcessingSingle-Level IndexingStatic HashingUnit 5 Summary — Storage & IndexingUnit 6 — Advanced Topics in DBMSBig Data and DBMSCloud DatabaseData MiningData WarehouseDatabase SecurityDistributed DatabaseDistributed Query ProcessingDistributed DBMS Architecture, Fragmentation & ReplicationMongoDB — IntroductionNoSQL DatabaseReplication in Distributed DatabasesUnit 6 Summary — Advanced Topics in DBMSViva Voce — DBMSImportant Viva Questions — DBMSInterview Questions — DBMSViva Answers — DBMSDBMS Multiple Choice Questions MCQsDBMS Lab Programs
Three-Level Architecture ANSI/SPARC Architecture
Last Updated : 21 May, 2026
The ANSI/SPARC three-level architecture also called the three-schema architecture is a framework for database management proposed by the American National Standards Insti
Unit 1416 words10 headingsTables includedExamples included
Overview
The ANSI/SPARC three-level architecture (also called the three-schema architecture) is a framework for database management proposed by the American National Standards Institute (ANSI) Standards Planning and Requirements Committee (SPARC) in 1975.
The goal is to separate the user's view of data from the physical storage of data, providing data independence and simplifying database management.
The Three Levels
External Level
Views / Apps
- Protected by logical independence
↓ Logical Independence
Conceptual Level
Logical Schema
- Central database structure
↓ Physical Independence
Internal Level
Physical Storage
- Files, indexes, pages, storage format
Level 1 — External Level (View Level)
The external level is the highest level and is closest to the end user. It describes the part of the database relevant to a particular user or application.
Key Points:
- There can be multiple external views for a single database
- Each view hides irrelevant or sensitive data from the user
- Defined using SQL CREATE VIEW statements
- Different users may see overlapping or completely different data
| Full Table | Employee(EmpID, Name, Salary, SSN, DeptID, HireDate) |
| HR View | Employee(EmpID, Name, DeptID, HireDate) |
| Payroll View | Employee(EmpID, Name, Salary, SSN) |
| Manager View | Employee(EmpID, Name, DeptID) |
Level 2 — Conceptual Level (Logical Level)
The conceptual level describes the logical structure of the entire database for all users. It is the "community view" — a single unified description of all data stored.
Key Points:
- Describes what data is stored (not how)
- Includes all entities, attributes, relationships, constraints
- Defined in terms of the conceptual data model (relational schema)
- Managed by the DBA
- Independent of physical storage and individual user views
Conceptual Schema:
Student(SID INT PK, Name VARCHAR, DOB DATE, DeptID INT FK)
Department(DeptID INT PK, DeptName VARCHAR, Location VARCHAR)
Course(CID INT PK, Title VARCHAR, Credits INT)
Enrollment(SID INT FK, CID INT FK, Grade CHAR)
Level 3 — Internal Level (Physical Level)
The internal level describes how data is physically stored on disk. It is the lowest level of abstraction.
Key Points:
- Describes actual storage structures used
- Includes file organization (sequential, indexed, hashed)
- Specifies indexes, storage allocation, compression
- Managed by the system (DBA has some influence)
- Completely hidden from end users
| - File | /var/db/student.ibd |
| - Format | InnoDB clustered B+ tree index on SID |
| - Page size | 16 KB |
| - Buffer pool allocation | 128 MB |
Mappings Between Levels
External-Conceptual Mapping
- Defines how external views correspond to the conceptual schema
- When a view is queried, the DBMS translates it to a conceptual-level query
- If the conceptual schema changes (e.g., a column is renamed), only this mapping is updated — the views themselves remain unchanged
Conceptual-Internal Mapping
- Defines how conceptual tables correspond to physical storage
- If the physical storage changes (e.g., a new index is added or data is moved), only this mapping is updated — the conceptual schema remains unchanged
Benefits of the Three-Level Architecture
| Benefit | Explanation |
|---|---|
| Data Independence | Changes at one level do not require changes at other levels |
| Data Security | Sensitive data can be hidden at the external level |
| Customization | Different users get tailored views of the same database |
| Simplification | Users work with simple views, not complex physical storage |
| Maintainability | Physical reorganization doesn't break applications |
Visual Summary
External Level
Views / Apps
- Protected by logical independence
↓ Logical Independence
Conceptual Level
Logical Schema
- Central database structure
↓ Physical Independence
Internal Level
Physical Storage
- Files, indexes, pages, storage format
Exam Focus
Revise definitions, diagrams, examples, and short-answer points for Three-Level Architecture ANSI/SPARC Architecture.
Interview Use
Prepare one clear explanation, one practical example, and one common mistake for this DBMS topic.
Search Terms
dbms, database management system, database notes, sql, unit, three, level, architecture
Related DBMS Topics
Continue learning this concept
Unit 1Unit 1 — Introduction to DBMS & Entity-Relationship ModelThis unit introduces the fundamental concepts of database management systems, the architecture that underlies them, and the Entity-Relationship ER model used for conceptuUnit 1Advantages and Disadvantages of DBMSData is stored in a centralized location. Normalization ensures that each fact is stored only once, eliminating duplicate data across multiple files.Unit 1AggregationAggregation is an abstraction mechanism in the Extended ER EER model that allows a relationship set to be treated as a higher-level entity set. This makes it possible forUnit 1Characteristics of DBMSA DBMS has distinct characteristics that differentiate it from simple file-storage systems. These characteristics are what make a DBMS powerful, flexible, and reliable foUnit 1Data IndependenceData independence is the ability to modify the definition or structure at one level of the database without affecting the definitions at the next higher level. It is one Unit 1Data ModelsA data model is a collection of conceptual tools for describing data, data relationships, data semantics, and consistency constraints. It provides a way to think about an