Chapter One

Introduction to Database System

1.1. Introduction
Database is a shared collection of logically related data, and a description of this data, designed to meet the
information needs of an organization. It is a collection of inter-related data which is used to retrieve, insert and
delete the data efficiently. It is also used to organize the data in the form of a table, schema, views, and reports,
etc. It is a collection of data that is managed by a database management system (DBMS). Databases are
generally accessed electronically from a computer system and are usually controlled by a database management
system (DBMS). DBMS is a collection of interrelated data's stored in a database server; these data's will be
stored in the form of tables.
The aim of the database is to provide a way to store and retrieve database information in a fast and efficient
manner.
Database and Database Systems have become an essential component of everyday life in modern society. It is
fair to say that databases play a critical role in almost all areas where computers are used.
Some areas of Database Applications:
 Banking: all transactions (depositing or withdrawing money from a bank)
 Airlines: reservations (making airline reservation), schedules
 Universities: registration, grades
 Sales: customers, products, purchases (purchasing using your credit card)
 Manufacturing: production, inventory, orders, supply chain
 Human resources: employee records, salaries, tax deductions
Data has no meaning and hence not helpful, information is useful to make decisions and control and
coordinate activities. Data is input to Information System and information is output from the system. Data is a
collection of facts and figure that can be recorded; it can be in text, number, speech, video, image.
Example: The numbers 18, 20, 23, 20, 25, 25, 28 can be considered as data. But if you are told that these are
the highest temperature recordings during the first week of the month of November, 2023, at Sawla, Ethiopia; it
becomes information.
Database systems are designed to manage large data set in an organization. The data management involves both
definition and the manipulation of the data which ranges from simple representation of the data to
considerations of structures for the storage of information.

Compiled by: Kutale T. (MSc.) Page 1

Today, Databases are essential to every business. They are used to maintain internal records, to present data to
customers and clients on the World-Wide-Web, and to support many other commercial processes. Databases are
likewise found at the core of many modern organizations.
Examples of a database: Consider the UNIVERSITY database for maintaining information concerning
Students, Courses, and grades in a university environment. The database is organized as five files, each of
which stores data records of the same type.
 The STUDENT file stores data on each student,
 The COURSE file stores data on each course,
 The SECTION file stores data on each section of the course,
 The GRADE_REPORT file stores the grade that the students receive in the various sections they have
completed, and
 The PREREQUISITE file stores the prerequisite of each course.
1.2. Data Handling approaches
Data can be handled by three basic approaches/methods.
1. Manual Approach 3. Database Approach
2. Traditional File Based Approach
1) Manual Data Handling Approach
In the manual approach, data storage and retrieval follows the primitive and traditional way of information
handling where cards and paper are used for the purpose. The data storage and retrieval will be performed using
human labor. Files for as many event and objects as the organization has are used to store information. Each of
the files containing various kinds of information is labeled and stored in one or more cabinets. The cabinets
could be kept in safe places for security purpose based on the sensitivity of the information contained in it.
Insertion and retrieval is done by searching first for the right cabinet then for the right the file then the
information. One could have an indexing system to facilitate access to the data
Limitations of the Manual approach
 Prone to error  Limited to small size information
 Difficult to update, retrieve, integrate  Cross referencing is difficult
 You have the data but it is difficult to compile the information
2) File Based Data Handling Approach
After the introduction of Computer for data processing to the business community, the need to use the device
for data storage and processing increase. There were, and still are, several computer applications with file based
processing used for the purpose of data handling.

Compiled by: Kutale T. (MSc.) Page 2

 File based systems were an early attempt to computerize the manual filing system. This approach is the
