What is Relational Database Management Systems

The foundational building block of any software or a web based application is the data it holds. Imagine the scale and size of data that websites like twitter and Facebook deals with each day, as users are increasing day by day their database is becoming bigger and bigger each minute.

Each and every organization needs to store the very critical information about their product,users,sales etc in an organized way so that they can be retrieved later without pain. Advancement in computer and storage technology has catered this need very efficiently. System administrators deal with very big and critical databases in their day to day life. The major factor that increases the efficiency of retrieval of this data is the "the method used to organize the data".

In this tutorial we will understand the concept of "Relational Databases". Although we system administrators deal with databases, most among us find it boring to go through those research papers to understand the internal working of a database. The main advantage of understanding concepts behind anything is that it will increase the interest to learn more about the subject.

What is a database?

You might have already read the same definition again and again in your computer books at college. so let me once again tell you that "Its an organized collection of data". Think of database as a spreadsheet made in Microsoft Excel, but yeah sometimes this spreadsheet can be very large, containing values that are really big than what you normally see in any excel sheet.

Database also contains columns and rows similar to an excel sheet. But keep in mind that it must be easy to insert,delete,retrieve,or modify fields in a database.

So lets make our Microsoft Excel sheet.... oops sorry database table and rows (don't worry its similar to a spreadsheet in excel). Lets take an example of students enrolled in a college.

I would suggest to look at the above table and analyze whether the above shown table is a good method for storing data. There are several problems in that table which are mentioned below.

• What if the students "Mark" and "Martin" leaves the college, and you delete student ID 304 & 306 from the table. If you see closely deleting "Mark" & "Martin" will also delete the course name "Computer Science" because no other students were in that course.
• If see further the column number 3 & 4 (courseid & coursename) are repetitive. Same course name appears multiple times in the same column as multiple students opted for it.
• What if i want to change the course name "Economics" to something like "Modern Economics", in that case i will have to sit and modify course name field of all those students who opted for "Economics"

Relational Database Concepts will rescue in such situations. You can in fact split the above shown table of students to two different tables and relate the two tables for retrieval.

Two tables can be made one for "students" and one for "courses" and we will relate those two with the help of a common element called as Foreign Keys.

Courses table

Students Table

In the above method of splitting the number of tables to two. I can easily obtain the information by accessing both the tables.

• To obtain the details of the student "Mark" i just need to look at students table to find the courseid of mark as "40", and from the courses table i will also come to know that courseid "40" means he is in "Computer Science".
• I can delete "Mark" & "Martin" from the students table, without deleting the course name "Computer science".

People might argue that the first method of storing data in a single table is more easier. But when it comes to retrieve data based on different requirements splitting the table and relating the splitted tables becomes very very efficient.

In the above shown example of two tables, if i modify the course name of "Economics" to "Modern Economics",all students having courseid of 30 will automatically become part of "Modern Economics" course. This is because course details is Anyways fetched from the courses table with the help of "courseid" from students table.

What is primary key in a database?

There must be some value in every table that must identify the entries of a row very uniquely. The unique values in a column that will be helpful in completely retrieving a record is called as a primary key.

From our example relational database tables, STNO(Student No) in the "Students" table can be the primary key. The main reason behind is the fact that, all values in the column "STNO" are unique and it never repeats in the column.

And that unique "STNO" can be used to retrieve the complete information of a student.

What is foreign key in a database?

Although i have already explained that in the above example relational database tables, lets go through that once again.

A foreign key is nothing but a column in a table that links two tables. This column of foreign key establishes a relation between two tables for cross reference.

Unlike primary keys,there is no restriction for unique values for foreign keys.

Some widely used Relational Database Management Systems

There are many proprietary as well as open source databases available to choose from. Some proprietary RDBMS systems are mentioned below.

1)Oracle Database

2)IBM DB2

3)Microsoft SQL Server

Also two widely used open source relational databases are as below.

1)MySQL

2)PostgreSQL

Structured Query Language Examples

The language used to create the tables and data inside the database is called "Structured Query Language". All database management system's uses this language to insert,delete,modify and do other stuffs in the database.

Lets create the exact same table that we have seen in the above example with the help of SQL. The below example SQL is done in MySQL database.

mysql> create database college;
Query OK, 1 row affected (0.11 sec)

The above SQL command creates a database with the name "college".

Now lets create our first table named courses with two columns as shown in the above example.

mysql> create table courses (courseid INTEGER(100),coursename VARCHAR(100));
Query OK, 0 rows affected (0.05 sec)

In the above shown example of creating table of courses INTEGER(Integer values of 100 length) & VARCHAR(character strings of length 100) is the type of data stored.

Lets add some entry in the table with SQL language.

mysql> insert into courses (courseid,coursename) values ("20", "Literature");
Query OK, 1 row affected (0.01 sec)

Lets see the content of this table.

mysql> select * from courses;
+----------+------------+
| courseid | coursename |
+----------+------------+
|       20 | Literature |
+----------+------------+

You can insert as many rows you need with the help of above commands. The main purpose behind showing the above mysql commands was to get an idea of SQL.

The above mentioned example of inserting data values to tables is done by application backend code in real world. Inserting data into database is mostly carried out by the web application front end.

Almost everything you do in a website is a database query at the backend. I will be doing a dedicated post on how database is fast because of index and how in fact is the data stored in a database.

Hope this introductory tutorial was helpful in getting an overview and idea behind Relational Database Management system.