23ACS12-Data Base Management

Systems
Program & Semester: [Link] & IVSEM
AcademicYear:2025- 26

UNIT II
Module 2: Relational Data Model and
Language
Relational Data Model-Introduction

• Relational data model is the primary data model, which is used

widely around the world for data storage and processing.
• This model is simple and it has all the properties and
capabilities required to process data with storage efficiency.
• The main construct for representing data in the relational
model is a relation.
• A Relation mainly contains two things
 Relation Schema
 Relation Instance
Relation Schema

• A relation schema specifies the relation’s name, the name of each field
(or column, or attribute), and the domain of each field.
• A domain is referred to in a relation schema by the domain name and has a
set of associated values.
• A relation schema R, denoted by R(A1, A2, ...,An), is made up of a relation
name R and a list of attributes, A1, A2, ..., An. Each attribute Ai is the name of
a role played by some domain D in the relation schema R. D is called the
domain of Ai and is denoted by dom(Ai).

Example:
Students(sid: string, name: string, login: string, age: integer, gpa: real) The
field named sid has a domain named string.
The set of values associated with domain string is the set of all character
strings..
Relation Instance

• A relation instance can be thought of as a table in which each tuple is a row,

and all rows have the same number of fields. (The term relation instance is
often abbreviated to just relation)
Key Concepts in the Relational Model
• The Relational Model is a way of structuring and managing data using tables.
The fundamental components of this model include domains, attributes, tuples,
and relations.
Integrity Constraints
Set of rules or conditions specified on a database schema and restricts the data
that can be stored in an instance of the database. They ensures that the data
insertion, updating and other processes have to be performed in such way that
data integrity is not affected.
Types of integrity
constraints
 Key constraints
 Domain constraints
 Referential integrity constraints
 Entity Integrity Constraints
Domain constraints

Domain constraints can be defined as the definition of a valid set of values for an
attribute. The data type of domain includes string, character, integer, time, date,
currency, etc.

The value of the attribute must be available in the corresponding domain .

Entity integrity constraints
⚫ The entity integrity constraint states that primary key value can't
be null.
⚫ This is because the primary key value is used to identify individual
rows in relation and if the primary key has a null value, then we
can't identify those rows.
⚫ A table can contain a null value other than the primary key field.
Referential Integrity Constraints
⚫ A referential integrity constraint is specified between two tables.
⚫ In the Referential integrity constraints, if a foreign key in Table 1 refers to
the Primary Key of Table 2, then every value of the Foreign Key in Table 1
must be available in Table 2 or NULL.
Key constraints
⚫ Keys are the entity set that is used to identify an entity within its
entity set uniquely.
⚫ An entity set can have multiple keys, but out of which one key will
be the primary key.
⚫ A primary key can contain a unique value in the relational table.
Relational Algebra
Relational Algebra is procedural query language, which takes Relation as input
and generate relation as output. It uses operators to perform queries. An operator
can be either unary or binary.
Relational algebra mainly provides theoretical foundation for relational databases
and SQL.
Relational algebra is performed recursively on a relation and intermediate results
are also considered relations.
We can divide the operations in two categories:
1. Basic Operations
2. Derived Operations
Basic/Fundamental Operations:
1. Select (σ)
2. Project (∏)
3. Union (𝖴)
4. Set Difference (-)
5. Cartesian product (X)
6. Rename (ρ)
Derived Operations
Natural Join (⋈)
Left, Right, Full outer join (𝔴, ⟖, 𝔴)
1.
2.
3. Intersection (∩)

Select Operator (σ)

Select Operator is denoted by sigma (σ) and it is used to find the tuples (or rows) in
a relation (or table) which satisfy the given condition.

If you understand little bit of SQL then you can think of it as a where clause in
SQL, which is used for the same purpose.
Syntax
σ Condition/Predicate (Relation/Table name)
Customer_Id Customer_Nam Customer_City
e
C10100 Staeve Agra
C10111 Raghu Agra
C10115 Chaitra Noida
C10117 Ajeet Delhi
C10118 Carl Delhi
Example: Find the customers who belongs to Agra
σ Customer_City="Agra" (CUSTOMER)

Customer_Id Customer_Nam Customer_City

e
C10100 Steve Agra
C10111 Raghu Agra

Project Operator (∏)

Project operator is denoted by ∏ symbol and it is used to select desired columns
(or attributes) from a table (or relation).

Project operator in relational algebra is similar to the Select statement in SQL.

Syntax

∏ column_name1, column_name2, ...., column_nameN (table_name)

 In this example, we have a table CUSTOMER with three columns

Customer_Id Customer_Nam Customer_City

e
C10100 Staeve Agra
C10111 Raghu Agra
C10115 Chaitra Noida
C10117 Ajeet Delhi
C10118 Carl Delhi

We want to fetch only two columns of the table, which we can do with the help of
Project Operator ∏.

∏ Customer_Id, Customer_Name (CUSTOMER)