decentralized computerized data handling method- it develops a program or a number of programs for each
different application. A collection of application programs performs services for the end-users. Since every
application defines and manages its own data, the system is subjected to serious data duplication problem.
In traditional file based approach:
 Records: Group of related fields, describes an entity (a person, place or thing about which information
must be kept - each characteristic of an entity is an attribute
 File: Group of records of the same type (a collection of records which contains logically related data).
 Database: Group of related files
Example: Three file processing systems at Company A
The systems illustrated are Order Filling, Invoicing, and Payroll. The figure also shows the major data files
associated with each application. A file is a collection of related records. For example, the Order Filling System
has three files: Customer Master, Inventory Master, and Back Order.
Notice that there is duplication of some of the files used by the three applications, which is typical of file
processing system.
Duplicated Data

Limitations of the Traditional File Based approach

 Data Redundancy (Duplication of data)
 Each program stores its own separate files. If the same data is to be accessed by different
programs, then each program must store its own copy of the same data i.e. same data is held by

Compiled by: Kutale T. (MSc.) Page 3

 different programs.
 Wasted space (Uncontrolled duplication of data)
 Separation and isolation of data
 Each program maintains its own set of data. Users of one program may be unaware of potentially
useful data held by other programs (available information in one application may not be known).
 Limited data sharing: With data stored in individual files, it can be difficult to share data between
different applications or departments, leading to inefficiencies information.
 Data Inconsistency and confusion: Data redundancy occurs when the same data is stored in multiple
files or locations, resulting in inconsistencies and errors.
 Data dependency on the application/ Program:
 File structure is defined in the program code and is dependent on the application programming
language. Each application program must have its own processing routines for reading, inserting,
updating and deleting data. Any change in data organization or format requires a change in all
the programs associated with those files.
 Incompatible file formats (Lack of Data Sharing and Availability)
 The structures of files are dependent on the application programming languages. The structure of
a file generated by a PYTHON program may be different from the structure of a file generated
by a 'C' program. The incompatibility of such files makes them difficult to process jointly.
 Poor Security and administration: File Processing Systems do not have built-in security features to
protect data from unauthorized access. Access to files is typically controlled through file permissions,
which can be difficult to manage and can lead to security breaches.
 Update Anomalies
 Modification Anomalies: A problem experienced when one or more data value is modified on
one application program but not on others containing the same data set.
 Deletion Anomalies: A problem encountered where one record set is deleted from one
application but remain untouched in other application programs
 Insertion Anomalies: A problem experienced whenever there is a new data item to be recorded,
and the recording is not made in all the applications

Compiled by: Kutale T. (MSc.) Page 4

3) Database Data Handling Approach
The Database approach was taken to overcome the limitations of the File-based systems.
In this approach:
 A database is maintained.
 A DBMS provides all the required services.
o A DBMS is a software system that enables users to define, create, maintain, and control access
to the database.
The database approach emphasizes the integration and sharing of data throughout the organization. Database is
a repository for collection of computerized data files. Since it is a shared corporate resource, the database is
integrated with minimum amount of duplication or no duplication.
Database is a collection of logically related data where these logically related data comprises entities, attributes,
relationships, and business rules of an organization's information.
The purpose of a database is to store information and to allow users to retrieve and update that information on
demand. Database is deigned once and used simultaneously by many users.
Unlike the traditional file based approach in database approach there is program data independence. That is the
separation of the data definition from the application. Thus the application is not affected by changes made in
the data structure and file organization. Each database application will perform the combination of: Creating
database, Reading, Updating and Deleting data.
Benefits of the database approach
 Data can be shared: two or more users can access and use same data instead of storing data in redundant
manner for each user.
 Improved accessibility of data: by using structured query languages, the users can easily access data
without programming experience.
 Redundancy can be reduced: isolated data is integrated in database to decrease the redundant data stored

Compiled by: Kutale T. (MSc.) Page 5

 at different applications.
 Quality data can be maintained: the different integrity constraints in the database approach will maintain
the quality leading to better decision making
 Inconsistency can be avoided: controlled data redundancy will avoid inconsistency of the data in the database.
 Integrity can be maintained: data at different applications will be integrated together with additional
constraints to facilitate shared data resource.
 Security majors can be enforced: the shared data can be secured by having different levels of clearance
