DATABASEMANAGEMENTSYSTEM BCAIIISEM

UNITI

WhatisData?

Data is a collection of a distinct small unit of information. It can be used in a

variety of forms
liketext,numbers,media,bytes,etc.itcanbestoredinpiecesofpaperorelectronicmem
ory, etc.

Word 'Data' is originated from the word 'datum'that means 'single pieceof
information.' It is plural of the word datum.

In computing, Data is information that can be translated into a form for efficient
movement and processing. Data is interchangeable.

Whatis 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.

Adatabaseisanorganizedcollectionofdata,sothatitcanbeeasilyaccessedand
managed.

You can organize data into tables, rows, columns, and index it to make it easier
to find relevant information.

Themain purposeof the database is to operate a large amount of information by

storing, retrieving, and managing data.

DBMS:

A database management system (DBMS) is a computerized system that

enables users to create and maintain a database. The DBMS is a general-purpose
software system that facilitates the processes of defining, constructing,
manipulating, and sharing databasesamong various users and applications.
Defining a database involves specifying the data types, structures, and
constraints of the data to be stored in the database. The database definition or
descriptive information is also stored by the DBMS in the form of a database
catalog or dictionary; it is called meta-data. Constructing the database is the
process of storing the data on some storage medium that is controlled by the
DBMS. Manipulating a database includes functions such as querying the
database to retrieve specific data, updating

Prof.Akshata Pawar 1
 DATABASEMANAGEMENTSYSTEM BCAIIISEM
the database to reflect changes in the miniworld, and generating reports from
the data. Sharing a database allows multiple users and programs to access the
database simultaneously.

o DBMSFullformisDatabaseManagement System

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

database. For example:MySQL,Oracle,etcare averypopular
commercialdatabase whichis 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, italso maintains data consistency.

DBMSallowsusersthefollowing tasks:

Prof.Akshata Pawar 2
 DATABASEMANAGEMENTSYSTEM BCAIIISEM
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 datafrom 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.

DatabaseApplications

Nowadays, any business that has small or large amounts of data needs a
database to store and manage the information. The database is an easy, reliable,
secure, and efficient way to maintain business information. There are many
applications where databases are used.

In this article, we will discuss some of the applications of databases, which are
mentioned below:

Prof.Akshata Pawar 3
 DATABASEMANAGEMENTSYSTEM BCAIIISEM

1.Universities:

It is an undeniable application of the database. Universities have so much data

which can be stored in the database, such as student information, teacher
information, non-teaching staff information, course information, section
information, grade report information, and many more. University information is
kept safe and secure in the database.

Anyone who needs information about the student, teacher, or course can easily
retrieve it from the database. Everything needs to be maintained because even
after ten years, information may be required, and the information may be useful,
so maintaining complete information is the primary responsibility of any
university or educational institution.

2.Banking:

It is one of the major applications of databases. Banks have a huge amount of

data asmillions of people have accounts that need to be maintained properly.
The database keeps the record of each user in a systematic manner. Banking
databases store a lot of information about account holders. It stores customer
details, asset details, banking transactions, balance sheets, credit card and debit
card details, loans, fixed deposits, and much more. Everything is maintained with
the help of a database.

Prof.Akshata Pawar 4
 DATABASEMANAGEMENTSYSTEM BCAIIISEM
3.RailwayReservationSystem:

It is an inevitable area of application of databases. They store information such

as passenger name, mobile number, booking status, reservation details, train
schedule, employee information, account details, seating arrangement, route &
alternate route details, etc. All the information needs to be maintained, so
railways use a database management system fortheir efficient storage and
retrieval purpose.

4.SocialMediaSites:

Nowadays, everyone has a smartphone and accounts on various social media

sites like Facebook, LinkedIn, Pinterest, Twitter, Instagram, etc. People can chat
with their friends and family and make new friends from all over the world. Social
media has millions of accounts, which means they have a huge amount of data
that needs to be stored and maintained. Social media sites use databases to
store information about users, images, videos, chats, etc.

5.LibraryManagementSystem:

There are hundreds and thousands of books in the library, so it is not easy to
maintain the records of the books in a register or diary, so a database
management system is used which maintains the information of the library
efficiently. The library database stores informationlike book name, issue date,
author name, book availability, book issuer name, book return details, etc.

6.E-commerceWebsites:

E-commerce websites are one of the prominent applications of the database.

Websites such as Flipkart, Myntra, Amazon, Nykaa, Snapdeal, Shopify, and many
more, are online shopping websites where people buy items online. These
websites have so much data. These websites usedatabases to securelystoreand
maintain customerdetails,productdetails, dealer details, purchase details, bank &
card details, transactions details, invoice details, etc. You cananalyze the sales
and maintain the inventory with the help of a database.

7.Medical:

There is a lot of important data collection in the medical field, so it is necessary

to use the database to store data related to the medical field, such as patient
details, medicine details, practitioner details, surgeon details, appointment
details, doctor schedule, patient discharge details, payment detail, invoices, and
other medical records. The database management system is a boon for the
medical field because it helps doctors to monitor their patients and provide
better care.

Prof.Akshata Pawar 5
 DATABASEMANAGEMENTSYSTEM BCAIIISEM
8.AccountingandFinance:

When there is big data regarding accounting and finance, there is a need to
maintain a large amount of data, which is done with the help of a database. The
database stores data such as accounting details, bank details, purchases of
stocks, invoice details, sales records, asset details, etc. Accounting and finance
database helps in maintaining and analyzing historical data.

9.Industries:

The database management system is the main priority of industries because

they need to store huge amounts of data. The industry database stores customer
details, sales records, product lists, transactions, etc. All the information is kept
secure and maintained by the database.

10. AirlineReservationSystem:

It is one of the applications of database management systems that contain data

such as passengername,passengercheck-in,
passengerdeparture,flightschedule,numberofflights, distance from source to
destination, reservation information, pilot details, accounting detail, route detail,
etc. The database provides maintenance and security to airline data.

11. Telecommunication:

We cannot deny that telecommunication has brought a remarkable revolution

worldwide.The Telecom field has huge data, and it is very difficult to manage big
data without a database; that is why a telecom database is required, which
stores data such as customer names, phone numbers, calling details, prepaid &
post-paid connection records, network usage, bill details, balance details, etc.

12. Manufacturing:

In the manufacturing field, a lot of data needs to be maintained regarding supply

chain management, so the database maintains the data such as product details,
customer information, order details, purchase details, payment info, worker's
details, invoice, etc. Manufacturing companies produce and supply products
every day, so it is important to use a database.

13. HumanResourceManagement:

Any organization will definitely have employees, and if there are a large number
of employees, then it becomes essential to store data in a database as it
maintains and securely

Prof.Akshata Pawar 6
 DATABASEMANAGEMENTSYSTEM BCAIIISEM
saves the data, which can be retrieved and accessed when required. The human
resource database stores data such as employee name, joining details,
designation, salary details, tax information, benefits & goodies details, etc.

14. Broadcasting:

Broadcasting is distributing video and audio content to a dispersed audience by

television, radio, or other means. Broadcasting database stores data such as
subscriber information, event recordings, event schedules, etc., so it becomes
important to store broadcasting datain the database.

15. Insurance:

An insurance company needs a database to store large amounts of data.

Insurance database stores data such as policy details, user details, buyer details,
payment details, nomineedetails, address details, etc.

CharacteristicsofDatabaseapproachinDBMS

A database control machine (DBMS) is a software program gadget that permits

the creation, organization, management, and retrieval of records in a dependent
manner. The database method is a way of organizing information in a DBMS that
has distinct characteristics that make it distinctive from different techniques. In
this newsletter, we will speak about the characteristics of the database technique
in DBMS.

 Self-describingnatureofadatabasesystem
A fundamental characteristic of the database approach is that the database
system contains not only the database itself but also a complete definition or
description of the database structure and constraints. This definition is stored in
the system catalog, which contains information such as the structure of each file,
the type and storage format of each data item, and various constraints on the
data. The information stored in the catalog is called meta-data, and it describes
the structure of the primary database. The catalog is used by the DBMS software
and also by database users who need information about the database structure.
A general purpose DBMS software package is not written for a specific database
application, and hence it must refer to the catalog to know the structure of the
files in a specificdatabase, such as thetype and format ofdata it willaccess.
TheDBMS software must work equally well with any number of database
applications—for example, a university database, a banking database, or a
company database—as long as the database definition is stored in the catalog.

Prof.Akshata Pawar 7
 DATABASEMANAGEMENTSYSTEM BCAIIISEM
In traditional file processing, data definition is typically part of the
application programs themselves. Hence, these programs are constrained to
work with only one specific database, whose structure is declared in the
application programs.

 InsulationbetweenProgramsandData,andDataAbstraction
In traditional file processing, the structure of data files is embedded in the access
programs, so any changes to the structure of a file may require changing all
programs that access this file. By contrast, DBMS access programs do not require
such changes in most cases. The structure of data files is stored in the DBMS
catalog separately from the access programs (figure 1.4)

We call this property program-data independence. For example, a file access

program maybe written in such a way that it can access only STUDENT records of
the structure shown in Figure 1.4. If we want to add another piece of data to
each STUDENT record, say the Birthdate, such a program will no longer work and
must be changed. By contrast, in a DBMS environment, we just need to change
the description of STUDENT records in the catalog to reflect the inclusion of the
new data item Birthdate; no programs are changed. The next time a DBMS
program refers to the catalog, the new structure of STUDENT records will be
accessed and used.

 SupportofMultipleViewsoftheData

Prof.Akshata Pawar 8
 DATABASEMANAGEMENTSYSTEM BCAIIISEM
A database typically has many users, each of whom may require a different
perspective or view of the database. A view may be a subset of the database or
it may contain virtual data that is derived from the database files but is not
explicitly stored. Some users may not need to be aware of whether the data they
refer to is stored or derived. A multiuser DBMS whose users have a variety of
applications must provide facilities for defining multiple views.
For example, one user of the database of Figure 1.4 may be interested only in
the transcript ofeach student; theviewfor this user is shown in Figure1.2. A
second user, who isinterested only in checking that students have taken all the
prerequisites of each course they register for, may require the view shown in

Figure 1.2(b).

 SharingofDataandMultiuserTransaction Processing
A multiuser DBMS, as its name implies, must allow multiple users to access the
database at the same time. This is essential if data for multiple applications is to
be integrated and maintained in a single database. The DBMS must include
concurrency control software to ensurethatseveraluserstryingto
updatethesamedatadoso inacontrolledmannerso that the result of the updates is
correct. For example, when several reservation clerks try to assign a seat on an
airline flight, the DBMS should ensure that each seat can be accessed by only
one clerk at a time for assignment to a passenger. These types of applications
are generally called on-line transaction processing (OLTP) applications. A
fundamental role of multiuser DBMS software is to ensure that concurrent
transactions operate correctly.

