ACID Properties in DBMS

DBMS is the management of data that should remain integrated when any changes are done in it. It is because if the integrity of the data is affected, the whole data will get disturbed and corrupted. Therefore, to maintain the integrity of the data, there are four properties described in the database management system, which are known as the ACID properties. The ACID properties are meant for a transaction that goes through a different group of tasks, and there we come to see the role of the ACID properties.

In this section, we will learn and understand the ACID properties. We will learn what these properties stand for and what does each property is used for. We will also understand the ACID properties with the help of some examples.

ACID Properties

The expansion of the term ACID defines:

1) Atomicity: The term atomicity defines that the data remains atomic. It means if any operation is performed on the data, either it should be performed or executed completely or should not be executed at all. It further means that the operation should not break in between or execute partially. In the case of executing operations on the transaction, the operation should be completely executed and not partially.

Example: If Remo has account A having $30 in his account from which he wishes to send $10 to Sheero's account, which is B. In account B, a sum of $ 100 is already present. When $10 will be transferred to account B, the sum will become $110. Now, there will be two operations that will take place. One is that the amount of $10 that Remo wants to transfer will be debited from his account A, and the same amount will get credited to account B, i.e., into Sheero's account. Now, what happens - the first operation of debit executes successfully, but the credit operation, however, fails. Thus, in Remo's account A, the value becomes $20, and in that of Sheero's account, it remains $100 as it was previously present.

In the above diagram, it can be seen that after crediting $10, the amount is still $100 in account B. So, it is not an atomic transaction.

The below image shows that both debit and credit operations are done successfully. Thus the transaction is atomic.

Thus, when the amount loses atomicity, then in the bank systems, this becomes a huge issue, and so the atomicity is the main focus in the bank systems.

2) Consistency: The word consistency means that the value should remain preserved always. In DBMS, the integrity of the data should be maintained, which means if a change in the database is made, it should remain preserved always. In the case of transactions, the integrity of the data is very essential so that the database remains consistent before and after the transaction. The data should always be correct.

Example:

In the above figure, there are three accounts, A, B, and C, where A is making a transaction T one by one to both B & C. Two operations take place, i.e., Debit and Credit. Account A firstly debits $50 to account B, and the amount in account A is read as $300 by B before the transaction. After the successful transaction T, the available amount in B becomes $150. Now, A debits $20 to account C, and at that time, the value read by C is $250 (that is correct as a debit of $50 has been successfully done to B). The debit and credit operation from account A to C has been done successfully. We can see that the transaction is done successfully, and the value is also read correctly. Thus, the data is consistent. In case the value read by B and C is $300, which means that data is inconsistent because when the debit operation executes, it will not be consistent.

4) Isolation: The term 'isolation' means separation. In DBMS, Isolation is the property of a database where no data should affect the other one and may occur concurrently. In short, the operation on one database should begin when the operation on the first database gets complete. It means if two operations are being performed on two different databases, they may not affect the value of one another. In the case of transactions, when two or more transactions occur simultaneously, the consistency should remain maintained. Any changes that occur in any particular transaction will not be seen by other transactions until the change is not committed in the memory.

Example: If two operations are concurrently running on two different accounts, then the value of both accounts should not get affected. The value should remain persistent. As you can see in the below diagram, account A is making T1 and T2 transactions to accounts B and C, but both are executing independently without affecting each other. It is known as Isolation.

4) Durability: Durability ensures the permanency of something. In DBMS, the term durability ensures that the data after the successful execution of the operation becomes permanent in the database. The durability of the data should be so perfect that even if the system fails or leads to a crash, the database still survives. However, it gets lost, and it becomes the responsibility of the recovery manager for ensuring the durability of the database. For committing the values, the COMMIT command must be used every time we make changes.

Therefore, the ACID property of DBMS plays a vital role in maintaining the consistency and availability of data in the database.

