BENEFITS OF DBMS:-

1. Faster storage and retrieval time

2. Accessing data is easy and fast
3. Data integrity(credibility of the data) and security. In govt. offices, anyone can pick up any file! But
DBMS requires password authorization to access important files.
4.Backups and recovery are possible only in digitalworld, hence today physical files are becoming
extinct.
POINTS TO REMEMBER IN DBMS
1. In DBMS data are stored in relational model, rows and columns (remember the steel racks!).
2. There are two types of Data Manipulation Languages (DML),Structured Query Language (SQL) and
Data Definition Language (DDL).
3.DML are computer languages also known asquery language, it is used for accessing and working on the
data.
In other words, DML (SQL and DDL), are like the languages people use, Hindi or English, in an office to
give orders to the peon!
4.Data redundancy–which means there are no duplications of data. No multiple files of the same kind.

What are the difference between DDL, DML and DCL commands?
Submitted by admin on Wed, 2004-08-04 13:49
DDL
Data Definition Language (DDL) statements are used to define the database structure or schema. Some
examples:
CREATE - to create objects in the database
ALTER - alters the structure of the database
DROP - delete objects from the database
TRUNCATE - remove all records from a table, including all spaces allocated for the records are removed
COMMENT - add comments to the data dictionary
RENAME - rename an object
DML
Data Manipulation Language (DML) statements are used for managing data within schema objects.
Some examples:
SELECT - retrieve data from the a database
INSERT - insert data into a table
UPDATE - updates existing data within a table
DELETE - deletes all records from a table, the space for the records remain
MERGE - UPSERT operation (insert or update)
CALL - call a PL/SQL or Java subprogram
EXPLAIN PLAN - explain access path to data
LOCK TABLE - control concurrency
DCL
Data Control Language (DCL) statements. Some examples:
GRANT - gives user's access privileges to database
REVOKE - withdraw access privileges given with the GRANT command
TCL
Transaction Control (TCL) statements are used to manage the changes made by DML statements. It
allows statements to be grouped together into logical transactions.
COMMIT - save work done
SAVEPOINT - identify a point in a transaction to which you can later roll back
ROLLBACK - restore database to original since the last COMMIT
SET TRANSACTION - Change transaction options like isolation level and what rollback segment to use

data Base Management Systems

Data: Data is nothing but collection of information

Meta Data: The data which describes the characteristics and properties of other data is called
Meta data. Simply we can say that Meta data is nothing but data about data.

Data Base: Collection of a related data with an implicit meaning is called as Data Base

Example: If you want to store details of a person i.e. Name, Phone Number, Address and Place.
You will store it in a hard drive or in a software such as MS Word or MS Excel called as data
base.

We can also consider a page as a data base as it is the collection of related data at one place.

So this collection of related data together at one place is called Data Base.

This information stored is accessed by application program by sending some queries.

Data Base Management System (DBMS): DBMS is a software used for

maintaining and implementing the data base.

Or we can say “A Software that manages the collection of Data”

Technical Definition:- “A software that provides an efficient environment to help us in

STORING and RETRIEVING the Data base of an enterprise.

Applications of DBMS:

 Banking
 Tele Communications
 Credit Card Transactions
 Airlines
  Finance
 Sales
 Human Resources
 Manufacturing

Data Base + DBMS = Data Base System

Read the Study notes on Operating System for IBPS SO (IT Officer)

There are three types of actors for maintaining this data base

Data Base Administrator: Responsible for maintaining the resources

Data Base Designer: Identifies the data in data base

End User: Responsible for querying, updating and generating the reports.

Advantages of DBMS:

 Redundancy will be decreased

 Multiple users can access at same time
 Restricting Unauthorized Access
 Providing Backup and Recovery

Data Base Management System (DBMS) Languages:

 DDL (Data Definition Language)

 DML (Data Manipulation Language)
 SDL (Storage Definition Language)
 VDL (View Definition Language)

Data Abstraction: An abstract view of the hidden data details of how data is stored and
maintained is given in architecture in three levels or layers.

Levels of Data Abstraction:

 Physical Level
 Logical Level
 View Level

Instance: Collection of data stored in data base at particular moment is called instance.

Schema: Overall design of data base is called schema

There are three types of schema

 1. Physical Schema
2. Logical Schema
3. Conceptual Schema

Data Base Users and Administrator

 Naive Users
 Online Users
 Application Users
 Sophisticated Users
 Specialized Users

DBA (Data Base Administrator) : The person who controls both Data and the Programs that
access that data in the Data Base is called the Data Base Administrator (DBA).

Functions of the DBA are,

 Defining Schemas (arrangement of Data)

 Creating Storage Structure and Access Methods ( I mean how to store data and access
that).
 Modifying the storage Data
 Granting Authorization Permissions
 Specifying the CONDITIONS of the data storage
 Periodically Updating the Data Base etc.,

