DBMS by Dr.

Deepika Bhatia
Syllabus

UNIT II

Relational Data Model- Concept of Relations, Overview of Various Keys,

Referential Integrity, and foreign keys. Relational Language- Relational Algebra,
Tuple and Domain Relational Calculus, SQL, DDL and DML,

Introduction and basic concepts of PL/SQL(cursors, procedures, triggers).

Basic steps in Query Processing and Optimization.

Relational Data Model-
• The relational Model was proposed by E.F. Codd to model data in the form of relations or tables.
• After designing the conceptual model of the Database using ER diagram, we need to convert the conceptual model into a
relational model which can be implemented using any RDBMS language like Oracle SQL, MySQL, etc.

• The relational model represents how data is stored in Table 1.

Relational Databases. A relational database stores data in ROLL_NO NAME ADDRESS PHONE AGE

the form of relations (tables). 1 ROHAN DELHI

945512345
18
1
• The relational model for database management is an approach
965243154
2 RAMESH GURGAON 18
to logically represent and manage the data stored in a 3

database. In this model, the data is organized into a collection 3 SUJIT BOMBAY
915625313
20
1
of two-dimensional inter-related tables, also known
4 SURESH DELHI 18
as relations.
• Consider a relation STUDENT with attributes
ROLL_NO, NAME, ADDRESS, PHONE, and AGE
shown in Table 1.
IMPORTANT TERMINOLOGIES
•Attribute: Attributes are the properties that define a relation. e.g.; ROLL_NO, NAME
•Relation Schema: A relation schema represents the name of the relation with its attributes. e.g.; STUDENT (ROLL_NO, NAME, ADDRESS,
PHONE, and AGE) is the relation schema for STUDENT. If a schema has more than 1 relation, it is called Relational Schema.
•Tuple: Each row in the relation is known as a tuple. The above relation contains 4 tuples, one of which is shown as:

1 ROHAN DELHI 9455123451 18

•Relation Instance: The set of tuples of a relation at a particular instance of time is called a relation instance. Table 1 shows the
relation instance of STUDENT at a particular time. It can change whenever there is an insertion, deletion, or update in the database.
•Degree: The number of attributes in the relation is known as the degree of the relation. The STUDENT relation defined above ROLL_NO
has degree 5. 1

•Cardinality: The number of tuples in a relation is known as cardinality. The STUDENT relation defined above has cardinality 4. 2
•Column: The column represents the set of values for a particular attribute. The column ROLL_NO is extracted from the relation 3
STUDENT.
4
•NULL Values: The value which is not known or unavailable is called a NULL value. It is represented by blank space. e.g.;
PHONE of STUDENT having ROLL_NO 4 is NULL.
Overview of Various Keys

• A huge amount of data is available in this real-world. Now, for storing

the data in DBMS, a large number of tables are required.

• These tables may contain thousand of duplicate, sorted, and unsorted

Records.

• Now, to fetch any particular or specific record, without any constraints/

restrictions from these tables is a very difficult process.

• To overcome all the difficulties, a new concept of Keys arose. Keys

ensure that there are no rows or tuples with duplicate records. Let’s

understand the concept of Keys in DBMS.

Super Key
• Super Key is a set of attributes that can uniquely identify a table. The Student table has 3 attributes namely-
• A single table can have multiple super keys. Roll_No, ID, and Name.
Now, as we can see, ID and Roll_Number will
• A candidate key, primary key and a unique key can be a super key, but the
contain unique values only. But, the Name column can
reverse does not hold true. contain duplicate values.
• Super key is the key that can uniquely identify any row in a database.
Super keys =
• Candidate Keys are super keys with the least number of columns.
{{Roll_No}, {ID}, {Roll_No, ID}, {Roll_No,
• We can generate the set of all super keys using the candidate keys as a base. Name}, {Roll_No, ID, Name}, {ID, Name}}

Roll List of super keys:

ID Name Real_Life Examples:
_No 1.Roll_No • PAN No + Name
1 CS13 Aman 2.ID • Employee ID + Name
• Aadhar No + DOB + Name
2 CS24 Aditya 3.Roll_No, ID
3 CS184 Sushant 4.Roll_No, Name
4 CS109 Mohit 5.Roll_No, ID, Name
5 CS136 Saumya 6.ID, Name
Candidate Key
•The candidate keys in a table are defined as the set of keys that is Let us select the candidate keys from the above superset of super keys.
minimal and can uniquely identify any data row in the table. 1.Roll_No: Roll_No is a candidate key as it can find all the rest attributes,
Properties of Candidate Key i.e., ID, and Name of the table. Also, {Roll_No} does not contain any
•Candidate key can uniquely identify all the other attributes of a table. redundant attributes.
•The candidate key should not have redundant attributes.
2.ID: ID is a candidate key as it can find all the rest attributes,
•Candidate keys are a subset of the super key. Hence, a candidate key is
i.e., Roll_No, and Name of the table. Also, {ID} does not contain any
a super key but vice versa is not correct.
redundant attributes.
•The attributes of a candidate key can contain null values.
•A Primary key is determined from the set of candidate keys by the Data 3.Roll_No, ID: {Roll_No, ID} contains redundant attributes as

