Relational databases & SQL | Model, How They Work, Benefits

Table of Contents hide

1 SQL

2 Relational Database

3 How do relational databases work?

4 Relational Database Model

5 What are the benefits of Relational Databases?

6 Stored Procedures

7 Locking and concurrency in relational databases

8 Choosing a Relational database for your system

9 Learning SQL

10 SQL Commands

In this article, we will discuss what is SQL? And what part is it playing in shaping business in today’s world? In order to understand this first, we need to understand what is the need for SQL and how it works. On what principles does it work and what is its history?

SQL

SQL is an acronym for Structured Query Language and it is a programming language that is used to work with databases. Most commonly SQL is used to work with relational databases. A relational database is a type of database in which the data is stored in the form of tables. These tables are called relations. In these tables, there is a set of rows and columns. In columns, there are different characteristics on which the data is categorized. In rows, the data is stored and the rows are called table records. SQL is used to access the data in these tables, query it, modify it, delete it, or use it for our own analysis.

In order to understand how SQL works first, we have to understand the median on which it is used. SQL is mainly used on relational databases. So before understanding the working of SQL we will first have to understand how relational databases work.

Relational Database

A relational database can be considered as a table in which the categories of data are stored in columns and the data is stored in rows. This makes it easier to depict the data and data handling is made easier when it is arranged in tables. A relational database is based on a relational model which is a pretty simple model in which the data is stored in the form of tables consisting of rows and columns. The columns in this table consist of the attributes or characteristics of data on which the data is categorized. The rows consist of records that exist corresponding to a unique identification number which is called a key. Following is an example of a relational database. Although it is a quite small table as shown the columns consist of attributes of data and the rows consist of data stored corresponding to these attributes.

Figure 1 Example of a table in Relational Database

How do relational databases work?

There are two types of database structures. One is logical database structure and the other one is physical database structure. The logical database structure is related to how an application or a user can specify what data it needs whereas physical database structure is related to how the data specified by the user or application can be accessed and how will this task be carried out using what resources.

The logical databases structure consists of the following elements:

Tables that consists of data i.e. data tables
Views
Indexes

The physical database structure consists of the following elements:

Data files i.e. A physical file that is stored on disk
Temp file i.e. contains temporary tablespace
Control file i.e. file that tracks the physical components of the database
Online redo log files i.e. these files contain information about the changes made to the stored data

One of the biggest advantages of having relational databases is that they consist of two types of database structure and making changes or managing one database structure does not affect the data stored in the other database structure i.e. making changes or managing physical database structure does not affect the access to data in the logical database structure.

Another plus point of relational databases is that they have a certain set of defined integrity rules which ensure that the data is not corrupted or contains deformities. For example one of the integrity rules is that the tables do not allow the storage of duplicate values. The benefit of this rule is that it helps save up storage spaces and also saves the database from the risk of storing huge amounts of duplicate data.

Relational Database Model

During the time when the concept of the database was new, everyone had their own structure or format in which they stored data. Every application has its own way of storing data in its own self-defined data structure. When a user or a developer had to find certain data from a database they had to first find out about the structure of that database. They had to first familiarize themselves with the structure and access the data. This whole process was very time consuming and ineffective as the data required should be extracted quickly. In order to overcome this drawback of databases, relational databases were introduced. The relational database model was introduced so that the data is stored in a readable format and can be accessed quickly when needed.

The developers figured that relational databases were the best option because they stored data in the form of tables. Using tables to store data is a very effective way to store data as it makes it easier to access data, modify data, store data, and recover it in the time of need.

Another benefit of using a relational database was that the developers started using Structured Query Language (SQL) to query data in databases. SQL is a very powerful programming language that is used to query the data present in databases. SQL is a great fit for relational databases because their relational algebra and mathematical languages match each other. SQL makes it very easy to query data in relational databases and play with data present in these databases. If we want to access the data in relational databases without using SQL we have to define a query for every individual task. This will be discussed in detail later on in the article.

What are the benefits of Relational Databases?