Data Models: A data model is a collection of conceptual tools for describing data, data
relationships, data semantics and consistency constraints

The Object based logical models are described in the different following models.

 The E-R (Entity-Relationship ) Model

 The Object-Based Logical Model
 The Semantic Data Model
 The Functional Data Model

Entity Relationship Model: (ER Model)

Symbols of ER Diagrams:
An E-R Diagram symbols and notations are as follows:
Cardinality: It is the ratio that expresses the number of entities on one side associated with
number of entities on another side

Types of Cardinalities:
One-to-one: A manager will manage only one branch of the bank

One-to-many: A branch may hold many accounts

Many-to-many: Many customers may own many accounts

PART-2

IBPS has recently released the notification for the recruitment of IBPS Specialist Officer.
The exam is scheduled to be held on 31 January 2015. In the Specialist Officer, you need
to prepare a professional knowledge exam which have a weightage of 75 marks.

So, here in this post we are providing an important studynote for the preparation of SO
(IT) officer. The study note will help you to secure good marks in Professional
Knowledge test. Read the study notes on DBMS for IBPS SO (IT) officer.

As we discuss in our Last Article of BDMS ER Model, The ER Model has the power of
expressing database entities in a conceptual hierarchical manner. As the hierarchy
goes up, it generalizes the view of entities, and as we go deep in the hierarchy, it gives us
the detail of every entity included.

Read the Study notes on Operating System for IBPS SO (IT Officer)

Generalization

In generalization, a number of entities are brought together into one generalized entity
based on their similar characteristics. For example, pigeon, house sparrow, crow and
dove can all be generalized as Birds.
Specialization

Specialization is the opposite of generalization.

Inheritance

Inheritance is an important feature of Generalization and Specialization. It allows

lower-level entities to inherit the attributes of higher-level entities.
Database Schema

Schema: Overall design of data base is called schema.

There are three types of schema

1. Physical Schema
2. Logical Schema
3. Conceptual Schema

A database schema is the skeleton structure that represents the logical view of the
entire database. It defines how the data is organized and how the relations among them
are associated. It formulates all the constraints that are to be applied on the data.

A database schema defines its entities and the relationship among them. It contains a
descriptive detail of the database, which can be depicted by means of schema diagrams.
It’s the database designers who design the schema to help programmers understand the
database and make it useful.
A database schema can be divided broadly into two categories −

o Physical Database Schema − This schema pertains to the actual storage of data
and its form of storage like files, indices, etc. It defines how the data will be
stored in a secondary storage.
o Logical Database Schema − This schema defines all the logical constraints that
need to be applied on the data stored. It defines tables, views, and integrity
constraints.

Data Independence

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.
Logical Data Independence

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 its
constraints, 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 the disk.

Physical Data Independence

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.

Codd’s Rule

These rules can be applied on any database system that manages stored data using only
its relational capabilities. This is a foundation rule, which acts as a base for all the other
rules.

Rule 1: Information Rule

The data stored in a database, may it be user data or metadata, must be a value of some
table cell. Everything in a database must be stored in a table format.

Rule 2: Guaranteed Access Rule

Every single data element (value) is guaranteed to be accessible logically with a
combination of table-name, primary-key (row value), and attribute-name (column
value). No other means, such as pointers, can be used to access data.

Rule 3: Systematic Treatment of NULL Values

The NULL values in a database must be given a systematic and uniform treatment.
This is a very important rule because a NULL can be interpreted as one the following −
data is missing, data is not known, or data is not applicable.

Rule 4: Active Online Catalog

The structure description of the entire database must be stored in an online catalog,
known as data dictionary, which can be accessed by authorized users. Users can use the
same query language to access the catalog which they use to access the database itself.

Rule 5: Comprehensive Data Sub-Language Rule

A database can only be accessed using a language having linear syntax that supports
data definition, data manipulation, and transaction management operations. If the
database allows access to data without any help of this language, then it is considered as
a violation.

Rule 6: View Updating Rule

All the views of a database, which can theoretically be updated, must also be updatable
by the system.

Rule 7: High-Level Insert, Update, and Delete Rule

A database must support high-level insertion, updation, and deletion. This must not be
limited to a single row, that is, it must also support union, intersection and minus
operations to yield sets of data records.

Rule 8: Physical Data Independence

The data stored in a database must be independent of the applications that access the
database. Any change in the physical structure of a database must not have any impact
on how the data is being accessed by external applications.

Rule 9: Logical Data Independence

The logical data in a database must be independent of its user’s view (application). Any
change in logical data must not affect the applications using it.This is one of the most
difficult rule to apply.
Rule 10: Integrity Independence

