Module1

Database System Concept and

Architecture and Apllications
● Introduction,
● File system v/s Database system,
● Characteristics of Databases
● Data abstraction and Data Independence,
● DBMS system architecture,
● Database Administrator (DBA),
● Role of DBA
Self-learning Topics: Identify the types of Databases.
 Introduction to DBMS

•Data

•Database

•DBMS
Data

Data is the known facts or figures that Can be recorded in system and
that have some special meaning assigned to it is called as data

e.g: 25, “ajit” etc.

Data

• It is the collection of information needed by the organization.

• Metadata - is information about the data i.e data about data

The college Database organizes the data about the admin, staff, students
and faculty etc. Using the database, you can easily retrieve, insert, and
delete the information.
 Database
•A database is collection of related data items stored at
one place

•A database is information that is set up for easy

access, management and updating. Computer
databases typically store aggregations of data records
or files that contain information, such as sales
transactions, customer data, financials and product
information.
DBMS

A database management system (or DBMS)

is essentially nothing more than a
computerized data-keeping system.
Users of the system are given facilities to
perform several kinds of operations on such
a system for either manipulation of the data
in the database or the management of the
database structure itself.
A 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.
Example

•Let’s take a layman example to understand this:

In a banking system, suppose a customer is having two accounts, one is saving
account and another is salary account. Let’s say bank stores saving account data
at one place (these places are called tables we will learn them later) and salary
account data at another place, in that case if the customer information such as
customer name, address etc. are stored at both places then this is just a wastage
of storage (redundancy/ duplication of data), to organize the data in a better
way the information should be stored at one place and both the accounts should
be linked to that information somehow. The same thing we achieve in DBMS.
Data: Data is the information which has been translated into a form that is more
convenient to process or move.

Database : The is a collection of related data is termed as database which is

organized in such way that it can be easily retrieved and managed.

DBMS:A database Management System is system software which manages the

data.It can perform various functions tasks like
creation,retrieval,insertion,modification and deletion of data to manage it in a
systematic way as per the requirement.
Purpose of Database
● Programming languages cannot store data permanently. For this purpose we have
to use Database Management System.
● Store data in DBMS and for operations like insertion,modification or deletion we
write code in programming languages
● software=Programming language + DBMS
● Examples
● Frontend and BackEnd
● To understand the purpose or need of database system ,we need to study previous
option to store data which is called File Processing System.
File Processing System

The way we stored in files is similar to papers, in the form of flat

files – to be simpler, in notepad. Yes, the information where all in
the notepads with each fields of information separated by space,
tab comma, semicolon or any other symbol.
File Processing System(FPS)
● Number of times we need to store the data in such way that it should be
easily accessible
● That data may be bank transaction details,daily expenses,employee details,
product details etc
● Before computer such data stored with the help of papers
● After invention of computers, it become easy to store the data with the help of
files
● Traditional File processing System is a computer based system in which all
the information is stored in various computer files
● It stores data in a systematic way hence the different departments of an organization can store
their data in set of files which helps to manage and differentiate the data
Sales
Productiion
Sales
Production Application
Apllication
Data Files
Data Files

● Initially The Traditional FPS seems to be useful but as the requirement of data processing and
the size of the data increases ,the drawbacks of this system come in picture
Disadvantages of file processing

File processing system is good when there is only limited number

of files and data are very less. As the data and files in the system
grow, handling them becomes difficult.
Differences between file systems and
DBMS?
Parameter DBMS Approach File System
Approach
Meaning DBMS is a collection of data. In The file system is a collection
DBMS, the user is not required of data. In this system, the
to write the procedures. user has to write the
procedures for managing the
database

Sharing of data Due to the centralized Data is distributed in many

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

Data Abstraction DBMS gives an abstract view The file system provides the
of data that hides the details. detail of the data
representation and storage of
data.

Security and Protection DBMS provides a good It isn't easy to protect a file
protection mechanism. under the file system.

Recovery Mechanism DBMS provides a crash The file system doesn't have a
recovery mechanism, i.e., crash mechanism, i.e., if the
DBMS protects the user from system crashes while entering
Parameter DBMS Approach File System
Approach
Cost The database system is The file system approach is
expensive to design. cheaper to design.

Data Redundancy and Due to the centralization of In this, the files and
Inconsistency the database, the problems of application programs are
data redundancy and created by different
inconsistency are controlled. programmers so that there
exists a lot of duplication of
data which may lead to
inconsistency.

Structure The database structure is The file system approach has

complex to design. a simple structure.

Concurrency Concurrency problems means DBMS has subsystems to