Customer_Id Customer_Nam
e
C10100 Staeve
C10111 Raghu
C10115 Chaitra
C10117 Ajeet
C10118 Carl
Union Operator (𝖴)
Union operator is denoted by 𝖴 symbol and it is used to select all the rows (tuples)
from two tables (relations).

Lets say we have two relations R1 and R2 both have same columns and we want
to select all the tuples(rows) from these relations then we can apply the union
operator on these relations.

Note: The rows (tuples) that are present in both the tables will only appear once in
the union set. In short you can say that there are no duplicates present after the

r 𝖴 s = { t | t ∈ r or t ∈ s}
union operation.

For a union operation to be valid, the following conditions must hold

 r, and s must have the same number of attributes.
 Attribute domains must be compatible.
 Duplicate tuples are automatically eliminated.
Syntax
table_name1 𝖴 table_name2
Course_Id Student_Name Student_Id
C101 Aditya S901
C104 Aditya S901
C106 Steve S911
C109 Paul S921
C115 Lucy S931

Student_Id Student_NameStudent_Age
S901 Aditya 19
S911 Steve 18
S921 Paul 19
S931 Lucy 17
S941 Carl 16
S951 Rick 18
Example: ∏ Student_Name (COURSE) 𝖴 ∏ Student_Name (STUDENT)

Student_Nam
e Adity
a
Carl
Paul
Lucy
Rick
Stev

You can see there are no duplicate names present in the output
even though we had few common names in both the tables
Set Difference (-)
Set Difference is denoted by – symbol. Lets say we have two relations R1 and
R2 and we want to select all those tuples(rows) that are present in Relation R1
but not present in Relation R2, this can be done using Set difference R1 – R2.
Syntax
table_name1 - table_name2
Example
Select those student names that are present in STUDENT table but not present in
COURSE table

∏ Student_Name (STUDENT) - ∏ Student_Name (COURSE)

Student_Name
Car
l
Ric
Cartesian product (X)
Cartesian Product is denoted by X symbol.
Lets say we have two relations R1 and R2 then the cartesian product of these two
relations (R1 X R2) would combine each tuple of first relation R1 with the each
tuple of second relation R2.
Syntax
R1 X R2
R1
A B
AA 100
BB 200
CC 300
R2

X Y
XX 99
YY 11
ZZ 101
R1 X R2

A B X Y
AA 100 XX 99
AA 100 YY 11
AA 100 ZZ 101
BB 200 XX 99
BB 200 YY 11
BB 200 ZZ 101
CC 300 XX 99
CC 300 YY 11
CC 300 ZZ 101
Rename (ρ)
Rename (ρ) operation can be used to rename a relation or an attribute of a
relation.

Syntax
ρ(new_relation_name, old_relation_name)

Example
Customer_Id Customer_Nam Customer_Cit
e y
C10100 Steve Agra
C10111 Raghu Agra
C10115 Chaitanya Noida
C10117 Ajeet Delhi
C10118 Carl Delhi
ρ(CUST_NAMES, ∏Customer_Name (CUSTOMER))
CUST_NAME
S Steve
Raghu
Chaitan
ya Ajeet
Carl
 Derived Operators
Intersection Operator (∩)
Intersection operator is denoted by ∩ symbol and it is used to select common
rows (tuples) from two tables (relations).
Lets say we have two relations R1 and R2 both have same columns and we want
to select all those tuples(rows) that are present in both the relations, then in that
case we can apply intersection operation on these two relations R1 ∩ R2.
Course_Id Student_Name Student_Id
C10 Adity S90
1 a 1
C10 Adity S90
4 a 1
C10 Stev S91
6 e 1
C10 Paul S92
9 Lucy 1
C11 S93
5 1
Student_Id Student_Name Student_Age
S901 Aditya 19
S911 Steve 18
S921 Paul 19
S931 Lucy 17
S941 Carl 16
S951 Rick 18
Derived Operators
Intersection Operator (∩)
Intersection operator is denoted by ∩ symbol and it is used to
select common rows (tuples) from two tables (relations).
Lets say we have two relations R1 and R2 both have same columns and
we want to select all those tuples(rows) that are present in both the
relations, then in that case we can apply intersection operation on these
two relations R1 ∩ R2.

Syntax
table_name1 ∩ table_name2
Example
∏ Student_Name (COURSE) ∩ ∏ Student_Name (STUDENT)

Student_Name
Adity
a
Stev
e
Paul
JOINS
• In Relational Algebra, a Join operation combines tuples from two
relations (tables) based on a common attribute. It is one of the
most commonly used operations for querying relational database
Types of Joins in Relational Algebra
1. Theta Join (𝔚 condition)
2. Equi Join (𝔚)
3. Natural Join (𝔚)
4. Outer Joins
1. Left Outer Join (𝔴)
2. Right Outer Join (⟖)

5. Semi Join (⋉, ⋊)

3. Full Outer Join (𝔴)

6. Division (÷) (Not a join but related to query processing)

Example: Employee Department
Queries on Sailors-Boats-Reserves Tables in Relational Algebra

