DBMS 7 | Relational Query Language

Database Management System
Relational Query Language
Mohammad Imam Hossain, Lecturer, Dept. of CSE, UIU

Design Phases
2
User Requirements Specification
Conceptual-design Phase
(ER Diagram)
Logical-design Phase
(Relational Schema)
Physical-design Phase
(Relational Database, MySQL)
CREATE TABLE customer
(
cust_id INT NOT NULL,
cust_name VARCHAR(30) NOT NULL,
cust_street VARCHAR(100) NOT NULL,
cust_city VARCHAR(100) NOT NULL,
PRIMARY KEY (cust_id)
);
CREATE TABLE borrower
(
access_date DATE NOT NULL,
loan_id INT NOT NULL,
PRIMARY KEY (cust_id, loan_id),
FOREIGN KEY (cust_id) REFERENCES customer(cust_id),
FOREIGN KEY (loan_id) REFERENCES loan(loan_id)
);
CREATE TABLE loan
(
amount NUMERIC NOT NULL,
PRIMARY KEY (loan_id)
);

Structured Query Language
3
(ER Diagram)
(Relational Schema)
(
);
(
);
CREATE TABLE loan
(
);
Structured Query Language (SQL)
▪ Domain-specific programming language
▪ Highly targeted language for talking to databases

4
(ER Diagram)
(Relational Schema)
(
);
(
);
CREATE TABLE loan
(
);
Structured Query Language (SQL)
▪ Domain-specific programming language
▪ Highly targeted language for talking to databases
▪ Query language
– allows manipulation and retrieval of data from a database
▪ Uses relational algebra to communicate with the database.

Relational Algebra
Relational Algebra
▸ Relational Algebra is a procedural language consisting of a set of operations that take one or two relations as input and
produce a new relation as their result.
▸ Six basic operations:
▪ select: 𝜎
▪ project: Π
▪ union: ∪
▪ set difference: −
▪ Cartesian product: ×
▪ rename: 𝜌
▸ Additional operations:
▪ set intersection: ∩
▪ division: ÷
▪ assignment: ←
▪ aggregate: 𝒢
▪ Natural join: ⋈
▪ Theta join: ⋈𝜃
▪ Outer join: ⋈ (𝑙𝑒𝑓𝑡), ⋈ 𝑟𝑖𝑔ℎ𝑡 , ⋈ (𝑓𝑢𝑙𝑙)
5
⊐ ⊏
⊐ ⊐ ⊏

project Operation
project Operation
▸ Notation: ς𝐴1, 𝐴2, … , 𝐴𝑘
(𝑟)
▸ 𝐴1, 𝐴2, … , 𝐴𝑘 are attribute names
▸ 𝑟 is a relation name
▸ Duplicate rows are removed from the result, since relations are sets
6
A B C D
p p 10 70
p q 50 70
q q 12 30
q q 25 15
Relation, r
SELECT A, C
FROM r
ς𝐴,𝐶(𝑟)
A C
p 10
p 50
q 12
q 25
MySQL
output

select Operation
select Operation
▸ Notation: 𝜎𝑝 𝑟
▸ 𝑝 is called the selection predicate
▸ Defined as: 𝜎𝑝 𝑟 = 𝑡 𝑡 ∈ 𝑟 𝑎𝑛𝑑 𝑝(𝑡)}
▸ Operators: =, ≠, >, ≥, <, ≤, ∧ 𝑎𝑛𝑑 , ∨ 𝑜𝑟 , ¬ (𝑛𝑜𝑡)
7
A B C D
p p 10 70
p q 50 70
q q 12 30
q q 25 15
Relation, r
SELECT *
FROM r
WHERE A=B AND D>20
𝜎𝐴=𝐵 ∧ 𝐷>20(𝑟)
A B C D
p p 10 70
q q 12 30
output
MySQL

