K.R.

E Society's
Karnatak Arts, Science and Commerce College, Bidar
Department of Computer Science

Unit-4
Database Management Systems
 Introduction to Database:-
Database is a collection of inter-related data which helps in efficient retrieval, insertion and deletion
of data from database and organizes the data in the form of tables, views, schemas, reports etc. For
Example, university database organizes the data about students, faculty, and admin staff etc. which
helps in efficient retrieval, insertion and deletion of data from it.

 DDL:
It is short name of Data Definition Language, which deals with database schemas and descriptions, of
how the data should reside in the database.
 CREATE: to create a database and its objects like (table, index, views, store procedure, function,
and triggers)
 ALTER: alters the structure of the existing 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 
It is short name of Data Manipulation Language which deals with data manipulation and includes
most common SQL statements such SELECT, INSERT, UPDATE, DELETE, etc., and it is used to
store, modify, retrieve, delete and update data in a database.
 SELECT: retrieve data from a database
 INSERT: insert data into a table
 UPDATE: updates existing data within a table
 DELETE: Delete all records from a database table
 MERGE: UPSERT operation (insert or update)
 CALL: call a PL/SQL or Java subprogram
 EXPLAIN PLAN: interpretation of the data access path
 LOCK TABLE: concurrency Control

 Database Management System: 

DBMS, is also known as Database Management System. It is also a software used to store and regain
user’s data, while also maintaining the required security measures. This includes a group of
programmers that can help to manipulate the database. In bigger systems, DBMS helps the users as
well as third party software to store and recover the data.
Examples are MySQL, MS SQL Server, Oracle and so on.

Page 1
K.R.E Society's
Karnatak Arts, Science and Commerce College, Bidar
Department of Computer Science

Fig: DBMS

DBMS allows users the following tasks:

Data Definition: It helps in creation, modification and removal of definitions that define the
organization of data in database.
Data Updation: It helps in insertion, modification and deletion of the actual data in the database.
Data Retrieval: It helps in retrieval of data from the database which can be used by applications for
various purposes.
User Administration: It helps in registering and monitoring users, enforcing data security,
monitoring performance, maintaining data integrity, dealing with concurrency control and recovering
information corrupted by unexpected failure.

 What is a File System?

File system is a method of organizing the files with a hard disk or other medium of storage. File
system arranges the files and helps in retrieving the files, when required. It is compatible with
different file types, such as mp3, doc, txt, mp4,etc and these are also grouped into directories. It also
influences the method of writing and reading data to the hard disk.
Examples are NTFS or the New Technology File System and EXT, the Extended File System.

File System

 Difference between File System and DBMS:

The file system is a collection of data and for any management with it, the user has to write the
procedures, while DBMS is a collection of data and user need not write the procedures for handling

Page 2
K.R.E Society's
Karnatak Arts, Science and Commerce College, Bidar
Department of Computer Science

the database. Learn about what is the difference between a file system and DBMS from the table
given.

FILE SYSTEM DBMS

Used to manage and organize the files stored in A software to store and retrieve the user’s data
the hard disk of the computer

Redundant data is present No presence of redundant data

Query processing is not so efficient Query processing is efficient

Data consistency is low Due to the process of normalization, the data

consistency is high

Less complex, does not support complicated More complexity in managing the data, easier to
transactions implement complicated transactions

Less security Supports more security mechanisms

Less expensive in comparison to DBMS Higher cost than the File system

Does not support crash recovery Crash recovery mechanism is highly supported

 Application of DBMS:
There are different fields where a database management system is utilized. Following are a few
applications which utilize the information base administration framework – 
1. Railway Reservation System –
In the rail route reservation framework, the information base is needed to store the record or
information of ticket appointments, status about train’s appearance, and flight. Additionally, if trains
get late, individuals become acquainted with it through the information base update.  
2. Library Management System –
There are loads of books in the library so; it is difficult to store the record of the relative multitude of
books in a register or duplicate. Along these lines, the data set administration framework (DBMS) is
utilized to keep up all the data identified with the name of the book, issue date, accessibility of the
book, and its writer.  
3. Banking –
Database the executive’s framework is utilized to store the exchange data of the client in the
information base.
4. Education Sector –
Presently, assessments are led online by numerous schools and colleges. They deal with all
assessment information through the data set administration framework (DBMS). In spite of that
understudy’s enlistments subtleties, grades, courses, expense, participation, results, and so forth all
the data is put away in the information base.   
5. Credit card exchanges – 
The database Management framework is utilized for buying on charge cards and age of month to
month proclamations.   

Page 3
K.R.E Society's
Karnatak Arts, Science and Commerce College, Bidar
Department of Computer Science

6. Social Media Sites –