and other data security mechanisms.
 Speed: data storage and retrieval is fast as it will be using the modern fast computer systems.
 Less labor: unlike the other data handling methods, data maintenance will not demand much resource.
 Centralized information control: since relevant data in the organization will be stored at one repository,
it can be controlled and managed at the central level.
 Data Independence - Applications insulated from how data is structured and stored

Limitations and risk of Database Approach

 Introduction of new professional and specialized personnel.
 Complexity in designing and managing data
 The cost and risk during conversion from the old to the new system
 High cost to be incurred to develop and maintain the system
 Complex backup and recovery services from the user’s perspective
 Reduced performance due to centralization and data independency
 High impact on the system when failure occurs to the central system.
1.3. Database Management System (DBMS)
 DBMS is a Software package used for providing efficient, convenient and safe multi-user (many
people/programs accessing same database, or even same data, simultaneously) storage of and access to
massive amounts of persistent (data outlives programs that operate on it) data.

Compiled by: Kutale T. (MSc.) Page 6

 It is software that enables users to define, create, maintain, and control access to the database.
 It provides a systematic method for creating, updating, storing, retrieving data in a database.
 It also provides the service of controlling data access, enforcing data integrity, managing concurrency
control, and recovery.
 DBMS is software designed to define, manipulate, retrieve, and manage data in the database. For example:
MySQL, Oracle, MSSQL Server SQLite, etc.
DBMS provides an interface to perform various operations like database creation, storing data in it, updating
data, creating a table in the database and a lot more. It provides protection and security to the database. In the
case of multiple users, it also maintains data consistency.
A. Components and Interfaces of DBMS
The DBMS is software package that helps to design, manage, and use data using the database approach. Taking
a DBMS as a system, one can describe it with respect to its environment or other systems interacting with the
DBMS. The DBMS environment has five components. To design and use a database, there will be the
interaction or integration of Hardware, Software, Data, Procedure and People.
1) Hardware: refers to the physical, electronic devices such as computers and hard disks that offer the
interface between computers and real-world systems. These components are comprised of various types of
personal computers, mainframe or any server computers to be used in multi-user system, network
infrastructure, and other peripherals required in the system.
2) Software: Software is a set of programs used to manage and control the database and includes the database
software, application programs (used to access the data), operating systems, network software (used to share
the data with other users), language software and other relevant software.
3) Data: since the goal of any database system is to have better control of the data and making data useful.
Data are raw facts and information that need to be organized and processed to make it more meaningful.
Data is the most important component to the user of the database. There are two categories of data in any
database system: that is Operational and Metadata.
i. Operational data is the data actually stored in the system to be used by the user.
ii. Metadata is the data that is used to store information about the database itself.
The structure of the data in the database is called the schema, which is composed of the Entities, Properties of
entities, and relationship between entities.
4) Procedure: this is the rules and regulations on how to design and use a database. It includes procedures like
how to setup and install, how to login to the DBMS, how to logout from the DBMS, generate reports, how
to start and stop transaction, how to manage the day-to-day operations, how to make backup, how to treat

Compiled by: Kutale T. (MSc.) Page 7

 hardware and software failure, how to change the structure of the database.
5) People: this component is composed of the people in the organization that are responsible or play a role in
designing, implementing, managing, administering and using the resources in the database. This component
includes group of people with high level of knowledge about the database and the design technology to
other with no knowledge of the system except using the data in the database. Database Access Users can
write commands using Database Access Language like, SQL before submitting them to the database for
execution. Through utilizing the language, users can create new databases, tables, insert data, and delete
data.
B. Functions of DBMS
A full scale DBMS should at least have the following services to provide to the user.
 Define a database: in terms of data types, structures and constraints
 Manipulate the database: querying, generating reports, insertions, deletions and modifications to its
content
 Enforce Security measures: to prevent unauthorized access
 Provide Data Independence - Applications insulated from how data is structured and stored
 Enable the user to access database catalog
 e.g. names, types, and sizes of data items, names of relationships
 Provide backup and recovery services: a mechanism for recovering the database after a failure must