select Operation
select Operation
▸ Notation: 𝜎𝑝 𝑟
▸ 𝑝 is called the selection predicate
▸ Defined as: 𝜎𝑝 𝑟 = 𝑡 𝑡 ∈ 𝑟 𝑎𝑛𝑑 𝑝(𝑡)}
▸ Operators: =, ≠, >, ≥, <, ≤, ∧ 𝑎𝑛𝑑 , ∨ 𝑜𝑟 , ¬ (𝑛𝑜𝑡)
8
A B C D
p p 10 70
p q 50 70
q q 12 30
q q 25 15
Relation, r
SELECT A, C, D
FROM r
WHERE A=B AND D>20
ς𝐴,𝐶,𝐷 (𝜎𝐴=𝐵 ∧ 𝐷>20(𝑟))
A C D
p 10 70
q 12 30
MySQL
output

rename Operation
rename Operation
▸ Notation: 𝜌𝑋(𝐴1,𝐴2,…,𝐴𝑛) 𝐸
▸ It returns the result of expression 𝐸 under the name 𝑋, and with the attributes renamed to 𝐴1, 𝐴2, … , 𝐴𝑛
9
A B C D
p p 10 70
p q 50 70
q q 12 30
q q 25 15
Relation, r
SELECT A AS P,
C AS Q,
D AS R
FROM r AS r1
WHERE A=B AND D>20
𝜌𝑟1(𝑃,𝑄,𝑅)(ς𝐴,𝐶,𝐷 (𝜎𝐴=𝐵 ∧ 𝐷>20(𝑟)))
P Q R
p 10 70
q 12 30
Relation, r1
MySQL
output

Cartesian-product Operation
Cartesian-product Operation (Cross Join)
▸ Notation: 𝑟 × 𝑠
▸ Defined as: 𝑟 × 𝑠 = 𝑡𝑞 𝑡 ∈ 𝑟 𝑎𝑛𝑑 𝑞 ∈ 𝑠}
▸ Assume that attributes of 𝑟 and 𝑠 are disjoint.
▸ If attributes of 𝑟 and 𝑠 are not disjoint, then renaming must be used.
10
A B
p 1
q 2
Relation, r
SELECT *
FROM r
JOIN
s
𝑟 × 𝑠
A B C D E
p 1 p 10 a
p 1 q 10 a
p 1 q 20 b
p 1 r 10 b
q 2 p 10 a
q 2 q 10 a
q 2 q 20 b
q 2 r 10 b
C D E
p 10 a
q 10 a
q 20 b
r 10 b
Relation, s
MySQL
output

Cartesian-product Operation
Cartesian-product Operation (Cross Join)
▸ Notation: 𝑟 × 𝑠
▸ Defined as: 𝑟 × 𝑠 = 𝑡𝑞 𝑡 ∈ 𝑟 𝑎𝑛𝑑 𝑞 ∈ 𝑠}
▸ Assume that attributes of 𝑟 and 𝑠 are disjoint.
▸ If attributes of 𝑟 and 𝑠 are not disjoint, then renaming must be used.
11
A B
p 1
q 2
Relation, r
SELECT *
FROM r
JOIN
s
WHERE A=C
𝜎𝐴=𝐶(𝑟 × 𝑠)
A B C D E
p 1 p 10 a
p 1 q 10 a
p 1 q 20 b
p 1 r 10 b
q 2 p 10 a
q 2 q 10 a
q 2 q 20 b
q 2 r 10 b
C D E
p 10 a
q 10 a
q 20 b
r 10 b
Relation, s
MySQL
output

Natural Join Operation
Natural Join Operation
▸ Notation: 𝑟 ⋈ 𝑠
▸ Matches all the common column values.
12
A B C D
p 1 p a
q 2 r a
r 4 q b
p 1 r a
s 2 q b
Relation, r
SELECT *
FROM r
NATURAL JOIN
s
𝑟 ⋈ 𝑠
A B C D E
p 1 p a p
p 1 r a p
p 1 p a r
p 1 r a r
s 2 q b s
B D E
1 a p
3 a q
1 a r
2 b s
3 b t
Relation, s
MySQL
output

Theta Join Operation
Theta Join Operation (Inner Join)
▸ Notation: 𝑟 ⋈𝜃 𝑠
▸ Matches the 𝜃 condition.
13
SELECT *
FROM r
JOIN
s
ON r.A=s.C
𝑟 ⋈𝑟.𝐴=𝑠.𝐶 𝑠
A B C D E
p 1 p 10 a
q 2 q 10 a
q 2 q 20 b
A B
p 1
q 2
Relation, r
C D E
p 10 a
q 10 a
q 20 b
r 10 b
Relation, s
MySQL
output