Base Administrator. either Roll_No or ID can uniquely identify the other attributes of the table.
•The candidate key helps in determining the prime and non-prime Hence, {Roll_No, ID} is not a candidate Key.
attributes of a table. 4.Roll_No, Name: also contains redundant attribute, i.e., Name as
•The candidate key ensures the integrity of the data by preventing
only Roll_No can uniquely identify the other attributes of the table.
duplicate data.
Hence, {Roll_No, Name} is not a candidate Key.
•The candidate key must be unique.
5.Roll_No, ID, Name : {Roll_No, ID, Name} is not a candidate Key.
6.ID, Name: {ID, Name} {ID, Name} is not a candidate Key.

Candidate keys = {{ID}, {Roll_No}}.

Primary Key

•The primary key is selected from one of the candidate keys and becomes the identifying key of a table. It can uniquely
identify any data row of the table.
•It is the first key used to identify one and only one instance of an entity uniquely. An entity can contain multiple keys,
as we saw in the PERSON table. The key which is most suitable from those lists becomes a primary key.
Rules for Primary Key
•Unique values must be present in all columns, chosen as primary key
•A single table can have only one primary key
•No NULL value must be present in the column chosen as primary key
•A new row cannot be inserted with an existing primary key

Primary Key can be either ID or Roll_No.

One thing that the Data Base Administrator can think of is that when a student is enrolled, the student is given an ID but
his/her Roll Number can be given after some time. So, he can choose ID over the Roll_no attribute.
Alternate Key

•The Keys other than Primary

Key are called Alternate Keys.
•Alternate(Secondary) Keys
can be made Primary Key.
•Sometimes
Secondary/Alternate key is
required for the indexing, for
better and faster searching.

Roll No can be the alternate Key

Composite key

• Whenever a primary key consists of more than one attribute, it is known as a composite key. This key is
also known as Concatenated Key.
• Sometimes, a table might not have a single column/attribute that uniquely identifies all the records of a
table. To uniquely identify rows of a table, a combination of two or more columns/attributes can be
used. It still can give duplicate values in rare cases. So, we need to find the optimal set of attributes that
can uniquely identify rows in a table.
• It acts as a primary key if there is no primary key in a table.
• Two or more attributes are used together to make a composite key.
• Different combinations of attributes may give different accuracy in terms of identifying the rows uniquely.
Foreign key
•Foreign keys are the column of the table used to point
to the primary key of another table.
•Every employee works in a specific department in a
company, and employee and department are two
different entities. So we can't store the department's
information in the employee table. That's why we link
these two tables through the primary key of one table.
•We add the primary key of the DEPARTMENT table,
Department_Id, as a new attribute in the EMPLOYEE
table.
•In the EMPLOYEE table, Department_Id is the foreign
key, and both the tables are related.
Foreign key

https://www.programiz.com/sql/foreign-key
Surrogate Key
SR_
•This key is an artificial key that can distinctly identify every row Name Age Gender City
No.
in the table. Rajay
1 22 Male Delhi
Gangwar
•Surrogate Keys of DBMS are allowed in certain cases like
Vikas
• The primary key is too big, 2 22 Male Delhi
Yadav
• complicated Primary Key Sumit
3 22 Male Delhi
Bhatia
• Absence of key.
Vikas
4 22 Male Delhi
•It is unique, updatable, and can’t be NULL. Yadav
Vinay
•Example: 5 22 Male Delhi
Singh
•Real-Life Example:
• SR No
• Phone No Above Anonymous Table doesn’t have any primary key.
• Room No Thus, SR_No. is used as the Surrogate key. SR_No. is generated
• Batch No at the time of the insertion of the records. It is updatable.
• Shifts (like Morning Shift, Evening Shift, etc.)
Integrity Constraints
•Integrity constraints are a set of rules. It is used to maintain the 1. Domain constraints
quality of information.
•Domain constraints can be defined as the definition of a
•Integrity constraints ensure that the data insertion, updating, and
valid set of values for an attribute.
other processes have to be performed in such a way that data
•The data type of domain includes string, character, integer,
integrity is not affected.
•Thus, integrity constraint is used to guard against accidental time, date, currency, etc. The value of the attribute must be

damage to the database. available in the corresponding domain.

