1.

1 Introduction
Data storage is not the only objective of DBMS. Even files were capable of storing of data. DBMS
became so popular because of their ability to perform operations such as data access and manipulation
in minimum possible cost. There is clear separation of schemas used to display data to end user and
schemas used to store the data on storage devices. No business today can survive without a complete
database management solution.

1.2 Basic DBMS terminology

Data initially was stored in files. But it was very difficult to manage data in files as it grew larger.
Performing search operation on data stored in files was very expensive. Then came the concept of
database and database management system. A database is a collection of interrelated data items in
the form of tables. A single database may consist of number of tables. Let us first discuss the concept
of a table.
A table is also known as entity and database can be called an entity set. A table is a logical
representation of data in matrix form, i.e. rows and columns. Each column of table represents an
attribute or property of the table. Each row in a table represents a tuple or record. A cell in a table is
where a row and column intersect.

Database is a collection of related data and data is a collection of facts and figures that can be
processed to produce information.
A database management system stores data in such a way that it becomes easier to retrieve,
manipulate, and produce information.

Consider the following for an example,

You may want to create a database for a college. So let the name of the database be "college". Now, a
database is a collection of tables or entities, and following are possible entities for "college" database.
1. Student
2. fees
3. Attendance
4. Scholarship
5. Library, etc.

Each entity can be represented in a form of table and one table consists of interrelated attributes.
Following is an example of attributes for student entity:
1. Name
2. Faher_Name
3. Roll_no
4. Class
5. Address
6. Contact_no, etc.

This information can be represented in the form of table as shown below: (Student_Record)
Name father_name roll_no Class address contact_no
Ajit Ravi 12 11 ABC 123
Sachin Jiten 34 11 JALANDHAR 456
Vikas Anoop 2 12 GHI 789
Dhruv Pankaj 5 12 MNO 167
A B 121 12 BHY 5678

The table above consists of 5 records. 34 is highlighted in the table and it represents a cell where attribute
roll_no and 2nd record intersects. As already stated, database is a collection of interrelated tables or
entities. Consider the following two tables

T1
Name roll_no class
T2
emp_code Department salary

Combination of these two tables cannot be considered as a database, as the two tables are not interrelated
to each other. It is important that the two tables have a common attribute, that is used to relate the two
tables.
Consider the following two tables for example,
T1 (Student_Record)
Name roll_no class

T2 (Book_Issue_Record)
roll_no book_title date_of_issue

Now, the two tables have roll_no as the common attribute and hence the two tables can be considered
as interrelated to each other. They both can be part of a single database.
Interrelated tables are used to gather information about a subject, like student from multiple tables. For
example, information about the attendance of student, status of fees paid, books issued by the student,
details of scholarship if any paid to a student can be gathered from multiple tables using the common
attribute "roll_no".

Now that you have little idea of what a database is, it is time to get familiar with the concept of database
management system. It is important that set of rules and procedures are defined for managing the
database. The view of data stored on the disk and the users view of data is entirely different. Database
management system is set of programs that are used to manage the database. Database management
system is responsible for assisting in faster execution of operations such as insertion, deletion and search
on database.

Following are some of the responsibilities that are performed by the database management
system.
1. providing support for operations such as inserting, updating, deleting and accessing of data.
2. Providing security against unauthorized access to data.
3. Maintaining data integrity and consistency at all times.
4. Provide backup for the data and also the ability to recover data in case of any crash or system failure.
5. Maintaining the catalog and directory of database objects.
6. Providing support for various user interface packages, such as the SQL interface for relational
database systems.

1.3 Components of DBMS

Following are the four components of DBMS
1. Hardware: It is the most basic requirement for creating and computer based system. You need a client
system from where requests can be made for accessing the database. Network devices are needed in
case the client and server are not on the same system. Storage space is important component of a DBMS
and you need storage space not only to save the operational data, but also to save the backup and the
achieved data.
2. Software: As already stated that DBMS is a set of programs that defines the rules and procedures
to access the data. Complete database management solutions are very costly now a days. A DBMS
software is not expected to facilitate access to data, but also provide other functionalities like security,
regular backup, client server support, ensuring data integrity and consistency, etc.
3. Data: Data is core component of an DBMS. Data term existed even before DBMS. Data is an
interrelated pieces of information that must be stored for processing. Data must be organized in a
manner that facilitates quick operations on it.
4. Procedures: it refers to the rules and regulations that must be followed when designing the database.
Procedures may differ on the basis of end user requirement or the objective for which the DBMS is
being designed.
1.4 Data abstraction
You must already be familiar with the fact that there is a difference in the way data is stored on the disk
and the way it is viewed by the user. There is an obvious logic why this difference exists, and it is all
for the benefit of the end user. A database can also be viewed from different angles based on different
levels of abstraction. Any entity in DBMS can be seen from different perspectives or levels of
complexity to make it reveal its current amount of abstraction.
Consider a computer system for example. When you buy a computer system and have a look at the
CPU, you know very little of what is inside the CPU and this is what you call high level of abstraction.
Very little or no knowledge of the details. But you open the cabin or CPU, you are able to see its major
components and what it is made of. Still there is very little knowledge of details and it is called
middle level of abstraction. Finally, if we proceed to detach and open any of the hardware unit, you
will be able to see more of the details, and this is what you call low level of abstraction.
Similarly, based on level of abstraction, data can be viewed from three different prospective.

