Unit-1

Part-A Notes
What is a Database?
A database is a collection of related data which represents
some aspect of the real world.

A database is an organized collection of data, so that it can be

easily accessed and managed.

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

Database handlers create a database in such a way that only

one set of software program provides access of data to all the
users.

The main purpose of the database is to operate a large

amount of information by storing, retrieving, and managing
data.

There are many databases available like MySQL, Sybase,

Oracle, MongoDB, Informix, PostgreSQL, SQL Server, etc.

Modern databases are managed by the database management

system (DBMS).

SQL or Structured Query Language is used to operate on the

data stored in a database. SQL depends on relational algebra
and tuple relational calculus.

A cylindrical structure is used to display the image of a

database.

1|Page
What is DBMS?
Database Management System (DBMS) is a software for
storing and retrieving users’ data while considering appropriate
security measures. It consists of a group of programs which
manipulate the database. The DBMS accepts the request for
data from an application and instructs the operating system to
provide the specific data. In large systems, a DBMS helps users
and other third-party software to store and retrieve data.
DBMS allows users to create their own databases as per their
requirement. The term “DBMS” includes the user of
the database and other application programs. It provides an
interface between the data and the software application .

What is Data?
Data is a collection of a distinct small unit of information. It can
be used in a variety of forms like text, numbers, media, bytes,
etc. it can be stored in pieces of paper or electronic memory,
etc.

Word 'Data' is originated from the word 'datum' that means

'single piece of 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.

2|Page
 I. 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
o Data Updation: It is used for the insertion, modification,
and deletion of the actual data in the database.
o
o Data Retrieval: It is used to retrieve the data from the
database which can be used by applications for various
purposes.
o
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.

3|Page
II. 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.

4|Page
Database Vs File System :

There are the following differences between DBMS

and File systems:

Basis DBMS Approach File System Approach

Meaning DBMS is a collection of The file system is a

data. In DBMS, the collection of data. In
user is not required to this system, the user
write the procedures. has to write the
procedures for
managing the
database.

Sharing of Due to the centralized Data is distributed in

data approach, data many files, and it may
sharing is easy. be of different formats,
so it isn't easy to share
data.

Data DBMS gives an The file system

Abstraction abstract view of data provides the detail of
that hides the details. the data representation
and storage of data.

Security and DBMS provides a good It isn't easy to protect a

Protection protection file under the file
mechanism. system.

Recovery DBMS provides a The file system doesn't

Mechanism crash recovery have a crash
mechanism, i.e., mechanism, i.e., if the

5|Page
 DBMS protects the system crashes while
user from system entering some data,
failure. then the content of the
file will be lost.

Manipulatio DBMS contains a wide The file system can't

n variety of efficiently store and
Techniques sophisticated retrieve the data.
techniques to store
and retrieve the data.

Concurrency DBMS takes care of In the File system,

Problems Concurrent access of concurrent access has
data using some form many problems like
of locking. redirecting the file
while deleting some
information or updating
some information.

Where to Database approach File system approach

use used in large systems used in large systems
which interrelate which interrelate many
many files. files.

Cost The database system The file system

is expensive to design. approach is cheaper to
design.

Data Due to the In this, the files and

Redundancy centralization of the application programs
and database, the are created by different
Inconsistenc problems of data programmers so that
y redundancy and there exists a lot of
inconsistency are duplication of data
controlled. which may lead to

6|Page
 inconsistency.

Structure The database The file system

structure is complex approach has a simple
to design. structure.

Data In this system, Data In the File system

Independen Independence exists, approach, there exists
ce and it can be of two no Data Independence.
types.

o Logical Data
Independence
o Physical Data
Independence

Integrity Integrity Constraints Integrity Constraints

Constraints are easy to apply. are difficult to
implement in file
system.

Data Models In the database In the file system

approach, 3 types of approach, there is no
data models exist: concept of data models
exists.
o Hierarchal data
models
o Network data
models
o Relational data
models

Flexibility Changes are often a The flexibility of the

necessity to the system is less as

7|Page
 content of the data compared to the DBMS
stored in any system, approach.
and these changes are
more easily with a
database approach.

Examples Oracle, SQL Server, Cobol, C++ etc.

Sybase etc.

III. Database Users :

What is a Database User?

