Subject Name: Database Management System

Subject Code: CS-5003

Semester: 5th
Downloaded from be.rgpvnotes.in

UNIT-V
DBMS Vs RDBMS

DBMS RDBMS
1) DBMS applications store data as file. RDBMS applications store data in a tabular form.
In DBMS, data is generally stored in either a In RDBMS, the tables have an identifier called primary key and
2)
hierarchical form or a navigational form. the data values are stored in the form of tables.
3) Normalization is not present in DBMS. Normalization is present in RDBMS.
DBMS does not apply any security with RDBMS defines the integrity constraint for the purpose of ACID
4)
regards to data manipulation. (Atomicity, Consistency, Isolation and Durability) property.
in RDBMS, data values are stored in the form of tables, so a
DBMS uses file system to store data, so there
5) relationship between these data values will be stored in the form
will be no relation between the tables.
of a table as well.
DBMS has to provide some uniform methods RDBMS system supports a tabular structure of the data and a
6)
to access the stored information. relationship between them to access the stored information.
DBMS does not support distributed
7) RDBMS supports distributed database.
database.
DBMS is meant to be for small organization
RDBMS is designed to handle large amount of data. it supports
8) and deal with small data. it supports single
multiple users.
user.
9) Examples of DBMS are file systems, xml etc. Example of RDBMS are MySQL, postage, SQL server, oracle etc.

Study of Relational Database Management Systems through SQL/MySQL: Architecture segments

MySQL
M “QL is e diffe e t f o othe data ase se e s, a d its ar hite tural ha a te isti s ake it useful fo a
ide a ge of pu poses as ell as aki g it a poo hoi e fo othe s. M “QL is ot pe fe t, ut it is fle i le
e ough to o k ell i e de a di g e i o e ts, su h as e appli atio s. At the sa e ti e, M “QL a
po e e edded appli atio s, data a ehouses, o te t i de i g a d deli e soft a e, highl a aila le
edu da t s ste s, o li e t a sa tio p o essi g OLTP , a d u h o e.
To get the ost f o M “QL, ou eed to u de sta d its desig so that ou a o k ith it, ot agai st it.
M “QL is fle i le i a a s. Fo e a ple, ou a o figu e it to u ell o a ide a ge of ha d a e, a d
it suppo ts a a iet of data t pes. Ho e e , M “QL’s ost u usual a d i po ta t featu e is its sto age-e gi e
a hite tu e, hose desig sepa ates ue p o essi g a d othe se e tasks f o data sto age a d et ie al.
This sepa atio of o e s lets ou hoose ho ou data is sto ed a d hat pe fo a e, featu es, a d othe
ha a te isti s ou a t.
This p o ides a high-le el o e ie of the M “QL se e a hite tu e, the ajo diffe e es et ee the sto age
e gi es, a d h those diffe e es a e i po ta t. We’ll fi ish ith so e histo i al o te t a d e h a ks.
We’ e t ied to e plai M “QL si plif i g the details a d sho i g e a ples. This dis ussio ill e useful fo
those e to data ase se e s as ell as eade s ho a e e pe ts ith othe data ase se e s.
M “QL’s Logi al A hite tu e
A figu e sho of ho M “QL’s o po e ts o k togethe ill help ou u de sta d the se e

Page no: 1 Follow us on facebook to get real-time updates from RGPV

Downloaded from be.rgpvnotes.in

Figu e 5- . A logi al ie of the M “QL se e a hite tu e

The top ost layer o tai s the se i es that a e ’t u i ue to M “QL. The ’ e se i es ost et o k- ased
lie t/se e tools o se e s eed: o e tio ha dli g, authe ti atio , se u it , a d so fo th.
The se o d layer is he e thi gs get i te esti g. Mu h of M “QL’s ai s a e he e, i ludi g the ode fo ue
pa si g, a al sis, opti izatio , a hi g, a d all the uilt-i fu tio s e.g., dates, ti es, ath, a d e ptio .
A fu tio alit p o ided a oss sto age e gi es li es at this le el: sto ed p o edu es, t igge s, a d ie s, fo
e a ple.
The third layer o tai s the sto age e gi es. The a e espo si le fo sto i g a d et ie i g all data sto ed i
M “QL. Like the a ious files ste s a aila le fo GNU/Li u , ea h sto age e gi e has its o e efits a d
d a a ks. The se e o u i ates ith the th ough the storage e gi e API. This i te fa e hides diffe e es
et ee sto age e gi es a d akes the la gel t a spa e t at the ue la e . The API o tai s a ouple of
doze lo -le el fu tio s that pe fo ope atio s su h as egi a t a sa tio o fet h the o that has this
p i a ke . The sto age e gi es do ’t pa se “QL o o u i ate ith ea h othe ; the si pl espo d to
e uests f o the se e .

Physical files and memory structures

Introduction:
The collection of data that makes up a computerized database must be stored physically on some computer
storage medium. The DBMS software that can then retrieve, updates, and processes this data as needed.
Computer storage media from a storage hierarchy that includes two main categories.

Primary Storage
The category includes storage media that can be operated on directly by the computer Central Processing
Unit(CPU), such as the computer main memory and smaller but faster cache memories. Primary storage usually
provides fast access to data but is of limited storage capacity.

