SQL RDBMS Concept – Features & Advantages
In this tutorial, we will be focusing on RDBMS concepts.
RDBMS stands for Relational Database Management System.
RDBMS concepts are a basic requirement to understand SQL and all other modern database examples: MS Access, SQL, MS SQL Server, etc.
It is a database management and organization system based on relational models and their principles. Let us now dive into the RDBMS concepts and master them with this tutorial.
Let us first have a look at the database we will be using for this tutorial. We are using the DataFlair database.
SELECT * FROM DataFlair;
Here we can view the contents of the DataFlair database.
Stay updated with latest technology trends
Join DataFlair on Telegram!!
Components of RDBMS
The major components of an RDBMS are as follows:
1. RDBMS Table
A table is a collection of records or tuples containing the values associated with the columns or attributes available in a table.
SELECT * FROM DataFlair;
In the above example, we can see a table with various records or tuples.
2. RDBMS Tuple(A record)
In the database, we have data put in various tuples. These tuples are what we know as rows under the attributes. The tuple, row, and record terms are put interchangeably here.
Example: Let us have a look at a single tuple in our DataFlair database.
SELECT * FROM DataFlair LIMIT 1;
Here we can see what a single tuple or row represents in a database.
3. RDBMS Column(Attribute)
Attributes are commonly known as column names and are used to map data to the correct name.
Example: Let us have a look at the name column in our Dataflair database.
SELECT name FROM DataFlair ;
Here we can see what a column is in a database.
We can also view multiple columns by specifying multiple column names in the query.
4. RDBMS Domain
The domain of a database is the permissible values that a database can store.
Example: When we talk about age, the age can’t be negative thus, the domain is greater than 0. Let us view the same.
SELECT age FROM DataFlair;
Here we can see the age column in the DataFlair database and we can observe that the domain of the column is only positive values.
5. RDBMS Schema
Schema is the composition of a table. In simple words, the columns and the type of data they store is the schema of the database.
Example: Let us view the schema of our DataFlair database.
Here we can see the composition or the schema of our DataFlair database.
6. RDBMS Constraints
These are the rules which we want to impose on our data when it is put and maintained in our database.
By the use of constraints, we maintain the integrity and accuracy of our data. We could have column level or table level constraints depending on our use case.
Let us first understand the concept of NULL values and then we will discuss constraints further:
RDBMS NULL values:
When some data is missing or not available at the moment, we use a placeholder for such data in the database.
These placeholders are null values. We can reassign the null values at any time and prevent errors from creeping in due to the missing data.
Example: Let us check if we have an employee with the location not available or null in our DataFlair database.
SELECT * FROM DataFlair where location = null;
Here we can see the details of an employee whose location is not specified i.e. NULL.
Some of the major constraints we use are as follows:
|1||NOT NULL||Prevents the column from taking NULL values.|
|2||DEFAULT||Sets a default value for the column.|
|3||UNIQUE||Prevents duplicate values in the column.|
|4||PRIMARY KEY||Helps to identify each record uniquely.|
|5||FOREIGN KEY||Helps to identify each record in another database uniquely.|
|6||CHECK||Applies certain conditions on the column data.|
|7||INDEX||Helps in creating and retrieving records fast.|
Example: Let us view the Primary Key in our DataFlair database.
Here we can see in the columns that emp_id is the Primary key of our DataFlair database.
The major categories of Data Integrity in an RDBMS are as follows:
- Entity Integrity: According to this principle, we can’t have any duplicate tuples in a table.
- Domain Integrity: According to this principle, we make sure to store data in the correct format, type, and range in any column specified.
- Referential Integrity: According to this principle, we can’t delete records that are required to access other records.
- User-Defined Integrity: According to this rule, the rules specified by the user while creating the database are always applied and checked before data is put in the database.
Null values are used when we want to substitute the missing information.
The null value is ignored by some SQL functions to work logically correctly. Null value comes in handy during analytics of large chunks of data which has missing or ambiguous data.
Database normalization is the process of filtering and organizing data in a database. We need to do normalization of a database for the following reasons:
- Helps in Eliminating redundant data, thus allowing effective use of available memory.
- Helps us to make sure that the data dependencies are logically correct.
There are four major normal forms which we need to know:
- 1NF or First Normal Form: When a database is in the First normal form, we ensure that there are no multi-value records in the database. Each record holds a single value corresponding to every attribute.
- 2NF or Second Normal Form: When a database is in Second normal form state, it should meet all the rules for 1NF, and partial dependency of any of the columns on the primary key is not permissible.
- 3NF of Third Normal Form: When a database is in third normal form, the following conditions should be met-
The database should be in second normal form.
All the non-primary attributes should be dependent on the primary key.
- BCNF or Boyce Codd Normal Form: When a database is in BCNF form the database needs to be in 3NF normal form. In addition to this, we need to check the transitive dependency as well. There should be no transitive dependency if the database is in BCNF.
Advantages of RDBMS
Some of the advantages of using RDBMS are as follows:
- It helps in maintaining the integrity of data.
- RDBMS maintains the relationships in the data and makes them easily accessible.
- With the help of RDBMS, we can apply various checks on our data using constraints.
- RDBMS allows us to access data through each available component.
In this article, we have understood the RDBMS concepts. We have learnt about the components in an RDBMS database and all the points that we need to take care of.
We have also taken enough knowledge on data integrity and the normalization of the database.
Hope you liked the article. Do share feedback in the comment section.