2. Entity integrity constraints 3. Referential Integrity Constraints
4. Key constraints
•The entity integrity constraint states that primary •A referential integrity constraint is specified
Keys are the set of entities that are used to
key value can't be null. between two tables.
identify an entity within its entity set uniquely.
•This is because the primary key value is used to •In the Referential integrity constraints, if a
identify individual rows in relation and if the There could be multiple keys in a single entity
foreign key in Table 1 refers to the Primary
primary key has a null value, then we can't identify set, but out of these multiple keys, only one
Key of Table 2, then every value of the Foreign
those rows. key will be the primary key. A primary key can
Key in Table 1 must be null or be available in
•A table can contain a null value other than the only contain unique and not null values in the
primary key field. Example: Table 2.
relational database table.
Referential Integrity Constraint CONTINUED
Referential Integrity Constraint : Referential Integrity is set of constraints applied to foreign key which
prevents entering a row in child table (where you have foreign key) for which you don't have any corresponding
row in parent table .Referential Integrity prevents your table from having incorrect or incomplete relationship .

Advantages of referential Integrity :

1.Prevents the entry of duplicate data
2.Prevents one table from pointing to a nonexistent field in another table
3.Guarantees consistency between "partnered" tables
4.Prevents the deletion of a record that contains a value referred to by a foreign key in another table
5.Prevents the addition of a record to a table that contains a foreign key unless there is a primary key in
the linked table
Foreign key
Persons Table

• A Foreign key (FK) is a column or combination of columns that is PersonID LastName FirstName Age

used to establish and enforce a link between the data in two tables 1 Hansen Ola 30
2 Svendson Tove 23
to control the data that can be stored in the foreign key table.
3 Pettersen Kari 20
• The FOREIGN KEY constraint is used to prevent actions that
Orders Table
would destroy links between tables. OrderID OrderNumber PersonID
1 77895 3
• A FOREIGN KEY is a field (or collection of fields) in one table,
2 44678 3
that refers to the PRIMARY KEY in another table. 3 22456 2

• The table with the foreign key is called the child table, and the 4 24562 1

table with the primary key is called the referenced or parent • Notice that the "PersonID" column in the "Orders" table points to the "PersonID" column
in the "Persons" table.
table. • The "PersonID" column in the "Persons" table is the PRIMARY KEY in the "Persons" table.
• The "PersonID" column in the "Orders" table is a FOREIGN KEY in the "Orders" table.
CREATE TABLE Order(
-> order_id INT,
-> item VARCHAR(40),
-> amount INT,
-> customer_id INT,
-> FOREIGN KEY (customer_id) REFERENCES master(id)
-> )
-> ;

ALTER TABLE Orders

ADD FOREIGN KEY (PersonID) REFERENCES Persons(PersonID);

Here, the customer_id field in the Orders table is FOREIGN

KEY which references the id field in the Customers table.
This means that the value of the customer_id (of
the Orders table) must be a value from the id column (of
the Customers table).
What is Relational Calculus?
Before understanding Relational calculus in DBMS, we need to understand Procedural
Language and Declarative Language.
1.Procedural Language - Those Languages which clearly define how to get the required results
from the Database are called Procedural Language. Relational algebra is a Procedural
Language.
2.Declarative Language - Those Language that only cares about What to get from the database
without getting into how to get the results are called Declarative Language. Relational
Calculus is a Declarative Language.
So Relational Calculus is a Declarative Language that uses Predicate Logic or First-Order Logic to
determine the results from Database.
Tuple Relational Calculus (TRC) and Domain Relational Calculus (DRC)

Relational Calculus: There is an alternate way of formulating queries known as

Relational Calculus. Relational calculus is a non-procedural query language. In the
non-procedural query language, the user is concerned with the details of how to
obtain the end results. The relational calculus tells what to do but never explains how
to do. Most commercial relational languages are based on aspects of relational
calculus including SQL-QBE and QUEL.

• Tuple Relational Calculus (TRC) and Domain Relational Calculus (DRC) are both formal languages used to specify queries on
a relational database.
• They are non-procedural languages that operate on a set of tuples (rows) in one or more relations (tables) and return a set of
tuples that satisfy a given condition.

• The main difference between Tuple Relational Calculus and Domain Relational Calculus is the way they express queries.
• Tuple Relational Calculus expresses queries in terms of logical formulas using variables to refer to tuples and conditions
to restrict the result set, while DRC expresses queries in terms of domain variables and quantifiers, which are used to
specify constraints on the tuples that should be returned.
Tuple Relational Calculus (TRC) :
A tuple relational calculus is a non-procedural query language that specifies to select of the tuples in a relation.
It can select the tuples with a range of values or tuples for certain attribute values etc. The resulting relation can
have one or more tuples.

Notation : {T | P (T)} or {T | Condition (T)}

where T is the resulting tuples and P(T) is a condition used to fetch T.
Example :
{T | EMPLOYEE (T) AND T.DEPT_ID = 10}
This selects all the tuples of employee names who work for Department 10.
2. Domain Relational Calculus (DRC) :
A domain relational calculus uses the list of attributes to be selected from the relation based on the
condition. It is the same as TRC but differs by selecting the attributes rather than selecting whole
tuples.

Notation : { a1, a2, a3, ..., an | P (a1, a2, a3, ..., an) }
Where a1, a2, a3, … an are attributes of the relation and P is the condition.