Secondary Storage
This category includes magnetic disks, optical disks, and tapes. These devices usually have a larger capacity,
cost less, and provide slower access to data than do primary storage devices. Data in secondary storage cannot
be processed directly by the CPU: it must first be copied into

Page no: 2 Follow us on facebook to get real-time updates from RGPV

Downloaded from be.rgpvnotes.in

Primary storage.
The storage media are classified by the speed with which data can be accessed, by the cost per unit of data to
u the ediu , a d the ediu ’s elia ilit . Let’s look i to the edia that a e t pi all a ailable.

Cache: The cache is the fastest and most costly form of storage. Cache memory is small. The computer hardware
manages its use

Main Memory: The general-purpose machine instructions operate on main memory. If a power failure or system
crash occurs, the contents of main memory are usually lost. (i.e.) volatile memory type.

Flash Memory: Also, known as Electrically Erasable programmable read only memory (EEPROM). Data Survive
power failure in flash memory (Non-volatile Type). Reading data from flash memory takes less than 100
nanoseconds, which is as fast as reading data from main memory. However, writing data to flash memory is
more complicated. Data can be written once, but cannot be overwritten directly. To overwrite memory that has
been written already, we have to erase an entire bank of memory once.
It is used for storing small volumes of data (5-10 MB) in hand-held computers, digital cameras.

Ba kgrou d pro ess

“HOW PROCE““LI“T sho s ou hi h th eads a e u i g. You a also get this i fo atio f o

the INFORMATION_“CHEMAPROCE““LI“T ta le o the s lad i p o ess list o a d. If ou ha e
the PROCE““ p i ilege, ou a see all th eads. Othe ise, ou a see o l ou o th eads that is, th eads
asso iated ith the M “QL a ou t that ou a e usi g . If ou do ot use the FULL ke o d, o l the fi st
ha a te s of ea h state e t a e sho i the I fo field.
P o ess i fo atio is also a aila le f o the pe fo a e_s he a th eads ta le. Ho e e , a ess
to th eads does ot e ui e a ute a d has i i al i pa t o se e
pe fo a e. INFORMATION_“CHEMA.PROCE““LI“T a d “HOW PROCE““LI“T ha e egati e pe fo a e
o se ue es e ause the e ui e a ute . th eads also sho i fo atio a out a kg ou d th eads, hi h
INFORMATION_“CHEMA.PROCE““LI“T a d “HOW PROCE““LI“T do ot. This ea s that th eads a e used to
o ito a ti it the othe th ead i fo atio sou es a ot.

Table Spaces
A data file that can hold data for one or more InnoDB tables and associated indexes.
There are many types of tablespaces based on the configuration w.r.t the information clubbing per table. These
are:
a. System tablespace b. File per tablespace c. General tablespace
System tablespace contains,
1. InnoDB data dictionary.
2. DoubleWrite Buffer.
3. Change buffer
4. Undo Logs.
Apart from this it also contains,
1. Tables &
2. Index data
Associated file is. idbdata1
The innodb_file_per_table option, which is enabled by default in MySQL 5.6 and higher, allows tables to be
created in file-per-table tablespaces, with a separate data file for each table. Enabling the innodb_file_per_table
option makes available other MySQL features such as table compression and transportable tablespaces.

Page no: 3 Follow us on facebook to get real-time updates from RGPV

Downloaded from be.rgpvnotes.in

Associated file is .idbd

InnoDB introduced general tablespaces in MySQL 5.7.6. General tablespaces are shared tablespaces created
using CREATE TABLESPACE syntax. They can be created outside of the MySQL data directory, are capable of
holding multiple tables, and support tables of all row formats.

Introduction to Data Blocks, Extents, and Segments

My sql allocates logical database space for all data in a database. The units of database space allocation are data
blocks, extents, and segments. Figure shows the relationships among these data structures:

Figure 5.1 The Relationships Among Segments, Extents, and Data Blocks"

At the finest level of granularity, Mysql stores data in data blocks (also called logical blocks, My sql blocks,
or pages). One data block corresponds to a specific number of bytes of physical database space on disk.

The next level of logical database space is an extent. An extent is a specific number of contiguous data blocks
allocated for storing a specific type of information.

The level of logical database storage greater than an extent is called a segment. A segment is a set of extents,
each of which has been allocated for a specific data structure and all of which are stored in the same tablespace.
For example, each table's data is stored in its own data segment, while each index's data is stored in its
own index segment. If the table or index is partitioned, each partition is stored in its own segment.

Page no: 4 Follow us on facebook to get real-time updates from RGPV

Downloaded from be.rgpvnotes.in

My sql allocates space for segments in units of one extent. When the existing extents of a segment are full, My
sql allocates another extent for that segment. Because extents are allocated as needed, the extents of a segment
may or may not be contiguous on disk.

A segment and all its extents are stored in one tablespace. Within a tablespace, a segment can include extents
from more than one file; that is, the segment can span datafiles. However, each extent can contain data from
only one datafile.

Although you can allocate additional extents, the blocks themselves are allocated separately. If you allocate an
extent to a specific instance, the blocks are immediately allocated to the free list. However, if the extent is not
allocated to a specific instance, then the blocks themselves are allocated only when the high-water mark moves.
The high-water mark is the boundary between used and unused space in a segment.

Profiles