Those users that use the database and avail of the benefits are
called users of the database. Or in other words, any person who
can access the database of a part of the database using an
application is called a database user.

Types of Users in Database

8|Page
Based on the interaction with the database, the users can be
categorized into the following types –

 Database Administrators

 Application Programmers / Back-End Developers

 Naive Users / Parametric Users

 End Users

1. Database Administrator :

Database Administrator which is commonly known by its short

form i.e., DBA is a person or a team who defines the schema
and also controls the three levels of the database.
DBA is responsible for managing the entire DBMS Sytem.
The DBA will then create a new account id and password for the
user if he/she needs to access the database.

9|Page
DBA is also responsible for providing security to the database
and he allows only authorized users to access or modify the
database.
DBA also monitors the recovery and backup and provides
technical support.

The DBA has a DBA account in the DBMS which is called a

system or superuser account.

2.System Analyst :

Those users that check whether the requirements of the end

users are fulfilled or not are known as System Analysts.

3.Naïve Users :

Naive users are unsophisticated users who interact with the

system by invoking one of the application programs that have
been written previously. For example, a bank teller who needs
to transfer $50 from account A to account B invokes a program
called transfer. This program asks the teller for the amount of
money to be transferred, the account from which the money is
to be transferred, and the account to which the money is to be
transferred.

4. Application Programmers :

Application programmers are computer professionals who write

application programs. Application programmers write programs
in various programming languages to interact with database.

5. End Users :

10 | P a g e
The people who interact with the DBMS. They conduct various
operations on database like retrieving, updating, deleting e.t.c

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

V. Database Applications :

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

11 | P a g e
maintain business information. There are many applications
where databases are used.

Database Language :
o A DBMS has appropriate languages and interfaces to
express database queries and updates.
o Database languages can be used to read, store and
update the data in the database.

Types of Database Language :

12 | P a g e
1. Data Definition Language :

o DDL stands for Data Definition Language. It is used to

define database structure or pattern.
o It is used to create schema, tables, indexes, constraints,
etc. in the database.
o Using the DDL statements, you can create the skeleton of
the database.
o 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:

o Create: It is used to create objects in the database.

o Alter: It is used to alter the structure of the database.
o Drop: It is used to delete objects from the database.
o Truncate: It is used to remove all records from a table.
o Rename: It is used to rename an object.
o Comment: It is used to comment on the data dictionary.

13 | P a g e
2. Data Manipulation Language :
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:

o Select: It is used to retrieve data from a database.

o Insert: It is used to insert data into a table.
o Update: It is used to update existing data within a table.
o Delete: It is used to delete all records from a table.
o Merge: It performs UPSERT operation, i.e., insert or
update operations.
o Call: It is used to call a structured query language or a
Java subprogram.
o Explain Plan: It has the parameter of explaining data.
o Lock Table: It controls concurrency.

3. Data Control Language :

o DCL stands for Data Control Language. It is used to
retrieve the stored or saved data.
o The DCL execution is transactional. It also has rollback
parameters.

Here are some tasks that come under DCL:

o Grant: It is used to give user access privileges to a

database.

14 | P a g e
 o 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 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:

o Commit: It is used to save the transaction on the

database.
o Rollback: It is used to restore the database to original
since the last Commit.

VI. Brief introduction of different Data

Models :

Data Model is the modeling of the data description, data

semantics, and consistency constraints of the data. It
provides the conceptual tools for describing the design of a

15 | P a g e
 database at each level of data abstraction. Therefore, there
are following four data models used for understanding the
structure of the database:

1) Relational Data Model: This type of model designs the

data in the form of rows and columns within a table. Thus, a
relational model uses tables for representing data and in-
between relationships. Tables are also called relations. This
model was initially described by Edgar F. Codd, in 1969. The
relational data model is the widely used model which is
primarily used by commercial data processing applications.

2) Entity-Relationship Data Model: An ER model is the

logical representation of data as objects and relationships
among them. These objects are known as entities, and
relationship is an association among these entities. This model
was designed by Peter Chen and published in 1976 papers. It
was widely used in database designing. A set of attributes

16 | P a g e
describe the entities. For example, student_name, student_id
describes the 'student' entity. A set of the same type of entities
is known as an 'Entity set', and the set of the same type of
relationships is known as 'relationship set'.