Thus, it was a precise introduction to ACID properties in DBMS. We have discussed these properties in the transaction section also.

Read More

 

Database Language

• A DBMS has appropriate languages and interfaces to express database queries and updates.
• Database languages can be used to read, store and update the data in the database.

Types of Database Language

1. Data Definition Language

• DDL stands for Data Definition Language. It is used to define database structure or pattern.
• It is used to create schema, tables, indexes, constraints, etc. in the database.
• Using the DDL statements, you can create the skeleton of the database.
• Data definition language is used to store the information of metadata like the number of tables and schemas, their names, indexes, columns in each table, constraints, etc.

Here are some tasks that come under DDL:

• Create: It is used to create objects in the database.
• Alter: It is used to alter the structure of the database.
• Drop: It is used to delete objects from the database.
• Truncate: It is used to remove all records from a table.
• Rename: It is used to rename an object.
• Comment: It is used to comment on the data dictionary.

These commands are used to update the database schema that's why they come under Data definition language.

2. Data Manipulation Language

DML stands for Data Manipulation Language. It is used for accessing and manipulating data in a database. It handles user requests.

Here are some tasks that come under DML:

• Select: It is used to retrieve data from a database.
• Insert: It is used to insert data into a table.
• Update: It is used to update existing data within a table.
• Delete: It is used to delete all records from a table.
• Merge: It performs UPSERT operation, i.e., insert or update operations.
• Call: It is used to call a structured query language or a Java subprogram.
• Explain Plan: It has the parameter of explaining data.
• Lock Table: It controls concurrency.

3. Data Control Language

• DCL stands for Data Control Language. It is used to retrieve the stored or saved data.
• The DCL execution is transactional. It also has rollback parameters.

(But in the Oracle database, the execution of data control language does not have the feature of rolling back.)

Here are some tasks that come under DCL:

• Grant: It is used to give user access privileges to a database.
• Revoke: It is used to take back permissions from the user.

There are the following operations that have the authorization of Revoke:

CONNECT, INSERT, USAGE, EXECUTE, DELETE, UPDATE and SELECT.

4. Transaction Control Language

TCL is used to run the changes made by the DML statement. TCL can be grouped into logical transaction.

Here are some tasks that come under TCL:

• Commit: It is used to save the transaction on the database.
• Rollback: It is used to restore the database to transactions original since the last Commit.

Read More

 

Data Independence

• Data independence can be explained using the three-schema architecture.
• Data independence refers characteristic of being ability to modify the schema at one level of the database system without altering the schema at the next higher level.

There are two types of data independence:

1. Logical Data Independence

• Logical data independence refers characteristic of being able to change the conceptual schema without having to change the external schema.
• Logical data independence is used to separate the external level from the conceptual view.
• If we do any changes in the conceptual view of the data, then the user view of the data would not be affected.
• Logical data independence occurs at the user interface level.

2. Physical Data Independence

• Physical data independence can be defined as the capacity to change the internal schema without having to change the conceptual schema.
• If we do any changes in the storage size of the database system server, then the Conceptual structure of the database will not be affected.
• Physical data independence is used to separate conceptual levels from the internal levels.
• Physical data independence occurs at the logical interface level.

Fig: Data Independence

Read More

 

Data model Schema and Instance

• The data which is stored in the database at a particular moment of time is called an instance of the database.
• The overall design of a database is called schema.
• A database schema is the skeleton structure of the database. It represents the logical view of the entire database.
• A schema contains schema objects like table, foreign key, primary key, views, columns, data types, stored procedure, etc.
• A database schema can be represented by using the visuala diagram. That diagram shows the database objects and their relationship with each other.
• A database schema is designed by the database designers to help programmers whose software will interact with the database. The process of database creation is called data modeling.

A schema diagram can display only some aspects of a schema like the name of record type, data type, and constraints. Other aspects can't be specified through the schema diagram. For example, the given figure neither shows the data type of each data item nor the relationship among various files.

