UNIT- 2

DBMS

DATABASE MANAGEMENT SYSTEMS

 History of Database Management System

• Charles Bachman came up with the first DBMS system in 1960.

• Codd by IBM’S Information Management System enters in
1970.
• Peter Chen introduces the Entity-relationship model in 1976.
• The Relational Model became a database component in 1980.
• Object-oriented DBMS develops in 1985
• The incorporation of object orientation in DBMS takes place in
the 1990s.
• A personal DBMS by Microsoft – MS access came out in 1991.
• The first Internet database applications came out in 1995.
• XML became relevant to database processing in 1997.
A database management system (DBMS) refers to the
technology for creating and managing databases. DBMS is a
software tool to organize (create, retrieve, update, and
manage) data in a database.
The main aim of a DBMS is to supply a way to store up and
retrieve database information that is both convenient and
efficient. By data, we mean known facts that can be
recorded and that have embedded meaning.
Usually, people use software such as DBASE IV or V,
Microsoft ACCESS, or EXCEL to store data in the form of a
database. A datum is a unit of data. Meaningful data
combined to form information. Hence, information is
interpreted data - data provided with semantics. MS.
ACCESS is one of the most common examples of database
management software.
Why use DBMS?

• To develop software applications In less time.

• Data independence and efficient use of data.
• For uniform data administration.
• For data integrity and security.
• For concurrent access to data, and data recovery from crashes.
• To use user-friendly declarative query language.

DBMS ARE USED

• Airlines: reservations, schedules, etc.

• Telecom: calls made, customer details, network usage, etc.
• Universities: registration, results, grades, etc.
• Sales: products, purchases, customers, etc.
• Banking: all transactions etc.
 ADVANTAGES OF DBMS

A DBMS manages data and has many benefits. These are:

• Data independence: Application programs should be as free or
independent as possible from details of data representation and
storage. DBMS can supply an abstract view of the data for insulating
application code from such facts.
• Efficient data access: DBMS utilizes a mixture of sophisticated
concepts and techniques for storing and retrieving data competently.
This feature becomes important in cases where the data is stored on
external storage devices.
• Data integrity and security: If data is accessed through the DBMS, the
DBMS can enforce integrity constraints on the data.
• Data administration: When several users share the data, integrating
the administration of data can offer significant improvements.
Experienced professionals understand the nature of the data being
managed and can be responsible for organizing the data
representation to reduce redundancy and make the data to retrieve
efficiently.
 DISADVANTAGES OF DBMS

More Costly
Creating and managing a database is quite costly. High cost software and hardware is
required for the database. Also highly trained staff is required to handle the database and it
also needs continuous maintenance. All of these ends up making a database quite a costly
venture.
High Complexity
A Database Management System is quite complex as it involves creating, modifying and
editing a database. Consequently, the people who handle a database or work with it need to
be quite skilled or valuable data can be lost.
Database handling staff required
As discussed in the previous point, database and DBMS are quite complex. Hence, skilled
personnel are required to handle the database so that it works in optimum condition. This is
a costly venture as these professionals need to be very well paid.
Database Failure
All the relevant data for any company is stored in a database. So it is imperative that the
database works in optimal condition and there are no failures. A database failure can be
catastrophic and can lead to loss or corruption of very important data.
High Hardware Cost
A database contains vast amount of data. So a large disk storage is required to store all this
data. Sometimes extra storage may even be needed. All this increases hardware costs by a lot
and makes a database quite expensive.
Huge Size
A database contains a large amount of data, especially for bigger
organizations. This data may even increase as more data is updated
into the database. All of these leads to a large size of the database.
The bigger the database is, it is more difficult to handle and maintain.
It is also more complex to ensure data consistency and user
authentication across big databases.
Up-gradation Costs
Often new functionalities are added to the database. This leads to
database up-gradations. All of these up-gradations cost a lot of
money. Moreover it is also quite expensive to train the database
managers and users to handle these new up-gradations.
Cost of Data Conversion
If the database is changed or modified in some manner, all the data
needs to be converted to the new form. This cost may even exceed
the database creation and management costs sometimes. This is the
reason most organizations prefer to work on their old databases
rather than upgrade to new ones.
 COMPONENTS OF DBMS