Relational database model is a very powerful model and the characteristic that makes it so powerful is its flexibility. It can be used by almost any organization or business and can help store a large amount of data that can consist of various sorts and types of information. A relational database can be used in various scenarios for example it can be used to track transactions of money, help store information about inventories of items in a supermarket business, manage huge amounts of data that is related to customers such as in the billing department of telecommunication firms or any organization that has to deal with billing, etc. Relational databases can be used for businesses in which the data is in relation to one another and where there is a need to manage data in a safe way and the data is also consistent. The three most important benefits of relational databases are as follows:

Data Consistency: The most reliable and important feature of relational databases is the consistency of data that these databases offer. It is best at maintaining the consistency of data across different platforms and applications. Database copies are known as instances and relational databases are very good at maintaining these instances. Banking is an example of data consistency offered by relational databases. In the case of ATMs when a customer withdraws money from his account he expects to see that transaction is made successfully and their accounts are updated according to this transaction. This is made possible by the data consistency offered by the relational databases as the data is updated instantly in the data instance when the transaction is made. Relational databases are the best at offering this kind of data consistency and ensure that the databases and the instances all have the same data in it. In other types of databases it is difficult to maintain this level of data consistency. Maintaining this type of data consistency for massive amounts of data is a challenge for all the other types of databases. For example in the case of a newly introduced programming language NoSQL it is difficult to maintain consistency of data and they need some time to process data if changes are made to it. The type of data consistency which they offer is known as eventual consistency. Eventual consistency means that when data is updated, changed, or when many users are accessing the database at the same time the database and the data in it needs some time to catch up with the changes being made. This extra time required by the data to catch up to the change is known as catch up time. Meaning that they cannot offer real-time consistency. This is fine for some businesses where the delay is acceptable but in businesses where real-time update is required such as updating the shopping carts in online websites this fails and the relational databases are required to do the tasks.
Commitment: This feature is equally important to the data consistency feature offered by relational databases. By commitment it means that the relational databases offer the customer the ability to set policy and rules for the data that is stored in these databases. Let’s explain this with an example. Suppose that it is the requirement of a business that if three components are not present in the machine then the machine will not be registered in the database. So if a machine has two parts or just one part it will not be stored in the database as it does not meet the requirement set by the business owners. A relational database will not enter or save any data in the database as long as the data does not meet the policy or set of rules defined in the database.
Atomicity: It can be defined as the commitment capability of the database. It is responsible to ensure that the data stored in the databases is according to the policies set by the business owners. The data stored must be according to the policies defined by the organization using relational databases.

Stored Procedures

When the databases were newly introduced in the market accessing data was quite a difficult task as the users had to use quite a lot of commands to get information from a data table present in the database. The developers at that time had to write a code to access the data and this was a stressful task as the code was to be repeated quite a lot of times to get the desired results. Relational databases helped overcome this problem too. In relational databases, accessing data is not such a strenuous task as these databases consist of blocks of codes that can be accessed when the data is to be queried. Now instead of writing long lines of code for simple query tasks, the users can just access these blocks of codes and they can query whatever they want in the database. These blocks of codes are known as stored procedures and have made life much easier for developers as well as users. Another benefit of these stored procedures is that they eliminate the risk of fault as the predefined blocks of codes are run and things are done in a manner that is right.

Locking and concurrency in relational databases

An issue that arises frequently is that when the users or customers are trying to change the same data at the same time. Relational databases have two features that they offer to overcome this issue. These features are as follows:

Locking: This feature locks the data table present in the database and the user that is updating it is the only one that can access it at that time. In some databases the locking feature is applied to the whole database which is not an ideal practice as it affects the service provided to the customers. Some other databases perform locking at record level which means that the data a certain record of data is locked and the rest of it can be used by other customers or users at that time. This technique is more favorable as it does not halt the services for other customers or users.
Concurrency: When more than one customer is trying to activate a query at the same time for the same database then concurrency comes into play. It makes sure that the right person is given the user rights according to the policies defined by the business owners.

Choosing a Relational database for your system

Every database has its own management systems which can be defined as software that converts the high-level input language into a low-level language that is easily understood by the machines. This software helps users in querying databases and performing tasks such as managing data in the database, retrieving the data stored in the database, and storing new data. These systems also manage the performance of the databases as well as handle the policies of access rights.

While choosing a database for your business there are a few things one should consider. The management systems that you choose should comply with your business requirements and goals. The following things should be considered while choosing a database for your system:

Data accuracy: It varies from business to business. It is more related to what sort of data is to be stored with how much accuracy.
Scalability: This factor is a very important factor as the aim of business is to expand and grow and these databases should be easily scalable to cope with the change in amounts of data but then again it depends on the requirement of the business. Retail businesses when starting out might not need to store huge amounts of data but as they grow they need to store more amount of data in their databases hence the databases that they are choosing should be scalable.
Concurrency: It depends on the factor that how many users or customers are accessing your database at a single time. In the case of Banks this factor is very important as multiple customers are accessing the database at a single time.
Performance and reliability: These factors come into play when the business owners need to decide whether they want their databases to offer high performance, high reliability or both. Every business has its own KPIs and SLAs and the performance and reliability of the database should be able to meet these KPIs and SLAs.

Now that we have a very good understanding of the systems on which SQL works. Let’s study how we can use SQL to access and query data on these databases. We will discuss some basic commands of SQL and how they help in querying data.

Learning SQL

As we all know by now SQL is a language of relational databases. Each command of SQL can be defined as a set of instructions. These instructions are used to communicate with the database and query the data present in the database. These instructions tell the database what exactly is needed and what steps the database has to follow to get complete the command given to it by the user. We can perform various tasks using SQL such as creating a table in a database, adding data to these tables or modifying the existing data present in the tables, deleting the table of data present in it, and also giving permissions to the users.

SQL Commands

SQL consists of five basic commands which are as follows:

Relational databases and SQL: Click to learn more about Relational databases and SQL.

Figure 2 SQL Basic commands

1. Data Definition Language (DDL): These commands are used when the user needs to change the table structure i.e. creating tables in the database, changing the existing tables present in the database, deleting tables present in the database etc. The changes made by these commands are permanently saved in the database. Some of the most common commands that fall under this category are:

CREATE: This command is used to create a new table in the existing database. The syntax of this command is:

CREATE TABLE Name_of_table (Name_of_Column Datatype, Name_of_Column Database_type,….)

Name_of_table : Insert the name of the table you want to create.

Name_of_Column: Insert the name of the columns you want in the table

Database_type: The type of data that is to be inserted i.e. Integers, characters, etc.

Example:

CREATE TABLE EMPLOYEE(Name VARCHAR2(20), Email VARCHAR2(100), DOB DATE);

In this example, the name of the table is employee, the name of columns are Name, Email, and DOB. The Data Type is VARCHAR where the digits in the round bracket show the maximum number of characters that can be inserted in the column.

DROP: This command is used to delete the table present in a database and can also be used to delete the structure of the database. The syntax of the command is:

DROP TABLE Name_of_Table;

Name_of_Table: The name of the table is to be inserted here

Example:

DROP TABLE NAME;

This command will delete the table named NAME from the database.

ALTER: This command can be used to alter the structure of the existing database. This includes two types of commands:

I. This command is used to adding a new column in the table.

ALTER TABLE Table_name ADD(Column_name Datatype)

Table_name: Name of the table in which the column is to be added

Column_name: Name of the column that is to be added

Datatype: The attributes of the column such as the datatype

Example:

ALTER TABLE STUDENT_DETAILS ADD(EMAIL VARCHAR1(30));

This command will add a new column in the table STUDENT_DETAILS. The name of the newly added column is EMAIL and the data type is VARCHAR1(20) in which the limit of a number of characters is 30.

II. This command is used to alter the existing column present in the table

ALTER TABLE Table_name MODIFY(Column_name Datatype);

Table_name: Name of the table which contains the column that is to altered

Column_name: Name of the column that is to be altered

Datatype: Type of data

ALTER TABLE STUDENT_DETAILS MODIFY (EMAIL VARCHAR1(30));

This command will alter the table column EMAIL present in the table STUDENT_DETAILS. The data type is VARCHAR1 and the maximum number of characters allowed is 30.

TRUNCATE: This command is used to remove all the rows present in the table. This helps free space in the table. The syntax of the command is:

TRUNCATE TABLE Name_of_table;

Name_of_table: The name of the table that is to be truncated.

Example:

TRUNCATE TABLE STUDENT_DETAILS;