In the database, actual data changes quite frequently. For example, in the given figure, the database changes whenever we add a new grade or add a student. The data at a particular moment is called the instance of the database.

Read More

 

Data Models

Data Model is the modeling of the data description, data semantics, and consistency constraints of the data. It provides the conceptual tools for describing the design of a database at each level of data abstraction. Therefore, there are the following four data models used for understanding the structure of the database:

1) Relational Data Model: This type of model designs the data in the form of rows and columns within a table. Thus, a relational model uses tables for representing data and in-between relationships. Tables are also called relations. This model was initially described by Edgar F. Codd, in 1969. The relational data model is the widely used model which is primarily used by commercial data processing applications.

2) Entity-Relationship Data Model: An ER model is the logical representation of data as objects and relationships among them. These objects are known as entities, and a relationship is an association among these entities. This model was designed by Peter Chen and published in 1976 papers. It was widely used in database design. A set of attributes describe the entities. For example, student_name, and student_id describe the 'student' entity. A set of the same type of entities is known as an 'Entity set', and the set of the same type of relationships is known as a 'relationship set'.

3) Object-based Data Model: An extension of the ER model with notions of functions, encapsulation, and object identity, as well. This model supports a rich type system that includes structured and collection types. Thus, in the 1980s, various database systems following the object-oriented approach were developed. Here, the objects are nothing but data-carrying properties.

4) Semistructured Data Model: This type of data model is different from the other three data models (explained above). The semistructured data model allows the data specifications at places where the individual data items of the same type may have different attributes sets. The Extensible Markup Language, also known as XML, is widely used for representing semistructured data. Although XML was initially designed for including the markup information in the text document, it gains importance because of its application in the exchange of data.

Read More

 

Three schema Architecture

• The three schema architecture is also called ANSI/SPARC architecture or three-level architecture.
• This framework is used to describe the structure of a specific database system.
• The three schema architecture is also used to separate the user applications and physical database.
• The three schema architecture contains three-levels. It breaks the database down into three different categories.

The three-schema architecture is as follows:

In the above diagram:

• It shows the DBMS architecture.
• Mapping is used to transform the request and response between various database levels of architecture.
• Mapping is not good for small DBMS because it takes more time.
• In External / Conceptual mapping, it is necessary to transform the request from the external level to the conceptual schema.
• In Conceptual / Internal mapping, DBMS transforms the request from the conceptual to the internal level.

Objectives of Three schema Architecture

The main objective of the three-level architecture is to enable multiple users to access the same data with a personalized view while storing the underlying data only once. Thus it separates the user's view from the physical structure of the database. This separation is desirable for the following reasons:

• Different users need different views of the same data.
• The approach in which a particular user needs to see the data may change over time.
• The users of the database should not worry about the physical implementation and internal workings of the database such as data compression and encryption techniques, hashing, optimization of the internal structures, etc.
• All users should be able to access the same data according to their requirements.
• DBA should be able to change the conceptual structure of the database without affecting the user's
• The internal structure of the database should be unaffected by changes to physical aspects of the storage.

1. Internal Level

• The internal level has an internal schema that describes the physical storage structure of the database.
• The internal schema is also known as a physical schema.
• It uses the physical data model. It is used to define how the data will be stored in a block.
• The physical level is used to describe complex low-level data structures in detail.

The internal level is generally is concerned with the following activities:

• Storage space allocations.three levels
  For Example B-Trees, Hashing, etc.
• Access paths.
  For Example Specification of primary and secondary keys, indexes, pointers, and sequencing.
• Data compression and encryption techniques.
• Optimization of internal structures.
• Representation of stored fields.

2. Conceptual Level

• The conceptual schema describes the design of a database at the conceptual level. The conceptual level is also known as the logical level.
• The conceptual schema describes the structure of the whole database.
• The conceptual level describes what data are to be stored in the database and also describes what relationship exists among those data.
• At the conceptual level, internal details such as an implementation of the data structure are hidden.
• Programmers and database administrators work at this level.

