Database:

It is a collection of related data and data is a collection of recordable facts that can be processed to
produce information. For example, if we have data about marks obtained by all students, we can then
conclude about toppers and average marks.

Types of Databases:
1. Text database: When data is organized in a text file in rows and columns, it can be used to store,
organize, protect, and retrieve data. Saving a list of names in a file, starting with first name and
followed by last name, would be a simple database.
2. Desktop Databases: These are more complex than a text database but intended for a single user. A
Microsoft Excel spreadsheet or Microsoft Access is good examples of desktop database programs.
The benefit of desktop database programs over text databases is the speed of changing data, and
the ability to store large amounts of data while keeping performance of the system manageable.
3. Relational Databases: These allow multiple users to work with the data at the same time, creating
advanced security for access to the data. Examples include SQL Server, Oracle, and MySQL.
4. Object Oriented Databases: The most innovative structures for storing data today are object-
oriented databases. It is based on object-oriented programming (OOP). The data is represented
and stored in the form of objects. db4o, Smalltalk.

Characteristics:
1. Real-world entity: A modern DBMS is more realistic and uses real-world entities to design its
architecture. For example, a school database may use students as an entity and their age as an
attribute.
2. Relation-based tables: DBMS allows entities and relations among them to form tables.
3. Query Language: DBMS is equipped with query language, which makes it more efficient to retrieve
and manipulate data. A user can apply as many and as different filtering options as required to
retrieve a set of data.
4. ACID Properties: DBMS follows the concepts of Atomicity, Consistency, Isolation, and Durability
(ACID). These concepts are applied on transactions, which manipulate data in a database. ACID
properties help the database stay healthy in multi-transactional environments and in case of
failure.
5. Multiple views: DBMS offers multiple views for different users. A user who is in the Sales
department will have a different view of database than a person working in the Production
department.
Note: Add 6 Advantages in this answer.

Database Applications:
1. Banking: all transactions
2. Airlines: reservations, schedules
3. Universities: registration, grades
4. Sales: customers, products, purchases
5. Manufacturing: production, inventory, orders, supply chain
6. Human resources: employee records, salaries, tax deductions

Database Management System (DBMS):

DBMS is a computer based record keeping system which stores data in such a way that it becomes
easier to retrieve, manipulate, and produce information. It maintains the information about one
particular enterprise that may be necessary to the decision making process involved in the
management of that organisation.

DBMS File Processing System

Data Redundancy problem, i.e., duplication of
Minimal Data Redundancy problem.
information in different files exists.
No Data Inconsistency problem. Data Inconsistency exist here
Accessing database is easier Accessing is comparatively difficult
Data is scattered in various files and files may be of
No Data Isolation problem.
different format, so data isolation problem exists
Concurrent access and recovery is possible. Concurrent access and recovery is not possible
Integrity constraints like account balance>0
No Data Integrity problem. becomes part of program code, so difficult to add
new constraints
Data is secured. Security of data is not good

Advantages of DBMS in an Enterprise:

1. Data Sharing is improved in the Organization
2. Improvement in Data Security
3. Effective Data Integration
4. Database Management Systems Minimize Data Inconsistency
5. Better Access to Data
6. Quick Decision Making
7. Provision of Backup and Recovery

Database System Architecture:

Three-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.
1. Database (Data) Tier: At this tier the database resides along with its query processing languages.
2. Application (Middle) Tier: At this tier reside the application server and the programs that access the
database.
3. User (Presentation) Tier: At this layer, multiple views of the database can be provided by the
application.

Fig. 1: Overall DBMS Architecture

Database Administrator:
A database administrator (DBA) coordinates and performs all the activities of the database system in
order to maintain a successful database environment. Responsibilities include:
1. Defining the schema
2. Designing, implementing, and maintaining the database system;
3. Training employees in database management
4. Granting user authority to access the database
5. Resolving issues related to performance bottlenecks
6. Monitoring performance and responding to changes in requirements
7. Provide reporting on various metrics including availability, usage and performance
8. A DBA is expected to stay alert of emerging technologies and new design approaches and have
experience with one of the major database management products, such as SQL, SAP, and Oracle-
based software.
 Database Concepts

ADVANTAGES OF DATABASE
1. Reduced redundancy:
Database redundancy means duplication of data. Non-
database systems maintain separate copy of data for each
application.
For example:
In college, student records are maintained and hostel also
maintains the student records for those students who live
in hostel. Though the records of hosteller students are
already being maintained by the college, the hostel keeps
separate copy of it. But this duplication of data leads to
inconstancy or incorrect data.
 Database Concepts

ADVANTAGES OF DATABASE
2. Controlled Data Inconsistency:

When the redundancy is not controlled, there may be

occasions on which two entries about the same data do
not agree(that is one of them stores updated information
and the other does not.) at such times, database is said to
be inconsistence.

By controlling redundancy, the inconsistency is also

controlled. The database ensures any change is made to
either of the two entries is automatically made to the other.
This process is known as propagating update.
 Database Concepts

ADVANTAGES OF DATABASE
3. Shared Data

Sharing of data mean that individual piece of data in the

database may be shared among several different users, in
the sense that each of those users may have access to the
same piece of data and each of them may use it for
different purposes.
 Database Concepts

ADVANTAGES OF DATABASE
4. Standardized Data

The database management systems can ensure that all

the data (that is stored centrally) follow the applicable
standards. There may be certain standards laid by the
company or organization using the database.
Similarly, there may be national and or international
standards. Standardizing stored data formats is particularly
desirable as an aid to data interchange or migration
between systems.
 Database Concepts