updation of same data by control the concurrency.
multiple users may generate
irrelevant results.
Drawbacks of Traditional File Processing System
Consider Employee and team files
1. Data Redundancy-e.g name and address →duplication->Higher storage and access cost
2. Data Inconsistency—>When data is to be updated-> designation of employee
3. Limited Data Sharing→Difficult to share data in traditional File System->private files→write complex program
4. Difficulty in Accessing Data—>e.g data of employees who do not have taken leave→ write application
program or manual method—>wastage of space and time→ new requirement→do not provide efficient and
convenient way to retrieve
5. Data Dependence→Data stored in specific format,missing changes in single program may lead to failure of
application
6. Poor Data Control—>does not have centralized data control→Same fields may have different name→
Different meaning
7. Problem of security→ Project manager should be see all the data regarding teams under him.
Team leader should be see all about his specific team
8. Concurrency Problem→Access the same data by multiple users→e.g Bank
9. Data Isolation→difficult to store the entire data in a single file,distributed in different files
10. Integrity Problems→ enterprise has own constraints like Employee Id must start with E
But later on new constraint introduced,then it is difficult to add these constraints
11. Atomicity Problem→Failure in Computer system may occur any time leads to incorrect data updation
Advantages Of DBMS

•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.
•
•Data sharing: In DBMS, the authorized users of an
organization can share the data among multiple users.
•Reduce time: It reduces development time and maintenance need.
•Backup: It provides backup and recovery subsystems which create
automatic backup of data from hardware and software failures and
restores the data if required.
•
•multiple user interface: It provides different types of user interfaces like
graphical user interfaces, application program interfaces
•
•Easily Maintenance: It can be easily maintainable due to the centralized
nature of the database system
 Characteristics of database
1. Real World Entity: Should be able to store all kinds of data that exists
in this real world. Since we need to work with all kinds of data and
requirements, database should be strong enough to store that is present
around us. all kinds of data .
2. Relation Based Table: Should be able to relate the entities / tables in
the database by means of a relation. i.e.; any two tables should be
related. Let us say, an employee works for a department. This implies
that Employee is related to a particular department. We should be able
to define such a relationship between any two entities in the database.
There should not be any table lying without any mapping.
•
Characteristics of database
3. Isolation between Program and Data: Data and application should be
isolated. Because database is a system which gives the platform to store the
data, and the data is the one which allows the database to work. Hence
there should be clear differentiation between them.
4. Less Redundancy: There should not be any duplication of data in the
database. Data should be stored in such a way that it should not be repeated
in multiple tables. If repeated, it would be unnecessary waste of DB space
and maintaining such data becomes chaos.
5. Query Language: DBMS has a strong query language. Once the database is
designed, this helps the user to retrieve and manipulate the data. If a
particular user wants to see any specific data, he can apply as many filtering
conditions that he wants and pull the data that he needs.
•
Characteristics of database
6. Multiuser System (Concurrent Access): Multiple users should be
able to access the same database, without affecting the other user. i.e.;
if teachers want to update a student’s marks in Results table at the
same time, then they should be allowed to update the marks for their
subjects, without modifying other subject marks. A good database
should support this feature.
7. Multiple Views: It supports multiple views to the user, depending on
his role. In a school database, Students will able to see only their
reports and their access would be read only. At the same time teachers
will have access to all the students with the modification rights. But the
database is the same. Hence a single database provides different views
to different users.
Characteristics of database
8. Security (Constraints) : Database should also provide security, i.e.; when there
are multiple users accessing the database, each user will have their own levels of
rights to see the database. Some of them will be allowed to see whole database,
and some will have only partial rights. For example, instructor who is teaching
Physics will have access to see and update marks of his subject. He will not have
access for other subjects. But the HOD will have full access on all the subjects.
9. ACID Property: Database should also support ACID( A- Atomicity, C-
Consistency, I – Isolation, D – Durability) property. i.e.; while performing any
transactions like insert, update and delete, database makes sure that the real
purpose of the data is not lost. For example, if a student’s address is updated,
then it should make sure that there is no duplicate data is created nor there is any
data mismatch for that student.
10.Consistancy: The data should remain
consistent throughout the program or
application.
 Disadvantage of DBMS:

1.DBMS software and hardware (networking installation) cost is high

2. The processing overhead by the dbms for implementation of security,

integrity and sharing of the data.

3. centralized database control

4. Setup of the database system requires more knowledge, money,
skills, and time.
5. The complexity of the database may result in poor performance.
Applications of DBMS
● A DBMS is a computerized record-keeping system.It works as a container for
collection of computerized data files.
● Database touches all aspect of our lives. Some of the major areas of application
are as follows:
1.Telecom : database is maintain to keep track of the information about calls
made,customer details,network usage
2.Banking: Data related to transactions of customers is very huge.
Used for storing customer personal information,tracking day to day credit and
debit transactions,generating bank statements etc.
3.Industry: In department like production,sales or account it is very essential to
store the data in systematic format
E-commerce: There are number of shopping websites such as ebay, Flipkart ,Amazon etc.
Store information about various products, user addresses,credit details
Airlines: The data of ticket booking,flight schedule,employee details and customer details
is very huge
Education System:Schools,colleges,private institutes have to store data of
students,teachers,exam schedules accounts
Railway Reservation System:
Library Management System:
Social Media sites:
Users of Database System
● As we now know what is a database, who would be the users of database?

