UNIT -1 DATABASE MANAGEMENT SYSTEM

INTRODUCTION TO DBMS

A database is a collection of interrelated data that helps in the efficient retrieval, insertion, and
deletion of data from the database and organizes the data in the form of tables, views, schemas,
reports, etc. For Example, a university database organizes the data about students, faculty,
admin staff, etc. which helps in the efficient retrieval, insertion, and deletion of data from it.

A Database Management System (DBMS) is a software system that is designed to manage and
organize data in a structured manner. It allows users to create, modify, and query a database, as
well as manage the security and access controls for that database. DBMS provides an
environment to store and retrieve data in convenient and efficient manner.

Types of DBMS
Relational Database Management System (RDBMS): Data is organized into tables (relations)
with rows and columns, and the relationships between the data are managed through primary
and foreign keys. SQL (Structured Query Language) is used to query and manipulate the data.
Examples are MySQL, Oracle, Microsoft SQL Server and Postgre SQL.
NoSQL DBMS: Designed for high-performance scenarios and large-scale data, NoSQL
databases store data in various non-relational formats such as key-value pairs, documents,
graphs, or columns. Examples are NoSQL DBMS are MongoDB, Cassandra, DynamoDB and
Redis.
Object-Oriented DBMS (OODBMS): Stores data as objects, similar to those used in object-
oriented programming, allowing for complex data representations and relationships
Applications of DBMS
DBMS are used almost in every software that we use. For example, Emails, WhatsApp, Social
Media and in-fact this article that you are reading is being stored and accessed using DBMS.
Almost all programming languages provide libraries from different popular database
management systems like MySQL, Oracle, Microsoft SQL Server, MongoDB, etc.

Banking: Manages accounts, transactions, and financial records.

Airlines: Handles bookings, schedules, and availability.
E-commerce: Supports catalogs, orders, and secure transactions.
Healthcare: Stores patient records and billing.
Education: Manages student data and course enrollments.
Telecom: Tracks call records and billing.
Government: Maintains census and taxation data.
Social Media: Stores user profiles and posts efficiently.
Key Features of DBMS
Data modeling: A DBMS provides tools for creating and modifying data models, which define
the structure and relationships of the data in a database.
Data storage and retrieval: A DBMS is responsible for storing and retrieving data from the
database, and can provide various methods for searching and querying the data.
Concurrency control: A DBMS provides mechanisms for controlling concurrent access to the
database, to ensure that multiple users can access the data without conflicting with each other.
Data integrity and security: A DBMS provides tools for enforcing data integrity and security
constraints, such as constraints on the values of data and access controls that restrict who can
access the data.
Backup and recovery: A DBMS provides mechanisms for backing up and recovering the data
in the event of a system failure.
DBMS can be classified into two types: Relational Database Management System (RDBMS)
and Non-Relational Database Management System (NoSQL or Non-SQL)
RDBMS: Data is organized in the form of tables and each table has a set of rows and columns.
The data are related to each other through primary and foreign keys.
NoSQL: Data is organized in the form of key-value pairs, documents, graphs, or column-based.
These are designed to handle large-scale, high-performance scenarios.

DATA AND INFORMATION

Data and Information are important concepts in the world of computing and decision-making.
Data is defined as unstructured information such as text, observations, images, symbols, and
descriptions on the other hand, Information refers to processed, organized, and structured data.
It gives context to the facts and facilitates decision-making.

What is Data?

Data is a raw and unorganized fact that is required to be processed to make it meaningful. It can
be considered as facts and statistics collected together for reference or analysis.

Data are individual units of information. In analytical processes, data are represented by
variables. Data is always interpreted, by a human or machine, to derive meaning. So, data is
meaningless. Data contains numbers, statements, and characters in a raw form.

Types of Data
There are two types of Data:

Quantitative: Quantitative data refers to numerical information like weight, height, etc.
Qualitative: Qualitative data refers to non-numeric information like opinions, perceptions, etc.
What is Information?

Information is defined as structured, organized, and processed data, presented within a context
that makes it relevant and useful to the person who needs it. Data suggests that raw facts and
figures regarding individuals, places, or the other issue, that is expressed within the type of
numbers, letters or symbols.

Information is the knowledge that is remodeled and classified into an intelligible type, which
may be utilized in the method of deciding. In short, once knowledge ends up being purposeful
when conversing, it’s referred to as info. It’s one thing that informs, in essence, it provides a
solution to a specific question. It may be obtained from numerous sources like newspapers, the
internet, television, people, books, etc.

