DATABASE MANAGEMENT

SYSTEM
212BCS2304
UNIT 1 - INTRODUCTION
What is Database?
A database is an organized collection of structured
information, or data, typically stored electronically in a
computer system.
Databases are used for storing, maintaining and accessing
any sort of data. They collect information on people, places
or things. That information is gathered in one place so that it
can be observed and analyzed. Databases can be thought of
as an organized collection of information.
What is DBMS?
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.
Database Management Systems (DBMS) are software systems used to
store, retrieve, and run queries on data. A DBMS serves as an interface
between an end-user and a database, allowing users to create, read,
update, and delete data in the database.
Examples of DBMS
Oracle
• Oracle Database is a commercial relational database management system. It
utilizes enterprise-scale database technology with a robust set of features right
out of the box. It can be stored in the cloud or on-premises.
MySQL
• MySQL is a relational database management system that is commonly used
with open-source content management systems and large platforms like
Facebook, Twitter, and Youtube.
SQL Server
• Developed by Microsoft, SQL Server is a relational database management
system built on top of structured query language (SQL), a standardized
programming language that allows database administrators to manage
databases and query data.
System applications
Railway Reservation System –
In the rail route reservation framework, the information base is needed to
store the record or information of ticket appointments,

Library Management System –

There are lots of books in the library so; it is difficult to store the record of the
relative multitude of books in a register or duplicate. Along these lines, the
data set administration framework (DBMS) is utilized to keep up all the data
identified with the name of the book, issue date, accessibility of the book, and
its writer.
Banking –
Database the executive’s framework is utilized to store the exchange data
of the client in the information base.

Education Sector –
Presently, assessments are led online by numerous schools and colleges.
They deal with all assessment information through the data set
administration framework (DBMS). In spite of that understudy’s
enlistments subtleties, grades, courses, expense, participation, results, and
so forth all the data is put away in the information base.

Credit card exchanges –

The database Management framework is utilized for buying on charge
cards and age of month to month proclamations.
Social Media Sites –
We all utilization of online media sites to associate with companions and to
impart our perspectives to the world. Every day, many people group pursue
these online media accounts like Pinterest, Facebook, Twitter, and Google in
addition to. By the utilization of the data set administration framework, all the
data of clients are put away in the information base and, we become ready to
interface with others.

Broadcast communications –
Without DBMS any media transmission organization can’t think. The Database
the executive’s framework is fundamental for these organizations to store the
call subtleties and month to month postpaid bills in the information base.

Account –
The information base administration framework is utilized for putting away data
about deals, holding and acquisition of monetary instruments, for example,
stocks and bonds in a data set.
Online Shopping –
These days, web-based shopping has become a major pattern. Nobody
needs to visit the shop and burn through their time. Everybody needs to
shop through web based shopping sites, (for example, Amazon, Flipkart,
Snapdeal) from home.

Human Resource Management –

Big firms or organizations have numerous specialists or representatives
working under them. They store data about worker’s compensation,
assessment, and work with the assistance of an information base
administration framework (DBMS).
Manufacturing –
Manufacturing organizations make various kinds of items and deal them
consistently. To keep the data about their items like bills, acquisition of
the item, amount, inventory network the executives, information base
administration framework (DBMS) is utilized.

Airline Reservation System –

This framework is equivalent to the railroad reservation framework.
This framework additionally utilizes an information base administration
framework to store the records of flight takeoff, appearance, and defer
status.

Healthcare: DBMS is used in healthcare to manage patient data,

medical records, and billing information.
Data retrieval: DBMS provides a way to retrieve data quickly and
easily using search queries.
Data manipulation: DBMS provides tools to manipulate data, such as
sorting, filtering, and aggregating data.
Security: DBMS provides security features to ensure that only
authorized users have access to the data.
Data backup and recovery: DBMS provides tools to back up data and
recover it in case of system failures or data loss.
Multi-user access: DBMS allows multiple users to access and modify
data simultaneously.
Reporting and analysis: DBMS provides tools to generate reports and
analyze data to gain insights and make informed decisions.
Purpose of Database System
The following are the main disadvantages of DBMS in File Processing:
Data redundancy and inconsistency.
Difficult in accessing data.
Data isolation.
Data integrity.
Concurrency is not possible.
Security Problems.
A database system’s primary goal is to facilitate data retrieval
and provide a dependable storage platform for essential data.
• Efficient storage and retrieval are allowed by structured
organization of data through database systems utilizing
predefined schemas and data models.
• DBMS maintains the reliability and accuracy of the
information and returns it through enforced constraints and
rules defined in the database schema that eliminates data
redundancy and anomalies, respectively.
• Protecting confidential data is crucial and database systems
successfully achieve this with their safeguards against
unauthorized access.
• Database systems prioritize the security of sensitive data with
their solid mechanisms in place to preserve data
confidentiality.
• The inclusion of strong security measures in database
systems ensures the protection of sensitive data and upholds
its confidentiality. Confidentiality and privacy of data are
maintained by utilizing resilient security measures within
database systems.
View of Data
View of data in DBMS narrate how the data is visualized at each level of data
abstraction? Data abstraction allow developers to keep complex data structures e complex
data structures through levels of abstraction.