MySQL query profiling is a useful technique when trying to analyze the overall performance of a database driven
application. When developing a mid to large size application, there tends to be hundreds of queries distributed
throughout a large code base and potentially numerous queries ran against the database per second. Without
some sort of query profiling techniques, it becomes very difficult to determine locations and causes of
bottlenecks and applications slow down. This article will demonstrate some useful query profiling techniques
using tools that are built into MySQL server.

Dedicated Server
A dedicated server is a single computer in a network reserved for serving the needs of the network. For example,
some networks require that one computer be set aside to manage communications between all the other
computers. A dedicated server could also be a computer that manages printer resources. Note, however, that
not all servers are dedicated. In some networks, it is possible for a computer to act as a server and perform
other functions as well.
In the Web hosting business, a dedicated server is typically a rented service. The user rents the server, software
and an Internet connection from the Web host.

Multithreaded server
Multithreaded server, also known as a shared server, allows many user processes to share a few shared server
processes to connect to the database. Without MTS, each user process spawns its own dedicated server process,
consuming OS memory. A dedicated server process remains associated to the user process for the remainder of
the connection.
Multithreading will allow a program or an OS to handle multiple users and requests simultaneously by utilizing
multiple threads, as opposed to running the program multiple times. It uses the program resources more
effectively by handling multiple queries or commands and tracking them with multiple threads until completed.
Advantages of Multithreading
 Increase performance
 Concurrency
 Reduce number of servers needed reducing cost and maintenance
 Compatibility with applications that create client threads such as API
 Parallel tasks
 More efficient use of system resources

 Synchronizing resources that are being share

Disadvantages of Multithreading

Page no: 5 Follow us on facebook to get real-time updates from RGPV

Downloaded from be.rgpvnotes.in

 Potential deadlocks
Increased complexity in programming as well as troubleshooting problems

Distri uted Data ase Syste -

A dist i uted data ase DDB is a olle tio of ultiple, logically i terrelated data ases dist i uted o e a
co puter etwork.
A dist i uted data ase a age e t s ste D-DBM“ is the soft a e that a ages the DDB a d p o ides a
a ess e ha is that akes this dist i utio t a spa e t to the use s.
Dist i uted data ase s ste DDB“ = DDB + D– DBM“
What is ot a DDB“?
A ti esha i g o pute s ste .
A loosel o tightl oupled ultip o esso s ste .
A data ase s ste hi h esides at o e of the odes of a et o k of o pute s - this is a e t alized data ase
o a et o k od.

Data ase li ks, a d s apshot

Database Links?
A database link is a pointer that defines a one-way communication path from an Oracle Database server to
another database server. The link pointer is actually defined as an entry in a data dictionary table. To access the
link, you must be connected to the local database that contains the data dictionary entry.
A database link connection is one-way in the sense that a client connected to local database A can use a link
stored in database A to access information in remote database B, but users connected to database B cannot use
the same link to access data in database A. If local users on database B want to access data on database A, then
they must define a link that is stored in the data dictionary of database B.
A database link connection allows local users to access data on a remote database. For this connection to occur,
each database in the distributed system must have a unique global database name in the network domain. The
global database name uniquely identifies a database server in a distributed system.
Figure shows an example of user scott accessing the emp table on the remote database with the global
name hq.acme.com:

Page no: 6 Follow us on facebook to get real-time updates from RGPV

Downloaded from be.rgpvnotes.in

S apshot
A snapshot is a replica of a target master table from a single point-in-time. Whereas in multimaster replication
tables are continuously being updated by other master sites, snapshots are updated by one or more master
tables via individual batch updates, known as a refresh, from a single master site.

Snapshots also have the option of containing a WHERE clause so that snapshot sites can contain custom data
sets, which can be very helpful for regional offices or sales forces that don't require the complete corporate
data set.
Why use Snapshots?
Oracle offers a variety of snapshots to meet the needs of many different replication (and non-replication)
situations; each of these snapshots will be discussed in detail in following sections. You might use a snapshot to
achieve one or more of the following:
 Ease Network Loads
 Mass Deployment

Page no: 7 Follow us on facebook to get real-time updates from RGPV

Downloaded from be.rgpvnotes.in

 Data Subsetting
 Disconnected Computing
Data dictionary
A metadata (also called the data dictionary) is the data about the data. It is the self describing nature of the
database that provides program-data independence. It is also called as the System Catalog. It holds the


following information about each data element in the databases, it normally includes:


Name


Type


Range of values


Source


Access authorization
Indicates which application programs use the data so that, when a change in a data structure is
contemplated, a list of the affected programs can be generated.

Active and Passive Data Dictionaries

An active data dictionary (also called integrated data dictionary) is managed automatically by the database
management software.
The passive data di tio ary also alled o -i teg ated data di tio a is the o e used o l fo do u e tatio
pu poses. Data a out fields, files, people a d so o , i the data p o essi g e i o e t a e.
Dynamic Performance Views
Throughout its operation, Oracle Database maintains a set of virtual tables that record current database
activity. These views are dynamic because they are continuously updated while a database is open and in use.
The views are sometimes called V$ views because their names begin with V$.
These views contain information such as the following:
 System and session parameters
 Memory usage and allocation
 File states (including RMAN backup files)
 Progress of jobs and tasks
 SQL execution
 Statistics and metrics