Outer Join Operation
Outer Join Operation
▸ Avoids loss of information.
▸ Computes the join and then adds tuples from one relation that does not match tuples in the other relation to the result of
the join.
▸ Uses null values.
14
loan_number branch_name amount
L-170 Downtown 3000
L-230 Redwood 4000
L-260 Perryridge 1700
Relation, loan
customer_name l_no
Jones L-170
Smith L-230
Hayes L-155
Relation, borrower

Outer Join Operation – Left Outer Join
Left Outer Join Operation
▸ Notation: ⋈
15
L-170 Downtown 3000
L-230 Redwood 4000
Relation, loan
customer_name l_no
Jones L-170
Smith L-230
Hayes L-155
Relation, borrower
⊐
SELECT *
FROM loan
LEFT JOIN
borrower
ON
loan.loan_number=borrower.l_no
𝑙𝑜𝑎𝑛 ⋈(𝑙𝑜𝑎𝑛.𝑙𝑜𝑎𝑛_𝑛𝑢𝑚𝑏𝑒𝑟=𝑏𝑜𝑟𝑟𝑜𝑤𝑒𝑟.𝑙_𝑛𝑜)𝑏𝑜𝑟𝑟𝑜𝑤𝑒𝑟
loan_number branch_name amount customer_name l_no
L-170 Downtown 3000 Jones L-170
L-230 Redwood 4000 Smith L-230
L-260 Perryridge 1700 null null
⊐
MySQL
output

Outer Join Operation – Right Outer Join
Right Outer Join Operation
▸ Notation: ⋈
16
L-170 Downtown 3000
L-230 Redwood 4000
Relation, loan
customer_name l_no
Jones L-170
Smith L-230
Hayes L-155
Relation, borrower
⊏
SELECT *
FROM loan
RIGHT JOIN
borrower
ON
𝑙𝑜𝑎𝑛 ⋈ (𝑙𝑜𝑎𝑛.𝑙𝑜𝑎𝑛_𝑛𝑢𝑚𝑏𝑒𝑟=𝑏𝑜𝑟𝑟𝑜𝑤𝑒𝑟.𝑙_𝑛𝑜) 𝑏𝑜𝑟𝑟𝑜𝑤𝑒𝑟
null null null Hayes L-155
⊏
MySQL
output

Outer Join Operation – Full Outer Join
Full Outer Join Operation
▸ Notation: ⋈
17
L-170 Downtown 3000
L-230 Redwood 4000
Relation, loan
customer_name l_no
Jones L-170
Smith L-230
Hayes L-155
Relation, borrower
⊏
SELECT *
FROM loan
FULL OUTER JOIN
borrower
ON
𝑙𝑜𝑎𝑛 ⋈ (𝑙𝑜𝑎𝑛.𝑙𝑜𝑎𝑛_𝑛𝑢𝑚𝑏𝑒𝑟=𝑏𝑜𝑟𝑟𝑜𝑤𝑒𝑟.𝑙_𝑛𝑜) 𝑏𝑜𝑟𝑟𝑜𝑤𝑒𝑟
L-260 Perryridge 1700 null null
null null null Hayes L-155
⊏
Oracle Supported
⊐
⊐
output

Aggregate Operation
Aggregate Function
▸ Takes a collection of values and returns a single value as a result.
▸ Functions: avg, min, max, sum, count
Aggregate Operation
▸ Notation: 𝐺1, 𝐺2, … , 𝐺𝑛
𝒢𝐹1 𝐴1 ,𝐹2 𝐴2 ,…,𝐹𝑛 𝐴𝑛
(E)
▸ E is any relational-algebra function.
▸ Each 𝐹𝑖 is an aggregate function.
▸ Each 𝐴𝑖 is an attribute name.
18
A B C
p p 10
p q 50
q q 12
q q 25
Relation, r
SELECT SUM(C)
FROM r
𝒢𝑆𝑈𝑀 𝐶 (r)
MySQL
SUM(C)
97
output