Difference between Information and Data

[Link] DATA INFORMATION

Data is defined as unstructured Information refers to processed,

information such as text, organized, and structured data. It
observations, images, symbols, gives context for the facts and
and descriptions. In other words, facilitates decision making. In
data provides no specific other words, information is
function and has no meaning on processed data that makes sense
Definition its own. to us.

Data are the variables that help

Information is meaningful data.
Purpose to develop ideas/conclusions.

Data are text and numerical Information is refined form of

Nature values. actual data.

Data doesn’t rely on

While Information relies on Data.
Dependence Information.

Information is measured in
Bits and Bytes are the measuring
meaningful units like time,
unit of data.
Measurement quantity, etc.

Structure As tabular data, graphs, and data Information can also be

trees can be easily structured. structured as language, ideas, and
[Link] DATA INFORMATION

thoughts.

Information carries a meaning

Data does not have any specific
that has been assigned by
purpose
Purposefulness interpreting data.

Knowledge It is the second level of

It is low-level knowledge.
Level knowledge.

Decision Data does not directly help in Information directly helps in

Making decision making. decision making.

Data is a collection of facts, Information puts those facts into

Meaning which itself has no meaning. context.

Example of information is
Example of data is student test
average score of class that is
scores.
Example derived from given data.

DATABASE
A database is an organized collection of data stored in a computer system and usually managed
by a database management system (DBMS).
 The most commonly used types of databases are Relational and NoSQL.
 The data in relational databases is modeled in tables, making querying and processing
efficient. Structured query language (SQL) is commonly used for data querying and
writing.
 The data in NoSQL databases is modeled as key-value pairs.
 Examples of relational DBMS are MySQL, Oracle, Microdoft SQL Server and Postgre
SQL.
 Examples of NoSQL DBMS are MongoDB, Cassandra and DynamoDB
Applications of Databases:
 Databases are essential part of our life. We encounter several activities that involve our
interaction with databases, for example in the bank, in the railway station, in school, in
a grocery store, etc.
 Most of the applications that we use in our day to day life (like WhatsApp, Gmail,
Social Media Websites, etc) have a database that stores user, transaction and all other
required information in a database.
  New media technology has made it possible to store images, video clips. These
essential features are making multimedia databases.
 Databases are backbone for any business as they also allow businesses to do data
analysis and apply data science to take business decisions.
Databases can be stored at different locations:

Distributed Database: A distributed database is a database in which portions of the database

are stored in multiple physical locations, and in which processing is dispersed or replicated
among different points in a network.
Cloud Database: A cloud database is a database that typically runs on a cloud computing
platform. Database service provides access to the database. Database services make the
underlying software stack transparent to the user.
Types of Databases
There are many types of databases.

Relational Databases: A relational database’s contents are arranged as a collection of tables

with rows and columns. Accessing structured data is made most flexible and efficient by
relational database technology.
Object-Oriented Databases: Similar to object-oriented programming, data in an object-
oriented database is represented as objects.
Distributed Databases: A distributed database is made up of two or more files that are spread
across multiple locations. The database could be dispersed across many networks, housed in
one physical place, or kept on several computers.
Data Warehouses: A data warehouse is a sort of database created especially for quick query
and analysis. It is a central repository for data.
NoSQL Databases: Unlike relational databases, which specify how all data input must be
formatted, NoSQL, or nonrelational databases, permit the storing and manipulation of
unstructured and semistructured data. The prevalence and complexity of online applications led
to the rise in popularity of NoSQL databases.
Graph Databases: Data is stored in a graph database using entities and their relationships.
OLTP Database: An OLTP database is a quick, analytical database made to handle lots of
transactions from several users at once.
Open source databases: A database system that is open source can have either a SQL or
NoSQL database as its source code.
Cloud databases: A collection of organized or unorganized data that is housed on a private,
public, or hybrid cloud computing platform is known as a cloud database. Cloud database
models come in two flavors: traditional and database as a service (DBaaS). With DBaaS, a
service provider handles maintenance and administrative duties.

DATBASE MANAGEMENT SYSTEM

What is Database
The database is a collection of inter-related data which is used to retrieve, insert and delete the
data efficiently. It is also used to organize the data in the form of a table, schema, views, and
reports, etc.

For example: The college Database organizes the data about the admin, staff, students and
faculty etc.

Using the database, you can easily retrieve, insert, and delete the information.

