Microsoft SQL Server Certification Course
- 6k Enrolled Learners
- Weekend
- Live Class
(1600)
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:
What is a Database?
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.
Evolution of Database
- Databases have evolved since their inception in the early 1960s.
- In the 1980s, Relational databases became popular, followed by object-oriented databases in the 1990s.
- More recently, NoSQL databases came about as a response to the growth of the internet and the need for faster speed and processing of unstructured data.
- Today, Cloud databases and self-driving databases are breaking new ground when it comes to how data is collected, stored, managed, and utilized.
‘Databases’ is a very vast topic. So, covering the topics under this subject is a very tedious task.
DBMS Tutorial: Characteristics of Database
Now, the major characteristics of a Database include:
- It uses a digital repository established on a server to store and manage the information.|
- The database should be able to store all kinds of data that exist in this real world.
- It can provide a clear and logical view of the process that manipulates data.
- Most importantly, the database is used to provide security of data.
- DBMS contains all automatic backup and recovery procedures.
- It also contains ACID properties that maintain data in a healthy state in case of failure.
- Database can reduce the complex relationship between data.
- It is also used to support the manipulation and processing of data.
- You can view database from different viewpoints according to the requirements specified by the user.
Now, talking about the applications of a Database, we’ll see where exactly you can make use of Database.
DBMS Tutorial: Applications 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.
Accounting applications
Talking about the accounting system, it is a custom database application used to manage financial data.
- You can make use of the custom forms that are used to record assets, liabilities, inventory and the transactions between customers and suppliers.
- Get the overview of the income statements, balance sheets, purchase orders and invoices generated are custom reports based upon information that is entered into the database.
- Accounting applications run on a single computer suitable for a small business or in a networked shared environment in order to accommodate the needs of multiple departments and locations in larger organizations.
Web applications
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.
- Also many web applications are created using Database applications. There are web sites that also combine an accounting database system to record sales transactions and a CRM database application to incorporate feedback and drive positive customer experience. We’ll discuss the CRM database in the next topic.
- The most popular web-based application “Facebook” is essentially a database built upon the “MySQL” database system and is an indication of the increasing usage of database applications as foundation for Web-based applications.
CRM applications
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.
For details, You can even check out how to manage databases on SQL Server and its concepts with the Best SQL courses.
Advantages
- Reduced data redundancy.
- Also, there are reduced errors and increased consistency.
- Easier data integrity from application programs.
- Improved data access to users through the use of host and query languages.
- Data security is also improved.
- Reduced data entry, storage, and retrieval costs.
Disadvantages
- Complexity: Databases are complex hardware and software systems.
- Cost: It requires significant upfront and ongoing financial resources.
- Security: Most leading companies need to know that their Database systems can securely store data, including sensitive employee and customer information.
- Compatibility: There is a risk that a DBMS might not be compatible with a company’s operational requirements.
Now that you have got an idea of how Database works, let’s move on and understand the Database Management System.
DBMS
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:
- Define Data – Allows users to create, modify and delete the definitions which define the organization of the database.
- Update Data – Provides access to the users to insert, modify and delete data from the database.
- Retrieve Data – Allows users to retrieve data from a database based on the requirement.
- Administration of users – Registers users and monitors their action, enforces data security, maintains data integrity, monitors performance and deals with concurrency control.
Characteristics
- To limit access permissions of users
- Provide multiple views of the single database schema
- Facilitates security and removes data redundancy
- Allows multi-user transaction processing and sharing of data
- Follows the ACID property
- Offers both physical and logical data independence
Now, let’s see how to create a Database.
We use the CREATE DATABASE statement to create a new database.
Syntax:
CREATE DATABASE databasename;
Example:
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.
Applications of DBMS
- Banking
- Airlines
- Finance
- Sales and manufacturing
- Universities
These are some of the notable applications of DBMS. Now, let’s move on and understand the features of DBMS.
DBMS Tutorial: Features
- Minimum duplication: There are many users who use the database so chances of data duplicity is very high. In the Database management system, data files are shared that in turn minimizes data duplication.
- Saves storage space: DBMS has a lot to save, but the integration of data in a DBMS saves much more space.
- Cost-effective: Many companies are paying so much amount of money to store their data. If they have managed data to save, then it will save their cost of data entry.
- Security: DBMS stores all the data files permanently and there is no chance you could lose any data. For example, you happen to lose some data, then there is a backup and recovery method too that can save the organization’s data files. So, DBMS is highly secure.
Now, let’s understand the architecture of DBMS.
Architecture
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:
Single-tier
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.
2-Tier
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.
Three-Tier
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.
DBMS Tutorial: Components
Talking about the components of DBMS, we have:
- Hardware
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.
- Software
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.
- Data
Database Management System collects, stores, processes, and accesses data. The Database holds both the actual or operational data and the metadata.
- Procedure
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.
DBMS Tutorial: Types
Following are the different types of DBMS:
- Hierarchical: This type of DBMS showcases a style of predecessor-successor type of relationship. You can consider it to be similar to a tree, where the nodes of the tree represent records and the branches of the tree represent fields.
- Relational Database(RDBMS): This type has a structure which allows the users to identify and access data in relation to another piece of data in the database. Here, the data is stored in the form of tables.
- Network: This type of database management system supports many to many relations where multiple user records can be linked.
- Object-oriented: It uses small individual software called objects. Here, each object contains a piece of data and the instructions for the actions to be done with the data.
DBMS Tutorial: Data models
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?
- It ensures that all data objects required by the database are accurately represented. The omission of data at times will lead to the creation of faulty reports and produce incorrect results.
- A data model helps in designing the Database at the conceptual, physical and logical levels.
- The structure helps to define the relational tables, primary and foreign keys, and stored procedures.
- It is also helpful to identify missing and redundant data.
This Data model can further be divided into these types:
Types of Data model
Now, let’s see the working of these Data models.
Conceptual
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:
- Entity
- Attribute
- Relationship
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.
Physical
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.
This Physical Data model also helps to visualize the Database structure. It also helps to model Database columns keys, constraints, indexes, triggers, and other RDBMS features.
Now, let’s understand the Logical Data Model.
Logical
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.
I hope this is clear to you guys.
Moving on with the DBMS Tutorial, let’s take a look at the Keys in DBMS.
DBMS Tutorial: Keys
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,
- In a real-world application, a table could contain thousands or even more number of records. Moreover, the records can also be duplicated. Keys ensure that you can uniquely identify a table record despite many challenges.
- The Keys also allow you to establish a relationship and also identify the relationships between tables
- Keys also help you to enforce identity and integrity into the relationship.
Types of Keys
DBMS possess different Keys that have different functionalities.
- Super Key
- Primary Key
- Candidate Key
- Foreign Key
- Compound Key
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.
Foreign Key: An attribute that can only take the values present as the values of some other attribute, is the foreign key to the attribute to which it refers.
Moving on to the last topic of this article on the DBMS Tutorial, let’s learn about Normalization in DBMS.
Normalization
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
- Insertion anomaly: It occurs when we cannot insert data to the table without the presence of another attribute
- Update anomaly: It is a data inconsistency that results from data redundancy and a partial update of data.
- Deletion Anomaly: It occurs when certain attributes are lost because of the deletion of other attributes.
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.