be available.
 Backup is a replication of data that may be utilized in the case of a database failure.
 Recovery is the technique of restoring a database to its original condition after a failure.
 Enforce integrity constraints on the database
 Changes on data of DB should follow certain rules
 Provide transaction support
 An action, or series of actions, carried out by a single user or application program, which reads
or updates the contents of the database, should always transform the database from one
consistent state to another.
 Concurrency Control Services: access and update on the database by different users simultaneously
should be implemented correctly.
 Allow a set of concurrent users to retrieve and to update the database. Concurrency control
within the DBMS guarantees that each transaction is correctly executed or completely aborted.
OLTP (Online Transaction Processing) is a major part of database applications. OLTP enables

Compiled by: Kutale T. (MSc.) Page 8

 the real-time execution of large numbers of transactions by large numbers of people.
 Authorization Services (Security): must support the implementation of access and authorization
service to database administrator and users.
 Utility services: sets of utility service facilities like Importing data, Statistical analysis support, Index
reorganization, and Garbage collection.
1.4. Database Development Life Cycle (DDLC)
The DDLC is a process used in development to guide the creation of a database. Here is the steps to be followed
creating the database:
i. Planning: Planning is when an organization decides whether there is a need for a database, determines
the goals of the database, estimates the cost, debates feasibility etc. At the end of this step a mission
statement and mission objectives should be clearly defined for the database.
ii. Analysis: Analysis is also sometimes called “requirement gathering”. This step focuses on identifying
all the tasks the database will be performing, and identifying all the user use cases for the system. It
concentrates more on fact finding about the problem or the opportunity. Feasibility analysis, requirement
determination and structuring, and selection of best design method are also performed at this phase.
iii. Design: in database designing more emphasis is given to this phase. The phase is further divided into
three sub-phases.
a. Conceptual Design: In this step the requirement specifications are translated into a conceptual
model of the database. The data is categorized into graphical representation of the entities needed
and the relationship and dependencies between them. It is a concise description of the data, data
type, relationship between data and constraints on the data. There is no implementation or physical
detail consideration. Although the model represents the entire database it’s not very detailed. This
model should be high level and easy to understand. The goal here is to visualize the entities, their
fields, and clarify the connection between them.
b. Logical Design: is a higher level conceptual abstraction with selected specific data model to implement
the data structure. It is particular DBMS independent and with no other physical considerations.
Significantly more detail about each entity and the system as a whole is required in this step. You do
not need to worry about how they will be physically implemented. Instead, focus on:
 specifying all attributes for each entity listed in the conceptual model
 specifying primary keys, foreign keys, clustering columns and query lines (for NoSQL DBs)
 normalizing the database
 applying integrity constraints

Compiled by: Kutale T. (MSc.) Page 9

 c. Physical Design: It is a physical implementation of the upper level design of the database with
respect to internal storage and file structure of the database for the selected DBMS. You should
focus on:
 converting your entities into tables, nodes or documents
 assigning data types
 following name conventionality to your specific database system
 optimizing and validating the design before implementation
 Defining the indexes and the integrity constraints (rules)
iv. Implementation and Data Loading: Once you are sure your database design is solid and ready to be
implemented you can install your database management system (DBMS), create your database, load
data into the system (if you have any), integrate the database with another application, etc.
v. Testing: The goal is to make sure that everything works as expected. Testing can be done automatically
or manually, and it’s a good idea to test in multiple environments for better quality assurance.
vi. Deployment and Maintenance: Once everything is confirmed to be working, deployment of the
designed database for use is the next step. And - unless for some reason the database was created for a
one-time use - there will be a need to maintain the database. There will be changes, bug fixes, etc.
vii. Operation and Support: administering and maintaining the operation of the database system and
providing support to users.
1.5. Roles of people in a Database Environment
As people are one of the components in DBMS environment, there are group of roles played by different
stakeholders in designing and operating the database system.
A. Database Administrator (DBA)
 DBA is responsible to oversee, control and manage the database resources (the database itself is the
primary resource, the DBMS and other related software is secondary resource)
 Authorizing access to the database
 Coordinating and monitoring the use of the database
 Responsible for determining and acquiring hardware and software resources
 Responsible for managing the databases, including database security, access control, backup, and disaster