Database Management System

o Database management system is a software which is used to manage the database.

For example: MySQL, Oracle, etc are a very popular commercial database which is
used in different applications.
o DBMS provides an interface to perform various operations like database creation,
storing data in it, updating data, creating a table in the database and a lot more.
o It provides protection and security to the database. In the case of multiple users, it also
maintains data consistency.
DBMS allows users the following tasks:

o Data Definition: It is used for creation, modification, and removal of definition that
defines the organization of data in the database.
o Data Updation: It is used for the insertion, modification, and deletion of the actual data
in the database.
o Data Retrieval: It is used to retrieve the data from the database which can be used by
applications for various purposes.
o User Administration: It is used for registering and monitoring users, maintain data
integrity, enforcing data security, dealing with concurrency control, monitoring
performance and recovering information corrupted by unexpected failure.

Characteristics of DBMS

o It uses a digital repository established on a server to store and manage the

information.
o It can provide a clear and logical view of the process that manipulates data.
o DBMS contains automatic backup and recovery procedures.
o It contains ACID properties which maintain data in a healthy state in case of failure.
o It can reduce the complex relationship between data.
o It is used to support manipulation and processing of data.
o It is used to provide security of data.
o It can view the database from different viewpoints according to the requirements of the
user.
Objectives OF DBMS
The main objectives of database management system are data availability, data integrity, data security, and
data independence.

[Link] Availability

Data availability refers to the fact that the data are made available to wide variety of users in a meaningful
format at reasonable cost so that the users can easily access the data.

2. Data Integrity

Data integrity refers to the correctness of the data in the database. In other words, the data available in the
database is a reliable data.

3 .Data Security

Data security refers to the fact that only authorized users can access the data. Data security can be enforced
by passwords. If two separate users are accessing a particular data at the same time, the DBMS must not
allow them to make conflicting changes.

4. Data Independence

DBMS allows the user to store, update, and retrieve data in an efficient manner. DBMS provides an “abstract
view” of how the data is stored in the database.

In order to store the information efficiently, complex data structures are used to represent the data. The system
hides certain details of how the data are stored and maintained

Advantages of DBMS

o Controls database redundancy: It can control data redundancy because it stores all
the data in one single database file and that recorded data is placed in the database.
o Data sharing: In DBMS, the authorized users of an organization can share the data
among multiple users.
o Easily Maintenance: It can be easily maintainable due to the centralized nature of the
database system.
o Reduce time: It reduces development time and maintenance need.
o Backup: It provides backup and recovery subsystems which create automatic backup
of data from hardware and software failures and restores the data if required.
o multiple user interface: It provides different types of user interfaces like graphical
user interfaces, application program interfaces

Disadvantages of DBMS

o Cost of Hardware and Software: It requires a high speed of data processor and large
memory size to run DBMS software.
o Size: It occupies a large space of disks and large memory to run them efficiently.
o Complexity: Database system creates additional complexity and requirements.
 o Higher impact of failure: Failure is highly impacted the database because in most of
the organization, all the data stored in a single database and if the database is
damaged due to electric failure or database corruption then the data may be lost
forever.

COMPONENTS OF DBMS
There are many components available in the DBMS. Each component has a significant task in the
DBMS. A database environment is a collection of components that regulates the use of data,
management, and a group of data. These components consist of people, the technique of Handel the
database, data, hardware, software, etc. there are several components available for the DBMS. We
are going to explain five main topics of the database below.

1. Hardware
o Here the hardware means the physical part of the DBMS. Here the hardware includes
output devices like a printer, monitor, etc., and storage devices like a hard disk.
o In DBMS, information hardware is the most important visible part. The equipment which is
used for the visibility of the data is the printer, computer, scanner, etc. This equipment is
used to capture the data and present the output to the user.
o With the help of hardware, the DBMS can access and update the database.
 o The server can store a large amount of data, which can be shared with the help of the
user's own system.
o The database can be run in any system that ranges from microcomputers to mainframe
computers. And this database also provides an interface between the real worlds to the
database.
o When we try to run any database software like MySQL, we can type any commands with
the help of our keyboards, and RAM, ROM, and processor are part of our computer
system.