This command will remove all the rows in the STUDENT_DETAILS table.

2. Data Control Language (DCL): This command is used to handle the rights of the users. The two types of commands that fall under this category are:

GRANT: This command is used to grant rights to the user. The syntax of the command is:

GRANT Name_of_privilage ON Name_of_object TO {Username | PUBLIC | role_name } [with GRANT option];

Name_of_privilage: The right that is to be assigned to the user

Name_of_object: The name of the object i.e. table, view etc.

Username: Name of the user that is to be assigned the right

With Grant option: The user is allowed to assign rights to other users.

Example:

GRANT SELECT ON student TO ABC

This command allows the user ABC to use SELECT permission on the student table.

REVOKE: This command takes back permissions from the users.

REVOKE Name_of_privilage ON Name_of_object FROM {Username | PUBLIC | Role_name]

Name_of_privilage: The right that is to be assigned to the user

Name_of_object: The name of the object i.e. table, view etc.

Username: Name of the user that is to be assigned the right

Example:

REVOKE SELECT ON student FROM ABC

This command permits the user ABC to use SELECT privilege on the student table.

3. Data Manipulation Language (DML): This command helps modify the database. These commands are not permanently saved on the database and can be reverted if needed. Some of the commands are as follows:

INSERT: Used to insert data in the rows of the table. The syntax of the command is:

INSERT INTO Nane_of_table VALUES (v1, v2, v3, …. vN);

Name_of_table: The name of the table in which the data is inserted.

V1,v2,v3, … vN: The values to be inserted.

Example:

INSERT INTO student (Name, Subject) VALUES (“Ryan”, “English”);

This command will insert the values of Ryan and English in the column Name and Subject of the table student.

UPDATE: This command is used to modify the existing values present in the table of the database. The syntax of the command is:

UPDATE Name_of_table SET [Name_of_column1=value1,… Name_of_columnN = value N][WHERE CONDITION]

Name_of_table: The name of the table in which the data is updated.

Name_of_column: The name of the column in which the value is to be updated

Value1: The value that needs to be updated.

WHERE CONDITION: The unique ID or Key of the row

Example:

UPDATE student SET Name = “James” WHERE student_id = ‘4’

This command will update the name of the student having student id 4 in the table of students.

DELETE: Helps in removing rows of data. The syntax of the command is:

DELETE FROM Name_of_table [WHERE condition];

Name_of_table: The name of the table from where the data is to be deleted.

Example:

DELETE FROM student WHERE Name = “James”;

This command will delete the row that has the name of James in it.

4. Transaction Control Language (TCL): These are the type of commands that can only be used with DML commands. These commands help store the changes on the database and once used cannot be reverted. Some of the examples of TCL commands are:

COMMIT: This command helps save all the DML commands on the database. The syntax of the command is:

COMMIT;

Example:

DELETE FROM STUDENTS WHERE AGE = 32;

COMMIT;

This command will save the changes made in the database i.e. removal of students having age of 32 from the students’ table.

ROLLBACK: This command will undo the commands that have been executed previously and not saved on the database. The syntax of the command is:

ROLLBACK;

Example:

DELETE FROM STUDENTS WHERE AGE = 32;

ROLLBACK;

This command will revert the changes made in the students’ database.

SAVEPOINT: This command is used to revert the changes to a certain point. The complete revert back is not complete but the process is reverted to a certain point.

SAVEPOINT SAVEPOINT_NAME;

5. Data Query Language (DQL): This command is used to get data from a database. An example of such a command is the SELECT command. The syntax of the command is:

SELECT expression FROM Name_of_table WHERE condition;

Example:

SELECT Name FROM student where Age = 32;

This command will select the values from the name column in the student table where the age is 32.

Summary

In this article, we have discussed relational databases in detail and SQL which is issued to query data in relational databases. I hope that after reading this article you will end up with sufficient knowledge of Relational databases and SQL.

KS3 Computer Science

GCSE Computer Science

A-Level Computer Science

SQL

Relational Database

How do relational databases work?

Relational Database Model

What are the benefits of Relational Databases?

Stored Procedures

Locking and concurrency in relational databases

Choosing a Relational database for your system

Learning SQL

SQL Commands