● Of course the developers will be using this database to design and develop.

● Who else? There would be an administrator, who keeps watching the database for its
usages, who is accessing it, giving access to other users, limiting the security for the
users, and any other maintenance work of the database.

● And there is one more end users. These end users are the real group of people who
really uses the database and takes the advantages of database. In School database,
teachers, students are the end users, who really uses the database in their daily needs.
 DATA ABSTRACTION
● Extracting the important data by ignoring the remaining irrelevant details is
known as abstraction

● Database System are usually made up of complex data structure as per their
requirements

● To make the user interaction easy with database, the internal irrelevant details
can be hidden from user.

● We can hide the complexity of database fro user by different level of

abstraction.
DATA ABSTRACTION AND DATA INDEPENDENCE
Data Independence
● Database systems are designed in multi-layers
Physical Level
● In data abstraction physical level is the lowest level.This level describes
how the data is actually stored in the physical memory

● Each data value and structure details of the database objects are stored in
magnetic tapes, magnetic disks, optical disks etc.

● These information are usually the basic storage information of any

computer. This kind of information is called physical storage information

● They are least known to any programmer. This is called physical level of
data.
Logical Level

● This is next higher level of abstraction which is used to describe what data
the database stores and what relationships exist in between the data items

● The information like table/view names, their columns, indexes and

constraints on them, mapping between the tables are all next level of
information related to database.
● This information defines the structure of the objects in the database. These
are all called logical levels of data.
● The developer and the DBA will have the knowledge about this data.
View Level
● How to represent data to user. Only part allowed to user. Multiple views can be
available. View which comes after login into the app. (Data base Administrator
– Full control)
● Ex: University Portal
● The user will get to see only the data stored in the database. Either they will
see whole data values or any specific records.
● They will not have any information about how they are stored, what kind of data
type it has, how many records it has etc.
● This level of abstraction is called view level.
Example

● In a STUDENT table example, records of each student which user sees are
view level of information.
● Columns, their data types, their mapping, and constraints like primary key,
foreign key information are the logical level of information.
● The actual structure of table and data are stored in the server’s memory. This
is physical level of information.
Data Independence
● Database systems are designed in multi-layers
 DATA INDEPENDENCE
• Physical level of abstraction is the lowest level of abstraction
and view level of abstraction is the highest level of abstraction.
Based on these levels of abstraction, we have two types of
data independence.

• Physical Data Independence: Suppose there was a change

in memory size of the database servers. This will not affect the
logical structure of any of the objects in the database. They
are completely independent of the physical structure. This is
called physical data independence.
• Change in Storage Structure, Data Structure change, Index
• Logical Data Independence: Any changes to the database objects
like changes to table structure, size or addition/removal of columns
from the table will not affect user views. They will see the data like
before. This is called logical data independence.
• Insertion, deletion and alter table attributes

• By these two types of data independence, the isolation between the

physical and logical layer is achieved. This helps in reducing the time
and cost acquired by changes in any one level in the database.
Hence, the main purpose of database to provide abstract view of data
is achieved.
• Main Target is not to change anything in Application Program.
Database Architecture

45
Database Users and Administrators

1. Naive User : are unsophisticated users who

interact with the system by invoking by
invoking one of the application Ex. User who
wishes to find A/c balance.

2. Application programmers –are computer

professionals who write application
programs

3. Sophisticated users – form requests in a

database query language

4. Database Administrator (DBA) - A person

who has centralised control over the system.
46
Database Administrator (DBA)
●Responsibilities of DBA :
○Schema definition
○Schema and physical organization modification
○Granting of authorization to access the database
○Specifying integrity constraints
○Routine Maintenance
■ Taking periodic backups
■ Ensuring the enough disk space
47
■ Monitoring performance of jobs running
Components of DBMS
● Components of DBMS is broadly
classified as :

1. Query processor

2. Storage Manager

3. Disk Storage

48
Components of DBMS
1. Query processor
I. DDL interpreter:
which interprets DDL statements
and records the definition.

II. DML Compiler:

Which translates DML statements
in a query language and then to
low level instructions.

III. Query Evaluation engine:

Which executes low level
instructions

49
Components of DBMS
2. Storage Manager:
- is a program module that provides
interface between low level data stored
& query processor.

- The storage manager is responsible to