Prof.Akshata Pawar 9
 DATABASEMANAGEMENTSYSTEM BCAIIISEM

PurposeofDatabaseSystem
In
DBMS,databasesystemsprovideasafeandeffectiveplatformtomanagevastamounts
of data.Theirroleistoprovideserviceslike dataorganization,
storage,andmanipulation, as well as to guarantee data integrity. 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.
 Collaboration made easy with DBMS. With the provision of a platform to
accessand manipulate data, multiple users can now work together and
ensure data consistency across various applications. Data sharing and
collaboration are now synonymous with the help of DBMS.
 Databackupsandtransactionmanagementaremechanismsprovidedby
database systems to ensure data durability. Safeguarding data against
system crashes and failures is their main priority.

AdvantagesofDBMS

o Controls database redundancy:Itcancontroldataredundancybecauseit

storesallthe data in one single database file and that recorded data is placed
in the database.
o Datasharing:InDBMS,theauthorizedusersofanorganizationcansharethedata
among multiple users.

Prof.Akshata Pawar 10
 DATABASEMANAGEMENTSYSTEM BCAIIISEM
o Easily Maintenance:It can be easily maintainable due to the centralized
nature of thedatabase system.
o Reducetime:Itreducesdevelopmenttimeandmaintenanceneed.
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

Disadvantagesof DBMS

o Cost of Hardwareand Software:Itrequiresahighspeedof

dataprocessorandlarge memory size to run DBMS software.
o Size:Itoccupiesalargespaceofdisksandlargememorytorunthem efficiently.
o Complexity:Databasesystemcreatesadditionalcomplexityandrequirements.
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.

Manypersonsareinvolvedinthedesign,use,andmaintenance ofalargedatabasewitha
few hundred users. Here we will consider people who may

PeopleassociatedwithDatabasesystem.

Prof.Akshata Pawar 11
 DATABASEMANAGEMENTSYSTEM BCAIIISEM
ACTORSONTHESCENE

For a small personal database, such as the list of address discussedone

person typically defines, constructs, and manipulates the database.
However, many persons are involved in the design, use, and maintenance
of a large database with a few hundred users. In this section we identify
the people whose jobs involve the day to day use of large database; we
call them the actors on the scene.
1) Databaseadministrator-DBA:

In any organization where many people use the same resources, there is a
need for a chief
Administratortooverseeandmanagetheseresources.Inadatabaseenvironment, the
primary resource is the database itself, and secondary resource is the DBMS and
related software. Administering these resources 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. The DBA is accountable for problems such as
security breaches and poor system response time.

FunctionsorResponsibilitiesof DBA

 Authorizeaccesstothedatabase.
 Facilitatecoordinateuseofthedatabase.
 InstallandupgradetheDBMSandApplicationtoolsasandwhennecessary.
 Backingupandrestoringthedataduring failures.
 Tunethedatabasetooptimizeitsperformancewheretheuserneeds.
 Enrollusers,controlandmonitoruseraccesstothedatabaseinorderto
overcome the security breaches.

2) Databasedesigners
Database designers are responsible for identifying the data to be stored
in the databaseand For choosing appropriatestructuresto represent and
storethisdata. It is the responsibility Of database designers to
communicate with all prospective database users in order to
understand their requirements and to create a design that meets these
requirements. In many case, the designers are on the staff of the DBA
and may be assigned other staff responsibilities after the database
design is completed. Database designers typically interact with each
potential group ofusers and develop views of the database that
meetsthe data and processing requirements of these groups.

Prof.Akshata Pawar 12
 DATABASEMANAGEMENTSYSTEM BCAIIISEM
3) Endusers

Endusersarethepeopleswhosejobsrequiresaccesstothedatabasefor
querying,updating,andgeneratingreports,thedatabaseprimarilyexistsfortheiruse
.
Thereareseveralcategoriesofendusers

a) Casual end users: occasionally access the database, but they may
need different information each time. They use sophisticated
database query interface to specify their requirements and are
typically middle or high level managers or other occasional
browsers.
b) Naive or parametric end users make up a sizable portion of
database end users. Their main job function revolves around
constantly querying and updating that database, using standard
types of queries and updates—called canned transactionthat
have been carefully programmed and tested.
Manyofthesetasksarenowavailableasmobileappsforusewithmobile
devices.Thetasksthatsuchusersperformarevaried. Afewexamplesare:

o Bank customers and tellers check account balances and post

withdrawals and deposits.
o Reservation agents or customers for airlines, hotels, and car
rental companies check availability for a given request and make
reservations.

o Employees at receiving stations for shipping companies enter

package identifications via bar codes and descriptive information
through buttons update a central database of received and in-
transit packages.
o SocialmediauserspostandreaditemsonsocialmediaWeb sites.

c) Sophisticated end users include engineers, scientists, business

analysts, and others who thoroughly familiarize themselves with the
facilities of the DBMS in order to implement their own applications to
meet their complex requirements.

d) StandAloneusersmaintainpersonaldatabasesbyusingready-
madeprogram packages that provide easy-to-use menu-based or
graphics-based interfaces. An example is the user of a financial
software package that stores a variety of personal financial data.

3.Systemanalystandapplicationprogrammers(SoftwareEngineers)
System analysts determine the requirements of end users, especially
naive and parametric end users and develop specifications for standard
canned

Prof.Akshata Pawar 13
 DATABASEMANAGEMENTSYSTEM BCAIIISEM
transactionstomeettheirrequirement.Applicationprogrammersimplementt
hese