We all utilization of online media sites to associate with companions and to impart our perspectives to
the world. Every day, many people group pursue these online media accounts like Pinterest,
Facebook, Twitter, and Google in addition to. By the utilization of the data set administration
framework, all the data of clients are put away in the information base and, we become ready to
interface with others.  
7. Broadcast communications – 
Without DBMS any media transmission organization can’t think. The Database the executive’s
framework is fundamental for these organizations to store the call subtleties and month to month
postpaid bills in the information base.  
8. Account –
The information base administration framework is utilized for putting away data about deals, holding
and acquisition of monetary instruments, for example, stocks and bonds in a data set. 
9. Online Shopping – 
These days, web-based shopping has become a major pattern. Nobody needs to visit the shop and
burn through their time. Everybody needs to shop through web based shopping sites, (for example,
Amazon, Flipkart, Snapdeal) from home. So all the items are sold and added uniquely with the
assistance of the information base administration framework (DBMS). Receipt charges, installments,
buy data these are finished with the assistance of DBMS.  
10. Human Resource Management – 
Big firms or organizations have numerous specialists or representatives working under them. They
store data about worker’s compensation, assessment, and work with the assistance of an information
base administration framework (DBMS).  
11. Manufacturing – 
Manufacturing organizations make various kinds of items and deal them consistently. To keep the
data about their items like bills, acquisition of the item, amount, inventory network the executives,
information base administration framework (DBMS) is utilized.  
12. Airline Reservation System – 
This framework is equivalent to the railroad reservation framework. This framework additionally
utilizes an information base administration framework to store the records of flight takeoff,
appearance, and defer status.  

 Database Users:
Database users are the one who really use and take the benefits of database. They directly interact with
the database by means of query language like SQL. These users will be scientists, engineers, analysts who
thoroughly study SQL and DBMS to apply the concepts in their requirement.

 Different types of Database Users:

Database users are categorized based up on their interaction with the data base.
These are seven types of data base users in DBMS.
1. Database Administrator (DBA) :
Database Administrator (DBA) is a person/team who defines the schema and also controls the 3 levels
of database.
The DBA will then create a new account id and password for the user if he/she need to access the data
base.
DBA is also responsible for providing security to the data base and he allows only the authorized
users to access/modify the data base.
 DBA also monitors the recovery and back up and provide technical support.

Page 4
K.R.E Society's
Karnatak Arts, Science and Commerce College, Bidar
Department of Computer Science

 The DBA has a DBA account in the DBMS which called a system or superuser account.
 DBA repairs damage caused due to hardware and/or software failures.
2. Naive / Parametric End Users :
Parametric End Users are the unsophisticated who don’t have any DBMS knowledge but they
frequently use the data base applications in their daily life to get the desired results.
For examples, Railway’s ticket booking users are naive users. Clerks in any bank is a naive user
because they don’t have any DBMS knowledge but they still use the database and perform their given
task.
3. System Analyst :
System Analyst is a user who analyzes the requirements of parametric end users. They check whether
all the requirements of end users are satisfied.
4. Sophisticated Users :
Sophisticated users can be engineers, scientists, business analyst, who are familiar with the database.
They can develop their own data base applications according to their requirement. They don’t write
the program code but they interact the data base by writing SQL queries directly through the query
processor.
5. Data Base Designers :
Data Base Designers are the users who design the structure of data base which includes tables,
indexes, views, constraints, triggers, stored procedures. He/she controls what data must be stored and
how the data items to be related.
6. Application Program :
Application Program are the back end programmers who writes the code for the application programs.
They are the computer professionals. These programs could be written in Programming languages
such as Visual Basic, Developer, C, FORTRAN, COBOL etc.
7. Casual Users / Temporary Users :
Casual Users are the users who occasionally use/access the data base but each time when they access
the data base they require the new information, for example, Middle or higher level manager.

 Introduction to SQL:
o SQL stands for Structured Query Language. It is used for storing and managing data in relational
database management system (RDMS).
o It is a standard language for Relational Database System. It enables a user to create, read, update
and delete relational databases and tables.
o All the RDBMS like MySQL, Informix, Oracle, MS Access and SQL Server use SQL as their
standard database language.
o SQL allows users to query the database in a number of ways, using English-like statements.

 SQL Rules:
SQL follows the following rules:
o Structure query language is not case sensitive. Generally, keywords of SQL are written in
uppercase.
o Statements of SQL are dependent on text lines. We can use a single SQL statement on one or
multiple text line.
o Using the SQL statements, you can perform most of the actions in a database.
o SQL depends on tuple relational calculus and relational algebra.

 SQL process:
o When an SQL command is executing for any RDBMS, then the system figure out the best way to
carry out the request and the SQL engine determines that how to interpret the task.

Page 5
K.R.E Society's
Karnatak Arts, Science and Commerce College, Bidar
Department of Computer Science