ADVANTAGES OF DATABASE
5. Secured Data
A database management system ensures data security
and privacy by ensuring that the only means of access is
through proper channel and also by carrying out
authorization checks whenever access to sensitive data is
attempted.
Data Security: Data security refers to protection of data against
accidental or intentional disclosure to unauthorized persons, or
unauthorized modification or destructions.
Privacy of Data: it refers to the rights of individuals and
organizations to determine for themselves when, how and what
extent information about them is to be transmitted to others
 Database Concepts

ADVANTAGES OF DATABASE
6. Integrated Data
When database contains data employed by many different
users it is important that association between data items
not to be destroyed. Hardware failures and various types
of accidents will occur occasionally. The storage of data
and its updation, and insertion procedures defined by the
database, are such that the system can easily recover
from these circumstances without harm to the data.
The database management system designs certain
integrity checks to ensure that data values confirm to some
specified rules. For example date cant be like 25/25/12; it
is invalid date.
 Database Concepts

DATABASE ABSTRACTION

Data abstraction simplifies database design. The major

purpose of a database system is to provide users with
an abstract view of the system. The system hides certain
details of how data is stored and created and maintained
Complexity should be hidden from database users.

Three Levels of Abstractions are,

1.Physical Level (Internal Level)

2.Conceptual Level
3.External Level (View Level)
 Database Concepts

THREE LEVELS OF ABSTRACTION

VIEW 1 VIEW 2 ……. VIEW n

CONCEPTUAL LEVEL

PHYSICAL LEVEL
 Database Concepts

1. PHYSICAL LEVEL (INTERNAL OR LOW LEVEL)

It defines how data is stored. It is very complex and used
by developers. & it deals with,

How the data are stored.

E.g. index, B-tree, hashing.
Interface between OS and record structure.
Lowest level of abstraction.
Complex low-level structures described in detail.
 Database Concepts

1. CONCEPTUAL LEVEL
It defines data in terms of a data model. It tells what
data is stored and described in small numbers. This
level is usually used by DBA (Database administrator)
& it deals with,

Next highest level of abstraction.

Describes what data are stored.
Describes the relationships among data.
Database administrator level.
 Database Concepts

1. EXTERNAL LEVEL OR VIEW LEVEL

It defines a number of simplified domain-specific views.
It describes only part of databases. This level is used
by users.

Highest level.
Describes part of the database for a particular group
of users.
Can be many different views of a database.
E.g. In a School get a view of Student details, but not
of payroll data of employees or teachers of the school.
 Database Concepts

EXAMPLE OF THREE LEVELS OF ABSTRACTION

VIEW 1 VIEW 2 VIEW 3

Student Name, Roll No, …. Student
Marks Marks Name,Address

CONCEPTUAL LEVEL
Student Name char(25)
RollNo int
Address char(50)
Marks float

PHYSICAL LEVEL
Student name type=byte(25) offset =3
Rollno type=byte(2) offset = 28
 Database Concepts

DATA INDEPENDENCE
The ability to modify a scheme definition in one level
without affecting a scheme definition in the next higher
level is called DATA INDEPENDENCE
A major objective for three-level architecture is to
provide data independence, which means that upper
levels are unaffected by changes in lower levels.

There are two kinds of data independence:

 Logical data independence

 Physical data independence
 Database Concepts

DATA INDEPENDENCE

LOGICAL SCHEMA

………………………………………..

PHYSICAL SCHEMA
 Database Concepts

DATA INDEPENDENCE
SCHEMA
schema is nothing but definition of objects/tables
in a database.
A schema is the set of metadata (data dictionary) used
by the database, typically generated using DDL. It
defines attributes of the database, such as tables,
columns, and properties. A database schema is a
description of the data in a database.

One or more schemas can reside on the same

database.
 Database Concepts

DATA INDEPENDENCE
SCHEMA
A logical schema won't exist in your database. A logical
schema is a design-centric database structure built to meet
your business requirements. It is a model that exists on a
white board or in a diagramming tool. It is like the architect's
drawings of your database.
Logical schema is made out of actually what you plan(your
initial data model) and it's objects are:
 Tablespaces
 Tables
 Views
 procedures
 Database Concepts

DATA INDEPENDENCE
SCHEMA
A Physical schema is a term used in data management to
describe how data is to be represented and stored (files,
indices, et al.) in secondary storage using a particular
database management system (DBMS) (e.g., Oracle
RDBMS, Sybase SQL Server, etc.).
A Physical schema is one where you have your data files,
redo logs, control files etc, physical objects that reside on a
O.S. and are in row state. These objects are used by logical
structures to make our data make sense and readable.
 Database Concepts

DATA INDEPENDENCE
LOGICAL DATA INDEPENDENCE
Logical data independence indicates that the conceptual
schema can be changed without affecting the existing
external schemas.
Logical data is data about database, that is, it stores
information about how data is managed inside. For example,
a table (relation) stored in the database and all constraints,
which are applied on that relation.
Logical data independence is a kind of mechanism, which
liberalizes itself from actual data stored on the disk. If we do
some changes on table format it should not change the data
residing on disk.
 Database Concepts

DATA INDEPENDENCE
PHYSICAL DATA INDEPENDENCE
Physical data independence indicates that the physical
storage structures or devices could be changed without
affecting conceptual schema.
All schemas are logical and 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, that is, using SSD instead of Hard-
disks should not have any impact on logical data or
schemas.