What is RDBMS?
To discuss the Relational Database Management System, there are some terms and concepts you need to know.
Database
Any collection of related information is called a database. The phone book is also a database.
Database Management System
Software programmed to enable the user to create and maintain a database. We can implement create, read, update, and delete operations on the information stored in a database using DBMS.
Types of Databases
There are primarily two types of databases based on the structure in which the information is stored.
- Relational Database
- Non-Relational Database
Relational Database: The data is stored in one or more tables. Each table has rows and columns. A unique key identifies each row. Structured Query Language (SQL) handles the Relational Database and its information.
Non-Relational Database: Data is stored in anything but not in traditional tables.
Structured Query Language
SQL is the standardized language used to interact with RDBMS. It is used to implement CRUD operations. It is used to define tables and structures and manipulate the data stored in the database.
Note: SQL performed for a relational database is not necessarily portable and may require certain modifications.
Difference between RDBMS and DBMS
| Characteristics | RDBMS | DBMS |
| Users | Multiple users can access the data using RDBMS at the same time. | One user can access the data at a time. |
| Requirements | High-performance software and hardware | No such requirement |
| Data | Can handle a very large amount of data | Can handle comparatively less amount of data |
| Data definition | Data is stored in a hierarchical format | Data is stored in the form of tables |
| ACID properties | Maintains the atomicity, consistency, isolation and durability of the data | Does not necessarily maintains these properties |
| Normalization | It is possible to normalize the data | We cannot normalize the data stored in the DBMS |
| Distributed Database | It supports distributed database. | It does not support distributed database. |
| Recovery Mechanism | It has a backup and recovery mechanism | No such recovery or backup mechanism |
ACID properties in relational database management systems
From what we have discussed earlier, we know that RDBMS maintains the atomicity, consistency, isolation, and durability of its data. Now let us discuss all these four data characteristics stored in RDBMS one by one.
The relational database management system describes these four properties to maintain the quality of data stored.
- Atomicity: It specifies that whatever may be the operation that is being performed on the database either will be fully executed on the database or will not be executed at all.
- Consistency: It specifies whenever the change is made on the database data, that change should be preserved, which means when an operation is successfully implemented on the database, that change should be committed in the memory too.
- Isolation: It is the property that allows concurrent access to data in the RDBMS. It specifies that change in one data should not affect the value of any other data in the relational database.
- Durability: It ensures permanency. This property ensures that the result obtained after the successful execution of the operation is written in the memory. A database management system that cannot store any change in the information will be of no use.
Working on Relational Database Management System
As discussed earlier, relational database stores the data in the tables, and those tables are linked to each other by various links. Each table has a key that can be used to uniquely identify all the table's entities. This unique key is assigned as the primary key of the particular table. The table consists of rows and columns where rows are the horizontal entities are store specific information and columns are vertical entities that specify all types of information about a particular field. Certain constraints on these tables must be considered while constructing and using the tables.
The constraints are as follows:
- Domain constraint – This constraint is similar to defining a data type for a variable in the programming languages, the difference is that this data type applies to all the values that can be entered in the field for which this constraint is applied. Datatypes can be strings, a limited range of integers or dates, Boolean etc.
- Entity Integrity Constraint – This constraint is only applicable to the primary key, whenever a key is defined as the primary key of the table; the user can no longer a null value in that field.
- Referential Integrity Constraint – This constraint allows the user to increase the complexity of the schema, it is crucial to normalise the data and make the structure of the RDBMS more realistic. It acts as a medium of communication between different tables in the schema. It requires a foreign key that can either have a value of a Primary key of another table or a NULL value.
- Key Constraint- Each relationship should have an attribute having unique values for all the entities present in the relationship. It is necessary to have a primary key for the table in the RDBMS.
- Not null- This constraint is very useful when applied on a necessary field, the user is not allowed to enter NULL as the value for the column. It will accept NULL as the value of that attribute and will display an error.
Components of Relational Database Management System
Table
It represents a relationship in the relational database management system. It is the data object that allows the user to store data. It is the most important component of the RDBMS that stores other data objects inside it. Many tables associated together form the structural schema of the RDBMS. A particular table can contain all the related information about an entity. It is the simplest structure to store data in the relational database management system.
For example, let's look at this employee table that contains all the information about the employees in an organization.
| EMPLOYEE_ID | Name | AGE | SALARY |
| 1 | John | 25 | 20000 |
| 2 | James | 25 | 20000 |
| 3 | Alice | 27 | 15000 |
| 4 | Robert | 32 | 40000 |
| 5 | David | 31 | NULL |
Field
It is a component of the table; in layman’s terms, the table's column is called a field of the table. It is an entity of the table that stores the values for a specific entity's attribute. In the above employee table, there are four fields. They are EMPLOYEE_ID, Name, Age, and Salary. By referring to an employee's field, we can get the corresponding value of that particular attribute.
Record
It is responsible for holding all the attribute values of a particular entity in the table. It is the horizontal component of the table. In layman’s terms, it is the row of the table. By referring to the particular row, one can find all the related information about a particular entity stored in the relational database management system.
Example: Below is an example of a record from the above Employee table.
| 1 | John | 25 | 20000 |
Null Values
When the user does not want to enter any value in the table, the relational database management system provides an option to the user to store that value as NULL. It does not mean 0 or the field contains space. It is used to show a lack of knowledge about the field value.
Example, In the above Employee table, the salary of David is stored as NULL; storing 0 would have caused confusion, and it would have provided the wrong information. The NULL does not have any data type of its own and can be used for any attribute.
| 5 | David | 31 | NULL |