Prof.Akshata Pawar 14
 DATABASEMANAGEMENTSYSTEM BCAIIISEM
specifications as programs; then they test, debug, document, and
maintain these canned transactions. Such analysts and programmers
commonly referred to as software developers or software engineers—
should be familiar with the full rangeof capabilities provided by the
DBMS to accomplish their tasks.

WORKERSBEHINDTHESCENE

In addition to those who design, use, and administer a database, others are
associatedwith the design, development, and operation of the DBMS software
and system environment. These persons are typically not interested in the
database content itself. We call them the workers behind the scene, and they
include the following categories:

DBMS system designers and implementers: design and implement the

DBMS modules and interfaces as a software package. A DBMS is a very
complex software system that consists of many components, or modules,
including modules for implementing the catalog, query language processing,
interface processing, accessing and buffering data, controlling concurrency,
and handling data recovery and security. The DBMS mustinterface with other
system software such as the operating system and compilers for various
programming languages.

Tool developers design and implement tools:

the software packages that facilitate database modeling and design, database
system design, and improved performance. Tools are optional packages that
are often purchased separately. They include packages for database design,
performance monitoring, natural language or graphical interfaces, prototyping,
simulation, and test data generation. In many cases, independent software
vendors develop and market these tools.

Operators and maintenance personnel (system administration

personnel) are responsible for the actual running and maintenance of the
hardware and software environment for the
database system

Prof.Akshata Pawar 15
DatabaseManagementSystem

DATA MODELS

A Data model is a collection of concepts that can be used to

describe the structure of a database (data types, relationships, and
constraints) that should hold for the data. Most data model also
includes a set of basic operations for specifying (retrieval and
updates) on the database.

a. DataModelgivesusanideathathowthefinalsystemwilllooklik
e after its complete implementation.
b. It defines the data elements and the relationships
between the data elements. Data Models are used to
show how data isstored, connected, accessed and
updated in the database management system.
c. Here,weuseasetofsymbolsandtexttorepresenttheinformati
on so that members of the organization can communicate
and understand it.
d. Though there are many data models being used
nowadays but the Relational model is the most widely
used model.
e. ApartfromtheRelationalmodel,therearemanyothertype
sof data models about which we will study in details in
this blog.

SomeoftheDataModelsinDBMSare:

1. HierarchicalModel
2. NetworkModel
3. Entity-RelationshipModel
4. RelationalModel
5. Object-OrientedDataModel
6. Object-RelationalDataModel
7. FlatDataModel
8. Semi-StructuredDataModel
9. AssociativeData Model
10. ContextDataModel

1. HierarchicalModel
1. HierarchicalModelwasthefirstDBMSmodel.Thismodel
organizes the data in the hierarchical tree structure.
2. Thehierarchystartsfromtherootwhichhasrootdataandthenit

Prof.KavitaLand 16
age
DatabaseManagementSystem

expandsintheformofatreeaddingchildnodetotheparent node.
3. This model easily represents some of the real-
world relationships like food recipes, sitemap of a
website etc.
4. Example:Wecanrepresenttherelationshipbetweentheshoe
s present on a shopping website in the following way:

AdvantagesofHierarchicalModel
a. Itisverysimpleandfasttotraversethroughatree-likestructure.
b. Anychangeintheparentnodeisautomaticallyreflectedin
the child node so, the integrity of data is maintained.

DisadvantagesofHierarchicalModel
a. Complexrelationshipsarenotsupported.
b. As it does not support more than one parent of the
childnode so if we have some complex relationship
where a child node needs to have two parent node
then that can't be represented using this model.
c. Ifaparentnodeisdeleted,thenthechildnodeisautomatically deleted

2. NetworkModel

a. This model is an extension of the hierarchical model. It

was the most popular model before the relational
model.
b. Thismodel isthe same asthe hierarchical model, the
onlydifference is that a record can have more than one
parent. It replaces the hierarchical tree with a graph.
c. Example: In the example below we can see that node
student has
twoparentsi.e.CSEDepartmentandLibrary.Thiswasearlierno
t

Prof.KavitaLand 17
age
DatabaseManagementSystem

possibleinthehierarchicalmodel.

FeaturesofaNetworkModel

1. Ability to Merge more Relationships: In this model, as there are

more relationships so
data is more related. This model has the ability to manage one-
to-one relationships as well as many-to-many relationships.
2. Many paths: As there are more relationships so there can
be more than one path to the same record. This makes data
access fast and simple.
3. Circular Linked List: The operations on the network model
are done
withthehelpofthecircularlinkedlist.Thecurrentpositionismaintain
ed with the help of a program and this
positionnavigatesthroughtherecordsaccordingtotherelationship.

AdvantagesofNetworkModel

a. The data can be accessed faster as compared to the

hierarchical model. This is because the data is more related
in the network model and there can be more than one path
to reach a particular node. So the data can be accessed in
many ways.
b. As there is a parent-child relationship so data integrity is
present. Any change in parent record is reflected in the
child record.

DisadvantagesofNetworkModel

Prof.KavitaLand 18
age
DatabaseManagementSystem

a. As more and more relationships need to be handled the

system
mightgetcomplex.So,ausermustbehavingdetailedknowledge
of the model to work with the model.
b. Anychangelikeupdate,deletion,insertionisverycomplex

3. Entity-RelationshipModel

Entity-Relationship Model or simply ER Model is a high-level data

model diagram.
In thismodel, werepresent thereal-world problem in
thepictorial form to make it easy for the stakeholders to
understand.
It is also very easy for the developers to understand the
system by just looking at the ER diagram. We use the ER
diagram as a visual tool to represent an ER Model.