The dynamic performance views have the following primary uses:
 Oracle Enterprise Manager Uses the views to obtain information about the database (see "Oracle
Enterprise Manager").
 Administrators can use the views for performance monitoring and debugging.
Privilege a d Se urity
A privilege is a right to execute a particular type of SQL statement or to access another user's object. Some
examples of privileges include the right to:
 Connect to the database (create a session)
 Create a table
 Select rows from another user's table
 Execute another user's stored procedure
There are two distinct categories of privileges:
 System privileges
 Schema object privileges
Database security concerns the use of a broad range of information security controls to protect databases
(potentially including the data, the database applications or stored functions, the database systems, the
database servers and the associated network links) against compromises of their confidentiality, integrity and
availability. It involves various types or categories of controls, such as technical, procedural/administrative and

Page no: 8 Follow us on facebook to get real-time updates from RGPV

Downloaded from be.rgpvnotes.in

physical. Database security is a specialist topic within the broader realms of computer security, information
security and risk management.
Privileges
Authorization includes primarily two processes:
 Permitting only certain users to access, process, or alter data.
 Applying varying limitations on user access or actions. The limitations placed on (or removed from)
users can apply to objects such as schemas, tables, or rows or to resources such as time (CPU, connect, or idle
times).
A user privilege is the right to run a particular type of SQL statement, or the right to access an object that
belongs to another user, run a PL/SQL package, and so on. The types of privileges are defined by Oracle
Database.
Managing User Role
This section contains:
 About User Roles
 Predefined Roles in an Oracle Database Installation
 Creating a Role
 Specifying the Type of Role Authorization
 Dropping Roles
 Restricting SQL*Plus Users from Using Database Roles
Uses of Roles
In general, you create a role to serve one of two purposes:
 To manage the privileges for a database application (see "Common Uses of Application Roles")
 To manage the privileges for a user group (see "Common Uses of User Roles")

Introduction to SQL
The history of SQL begins in an IBM laboratory in San Jose, California, where SQL was developed in the late
1970s. The initials stand for Structured Query Language, and the language itself is often referred to as "sequel."
It was originally developed for IBM's DB2 product (a relational database management system, or RDBMS, that
can still be bought today for various platforms and environments). In fact, SQL makes an RDBMS possible. SQL
is a nonprocedural language, in contrast to the procedural or third-generation languages (3GLs) such as COBOL
and C that had been created up to that time.

Dr. Codd's 12 Rules for a Relational Database Model

The most popular data storage model is the relational database, which grew from the seminal paper "A
Relational Model of Data for Large Shared Data Banks," written by Dr. E. F. Codd in 1970. SQL evolved to service
the concepts of the relational database model. Dr. Codd defined 13 rules, oddly enough referred to as Codd's
12 Rules, for the relational model:
A relational DBMS must be able to manage databases entirely through its relational capabilities.

1. Information rule-- All information in a relational database (including table and column names) is represented
explicitly as values in tables.
2. Guaranteed access--Every value in a relational database is guaranteed to be accessible by using a combination
of the table name, primary key value, and column name.
3. Systematic null value support--The DBMS provides systematic support for the treatment of null values
(unknown or inapplicable data), distinct from default values, and independent of any domain.
4. Active, online relational catalog--The description of the database and its contents is represented at the logical
level as tables and can therefore be queried using the database language.
5. Comprehensive data sublanguage--At least one supported language must have a well-defined syntax and be
comprehensive. It must support data definition, manipulation, integrity rules, authorization, and transactions.

Page no: 9 Follow us on facebook to get real-time updates from RGPV

Downloaded from be.rgpvnotes.in

6. View updating rule--All views that are theoretically updatable can be updated through the system.
7. Set-level insertion, update, and deletion--The DBMS supports not only set-level retrievals but also set-level
inserts, updates, and deletes.
8. Physical data independence--Application programs and ad hoc programs are logically unaffected when
physical access methods or storage structures are altered.
9. Logical data independence--Application programs and ad hoc programs are logically unaffected, to the extent
possible, when changes are made to the table structures.
10. Integrity independence--The database language must be capable of defining integrity rules. They must be
stored in the online catalog, and they cannot be bypassed.
11. Distribution independence--Application programs and ad hoc requests are logically unaffected when data is
first distributed or when it is redistributed.
12. No subversion--It must not be possible to bypass the integrity rules defined through the database language
by using lower-level

SQL in Application Programming

SQL was originally made an ANSI standard in 1986. The ANSI 1989 standard (often called SQL-89) defines three
types of interfacing to SQL within an application program:

Module Language-Uses procedures within programs. These procedures can be called by the application program
and can return values to the program via parameter passing.

Embedded SQL-Uses SQL statements embedded with actual program code. This method often requires the use
of a precompile to process the SQL statements. The standard defines statements for Pascal, FORTRAN, COBOL,
and PL/1.

Direct Invocation-Left up to the implementer. Before the concept of dynamic SQL evolved, embedded SQL was
the most popular way to use SQL within a program. Embedded SQL, which is still used, uses static SQL--meaning
that the SQL statement is compiled into the application and cannot be changed at runtime. The principle is much
the same as a compiler versus an interpreter. The performance for this type of SQL is good; however, it is not
flexible--and cannot always meet the needs of today's changing business environments. Dynamic SQL is
discussed shortly.

