DATABASE MANAGEMENT SYSTEM

Data :- Raw facts and figures which are useful to an organization. We cannot take decisions
on the basis of data.

Information:-Well processed data is called information. We can take decisions on the basis
of information

Database

Database is a computer based record keeping system which is used to record, maintain and
retrieve data. It is an organized collection of interrelated (persistent) data.

Field: Set of characters that represents specific data element.

Record: Collection of fields is called a record. A record can have fields of different data
types.

Tuple: A row in a relation is called a tuple.

Attribute: A column in a relation is called an attribute. It is also termed as field or data

item.

Degree: Number of attributes in a relation is called degree of a relation.

Cardinality: Number of tuples in a relation is called cardinality of a relation.

Primary Key: Primary key is a key that can uniquely identifies the records/tuples in a
relation. This key can never be duplicated and NULL.

Foreign Key: Foreign Key is a key that is defined as a primary key in some other relation.
This key is used to enforce referential integrity in RDBMS.

Candidate Key: Set of all attributes which can serve as a primary key in a relation.

Alternate Key: All the candidate keys other than the primary keys of a relation are
alternate keys for a relation

Database Management System (DBMS)

A Database Management System (DBMS) is a collection of interrelated files and set of

programs which allows users to access and modify files. It provides a convenient and
efficient way to store, retrieve and modify information. Application programs request DBMS
to retrieve, modify/insert/delete data for them and thus it acts as a layer of abstraction
between the application programs and the file system.

 DBMS acts as a layer of abstraction on top of the File system.

 For interacting with the DBMS we use a Query language called Structured Query Language
(SQL)

Components of a DBMS

The DBMS accepts the SQL commands generated from a variety of user interfaces, produces
query evaluation plans, executes these plans against the database, and returns the
answers. As shown, the major software modules or components of DBMS are as follows:

(i) Query processor: The query processor transforms user queries into a series of low
level instructions. It is used to interpret the online user's query and convert it into an
efficient series of operations in a form capable of being sent to the run time data manager
for execution. The query processor uses the data dictionary to find the structure of the
relevant portion of the database and uses this information in modifying the query and
preparing and optimal plan to access the database.

(ii) Run time database manager: Run time database manager is the central software
component of the DBMS, which interfaces with user-submitted application programs and
queries. It handles database access at run time. It converts operations in user's queries
coming. Directly via the query processor or indirectly via an application program from the
user's logical view to a physical file system. It accepts queries and examines the external
and conceptual schemas to determine what conceptual records are required to satisfy the
user’s request. It enforces constraints to maintain the consistency and integrity of the data,
as well as its security. It also performs backing and recovery operations. Run time database
manager is sometimes referred to as the database control system and has the following
components:

• Authorization control: The authorization control module checks the authorization of

users in terms of various privileges to users.

• Command processor: The command processor processes the queries passed by

authorization control module.
Integrity checker: It .checks the integrity constraints so that only valid data can be
entered into the database.

Query optimizer: The query optimizers determine an optimal strategy for the query
execution.

• Transaction manager: The transaction manager ensures that the transaction properties
should be maintained by the system.

• Scheduler: It provides an environment in which multiple users can work on same piece
of data at the same time in other words it supports concurrency.

(iii) Data Manager: The data manager is responsible for the actual handling of data in the
database. It provides recovery to the system which that system should be able to recover
the data after some failure. It includes Recovery manager and Buffer manager. The buffer
manager is responsible for the transfer of data between the main memory and secondary
storage (such as disk or tape). It is also referred as the cache manger.

Execution Process of a DBMS

(I) Users issue a query using particular database language, for example, SQL commands.

(ii) The passes query is presented to a query optimizer, which uses information about how
the data is stored to produce an efficient execution plan for the evaluating the query.

(iii) The DBMS accepts the users SQL commands and analyses them.
(iv) The DBMS produces query evaluation plans, that is, the external schema for the user,
the corresponding external/conceptual mapping, the conceptual schema, the
conceptual/internal mapping, and the storage structure definition. Thus, an evaluation\ plan
is a blueprint for evaluating a query.

(v) The DBMS executes these plans against the physical database and returns the answers
to the user.