• Users: Users may be of any kind such as DB
administrator, System developer, or database
users.
• Database application: Database application
may be Departmental, Personal,
organization's and / or Internal.
• DBMS: Software that allows users to create
and manipulate database access,
• Database: Collection of logical data as a
single unit.
 Types of DBMS

There are broadly four categories or types of DBMS:

• Hierarchical databases
• Network databases
• Relational databases (RDBMS)
• Object-oriented databases

Hierarchical databases
This type of DBMS showcases a parent-child type of relationship. This relationship forms a tree-like
structure where the nodes (leaves) of the tree represent records and the fields are represented by
branches.

Network databases
This style of DBMS embraces several partnerships where it is possible to connect multiple user
records at the same time in parallel.

Relational databases (RDBMS)

This type of DBMS helps users to locate and manipulate data that has connections with another piece
of data in the database. It uses tables for storing the data in a row-and-column format.

Object-oriented databases
In this type of DBMS, data is stored in individual components called objects, where each object is a
piece of data with some instructions for the tasks that should be performed on that data.
 Paradigm Shift from File System to DBMS
Apart from the differences above, there were many reasons
why the file system did not work –

• The redundancy in data was tough here as copying of data

did not lead to an update at various sections.
• There was inconsistency of data because of typing errors
and irregular updates.
• It was difficult to access data as users found it difficult to
know the exact file location.
• The unauthorized access was easier due to fewer security
measures.
• There was no concurrent access as only one user could
access it at a time.
• No backup and recovery were leading to files being lost
forever.
 Database Schema
A schema is a structure that gives us an overview of the
entire database logically. It mentions the data
organization technique and how the relationship
between the component is formed.
It has a database description which the users see
through a schema diagram. But all elements cannot be
part of this diagram. It is of two types –
1. Physical Database Schema has the information
regarding the actual storage of data and how secondary
storage will make space for them.
2. Logical Database Schema has information regarding
logical constraints of the data in the form of tables,
views, and integrity constraints.
 Important DBMS Terms
• Data Manipulation Languages – A programming
language to modify and add data in the existing
database. SQL and DDL are the two types of data
manipulation languages that users use commonly.
• Structured Query Language (SQL) – A type of
programming language relevant for tabular database
management that is relational in nature.
• Data Definition Language (DDL) – Again a
programming language for modifying existing data in
the database, mainly in tabular form.
1. Primary Key – A unique key to identify a particular file.
2. Foreign Key – A key that the primary key uses to identify
the relationship between field and component in the
table.
Popular DBMS Software
• MySQL
• Microsoft Access
• Oracle
• PostgreSQL
• dBASE
• FoxPro
• SQLite
• IBM DB2
• LibreOffice Base
• MariaDB
• Microsoft SQL Server
 Architecture of DBMS
The architecture of a DBMS system can be central, decentral, or hierarchical. They look at DBMS in different
tiers starting from one and ending at three. The architecture without any tier has independent n modules
who modify, alter, change and replace by themselves.
1. 1-tier architecture
The user can have only one entity to access the database and use it. Any changes here will automatically
update the data itself. There are no handy tools for the users and this is generally ideal for database designers
and programmers.
2. 2-tier architecture
It has an application to access DBMS and it works independently. It manages operation, design, and
programming on itself.

3. 3-tier Architecture
It is a very complex design as each tier is different in the database. But it is the most common architecture
design of DBMS. The tiers are –
a. Database (Data) Tier
This tier is for query processing languages. The relations between the data and its constraints are also present
here.
b. Application (Middle) Tier
The application server and program accessing the data are present at this tier. The user and the data are not
aware of each other beyond this tier. This is why this tier becomes a mediator between them.

c. User (Presentation) Tier

The users are operating through this tier and know nothing above this. The multiple views option is also
present at this tier generated by the application.
Multiple-tier database architecture allows more modification and independence in terms of functioning.
 Structure of DBMS