Aggregate Operation
19
branch_name account_number balance
Perryridge A-102 400
Perryridge A-201 900
Brighton A-217 750
Brighton A-215 750
Redwood A-222 700
Relation, account
SELECT branch_name,
SUM(balance) AS sum_balance
FROM account
GROUP BY branch_name
𝑏𝑟𝑎𝑛𝑐ℎ_𝑛𝑎𝑚𝑒𝒢𝑆𝑈𝑀 𝑏𝑎𝑙𝑎𝑛𝑐𝑒 𝐴𝑆 𝑠𝑢𝑚_𝑏𝑎𝑙𝑎𝑛𝑐𝑒(account)
MySQL
branch_name sum_balance
Perryridge 1300
Brighton 1500
Redwood 700
output

union Operation
union Operation
▸ Notation: 𝑟 ∪ 𝑠
▸ Defined as: 𝑟 ∪ 𝑠 = 𝑡 𝑡 ∈ 𝑟 𝑜𝑟 𝑡 ∈ 𝑠}
20
A B
p 1
p 2
q 1
Relation, r
SELECT *
FROM r
UNION
SELECT *
FROM s
𝑟 ∪ 𝑠
MySQL
A B
p 2
q 3
Relation, s
A B
p 1
p 2
q 1
q 3
output

set difference Operation
set difference Operation
▸ Notation: 𝑟 − 𝑠
▸ Defined as: 𝑟 − 𝑠 = 𝑡 𝑡 ∈ 𝑟 𝑎𝑛𝑑 𝑡 ∉ 𝑠}
21
A B
p 1
p 2
q 1
Relation, r
SELECT *
FROM r
MINUS
SELECT *
FROM s
𝑟 − 𝑠
ORACLE Supported
A B
p 2
q 3
Relation, s
A B
p 1
q 1
output

set intersection Operation
set intersection Operation
▸ Notation: 𝑟 ∩ 𝑠
▸ Defined as: 𝑟 ∩ 𝑠 = 𝑡 𝑡 ∈ 𝑟 𝑎𝑛𝑑 𝑡 ∈ 𝑠}
22
A B
p 1
p 2
q 1
Relation, r
SELECT *
FROM r
INTERSECT
SELECT *
FROM s
𝑟 ∩ 𝑠
ORACLE Supported
A B
p 2
q 3
Relation, s
A B
p 2
output

Practices
Convert to equivalent Relational Algebra:
SELECT N.D, COUNT(M.A) AS cnt
FROM t1 AS M
JOIN
t2 AS N
ON M.C1=N.C
WHERE N.C>1
GROUP BY N.C, N.D
HAVING cnt>2
23
A B C1 A1
1 2000 2 3
2 2500 1 1
3 2200 2 2
4 2000 2 2
Relation, t1
C D
1 ios
2 web
Relation, t2

Practices
Simulate all the following Relational Algebra Expression. You have to show each and every steps of the simulations:
▸ 𝑡1. 𝐷𝒢𝑐𝑜𝑢𝑛𝑡 𝑡2.𝐷 (𝜎𝑡1.𝐷<𝑡2.𝐷(𝜌𝑡1(𝑟) × 𝜌𝑡2(𝑟)))
▸ 𝜎𝐸>100((𝑟 ⋈ 𝑠) ∪ (𝑟 ⋈ 𝑡))
▸ (𝑠 ⋈ 𝑡) ∩ (𝑠 ⋈ 𝑡)
24
A B C D
100 David Jones 20000
102 Loren Ipsum 40000
103 Chris Stanley 10000
Relation, r
C D E
Jones 20000 101
Stanley 10000 101
Ipsum 30000 103
Relation, s
B E
David 101
Felix 101
Chris 100
Relation, t
⊏
⊐

25
THANKS!
Any questions?
Email : imam@cse.uiu.ac.bd
References:
▪ Database System Concepts by S. Sudarshan, Henry F. Korth, Abraham Silberschatz

DBMS 7 | Relational Query Language

More Related Content

What's hot

Similar to DBMS 7 | Relational Query Language

More from Mohammad Imam Hossain

Recently uploaded

DBMS 7 | Relational Query Language