Example :
{ | < EMPLOYEE > DEPT_ID = 10 }
select EMP_ID and EMP_NAME of employees who work for department 10.
Quantifiers in Tuple Relation Calculus
Quantifiers in the tuple relational calculus is a determiner that determines the scope of the term it is
attached to. Quantifiers are of two types universal quantifier and existential quantifier.
Tuple Relational Calculus (TRC) Domain Relational Calculus (DRC)
The Tuple Relational Calculus (TRC) is used to select tuples from a relation. The Domain Relational Calculus (DRC) employs a list of attributes
The tuples with specific range values, tuples with certain attribute values, and from which to choose based on the condition. It’s similar to TRC, but
so on can be selected. instead of selecting entire tuples, it selects attributes.
In DRC, the variables represent the value drawn from a specified
In TRC, the variables represent the tuples from specified relations.
domain.
A domain is equivalent to column data type and any constraints on the
A tuple is a single element of relation. In database terms, it is a row.
value of data.
This filtering variable uses a tuple of relations. This filtering is done based on the domain of attributes.
The predicate expression condition associated with the TRC is used to test DRC takes advantage of domain variables and, based on the condition
every row using a tuple variable and return those tuples that met the set, returns the required attribute or column that satisfies the criteria
condition. of the condition.

The query cannot be expressed using a membership condition. The query can be expressed using a membership condition.

The QUEL or Query Language is a query language related to it, The QBE or Query-By-Example is query language related to it.
It reflects traditional pre-relational file structures. It is more similar to logic as a modeling language.
Notation: {T | P (T)} or {T | Condition (T)} Notation: { a1, a2, a3, …, an | P (a1, a2, a3, …, an)}
{T | EMPLOYEE (T) AND T.DEPT_ID = 10} { | < EMPLOYEE > DEPT_ID = 10 }
Uses tuple variables (e.g., t) Uses scalar variables (e.g., a1, a2, …, an)
Less expressive More expressive
Easier to use for simple queries. More difficult to use for simple queries.
Useful for selecting tuples that satisfy a certain condition or for retrieving a Useful for selecting specific values or for constructing more complex
subset of a relation. queries that involve multiple relations.
For SQL queries, and DDL,DML commands do refer lab
work done.
Schema Diagram for University Database
Database Schema
 Database schema -- is the logical structure of the database.
 Database instance -- is a snapshot of the data in the database at a given instant in time.

Example:
schema: instructor (ID, name, dept_name, salary)
Instance:
• Relational Algebra is a
procedural query language,
which takes Relation as input
and generates relation as output.
Relational algebra mainly
provides a theoretical foundation
for relational databases and
SQL.
• Relational Algebra is a
procedural query language used
to query the database tables to
access data in different ways.

• In relational algebra, input is a

relation(table from which data
has to be accessed) and output is
also a relation(a temporary table
holding the data asked for by the
user).
Select Operation
 The select operation selects tuples that satisfy a given predicate.

 Notation:  p(r)
 p is called the selection predicate
 We allow comparisons using
=, , >, . <.  Example: select those tuples of the
 We can combine several predicates into a larger predicate by using the connectives:instructor relation where the instructor is
 (and),  (or),  (not) in the “Physics” department.
 Example: Find the instructors in Physics with a salary greater $90,000, we write:
 dept_name=“Physics” (instructor)
 dept_name=“Physics”  salary > 90,000 (instructor)

 Then select predicate may include comparisons between two attributes.

 Example, find all departments whose name is the same as their building name:
 dept_name=building (department)
Project Operation
 Example: eliminate the dept_name attribute of instructor

 Query: ID, name, salary (instructor )

Projection
 One input relation.

 Deletes attributes that are not in projection list.

 Schema of result contains exactly the attributes in the projection list, with the same names that they had
in the (only) input relation.

 Projection operation has to eliminate duplicates, since relations are sets.

 Duplicate elimination is expensive.

 Commercial DBMS typically don’t do duplicate elimination unless the user explicitly asks for it.
Selection

S2
sid sname rating age
28 yuppy 9 35.0  rating 8(S2) sid sname rating age
31 lubber 8 55.5 28 yuppy 9 35.0
44 guppy 5 35.0 58 rusty 10 35.0
58 rusty 10 35.0
Projection

sname rating
 sname,rating(S2) yuppy 9
lubber 8
guppy 5
S2 sid sname rating age rusty 10
28 yuppy 9 35.0
31 lubber 8 55.5
44 guppy 5 35.0
age
58 rusty 10 35.0
35.0
55.5
 Other fields are projected out  age(S2)
Selection

 But the result of an expression is another relation, we can substitute an expression wherever a relation is expected

S2

sid sname rating age  rating 8(S2) sid sname rating age
28 yuppy 9 35.0 28 yuppy 9 35.0
31 lubber 8 55.5 58 rusty 10 35.0
44 guppy 5 35.0
58 rusty 10 35.0
 sname,rating( rating  8(S2))