recovery i.e. accountable for problems like poor security (security breaches), poor performance of the
system (poor system response time).
 Involves in all steps of database development
We can have further classifications of this role in big organizations having huge amount of data and user

Compiled by: Kutale T. (MSc.) Page 10

requirement.
i. Data Administrator (DA): is responsible on management of data resources. Involves in database planning,
development, maintenance of standards policies and procedures at the conceptual and logical design phases.
ii. Database Administrator (DBA): is more technically oriented role. Responsible for the physical realization
(physical design and implementation) of databases, security and integrity control of the database.
B. Database Designer (DBD)
 Database designers are responsible for identifying the data to be stored in the database and for choosing
appropriate structures to represent and store this data. These tasks are mostly undertaken before the
database is actually implemented and populated with data.
 It is the responsibility of database designers to communicate with all prospective database users in order to
understand their requirements and to create a design that meets these requirements. In many cases, the
designers are on the staff of the DBA and may be assigned other staff responsibilities after the database
design is completed.
 Database designers typically interact with each potential group of users and develop views of the database
that meet the data and processing requirements of these groups. Each view is then analyzed and integrated
with the views of other user groups. The final database design must be capable of supporting the
requirements of all user groups.
In large database design projects, we can distinguish between two types of designer: logical and physical
database designers.
1. Logical DBD
The logical database designer is concerned with identifying the data (that is, the entities and attributes), the
relationships between the data, and the constraints on the data that is to be stored in the database.
The logical database designer must have a thorough and complete understanding of the organization‘s data and
any constraints on this data (the constraints are sometimes called business rules).
Examples of constraints:
 A member of staff cannot manage more than 100 properties for rent or sale at the same time;
 A member of staff cannot handle the sale or rent of his or her own property;
2. Physical DBD
The physical database designer decides how the logical database design is to be physically realized.

 Mapping the logical database design into a set of tables and integrity constraints.
 Selecting specific storage structures and access methods for the data to achieve good performance.
 Designing any security measures.

Compiled by: Kutale T. (MSc.) Page 11

 The physical database designer must be fully aware of the functionality of the target DBMS and must
understand the advantages and disadvantages of each alternative implementation.
C. Application Programmer
System analyst determines the user requirement and how the user wants to view the database. Determines the
interface on how to retrieve, insert, update and delete data in the database.
The application programmer implements these specifications as programs; code, test, debug, document and
maintain the application program. The application could use any high level programming language according to
the availability, the facility and the required service.
Once the database has been implemented, the application programs that provide the required functionality for
the end-users must be implemented. This is the responsibility of the application developers.
They are the developers who interact with the database by means of DML queries. These DML queries are
written in the application programs like C, C++, JAVA, Pascal etc.
D. End Users
End users are the people whose jobs require access to the database for querying, updating, and generating
reports; the database primarily exists for their use. There are several categories of end users:
i. Naïve Users:
 They are unsophisticated users and unaware of the DBMS and the database
 Thy depend on the simplicity of the graphical user interface (GUI)
 They are using the application programs like desktop, mobile, web through the interface
 E.g.: Bank customers and tellers check account balances and post withdrawals and deposits.
ii. Sophisticated Users:
 They are familiar with the structure of the database and the facilities of the DBMS
 They may use high-level query languages (SQL) to perform the required operation
 They know how to interact with the database system using database query languages
 Ex: DBA
iii. Specialized Users: Specialized users who write specialized database applications that do not fit into the
fractional database processing framework.
1.6. Database Architecture
 The major aim of database is to provide users with an abstract view of data, hiding certain details of how