DBMS
DBMS means Database Management System, which is a tool or software used to create the
database or delete or manipulate the database. A software programme created to store,
retrieve, query, and manage data is known as a Database Management System (DBMS). Data
can be generated, read, updated, and destroyed by authorized entities thanks to user
interfaces (UIs).
Three Parts that make up the Database System are:
• Query Processor
• Storage Manager
• Disk Storage
The explanations for these are provided below:
1. Query Processor
The query processing is handled by the query processor, as the name implies. It executes the
user's query, to put it simply. In this way, the query processor aids the database system in
making data access simple and easy. The query processor's primary duty is to successfully
execute the query. The Query Processor transforms (or interprets) the user's application
program-provided requests into instructions that a computer can understand.
Components of the Query Processor
a. DDL Interpreter:
Data Definition Language is what DDL stands for. As implied by the name, the DDL Interpreter
interprets DDL statements like those used in schema definitions (such as create, remove,
etc.). This interpretation yields a set of tables that include the meta-data (data of data) that is
kept in the data dictionary. Metadata may be stored in a data dictionary.
b. DML Compiler:
Compiler for DML Data Manipulation Language is what DML stands for. In keeping with its
name, the DML Compiler converts DML statements like select, update, and delete into low-
level instructions or simply machine-readable object code, to enable execution. The
optimization of queries is another function of the DML compiler. Since a single question can
typically be translated into a number of evaluation plans. As a result, some optimization is
needed to select the evaluation plan with the lowest cost out of all the options. This process,
known as query optimization, is exclusively carried out by the DML compiler. Simply put, query
optimization determines the most effective technique to carry out a query.
c. Embedded DML Pre-compiler:
Before the query evaluation, the embedded DML commands in the application program (such
as SELECT, FROM, etc., in SQL) must be pre-compiled into standard procedural calls (program
instructions that the host language can understand). Therefore, the DML statements which are
embedded in an application program must be converted into routine calls by the Embedded
DML Pre-compiler.
d. Query Optimizer:
It starts by taking the evaluation plan for the question, runs it, and then returns the result.
Simply said, the query evaluation engine evaluates the SQL commands used to access the
database's contents before returning the result of the query. In a nutshell, it is in charge of
analyzing the queries and running the object code that the DML Compiler produces. Apache
Drill, Presto, and other Query Evaluation Engines are a few examples.
2. Storage Manager:
An application called Storage Manager acts as a conduit between the queries made and the
data kept in the database. Another name for it is Database Control System. By applying the
restrictions and running the DCL instructions, it keeps the database's consistency and
integrity. It is in charge of retrieving, storing, updating, and removing data from the database.
Components of Storage Manager
Following are the components of Storage Manager:
a. Integrity Manager:
Whenever there is any change in the database, the Integrity manager will manage the
integrity constraints.
b. Authorization Manager:
Authorization manager verifies the user that he is valid and authenticated for the specific
query or request.
c. File Manager:
All the files and data structure of the database are managed by this component.
d. Transaction Manager:
It is responsible for making the database consistent before and after the transactions.
Concurrent processes are generally controlled by this component.
e. Buffer Manager:
The transfer of data between primary and main memory and managing the cache
memory is done by the buffer manager.
3. Disk Storage
A DBMS can use various kinds of Data Structures as a part of physical system
implementation in the form of disk storage.
Components of Disk Storage
Following are the components of Disk Manager:
a. Data Dictionary:
It contains the metadata (data of data), which means each object of the database has
some information about its structure. So, it creates a repository which contains the details
about the structure of the database object.
a. Data Files:
This component stores the data in the files.
 Personnel involved in Database Management System

Many persons are involved in the design, use, and maintenance of a large database with a few hundred
users. Here we will consider people who may be called “Actors on the Scene”, whose jobs involve the day-
to-day use of a large database.
1. Database Administrators: Administrating the primary (database) and secondary (DBMS and related
software) is the responsibility of the database administrator (DBA). The DBA is responsible for
authorizing access to the database, coordinating and monitoring its use, and acquiring software and
hardware resources as needed.
2. Database Designers: Database Designers are responsible for identifying the data to be stored in the
database and for choosing appropriate structures to represent and store this data. Database
designers typically interact with each potential group and user and develop a view of the database
that meets the data and processing requirements of these groups.
3. End Users: End users are the people whose jobs require access to the database for querying,
updating, and generating reports; the database primarily exists for their use. There are several
categories of end users:
• Casual end user: Occasionally access the database, but they may need different information each time.
They are typically middle-or high-level managers or other occasional browsers.
• Naive or Parametric end user: Their main job function revolves around constantly querying and
updating the database, using standard types of queries and updates that have been carefully
programmed and tested. Bank tellers, Reservation Clerks for airlines, hotels, etc are the example of
Naive end users.
• Sophisticated end users: Sophisticated end users include engineers, scientists, business analysts, and
others who thoroughly familiarize themselves with the facilities of the DBMS so as to implement their
applications to meet their complex requirements.
• Stand-alone users: They maintain the personal databases by using ready-made program packages that
provide an easy-to-use menu or graphics-based interfaces.
4. Software Engineers: System analysts determines the requirements of end users,
especially naive and parametric end users, and develop specifications for canned
transactions that meet these requirements. Application programmers implement these
specifications as programs; then they test, debug, document, and maintain these canned
transactions. Such analysts and programmers are called Software Engineers.