(Q1) Find the names of sailors who have reserved boat 103.
This query can be written as follows:
Queries on Sailors-Boats-Reserves Tables in Relational Algebra

(Q2) Find the names of sailors who have reserved a red boat
Queries on Sailors-Boats-Reserves Tables in Relational Algebra

(Q3) Find the colors of boats reserved by Lubber.

Queries on Sailors-Boats-Reserves Tables in Relational Algebra

(Q4) Find the names of sailors who have reserved a red or a green boat.
Queries on Sailors-Boats-Reserves Tables in Relational Algebra
(Q5) Find the sids of sailors with age over 20 who have not reserved a red boat.
Queries on Sailors-Boats-Reserves Tables in Relational Algebra
(Q6) Find the names of sailors who have reserved at least two boats
Relational Calculus
 Relational calculus is a non-procedural query language in
DBMS that specifies what to retrieve rather than how to retrieve
it.
 Unlike relational algebra, which uses operators to manipulate
relations, relational calculus uses logical predicates to
describe the desired results.

Types of Relational Calculus

There are two types of relational calculus:

 Tuple Relational Calculus (TRC)

 Domain Relational Calculus (DRC)
 Relational Calculus
1. Tuple Relational Calculus (TRC)

Tuple Relational Calculus uses tuples (row-based approach) to define queries. A

query in TRC returns a set of tuples that satisfy a given condition.

{t ∣ P(t)} Where:
General Syntax:

t is a tuple variable.
P(t) is a predicate (a logical condition that must be satisfied).
Example-1 : Find names of sailors

{[Link] ∣ t∈Sailors 𝖠 [Link]>30}

who are older than 30.

t is a tuple variable referring to the Sailors table.

The query returns sname of sailors where age > 30.

{[Link] ∣ t∈Sailors 𝖠 ∃r∈Reserves([Link]=[Link]𝖠[Link]=101)}

Example 2: Find sailors who have reserved boat with bid = 101.

.
Relational Calculus
2. Domain Relational Calculus (DRC)
Domain Relational Calculus uses domain variables (column-based approach) to
define queries.

{<d1,d2,...,dn> ∣ P(d1,d2,...,dn)}
General Syntax:

Where:
d1,d2,...,dn are domain variables representing attributes (columns).
P is a predicate (a condition that must be satisfied).

Ans: {sname ∣ ∃sid,rating,age(Sailors(sid,sname,rating,age)𝖠age>30).

Example-1 : Find names of sailors who are older than 30.

Ans: {sname ∣ ∃sid,bid,day(Sailors(sid,sname,rating,age)𝖠Reserves(sid,bid,day))}

Example 2: Find the names of sailors who have reserved a boat.
Relational Calculus- Example Queries
Query 1: Find the names of sailors who have
reserved a red boat.
Tuple Relational Calculus (TRC):
{t∣∃s∈Sailors,∃r∈Reserves,∃b∈Boats([Link]=[Link]𝖠r.b
id=[Link]𝖠[Link]=′red′𝖠[Link]=[Link])}

Domain Relational Calculus (DRC):

{⟨sn⟩∣∃sid,bid(Sailors(sid,sn,_,_)𝖠Reserves(sid,bid
,_)𝖠Boats(bid,_,′red′))}

Query 2: Find the names of sailors who have not

reserved any boat.
Tuple Relational Calculus (TRC):
{t∣∃s∈Sailors(∀r∈Reserves([Link]≠[Link])𝖠[Link]=s.s
name)}
Domain Relational Calculus (DRC):
{⟨sn⟩∣∃sid(Sailors(sid,sn,_,_)𝖠∀bid,day(¬Reserves(s
id,bid,day)))}
Relational Calculus- Example Queries
Query 3: Find the names of sailors who have reserved a
boat on a specific day (e.g., '2023-10-01').
Tuple Relational Calculus (TRC):
{t∣∃s∈Sailors,∃r∈Reserves,∃b∈Boats([Link]=[Link]𝖠[Link]=
b. bid𝖠[Link]=′2023−10−01′𝖠[Link]=[Link])}

Domain Relational Calculus (DRC):

{⟨sn⟩∣∃sid,bid(Sailors(sid,sn,_,_)𝖠Reserves(sid,bid,
′202 3−10−01′))}
{⟨sn⟩∣∃sid,bid(Sailors(sid,sn,_,_)𝖠Reserves(s id,bid,
′2023−10−01′))}

Query 4: Find the names of sailors who have reserved

at least two boats.
Tuple Relational Calculus (TRC):

𝖠[Link]!=[Link] 𝖠 [Link]=[Link])}
{t∣∃s∈Sailors,∃r1,r2∈Reserves([Link]=[Link]𝖠[Link]=[Link]

Domain Relational Calculus (DRC):

{⟨sn⟩∣∃sid,bid1,bid2(Sailors(sid,sn,_,_)𝖠Reserves(sid,bi
d1,_)𝖠Reserves(sid,bid2,_)𝖠bid1!=bid2)}