The ANSI 1992 standard (SQL-92) extended the language and became an international standard. It defines three
levels of SQL compliance: entry, intermediate, and full. The new features introduced include the following:

Connections to databases
Scrollable cursors
Dynamic SQL
Outer joins
SQL queries
Structure Query Language(SQL) is a programming language used for storing and managing data in RDBMS. SQL
was the first commercial language introduced for E.F Codd's Relational model. Today almost all RDBMS (MySql,
Oracle, Infomix, Sybase, MS Access) uses SQL as the standard database language. SQL is used to perform all type
of data operations in RDBMS.
SQL Joins
SQL Joins are used to relate information in different tables. A Join condition is a part of the sql query that
retrieves rows from two or more tables. A SQL Join condition is used in the SQL WHERE Clause of select, update,
delete statements.

Page no: 10 Follow us on facebook to get real-time updates from RGPV

Downloaded from be.rgpvnotes.in

Joins in SQL
The SQL Syntax for joining two tables is:

Syntax:
SELECT column_list
FROM table1, table2....
WHERE table1.column_name =
table2.column_name;
or
SELECT *
FROM table1
JOIN table2
[ON (join_condition)]

If a sql join condition is omitted or if it is invalid the join operation will result in a Cartesian product. The Cartesian
product returns a number of rows equal to the product of all rows in all the tables being joined. For example, if
the first table has 20 rows and the second table has 10 rows, the result will be 20 * 10, or 200 rows. This query
takes a long time to execute.
SQL Joins Example
Lets use the below two tables to explain the sql join conditions.

Database table "product";

product_id product_name supplier_name unit_price

100 Camera Nikon 300

101 Television Onida 100
102 Refrigerator Vediocon 150
103 Ipod Apple 75
104 Mobile Nokia 50
Database table "order_items";

order_id product_id total_units customer

5100 104 30 Infosys
5101 102 5 Satyam
5102 103 25 Wipro
5103 101 10 TCS
SQL Joins can be classified into Equi join and Non Equi join.
1) SQL Equi joins
It is a simple sql join condition which uses the equal sign as the comparison operator. Two types of equi joins
are SQL Outer join and SQL Inner join.
For example: You can get the information about a customer who purchased a product and the quantity of
product.
2) SQL Non equi joins
It is a sql join condition which makes use of some comparison operator other than the equal sign like >, <, >=,
<=

Page no: 11 Follow us on facebook to get real-time updates from RGPV

Downloaded from be.rgpvnotes.in

1) SQL Equi Joins:

An equi-join is further classified into two categories:

a) SQL Inner Join

b) SQL Outer Join
a) SQL Inner Join:
All the rows returned by the sql query satisfy the sql join condition specified.
SQL Inner Join Example:
If you want to display the product information for each order the query will be as given below. Since you are
retrieving the data from two tables, you need to identify the common column between these two tables, which
is the product_id.
The query for this type of sql joins would be like,

SELECT order_id, product_name, unit_price, supplier_name, total_units

FROM product, order_items
WHERE order_items.product_id = product.product_id;

The columns must be referenced by the table name in the join condition, because product_id is a column in
both the tables and needs a way to be identified. This avoids ambiguity in using the columns in the SQL SELECT
statement.
The number of join conditions is (n-1), if there are more than two tables joined in a query where 'n' is the number
of tables involved. The rule must be true to avoid Cartesian product.
We can also use aliases to reference the column name, then the above query would be like,

SELECT o.order_id, p.product_name, p.unit_price, p.supplier_name, o.total_units

FROM product p, order_items o
WHERE o.product_id = p.product_id;

SQL Self Join:

A Self Join is a type of sql join which is used to join a table to itself, particularly when the table has a FOREIGN
KEY that references its own PRIMARY KEY. It is necessary to ensure that the join statement defines an alias for
both copies of the table to avoid column ambiguity.
The below query is an example of a self-join,

SELECT a.sales_person_id, a.name, a.manager_id, b.sales_person_id, b.name

FROM sales_person a, sales_person b
WHERE a.manager_id = b.sales_person_id;

2) SQL Non Equi Join:

A Non Equi Join is a SQL Join whose condition is established using all comparison operators except the equal (=)
operator. Like >=, <=, <, >

SQL Non Equi Join Example:

If you want to find the names of students who are not studying either Economics, the sql query would be like,
(lets use student_details table defined earlier).

SELECT first_name, last_name, subject

FROM student_details
WHERE subject != 'Economics'

Page no: 12 Follow us on facebook to get real-time updates from RGPV

Downloaded from be.rgpvnotes.in

first_name last_name subject

Anajali Bhagwat Maths

Shekar Gowda Maths
Rahul Sharma Science
Stephen Fleming Science
Outer Join
This type of join is needed when we need to select all the rows from the table on the left (or right or both)
regardless of whether the other table has common values or not and it usually enter null values for the data
which is missing.
The Outer join can be of three types
1. Left Outer Join
2. Right Outer Join
3. Full Outer Join
Left Outer Join
If we want to get employee id, employee first name, employes last name and their department name for all
the employees regardless of whether they belong to any department or not,then we can use the left outer
join. In this case we keep the Employee table on the left side of the join clause. It will insert NULL values for
the data which is missing in the right table.