Using components such as transaction manager, buffer manager, and recovery manager,
the DBMS supports concurrency and recovery.

Structured Query Language

SQL is a non procedural language that is used to create, manipulate and process the
databases(relations).

Characteristics of SQL

 It is very easy to learn and use.

 Large volume of databases can be handled quite easily.

 It is non procedural language. It means that we do not need to specify the

procedures to accomplish a task but just to give a command to perform the activity.

 SQL can be linked to most of other high level languages that makes it first choice for
the database programmers.

Data types of SQL

Just like any other programming language, the facility of defining data of various types is
available in SQL also. Following are the most common data types of SQL.

1. NUMBER

2. CHAR

3. VARCHAR / VARCHAR2

4. DATE

5. LONG

6. RAW/LONG RAW

1. NUMBER
Used to store a numeric value in a field/column. It may be decimal, integer or a real value.
General syntax is:

Number(n,d)

Where n specifies the number of digits and

d specifies the number of digits to the right of the decimal point.

e.g marks number(3) declares marks to be of type number with maximum value 999.

pct number(5,2) declares pct to be of type number of 5 digits with two digits to the
right of decimal point.

2. CHAR

Used to store character type data in a column. General syntax is

Char (size)

where size represents the maximum number of characters in a column. The CHAR type data
can hold at most 255 characters.

e.g name char(25) declares a data item name of type character of upto 25 size long.

3. VARCHAR/VARCHAR2

This data type is used to store variable length alphanumeric data. General syntax is,

varchar(size) / varchar2(size)

where size represents the maximum number of characters in a column. The maximum
allowed size in this data type is 2000 characters.

e.g address varchar(50);

address is of type varchar of upto 50 characters long.

4. DATE

Date data type is used to store dates in columns. SQL supports the various date formats
other that the standard DD-MON-YY.

e.g dob date; declares dob to be of type date.

5. LONG

This data type is used to store variable length strings of upto 2 GB size.
e.g description long;

6. RAW/LONG RAW

To store binary data (images/pictures/animation/clips etc.) RAW or LONG RAW data type is
used. A column LONG RAW type can hold upto 2 GB of binary data.

e.g image raw(2000);

Processing Capabilities of SQL

The following are the processing capabilities of SQL.

Language Command List

 CREATE
 DROP
DDL  ALTER
 RENAME
 TRUNCATE

 SELECT
 INSERT
DML
 UPDATE
 DELETE

 GRANT
DCL
 REVOKE

 COMMIT
TCL
 ROLLBACK

1.Data Definition Language (DDL)

DDL contains commands that are used to create the tables, databases, indexes,
views, sequences and synonyms etc.

e.g: Create table, create view, create index, alter table, delete table

CREATE TABLE Command:

Create table command is used to create a table in SQL. The general syntax of
creating a table is
create table <table name> (
<column 1> <data type> [not null] [unique] [<column constraint>],
.........
 <column n> <data type> [not null] [unique] [<column constraint>],
[<table constraint(s)>]
);

Constraints:
Constraints are the conditions that can be enforced on the attributes of a relation.
The constraints come in play when ever we try to insert, delete or update a record in
a relation.
1. NOT NULL
2. UNIQUE
3. PRIMARY KEY
4. FOREIGN KEY
5. CHECK
6. DEFAULT

Constraints:

1. NOT NULL: Ensures that we cannot leave a column as null. That is a value has to be
supplied for that column.

e.g name varchar(25) not null;

2. UNIQUE: Constraint means that the values under that column are always unique.

e.g Roll_no number(3) unique;

3. PRIMARY KEY: Constraint means that a column can not have duplicate values and
not even a null value.

e.g. Roll_no number(3) primary key;

The main difference between unique and primary key constraint is that a column
specified as unique may have null value but primary key constraint does not allow null
values in the column.

3. PRIMARY KEY: Constraint means that a column can not have duplicate values and
not even a null value.

e.g. Roll_no number(3) primary key;

The main difference between unique and primary key constraint is that a column
specified as unique may have null value but primary key constraint does not allow null
values in the column.

5. CHECK: Constraint limits the values that can be inserted into a column of a table.

e.g marks number(3) check(marks>=0);