AdvantagesofERModel

Simple:ConceptuallyERModelisveryeasytobuild.Ifweknowtherelations
hip between the attributes and the entities we can easily build the ER
Diagram for the model.
Effective Communication Tool: This model is used widely by the
database designers for communicating their ideas.
Easy Conversion to any Model: Thismodel maps well to
therelational model and can be easily converted relational
model by converting the ER model to the table. This model can
also be converted to any other model

Prof.KavitaLand 19
age
DatabaseManagementSystem

likenetworkmodel,hierarchicalmodeletc.

DisadvantagesofERModel

No industry standard for notation: There is no industry

standard for
developinganERmodel.Soonedevelopermightusenotationswhich
are not understood by other developers.

Hidden information: Some information might be lost or

hidden in the ER model. As it is a high-level view so there are
chances that some details of information might be hidden

4. RelationalModel

RelationalModelisthe most widely used model. In this model, the

datais maintained in the form of a two-dimensional table. All the
information is stored in the form of row and
columns.
Thebasicstructureof
arelationalmodelistables.So,thetablesarealso called relations
in the relational model. Example: In this example, we have an
Employee table

FeaturesofRelationalModel

Tuples: Each row in the table is called tuple. A row contains

all the information about any instance of the object. In the
above example, each row has all the information about any
specificindividuallikethefirstrowhasinformation about John.

Prof.KavitaLand 20
age
DatabaseManagementSystem

Attributeorfield:Attributesarethepropertywhichdefinesthet
able or relation. The values of the attribute should be from the
samedomain. In the above example, we have different
attributes of the employee like Salary, Mobile_no, etc.

AdvnatagesofRelational Model

Simple:Thismodelismoresimpleascomparedto
thenetworkandhierarchical model.
Scalable: This model can be easily scaled as we can add as
many rows and columns we want.
StructuralIndependence:
Wecanmakechangesindatabasestructure
withoutchangingthewaytoaccessthedata.Whenwecanmakechang
es to the database structure without affecting the capability to
DBMS to access the data we can say that structural
independence has been achieved.

DisadvantagesofRelationalModel

Hardware Overheads: For hiding the complexities and

making things easier for the user this model requires more
powerful hardwarecomputers and data storage devices.
Bad Design: As the relational model is very easy to design and use.
So theusers don't
need to know how the data is stored in order to access it. This ease of
design can lead to the development of a poor database which would
slow down if the database grows

5. Object-OrientedDataModel
1. Thereal-
worldproblemsaremorecloselyrepresentedthrough the
object-oriented data model. In this model, both the
data and relationship are present in a single
structureknownasanobject.

2. Wecanstoreaudio,video,images,etcinthedatabasewhichw
as not possible in the relational model(although you can
store audio and video in relational database, it is
adviced not to store in the relational database). In this
model, two are more objects are connected through links.
3. Weusethislinktorelateoneobjecttootherobjects.Thiscan

Prof.KavitaLand 21
age
DatabaseManagementSystem

beunderstoodbytheexamplegiven below

1. Intheaboveexample,wehavetwoobjectsEmployeeand Department
2. Allthedataandrelationshipsofeachobjectarecontainedasasingleunit
3. The attributes like Name, Job_title of the employee and
the methodswhich willbe
performedbythatobjectarestoredas a single object.
4. The two objects are connected through a common
attribute i.e the Department_id and the communication
between these two will be done with the help of this
common id.

6. FlatDataModel

It is a simple model in which the database is represented as a table

consisting of rows and
columns. To access any data, the computer has to read the
entire table.This makes the modes slow and inefficient.
7. Semi-StructuredModel
 Semi-structuredmodelisanevolvedformoftherelationalmodel.
 Wecannotdifferentiatebetweendataandschemainthismodel.
 Example:Web-Baseddata sources which we
can'tdifferentiate between the schema and data of the
website.
 In this model, some entities may have missing
attributes while others may have an extra attribute.
 This model gives flexibility in storing the data. It also gives
flexibility to theattributes.
Example:Ifwearestoringanyvalueinany attribute then that
value can be either atomic value or a collection of values.

8. AssociativeDataModel
 AssociativeDataModelisamodelin whichthedataisdividedintotwo parts.

Prof.KavitaLand 22
age
DatabaseManagementSystem

Everything which has independent existence is called as

an entity and the relationship among these entities are
called association. The data divided into two parts are
called items and links.
 Item:Itemscontainthenameandtheidentifier(somenumericvalue).
 Links:Linkscontaintheidentifier,source,verbandsubject.
Example: Let us say we have a statement "The world cup is
being hosted by London from 30 May 2020". In this data two
links need to be stored:
1.TheworldcupisbeinghostedbyLondon.Thesourcehereis'the world
cup', the verb 'is being' and the target is 'London'.
2...from30May2020.Thesourcehereisthepreviouslink,theverbis'from'and
the target
is'30May2020'.

Thisisrepresentedusingthetableasfollows

9. ContextDataModel

Context Data Model is a collection of several models. This

consists of modelslikenetworkmodel,relationalmodels etc.
Usingthismodel, we can do various types of tasks which are
not possible using any model alone

AdvantagesofDataModels
1. DataModelshelpusinrepresentingdataaccurately.
2. IthelpsusinfindingthemissingdataandalsoinminimizingData
Redundancy.

Prof.KavitaLand 23
age
DatabaseManagementSystem