A database must be independent of the application that uses it. All its integrity
constraints can be independently modified without the need of any change in the
application. This rule makes a database independent of the front-end application and its
interface.

Rule 11: Distribution Independence

The end-user must not be able to see that the data is distributed over various locations.
Users should always get the impression that the data is located at one site only. This rule
has been regarded as the foundation of distributed database systems.

Rule 12: Non-Subversion Rule

If a system has an interface that provides access to low-level records, then the interface
must not be able to subvert the system and bypass security and integrity constraints.

Relational Algebra
Relational algebra is a procedural query language, which takes instances of relations
as input and yields instances of relations as output. It uses operators to perform queries.
An operator can be either unary or binary. The fundamental operations of relational
algebra are as follows −

o Select
o Project
o Union
o Set different
o Cartesian product
o Rename

We will discuss all these operations in the following sections.

Select Operation (σ)

It selects tuples that satisfy the given predicate from a relation.

Notation − σp(r)

Where σ stands for selection predicate and r stands for relation. p is prepositional logic
formula which may use connectors like and, or, and not. These terms may use relational
operators like − =, ≠, ≥, < , >, ≤.

For example −
σsubject = “database”(Books)

Output − Selects tuples from books where subject is ‘database’.

σsubject = “database” and price = “450”(Books)

Output − Selects tuples from books where subject is ‘database’ and ‘price’ is 450.

σsubject = “database” and price = “450” or year > “2015”(Books)

Output − Selects tuples from books where subject is ‘database’ and ‘price’ is 450 or
those books published after 2015.

Project Operation (∏)

It projects column(s) that satisfy a given predicate.

Notation − ∏A1, A2, An (r)

Where A1, A2 , An are attribute names of relation r.

Duplicate rows are automatically eliminated, as relation is a set.

For example −

∏subject, author (Books)

Selects and projects columns named as subject and author from the relation Books.

Union Operation (∪)

It performs binary union between two given relations and is defined as −

r ∪ s = { t | t ∈ r or t ∈ s}

Notion − r U s

Where r and s are either database relations or relation result set (temporary relation).

For a union operation to be valid, the following conditions must hold −

o r, and s must have the same number of attributes.

o Attribute domains must be compatible.
o Duplicate tuples are automatically eliminated.

∏ author (Books) ∪ ∏ author (Articles)

Output − Projects the names of the authors who have either written a book or an article
or both.

Set Difference (−)

The result of set difference operation is tuples, which are present in one relation but are
not in the second relation.

Notation − r − s

Finds all the tuples that are present in r but not in s.

∏ author (Books) − ∏ author (Articles)

Output − Provides the name of authors who have written books but not articles.

Cartesian Product (Χ)

Combines information of two different relations into one.

Notation − r Χ s

Where r and s are relations and their output will be defined as −

r Χ s = { q t | q ∈ r and t ∈ s}

σauthor = ‘tutorialspoint’(Books Χ Articles)

Output − Yields a relation, which shows all the books and articles written by
tutorialspoint.

Rename Operation (ρ)

The results of relational algebra are also relations but without any name. The rename
operation allows us to rename the output relation. ‘rename’ operation is denoted with
small Greek letter rho ρ.

Notation − ρ x (E)

Where the result of expression E is saved with name of x.

Additional operations are −

o Set intersection
o Assignment
o Natural join
 Relational Calculus
In contrast to Relational Algebra, Relational Calculus is a non-procedural query
language, that is, it tells what to do but never explains how to do it.

Relational calculus exists in two forms −

Tuple Relational Calculus (TRC)

Filtering variable ranges over tuples

Notation − {T | Condition}

Returns all tuples T that satisfies a condition.

For example −

{ T.name | Author(T) AND T.article = ‘database’ }

Output − Returns tuples with ‘name’ from Author who has written article on ‘database’.

TRC can be quantified. We can use Existential (∃) and Universal Quantifiers (∀).

For example −

{ R| ∃T ∈ Authors(T.article=’database’ AND R.name=T.name)}

Output − The above query will yield the same result as the previous one.

Domain Relational Calculus (DRC)

In DRC, the filtering variable uses the domain of attributes instead of entire tuple values
(as done in TRC, mentioned above).

Notation −

{ a1, a2, a3, …, an | P (a1, a2, a3, … ,an)}

Where a1, a2 are attributes and P stands for formulae built by inner attributes.

For example −

{< article, page, subject > | ∈ TutorialsPoint ∧ subject = ‘database’}

Output − Yields Article, Page, and Subject from the relation TutorialsPoint, where
subject is database.

Just like TRC, DRC can also be written using existential and universal quantifiers. DRC
also involves relational operators.