The above statement declares marks to be of type number and while inserting or
updating the value in marks it is ensured that its value is always greater than or equal
to zero.

6. DEFAULT: Constraint is used to specify a default value to a column of a table

automatically. This default value will be used when user does not enter any value for
that column.

e.g balance number(5) default = 0;

CREATE TABLE student (

Roll_no number(3) primary key,

Name varchar(25) not null,

Class varchar(10),

Marks number(3) check(marks>0),

City varchar(25) );

CREATE VIEW Command

In SQL we can create a view of the already existing table that contains specific attributes
of the table.

e. g. the table student that we created contains following fields:

Student (Roll_no, Name, Marks, Class, City)

Suppose we need to create a view v_student that contains Roll_no,name and class of
student table, then Create View command can be used:

CREATE VIEW v_student AS SELECT Roll_no, Name, Class FROM student;

The above command create a virtual table (view) named v_student that has three
attributes as mentioned and all the rows under those attributes as in student table.

We can also create a view from an existing table based on some specific conditions, like

CREATE VIEW v_student AS SELECT Roll_no, Name, Class FROM student WHERE City
<>’Delhi’;

The main difference between a Table and view is that:

A Table is a repository of data. The table resides physically in the database.

A View is not a part of the database's physical representation. It is created on a table or
another view. It is precompiled, so that data retrieval behaves faster, and also provides
a secure accessibility mechanism.

ALTER TABLE Command

In SQL if we ever need to change the structure of the database then ALTER TABLE
command is used. By using this command we can add a column in the existing table,
delete a column from a table or modify columns in a table.

Adding a column

The syntax to add a column is:-

ALTER TABLE table_name

ADD column_name datatype;

e.g ALTER TABLE student ADD(Address varchar(30));

The above command add a column Address to the table student.

If we give command

SELECT * FROM student;

Removing a column

ALTER TABLE table_name

DROP COLUMN column_name;

e.g ALTER TABLE Student

DROP COLUMN Address;

The column Address will be removed from the table student

Modify a column

ALTER TABLE table_name

modify COLUMN column_name;
 e.g ALTER TABLE Student
MODIFY column_name datatype;

To change the data type of a column in a table

DROP TABLE Command

Sometimes you may need to drop a table which is not in use. DROP TABLE command is
used to Delete / drop a table permanently. It should be kept in mind that we cannot
drop a table if it contains records. That is first all the rows of the table have to be
deleted and only then the table can be dropped.

The general syntax of this command is:-

DROP TABLE <table_name>;

e.g DROP TABLE student;

This command will remove the table student

RENAME
RENAME command is used to rename SQL table.

Eg RENAME TABLE user TO student

TRUNCATE
TRUNCATE operation is used to delete all table records.

Logically it’s the same as DELETE command.

Differences between DELETE and TRUNCATE commands are:

 TRUNCATE is really faster

 TRUNCATE cannot be rolled back
 TRUNCATE command does not invoke ON DELETE triggers
Eg TRUNCATE student;

2. Data Manipulation Language (DML)

DML contains command that can be used to manipulate the data base objects and to
query the databases for information retrieval.
e.g Select, Insert, Delete, Update.
INSERT Statement

The simplest way to insert a tuple into a table is to use the insert statement
insert into <table> [(<column i, . . . , column j>)] values (<value i, . . . ,
value j>);