3. DataModelprovidesdatasecurityinabetterway.
4. The data model should be detailed enough to be used for
building the physical database.
5. The information in the data model can be used for defining
therelationship between tables, primary and foreign keys, and
stored procedures.
DisadvantagesofDataModels
1. In the case of a vast database, sometimes it becomes difficult to
understand the data model.
2. YoumusthavetheproperknowledgeofSQLtousephysicalmodels.
3. Even smaller change made in structure require modification in
the entire application.
4. ThereisnosetdatamanipulationlanguageinDBMS.
5. To develop Data model one should know physical data stored
characteristics.

DatabaseInstance:
1. It is important that we distinguish these two terms individually.
Database schema is
the skeleton of database. It isdesigned when the database
doesn't exist at all. Once the database is operational, it is
very difficult to make any changes to it.
2. Adatabaseschemadoesnotcontain anydataorinformation.
3. A database instance is a state of operational
databasewith data at any given time. It contains a
snapshot of the database.
4. Database instances tend to change with time. A DBMS
ensures that its every instance (state) is in a valid state,
by diligently following all the validations, constraints, and
conditions that the database designers have imposed.

Databaseschema.
In a data model, it is important to distinguish between the description of
thedatabaseandthedatabaseitself.Thedescriptionofadatabaseiscalledthe
database schema, which is specified during database design and is not
expected to change frequently. Most data models have certain
conventions for displaying schemas as diagrams. A displayed schema is
called a schema diagram. Figure 2.1 shows a schema diagram for the
database shown in Figure 1.2; the diagram displays the structure of each
record type but not the actual instances of records.

Prof.KavitaLand 24
age
DatabaseManagementSystem

A schema construct. A schema diagram displays only some aspects of a

schema, such as the names record types and data items, and some types
of constraints. Other aspects are not specified in the schema diagram;

Three-SchemaArchitecture
Thegoalofthethree-schemaarchitectureistoseparatetheuser
applications from the

physicaldatabase.Inthisarchitecture,schemascanbedefinedatthe

Prof.KavitaLand 25
age
DatabaseManagementSystem

followingthreelevels:
1. Theinternallevelhasaninternal schema,
▻whichdescribesthephysicalstoragestructureofthedatabase.
▻The internal schema uses a physical data model and describes
the complete details of data storage and access paths for the
database.
2.Theconceptuallevelhasaconceptual schema,
▻Which describes the structure of the whole database for a
communityof users.
▻The conceptual schema hides the details of physical storage
structures and concentrates on describing entities, data
types, relationships, user operations, and constraints.
▻ Usually, a representational data model is used to describe
theconceptual schema when a database system is
implemented. This implementation conceptual schema is
often based on a conceptual schema design in a high-level
data model
3.Theexternalorview
levelincludesanumberofexternalschemasoruserviews.
▻Each external schema describes the part of the database that
aparticular user group is interested in and hides the rest of
the database from that user group.
▻As in the previous level, each external schema is typically
implemented using a representational data model, possibly
based on an external schema design in a high-level data
model.

DBMSArchitecture:
1. ThedesignofaDBMSdependsonitsarchitecture.
2. Itcanbecentralizedordecentralizedor hierarchical.
3. ThearchitectureofaDBMScanbeseen aseithersingletierormulti-tier.
4. An n-tier architecturedivides the whole system into related
but independent n
modules,whichcanbeindependentlymodified,altered,changed,or
replaced.
5. In 1-tier architecture, the DBMS is the only entity where
the user directly sits on the DBMS and uses it. Any
changesdone herewill directly be done on the DBMS
itself.
6. It does not provide handy tools for end-users. Database
designers and programmers normally prefer to use
single-tier architecture.
7. IfthearchitectureofDBMSis2-tier,thenitmusthavean

Prof.KavitaLand 26
age
DatabaseManagementSystem

application through which the DBMS can be accessed.

Programmersuse2-
tierarchitecturewheretheyaccess the DBMS by
means of an application.

3-tierArchitecture

A 3-tier architecture separates its tiers from each other based

on the complexity of the users and how they use the data
present in the database. It is the most widely used
architecture to design a DBMS.

Database (Data) Tier − At this tier, the database resides

along with its query processing languages. We also have the
relations that define the data and their constraints at this
level.
Application (Middle) Tier − At this tier reside the
application server and the programs that access the database.
For a user, this application
tierpresentsanabstractedviewofthedatabase.End-
Prof.KavitaLand 27
age
DatabaseManagementSystem
usersareunaware

Prof.KavitaLand 28
age
DatabaseManagementSystem

of any existence of the database beyond the application. At the

other end, the database tier is not aware of any other user
beyond the
applicationtier.Hence,theapplicationlayersitsinthemiddleandact
sas a mediator between the end-user and the database.
User (Presentation) Tier − End-users operate on this tier
and they know nothing about any existence ofthe
databasebeyondthislayer. At this layer, multiple views of the
database can be
provided by the application. All views are generated by
applications that reside in the application tier.
Multiple-tier database architecture is highly modifiable, as almost
all its components are independent and can be changed
independently.

Data Independence:

▰ Data independence, which can be defined as the capacity to change

the schema at one level of a database system without having to
change the schema at the next higher level
▰A database system normally contains a lot of data in addition
to users’ data. For example, it stores data about data,
known as metadata, to locate and retrieve data easily.
▰It is rather difficult to modify or update a set of metadata once it is
stored in the database.
▰But as a DBMS expands, it needs to change over time to
satisfy the requirements of the users. If the entire data is
dependent, it would become a tedious and highly complex
job.
▰Therearetwotypesofdata independence
 Logicaldataindependence
 Physicaldataindependence

