Unit-2(Chapter-2)

Relational Model
Contents:

Structure of Relational Databases, Relational Algebra, Extended Relational Algebra Operations,

Modification of the database, Views.

A relational database is defined as a group of independent tables which are linked to each other using
some common fields of each related table. This model can be represented as a table with columns and
rows. Each row is known as a tuple. Each table of the column has a name or attribute.
Ex:Employee

Relation: A relation is usually represented as a table, organized into rows and columns. A relationship
consists of multiple records. For example: fig 2.1-Employee relation which contains tuples and
attributes.
Tuple: The rows of a relation that contain the values corresponding to the attributes are called
tuples. For example: in the Employee relation there are 2 tuples.
The value of tuples contains (101, Raju, DVG) etc.
Domain: It contains a set of atomic values that an attribute can take.
Attribute: The smallest unit of data in relational model is an attribute. It contains the name of a
column in a particular table. For example: emp_id, emp_name, emp_address.
Database Schema database schema is the logical design of the database.
Ex: Employee(Emp_id, Emp_name, Emp_address)
Database instance is a snapshot of the data in the database at a given instant in time.
ex: Employee

Schema diagrams pictorially represents database schema along with primary key and foreign key
dependencies. Ex: Schema diagram for bank database.

1 | Dept of BCA
 Unit-2(Chapter-2)

Fundamental of Relational Algebra

Relational algebra consists of a set of operations that take one or two relations as input and produce a
new relation as their result.
There are six fundamental operations in Relational algebra are
Unary operations - select, project and rename.
Binary operations - union, set difference and cartesion product.
Apart from these fundamental operations there are other operations like set intersection, natural join,
division and assignment all these operations are defined in terms of then fundamental operations .
The result of a relational operation itself is a relation .

Select Operation:-
The select operation selects tuples that satisfy a given predicate. Lower case Greek letter sigma (σ) is
used to denote selection operation and the predicate appears as subscript to σ.
Relational algebra queries ex:
1. To select tuples from Employee relation whose address is “cta”.
σ emp_address=”cta” (Employee)
We can use relational operators =,≠,<,≤,>,≥, in the selection predicate to compare. To use more
complex predicates (or) to combine them we also use logical operators AND-(ᴧ), OR-(v), NOT-().

Ex:- to use both conditions in the above examples.

σ emp_address=”cta” ᴧ emp_name=”rama”(Employee)

Project operation:-
The project operation is a unary operation that returns it’s argument relation with only specified
attributes (or) certain attributes left at. Projection is denoted by upper case greek letter ‘PAI’(П),
the attributes we wish to get in the result are listed in the subscript.

П Emp_address(Employee)

Composition of relation operators:

Output of a relational operation is again a relation, input of a relational operation is also a
relation. Since the result of a relational algebra operation is of the same type as it’s input’s.
Relational algebra operations can be composed together into a relational algebra expression.
П emp_name(σ emp_address=”dvg”(Employee)).

Set Operations:
Union operation:
UNION is symbolized by ∪ symbol. It includes all tuples that are in tables A or in B. It also
eliminates duplicate tuples. So, set A UNION set B would be expressed as:
The result <- A ∪ B
For a union operation to be valid, the following conditions must hold –

2 | Dept of BCA
 Unit-2(Chapter-2)

 R and S must be the same number of attributes.

 Attribute domains need to be compatible.
 Duplicate tuples should be automatically removed.

Intersection:
An intersection is defined by the symbol ∩
A∩ B
Defines a relation consisting of a set of all tuple that are in both A and B. However, A and B
must be union-compatible.

Set Difference (-):

– Symbol denotes it. The result of A – B, is a relation which includes all tuples that are in A
but not in B.
 The attribute name of A has to match with the attribute name in B.
 The two-operand relations A and B should be Union compatible.
 It should be defined relation consisting of the tuples that are in relation A, but not in
B.

Cartesian product:
o The Cartesian product is used to combine each row in one table with each row in the
other table. It is also known as a cross product.
o It is denoted by X.
o It is a binary relation which means that it always operates on two relations.

RENAME (ρ) Operation:

The rename operator in relational databases is used to change the name of a relation (table) or
its attributes (columns). It is denoted by rho (ρ). This operation doesn't change the actual data
in the relation, but only the labels used to identify the relation or its attributes.
The rename operation in relational databases is considered a unary operator.
In relational databases, it's possible to rename both the relation name and its attributes.
Let's assume we have the following Employee table:

3 | Dept of BCA
 Unit-2(Chapter-2)

EmpID EmpName EmpSalary

1 John 50000

2 Alice 60000

3 Bob 55000

1. Change both the relation name and attribute names: If you want to rename both the relation
(table) and the attributes (columns), the structure would look like this:
ρS(B1,B2,…,Bn)(R)
Where R is the old relation name, and S is the new name of the relation. The attributes of the
relation are renamed from their original names to B1, B2, ..., Bn.
Example: ρStaff(StaffID, StaffName, Salary)(Employee):
This operation indicates that the relation Employee has been renamed to Staff, and its
attributes have been renamed to StaffID, StaffName, and Salary.
Staff

StaffID StaffName Salary

1 John 50000

2 Alice 60000

3 Bob 55000

2. Change only the relation name: In this case, you only change the relation (table) name,
keeping the attribute names the same:
ρS(R)
Here, R is the old relation name, and S is the new name of the relation, while the attribute
names remain unchanged.
Example: ρStaff(Employee):
The relation name has changed from Employee to Staff, but the attributes (EmpID,
EmpName, EmpSalary) remain unchanged.
Staff

4 | Dept of BCA
 Unit-2(Chapter-2)

EmpID EmpName EmpSalary

1 John 50000

2 Alice 60000

3 Bob 55000

3. Change only the attribute names: This option involves renaming just the attributes
(columns) of the relation:
ρ(B1,B2,…,Bn)(R)
Here, the relation name R stays the same, but the column names are changed to B1, B2, ...,
Bn.
Example: ρ(StaffID, StaffName, Salary)(Employee):
The relation name Employee remains, but the column names are updated to StaffID,
StaffName, and Salary.
Employee

StaffID StaffName Salary

1 John 50000

2 Alice 60000

3 Bob 55000

Division Operation:
The Core Idea: "For All" Queries
The division operator is used to answer queries that involve universal quantification. In
simple, these are queries that use the phrase "for all" or "has every".
Classic Example Questions:
• "Find students who have taken all courses offered by the department."

5 | Dept of BCA
 Unit-2(Chapter-2)

• "Find suppliers who supply all parts needed for a specific product."
• "Find employees who have every required skill listed in a job profile."

Notation and Prerequisites

• Notation: R ÷ S
• Input:
o R is the Dividend relation. It typically has two sets of attributes, let's call them X and Y.
o S is the Divisor relation. It has a set of attributes Y that is a subset of R's attributes.
• Output: A new relation with a schema containing only the attributes X .
Prerequisite: The set of attributes in the divisor S must be a subset of the attributes in the
dividend R.
• If R(A, B, C) and S(C), then S's attributes are a subset of R's. This is valid.
• If R(A, B) and S(C), this is invalid for division.

Example:
Relation R (Student, Course)
Student Course
A DBMS
A OS
B DBMS
B OS
B CN
C DBMS

Relation S (Course)
Course
DBMS
OS

Division: R ÷ S
Meaning: Find students who have taken ALL courses in S (DBMS and OS)

6 | Dept of BCA
 Unit-2(Chapter-2)

Check each student:

* A → DBMS, OS ✅ (has both)

* B → DBMS, OS, CN ✅ (has both)

* C → DBMS ❌ (missing OS)

Result:
Student
A
B

Another Example:
Relation R (Employee, Project, Skill)
Employee Project Skill
E1 P1 Java
E1 P2 Java
E1 P1 SQL
E2 P1 Java
E2 P2 Java
E2 P1 SQL
E2 P2 SQL
E3 P1 Java

Relation S (Project, Skill)

Project Skill
P1 Java
P2 Java

Operation: R ÷ S
Step 1: Understand attributes
* R has: (Employee, Project, Skill)

7 | Dept of BCA
 Unit-2(Chapter-2)

* S has: (Project, Skill)

S ⊆ R ✔️ valid for division

Step 2: What will be the result attributes?

Result = attributes in R not in S
So result will be:
(Employee)

Step 3: Meaning of division