There are several personnel involved in the management of a database management

system, including:
• Database administrators (DBAs): These professionals are responsible for the overall
management of the database system. They are in charge of setting up, configuring, and
maintaining the database, as well as ensuring its security and performance.
• Database developers: These professionals design and create the database schema,
tables, views, and queries. They work closely with the DBAs to ensure that the database
is designed and implemented to meet the needs of the organization
• Data analysts: These professionals use the database to extract and analyze data to
provide insights into business performance, trends, and customer behavior.
• Data architects: These professionals are responsible for designing the overall structure
of the database and ensuring that it is scalable, secure, and optimized for performance.
• Application developers: These professionals create applications that interact with the
database. They work closely with the database developers and DBAs to ensure that the
applications are efficient and use the database resources appropriately.
• System administrators: These professionals are responsible for the hardware and
software infrastructure that supports the database system. They ensure that the
database servers are up and running and that they have adequate resources to support
• Security personnel: These professionals are responsible for ensuring that the database is
secure and that data is protected from unauthorized access or theft. They work closely with
the DBAs and system administrators to implement security measures and monitor the
database for security breaches.
Several personnel are typically involved in managing and maintaining a Database Management
System (DBMS) to ensure its efficient functioning. The key personnel include:
a) Database Administrators (DBAs): DBAs are responsible for managing and maintaining the
DBMS. They are responsible for tasks such as installing and configuring the DBMS, designing
and implementing the database schema, ensuring data security and backup, and optimizing
database performance.
b) Database Developers: Database developers are responsible for developing and
implementing the database applications using the DBMS. They are responsible for tasks such
as designing and implementing the database schema, developing stored procedures,
triggers, and functions, and writing database queries.
c) Data Analysts: Data analysts are responsible for analyzing the data stored in the DBMS to
derive insights and trends. They use tools and techniques such as data mining, statistical
analysis, and machine learning to analyze the data and provide insights to the organization.
d) Business Analysts: Business analysts are responsible for understanding the business
requirements of the organization and translating them into data requirements. They work
closely with the data analysts and DBAs to ensure that the database is designed to meet the
business requirements.
e) End Users: End users are the people who use the DBMS to access and retrieve
the data. They can be employees within the organization or external customers
and partners who need access to the data.
 Database Normalization
In database management systems (DBMS), normal forms are a series of guidelines that help to
ensure that the design of a database is efficient, organized, and free from data anomalies. There
are several levels of normalization, each with its own set of guidelines, known as normal forms.
Important Points Regarding Normal Forms in DBMS
• First Normal Form (1NF): This is the most basic level of normalization. In 1NF, each table cell
should contain only a single value, and each column should have a unique name. The first
normal form helps to eliminate duplicate data and simplify queries.
• Second Normal Form (2NF): 2NF eliminates redundant data by requiring that each non-key
attribute be dependent on the primary key. This means that each column should be directly
related to the primary key, and not to other columns.
• Third Normal Form (3NF): 3NF builds on 2NF by requiring that all non-key attributes are
independent of each other. This means that each column should be directly related to the
primary key, and not to any other columns in the same table.
• Boyce-Codd Normal Form (BCNF): BCNF is a stricter form of 3NF that ensures that each
determinant in a table is a candidate key. In other words, BCNF ensures that each non-key
attribute is dependent only on the candidate key.
• Fourth Normal Form (4NF): 4NF is a further refinement of BCNF that ensures that a table
does not contain any multi-valued dependencies.
• Fifth Normal Form (5NF): 5NF is the highest level of normalization and involves decomposing
a table into smaller tables to remove data redundancy and improve data integrity.