sname rating
yuppy 9
rusty 10
Union
S1 S2
sid sname rating age
sid sname rating age
28 yuppy 9 35.0
22 dustin 7 45.0
31 lubber 8 55.5
31 lubber 8 55.5 44 guppy 5 35.0
58 rusty 10 35.0
58 rusty 10 35.0
S1 S2
sid sname rating age
22 dustin 7 45.0
31 lubber 8 55.5
58 rusty 10 35.0
44 guppy 5 35.0
28 yuppy 9 35.0 SELECT * FROM S1
UNION
 Result contains ALL tuples that occur in either S1 or S2 SELECT * FROM S2
 Schemas must be identical
 If they have the same number of fields
 Fields have same domains
Aggregate Functions

• Very useful to apply a function to a

collection of values to generate a single
result ¨
• Most common aggregate functions:
a) sum sums the values in the collection
b) avg computes average of values in the
collection
c) count counts number of elements in
the collection
d) min returns minimum value in the
collection
e) max returns maximum value in the
collection ¨
• Aggregate functions work on multisets,
not sets ¤ A value can appear in the
input multiple times
Division
sno pno pno pno pno
s1 p1 p2 p2 p1
s1 p2 p4 p2
s1 p3 B1 p4
s1 p4 B2
s2 p1 sno B3
s2 p2 s1
s3 p2 s2 sno
s4 p2 s3 s1 sno
s4 p4 s4 s4 s1

A A/B1 A/B2 A/B3

Division
 Division is not an essential operation; can be implemented
using the five basic operations. If such courses don’t exist (NOT
EXISTS), then student must’ve
taken all courses
SELECT name
FROM Student
WHERE NOT EXISTS (
SELECT CID
FROM Course
returns those courses
WHERE CID NOT IN ( this student didn’t
SELECT CID take
FROM Register
WHERE Student.SID
= Register.SID))
Intersection
S1 S2
sid sname rating age
sid sname rating age
28 yuppy 9 35.0
22 dustin 7 45.0
31 lubber 8 55.5
31 lubber 8 55.5 44 guppy 5 35.0
58 rusty 10 35.0 58 rusty 10 35.0

𝑺𝟏 ∩ 𝑺𝟐

 Result contains ALL tuples that occur in both S1 or S2

 Schemas must be identical
SELECT * FROM S1
INTERSECT
SELECT * FROM S2
Set-Difference
S1 S2
sid sname rating age sid sname rating age
22 dustin 7 45.0 28 yuppy 9 35.0
31 lubber 8 55.5
31 lubber 8 55.5
44 guppy 5 35.0
58 rusty 10 35.0 58 rusty 10 35.0

𝑺𝟏 − 𝑺𝟐

 Result contains ALL tuples that

SELECT * FROM S1
occur in S1 but not in S2.
MINUS
 Schemas must be identical. SELECT * FROM S2
Cartesian Product

 Also referred to as cross-product or product.

 Two input relations.

 Each tuple of the one relation is paired with each tuple of the other
relation.

 Result schema has one attribute per attribute of both input relations, with