Find employees who are associated with ALL (Project, Skill) pairs in S
So each employee must have:
* (P1, Java)
* (P2, Java)
Step-by-step checking
Employee E1:

* (P1, Java) ✅

* (P2, Java) ✅✔ Included

Employee E2:

* (P1, Java) ✅

* (P2, Java) ✅✔ Included

Employee E3:

* (P1, Java) ✅

* (P2, Java) ❌✘ Not included

Final Result
Employee
E1
E2
* Division checks all combinations together, not separately

8 | Dept of BCA
 Unit-2(Chapter-2)

Join Operations:
A Join operation combines related tuples from different relations, if and only if a given join
condition is satisfied. It is denoted by ⋈.
Types of Join operations:

Join Type Symbol

INNER JOIN ⋈
EQUI JOIN ⋈ (=)
NATURAL JOIN ⋈
LEFT OUTER JOIN ⟕
RIGHT OUTER JOIN ⟖
FULL OUTER JOIN ⟗

Example Tables
Employees

emp_id name dept_id

1 Alice 10

2 Bob 20

3 Charlie 30

4 David NULL

Departments

dept_id dept_name
10 HR
20 IT
40 Finance

9 | Dept of BCA
 Unit-2(Chapter-2)

NATURAL JOIN:
Natural Join:
o A natural join is the set of tuples of all combinations in R and S that are equal on their
common attribute names.
o It is denoted by ⋈.
It automatically joins two tables based on columns with the same name.
In the example, DBMS automatically finds common column (dept_id).
Employees ⋈ Departments
Result
emp_id name dept_id dept_name
1 Alice 10 HR
2 Bob 20 IT

 Only matching rows are returned (like INNER JOIN)

 Automatically detects columns based on same column names
 No need to write condition

INNER JOIN:
Returns only matching rows from both tables.
User has to specify the common columns manually.

Employees ⋈ Employees.dept_id = Departments.dept_id Departments

Result
emp_id name dept_id dept_name
1 Alice 10 HR
2 Bob 20 IT

LEFT JOIN (LEFT OUTER JOIN):

o Left outer join contains the set of tuples of all combinations in R and S that are equal
on their common attribute names.
o In the left outer join, tuples in R have no matching tuples in S.
o It is denoted by ⟕.
Returns all rows from left table + matching rows from right table.
SELECT name, dept_name
FROM Employees
LEFT JOIN Departments
ON Employees.dept_id = Departments.dept_id;
Employees ⟕ Departments
Result

10 | Dept of BCA
 Unit-2(Chapter-2)

name dept_name

Alice HR

Bob IT

Charlie NULL

David NULL

Keeps all employees, even if no department exists.

emp_id name dept_id dept_name

1 Alice 10 HR

2 Bob 20 IT

3 Charlie 30 NULL

4 David NULL NULL

RIGHT JOIN (RIGHT OUTER JOIN):

o Right outer join contains the set of tuples of all combinations in R and S that are equal
on their common attribute names.
o In right outer join, tuples in S have no matching tuples in R.
o It is denoted by ⟖.
Returns all rows from right table + matching rows from left table.
SELECT name, dept_name
FROM Employees
RIGHT JOIN Departments
ON Employees.dept_id = Departments.dept_id;
Employees ⟖ Departments

emp_id name dept_id dept_name

1 Alice 10 HR

2 Bob 20 IT

NULL NULL NULL Finance

Result

11 | Dept of BCA
 Unit-2(Chapter-2)

name dept_name

Alice HR

Bob IT

NULL Finance

Keeps all departments, even if no employee belongs to them.

FULL JOIN (FULL OUTER JOIN):

o Full outer join is like a left or right join except that it contains all rows from both
tables.
o In full outer join, tuples in R that have no matching tuples in S and tuples in S that
have no matching tuples in R in their common attribute name.
o It is denoted by ⟗.
Returns all rows from both tables, matched where possible. It combines LEFT + RIGHT
JOIN.
SELECT name, dept_name
FROM Employees
FULL JOIN Departments
ON Employees.dept_id = Departments.dept_id;
Employees ⟗ Departments

emp_id name dept_id dept_name

1 Alice 10 HR

2 Bob 20 IT

3 Charlie 30 NULL

4 David NULL NULL

