A relational database is a storage mechanism based on the relational model of Computer Scientist Edgar Codd. It stores, retrieves and allows functional implementation of data in a fashion that they are interrelated. Data is stored as tables in a relational database.
Tables: Rows and Columns
Tables are a combination of rows and columns where data is represented following a relational model and can store thousands or sometimes, millions of data.
Data is stored as an instance in a row called a record. Each Row is unique and has a unique identifier called the key.
Each column has a descriptive name called attributes and contains data of a certain datatype.
In the above example, there are three rows or records.
Each record has a unique identifier called ID and each column stores a different datatype.
The first and third column stores integer, the second column stores string, and the fourth column stores date.
What is SQL?
SQL or Structured Query Language is a programming language used to communicate with a relational database. Furthermore, SQL syntax is similar to the English language making it easy to learn.
SQL has several benefits which make all the major RDBMS choose it as their database language. With SQL users mainly perform CRUD operations like:
- Access data in their RDBMS
- Describe, define, and manipulate data
- Use SQL modules, libraries, and pre-compilers to embed it into other programming languages
- Make and delete databases and tables
- Set permissions on tables, processes, and views
SQL commands are categorized into three groups:
Data Definition Language (DDL)
- CREATE
- ALTER
- DROP
Data Manipulation Language (DML)
- SELECT
- INSERT
- UPDATE
- DELETE
Data Control Language (DCL)
- GRANT
- REVOKE
What is RDBMS?
A relational database management system (RDBMS) is the software that is used to record, manipulate, and retrieve data securely and reliably from connected data points.
Advanced use of RDBMS
The basic function of a RDBMS is the CRUD (Create, Read, Update, Delete) function. Besides, there is also some advance usage of RDBMS:
Object Storage
An object requires to be flattened before storing in a relational database as a relational database stores data only as tables. Consequently, object schema needs to be correctly mapped with relational database schema to avoid Object Relational Impedance Mismatch.
Implementing Behaviour within the Database
In a relational database, behavior is implemented via stored procedures and/or functions that can be called both internally by the database and by external applications. The difference between stored functions and procedures that run within an RDBMS lies in what the operation can return and if it can be invoked in a query.
Concurrency Control
Concurrency Control is a collision resolution strategy when a concurrent query is being executed on the same record. For example, when two people try to book the same airline ticket the first one should be allocated the choice of his seat and the second one rejected.
Transaction Control
Transactions are a collection of actions on a database like create, read, update records- which form a work unit. The transaction may be flat ie, they are rolled back if each and every action is not successfully completed or, they may be nested. In nested transactions, some of the sub-transaction can’t be rolled back.
Enforcing Referential Integrity
Referential integrity is the maintenance of synchronicity between various data. If one data references another data then the second data must exist. If data is deleted then all the references to it also must be deleted to prevent false references.
Relational vs Non-Relational Database
A relational database is maintained through a relationship between two tables using a primary key/foreign key pair. The connection between the foreign and primary keys maintains a relationship throughout the entire database.
In a non-relational database, there are no tables, rows, or keys, instead a storage model suitable to the specific data requirement is used.
Data in a relational database is consistent in input and meaning whereas data in a non-relational database allows variants of input making it very dynamic.
Examples of Popular RDBMS
Examples of NoSQL Databases
Conclusion
Data is stored in tables in relational databases. The number of columns and records in a table can increase dramatically. SQL (and its variations) is used by relational database management systems (RDBMSs) to handle the data in these enormous tables. Therefore, your choice of RDBMS will depend on how complex your application is.