attribute names `inherited’ if possible.
Cartesian Product

Field names in conflict become unnamed

S1 R1
sid sname rating age sid bid day
22 dustin 7 45.0 22 101 10/10/96
31 lubber 8 55.5 58 103 11/12/96
58 rusty 10 35.0

(sid) sname rating age (sid) bid day

22 dustin 7 45.0 22 101 10/10/96
22 dustin 7 45.0 58 103 11/12/96
31 lubber 8 55.5 22 101 10/10/96 𝑺𝟏 × 𝑺𝟐
31 lubber 8 55.5 58 103 11/12/96
58 rusty 10 35.0 22 101 10/10/96
58 rusty 10 35.0 58 103 11/12/96
Join

 Similar to Cartesian product with same result schema.

 Cartesian Product has no conditions
 Joinhas conditions
 Join can be defined as a Cartesian Product, followed by selections and
projections
 Join however is much more efficient

S1 R1

sid sname rating age sid bid day

22 dustin 7 45.0 22 101 10/10/96
31 lubber 8 55.5 58 103 11/12/96
58 rusty 10 35.0
Natural join is an SQL join operation that creates a join on the
base of the common columns in the tables. To perform
natural join there must be one common attribute(Column)
between two tables. Natural join will retrieve from multiple
relations. It works in three steps. Lets have 2 tables,
employee and department below:

In SQL:
SELECT *
FROM employee
NATURAL JOIN department
Theta Join (θ)
Theta Join combines two relations using a condition. This condition is
represented by the symbol "theta"(θ).

Here conditions can be inequality conditions such as >,<,>=,<=, etc.

Notation : R ⋈θ S
Where R is the first relation and S is the second relation.
Theta Join (θ)
EMPLOYEE ⋈θ EMPLOYEE.EXPERIENCE>=DEPARTMENT.MIN_EXPERIENCE DEPARTMENT
EMPLOYEE E_NO E_NAME CITY
EXPERIE
D_NO D_NAME E_NO
MIN_EXP
NCE ERIENCE
EXPERIE
E_NO E_NAME CITY E-1 Ram Delhi 04 D-1 HR E-1 03
NCE
Marketin
E-1 Ram Delhi 04 E-1 Ram Delhi 04 D-3 E-3 02
g
Chandiga Chandiga
E-2 Varun 09 E-2 Varun 09 D-1 HR E-1 03
rh rh
E-3 Ravi Noida 03 Chandiga
E-2 Varun 09 D-2 IT E-2 05
rh
Bangalor
E-4 Amit 07 E-2 Varun
Chandiga
09 D-3
Marketin
E-3 02
e rh g
DEPARTMENT
E-3 Ravi Noida 03 D-1 HR E-1 03
MIN_EXP Marketin
D_NO D_NAME E_NO E-3 Ravi Noida 03 D-3 E-3 02
ERIENCE g
Bangalor
E-4 Amit 07 D-1 HR E-1 03
D-1 HR E-1 03 e
Bangalor
D-2 IT E-2 05 E-4 Amit 07 D-2 IT E-2 05
e
Marketin Bangalor Marketin
D-3 E-3 02 E-4 Amit 07 D-3 E-3 02
g e g
Equijoin(⋈):
• Equijoin is a special case of conditional join where
only equality condition holds between a pair of
attributes.
STUDENT⋈ STUDENT.Regno=EMPLOYEE.EMP_NO EMPLOYEE
Outer Join
There are three types of outer joins:
•Left outer join ( denoted by the symbol ⟕
•Right outer join( denoted by the symbol ⟖
•Full outer join ( denoted by the symbol ⟗

Right Outer Join

Left Outer Join ⟕

Full outer join (⟗)

The full outer join contains all
tuples from both tables. The full
outer join of the above table
represented as A and B can be
represented as:
PL/SQL=SQL+ Programming Language

 PL/SQL is a combination of SQL along with the procedural features of programming languages. It was developed by Oracle
Corporation in the early 90's to enhance the capabilities of SQL.

 PL/SQL is one of three key programming languages embedded in the Oracle Database, along with SQL itself and Java.

 The PL/SQL programming language was developed by Oracle Corporation in the late 1980s as procedural extension language for
SQL and the Oracle relational database. Following are notable facts about PL/SQL:

• PL/SQL is a completely portable, high-performance transaction-processing

language.
• PL/SQL provides a built-in interpreted and OS independent programming
environment.
• PL/SQL can also directly be called from the command-line SQL*Plus interface.
• Direct call can also be made from external programming language calls to database.
• PL/SQL's general syntax is based on that of ADA and Pascal programming
language.
• Apart from Oracle, PL/SQL is available in TimesTen in-memory database and IBM
DB2.
Features of PL/SQL
 PL/SQL has the following features:

 PL/SQL is tightly integrated with SQL.

 It offers extensive error checking.

 It offers numerous data types.

 It offers a variety of programming structures.

 It supports structured programming through functions

and procedures.

 It supports object-oriented programming.

 It supports developing web applications and server

pages.
PL/SQL Engine and Server
PL/SQL allows you to mix SQL statements with procedural statements like IF statements. Looping structure etc, PL/SQL is the superset of
SQL. It uses SQL for data retrieval and manipulation and uses its own statement for data processing. Pl/SQL program units are generally
categorized as follows −

Anonymous block
This is a PL/SQL block that appears within your application. In many applications PL/SQL blocks can appear where SQL
statements can appear. Such blocks are called Anonymous blocks.

Stored procedures
• This is a PL/SQL block that is stored in the database with a name. Application
programs are executing these procedures using the name.
• Oracle allows you to create functions, which are the same as procedures but
return a value, and packages, which are a collection of procedures and
functions.

PL/SQL Engine
• Every PL/SQL block is first executed by the PL/SQL engine. This is the engine
that compiles and executes Pl/SQL blocks.
• PL/SQL engine is available in oracle server and certain oracle tools such as
oracle forms and oracle reports. It executes all procedural statements of a
PL/SQL of the block, but sends SQL commands to SQL statement executors in
the oracle RDBMS.
• That means PL/SQL separates SQL commands from PL/SQL commands and
executes PL/SQL commands using procedural statement executor, which is a
part of the PL/SQL engine.
PL/SQL Program Units
 A PL/SQL unit is any one of the following:

 PL/SQL block, Function, Package, Package body, Procedure, Trigger, Type, Type
body

•Declare section starts: with DECLARE keyword in which variables, constants, records as
cursors can be declared which stores data temporarily. It basically consists definition of PL/SQL
identifiers. This part of the code is optional.
•Execution section starts with BEGIN and ends with END keyword. This is a mandatory
section and here the program logic is written to perform any task like loops and conditional
statements. It supports all DML commands, DDL commands and SQL*PLUS built-in functions
as well.
•Exception section starts with EXCEPTION keyword.This section is optional which contains
statements that are executed when a run-time error occurs. Any exceptions can be handled in
this section.
Nested Block Structure
 A block can be nested into another block.
 This can be nested either in the execution part
or in the exception handling part.
 These block can also be labelled. One outer
block can contain many inner blocks. Each inner
block is once again a PL/SQL block, hence all
the properties and characteristics of the inner
block will be the same as outer block.
 The below image gives the pictorial
representation of nested block structure. Parent
block is the main block and child block is the
nested block.
The 'Hello World' Example:

 Every PL/SQL statement ends with a

semicolon (;).
 PL/SQL blocks can be nested within other
PL/SQL blocks using BEGIN and END.
 Here is the basic structure of a
PL/SQL block:
The end; line signals the end of the PL/SQL block.
To run the code from SQL command line, you may
need to type / at the beginning of the first blank
line after the last line of the code. When the above
code is executed at SQL prompt, it produces the
following result:
The PL/SQL Comments

 Program comments are explanatory statements that

you can include in the PL/SQL code that you write
and helps anyone reading its source code. All
programming languages allow for some form of
comments.

 The PL/SQL supports single-line and multi-line

comments. All characters available inside any
When the above code is executed at SQL prompt, it produces the following result:
comment are ignored by PL/SQL compiler. The
PL/SQL single-line comments start with the
delimiter -- (double hyphen) and multi-line comments
are enclosed by /* and */.
*****: Addition Program in PL/SQL
Procedures in pl/sql- Refer class notes
What is Cursor in SQL ?
What is Cursor in SQL ?
Cursor is a Temporary Memory or Temporary Work Station. It is Allocated by Database Server at the Time of
Performing DML(Data Manipulation Language) operations on the Table by the User. Cursors are used to store
Database Tables.
There are 2 types of Cursors: Implicit Cursors, and Explicit Cursors. These are explained as following below.
1.Implicit Cursors: Implicit Cursors are also known as Default Cursors of SQL SERVER. These Cursors are
allocated by SQL SERVER when the user performs DML operations.
2.Explicit Cursors: Explicit Cursors are Created by Users whenever the user requires them. Explicit Cursors are
used for Fetching data from Table in Row-By-Row Manner.
How To Create Explicit Cursor?
Declare Cursor Object
Syntax: DECLARE cursor_name CURSOR FOR SELECT * FROM table_name

Query: DECLARE s1 CURSOR FOR SELECT * FROM studDetails

2. Open Cursor Connection

Syntax: OPEN cursor_connection

Query: OPEN s1
Fetch Data from the Cursor There is a total of 6 methods to access data from the cursor. They are as follows:
FIRST is used to fetch only the first row from the cursor table.
LAST is used to fetch only the last row from the cursor table.
NEXT is used to fetch data in a forward direction from the cursor table.
PRIOR is used to fetch data in a backward direction from the cursor table.
ABSOLUTE n is used to fetch the exact nth row from the cursor table.
RELATIVE n is used to fetch the data in an incremental way as well as a decremental way.
Syntax: FETCH NEXT/FIRST/LAST/PRIOR/ABSOLUTE n/RELATIVE n FROM cursor_name

Query:
FETCH FIRST FROM s1
FETCH LAST FROM s1
FETCH NEXT FROM s1
FETCH PRIOR FROM s1
FETCH ABSOLUTE 7 FROM s1
•Close cursor connection
Syntax:
CLOSE cursor_name
Query:
CLOSE s1
•Deallocate cursor memory
Syntax:
DEALLOCATE cursor_name
How To Create an Implicit Cursor?
An implicit cursor is a cursor that is automatically created by PL/SQL when you execute a SQL statement. You don’t need to
declare or open an implicit cursor explicitly. Instead, PL/SQL manages the cursor for you behind the scenes.

To create an implicit cursor in PL/SQL, you simply need to execute a SQL statement. For example, to retrieve all rows from the
EMP table, you can use the following code:

Query:
BEGIN
FOR emp_rec IN SELECT * FROM emp LOOP
DBMS_OUTPUT.PUT_LINE('Employee name: ' || emp_rec.ename);
END LOOP;
END;
In PL/SQL, when we perform INSERT, UPDATE or DELETE operations, an implicit cursor is automatically created. This cursor
holds the data to be inserted or identifies the rows to be updated or deleted. You can refer to this cursor as the SQL cursor in
your code. Thi SQL cursor has several useful attributes.

%FOUND is true if the most recent SQL operation affected at least one row.
%NOTFOUND is true if it didn’t affect any rows.
%ROWCOUNT is returns the number of rows affected.
%ISOPEN checks if the cursor is open.
In addition to these attributes, %BULK_ROWCOUNT and %BULK_EXCEPTIONS are specific to the FORALL statement, which is
used to perform multiple DML operations at once. %BULK_ROWCOUNT returns the number of rows affected by each DML
operation, while %BULK_EXCEPTION returns any exception that occurred during the operations.
Query:

CREATE TABLE Emp(

EmpID INT PRIMARY KEY,
Name VARCHAR(50),
Country VARCHAR(50),
Age int(2),
Salary int(10)
);
-- Insert some sample data into the Customers table
INSERT INTO Emp (EmpID, Name,Country, Age, Salary)
VALUES (1, 'Shubham', 'India','23','30000'),
(2, 'Aman ', 'Australia','21','45000'),
(3, 'Naveen', 'Sri lanka','24','40000'),
(4, 'Aditya', 'Austria','21','35000'),
(5, 'Nishant', 'Spain','22','25000');
Select * from Emp;

This program updates a table by increasing the salary of each employee by 1500. After the update, the SQL%ROWCOUNT attribute
is used to find out how many rows were affected by the operation.
Query:

DECLARE
total_rows number;
BEGIN
UPDATE Emp
SET Salary = Salary + 1500;

total_rows := SQL%ROWCOUNT;

dbms_output.put_line(total_rows || ' rows updated.');

END;
Output:

5 Emp selected
Need of Cursor in an SQL server
1.Cursors allow us to process data row-by-row, which can be useful when we need to perform complex calculations or
transformations on the data.
2.Cursors allow us to iterate over a result set multiple times, which can be useful when we need to perform multiple operations
on the same data.
3.Cursors can be useful when we need to join multiple tables with complex relationships, such as when processing hierarchical
data structures or when performing recursive queries.
4.Cursors allow us to perform operations such as updating, deleting, or inserting records based on some condition or criteria.
5.Cursors are especially useful when processing data from multiple tables where the relationships are not straightforward.
SQL Server Cursor Limitations
As a cursor has some limitations, it should only be used when there is no other choice. These restrictions include:
1.When processing data, it imposes locks on a subset or the entire table.
2.The cursor updates table records one row at a time, which slows down its performance.
3.While loops are slower than cursors, they do have more overhead.
4.Another factor that influences cursor speed is the quantity of rows and columns brought into the cursor.
Link for triggers topic of pl/sql

PL/SQL - Triggers (tutorialspoint.com)

Query Processing in DBMS
Query Processing is the activity performed in extracting data from the database. In query processing, it
takes various steps for fetching the data from the database. The steps involved are:
1.Parsing and translation
2.Optimization
3.Evaluation
The query processing works in the following way:
Parsing and Translation:-
As query processing includes certain activities for data retrieval. Initially, the given user queries get
translated in high-level database languages such as SQL. It gets translated into expressions that can be
further used at the physical level of the file system. After this, the actual evaluation of the queries and a
variety of query -optimizing transformations and takes place. Thus before processing a query, a computer
system needs to translate the query into a human-readable and understandable language. Consequently,
SQL or Structured Query Language is the best suitable choice for humans. But, it is not perfectly suitable
for the internal representation of the query to the system. Relational algebra is well suited for the internal
representation of a query. The translation process in query processing is similar to the parser of a query.
When a user executes any query, for generating the internal form of the query, the parser in the system
checks the syntax of the query, verifies the name of the relation in the database, the tuple, and finally the
required attribute value. The parser creates a tree of the query, known as 'parse-tree.' Further, translate it
into the form of relational algebra. With this, it evenly replaces all the use of the views when used in the
query.
Suppose a user executes a query. As we have learned that there are various methods of extracting the data
from the database. In SQL, a user wants to fetch the records of the employees whose salary is greater than
or equal to 10000. select emp_name from Employee where salary>10000;
Thus, to make the system understand the user query, it needs to be translated in the form of relational
algebra. We can bring this query in the relational algebra form as:
•σsalary>10000 (πsalary (Employee))
•πsalary (σsalary>10000 (Employee))
After translating the given query, we can execute each relational algebra operation by using different
algorithms. So, in this way, a query processing begins its working.
Evaluation
For this, with addition to the relational algebra translation, it is required to annotate the translated
relational algebra expression with the instructions used for specifying and evaluating each operation. Thus,
after translating the user query, the system executes a query evaluation plan.
Query Evaluation Plan
•In order to fully evaluate a query, the system needs to construct a query evaluation
plan.
•The annotations in the evaluation plan may refer to the algorithms to be used for the
particular index or the specific operations.
•Such relational algebra with annotations is referred to as Evaluation Primitives. The
evaluation primitives carry the instructions needed for the evaluation of the operation.
•Thus, a query evaluation plan defines a sequence of primitive operations used for
evaluating a query. The query evaluation plan is also referred to as the query execution
plan.
Optimization
•The cost of the query evaluation can vary for different types of queries. Although the system is responsible for constructing the
evaluation plan, the user does need not to write their query efficiently.
•Usually, a database system generates an efficient query evaluation plan, which minimizes its cost. This type of task performed
by the database system and is known as Query Optimization.
•For optimizing a query, the query optimizer should have an estimated cost analysis of each operation. It is because the overall
operation cost depends on the memory allocations to several operations, execution costs, and so on.
Finally, after selecting an evaluation plan, the system evaluates the query and produces the output of the query.