There is one more feature that should be kept in mind i.e. the data independence. While
changing the data schema at one level of the database must not modify the data schema at
the next level.
Data Abstraction
• Data abstraction is hiding the complex data structure in order to simplify
the user’s interface of the system. It is done because many of the users
interacting with the database system are not that much computer trained to
understand the complex data structures of the database system.
• To achieve data abstraction, we will discuss a Three-Schema architecture.
Three-Schema Architecture:
• The main objective of this architecture is to have an effective separation
between the user interface and the physical database. So, the user never
has to be concerned regarding the internal storage of the database and it has a
simplified interaction with the database system.
• The three-schema architecture defines the view of data at three levels:
1.Physical level (internal level)
2.Logical level (conceptual level)
3.View level (external level)
1. Physical Level/ Internal Level

• The physical or the internal level schema describes how the data is stored in the
hardware. It also describes how the data can be accessed. The physical level
shows the data abstraction at the lowest level and it has complex data structures.
Only the database administrator operates at this level.

2. Logical Level/ Conceptual Level

It is a level above the physical level. Here, the data is stored in the form of the entity
set, entities, their data types, the relationship among the entity sets, user
operations performed to retrieve or modify the data and certain constraints on the data.
Well adding constraints to the view of data adds the security. As users are restricted to
access some particular parts of the database.
It is the developer and database administrator who operates at the logical or the
conceptual level.
3. View Level/ User level/ External level
• It is the highest level of data abstraction and exhibits only a part of the
whole database. It exhibits the data in which the user is interested. The
view level can describe many views of the same data. Here, the user
retrieves the information using different application from the database.
Database Languages in DBMS
• A DBMS has appropriate languages and interfaces to express database
queries and updates.
• Database languages can be used to read, store and update the data in the
database.
Types of Database Languages
1. Data Definition Language (DDL)
• DDL stands for Data Definition Language. It is used to define database structure or
pattern.
• It is used to create schema, tables, indexes, constraints, etc. in the database.
• Using the DDL statements, you can create the skeleton of the database.
• Data definition language is used to store the information of metadata like the number
of tables and schemas, their names, indexes, columns in each table, constraints, etc.
Here are some tasks that come under DDL:
• Create: It is used to create objects in the database.
• Alter: It is used to alter the structure of the database.
• Drop: It is used to delete objects from the database.
• Truncate: It is used to remove all records from a table.
• Rename: It is used to rename an object.
• Comment: It is used to comment on the data dictionary.
2. Data Manipulation Language (DML)
• DML stands for Data Manipulation Language. It is used for accessing and
manipulating data in a database. It handles user requests.
Here are some tasks that come under DML:
• Select: It is used to retrieve data from a database.
• Insert: It is used to insert data into a table.
• Update: It is used to update existing data within a table.
• Delete: It is used to delete all records from a table.
• Merge: It performs UPSERT operation, i.e., insert or update operations.
• Call: It is used to call a structured query language or a Java subprogram.
• Explain Plan: It has the parameter of explaining data.
• Lock Table: It controls concurrency.
3. Data Control Language (DCL)
• DCL stands for Data Control Language. It is used to retrieve the stored or
saved data.
• The DCL execution is transactional. It also has rollback parameters.
• (But in Oracle database, the execution of data control language does not have
the feature of rolling back.)
Here are some tasks that come under DCL:
• Grant: It is used to give user access privileges to a database.
• Revoke: It is used to take back permissions from the user.
There are the following operations which have the authorization of Revoke:
• CONNECT, INSERT, USAGE, EXECUTE, DELETE, UPDATE and
SELECT.
4. Transaction Control Language (TCL)
• TCL is used to run the changes made by the DML statement. TCL can be
grouped into a logical transaction.
Here are some tasks that come under TCL:
• Commit: It is used to save the transaction on the database.
• Rollback: It is used to restore the database to original since the last Commit.
Database design
Database Design can be defined as a set of procedures or collection of tasks
involving various steps taken to implement a database. Following are some
critical points to keep in mind to achieve a good database design:
1.Data consistency and integrity must be maintained.
2.Low Redundancy
3.Faster searching through indices
4.Security measures should be taken by enforcing various integrity constraints.
5.Data should be stored in fragmented bits of information in the most atomic
format possible.
Design process
• Determine the purpose of your database
This helps prepare you for the remaining steps.
• Find and organize the information required
Gather all of the types of information you might want to record in the
database, such as product name and order number.
• Divide the information into tables
Divide your information items into major entities or subjects, such as
Products or Orders. Each subject then becomes a table.
• Turn information items into columns
Decide what information you want to store in each table. Each item
becomes a field, and is displayed as a column in the table. For example, an
Employees table might include fields such as Last Name and Hire Date.
• Specify primary keys
Choose each table’s primary key. The primary key is a column that is
used to uniquely identify each row. An example might be Product ID or
Order ID.
• Set up the table relationships
Look at each table and decide how the data in one table is related to
the data in other tables. Add fields to tables or create new tables to
clarify the relationships, as necessary.
• Refine your design
Analyze your design for errors. Create the tables and add a few records
of sample data. See if you can get the results you want from your
tables. Make adjustments to the design, as needed.
• Apply the normalization rules
Apply the data normalization rules to see if your tables are structured
correctly. Make adjustments to the tables, as needed.
Database design lifecycle
1. Requirement Analysis
It’s very crucial to understand the requirements of our application so
that you can think in productive terms. And imply appropriate integrity
constraints to maintain the data integrity & consistency.
2. Logical & Physical Design
This is the actual design phase that involves various steps that are to be taken
while designing a database. This phase is further divided into two stages:
• Logical Data Model Design: This phase consists of coming up with a high-
level design of our database based on initially gathered requirements to
structure & organize our data accordingly. A high-level overview on paper is
made of the database without considering the physical level design, this phase
proceeds by identifying the kind of data to be stored and what relationship
will exist among those data.
• Physical Design of Data Model: This phase involves the implementation of
the logical design made in the previous stage. All the relationships among
data and integrity constraints are implemented to maintain consistency &
generate the actual database.
3. Data Insertion and testing for various integrity Constraints
Finally, after implementing the physical design of the database, we’re
ready to input the data & test our integrity. This phase involves testing
our database for its integrity to see if something got left out or, if
anything new to add & then integrating it with the desired application.
Logical Data Model Design
The logical data model design defines the structure of data and what relationship
exists among those data. The following are the major components of the logical
design:
• Data Models: Data modeling is a visual modeling technique used to get a
high-level overview of our database. Data models help us understand the needs
and requirements of our database by defining the design of our database
through diagrammatic representation. Ex: model, Network model, Relational
Model, object-oriented data model.
• Entity: Entities are objects in the real world, which can have certain properties
& these properties are referred to as attributes of that particular entity.
• There are 2 types of entities: Strong and weak entity, weak entity do not have
a key attribute to identify them, their existence solely depends on one 1-
specific strong entity & also have full participation in a relationship whereas
strong entity does have a key attribute to uniquely identify them.
 Relationships: How data is logically related to each other defines the relationship