Query for Left Outer Join

SELECT Emp.Empid, Emp.EmpFirstName, Emp.EmpLastName, Dept.DepartmentName,

FROM Employee Emp LEFT OUTER JOIN Department dept
ON Emp.Departmentid=Dept.Departmenttid

Result

Empid EmpFirstName EmpLastName DepartmentName

1 samir singh admin
2 amit kumar accounts
3 neha sharma admin
4 vivek kumar NULL

Right Outer Join

If we want to get all the departments name and employee id, employee first name, and employees last name
of all the employees belonging to the department regardless of whether a department have employees or not,
then we can use the right outer join. In this case we keep the Department table on the right side of the join
clause. It will insert NULL values for the data which is missing in the left table (Employee).

SELECT Dept.DepartmentName, Emp.Empid, Emp.EmpFirstName, Emp.EmpLastName FROM Employee Emp

RIGHT OUTER JOIN Department dept
ON Emp.Departmentid=Dept.Departmentid

Page no: 13 Follow us on facebook to get real-time updates from RGPV

Downloaded from be.rgpvnotes.in

DepartmentName Empid EmpFirstName EmpLastName

accounts 2 amit kumar
admin 1 samir singh
admin 3 neha sharma
HR NULL NULL NULL
Technology NULL NULL NULL

Full Outer Join

If we want to get all the departments name and the employee id, employee first name, employes last name of
all the employees regardless of whether a department have employees or not, or whether a employee belong
to a department or not, then we can use the full outer join. It will insert null values for the data which is
missing in both the tables.
Query for Full Outer Join

SELECT Emp.Empid, Emp.EmpFirstName, Emp.EmpLastName, Emp.EmpLastName,Dept.DepartmentName,

FROM Employee Emp FULL OUTER JOIN Department dept ON Emp.Departmentid=Dept.Departmenttid

Empid EmpFirstName EmpLastName DepartmentName

1 samir singh admin
2 amit kumar accounts
3 neha sharma admin
4 vivek kumar NULL
NULL NULL NULL HR
NULL NULL NULL Technology

Special Select operator

ANY
The ANY operator returns true if any of the subquery values meet the condition.
SELECT column_name(s)
FROM table_name
WHERE column_name operator ANY
(SELECT column_name FROM table_name WHERE condition);

ALL
The ALL operator returns true if all of the subquery values meet the condition.
SELECT column_name(s)
FROM table_name
WHERE column_name operator ALL
(SELECT column_name FROM table_name WHERE condition);

Page no: 14 Follow us on facebook to get real-time updates from RGPV

Downloaded from be.rgpvnotes.in

LIKE Operator
The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.
There are two wildcards used in conjunction with the LIKE operator:
 % The percent sign represents zero, one, or multiple characters
 _ The underscore represents a single character
SELECT column1, column2, ...
FROM table_name
WHERE columnN LIKE pattern;

The SQL IN Operator

The IN operator allows you to specify multiple values in a WHERE clause.
The IN operator is a shorthand for multiple OR conditions.
SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1, value2, ...);
The SQL EXISTS Operator
The EXISTS operator is used to test for the existence of any record in a subquery.
The EXISTS operator returns true if the subquery returns one or more records.
SELECT column_name(s)
FROM table_name
WHERE EXISTS
(SELECT column_name FROM table_name WHERE condition);

Hierarchical Queries
A hierarchy is built upon a parent-child relationship within the same table or view.

START WITH specifies the root row(s) of the hierarchy.

CONNECT BY specifies the relationship between parent rows and child rows of the hierarchy.

The NOCYCLE parameter instructs Oracle Database to return rows from a query even if a CONNECT BY LOOP
exists in the data. Use this parameter along with the CONNECT_BY_ISCYCLE pseudocolumn to see which rows
contain the loop. Please refer to CONNECT_BY_ISCYCLE Pseudocolumn for more information.

In a hierarchical query, one expression in condition must be qualified with the PRIOR operator to refer to the
parent row.

hierarchical queries as follows:

A join, if present, is evaluated first, whether the join is specified in the FROM clause or with WHERE clause
predicates.
The CONNECT BY condition is evaluated.
Any remaining WHERE clause predicates are evaluated.

Page no: 15 Follow us on facebook to get real-time updates from RGPV

Downloaded from be.rgpvnotes.in

SELECT employee_id, last_name, manager_id

FROM employees
CONNECT BY PRIOR employee_id = manager_id;

Inline Query
When you use SQL Subquery in From clause of the select statement it is called inline view.

A common use for inline views in Oracle SQL is to simplify complex queries by removing join operations and
condensing several separate queries into a single query. A subquery which is enclosed in parenthesis in the
FROM clause may be given an alias name. The columns selected in the subquery can be referenced in the
parent query, just as you would select from any normal table or view.

Example
Display the top five earner names and salaries from the EMPLOYEES table:

SELECT ROWNUM as RANK, last_name, salary

FROM (SELECT last_name, salary
FROM employees
ORDER BY salary DESC)
WHERE ROWNUM <= 5;

