Fundamentals of Database Systems: DBMS Tutorial

Fundamentals of Database Systems: DBMS Tutorial
Fundamentals of Database Systems: DBMS Tutorial

Introduction

Before we start the DBMS Tutorial, we need to have a clear insight into what Databases are. Let us say that you’re working in a corporation where there are about a million employees scattered across the globe. Every employee will have a Unique Employee ID, a job role, a manager who the employee is working under, a ‘hire’ date and a ‘termination of contract’ date, and a particular amount of salary.

Now it is not impossible to make tables, categorize the data, write a million entries down into one long sheet of paper, but it is rather unethical. This is why Databases officially came into existence in the 1960s. 

A Database is an electronic form of the ‘unethical long sheet’. This Database can now be broken into parts, related to one another, sorted by different variables, and can even be compared to each other. Let’s say that you want to compare the salary of employees, having the same job role and years of experience.

The only key difference is that they are working in different countries. The DBMS or Database Management Systems let us do so.

What is DBMS?

Now that we have a clear insight on what Databases are, we can start discussing DBMS (Database Management Systems). You might have heard of MySQL or Microsoft Access. These are two out of the many Database Management Systems. These are software that allow you to store data, fetch data and retrieve it and also allow you to run queries on Databases.

Types of DBMS

There are mainly four types of DBMS:

  • Relational DBMS
  • Hierarchical DBMS
  • Network DBMS
  • Object-oriented DBMS

Out of these four, the most widely used DBMS is the Relational DBMS. Some commonly used Relational DBMS are ‘Oracle Database’, ‘MySQL’, ‘Microsoft SQL Server’, and ‘IBM DB2’. 

Relational DBMS vs. Spreadsheets

All of us have heard of Spreadsheets. It lets us create tables, enter data, perform calculations on the data in the cells, and write mathematical functions. You can also maintain multiple worksheets, relate the data amongst the worksheets, even during calculation. So why not use just spreadsheets then? Why rely on SQL commands to operate on the same tabular data using a Relational DBMS? Since both works on tabular data, people think that they are interchangeable, but it is not so.

Let’s say that you join a company that has a Dataset for its employees containing ‘Unique Employee ID’, a ‘job role’, a ‘manager’ who the employee is working under, a ‘hire’ date, and a ‘termination of contract’ date and a particular amount of salary. 

In a Spreadsheet, all the entries will be stored as a String. Hence, if the person handling data enters a job role into the ‘salary’ column, it will show no error. That would disrupt our entire dataset as we won’t be able to work with the ‘salary’ column. This does not happen in a Relational DBMS.

blog banner 1

When this same data is taken as a Database, the columns must be preset to a specific type, say INTEGER or DATE for example. This will prevent a user from entering a String into the salary column as an error would pop up.

In spreadsheets, each cell contains various formulas, functions and calculations. When combining multiple integers inside a spreadsheet, the results (or retrieved data) are stored in other cells. In the case of databases, each operation or calculation based on existing data is returned only once the data is retrieved. This allows the main dataset to remain unaffected by these operations or functions.

A few pros of Databases over Spreadsheets:

  • Retrieval of Records
  • Updating of Records
  • Efficiency
  • Data Consistency
  • Data Integrity
  • Speed 
  • Security

DBMS for Placement

During a placement interview, you will definitely be asked to answer questions about DBMS. In order to ace your placement interview, it is necessary to know about DBMS. You can definitely take up a DBMS course for placement, you can take up a DBMS course online, or join an institute that will train you in DBMS. The courses will prepare you for the questions asked on DBMS in software engineering interviews. You need to practice running SQL commands on Databases to get a better understanding of DBMS.

Most of the questions asked in interviews for the position of a Database Engineer are on SQL (Structured Query Language) commands and RDBMS terms like, ‘What are Joins?’, ‘What are the fundamentals of SQL commands used when running a query?’

So what are the fundamentals of SQL commands used while running a query?