of that data with other entities. In simple words, the association of one entity with
another is defined here.
 A relationship can be further categorized into – unary, binary, and ternary
relationships.
• Unary: In this, the associating entity & the associated entity both are the same.
Ex: Employee Manages themselves, and students are also given the post of
monitor hence here the student themselves is a monitor.
• Binary: This is a very common relationship that you will come across while
designing a database.
Ex: Student is enrolled in courses, Employee is managed by different managers,
One student can be taught by many professors.
• Ternary: In this, we have 3 entities involved in a single relationship. Ex: an
employee works on a project for a client. Note that, here we have 3 entities:
Employee, Project & Client.
• Attributes: Attributes are nothing but properties of a specific entity that define
its behavior. For example, an employee can have unique_id, name, age, date of
birth (DOB), salary, department, Manager, project id, etc.
• Normalization: After all the entities are put in place and the relationship
among data is defined, we need to look for loopholes or possible ambiguities
that may arise as a result of CRUD operations. To prevent various Anomalies
such as INSERTION, UPDATION, and DELETION Anomalies.
• Data Normalization is a basic procedure defined for databases to eliminate
such anomalies & prevent redundancy.
An Example of Logical Design
Physical Design
• The main purpose of the physical design is to actually implement the logical
design that is, show the structure of the database along with all the columns &
their data types, rows, relations, relationships among data & clearly define how
relations are related to each other.
Following are the steps taken in physical design
• Step 1: Entities are converted into tables or relations that consist of their
properties (attributes)
• Step 2: Apply integrity constraints: establish foreign key, unique key, and
composite key relationships among the data. And apply various constraints.
• Step 3: Entity names are converted into table names, property names are
translated into attribute names, and so on.
• Step 4: Apply normalization & modify as per the requirements.
• Step 5: Final Schemes are defined based on the entities & attributes derived in
logical design.
Database engine
• A database engine (or storage engine) is the underlying software
component that a database management system (DBMS) uses to
create, read, update and delete (CRUD) data from a database. Most
database management systems include their own
application programming interface (API) that allows the user to
interact with their underlying engine without going through the user
interface of the DBMS.
• The term "database engine" is frequently used interchangeably with
"database server" or "database management system". A "database
instance" refers to the processes and memory structures of the
running database engine
There are 2 types of database engines :
• Transactional Databases: In this type, we can roll back the write operations
on the database if they are left incomplete. These operations are known as
transactions. Majorly, modern engines are transactional.
• Non-Transactional Databases: Unlike transactional databases, they don’t
provide Rollback/Commit. Instead, we need to manually code to perform the
rollback operations.
Major role of DBMS engine
1.Database engines are responsible for managing and efficiently accessing,
storing, and retrieving data in a database.
2.There are several types of database engines, such as relational, object-
oriented, NoSQL, and NewSQL, each designed for specific data needs
and use-cases.
3.Performance, scalability, and data integrity are important factors to
consider when choosing a database engine for your project or
application.
The below are the different available engines in
MySQL:
• InnoDB
• MyISAM
• MEMORY
• MERGE
• CSV
• SEQUENCE

