SQL Essentials Training & Certification
- 7k Enrolled Learners
- Self Paced
As you all know, Database management system (DBMS) is software that is used to manage databases. So, this article on DBMS Tutorial will help you understand both, basic and advanced concepts of DBMS.
The topics discussed in this article are:
The Database is an organized collection of structured data to make it easily accessible, manageable and update. In simple words, you can say, a database in a place where the data is stored. The best analogy is the library. The library contains a huge collection of books of different genres, here the library is database and books are the data.
During the initial phase of computer era, data was collected and stored on tapes, which were mostly write-only devices, which meant once data was stored on it, it could never be read again. They were slow and bulky, and soon computer scientists realized that they needed a better solution to this problem.
Together, the data and the DBMS, along with the applications that are associated with them, are referred to as a database system, often shortened to just a database.
‘Databases’ is a very vast topic. So, covering the topics under this subject is a very tedious task.
Now, the major characteristics of a Database include:
Now, talking about the applications of a Database, we’ll see where exactly you can make use of Database.
Database applications are software programs that are designed to collect, manage and disseminate information very efficiently. So many small business owners create simple databases such as customer contact and mailing lists with easy to use software and there are companies that use the advance databases for data manipulation.
Talking about the accounting system, it is a custom database application used to manage financial data.
Many web applications also use Databases in order to store data. This can be confidential information of an organization or some private information about the user. Database is used to store data in sequential order and helps you in accessing data whenever required.
A Customer Relationship Management System (CRM) is a perfect database application that has been customized to manage the marketing, sales, and support relationships between a business and it’s customers.
The major goal is to maximize the number of sales, minimize costs and foster strategic customer relationships.
Now that you have got an idea of how Database works, let’s move on and understand the Database Management System.
A Database Management System (DBMS) is a software that is used to manage the Database. It receives instruction from a Database Administrator (DBA) and accordingly instructs the system to make the corresponding changes. These are basically commands that are used to load, retrieve or modify existing data from the system.
Database Management Systems also aim to facilitate an overview of the databases, by providing a variety of administrative operations such as tuning, performance monitoring, and backup recovery.
Database Management Systems allow users to do the following:
Now, let’s see how to create a Database.
We use the CREATE DATABASE statement to create a new database.
CREATE DATABASE databasename;
CREATE DATABASE College;
So the database of name College will be created. This is how simple you can create a Database.
Now let’s understand the applications of DBMS.
These are some of the notable applications of DBMS. Now, let’s move on and understand the features of DBMS.
Now, let’s understand the architecture of DBMS.
Designing DBMS, mainly depends on its architecture. The architecture can either be Centralized or Decentralized or Hierarchical. It can be seen as a single-tier or multi-tier. You can also have an n-tier architecture that divides the whole system into related but independent n modules, that can be independently modified, altered, changed, or replaced.
You can have:
Here a database is directly accessible to the user. It means the user can directly reside on a DBMS and uses it. Any changes done here will directly be done on the database itself. And, it doesn’t provide a handy tool for end-users.
1-Tier is used where the Client, Server, and Database all reside on the same machine. Anytime you install a Database in your system and access SQL queries, it is the 1 tier architecture that is used. But this architecture is rarely used in the production section.
The two-tier architecture is the same as the basic client-server. In this architecture, applications on the client end can directly communicate with the database at the server-side. In order to communicate with the DBMS, the client-side application establishes a connection with the server-side.
Whenever the client machine makes a request to access the database present in the server using SQL, the server performs the request on the database and returns the result back to the client.
The 3-Tier architecture contains a layer between the client and the server. Here, the client can’t directly communicate with the server. The end-user has no idea about the application server. The database also has no idea about any other user beyond the application.
The application present on the client-end interacts with an application server which in turn communicates with the database system.
It has three layers or tiers namely, Presentation layer, Application layer, and Database layer.
Database Tier: In this tier, a database is present along with its processing languages (Query). You also have the relations that define the data and their constraints at this level.
Application Tier: It is also called the middle tier. This tier consists of application server and the programs that access the Database. For a user, this application tier showcases an abstract view of the Database. At the other end, the Database tier is not aware of other users beyond the application tier. Therefore, the application layer sits in the middle and acts as a mediator between the end-user and the Database.
User Tier: This is also called as a Presentation tier. The end-users operate in this tier and know nothing about the existence of the Database beyond this layer. In this layer, multiple views of the Database can be provided by the application. All views are generated by applications that are present in the Application tier.
Now that you have understood the architecture, let’s move on and understand the components of DBMS.
Talking about the components of DBMS, we have:
This consists of a set of physical electronic devices such as I/O devices, storage devices and many more. It also provides an interface between computers and real-world systems.
This is the set of programs that are used to control and manage the overall Database. It also includes the DBMS software itself. The Operating System, the network software being used to share the data among the users, the application programs used to access data in the DBMS.
Database Management System collects, stores, processes, and accesses data. The Database holds both the actual or operational data and the metadata.
These are the rules and instructions on how to use the Database in order to design and run the DBMS, to guide the users that operate and manage it.
Database Access Language
It is used to access the data to and from the database. In order to enter new data, updating, or retrieving requires data from databases. You can write a set of appropriate commands in the database access language, submit these to the DBMS, which then processes the data and generates it, displays a set of results into a user-readable form.
Now that you guys have understood the components of a database, let’s move ahead and understand the types.
Following are the different types of DBMS:
Data models in DBMS help define how the logical structure of a database is modeled. Data Models are basically the fundamental entities that introduce abstraction in DBMS. These Data models also define how data is connected to each other and how they are processed and stored inside the system.
Now, why do you need this Data model?
This Data model can further be divided into these types:
Now, let’s see the working of these Data models.
This type of Data Model defines what the system contains. The Conceptual model is created by Data Architects in general. The purpose is to organize, scope and define business concepts and rules.
There are 3 basic styles under Conceptual Data models:
This can be referred to as the Entity-Relationship Model.
Entity-Relationship (ER) Model is based on the idea of real-world entities and relationships among them. This ER Model is best used for the Conceptual Design of a Database.
Entity: An Entity in an ER Model is a real-world entity having properties named as Attributes. Every attribute is defined by its set of values called the Domains.
For example, consider the details of a Student. The details like the name, age, class, section and all of these come under the entity.
Relationship: The logical association among the entities is called a Relationship. These Relationships are mapped with entities in different ways. The Mapping (one-to-one, one-to-many, many-to-many) defines the number of association between two entities.
Now let’s understand the Physical Data Model.
A Physical Data Model helps in describing the database-specific implementation of the Data model. The Physical Data model offers an abstraction of the Database and helps to generate the Schema.
Now, let’s understand the Logical Data Model.
Logical data models help to add further information to the Conceptual model elements. This model defines the structure of the data elements and also set the corresponding relationships between them.
In this level, no Primary or Secondary key is defined and you need to verify and adjust the connector details that were set earlier for relationships.
The main advantage of this Logical data model is to provide a foundation to form the base for the Physical model.
Moving on with the DBMS Tutorial, let’s take a look at the Keys in DBMS.
Keys are the most important concept of Databases. Keys play a vital role in the Relational Database. This is used for identifying unique rows from the table. It also establishes the relationship among tables.
Why do you need these Keys in the Database?
The answer to this would be,
DBMS possess different Keys that have different functionalities.
Let’s discuss the most commonly used Keys in DBMS.
Candidate Key: The minimal set of attributes that can uniquely identify a tuple is known as a candidate key. A relation can hold more than a single candidate key, where the key is either a simple or composite key.
Super Key: The set of attributes that can uniquely identify a tuple is known as Super Key. So, a candidate key is a superkey, but vice-versa isn’t true.
Primary Key: A set of attributes that can be used to uniquely identify every tuple is also a primary key. So, if there are 3-4 candidate keys present in a relationship, then out of those, one can be chosen as a primary key.
Alternate Key: The candidate key other than the primary key is called as an alternate key.
Moving on to the last topic of this article on the DBMS Tutorial, let’s learn about Normalization in DBMS.
Normalization is the process of reducing the redundancy of data in the table and also improving data integrity. So why is this required? without Normalization in SQL, we may face many issues such as
This below image depicts how Normalization in SQL works.
So, with this, we come to the end of this DBMS Tutorial. I hope you guys are clear about the topics that are discussed in this tutorial.
If you wish to learn more about MySQL and get to know this open-source relational database, then check out our MySQL DBA Certification Training which comes with instructor-led live training and real-life project experience. This training will help you understand MySQL in-depth and help you achieve mastery over the subject.
In case of queries you can put those in the comment section of DBMS Tutorial and we will revert at the earliest.