the following tasks:

■ Interaction with the file manager

■ Efficient storing, retrieving and
updating of data

50
Components of DBMS
● Storage Manager:
● File Manager: which manages the
allocation of space on disk.

● Buffer Manager: which is responsible for

fetching of data from disk.

● Authorization & Integrity Manager : which

checks authority of a user & integrity of data.

● Transaction Manager: ensures that the

database remains in a consistent (correct)
state despite system failures (e.g., power
failures and operating system crashes) and
transaction failures.
51
Components of DBMS
● Disk Storage:
● Data Files :
○ Which stores the database itself.

● Data Dictionary :
○ stores metadata. i.e. data about data

● Indices :
○ which provides fast access to data items.

52
DBMS System Architecture

● At very high level, a database is considered as

shown in below diagram. Let us see them in
detail below.
DBMS System Architecture

● Applications: - It can be considered as a user friendly web page where the

user enters the requests. Here he simply enters the details that he needs and
presses buttons to get the data.
● End User: - They are the real users of the database. They can be developers,
designers, administrator or the actual users of the database.
DBMS System Architecture
● DDL: - Data Definition Language (DDL) is a query fired to create
database, schema, tables, mappings etc in the database. These are
the commands used to create the objects like tables, indexes in the
database for the first time. In other words, they create structure of the
database.

● DDL Compiler: - This part of database is responsible for processing

the DDL commands. That means these compiler actually breaks down
the command into machine understandable codes. It is also
responsible for storing the metadata information like table name, space
used by it, number of columns in it, mapping information etc.
 DBMS System Architecture

● DML (Data Manipulation Language) Compiler: - When the user

inserts, deletes, updates or retrieves the record from the database, he
will be sending request which he understands by pressing some buttons.
But for the database to work/understand the request, it should be broken
down to object code. This is done by this compiler.
● Query Optimizer: - When user fires some request, he is least bothered
how it will be fired on the database. He is not all aware of database or its
way of performance. But whatever be the request, it should be efficient
enough to fetch, insert, update or delete the data from the database. The
query optimizer decides the best way to execute the user request which
is received from the DML compiler.
 DBMS System Architecture

● Stored Data Manager: - This is also known as Database Control System. It is one the main
central system of the database. It is responsible for various tasks,
○ It converts the requests received from query optimizer to machine understandable form. It
makes actual request inside the database.
○ It helps to maintain consistency and integrity by applying the constraints. That means, it
does not allow inserting / updating / deleting any data if it has child entry. Similarly it does not
allow entering any duplicate value into database tables.
○ It controls concurrent access. If there is multiple users accessing the database at the same
time, it makes sure, all of them see correct data. It guarantees that there is no data loss or
data mismatch happens between the transactions of multiple users.
○ It helps to backup the database and recover data whenever required. Since it is a huge
database and when there is any unexpected exploit of transaction, and reverting the
changes are not easy. It maintains the backup of all data, so that it can be recovered.
 DBMS System Architecture
● Data Files: - It has the real data stored in it. It can be stored as magnetic
tapes, magnetic disks or optical disks.
● Compiled DML: - Some of the processed DML statements (insert, update,
delete) are stored in it so that if there is similar requests, it will be re-used.
● Data Dictionary: - It contains all the information about the database. As the
name suggests, it is the dictionary of all the data items. It contains description
of all the tables, view, materialized views, constraints, indexes, triggers etc.
 Database Administrator

● There will be unused memory in database,making the memory inevitably

huge.These administration and maintenance of database is taken care by
database Administrator(DBA).

● The important difference between database user and database administrator is

that :database user are only concerned with use of database as per their need
while DBA has to manage and maintain the database.

● One of the main reasons for using DBMSs is to have central control of both the
data and the programs that access those data. A person who has such central
control over the system is called a database administrator(DBA).
 The functions of DBA include:
● Schema definition: The DBA creates the original db schema by executing a set
of data definition statements in the DDL.
● Storage structure and access method definition:
● Schema and physical organization modification: The DBA carries out
changes to the schema and physical organization to reflect the changing needs
of the organization, or to alter the physical organization to improve performance.

● Granting of authorization for data access: By granting different types of

authorization, the database administrator can regulate which parts of database
various users can access. The authorization information is kept in a special
system structure that the database system consults whenever someone
attempts to access the data in the system.
● Routine maintenance: Examples of the DBA’s routine
maintenance activities are,
⮚ Periodically backing up the db, either onto tapes or onto
remote servers, to prevent loss of data in case of disasters
such as flooding.
⮚ Ensuring that enough free disk space is available for normal
operations, and upgrading disk space as required.
⮚ Monitoring job running on the db and ensuring that
performance is not degraded by very expensive tasks
submitted by some users.
Thanks….