SQL
MySQL Index Tutorial - Create, Add & Drop
What is an Index? Indexes in MySQL sort data in an organized sequential way. They are created on...
Data Independence in DBMS: Physical & Logical with Examples
- Details
What is Data Independence of DBMS?
Data Independence is defined as a property of DBMS that helps you to change the Database schema at one level of a database system without requiring to change the schema at the next higher level. Data independence helps you to keep data separated from all programs that make use of it.
You can use this stored data for computing and presentation. In many systems, data independence is an essential function for components of the system.
In this tutorial, you will learn:
- What is Data Independence of DBMS?
- Types of Data Independence
- Levels of Database
- Physical Data Independence
- Logical Data Independence
- Difference between Physical and Logical Data Independence
- Importance of Data Independence
Types of Data Independence
In DBMS there are two types of data independence
- Physical data independence
- Logical data independence.
Levels of Database
Before we learn Data Independence, a refresher on Database Levels is important. The database has 3 levels as shown in the diagram below
- Physical/Internal
- Conceptual
- External
Consider an Example of a University Database. At the different levels this is how the implementation will look like:
| Type of Schema | Implementation |
| External Schema | View 1: Course info(cid:int,cname:string) View 2: studeninfo(id:int. name:string) |
| Conceptual Shema | Students(id: int, name: string, login: string, age: integer) Courses(id: int, cname.string, credits:integer) Enrolled(id: int, grade:string) |
| Physical Schema |
|
Physical Data Independence
Physical data independence helps you to separate conceptual levels from the internal/physical levels. It allows you to provide a logical description of the database without the need to specify physical structures. Compared to Logical Independence, it is easy to achieve physical data independence.
With Physical independence, you can easily change the physical storage structures or devices with an effect on the conceptual schema. Any change done would be absorbed by the mapping between the conceptual and internal levels. Physical data independence is achieved by the presence of the internal level of the database and then the transformation from the conceptual level of the database to the internal level.
Examples of changes under Physical Data Independence
Due to Physical independence, any of the below change will not affect the conceptual layer.
- Using a new storage device like Hard Drive or Magnetic Tapes
- Modifying the file organization technique in the Database
- Switching to different data structures.
- Changing the access method.
- Modifying indexes.
- Changes to compression techniques or hashing algorithms.
- Change of Location of Database from say C drive to D Drive
Logical Data Independence
Logical Data Independence is the ability to change the conceptual scheme without changing
- External views
- External API or programs
Any change made will be absorbed by the mapping between external and conceptual levels.
When compared to Physical Data independence, it is challenging to achieve logical data independence.
Examples of changes under Logical Data Independence
Due to Logical independence, any of the below change will not affect the external layer.
- Add/Modify/Delete a new attribute, entity or relationship is possible without a rewrite of existing application programs
- Merging two records into one
- Breaking an existing record into two or more records
Difference between Physical and Logical Data Independence
| Logica Data Independence | Physical Data Independence |
| Logical Data Independence is mainly concerned with the structure or changing the data definition. | Mainly concerned with the storage of the data. |
| It is difficult as the retrieving of data is mainly dependent on the logical structure of data. | It is easy to retrieve. |
| Compared to Logic Physical independence it is difficult to achieve logical data independence. | Compared to Logical Independence it is easy to achieve physical data independence. |
| You need to make changes in the Application program if new fields are added or deleted from the database. | A change in the physical level usually does not need change at the Application program level. |
| Modification at the logical levels is significant whenever the logical structures of the database are changed. | Modifications made at the internal levels may or may not be needed to improve the performance of the structure. |
| Concerned with conceptual schema | Concerned with internal schema |
| Example: Add/Modify/Delete a new attribute | Example: change in compression techniques, hashing algorithms, storage devices, etc |
Importance of Data Independence
- Helps you to improve the quality of the data
- Database system maintenance becomes affordable
- Enforcement of standards and improvement in database security
- You don't need to alter data structure in application programs
- Permit developers to focus on the general structure of the Database rather than worrying about the internal implementation
- It allows you to improve state which is undamaged or undivided
- Database incongruity is vastly reduced.
- Easily make modifications in the physical level is needed to improve the performance of the system.
Summary
- Data Independence is the property of DBMS that helps you to change the Database schema at one level of a database system without requiring to change the schema at the next higher level.
- Two levels of data independence are 1) Physical and 2) Logical
- Physical data independence helps you to separate conceptual levels from the internal/physical levels
- Logical Data Independence is the ability to change the conceptual scheme without changing
- When compared to Physical Data independence, it is challenging to achieve logical data independence
- Data Independence Helps you to improve the quality of the data