2. Software
o Software is the main component of the DBMS.
o Software is defined as the collection of programs that are used to instruct the computer
about its work. The software consists of a set of procedures, programs, and routines
associated with the computer system's operation and performance. Also, we can say that
computer software is a set of instructions that is used to instruct the computer hardware for
the operation of the computers.
o The software includes so many software like network software and operating software. The
database software is used to access the database, and the database application performs
the task.
o This software has the ability to understand the database accessing language and then
convert these languages to real database commands and then execute the database.
o This is the main component as the total database operation works on a software or
application. We can also be called as database software the wrapper of the whole physical
database, which provides an easy interface for the user to store, update and delete the
data from the database.
o Some examples of DBMS software include MySQL, Oracle, SQL Server, dBase,
FileMaker, Clipper, Foxpro, Microsoft Access, etc.

3. Data
o The term data means the collection of any raw fact stored in the database. Here the data
are any type of raw material from which meaningful information is generated.
o The database can store any form of data, such as structural data, non-structural data, and
logical data.
o The structured data are highly specific in the database and have a structured format. But in
the case of non-structural data, it is a collection of different types of data, and these data
are stored in their native format.
o We also call the database the structure of the DBMS. With the help of the database, we
can create and construct the DBMS. After the creation of the database, we can create,
access, and update that database.
o The main reason behind discovering the database is to create and manage the data within
the database.
o Data is the most important part of the DBMS. Here the database contains the actual data
and metadata. Here metadata means data about data.
 o For example, when the user stores the data in a database, some data, such as the size of
the data, the name of the data, and some data related to the user, are stored within the
database. These data are called metadata.

4. Procedures
o The procedure is a type of general instruction or guidelines for the use of DBMS. This
instruction includes how to set up the database, how to install the database, how to log in
and log out of the database, how to manage the database, how to take a backup of the
database, and how to generate the report of the database.
o In DBMS, with the help of procedure, we can validate the data, control the access and
reduce the traffic between the server and the clients. The DBMS can offer better
performance to extensive or complex business logic when the user follows all the
procedures correctly.
o The main purpose of the procedure is to guide the user during the management and
operation of the database.
o The procedure of the databases is so similar to the function of the database. The major
difference between the database procedure and database function is that the database
function acts the same as the SQL statement. In contrast, the database procedure is
invoked using the CALL statement of the DBMS.
o Database procedures can be created in two ways in enterprise architecture. These two
ways are as below.
o The individual object or the default object.
o The operations in a container.
1. CREATE [OR REPLACE] PROCEDURE procedure_name (<Argument> {IN, OUT, IN OUT}
2. <Datatype>,...)
3. IS
4. Declaration section<variable, constant> ;
5. BEGIN
6. Execution section
7. EXCEPTION
8. Exception section
9. END

5. Database Access Language

o Database Access Language is a simple language that allows users to write commands to
perform the desired operations on the data that is stored in the database.
o Database Access Language is a language used to write commands to access, upsert, and
delete data stored in a database.
o Users can write commands or query the database using Database Access Language
before submitting them to the database for execution.
o Through utilizing the language, users can create new databases and tables, insert data
and delete data.
o Examples of database languages are SQL (structured query language), My Access,
Oracle, etc. A database language is comprised of two languages.
1. Data Definition Language(DDL):It is used to construct a database. DDL implements database
schema at the physical, logical, and external levels.
The following commands serve as the base for all DDL commands:

o ALTER<object>
o COMMENT
o CREATE<object>
o DESCRIBE<object>
o DROP<object>
o SHOW<object>
o USE<object>
2. Data Manipulation Language(DML): It is used to access a database. The DML provides the
statements to retrieve, modify, insert and delete the data from the database.

The following commands serve as the base for all DML commands:

o INSERT
o UPDATE
o DELETE
o LOCK
o CALL
o EXPLAIN PLAN

6. People
o The people who control and manage the databases and perform different types of
operations on the database in the DBMS.
o The people include database administrator, software developer, and End-user.
o Database administrator-database administrator is the one who manages the complete
database management system. DBA takes care of the security of the DBMS, its
availability, managing the license keys, managing user accounts and access, etc.
o Software developer- theThis user group is involved in developing and designing the parts
of DBMS. They can handle massive quantities of data, modify and edit databases, design
and develop new databases, and troubleshoot database issues.
o End user - These days, all modern web or mobile applications store user data. How do you
think they do it? Yes, applications are programmed in such a way that they collect user
data and store the data on a DBMS system running on their server. End users are the ones
who store, retrieve, update and delete data.
o The users of the database can be classified into different groups.

o Native Users
o Online Users
o Sophisticated Users
o Specialized Users
o Application Users
o DBA - Database Administrator
ARCHITECTURE OF DBMS