Prof.KavitaLand 29
age
DatabaseManagementSystem

LogicalDataIndependence
 Logical data is data about database, that is, it stores
information about how data is managed inside. For example,
a table (relation) stored inthe database and allits
constraints, applied on that relation.
 Logicaldataindependenceisa kindofmechanism,which
liberalizes itselffromactualdatastoredonthedisk.Ifwedo
somechangeson table format, it should not change the
data
residingonthedisk.
PhysicalDataIndependence

 All the schemas are logical, and the actual data is stored
in bit format on the disk. Physical data independence is
the power to change the physical data without impacting
the schema or logical data.
 For example, in case we want to change or upgrade the
storage system itself − suppose we want to replace hard-
disks with SSD −it should not have any impact on the
logical data or schemas.

DatabaseLanguages:

Prof.KavitaLand 30
age
DatabaseManagementSystem

TheDBMSprovidesappropriatelanguagesandinterfacesforeachcategoryof
users. Fallowing are different languages for different kinds of users
▰Datadefinitionlanguage(DDL)
▰Data manipulationlanguage (DML)

▰Storagedefinitionlanguage(SDL)

▰Viewdefinitionlanguage(VDL)

 DDL(Data definition language)-is used by the DBA and by database

designers to define both internal and conceptual schema.
 DBMShaveDDLcompilertoprocessthefunctionsofDDLstatementsinord
er to identifydescriptions oftheschemaconstructsoftheschema
description in the DBMS catalog.

HerearesometasksthatcomeunderDDL:

o Create:Itisusedtocreateobjectsinthedatabase.
o Alter:Itisusedtoalter thestructureofthe database.
o Drop:Itisused todeleteobjectsfromthe database.
o Truncate:Itisusedto removeallrecordsfromatable.
o Rename:Itisusedto renamean object.
o Comment:Itisusedtocomment onthedatadictionary.

 SDL(stored definition language)-Is used to specify the internal

schema.The mapping between 2 schemas may be specified in either
one of these languages.
 DataControlLanguage(DCL)

o DCLstands forDataControlLanguage. It is used to retrieve the stored

or saved data.
o TheDCLexecutionistransactional.Italsohasrollbackparameters.

Prof.KavitaLand 31
age
DatabaseManagementSystem

(But in Oracle database, the execution of data control language does

not have the feature of rolling back.)

HerearesometasksthatcomeunderDCL:

o Grant:Itisusedtogiveuseraccessprivilegestoa database.
o Revoke:Itisusedtotakeback permissionsfromthe user.

 VDL(View definition languages)-to specify user views and their

mappings to the conceptual schema. But most of DBMS used DDL to
specify internal and external schema.
 DML:Oncethedatabaseschemasarecompiledandthe
databaseispopulated with data, users must have some means to
manipulate the database. The DBMSprovides a set ofoperationsora
languagecalledthe data manipulation language (DML) for these
purposes.
 A data-manipulation language is a language that enables users to
access or manipulate data as organized by the appropriate data
model.
 Thetypesof
accessare:Retrieval,insertion,deletionandmodificationof the
data

 Select:Itisused toretrievedatafromadatabase.
 Insert:Itisusedtoinsertdataintoatable.
 Update:Itisusedtoupdateexistingdatawithinatable.
 Delete:Itisusedtodeleteallrecordsfroma table.

 TransactionControlLanguage(TCL)

TCLisusedtorunthechangesmadebytheDMLstatement.TCLcanbegrouped
into a logical transaction.

HerearesometasksthatcomeunderTCL:

o Commit:Itisusedtosavethetransactiononthe database.
o Rollback:Itisusedtorestorethedatabasetooriginalsincethelast
Commit.

Prof.KavitaLand 32
age
DatabaseManagementSystem

InterfacesinDBMS

A database management system (DBMS) interface is a user interface that

allows for the ability to input queries to a database without using the
query language itself. User-friendly interfaces provided by DBMS may
include the following:
 Menu-BasedInterfaces
 Forms-BasedInterfaces
 GraphicalUserInterfaces
 NaturalLanguageInterfaces
 SpeechInputandOutputInterfaces
 InterfacesforParametricUsers
 InterfacesfortheDatabaseAdministrator(DBA)
Menu-BasedInterfaces
These interfaces present the user with lists of options (called menus) that
lead the
userthroughtheformationofarequest.Thebasicadvantageofusingmenusis
that they remove the tension of remembering specific commands and
syntax of any query language. The query is basically composed step by
step by collecting orpicking options from a menu that is shown by the
system. Pull-down menus are a very popular technique in Web-based
interfaces. They are also often used in
browsinginterfaceswhichallowausertolookthroughthecontentsofadatabase
in an exploratory and unstructured manner.

Forms-BasedInterfaces
A forms-based interface displays a form to each user. Users can fill out all
of
theformentriestoinsertnewdata,ortheycanfilloutonlycertainentries,inwhich
casetheDBMSwillredeemthesametypeofdataforotherremainingentries.
Thesetypesofforms areusuallydesigned orcreatedandprogrammedfor
users that have no expertise in operating systems. Many DBMS’s have
form specification
languageswhicharespeciallanguagesthathelpspecifysuchforms.

Example: SQL Forms is a form-based language that specifies queries

using a form designed in conjunction with the relational database schema.

GraphicalUserInterface
A GUI typically displays a schema to the user in diagrammatic form. The
user thencan specify a query by manipulating the diagram. In many
cases, GUI utilise both
menusandforms.MostGUIuseapointingdevicesuchasamouse,topicka
certain part of the displayed schema diagram.

