CUIT201
DATABASE SYSTEMS
THE ANSI SPARC
Outline of Today’s Class
DBMS Schemas: ANSI SPARC
Internal
Conceptual
External
Database Schemas
Database systems comprise of complex data
structures. Thus, to make the system efficient
for retrieval of data and reduce the complexity of
the users, developers use the method of Data
Abstraction.
Data Abstraction
There are mainly three levels of data abstraction:
•Internal Level: Actual PHYSICAL storage
structure and access paths.
•Conceptual or Logical Level: Structure and
constraints for the entire database
•External or View level: Describes various user
views
Data Abstraction
There are mainly three levels of data abstraction:
•Internal Level: Actual PHYSICAL storage
structure and access paths.
•Conceptual or Logical Level: Structure and
constraints for the entire database
•External or View level: Describes various user
views
Data Abstraction
Internal Level/Schema
The internal schema defines the physical storage
structure of the database. The internal schema is a
very low-level representation of the entire database. It
contains multiple occurrences of multiple types of
internal record. In the ANSI term, it is also called
"stored record'.
Facts about Internal schema:
The internal schema is the lowest level of data abstraction
It helps you to keeps information about the actual representation of the
entire database. Like the actual storage of the data on the disk in the
form of records
The internal view tells us what data is stored in the database and how
It never deals with the physical devices. Instead, internal schema views
a physical device as a collection of physical pages
Conceptual Schema/Level
The conceptual schema describes the Database structure of the
whole database for the community of users. This schema hides
information about the physical storage structures and focuses
on describing data types, entities, relationships, etc. This logical
level comes between the user level and physical storage view.
However, there is only single conceptual view of a single
database.
Facts about Conceptual schema:
Defines all database entities, their attributes, and their
relationships
Security and integrity information
In the conceptual level, the data available to a user must be
contained in or derivable from the physical level
External Schema/Level
An external schema describes the part of the database which
specific user is interested in. It hides the unrelated details of the
database from the user. There may be "n" number of external
views for each database.
Each external view is defined using an external schema, which
consists of definitions of various types of external record of that
specific view.
An external view is just the content of the database as it is seen
by some specific particular user. For example, a user from the
sales department will see only sales related data.
External Schema/Level Cont’
Facts about external schema:
An external level is only related to the data which is viewed by
specific end users.
This level includes some external schemas.
External schema level is nearest to the user
The external schema describes the segment of the database
which is needed for a certain user group and hides the
remaining details from the database from the specific user
group
Goal of 3 level/schema of Database
Every user should be able to access the same data but able to
see a customized view of the data.
The user need not to deal directly with physical database
storage detail.
The DBA should be able to change the database storage
structure without disturbing the user's views
The internal structure of the database should remain unaffected
when changes made to the physical aspects of storage.
Advantages Database Schema
You can manage data independent of the physical storage
Faster Migration to new graphical environments
DBMS Architecture allows you to make changes on the
presentation level without affecting the other two layers
As each tier is separate, it is possible to use different sets of
developers
It is more secure as the client doesn't have direct access to the
database business logic
In case of the failure of the one-tier no data loss as you are
always secure by accessing the other tier
Disadvantages Database Schema
Complete DB Schema is a complex structure which is difficult to
understand for every one
Difficult to set up and maintain
The physical separation of the tiers can affect the performance
of the Database
Summary
There are mainly three levels of data abstraction: Internal Level,
Conceptual or Logical Level or External or View level
The internal schema defines the physical storage structure of
the database
The conceptual schema describes the Database structure of the
whole database for the community of users
An external schema describe the part of the database which
specific user is interested in
DBMS Architecture allows you to make changes on the
presentation level without affecting the other two layers