Flashback Query
It is useful to recover from accidental statement failures. For example, suppose a user accidently deletes rows
from a table and commits it also then, using flash back query he can get back the rows.
Flashback feature depends upon on how much undo retention time you have specified. If you have set the
UNDO_RETENTION parameter to 2 hours then, Oracle will not overwrite the data in undo tablespace even
after committing until 2 Hours have passed. Users can recover from their mistakes made since last 2 hours
only.
For example, suppose John gives a delete statement at 10 AM and commits it. After 1 hour he realizes that
delete statement is mistakenly performed. Now he can give a flashback AS.. OF query to get back the deleted
rows like this.
Flashback Query
SQL>select * from emp as of timestamp sysdate-1/24;
Or
SQL> SELECT * FROM emp AS OF TIMESTAMP
TO_TIMESTAMP('2007-06-07 10:00:00', 'YYYY-MM-DD HH:MI:SS')
To insert the accidently deleted rows again in the table he can type
SQL> insert into emp (select * from emp as of timestamp sysdate-1/24)

Introduction of ANSI SQL

SQL is an ANSI (American National Standards Institute) standard
Although SQL is an ANSI (American National Standards Institute) standard, there are different versions of the
SQL language.
However, to be compliant with the ANSI standard, they all support at least the major commands (such as
SELECT, UPDATE, DELETE, INSERT, WHERE) in a similar manner.
An anonymous block is an unnamed sequence of actions. Since they are unnamed, anonymous blocks cannot
be referenced by other program units.
In contrast to anonymous blocks, stored/ named code blocks include Packages, Procedures, and Functions.
Here are some example anonymous blocks written in PL/SQL:

Page no: 16 Follow us on facebook to get real-time updates from RGPV

Downloaded from be.rgpvnotes.in

Description

DECLARE
An optional keyword that starts the DECLARE statement, which can be used to declare data types, variables, or
cursors. The use of this keyword depends upon the context in which the block appears.
declaration
Specifies a variable, cursor, or type declaration whose scope is local to the block. Each declaration must be
terminated by a semicolon.
BEGIN
A mandatory keyword that introduces the executable section, which can include one or more SQL or PL/SQL
statements. A BEGIN-END block can contain nested BEGIN-END blocks.
statement
Specifies a PL/SQL or SQL statement. Each statement must be terminated by a semicolon.
EXCEPTION
An optional keyword that introduces the exception section.
WHEN exception-condition
Specifies a conditional expression that tests for one or more types of exceptions.
THEN handler-statement
Specifies a PL/SQL or SQL statement that is executed if a thrown exception matches an exception in exception-
condition. Each statement must be terminated by a semicolon.
END
A mandatory keyword that ends the block.
Examples
The following example shows the simplest possible anonymous block statement that the DB2 data server can
compile:

BEGIN
NULL;
END;
Example:
DECLARE
current_date DATE := SYSDATE;
BEGIN
dbms_output.put_line( current_date );
END;

Cursor
Database cursor is a control structure that enables traversal over the records in a database. Cursors facilitate
subsequent processing in conjunction with the traversal, such as retrieval, addition and removal of database
records.
The e a e t o t pes of u so s −
1) Implicit Cursors
Implicit cursors are automatically created by Oracle whenever an SQL statement is executed, when there is no
explicit cursor for the statement. Programmers cannot control the implicit cursors and the information in it.
2) Explicit Cursors
Explicit cursors are programmer-defined cursors for gaining more control over the context area. An explicit
cursor should be defined in the declaration section of the PL/SQL Block. It is created on a SELECT Statement
which returns more than one row.
Creating an explicit cursor is –

Page no: 17 Follow us on facebook to get real-time updates from RGPV

Downloaded from be.rgpvnotes.in

 Declaring the cursor for initializing the memory

 Opening the cursor for allocating the memory
 Fetching the cursor for retrieving the data
 Closing the cursor to release the allocated memory
DECLARE
c_id customers.id%type;
c_name customerS.No.ame%type;
c_addr customers.address%type;
CURSOR c_customers is
SELECT id, name, address FROM customers;
BEGIN
OPEN c_customers;
LOOP
FETCH c_customers into c_id, c_name, c_addr;
EXIT WHEN c_customers%notfound;
dbms_output.put_line(c_id || ' ' || c_name || ' ' || c_addr);
END LOOP;
CLOSE c_customers;
END;

Stored procedure
A procedure (often called a stored procedure) is a subroutine like a subprogram in a regular computing language,
stored in database. A procedure has a name, a parameter list, and SQL statement(s).
Why Stored Procedures?
 Stored procedures are fast. MySQL server takes some advantage of caching, just as prepared statements do.
The main speed gain comes from reduction of network traffic. If you have a repetitive task that requires
checking, looping, multiple statements, and no user interaction, do it with a single call to a procedure that's
stored on the server.
 Stored procedures are portable. When you write your stored procedure in SQL, you know that it will run on
every platform that MySQL runs on, without obliging you to install an additional runtime-environment
package, or set permissions for program execution in the operating system, or deploy different packages if you
have different computer types. That's the advantage of writing in SQL rather than in an external language like
Java or C or PHP.
 Stored procedures are always available as 'source code' in the database itself. And it makes sense to link the