data is stored and manipulated. Since database is a shared resources each users may require a different
view of the data. To satisfy these needs, the architecture of most commercial DBMS available today is
based on the so-called ANSI-SPARC Architecture (America National Standard Institution Standard

Compiled by: Kutale T. (MSc.) Page 12

 Planning and Requirement committee). ANSI-SPARC introduced the three- level architecture of the
database based on their degree of abstraction.
The purpose and origin of the Three-Level database architecture
 All users should be able to access same data. This is important since the database is having a shared data
feature where all the data is stored in one location and all users will have their own customized way of
interacting with the data.
 A user’s view is unaffected or immune to changes made in other views. Since the requirement of one user
is independent of the other, a change made in one user’s view should not affect other users.
 Users should not need to know physical database storage details. As there are naïve users of the system,
hardware level or physical details should be a black-box for such users.
 DBA should be able to change database storage structures without affecting the users’ views. A change in
file organization, access method should not affect the structure of the data which in turn will have no effect
on the users.
 Internal structure of database should be unaffected by changes to physical aspects of storage, such as
change of hard disk.
 DBA should be able to change conceptual structure of database without affecting all users. In any database
system, the DBA will have the privilege to change the structure of the database, like adding tables, adding
and deleting an attribute, changing the specification of the objects in the database.
1.6.1. Levels of Abstraction - (three levels ANSI_SPARC architecture)
We have three distinct levels of data abstraction at which data items can be described. The levels form a three
level architecture comprising an external, conceptual and internal level.

Compiled by: Kutale T. (MSc.) Page 13

The objective of the three-level architecture is to separate each users' view of the database from the way it is
physically represented.
1. External level
 This is a highest level of abstraction as seen by user.
 It is the users' view of the database.
 This level of abstraction describes only the part of entire database that is relevant to a particular user.
 Different users have their own customized view of the database independent of other users.
o For example, one user may view dates in the form (day, month, year), while another may view
dates as (year, month, day), some views may include derived or calculated data. That is, data not
actually stored in the database,
2. Conceptual level:
 It is the community view of the database.
 At this level of database abstraction, all the database entities and the relationships among them are
included.
 This level describes what data is stored in the database and the relationships among the data. The data is
actually stored using the data structures and file.
 The middle level in the three-level architecture is the conceptual level. This level contains the logical
structure of the entire database as seen by the DBA.
 It is a complete view of the data requirements of the organization that is independent of any storage
considerations.
 It supports each external view, in that any data available to a user must be contained in or derivable
from conceptual level.
 The conceptual level represents all entities, their attributes, and their relationships; the constraints on
the data; semantic information about the data; Security and integrity information.
3. Internal level:
 This is a lowest level of data abstraction. It is closest to physical storage device used.
 The way the DBMS and the operating system perceive the data is the internal level
 The internal level is the physical representation of the database on the computer.
 This level describes how the data is stored in the database and describes the data structures and access
methods to be used by the database.
 The internal level covers the physical implementation of the database to achieve optimal runtime
performance and storage space utilization.

Compiled by: Kutale T. (MSc.) Page 14

  It covers the data structures and file organizations used to store data on storage devices.
 It interfaces with the operating system access methods (file management techniques for storing and retrieving
data records) to place the data on the storage devices, build the indexes, retrieve the data, and so on.
 The internal level is concerned with storage space allocation for data and indexes; record description for
storage(with stored sizes for data items); record placement, data compression and data encryption
techniques
 It is expressed by the INTERNAL SCHEMA
 Internal schema contains the definition of the stored data (record), the method of representing the
data fields (expresses the internal view) and the access aids used

The following example can be taken as an illustration for the difference between the 3 levels in the ANSI-
SPARK database Architecture. Where:
 The first level is concerned about the group of users and their respective data requirements
independent of the other.
 The second level is describing the whole content of the database where one pieces of information
will be
 The third level is describing how data is physically stored in the database.

Compiled by: Kutale T. (MSc.) Page 15