3) Object-based Data Model: An extension of the ER model

with notions of functions, encapsulation, and object identity, as
well. This model supports a rich type system that includes
structured and collection types. Thus, in 1980s, various
database systems following the object-oriented approach were
developed. Here, the objects are nothing but the data carrying
its properties.rd Skip 10s

4) Semistructured Data Model: This type of data model is

different from the other three data models (explained above).
The semistructured data model allows the data specifications at
places where the individual data items of the same type may
have different attributes sets. The Extensible Markup
Language, also known as XML, is widely used for representing
the semistructured data. Although XML was initially designed
for including the markup information to the text document, it
gains importance because of its application in the exchange of
data.

VII. Concepts of Schema :

Database Schema :

A database schema is a structure that represents the logical

storage of the data in a database. It represents the

17 | P a g e
organization of data and provides information about the
relationships between the tables in a given database.

o database schema is the logical representation of a

database, which shows how the data is stored logically in
the entire database. It contains list of attributes and
instruction that informs the database engine that how the
data is organized and how the elements are related to
each other.
o A database schema contains schema objects that may
include tables, fields, packages, views,
relationships, primary key, foreign key,
o The schema does not physically contain the data itself;
instead, it gives information about the shape of data and
how it can be related to other tables or models.
o A database schema object includes the following:
o Consistent formatting for all data entries.
o Database objects and unique keys for all data
entries.
o Tables with multiple columns, and each column
contains its name and datatype.

Types of Database Schema :

The database schema is divided into three types, which are:

1. Logical Schema

18 | P a g e
 2. Physical Schema
3. View Schema

1. Physical Database Schema :

A physical database schema specifies how the data is stored

physically on a storage system or disk storage in the form of
Files and Indices. Designing a database at the physical level is
called a physical schema.

2. Logical Database Schema :

The Logical database schema specifies all the logical

constraints that need to be applied to the stored data. It

19 | P a g e
defines the views, integrity constraints, and table. Here the
term integrity constraints define the set of rules that are
used by DBMS (Database Management System) to maintain the
quality for insertion & update the data. The logical schema
represents how the data is stored in the form of tables and how
the attributes of a table are linked together.

At this level, programmers and administrators work, and the

implementation of the data structure is hidden at this level.

Various tools are used to create a logical database schema, and

these tools demonstrate the relationships between the
component of your data; this process is called ER modelling.

The ER modelling stands for entity-relationship modelling,

which specifies the relationships between different entities.

3. View Schema :

The view level design of a database is known as view schema.

This schema generally describes the end-user interaction with
the database systems.

XIII. Instance and Data Independence :

Instance :

20 | P a g e
In DBMS, the data is stored for a particular amount of time and
is called an instance of the database. The database schema
defines the attributes of the database in the particular DBMS.
The value of the particular attribute at a particular moment in
time is known as an instance of the DBMS.

What is an Instance in a Database?

The instance of the database is the values of these variables at

any given time. Instances are also called the current state or
database state. The new data items in a record can be inserted,
modified, or deleted at any time.

Example 1 (Orders table):

The 5 rows in the above-provided table are called Instances

because they provide the information of the Database stored at
the current point in time.

Data Independence :

21 | P a g e
 o Data independence can be explained using the three-
schema architecture.
o Data independence refers characteristic of being able to
modify the schema at one level of the database system
without altering the schema at the next higher level.

There are two types of data independence:

1. Logical Data Independence :

o Logical data independence refers characteristic of being

able to change the conceptual schema without having to
change the external schema.
o Logical data independence is used to separate the
external level from the conceptual view.
o If we do any changes in the conceptual view of the data,
then the user view of the data would not be affected.
o Logical data independence occurs at the user interface
level.

2. Physical Data Independence :

o Physical data independence can be defined as the

capacity to change the internal schema without having to
change the conceptual schema.
o If we do any changes in the storage size of the database
system server, then the Conceptual structure of the
database will not be affected.
o Physical data independence is used to separate
conceptual levels from the internal levels.

22 | P a g e
 o Physical data independence occurs at the logical interface
level.

Fig: Data Independence

IX : Three Tier Schema Architecture for

Data Independence :

23 | P a g e
 o The three schema architecture is also called ANSI/SPARC