InnoDB: InnoDB is a storage engine for DBMS MySQL and MariaDB.It is

the default storage engine for MySQL versions 5.5 and higher. It replaced
MyISAM as the default engine.
• MyISAM: It is the default engine for MySQL DBMS versions prior to
5.5.MyISAM is a high-speed storage and retrieval storage engine. It doesn’t
support transactions. It is easy to copy between systems and has a small data
footprint. Mostly used in Web and Data Warehousing.
• CSV: It is used to store CSV format in a text file. It is always compiled into
the MySQL server. It is lightweight and can be easily imported into
spreadsheet programs.
• MERGE: Tables created using the MERGE engine are used to handle a large
volume of data easily. It is a collection of identical MyISAM tables that can be
used as one. Thus, it is also known as the MRG_MyISAM engine. It is good
for data warehousing environments.
• MEMORY: It is considered to be the fastest engine used to usually create
temporary tables in memory (also known as HEAP). Thus data is lost when
the database is restarted. It is non-transactional. It is useful for quick looks up
of references and other identifying data. It stores all data in RAM for faster
access rather than storing data in disks. It is widely used for read-only caches
of data from tables or for temporary usage.
• SEQUENCE: It is the transactional engine used for creating ascending or
descending sequences of positive integers with given initial, end, and
increment values. It is read-only and supports XA.
DBMS Architecture
• 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.
• The client/server architecture consists of many PCs and a workstation
which are connected via the network.
• DBMS architecture depends upon how users are connected to the
database to get their request done.
1-Tier Architecture
• In this architecture, the database is directly available to the user. It
means the user can directly sit on the DBMS and uses it.
• Any changes done here will directly be done on the database itself. It
doesn't provide a handy tool for end users.
• The 1-Tier architecture is used for development of the local
application, where programmers can directly communicate with the
database for the quick response.
Advantages of 1-Tier Architecture
• Simple Architecture: 1-Tier Architecture is the most simple
architecture to set up, as only a single machine is required to maintain
it.
• Cost-Effective: No additional hardware is required for implementing
1-Tier Architecture, which makes it cost-effective.
• Easy to Implement: 1-Tier Architecture can be easily deployed, and
hence it is mostly used in small projects.
2-Tier Architecture