o In the process, various components are included. These components can be optimization Engine,
Query engine, Query dispatcher, classic, etc.
o All the non-SQL queries are handled by the classic query engine, but SQL query engine won't
handle logical files.

 Data Types:
Data types are used to represent the nature of the data that can be stored in the database table. For
example, in a particular column of a table, if we want to store a string type of data then we will have to
declare a string data type of this column.
Data types mainly classified into three categories for every database.
o String Data types
o Numeric Data types
o Date and time Data types

 String Data Types

It is used to specify a fixed length string that can contain numbers, letters, and special
CHAR(Size)
characters. Its size can be 0 to 255 characters. Default is 1.

It is used to specify a variable length string that can contain numbers, letters, and
VARCHAR(Size)
special characters. Its size can be from 0 to 65535 characters.

It is equal to CHAR() but stores binary byte strings. Its size parameter specifies the
BINARY(Size)
column length in the bytes. Default is 1.

It is equal to VARCHAR() but stores binary byte strings. Its size parameter specifies
VARBINARY(Size)
the maximum column length in bytes.

Page 6
K.R.E Society's
Karnatak Arts, Science and Commerce College, Bidar
Department of Computer Science

TEXT(Size) It holds a string that can contain a maximum length of 255 characters.

TINYTEXT It holds a string with a maximum length of 255 characters.

MEDIUMTEXT It holds a string with a maximum length of 16,777,215.

LONGTEXT It holds a string with a maximum length of 4,294,967,295 characters.

It is used when a string object having only one value, chosen from a list of possible
ENUM(val1, val2,
values. It contains 65535 values in an ENUM list. If you insert a value that is not in
val3,...)
the list, a blank value will be inserted.

SET( val1,val2,val3,... It is used to specify a string that can have 0 or more values, chosen from a list of
.) possible values. You can list up to 64 values at one time in a SET list.

BLOB(size) It is used for BLOBs (Binary Large Objects). It can hold up to 65,535 bytes.

 Numeric Data Types:

It is used for a bit-value type. The number of bits per value is specified in size. Its size can
BIT(Size)
be 1 to 64. The default value is 1.

It is used for the integer value. Its signed range varies from -2147483648 to 2147483647
INT(size) and unsigned range varies from 0 to 4294967295. The size parameter specifies the max
display width that is 255.

INTEGER(size) It is equal to INT(size).

It is used to specify a floating point number. Its size parameter specifies the total number
FLOAT(size, d)
of digits. The number of digits after the decimal point is specified by d parameter.

It is used to specify a floating point number. MySQL used p parameter to determine

FLOAT(p) whether to use FLOAT or DOUBLE. If p is between 0 to24, the data type becomes
FLOAT (). If p is from 25 to 53, the data type becomes DOUBLE().

DOUBLE(size, It is a normal size floating point number. Its size parameter specifies the total number of
d) digits. The number of digits after the decimal is specified by d parameter.

It is used to specify a fixed point number. Its size parameter specifies the total number of
DECIMAL(size, digits. The number of digits after the decimal parameter is specified by d parameter. The
d) maximum value for the size is 65, and the default value is 10. The maximum value for d is
30, and the default value is 0.

DEC(size, d) It is equal to DECIMAL(size, d).

It is used to specify Boolean values true and false. Zero is considered as false, and nonzero
BOOL
values are considered as true.

 Date and Time Data Types:

Page 7
K.R.E Society's
Karnatak Arts, Science and Commerce College, Bidar
Department of Computer Science

It is used to specify date format YYYY-MM-DD. Its supported range is from '1000-01-
DATE
01' to '9999-12-31'.

It is used to specify date and time combination. Its format is YYYY-MM-DD

DATETIME(fsp)
hh:mm:ss. Its supported range is from '1000-01-01 00:00:00' to 9999-12-31 23:59:59'.

It is used to specify the timestamp. Its value is stored as the number of seconds since the
TIMESTAMP(fsp
Unix epoch('1970-01-01 00:00:00' UTC). Its format is YYYY-MM-DD hh:mm:ss. Its
)
supported range is from '1970-01-01 00:00:01' UTC to '2038-01-09 03:14:07' UTC.

It is used to specify the time format. Its format is hh:mm:ss. Its supported range is from
TIME(fsp)
'-838:59:59' to '838:59:59'

It is used to specify a year in four-digit format. Values allowed in four digit format from
YEAR
1901 to 2155, and 0000.

 Classification of SQL:
SQL defines following ways to manipulate data stored in an RDBMS.
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 . 

 DDL: Data Definition Language:

This includes changes to the structure of the table like creation of table, altering table, deleting a table
etc. All DDL commands are auto-committed. That means it saves all the changes permanently in the
database.
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.DDL is a set of SQL commands used to

