ANSI SPARC Architecture

 

Think of it like building and using a university library:

• Users (students, researchers) just want to find books by topic or author. They don't care how the books are physically arranged in the stacks.

• Librarians need a logical map of the entire collection—what books the library owns, their subjects, and how they relate.

• Staff in the backroom need to know the exact physical location of each book on a specific shelf in a specific aisle.

The ANSI-SPARC architecture organizes a database system into three distinct levels or "schemas" to manage these different perspectives. A schema is simply a formal description of the structure of a database.

The Three Levels (Schemas)

Here are the three levels, from the user's perspective down to the physical storage.

1. The External Level (User View)

This is the highest level of abstraction and is what the end-users and application programs see.

• What it is: It describes the part of the database that is relevant to a specific user or group of users. It hides the rest of the database from them. A database can have many different external views.

• Purpose: To provide a customized and simplified view of the data. This also provides a layer of security, as users can only see and interact with the data they are authorized to access.

• Schema Name: External Schema or Subschema. In SQL, this is often implemented using VIEWS.

• Library Analogy: This is like a curated reading list for a specific course. A "History 101" student gets a view that only shows books relevant to their course, hiding all the science and engineering books. A biology student gets a different view.

Example:
In a university database, an external view for a Student might only show their own Courses, Grades, and Personal_Details. An external view for a Professor might show the Students enrolled in the courses they teach. Both are different views of the same underlying database.

2. The Conceptual Level (Community View)

This is the logical "big picture" of the entire database. It's the core of the architecture.

• What it is: It describes the structure of the entire database for the whole organization. It defines all the entities, attributes, relationships, and the constraints that govern the data.

• Purpose: To provide a single, unified, and consistent definition of the organization's data, independent of how it is physically stored. This is the level where Database Designers and Administrators work.

• Schema Name: Conceptual Schema. This is often represented by an Entity-Relationship (ER) Diagram.

• Library Analogy: This is the library's master card catalog or database. It contains information on every single book the library owns, including its title, author, subject, and publisher, and how books relate to each other (e.g., volumes in a series). It doesn't say where the book is physically located.

Example:
For the university database, the conceptual schema would define the STUDENT table (with columns like StudentID, Name, Address), the COURSE table, and the ENROLLMENT table that links them. It would also define rules like "A StudentID must be unique" and "A student can enroll in many courses."

3. The Internal Level (Physical View)

This is the lowest level of abstraction and describes how the data is physically stored on the storage medium (e.g., hard drives).

• What it is: It describes the physical implementation details of the database. This includes file organization, data structures used (e.g., B-trees, hash tables), indexes, and storage allocation.

• Purpose: To manage the physical storage and optimize performance for data retrieval and storage. This level is the concern of the DBMS and the Database Administrator (DBA). It is hidden from application developers and end-users.

• Schema Name: Internal Schema or Physical Schema.

• Library Analogy: This describes the physical layout of the library building. It specifies that "History books are on the 3rd floor, in aisles 5-8, organized by the Dewey Decimal System." It's the low-level detail needed to physically find a book.

Example:
The internal schema might specify that the STUDENT table is stored in a file named stud_data.dbf, that there is a B-tree index on the StudentID column for fast lookups, and that the data is stored in blocks of 8KB on the disk.

---

The Most Important Benefit: Data Independence

The whole point of this three-level separation is to achieve data independence. This is a critical concept and very likely to be on your exam.

Data Independence is the ability to change the schema at one level of the database system without having to change the schema at the next higher level.

There are two types:

1. Logical Data Independence

• Definition: The ability to change the conceptual schema without having to change the external schemas or application programs.

• Why it's important: It allows the DBA to evolve the database structure to meet new business requirements. You can add a new table, add a new column to an existing table, or split a table into two (normalization) without breaking the existing applications that use the database. As long as the data needed by an external view is still present in the conceptual schema, that view will continue to work.

• Example: You decide to add a Phone Number column to the STUDENT table. The professor's application, which only displays student names and IDs for their class roster, doesn't need to be rewritten. It is independent of this logical change.

2. Physical Data Independence

• Definition: The ability to change the internal schema without having to change the conceptual schema.

• Why it's important: It allows the DBA to optimize the database's performance without impacting anyone. The DBA can change the file structures, add or remove indexes, or move the database to a different disk drive to make it faster. The logical structure of the data (the conceptual schema i.e relations and attributes doesn't change unless we explicitly want to change the data) remains the same, so no applications or user views are affected.

• Example: The database is running slowly. The DBA adds an index to the Major column in the STUDENT table to speed up searches for students in a particular major. This is a purely physical change. The conceptual schema doesn't change, and no application code needs to be modified. The applications just run faster.

Summary for Your Exam

• What: The ANSI-SPARC architecture is a three-level framework (External, Conceptual, Internal) to separate user views from physical storage.

• External Level: Individual user views (many of them).

• Conceptual Level: The logical big picture of the entire database (only one).

• Internal Level: The physical storage details (only one).

• Why: To achieve Data Independence.

• Logical Data Independence: Change the conceptual schema without affecting external schemas (e.g., add a column).

• Physical Data Independence: Change the internal schema without affecting the conceptual schema (e.g., add an index for performance).

This separation provides flexibility, reduces application maintenance costs, and allows the database to evolve over time.