o The DBMS design depends upon its architecture. The basic client/server architecture is
used to deal with a large number of PCs, web servers, database servers and other
components that are connected with networks.
o The client/server architecture consists of many PCs and a workstation which are
connected via the network.
o DBMS architecture depends upon how users are connected to the database to get their
request done.

Types of DBMS Architecture

Database architecture can be seen as a single tier or multi-tier. But logically, database architecture is
of two types like: 2-tier architecture and 3-tier architecture.

1-Tier Architecture
o In this architecture, the database is directly available to the user. It means the user can
directly sit on the DBMS and uses it.
o Any changes done here will directly be done on the database itself. It doesn't provide a
handy tool for end users.
o The 1-Tier architecture is used for development of the local application, where
programmers can directly communicate with the database for the quick response.
2-Tier Architecture
o The 2-Tier architecture is same as basic client-server. In the two-tier architecture,
applications on the client end can directly communicate with the database at the server
side. For this interaction, API's like: ODBC, JDBC are used.
o The user interfaces and application programs are run on the client-side.
o The server side is responsible to provide the functionalities like: query processing and
transaction management.
o To communicate with the DBMS, client-side application establishes a connection with the
server side.

Fig: 2-tier Architecture

3-Tier Architecture
o The 3-Tier architecture contains another layer between the client and server. In this
architecture, client can't directly communicate with the server.
o The application on the client-end interacts with an application server which further
communicates with the database system.
o End user has no idea about the existence of the database beyond the application server.
The database also has no idea about any other user beyond the application.
o The 3-Tier architecture is used in case of large web application.
Fig: 3-tier Architecture

ER MODEL

BUILDING BLOCKS OF ER DIAGRAM

o ER model stands for an Entity-Relationship model. It is a high-level data model. This

model is used to define the data elements and relationship for a specified system.
o It develops a conceptual design for the database. It also develops a very simple and
easy to design view of data.
o In ER modeling, the database structure is portrayed as a diagram called an entity-
relationship diagram.
For example, Suppose we design a school database. In this database, the student will be an
entity with attributes like address, name, id, age, etc. The address can be another entity with
attributes like city, street name, pin code, etc and there will be a relationship between them.
Component of ER Diagram
1. Entity:
An entity may be any object, class, person or place. In the ER diagram, an entity can be
represented as rectangles.

Consider an organization as an example- manager, product, employee, department etc. can be

taken as an entity.

a. Weak Entity

An entity that depends on another entity called a weak entity. The weak entity doesn't contain
any key attribute of its own. The weak entity is represented by a double rectangle.

2. Attribute
The attribute is used to describe the property of an entity. Eclipse is used to represent an
attribute.

For example, id, age, contact number, name, etc. can be attributes of a student.

a. Key Attribute
The key attribute is used to represent the main characteristics of an entity. It represents a
primary key. The key attribute is represented by an ellipse with the text underlined.

b. Composite Attribute

An attribute that composed of many other attributes is known as a composite attribute. The
composite attribute is represented by an ellipse, and those ellipses are connected with an
ellipse.

c. Multivalued Attribute

An attribute can have more than one value. These attributes are known as a multivalued
attribute. The double oval is used to represent multivalued attribute.

For example, a student can have more than one phone number.
d. Derived Attribute

An attribute that can be derived from other attribute is known as a derived attribute. It can be
represented by a dashed ellipse.

For example, A person's age changes over time and can be derived from another attribute like
Date of birth.

3. Relationship
A relationship is used to describe the relation between entities. Diamond or rhombus is used to
represent the relationship.

Types of relationship are as follows:

a. One-to-One Relationship
When only one instance of an entity is associated with the relationship, then it is known as one
to one relationship.

For example, A female can marry to one male, and a male can marry to one female.

b. One-to-many relationship

When only one instance of the entity on the left, and more than one instance of an entity on the
right associates with the relationship then this is known as a one-to-many relationship.

For example, Scientist can invent many inventions, but the invention is done by the only
specific scientist.

c. Many-to-one relationship

When more than one instance of the entity on the left, and only one instance of an entity on the
right associates with the relationship then it is known as a many-to-one relationship.

For example, Student enrolls for only one course, but a course can have many students.

d. Many-to-many relationship

When more than one instance of the entity on the left, and more than one instance of an entity
on the right associates with the relationship then it is known as a many-to-many relationship.

For example, Employee can assign by many projects and project can have many employees.
Next Top