Page 8
K.R.E Society's
Karnatak Arts, Science and Commerce College, Bidar
Department of Computer Science

create, modify, and delete database structures but not data. These commands are normally not used by
a general user, who should be accessing the database via an application.
1. CREATE:
CREATE statements is used to define the database structure schema:
Syntax:
CREATE TABLE TABLE_NAME (COLUMN_NAME DATATYPES[,....]);
For example:
Create database university;
Create table students;
Create view for_students;
2. DROP
Drops commands remove tables and databases from RDBMS.
Syntax:
DROP TABLE ;
For example:
Drop object_type object_name;
Drop database university;
Drop table student;
3. ALTER
Alters command allows you to alter the structure of the database.
Syntax:
To add a new column in the table
ALTER TABLE table_name ADD column_name COLUMN-definition;
To modify an existing column in the table:
ALTER TABLE MODIFY(COLUMN DEFINITION....);
For example:
Alter table guru99 add subject varchar;
4. TRUNCATE:
This command used to delete all the rows from the table and free the space containing the table.
Syntax:
TRUNCATE TABLE table_name;
Example:
TRUNCATE table students;

 DML: Data Manipulation Language:

DML commands are used for manipulating the data stored in the table and not the table itself.
DML commands are not auto-committed. It means changes are not permanent to database, they can
be rolled back.
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. It is the component of
the SQL statement that controls access to data and to the database. Basically, DCL statements are
grouped with DML statements.
o INSERT
o UPDATE
o DELETE
a. INSERT: The INSERT statement is a SQL query. It is used to insert data into the row of a table.

Page 9
K.R.E Society's
Karnatak Arts, Science and Commerce College, Bidar
Department of Computer Science

Syntax:
INSERT INTO TABLE_NAME (col1, col2, col3,.... col N) VALUES (value1, value2, value3, .... 
valueN); 
Or
INSERT INTO TABLE_NAME VALUES (value1, value2, value3, .... valueN);    
For example:
INSERT INTO javatpoint (Author, Subject) VALUES ("Sonoo", "DBMS");  
b. UPDATE: This command is used to update or modify the value of a column in the table.
Syntax:
UPDATE table_name SET [column_name1= value1,...column_nameN = valueN] [WHERE CON
DITION]   
For example:
UPDATE students SET User_Name = 'Sonoo' WHERE Student_Id = '3'  
c. DELETE: It is used to remove one or more row from a table.
Syntax:
DELETE FROM table_name [WHERE condition];  
For example:
DELETE FROM javatpoint WHERE Author="Sonoo";  

 TCL: Transaction Control Language:

These commands are to keep a check on other commands and their affect on the database. These
commands can annul changes made by other commands by rolling the data back to its original state.
It can also make any temporary change permanent.

o COMMIT
o ROLLBACK
o SAVEPOINT
a. Commit: Commit command is used to save all the transactions to the database.
Syntax:
COMMIT;  
Example:
DELETE FROM CUSTOMERS  
WHERE AGE = 25;  
COMMIT;  
b. Rollback: Rollback command is used to undo transactions that have not already been saved to the
database.
Syntax:
ROLLBACK;  
Example:
DELETE FROM CUSTOMERS  
WHERE AGE = 25;  
ROLLBACK;  
c. SAVEPOINT: It is used to roll the transaction back to a certain point without rolling back the entire
transaction.
Syntax:
SAVEPOINT SAVEPOINT_NAME;  

Page 10
K.R.E Society's
Karnatak Arts, Science and Commerce College, Bidar
Department of Computer Science

 DCL: Data Control Language:

Data control languages are the commands to grant and take back authority from any database user.
DCL includes commands such as GRANT and REVOKE which mainly deal with the rights,
permissions, and other controls of the database system. 
o Grant
o Revoke
a. Grant: It is used to give user access privileges to a database.
Example
GRANT SELECT, UPDATE ON MY_TABLE TO SOME_USER, ANOTHER_USER;  

b. Revoke: It is used to take back permissions from the user.

Example
REVOKE SELECT, UPDATE ON MY_TABLE FROM USER1, USER2;  

 DQL: Data Query Language:

Data query language is used to fetch data from tables based on conditions that we can easily apply.
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. We can define
DQL as follows it is a component of SQL statement that allows getting data from the database and
imposing order upon it. It includes the SELECT statement. This command allows getting the data out
of the database to perform operations with it. When a SELECT is fired against a table or tables the
result is compiled into a further temporary table, which is displayed or perhaps received by the
program i.e. a front-end.
o SELECT
a) SELECT: This is the same as the projection operation of relational algebra. It is used to select the
attribute based on the condition described by WHERE clause.
Syntax:
SELECT expressions    
FROM TABLES    
WHERE conditions;  
For example:
SELECT emp_name  
FROM employee  
WHERE age > 20;  

Page 11