3.6.2. DBMS schemas at three levels
A database schema is the skeleton structure that represents the logical view of the entire database. It defines
how the data is organized and how the relations among them are associated. It formulates all the constraints that
are to be applied on the data.
A database schema defines its entities and the relationship among them. It contains a descriptive detail of the
database, which can be depicted by means of schema diagrams. It’s the database designers who design the
schema to help programmers understand the database and make it useful.
DBMS schemas at three levels are:
a) Internal schema at the internal level to describe physical storage structures and access paths. Typically
uses a physical data model. It ontrols how the data is stored on physical media, such as magnetic disks.
b) Conceptual schema at the conceptual level to describe the structure and constraints for the whole
database for a community of users. Uses a conceptual or an implementation data model.
c) External schemas at the external level to describe the various user views. Usually uses the same data
model as the conceptual level. Application programs interact with the external database schema, which
has an interface, or mapping, to the conceptual schema.

Compiled by: Kutale T. (MSc.) Page 16

3.6.3. Data Independence
The three-schema architecture can be used to further explain the concept of data independence , which can be
defined as the capacity to change the schema at one level of a database system without having to change the
schema at the next higher level.
Application programs interact with the external database schema, which has an interface, or mapping, to the
conceptual schema. The conceptual schema is concerned with the identity and relationships between elements
of data of interest to an organization, and has an interface or mapping to the internal schema. The internal
schema controls how the data is stored on physical media, such as magnetic disks.
In a database environment, if there is a requirement to change the structure of a particular file of data held on
disk, this will be recorded in the internal schema. The interface between the internal schema and the conceptual
schema will be amended to reflect this, but there will be no need to change the external schema. This means
that any such change of physical data storage is transparent to users and application programs. This approach
removes the problem of physical data dependence.
In a similar manner, any changes to the conceptual schema, can be isolated from the external schema and the
internal schema; such changes will be reflected in the interface between the conceptual schema and the other
levels. This achieves logical data independence. What this means effectively, is that changes can be made at
the conceptual level, where the overall model of an organizations data is specified, and these changes can be
made independently of both the physical storage level, and the external level seen by individual users. The
changes are handled by the interfaces between the conceptual, middle layer, and the physical and external
layers.
Mappings among schema levels are needed to transform requests and data. Programs refer to an external
schema, and are mapped by the DBMS to the internal schema for execution. When a schema at a lower level is
changed, only the mappings between this schema and higher-level schemas need to be changed in a DBMS that
fully supports data independence. The higher-level schemas theselves are unchanged. Hence, the application
programs need not be changed since they refer to the external scemas.
The ability to modify a scheme definition in one level without affecting a scheme definition in a higher level is
called data independence.
A major objective for three-level architecture is to provide data independence, which means that upper levels
are unaffected by changes in lower levels. We can define two types of data independence:
1) Physical data independence
2) Logical data independence

Compiled by: Kutale T. (MSc.) Page 17

Logical data independence
 Refers to immunity of external schemas to changes in conceptual schema.
 Indicates that the conceptual schema changes e.g. addition/removal of entities should not require
changes to external schema or rewrites of application programs.
 The change would be absorbed by the mapping between the external and conceptual levels.
 Insulates application programs from operations such as combining two records into one or splitting an
existing record into two or more records.
 This would require a change in the external/conceptual mapping so as to leave the external view
unchanged.
Physical data independence
 Refers to immunity of conceptual schema to changes in the internal schema.
 Internal (physical) schema changes e.g. using different file organizations, storage structures/devices
should not require change to conceptual schemas i.e. the physical storage structures or devices could be
changed without affecting conceptual schema.
 The change would be absorbed by the mapping between the conceptual and internal levels.
 It is achieved by the presence of the internal level of the database and then, transformation from the
conceptual level of the database to the internal level.
 Conceptual level to internal level mapping, therefore provides a means to go from the conceptual view
(conceptual records) to the internal view and hence to the stored data in the database (physical records).

Compiled by: Kutale T. (MSc.) Page 18