What is DBMS?
DBMS, or Data Base Management System, is a file-handling program that is used to create, manage, or handle database systems efficiently. DBMS makes it easier to define, design, manipulate, or share databases with different users. It can perform various operations such as insert, update or delete, etc. A database can be considered as a group of similar data.
A database is of two types: Structured Database and Unstructured Database. A structured database has a proper structure or format to store data and maintain a relationship between them. For example, storing data in the form of tables (Excel Files). An unstructured database does not have a proper format, and the data is stored randomly-for example, a webpage.
Some examples of DBMS are SQL, MYSQL, Oracle, etc.
Databases are of two types- relational databases and non-relational databases.
What is RDBMS?
A Relational Database Management System or RDBMS is a type of database system that is based upon a model called relational model. RDBMS is used to store, manipulate and handle structured databases that forms relation with each other through tables. A table has rows that are also called Tuples or Records and columns that are called Attributes or Fields. RDBMS is the most popular Database Management System because of its easy implementation and its ability to store and retrieve a large amount of data.
Example and Terminologies of an RDBMS
Let's take an example where we store data of an employee in a table where the columns include employee name, employee age, employee id, and employee salary.
Here, the data of employees are organized in a tabular format with rows and columns, and thus it can be considered a relational database. Here, each row is termed as Tuple or Record. Each tuple should be unique. Each column is called an Attribute. An attribute should have a specific name. In the above table, Employee Name, Employee age, Employee id, and Employee salary are the attributes. A Primary Key is an attribute that is unique to each row. In the above table, Employee name, age, or salary can be similar for some employees, but Employee id will be unique for every employee; hence Employee id is the primary key here. The cell where an attribute and a tuple intersect each other is known as Data Cell. The total count of attributes in a table is called as the Degree. Here, the Degree of RDBMS is four because we have four attributes, which are employee name, age, id, and salary, whereas the number of tuples or rows in a table is termed as Cardinality. Here, the Cardinality is three because we have three rows in the table. A total of achievable values of a particular attribute is termed as the Domain. Let's consider the age of employees, ranging from maybe 25 to 60 years. Thus the domain of Employee age will be (25, 60). If no value is inserted in a data cell, the value is considered a NULL value, and a NULL value is different from zero.
The Relational Model of RDBMS
It is a model which says that any table is related to another table through a common attribute. Tables in an RDBMS have an attribute or a primary key that is identified uniquely in a row, and these rows are used to create a relationship between other tables using a foreign key.
Some most common RDBMS include MYSQL, Oracle, and MariaDB database.
Primary Key: Primary key is the unique key used to find the relationship or similarity in the table. Each table has a unique primary key that other tables cannot share.
Foreign Key: Foreign key is used for a different table referred by a primary key. There can be more than one foreign key in a table, which can also be shared with other tables.
Advantages of RDBMS
- Data Organization: RDBMS provides a better way of organization of data as it shows a clear relation among them with the help of rows and columns.
- Better Safety Measures: Databases are safer and more secure because RDBMS provides role-based security, user-level permissions are required for access control, and communication is encrypted.
- Avoids Redundancy: Data is stored only once; hence it avoids redundancy, and there is no need for multiple changes.
- Easy to Use: The formation of relationships among the database helps the users to easily understand and use it.
- Multiple Users: RDBMS permits numerous users simultaneously.
- SQL: It supports SQL language.
- Fast: RDBMS supports storing and retrieving a large amount of data in less amount of time.
- Tolerance: Helps the system to recover lost data in case of a sudden shutdown or power failure.
Disadvantage of RDBMS
- Large amount of costs and setups are needed to make these systems function properly.
- Additional cost as well as power is required.
- Complexity of the System; large amount of data creates complexity in understanding relations and thus lowers performance.
- Limitations; the fields or columns of an RDBMS are stored within various limits.
Characteristics of RDBMS
- Data is stored in rows and columns in tabular format where each row is called a tuple or record, and each column is called an attribute or field.
- Each row is unique, and no two records can be the same.
- Use of Foreign Keys to relate tables with each other.
- Any missing value in a table is considered a NULL value, and a NULL value is different from zero.
- RDBMS supports Normalization; it is the procedure of managing and organizing database efficiently to avoid data redundancy and thus helps in maintaining and storing of data logically. Normalization is divided into various normal forms. There are three types of Normal Forms and they are: First Normal Form (1NF), Second Normal Form (2NF) and Third Normal Form (3NF).
- RDBMS supports distributed database. Distributed Database is a database that stores data across multiple computers instead of storing it on one system.
RDBMS as a Distributed System
- Entity Integrity - Entity Integrity means that there cannot be duplicate rows in a relation or table.
- Domain Integrity - Domain Integrity refers to restricting the type, format, and the range of values.
- Referential integrity - It means that the rows which are used by other different rows cannot be deleted.
- User-Defined Integrity - It consists of some specific rules that are not the part of above three rules.
What is the Difference between DBMS and RDBMS?
|DBMS implies for Database Management System.
||RDBMS implies for Relational Database Management System.
|A File-System method is used to store the data.
||Data is stored in tabular format that consists of rows and columns.
|Data is represented and accessed in hierarchical form.
||Tables are used to represent and access the data.
|No relationship between the data is represented in DBMS.
||Relationship between the data is presented through tables.
|Normalization is not supported in DBMS.
||Normalization can be applied in RDBMS.
|It is limited to handling small amount of data.
||It can handle large and complex set of data.
|It supports distributed database.
||It does not support distributed database.
|Only single user at a time can access the data.
||Multiple users are allowed to access data at a time.
|Example: File System
||Example: SQL, MySQL, Oracle, etc.
Relational Operations in RDBMS
Relational Operations in RDBMS are operations performed on one or more than one tables to form another table. Union, Intersection and Difference are few basic relational operations.
- Union: Union operation (UNION) is used to combine two different tables or relations into a single table or relation. The new table contains all the elements of both the tables. Union operation removes all the duplicates and shows only unique values. If you want to show duplicate values also, then UNION ALL is used.
- Intersection: Intersection operation (INTERSECT) displays only the common values from the two tables. The resultant table has only common values from the two relations. Intersection also removes all the duplicate values.
- Difference: Difference operation (MINUS) displays the values that are present in first relation and not in second relation.
12 Rules by Codd's
In 1970, Edgar F. Codd gave twelve rules for a database to be considered a true relational database. The rules are as follows-
- RULE 0: The Foundation Rule- To consider any database as a Relational DBMS, it must be able to manipulate, handle and implement the database within its relational capabilities.
- RULE 1: The Information Rule- A database to be considered an RDBMS, the data in it must be stored in a tabular format having rows and columns.
- RULE 2: The Guaranteed Access Rule- the Guaranteed Access Rule states that it is possible to access every value in the database with the help of a row value (primary key), a column value (attribute), and a table name.
- RULE 3: The Systematic Treatment of NULL Values- This rule states that missing or empty values in the table are represented as NULL values, and a NULL value differs from a zero.
- RULE 4: Active Online Catalog Based on the Relational Model- The structured representation of the entire dataset must be stored in a data dictionary so that it can easily be accessed by allowed users. The authorized users are allowed to apply the same relational query language as they use to access the database itself.
- RULE 5: The Comprehensive Data Sub-Language Rule- An RDBMS can be accessed with the help of a language having well-defined syntax and which can support operations such as data definition, data manipulation, authorization, and integrity constraints.
- RULE 6: The View Updating Rule- All the views of a database that can be theoretically updated should be able to be updated in the database by the system.
- RULE 7: The High-Level Insert, Update, and Delete Rule-a DBMS is considered as a Relational DBMS if it has the capability to perform High-Level operations such as insertion, deletion and update. It should also support set relational operations such as union, intersection, and minus operations.
- RULE 8: The Physical Data Independence Rule: Application programs should remain unaffected when any changes are made to the physical structure of the database. Thus the database should be independent of the applications that can access it.
- RULE 9: The Logical Data Independence- Applications using the database should not be affected by any logical changes made to the data.
- RULE 10: The Integrity Independence Rule- the Integrity Independence Rule states that the database should not be dependent on the applications using it.
- RULE 11: The Distribution Independence Rule- The users should not be able to witness that the data is distributed over more than one networks.
- RULE 12: The Non-Subversion Rule- If a system has a low-level language, it cannot be used to bring down the integrity rules stated in high-level relational language.
Examples of RDBMS
- MySQL: MySQL is the most popular open-source relational database management system that is used for the purpose of the development of web-based applications. It uses the most common and well-known language, SQL, and thus it is an easy-to-use RDBMS. It is quick-to-use and can handle complex datasets efficiently.
- Microsoft SQL Server: Developed by Microsoft, Microsoft SQL Server is a relational database management system that can perform various basic functions, such as retrieving and storing data. It is used for multiple transaction processing, business analytics applications, etc.
- Oracle: Developed by Oracle Corporation, Oracle is a relational database whose primary functions are the retrieval and storage of large and complex data. It is one of the most popular RDBMS used for storing and handling large data.
- PostgreSQL: PostgreSQL is free-to-use, open-source relational database management system that is considered the most secure and accurate RDBMS, with an experience of over 15 years. It is supported by all the important operating systems and consists of all the major programming interfaces such as Java, C, C++, Python, Ruby, Perl, etc. It supports all the major standards of SQL and includes various new features.
Other examples of RDBMS include SQLite, MariaDB, IBM Informix, Amazon RDS, H2 Database Engine, Sybase ASE, Apache Derby, etc.