The fundamentals of SQL commands:

  • Data Definition Language(DDL) – It contains the commands
    • CREATE
    • ALTER
    • TRUNCATE
    • DROP
    • RENAME

The DDL is used to create or modify, in simple terms, ‘define’ the objects of the database or its structure.

  • Data Manipulation Language (DML) – It contains the commands
    • INSERT
    • UPDATE
    • DELETE

The DML is used to manipulate the database according to the user by running queries. These are the most common codes used while working with Databases. It lets you change the data by updating a value, inserting a value, or deleting it from the Database.

  • Data Query Language (DQL) – It contains the commands
    • SELECT

The DQL is used to run queries on the Database. The ‘SELECT’ command allows the user to nitpick whatever he or she wants from a Database. The SELECT command can be used with other commands like ‘WHERE’ and ‘HAVING’, to pick out specific portions of data from a Database by conditions according to the user’s requirements.

  • Data Control Language (DCL) – It contains the commands
    • GRANT 
    • REVOKE

The DCL is used to specify access to users over a Database. If 100 people are working on a particular Database, the Database Administrator can grant or revoke access to particular parts of the Database depending on what he wants the Database Engineers to work with.

  • Transaction Control Language (TCL) – It contains the commands
    • COMMIT
    • ROLLBACK
    • SAVEPOINT 
    • SET TRANSACTION

The TCL is used to deal with the transactions in the Database. The COMMIT command lets a user save the Database after its last query is processed, whilst the ROLLBACK lets the user undo the changes made with the last query processed on the Database. You can learn all of this by taking up a DBMS course online.

Frequently Asked Questions

Where can I learn a DBMS course?

We are in the era of Digitalisation. There are many e-courses available over the internet, all you need to do is search on Google.
Here is the link for a DBMS Tutorial, this is all you will need to answer those questions asked in the software engineering interviews :
https://www.codingninjas.com/courses/dbms-course

What are the 4 major constituents of DBMS?

The four major constituents are Data, Hardware, Software and Users.

What are the 4 types of DBMS?

The four types of DBMS are Relational, Hierarchical, Network, and Object-oriented DBMS.

What is an example of DBMS?

MySQL, PostgreSQL, Oracle, dBase, MariaDB, Microsoft Access are a few DBMS out there.

Are there any prerequisites for taking up a DBMS course?

Whilst speaking about a DBMS course eligibility there are no prerequisites for coding. DBMS like MySQL has its own syntax which is a bit different from languages like C++ or Java. A prior experience of Spreadsheets might help in visualising the data that you’re going to be working with.

Does a DBMS course certification help in getting a job?

The short answer is yes. Although no one is going to ask you if you have a DBMS certification during your interview, you have the option to take up DBMS courses with certificates. This will make your resume look trustworthy if you’re applying for a job role in DBMS.

What are the career prospects available after learning DBMS?

Databases find their way in almost every career path right now, starting from a Financial Analyst to a Machine Learning Engineer. Some direct job roles in DBMS are Database Engineer, Database Administrator, Information Security Analyst, Market Research Analyst.

What are the Databases that are currently being used in the industry?

There are many Databases that are currently in use, a few of them are Relational databases,
Operational databases, NoSQL databases, Cloud databases, Object-oriented databases, Graph databases.

What DBMS should I learn first?

Although there is a wide variety to choose from, MySQL is probably the most popular DBMS currently in use.

Key Takeaway

As DBMS allows us to work with data in real-time, two engineers working on the same Database, sitting on the opposite sides of the globe will be able to instantly see any changes made on the other side, there is almost no delay. Databases allow us to handle enormous amounts of data at once and offer a stable and secure environment as they can grant or revoke access to users.

A person working with a Dataset in a Database might not be able to view or work with the other Datasets in the same Database as he or she might not have access to it. As a software engineer, DBMS is one of the most essential fields that you must train yourself in or have substantial knowledge about.

Even Machine Learning Engineers use Databases to create computational models. Almost all companies out there, including Google, Amazon, Microsoft, and Oracle use DBMS for their operations.