data with the processes that operate on the data.
 Stored procedures are migratory! MySQL adheres fairly closely to the SQL:2003 standard. Others (DB2,
Mimer) also adhere.
Create Procedure
Following statements create a stored procedure. By default, a procedure is associated with the default
database (currently used database). To associate the procedure with a given database, specify the name as
database_name. stored_procedure_name when you create it. Here is the complete syntax:
Syntax:
CREATE [DEFINER = { user | CURRENT_USER }]
PROCEDURE sp_name ([proc_parameter[,...]])
[characteristic ...] routine_body
proc_parameter: [ IN | OUT | INOUT ] param_name type
type:
Any valid MySQL data type

Page no: 18 Follow us on facebook to get real-time updates from RGPV

Downloaded from be.rgpvnotes.in

characteristic:
COMMENT 'string'
| LANGUAGE SQL
| [NOT] DETERMINISTIC
| {CONTAINS SQL | NO SQL | READS SQL DATA
| MODIFIES SQL DATA}
| SQL SECURITY {DEFINER | INVOKER}
routine_body:

Function
A stored function is a special kind stored program that returns a single value. You use stored functions to
encapsulate common formulas or business rules that are reusable among SQL statements or stored programs.
Different from a stored procedure, you can use a stored function in SQL statements wherever an expression is
used. This helps improve the readability and maintainability of the procedural code.
MySQL stored function syntax
DELIMITER $$

CREATE FUNCTION CustomerLevel(p_creditLimit double) RETURNS VARCHAR(10)

DETERMINISTIC
BEGIN
DECLARE lvl varchar(10);

IF p_creditLimit > 50000 THEN

SET lvl = 'PLATINUM';
ELSEIF (p_creditLimit <= 50000 AND p_creditLimit >= 10000) THEN
SET lvl = 'GOLD';
ELSEIF p_creditLimit < 10000 THEN
SET lvl = 'SILVER';
END IF;

RETURN (lvl);
END

Trigger
In a DBMS, a trigger is a SQL procedure that initiates an action (i.e., fires an action) when an event (INSERT,
DELETE or UPDATE) occurs. Since triggers are event-driven specialized procedures, they are stored in and
managed by the DBMS. A trigger cannot be called or executed; the DBMS automatically fires the trigger as a
result of a data modification to the associated table. Triggers are used to maintain the referential integrity of
data by changing the data in a systematic fashion.

Syntax CREATE [OR REPLACE] TRIGGER trigger_name

{BEFORE | AFTER | INSTEAD OF}
{INSERT [OR]| UPDATE [OR]| DELETE}
[OF col_name]
ON table_name
Require INSTEAD OF Triggers
A view cannot be modified by UPDATE, INSERT, or DELETE statements if the view query contains any of the
following constructs:
 A set operator

Page no: 19 Follow us on facebook to get real-time updates from RGPV

Downloaded from be.rgpvnotes.in

 A DISTINCT operator
 An aggregate or analytic function
 A GROUP BY, ORDER BY, MODEL, CONNECT BY, or START WITH clause
 A collection expression in a SELECT list
 A subquery in a SELECT list
 A subquery designated WITH READ ONLY
 Joins, with some exceptions, as documented in Oracle Database Administrator's Guide
If a view contains pseudocolumns or expressions, then you can only update the view with
an UPDATE statement that does not refer to any of the pseudocolumns or expressions.
INSTEAD OF triggers provide the means to modify object view instances on the client-side through OCI calls.
To modify an object materialized by an object view in the client-side object cache and flush it back to the
persistent store, you must specify INSTEAD OFtriggers, unless the object view is modifiable. If the object is
read only, then it is not necessary to define triggers to pin it.

Mutating Errors
Mutating table errors occur when a trigger attempts to access a "mutating" table, like the table from which the
trigger has been called. In MySQL, triggers are not initiated by cascading foreign keys, and they cannot modify
the table from which they are called, so neither of those issues can cause a mutating table error. However,
foreign keys appear to operate before 'AFTER' triggers, so it is possible to create a trigger that will cause a
statement to fail.
Instead of Triggers
INSTEAD OF triggers can be defined on either tables or views; however, INSTEAD OF triggers are most useful for
extending the types of updates a view can support. For example, INSTEAD OF triggers can provide the logic to
modify multiple base tables through a view or to modify base tables.

Example using an INSTEAD-OF-trigger:

CREATE VIEW VIEW_TEST
AS SELECT A.NAME , A.ID_LOC , B.CITY , B.STATE , B.COUNTRY , A.DEPARTMENT FROM A, B
where A.ID_LOC = B.ID_LOC;

CREATE TRIGGER TRG_VIEW_TEST_INST_OF

INSTEAD OF INSERT ON VIEW_TEST
FOR EACH ROW
BEGIN
insert into B (ID_LOC, CITY, state, country)
values (varID, varCity, varState, varCountry);

INSERT INTO A (ID, NAME, ID_LOC, DEPARTMENT)

VALUES(varID, varName, varLoc, varDept);

END TRG_TEST_INST_OF;

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

Page no: 20 Follow us on facebook to get real-time updates from RGPV

Downloaded from be.rgpvnotes.in



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 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

Page no: 21 Follow us on facebook to get real-time updates from RGPV

Downloaded from be.rgpvnotes.in

CS-5003

DBMS

Page no: 22 Follow us on facebook to get real-time updates from RGPV

 We hope you find these notes useful.
You can get previous year question papers at
https://qp.rgpvnotes.in .

If you have any queries or you want to submit your

study notes please write us at
rgpvnotes.in@gmail.com