Prof.KavitaLand 33
age
DatabaseManagementSystem

NaturalLanguageInterfaces
These interfaces accept requests written in English or some other
language and attempt to understand them. A Natural language interface
has itsown schema,which is similar to the database conceptual schema as
well as a dictionary of important words.
The natural language interface refers to the words in its schema as well as
to the set of standard words in a dictionary to interpret the request. If the
interpretation is successful, the interface generates a high-level query
corresponding to the natural
languageandsubmitsittotheDBMSforprocessing,otherwise,adialogueis
started with the user to clarify any provided condition or request. The
main disadvantage of this is that the capabilities of this type of interface
are not that advance.

SpeechInputandOutputInterfaces
Thereislimiteduseofspeechbeitforaqueryorananswertoaquestionorbeing a
result of a request it is becoming commonplace. Applications with limited
vocabulary such as inquiries for telephone directory, flight
arrival/departure, and bank account information are allowed speech for
input and output to enableordinary folks to access this information.
The Speech input is detected using predefined words and used to set up
the parameters that are supplied to the queries. For output, a similar
conversion from text or numbers into speech takes place.

InterfaceforParametricUsers

Interfaces for Parametric Users contain some commands that can be

handled with a minimum of keystrokes. It is generally used in bank
transactions for transferring money. These operations are performed
repeatedly.
InterfacesforDatabaseAdministrators(DBA)
Most database system contains privileged commands that can be used
only by the DBA’s staff. These include commands for creating accounts,
setting system parameters, granting account authorization, changing a
schema, and reorganizing the storage structures of databases.

Prof.KavitaLand 34
age
DatabaseManagementSystem

ClassificationofDBMS:
CentralizedDatabase

It is the type of database that stores data at a centralized

database system. It comforts the users to access the stored
data from different locations through several applications. These
applications contain the authentication process to let users
access data securely. An example of a Centralized database can
be Central Library that carries a centraldatabase of each library
in a college/university.

AdvantagesofCentralizedDatabase

o Ithasdecreasedthe risk ofdata management, i.e.,

manipulation of data will not affect the core data.
o Dataconsistencyismaintainedasitmanagesdatainacentral
repository.
o Itprovidesbetterdataquality,whichenablesorganizationstoestablish
data standards.
o Itislesscostlybecausefewervendorsarerequiredtohandlethedatasets
.

2) DistributedDatabase

Unlike a centralized database system, in distributed systems,

data is distributed among different database systems of an
organization. These
databasesystemsareconnectedviacommunication links. Such
links help the end-users to access the data easily. Examples of
the Distributed database are Apache Cassandra, HBase, Ignite,
etc.

Wecan furtherdivideadistributeddatabasesystem into:

o Homogeneous DDB: Those database systems which

execute on the same operatingsystem and use the same
application process and carry the same hardware devices.
o HeterogeneousDDB:Thosedatabasesystemswhichexecuteon

Prof.KavitaLand 35
age
DatabaseManagementSystem

differentoperatingsystemsunderdifferentapplicationproced
ures, and carries different hardware devices
3) NoSQLDatabase

Non-SQL/Not Only SQL is a type of database that is used for

storing a widerangeofdatasets. It isnot a relationaldatabaseas it
stores data not only in tabular form but in several different
ways. It came into existence when the demand for building
modern applications increased. Thus, NoSQL presented a wide
variety of database technologies in response to the demands.

Wecanfurtherdivide aNoSQLdatabaseintothefollowingfourtypes:
 Key-value storage: It is the simplest type of
database storage where it stores every single item as a
key (or attribute name) holding its value, together.
 Document-oriented Database: A type of database
used to store data as JSON-like document. It helps
developers in storing data by using the same
document-model format as used in the application
code.
 Graph Databases: It is used for storing vast amounts of data
in agraph-like
structure. Most commonly, social networking websites use the
graph database.
 Wide-column stores: It is similar to the data
represented in relational databases. Here, data is
stored in large columns together, instead of storing in
rows

AdvantagesofNoSQLDatabase

o Itenablesgoodproductivityintheapplicationdevelopmentasi
t is not required to store data in a structured format.
o Itisabetteroptionformanagingandhandlinglargedata sets.
o Itprovideshighscalability.
o Userscanquicklyaccessdatafromthedatabasethroughkey-value

Prof.KavitaLand 36
age
DatabaseManagementSystem

4) CloudDatabase

A type of database where data is stored in a virtual environment

and executes over the cloud computing platform. It provides
users with various cloud computing services (SaaS, PaaS, IaaS,
etc.)foraccessing the database. Thereare numerous cloud
platforms, but the best options are:
o AmazonWebServices(AWS)
o MicrosoftAzure
o Kamatera
o PhonixNAP
o ScienceSoft
o GoogleCloudSQL,etc.

5) Object-orientedDatabases

Thetypeofdatabasethatusestheobject-
baseddatamodelapproachfor storing data in the database
system. The data is represented and stored as objects which are
similar to the objects used in the object-oriented programming
language.

6) HierarchicalDatabases
It is the type of database that stores data in the form of parent-
children relationshipnodes.Here, it organizes data in a tree-like
structure.

Prof.KavitaLand 37
age
DatabaseManagementSystem

Data get stored in the form of records that are connected via
links. Each child record in the tree will contain only one parent.
On the other hand, each parent record can have multiple child
records

Prof.KavitaLand 38
age