Figure 1.1: DBMS view or DBMS architecture from data abstraction point of view.

Arrangement of things is what you all schema and in this case that thing is data. So, the three schemas
represent the arrangement of data. You can see in the figure above, data is represented in DBMS using
3 schemas that represent three different levels of abstraction.
Physical schema is also known as Internal schema and it represent the lowest level of abstraction. It
deals with the description of how raw data items, i.e. values for each attribute in a record are stored
on the physical storage device (Hard Disc, CD, Tape Drive etc.). It also describes the location or
physical address of the items and the size of the items in the storage device. This schema is useful for
database application developers and database administrator.
Conceptual schema is also known as Logical Schema and it represents the middle level of abstraction.
It deals with the overall logical structure of the entire database. This level deals with only the
structure of the database and has nothing to do with the raw data items of the physical schema. This
level of abstraction deals with defining the attributes for each table in the database, and that includes
the common attributes in different tables that defines the relationship between them. It also deals with
the type of values that will be stored in each attribute. Success of any database system depends on
careful design of conceptual schema.
External schema is also known as view schema and it represent the user view. External schema
represents the highest level of abstraction. It is designed keeping the end user in mind who has little
knowledge about the working of the DBMS. End user is least bothered about the functionality and
structure of the database and is only concerned with the information relevant to it. DBMS provides
more than one external schema, each different for a group of users. It is important that the user is
presented with only a limited view and not left confused by pushing all data in front of it. Also creating
different external schemas help to implement the data security, by hiding the information not meant for
that user. Virtual tables are used keeping in mind the need of the end user to design external views.
These are created dynamically for the end users at runtime. Some of the fields that an end user may
view might not be physically saved in the database. On the contrary, lots of attributes that are part of
the database and used to maintain the relations are hidden from the user.

1.5 Data Independence

It is a key property of an DBMS. According to this property, a DBMS must ensure that any changes
made to one level of schema of the database should require little or no change in the schema above to
it. Data independence does not apply to external schema, as there is no schema above it. Data
independence can be classified into the following two types:
1. Physical Data Independence: Physical data independence exists if changes made to the physical or
internal schema require no or little change in the conceptual or logical schema.
2. Logical Data Independence: Logical data independence exists if changes made to the conceptual or
logical schema require no or little change in the external or view schema. It is easy to achieve as
compared to physical data independence.

1.6 DBMS Architecture [system structure]

The database may not be saved at the same machine where the end user is working on. The machine
from where the end user requests for data is called client and the machine where the database is stored
is called the server. Database applications work on either a 2-tier or a 3-tier DBMS architecture. The
application in a two-tier architecture, resides at the client machine and invokes database system
functionality at the server machine through query language statements. ODBC And JDBC (API
standards) are used for interaction between the client and server. Whereas, in case of 3-tier architecture,
the client machine simply acts as a front end. It then communicates with an application server through
a forms interface, which is turn communicates with a database system. Application server contains the
business logic. 3-tier architecture is more appropriate for applications that run on the World Wide Web.

Figure 1.2: 2-tier and 3-tier architecture

Source: Database System Concepts by Abraham Silberschatz, Henry F. Korth, S. Sudarshan, Mc
Graw Hill.
Following is the system structure of a DBMS architecture

Figure1.3: System structure of DBMS

Source: Database System Concepts by Abraham Silberschatz, Henry F. Korth, S. Sudarshan, Mc
Graw Hill.
There are four types of users as you can see in the figure above:
1. Application programmers are the one responsible for coding the procedures and routines.
2. Naive users are one of the end users who have little or no knowledge of the system and often use
automatic teller machines to interact with the system.
3. Sophisticated users are familiar with the system and query languages and they themselves design
their ad-hoc queries to interact with the system.
4. Database administrator is responsible for the well being of the DBMS. DBA is responsible for
defining the schema, creating new used-id's, roles, access permissions , etc.

Query processor is responsible for optimizing the query such that its processing costs the minimum in
terms of time and resources needed. Transactional storage manager is responsible for managing the
data access and data manipulation calls. The storage system includes algorithms and data structures for
organizing and accessing data on disk. A data dictionary is a file or a set of files that contains a
database's metadata. The data dictionary contains records about other objects in the database, such as
data ownership, data relationships to other objects, and other data. The data dictionary is a crucial
component of any relational database. Indexing is a data structure technique to efficiently retrieve
records from the database files based on some attributes on which the indexing has been done.

Database Administrator