INSERT INTO student VALUES(101,'Rohan','XI',400,‘Chennai');

While inserting the record it should be checked that the values passed are of same
data types as the one which is specified for that particular column.
For inserting a row interactively (from keyboard) & operator can be used.
e.g INSERT INTO student VALUES(&Roll_no’,’&Name’,’&Class’,’&Marks’,’&City’);
In the above command the values for all the columns are read from keyboard and
inserted into the table student.

NOTE:- In SQL we can repeat or re-execute the last command typed at SQL
prompt by typing “/” key and pressing enter.

SELECT Command

This command can perform selection as well as projection.

Selection: This capability of SQL can return you the tuples form a relation with all
the attributes.
Projection: This is the capability of SQL to return only specific attributes in the
relation.
 SELECT * FROM student; command will display all the tuples in the
relation student
 SELECT * FROM student WHERE Roll_no <=102;
The above command display only those records whose Roll_no less than or equal to
102.
Select command can also display specific attributes from a relation.
 SELECT name, class FROM student;
The above command displays only name and class attributes from student table.
 SELECT count(*) AS “Total Number of Records” FROM student;
Display the total number of records with title as “Total Number of Records” i.e an
alias
We can also use arithmetic operators in select statement, like
 SELECT Roll_no, name, marks+20 FROM student;
 SELECT name, (marks/500)*100 FROM student WHERE Roll_no > 103;
Eliminating Duplicate/Redundant data

DISTINCT keyword is used to restrict the duplicate rows from the results of a
SELECT statement.
e.g. SELECT DISTINCT name FROM student;

UPDATE
UPDATE statement modifies records into the table.

UPDATE student SET name = 'Dima' WHERE lastname = 'Shvechikov';

DELETE
DELETE query removes entries from the table.

DELETE FROM student WHERE name = 'Dima';

4. Data Control Language:
This language is used for controlling the access to the data. The commonly used
commands DCL are,
GRANT, REVOKE

GRANT
GRANT command gives permissions to SQL user account.

For example, I want to grant all privileges to ‘explainjava’ database for user
‘dmytro@localhost’.

Let’s create a user first:

CREATE USER 'dmytro'@'localhost' IDENTIFIED BY '123';

Then I can grant all privileges using GRANT statement:
GRANT ALL PRIVILEGES ON explainjava.* TO 'dmytro'@'localhost';

REVOKE
REVOKE statement is used to remove privileges from user accounts

Eg.REVOKE ALL PRIVILEGES ON explainjava.* FROM 'dmytro'@'localhost';

5. Transaction Control Language (TCL)

TCL include commands to control the transactions in a data base system. The
commonly used commands in TCL are
COMMIT, ROLLBACK

COMMIT:
 COMMIT is one of the transactional SQL command which is used to save changes.
 For example, if you delete/update some records from a table, then this change will be
existing only on the current session. If you close your session, then it may not be saved
in the table/database. But, after delete/update transaction, if you save this operation
by COMMIT command, then it will be permanently saved in table/database.
 Syntax:
COMMIT;
ROLLBACK:
 ROLLBACK is one of the transactional SQL command which is used to rollback the
changes.
 For example, if you delete/update some records from a table, then this change will be
existing only on the current session. If you close your session, then it may not be saved
in the table/database. But, after delete/update transaction, if you save this operation
by ROLLBACK command, then it will be permanently saved in table/database.
 Syntax:
ROLLBACK;

Operators in SQL:

The following are the commonly used operators in SQL

 Arithmetic Operators +, -, *, /

 Relational Operators =, <, >, <=, >=, <>

 Logical Operators OR, AND, NOT

 Arithmetic operators are used to perform simple arithmetic operations.

 Relational Operators are used when two values are to be compared and

 Logical operators are used to connect search conditions in the WHERE Clause in
SQL.

ORDER BY Clause

ORDER BY clause is used to display the result of a query in a specific order(sorted order).

The sorting can be done in ascending or in descending order. It should be kept in mind that
the actual data in the database is not sorted but only the results of the query are displayed
in sorted order.

e.g. SELECT name, city FROM student ORDER BY name;

The above query returns name and city columns of table student sorted by name in
increasing/ascending order.

e.g. SELECT * FROM student ORDER BY city DESC;

It displays all the records of table student ordered by city in descending order.

Note:- If order is not specifies that by default the sorting will be performed in
ascending order.
GROUP BY Clause

The GROUP BY clause can be used in a SELECT statement to collect data across multiple
records and group the results by one or more columns.

The syntax for the GROUP BY clause is:

SELECT column1, column2, ... column_n, aggregate_function (expression)

FROM tables
WHERE conditions
GROUP BY column1, column2, ... column_n;

HAVING Clause

The HAVING clause is used in combination with the GROUP BY clause. It can be used in a
SELECT statement to filter the records that a GROUP BY returns.

The syntax for the HAVING clause is:

SELECT column1, column2, ... column_n, aggregate_function (expression)

FROM tables
WHERE predicates
GROUP BY column1, column2, ... column_n
HAVING condition1 ... condition_n;

e.g SELECT SUM(marks) as "Total marks"

FROM student
GROUP BY department
HAVING SUM(sales) > 1000;

Note: select statement can contain only those attribute which are already present in the
group by clause.

Functions available in SQL

SQL provide large collection of inbuilt functions also called library functions that can be used
directly in SQL statements.

TYPES OF FUNCTION

There are two types of function:

 Single row functions

 Multiple row functions

Single row functions

 1. Numeric functions

2. String functions

3. Date & Time functions

4. Aggregate or group functions

5. Date conversion function

Numeric Functions

Function Input Argument Value Returned

ABS ( m ) m = value Absolute value of m
Remainder of m divided by
MOD ( m, n ) m = value, n = divisor
n
POWER ( m, n
m = value, n = exponent m raised to the nth power
)
ROUND ( m [, m = value, n = number of decimal m rounded to the nth
n]) places, default 0 decimal place
TRUNC ( m [, m = value, n = number of decimal m truncated to the nth
n]) places, default 0 decimal place
SIN ( n ) n = angle expressed in radians sine (n)
COS ( n ) n = angle expressed in radians cosine (n)
TAN ( n ) n = angle expressed in radians tan (n)
arc sine of n in the range -
ASIN ( n ) n is in the range -1 to +1
π/2 to +π/2
arc cosine of n in the
ACOS ( n ) n is in the range -1 to +1
range 0 to π
arc tangent of n in the
ATAN ( n ) n is unbounded
range -π/2 to + π/2
SINH ( n ) n = value hyperbolic sine of n
COSH ( n ) n = value hyperbolic cosine of n
TANH ( n ) n = value hyperbolic tangent of n
SQRT ( n ) n = value positive square root of n
EXP ( n ) n = value e raised to the power n
LN ( n ) n>0 natural logarithm of n
LOG ( n2, n1 base n2 any positive value other than 0
logarithm of n1, base n2
) or 1, n1 any positive value
smallest integer greater
CEIL ( n ) n = value
than or equal to n
greatest integer smaller
FLOOR ( n ) n = value
than or equal to n
 -1 if n < 0, 0 if n = 0, and
SIGN ( n ) n = value
1 if n > 0

Here are some examples of the use of some of these numeric functions:

select round (83.28749, 2) from dual;

select sqrt (3.67) from dual;

select power (2.512, 5) from dual;

Function Input Argument Value Returned

First letter of each word is changed
INITCAP ( s ) s = character string to uppercase and all other letters
are in lower case.
All letters are changed to
LOWER ( s ) s = character string
lowercase.
All letters are changed to
UPPER ( s ) s = character string
uppercase.
CONCAT ( s1, s1 and s2 are character Concatenation of s1 and s2.
s2 ) strings Equivalent to s1 || s2
s1 and s2 are character Returns s1 right justified and
LPAD ( s1, n [,
strings and n is an integer padded left with n characters from
s2] )
value s2; s2 defaults to space.
s1 and s2 are character Returns s1 left justified and padded
RPAD ( s1, n [,
strings and n is an integer right with n characters from s2; s2
s2] )
value defaults to space.
Returns s with characters removed
LTRIM ( s [, set s is a character string
up to the first character not in set;
]) and set is a set of characters
defaults to space
Returns s with final characters
RTRIM ( s [, set s is a character string
removed after the last character
]) and set is a set of characters
not in set; defaults to space
s = character string, Returns s with every occurrence of
REPLACE ( s,
search_s = target string, search_s in s replaced by
search_s [,
replace_s = replacement replace_s; default removes
replace_s ] )
string search_s
Returns a substring from s,
s = character string, m =
SUBSTR ( s, m beginning in position m and n
beginning position, n =
[, n ] ) characters long; default returns to
number of characters
end of s.
Returns the number of characters
LENGTH ( s ) s = character string
in s.
s1 and s2 are character Returns the position of the nth
INSTR ( s1, s2
strings, m = beginning occurrence of s2 in s1, beginning at
[, m [, n ] ] )
position, n = occurrence of position m, both m and n default to
 s2 in s1 1.

Here are some examples of the use of String functions:

select concat ('Alan', 'Turing') as "NAME" from dual;

select 'Alan' || 'Turing' as "NAME" from dual;

select initcap ("now is the time for all good men to come to the aid of the party") as
"SLOGAN" from dual;

select substr ('Alan Turing', 1, 4) as "FIRST" from dual;

String / Number Conversion Functions

Function Input Argument Value Returned

if (n2 = NaN) returns n1 else returns
NANVL ( n2, n1 ) n1, n2 = value
n2
TO_CHAR ( m [, m = numeric value, Number m converted to character
fmt ] ) fmt = format string as specified by the format
TO_NUMBER ( s [, s = character string, Character string s converted to a
fmt ] ) fmt = format number as specified by the format

Formats for TO_CHAR Function

Symbol Explanation
Each 9 represents one digit in the
9
result
Represents a leading zero to be
0
displayed
Floating dollar sign printed to the
$
left of number
L Any local floating currency symbol
. Prints the decimal point
Prints the comma to represent
,
thousands

Group Functions

Function Input Argument Value Returned

AVG ( [ DISTINCT |
col = column name The average value of that column
ALL ] col )
COUNT ( * ) none Number of rows returned including
 duplicates and NULLs
COUNT ( [ DISTINCT | Number of rows where the value of the
col = column name
ALL ] col ) column is not NULL
MAX ( [ DISTINCT |
col = column name Maximum value in the column
ALL ] col )
MIN ( [ DISTINCT |
col = column name Minimum value in the column
ALL ] col )
SUM ( [ DISTINCT |
col = column name Sum of the values in the column
ALL ] col )
e1 and e2 are Correlation coefficient between the two
CORR ( e1, e2 )
column names columns after eliminating nulls
Middle value in the sorted column,
MEDIAN ( col ) col = column name
interpolating if necessary
STDDEV ( [ DISTINCT Standard deviation of the column
col = column name
| ALL ] col ) ignoring NULL values
VARIANCE ( [ Variance of the column ignoring NULL
col = column name
DISTINCT | ALL ] col ) values

Date and Time Functions

Function Input Argument Value Returned

d = date, n = number of
ADD_MONTHS ( d, n ) Date d plus n months
months
Date of the last day of the
LAST_DAY ( d ) d = date
month containing d
MONTHS_BETWEEN ( Number of months by which e
d and e are dates
d, e ) precedes d
d = date, a = time zone The date and time in time
NEW_TIME ( d, a, b ) (char), b = time zone zone b when date d is for time
(char) zone a
d = date, day = day of the Date of the first day of the
NEXT_DAY ( d, day )
week week after d
SYSDATE none Current date and time
GREATEST ( d1, d2,
d1 ... dn = list of dates Latest of the given dates
..., dn )
LEAST ( d1, d2, ..., dn
d1 ... dn = list of dates Earliest of the given dates
)

Date Conversion Functions

Function Input Argument Value Returned

TO_CHAR ( d [, d = date value, fmt = format The date d converted to a string
fmt ] ) for string in the given format
TO_DATE ( s [, s = character string, fmt = String s converted to a date value
fmt ] ) format for date
ROUND ( d [, d = date value, fmt = format Date d rounded as specified by
fmt ] ) for string the format
TRUNC ( d [, fmt d = date value, fmt = format Date d truncated as specified by
]) for string the format

Sub Queries:

A Subquery or Inner query or a Nested query is a query within another SQL query and

embedded within the WHERE clause.

A subquery is used to return data that will be used in the main query as a condition to

further restrict the data to be retrieved.

Subqueries can be used with the SELECT, INSERT, UPDATE, and DELETE statements along

with the operators like =, <, >, >=, <=, IN, BETWEEN, etc.

There are a few rules that subqueries must follow −

 Subqueries must be enclosed within parentheses.

 A subquery can have only one column in the SELECT clause

 An ORDER BY command cannot be used in a subquery

 A subquery cannot be immediately enclosed in a set function.

Eg:
SELECT
employee_id, first_name, last_name, salary
FROM
employees
WHERE
salary > (SELECT
AVG(salary)
FROM
employees);