• 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.
• The user interfaces and application programs are run on the client-side.
• The server side is responsible to provide the functionalities like: query processing and
transaction management.
• To communicate with the DBMS, client-side application establishes a connection with the
server side.
Advantages of 2-Tier Architecture
• Easy to Access: 2-Tier Architecture makes easy access to the database,
which makes fast retrieval.
• Scalable: We can scale the database easily, by adding clients or
upgrading hardware.
• Low Cost: 2-Tier Architecture is cheaper than 3-Tier Architecture and
Multi-Tier Architecture.
• Easy Deployment: 2-Tier Architecture is easier to deploy than 3-Tier
Architecture.
• Simple: 2-Tier Architecture is easily understandable as well as simple
because of only two components.
3-Tier Architecture

• The 3-Tier architecture contains another layer between the client and server. In this
architecture, client can't directly communicate with the server.
• The application on the client-end interacts with an application server which further
communicates with the database system.
• 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.
• The 3-Tier architecture is used in case of large web application.
Advantages of 3-Tier Architecture

• Enhanced scalability: Scalability is enhanced due to the distributed

deployment of application servers. Now, individual connections need not
be made between the client and server.
• Data Integrity: 3-Tier Architecture maintains Data Integrity. Since there
is a middle layer between the client and the server, data corruption can be
avoided/removed.
• Security: 3-Tier Architecture Improves Security. This type of model
prevents direct interaction of the client with the server thereby reducing
access to unauthorized data.
Database users and Administrators
 Naive / Parametric End Users : Parametric End Users are the
unsophisticated who don’t have any DBMS knowledge but they frequently
use the database applications in their daily life to get the desired results. For
examples, Railway’s ticket booking users are naive users. Clerks in any bank
is a naive user because they don’t have any DBMS knowledge but they still
use the database and perform their given task.
 System Analyst :
System Analyst is a user who analyzes the requirements of parametric end
users. They check whether all the requirements of end users are satisfied.
• Sophisticated Users : Sophisticated users can be engineers, scientists,
business analyst, who are familiar with the database. They can develop their
own database applications according to their requirement. They don’t write the
program code but they interact the database by writing SQL queries directly
through the query processor.
• Database Designers : Data Base Designers are the users who design the
structure of database which includes tables, indexes, views, triggers, stored
procedures and constraints which are usually enforced before the database is
created or populated with data.
 Application Programmers : Application Programmers also referred as
System Analysts or simply Software Engineers, are the back-end
programmers who writes the code for the application programs.
 Casual Users / Temporary Users : Casual Users are the users who
occasionally use/access the database but each time when they access the
database they require the new information, for example, Middle or higher
level manager.
 Specialized users : Specialized users are sophisticated users who
write specialized database application that does not fit into the traditional
data- processing framework. Among these applications are computer aided-
design systems, knowledge-base and expert systems etc.
Database Administrator (DBA)
• A Database Administrator (DBA) is an individual or person responsible
for controlling, maintaining, coordinating, and operating a database
management system.
• Managing, securing, and taking care of the database systems is a prime
responsibility.
• They are responsible and in charge of authorizing access to the database,
coordinating, capacity, planning, installation, and monitoring uses, and
acquiring and gathering software and hardware resources as and when
needed
Types of Database Administrator (DBA) :

• Administrative DBA –
Their job is to maintain the server and keep it functional. They are concerned
with data backups, security, troubleshooting, replication, migration, etc.
• Data Warehouse DBA –
Assigned earlier roles, but held accountable for merging data from various
sources into the data warehouse. They also design the warehouse, with
cleaning and scrubs data prior to loading.
• Cloud DBA –
Nowadays companies are preferring to save their workpiece on cloud storage.
As it reduces the chance of data loss and provides an extra layer of data
security and integrity.
• Development DBA –
They build and develop queries, stores procedure, etc. that meets firm or
organization needs. They are par at programming.
• Application DBA –
They particularly manage all requirements of application components that
interact with the database and accomplish activities such as application
installation and coordination, application upgrades, database cloning, data load
process management, etc.
• Architect –
They are held responsible for designing schemas like building tables. They
work to build a structure that meets organizational needs. The design is further
used by developers and development DBAs to design and implement real
applications.
• OLAP DBA –
They design and build multi-dimensional cubes for determination support or
OLAP systems.
• Data Modeler –
In general, a data modeler is in charge of a portion of a data architect’s duties.
A data modeler is typically not regarded as a DBA, but this is not a hard and
fast rule.
• Database Analyst –
This position doesn’t actually have a set definition. Junior DBAs may
occasionally be referred to as database analysts. A database analyst
occasionally performs functions that are comparable to those of a database
architect.