A Database Administrator (DBA) is individual or person responsible for controlling, maintenance,

coordinating, and operation of database management system. Managing, securing, and taking care of
database system is prime responsibility.

They are responsible and in charge for authorizing access to database, coordinating, capacity, planning,
installation, and monitoring uses and for acquiring and gathering software and hardware resources as
and when needed. Their role also varies from configuration, database design, migration, security,
troubleshooting, backup, and data recovery. Database administration is major and key function in any
firm or organization that is relying on one or more databases. They are overall commander of Database
system.

Types of Database Administrator (DBA) :

• Administrative DBA –
Their job is to maintain server and keep it functional. They are concerned with data
backups, security, trouble shooting, replication, migration etc.
• Data Warehouse DBA –
Assigned earlier roles, but held accountable for merging data from various sources into
data warehouse. They also design warehouse, with cleaning and scrubs data prior to
loading.
• Development DBA –
They build and develop queries, stores procedure, etc. that meets firm or organization
needs. They are par at programmer.
• Application DBA –
They particularly manages all requirements of application components that interact with
database and accomplish activities such as application installation and coordinating,
application upgrades, database cloning, data load process management, etc.
• Architect –
They are held responsible for designing schemas like building tables. They work to build
 structure that meets organisation needs. The design is further used by developers and
development DBAs to design and implement real application.
• OLAP DBA –
They design and builds multi-dimensional cubes for determination support or OLAP
systems.

Importance of Database Administrator (DBA) :

• Database Administrator manages and controls three levels of database like internal level,
conceptual level, and external level of Database management system architecture and in
discussion with comprehensive user community, gives definition of world view of
database. It then provides external view of different users and applications.
• Database Administrator ensures held responsible to maintain integrity and security of
database restricting from unauthorized users. It grants permission to users of database and
contains profile of each and every user in database.
• Database Administrator also held accountable that database is protected and secured and
that any chance of data loss keeps at minimum.

Role and Duties of Database Administrator (DBA) :

• Decides hardware –
They decides economical hardware, based upon cost, performance and efficiency of
hardware, and best suits organisation. It is hardware which is interface between end users
and database.
• Manages data integrity and security –
Data integrity need to be checked and managed accurately as it protects and restricts data
from unauthorized use. DBA eyes on relationship within data to maintain data integrity.
• Database design –
DBA is held responsible and accountable for logical, physical design, external model
design, and integrity and security control.
• Database implementation –
DBA implements DBMS and checks database loading at time of its implementation.
• Query processing performance –
DBA enhances query processing by improving their speed, performance and accuracy.
• Tuning Database Performance –
If user is not able to get data speedily and accurately then it may loss organization
business. So by tuning SQL commands DBA can enhance performance of database.
SQL
Structured Query Language(SQL) as we all know is the database language by the use of which we can
perform certain operations on the existing database and also we can use this language to create a
database. SQL uses certain commands like Create, Drop, Insert, etc. to carry out the required tasks.
These SQL commands are mainly categorized into four categories as:

1. DDL – Data Definition Language

2. DQl – Data Query Language
3. DML – Data Manipulation Language
4. DCL(Data Control Language

[Link](Data Definition Language) : DDL or Data Definition Language actually consists of the SQL
commands that can be used to define the database schema. It simply deals with descriptions of the
database schema and is used to create and modify the structure of database objects in the database.

Examples of DDL commands:

CREATE – is used to create the database or its objects (like table, index, function, views, store
procedure and triggers).
DROP – is used to delete objects from the database.
ALTER-is used to alter the structure of the database.
TRUNCATE–is used to remove all records from a table, including all spaces allocated for the records
are removed.
COMMENT –is used to add comments to the data dictionary.
RENAME –is used to rename an object existing in the database.

[Link] (Data Query Language) :

DQL statements are used for performing queries on the data within schema objects. The purpose of the
DQL Command is to get some schema relation based on the query passed to it.
Example of DQL:
SELECT – is used to retrieve data from the database.

[Link](Data Manipulation Language): The SQL commands that deals with the manipulation of
data present in the database belong to DML or Data Manipulation Language and this includes most of
the SQL statements.
Examples of DML:
INSERT – is used to insert data into a table.
UPDATE – is used to update existing data within a table.
DELETE – is used to delete records from a database table.

4. DCL(Data Control Language): DCL includes commands such as GRANT and REVOKE which
mainly deal with the rights, permissions and other controls of the database system.

Examples of DCL commands:

GRANT-gives users access privileges to the database.
REVOKE-withdraw user’s access privileges given by using the GRANT command.

TCL(transaction Control Language): TCL commands deal with the transaction within the database.
Examples of TCL commands:
COMMIT– commits a Transaction (durability).
ROLLBACK– rollbacks a transaction in case of any error occurs.
SAVEPOINT–sets a savepoint/ checkpoint within a transaction.
SET TRANSACTION–specify characteristics for the transaction.