3. External Level

• At the external level, a database contains several schemas that are sometimes called subschema. The subschema is used to describe the different views of the database.
• An external schema is also known as a view schema.
• Each view schema describes the database part that which a particular user group is interested and hides the remaining database from that user group.
• The view schema describes the end-user interaction with database systems.

Mapping between Views

The three levels of DBMS architecture don't exist independently of each other. There must be a correspondence between the three levels i.e. how they actually correspond with each other. DBMS is responsible for correspondence between the three types of schema. This correspondence is called Mapping.

There are basically two types of mapping in the database architecture:

• Conceptual/ Internal Mapping
• External / Conceptual Mapping

Conceptual/ Internal Mapping

The Conceptual/ Internal Mapping lies between the conceptual level and the internal level. Its role is to define the correspondence between the records and fields of the conceptual level and files and data structures of the internal level.

External/ Conceptual Mapping

The external/Conceptual Mapping lies between the external level and the Conceptual level. Its role is to define the correspondence between a particular external and conceptual view.

Read More

 

DBMS Architecture

• The DBMS design depends upon its architecture. The basic client/server architecture is used to deal with a large number of PCs, web servers, database servers, and other components that are connected with networks.
• The client/server architecture consists of many PCs and a workstation that are connected via the network.
• DBMS architecture depends upon how users are connected to the database to get their requests done.

Types of DBMS Architecture

Database architecture can be seen as a single-tier or multi-tier. But logically, database architecture is of two types: 2-tier architecture and 3-tier architecture.

1-Tier Architecture

• In this architecture, the database is directly available to the user. It means the user can directly sit on the DBMS and uses it.
• Any changes done here will directly be done on the database itself. It doesn't provide a handy tool for end-users.
• The 1-Tier architecture is used for the development of the local application, where programmers can directly communicate with the database for a quick response.

2-Tier Architecture

• The 2-Tier architecture is the same as the basic client-server. In the two-tier architecture, applications on the client end can directly communicate with the database on the server-side. For this interaction, APIs like ODBC, and JDBC are used.
• The user interfaces and application programs are run on the client-side.
• The server side is responsible to provide the functionalities like query processing and transaction management.
• To communicate with the DBMS, the client-side application establishes a connection with the server-side.

Fig: 2-tier Architecture

3-Tier Architecture

• The 3-Tier architecture contains another layer between the client and server. In this architecture, the client can't directly communicate with the server.
• The application on the client-end interacts with an application server which further communicates with the database system.
• The end user has no idea about the existence of the database beyond the application server. The database also has no idea about any other user beyond the application.
• The 3-Tier architecture is used in the case of the large web applications.

Fig: 3-tier Architecture

Read More

 

DBMS vs. File System

File System Approach

File-based systems were an early attempt to computerize the manual system. It is also called a traditional-based approach in which a decentralized approach was taken where each department stored and controlled its own data with the help of a data processing specialist. The main role of a data processing specialist was to create the necessary computer file structures, and also manage the data within structures and design some application programs that create reports based on file data.

In the above figure:

Consider an example of a student's file system. The student file will contain information regarding the student (i.e. roll no, student name, course, etc.). Similarly, we have a subject file that contains information about the subject and the result file which contains the information regarding the result.

Some fields are duplicated in more than one file, which leads to data redundancy. So to overcome this problem, we need to create a centralized system, i.e. DBMS approach.

DBMS:

A database approach is a well-organized collection of data that are related in a meaningful way that can be accessed by different users but stored only once in a system. The various operations performed by the DBMS system are Insertion, deletion, selection, sorting, etc.

In the above figure,

In the above figure, duplication of data is reduced due to the centralization of data.

There are the following differences between DBMS and File systems:

Basis	DBMS Approach	File System Approach
Meaning	DBMS is a collection of data. In DBMS, the user is not required to write the procedures.	The file system is a collection of data. In this system, the user has to write the procedures for managing the database.
Sharing of data due	Due to the centralized approach, data sharing is easy.	Data is distributed in many files, and it may be of different formats, so it isn't easy to share data.
Data Abstraction	DBMS gives an abstract view of data that hides the details.	The file system provides the detail of the data representation and storage of data.
Security and Protection	DBMS provides a good protection mechanism.	It isn't easy to protect a file under the file system.
Recovery Mechanism	DBMS provides a crash recovery mechanism, i.e., DBMS protects the user from system failure.	The file system doesn't have a crash mechanism, i.e., if the system crashes while entering some data, then the content of the file will be lost.
Manipulation Techniques	DBMS contains a wide variety of sophisticated techniques to store and retrieve the data.	The file system can't efficiently store and retrieve the data.
Concurrency Problems	DBMS takes care of Concurrent access of data using some form of locking.	In the File system, concurrent access has many problems like redirecting the file while deleting some information or updating some information.
Where to use	Database approach used in large systems which interrelate many files.	File system approach used in large systems which interrelate many files.
Cost	The database system is expensive to design.	The file system approach is cheaper to design.
Data Redundancy and Inconsistency	Due to the centralization of the database, the problems of data redundancy and inconsistency are controlled.	In this, the files and application programs are created by different programmers so that there exists a lot of duplication of data which may lead to inconsistency.
Structure	The database structure is complex to design.	The file system approach has a simple structure.
Data Independence	In this system, Data Independence exists, and it can be of two types. Logical Data Independence Physical Data Independence	In the File system approach, there exists no Data Independence.
Integrity Constraints	Integrity Constraints are easy to apply.	Integrity Constraints are difficult to implement in file system.
Data Models	In the database approach, 3 types of data models exist: Hierarchal data models Network data models Relational data models	In the file system approach, there is no concept of data models exists.
Flexibility	Changes are often a necessity to the content of the data stored in any system, and these changes are more easily with a database approach.	The flexibility of the system is less as compared to the DBMS approach.
Examples	Oracle, SQL Server, Sybase etc.	Cobol, C++ etc.

Read More

 

Difference between DBMS and RDBMS

Although DBMS and RDBMS both are used to store information in the physical database there are some remarkable differences between them.

The main differences between DBMS and RDBMS are given below:

No.	DBMS	RDBMS
1)	DBMS applications store data as files.	RDBMS applications store data in a tabular form.
2)	In DBMS, data is generally stored in either a hierarchical form or a navigational form.	In RDBMS, the tables have an identifier called the primary key and the data values are stored in the form of tables.
3)	Normalization is not present in DBMS.	Normalization is present in RDBMS.
4)	DBMS does not apply any security with regards to data manipulation.	RDBMS defines the integrity constraint for the purpose of ACID (Atomocity, Consistency, Isolation and Durability) property.
5)	DBMS uses file system to store data, so there will be no relation between the tables.	in RDBMS, data values are stored in the form of tables, so a relationship between these data values will be stored in the form of a table as well.
6)	DBMS has to provide some uniform methods to access the stored information.	RDBMS system supports a tabular structure of the data and a relationship between them to access the stored information.
7)	DBMS does not support distributed database.	RDBMS supports distributed database.
8)	DBMS is meant to be for small organization and deal with small data. it supports single user.	RDBMS is designed to handle large amount of data. it supports multiple users.
9)	Examples of DBMS are file systems, xml etc.	Example of RDBMS are mysql, postgre, sql server, oracle etc.

After observing the differences between DBMS and RDBMS, you can say that RDBMS is an extension of DBMS. There are many software products in the market today who are compatible for both DBMS and RDBMS. Means today a RDBMS application is DBMS application and vice-versa.

Read More