NULL NULL NULL Finance

Result

name dept_name

Alice HR

Bob IT

12 | Dept of BCA
 Unit-2(Chapter-2)

name dept_name

Charlie NULL

David NULL

NULL Finance

EQUI JOIN:
It is also known as an inner join. It is the most common join. It is based on matched data as
per the equality condition. The equi join uses the comparison operator(=).
An EQUI JOIN is a type of join where the condition uses only equality (=) operator.
 It is usually written using INNER JOIN + ON
 The join condition is explicitly defined using =
 Uses ONLY equal (=) operator → hence EQUI JOIN
SELECT [Link], Departments.dept_name
FROM Employees
JOIN Departments
ON Employees.dept_id = Departments.dept_id;
Employees ⋈ Employees.dept_id = Departments.dept_id Departments
Result

name dept_name

Alice HR

Bob IT

Extended Relational Algebra Operations:

Extended Relational Algebra increases power over basic relational algebra by adding new
features and capabilities.

Generalized Projection:
Normal Projection only projects specific columns from a relation.
Generalized Projection extends this by allowing arithmetic operations on the projected
columns.

13 | Dept of BCA
 Unit-2(Chapter-2)

⇒ E is any relational-algebra expression.

⇒ Each F1, F2, ..., Fn is an arithmetic expression involving constants and attributes in the
schema of E.
Ex:

Aggregate Functions and Operations:

Aggregate Functions take a collection of values and return a single value as a result.
Common aggregate functions include:
⇒ avg: Average value
⇒ min: Minimum value
⇒ max: Maximum value
⇒ sum: Sum of values
⇒ count: Number of values
⇒ These operations can be applied to the entire relation or to certain groups of tuples. They
ignore NULL values, except for count.

⇒ E is any relational-algebra expression.

⇒ G1, G2, ..., Gn is a list of attributes on which to group (can be empty).
⇒ Each Fi is an aggregate function.
⇒ Each Ai is an attribute name.

14 | Dept of BCA
 Unit-2(Chapter-2)

Modification of the Database:

The content of the database may be modified using the following operations:
[Link]
[Link]
[Link]
All these operations are expressed using the assignment operator ( ←).

Deletion:
A delete request is expressed similarly to a query, except instead of displaying tuples to the
user, the selected tuples are removed from the database.
In deletion, tuples are deleted from the relation.
You can only delete whole tuples; you cannot delete values from specific attributes.
A deletion is expressed in relational algebra by: r ← r - E
Where r is a relation and E is a relational algebra expression.

Insertion:
Similar to deletion, but uses the union operator (∪) instead of the difference operator (-).
In insertion, tuples are added to the relation.
To insert data into a relation, you can either:
Specify a tuple to be inserted, or
Write a query whose result is a set of tuples to be inserted.
An insertion is expressed in relational algebra by: r ← r ∪ E
Where r is a relation and E is a relational algebra expression.
The insertion of a single tuple is expressed by letting E be a constant relation containing one
tuple.

15 | Dept of BCA
 Unit-2(Chapter-2)

Updating:
Updating is a mechanism to change a value in a tuple without changing all values in the tuple.
Use the generalized projection operator to perform this task.

Each Fi can be either:

The attribute of r, or
An expression involving only constants and the attributes of r that gives the new value for the
attribute.

Views in Relational Algebra:

In some cases, it is not desirable for all users to see the entire logical model, i.e., all the actual
relations stored in the database.
Consider a person who needs to know a customer's loan number but has no need to see the
loan amount. This person should see a relation described, in the relational algebra, by a view.

16 | Dept of BCA
 Unit-2(Chapter-2)

Any relation that is made visible to a user as a "virtual relation" is called a view.
Views provide limited access to the database and present a tailored schema.
Views do not contain data of their own.
Views do not exist physically.

Uses of Views:
They help in query processing, such as simplifying commands for the user and storing
complex queries.
They restrict access to the database.
They hide data complexity.
Database modifications (like insert, delete, and update operations) on views affect the actual
relations in the database upon which the view is based (also true in SQL).
Views are stored in the data dictionary in the table called USER_VIEWS.

View Definition
A view is defined using the CREATE VIEW statement.
A view can be deleted using the DROP VIEW statement.

17 | Dept of BCA