architecture or three-level architecture.
o This framework is used to describe the structure of a
specific database system.
o The three schema architecture is also used to separate
the user applications and physical database.
o The three schema architecture contains three-levels. It
breaks the database down into three different categories.
o

The three-schema architecture is as follows:

1. Internal Level :

24 | P a g e
o The internal level has an internal schema which describes
the physical storage structure of the database.
o The internal schema is also known as a physical schema.
o It uses the physical data model. It is used to define that
how the data will be stored in a block.
o The physical level is used to describe complex low-level
data structures in detail.

2. Conceptual Level :

o The conceptual schema describes the design of a

database at the conceptual level. Conceptual level is also
known as logical level.
o The conceptual schema describes the structure of the
whole database.
o The conceptual level describes what data are to be stored
in the database and also describes what relationship
exists among those data.
o In the conceptual level, internal details such as an
implementation of the data structure are hidden.
3. External Level :

25 | P a g e
 o At the external level, a database contains several schemas
that sometimes called as subschema. The subschema is
used to describe the different view of the database.
o An external schema is also known as view schema.
o Each view schema describes the database part that a
particular user group is interested and hides the remaining
database from that user group.
o The view schema describes the end user interaction with
database systems.

X. Database System Structure :

DBMS
Database Management System, which is a tool or software
used to create the database or delete or manipulate the
database. A software programme created to store, retrieve,
query, and manage data is known as a Database Management
System (DBMS). Data can be generated, read, updated, and
destroyed by authorized entities thanks to user interfaces (UIs).

Users of DBMSs include application programmers, Database

Administrators (DBAs), and end users.

Database Administrators are typically the only people who work

directly with a DBMS. Today, end users read and write to
databases using front-end interfaces made by programmers,
while programmers use cloud APIs to connect with DBMSs .

26 | P a g e
27 | P a g e
Three Parts that make up the Database System are:

o Query Processor
o Storage Manager
o Disk Storage
The explanations for these are provided below:

1. Query Processor :
The query processing is handled by the query processor, as the
name implies. It executes the user's query, to put it simply. In
this way, the query processor aids the database system in
making data access simple and easy. The query processor's
primary duty is to successfully execute the query. The Query
Processor transforms (or interprets) the user's application
program-provided requests into instructions that a computer
can understand.

Components of the Query Processor :

o DDL Interpreter:
Data Definition Language is what DDL stands for. As implied by
the name, the DDL Interpreter interprets DDL statements like
those used in schema definitions (such as create, remove, etc.).
This interpretation yields a set of tables that include the meta-
data (data of data) that is kept in the data dictionary. Metadata
may be stored in a data dictionary.

o DML Compiler:
Compiler for DML Data Manipulation Language is what DML
stands for. In keeping with its name, the DML Compiler converts
DML statements like select, update, and delete into low-level
instructions or simply machine-readable object code, to enable
execution. The optimization of queries is another function of
the DML compiler. Since a single question can typically be
translated into a number of evaluation plans. As a result, some
optimization is needed to select the evaluation plan with the

28 | P a g e
lowest cost out of all the options. This process, known as query
optimization, is exclusively carried out by the DML compiler.

o Embedded DML Pre-compiler:

Before the query evaluation, the embedded DML commands in
the application program (such as SELECT, FROM, etc., in SQL)
must be pre-compiled into standard procedural calls (program
instructions that the host language can understand). Therefore,
the DML statements which are embedded in an application
program must be converted into routine calls by the Embedded
DML Pre-compiler.

o Query Optimizer:
It starts by taking the evaluation plan for the question, runs it,
and then returns the result. Simply said, the query evaluation
engine evaluates the SQL commands used to access the
database's contents before returning the result of the query.

2. Storage Manager:

An application called Storage Manager acts as a conduit

between the queries made and the data kept in the database.
Another name for it is Database Control System. By applying
the restrictions and running the DCL instructions, it keeps the
database's consistency and integrity. It is in charge of
retrieving, storing, updating, and removing data from the
database.

Components of Storage Manager :

o Integrity Manager:
Whenever there is any change in the database, the Integrity
manager will manage the integrity constraints.

o Authorization Manager:
Authorization manager verifies the user that he is valid and
authenticated for the specific query or request.

29 | P a g e
 o File Manager:
All the files and data structure of the database are managed by
this component.

o Transaction Manager:
It is responsible for making the database consistent before and
after the transactions. Concurrent processes are generally
controlled by this component.

o Buffer Manager:
The transfer of data between primary and main memory and
managing the cache memory is done by the buffer manager.

3. Disk Storage :

A DBMS can use various kinds of Data Structures as a part of

physical system implementation in the form of disk storage.

Components of Disk Storage :

o Data Dictionary:
It contains the metadata (data of data), which means each
object of the database has some information about its
structure. So, it creates a repository which contains the details
about the structure of the database object.

o Data Files:
This component stores the data in the files.

o Indices:
These indices are used to access and retrieve the data in a very
fast and efficient way.

30 | P a g e
XI. Database Environment :

The database management system can be divided into five

major components, they are:

1.Hardware

2.Software

3.Data

4.Procedures

5.People

1] Hardware-

 When we say hardware, we mean computer, hard disk, I/o

channels for data and any other physical components
involved before any data is successfully stored into the
memory.
 Consists of a set of physical electronics devices such as
computers, I/o devices, storage devices, etc. This provides

31 | P a g e
 the interface between computers and the real world
system.
 This DBMS component is used for keeping and storing the
data in the database.

2] Software

 This is the set of programs used to control and manage

the overall database. This includes the DBMS software
itself, the operating system, the network software being
used to share the data among users, and the application
program used to access data in the DBMS.
 The software in DBMS environment includes operating
system, database management system, application
programs, support utility programs.

3] Data

 The main task of DBMS is to process the data. Here

database are defined, constructed, and then data is
stored, retrieved, and updated to and form the database.
 It is the most important component of the database
management system. The database contains both the
metadata(data about data) and the actual(operational)
data.
 DBMS exists to collect, store, process and access data, the
most important component. In a typical database , the
user saved data is present and meta data is stored.

32 | P a g e
4] Procedures

 These are the instructions and rules that assist on how to

use the DBMS, and in designing and running the database,
using documented procedures, to guide the users that
operate and manage it.
 Procedures are used to setup and install a new DBMS to
login and logout of DBMS software, to manage DBMS or
application programs, to take backup of the database, and
to change the structure of the database, etc.

5] People

 The people are who control and manage the databases

and perform different types of operations on the database
in the DBMS.
 The people includes databse administrator, software
developer and End user.
 Database administrator-database administrator is the one
who manages the complete database management
system. DBA takes care of the security of the DBMS, it’s
availability, managing the license keys, managing user
accounts and access,etc.

33 | P a g e
XII. Client Server Architecture for the database :

Client-Server architecture is an architectural deployment style

that describe the separation of functionality into layers with
each segment being a tier that can be located on a physically
separate computer.

This architecture has different usages with different

applications. It can be used in web applications and distributed
applications.

Structure :

Using this architecture the software is divided into 3 different

tiers: Presentation tier, Logic tier, and Data tier. Each tier is
developed and maintained as an independent tier.

1-Presentation tier

This is the topmost level of the application. The presentation

layer provides the application’s user interface (UI). Typically,
this involves the use of Graphical User Interface for smart client
interaction, and Web based technologies for browser-based
interaction. The presentation tier displays information related
to such services as browsing merchandise, purchasing, and
shopping cart contents. It communicates with other tiers by
outputting results to the browser/client tier and all other tiers in
the network.

34 | P a g e
2-Logic tier (called also business logic, data access tier,
or middle tier)

The logic tier is pulled out from the presentation tier and, as its
own layer; it controls an application’s functionality by
performing detailed processing. Logic tier is where mission-
critical business problems are solved. The components that
make up this layer can exist on a server machine, to assist in
resource sharing.

3-Data tier

This tier consists of database servers, is the actual DBMS

access layer. It can be accessed through the business services
layer and on occasion by the user services layer. Here
information is stored and retrieved. This tier keeps data neutral
and independent from application servers or business logic.
Giving data its own tier also improves scalability and
performance. This layer consists of data access components
(rather than raw DBMS connections) to aid in resource sharing
and to allow clients to be configured without installing the
DBMS libraries and ODBC drivers on each client. An example
would be a computer hosting a database management system
(DBMS), such as a Microsoft SQL Server database.

35 | P a g e