PREPARED BY: MS. PRIYANKA KAMBLE RELATIONAL MODEL AND RELATIONAL ALGEBRA

1

Relational Model

&

Relational Algebra

Module 3

Relational Model

• The Relational Model is a data model that represents data as a collection of relations (tables), where each relation consists of tuples (rows) and attributes (columns), and every piece of data is represented in a tabular form.

​

​

PREPARED BY: MS. PRIYANKA KAMBLE RELATIONAL MODEL AND RELATIONAL ALGEBRA

2

Relational Model

In relational model

• The term 𝒓𝒆𝒍𝒂𝒕𝒊𝒐𝒏 is used to refer to a table
• The term 𝒕𝒖𝒑𝒍𝒆 is used to refer to a row.
• The term 𝒂𝒕𝒕𝒓𝒊𝒃𝒖𝒕𝒆refers to a column of a table.

​

​

PREPARED BY: MS. PRIYANKA KAMBLE RELATIONAL MODEL AND RELATIONAL ALGEBRA

3

Terminology

• Relations (Tables): It is the basic structure in which data is stored. Each relation is made up of rows and columns.
• Attribute: Each relation is defined in terms of some properties, each of which is known as an attribute. or Each column shows an attribute of the data. 
• Tuple: Each row of a relation is known as a tuple.        
• Degree (Arity): The degree of relation refers to total number of attribute a relation has. It is also known as Arity.� - Example: The degree of this table is 4 because it has 4 columns: StudentID, Name, Age and Course.
• Primary Key:  The primary key is an attribute or a set of attributes that help to uniquely identify the tuples(records) in the relational table.
• NULL values: Values of some attribute for some tuples may be unknown, missing, or undefined which are represented by NULL. 

PREPARED BY: MS. PRIYANKA KAMBLE RELATIONAL MODEL AND RELATIONAL ALGEBRA

4

𝑫𝒂𝒕𝒂𝒃𝒂𝒔𝒆 𝑺𝒄𝒉𝒆𝒎𝒂

​

• 𝑫𝒂𝒕𝒂𝒃𝒂𝒔𝒆 𝑺𝒄𝒉𝒆𝒎𝒂 is the logical design of the database.
• 𝑫𝒂𝒕𝒂𝒃𝒂𝒔𝒆 𝑰𝒏𝒔𝒕𝒂𝒏𝒄𝒆 is a snapshot of the data in the database at a given instant in time.
• A 𝒓𝒆𝒍𝒂𝒕𝒊𝒐𝒏 𝒔𝒄𝒉𝒆𝒎𝒂 corresponds to the programming-language notion of type definition.
• A relation schema consists of a list of attributes and their corresponding domains.
• A relation instance 𝑟 defined over schema 𝑅 is denoted by 𝑟(𝑅).
• The current values a relation are specified by a table.
• The schema of a relation does not generally change.

​

PREPARED BY: MS. PRIYANKA KAMBLE RELATIONAL MODEL AND RELATIONAL ALGEBRA

5

𝑫𝒂𝒕𝒂𝒃𝒂𝒔𝒆 𝑺𝒄𝒉𝒆𝒎𝒂

• Consider 𝑡𝑒𝑎𝑐ℎ𝑒𝑟 relation given below. It is also known as 𝒊𝒏𝒔𝒕𝒂𝒏𝒄𝒆.

​

PREPARED BY: MS. PRIYANKA KAMBLE RELATIONAL MODEL AND RELATIONAL ALGEBRA

6

The 𝒔𝒄𝒉𝒆𝒎𝒂 for 𝑒𝑚𝑝𝑙𝑜𝑦𝑒𝑒 relation is

𝑡𝑒𝑎𝑐ℎ𝑒𝑟(𝐼𝐷, 𝑛𝑎𝑚𝑒, 𝑑𝑒𝑝𝑡_𝑛𝑎𝑚𝑒, 𝑐𝑖𝑡𝑦, 𝑠𝑎𝑙𝑎𝑟𝑦)

Relational Integrity Constraints

• Constraints are conditions that must hold on all valid relation states.
• There are Four main types of constraints in the relational model:

​

​

​

​

​

• Another implicit constraint is the domain constraint
• Every value in a tuple must be from the domain of its attribute (or it could be null, if allowed for that attribute).

PREPARED BY: MS. PRIYANKA KAMBLE RELATIONAL MODEL AND RELATIONAL ALGEBRA

7

Relational Integrity Constraints

A. Domain Constraint

• Every domain must contain atomic values(smallest indivisible units) which means composite and multi-valued attributes are not allowed.
• We perform a datatype check here, which means when we assign a data type to a column we limit the values that it can contain.
• Eg. If we assign the datatype of attribute age as int, we can't give it values other than int datatype.

​

​

​

​

PREPARED BY: MS. PRIYANKA KAMBLE RELATIONAL MODEL AND RELATIONAL ALGEBRA

8

Relational Integrity Constraints

B. Key Constraint

• These are called uniqueness constraints since it ensures that every tuple in the relation should be unique.
• A relation can have multiple keys or candidate keys(minimal superkey), out of which we choose one of the keys as the primary key.
• Don’t have any restriction on choosing the primary key out of candidate keys, but it is suggested to go with the candidate key with less number of attributes.
• Null values are not allowed in the primary key.

​

​

​

PREPARED BY: MS. PRIYANKA KAMBLE RELATIONAL MODEL AND RELATIONAL ALGEBRA

9

Relational Integrity Constraints

C. Entity Integrity Constraint

• Entity Integrity constraints say that no primary key can take a NULL value, since using the primary key we identify each tuple uniquely in a  relation.

​

​

​

PREPARED BY: MS. PRIYANKA KAMBLE RELATIONAL MODEL AND RELATIONAL ALGEBRA

10

Relational Integrity Constraints

D. Referential Integrity Constraint

• The Referential integrity constraint is specified between two relations or tables and used to maintain the consistency among the tuples in two relations.
• This constraint is enforced through a foreign key, when an attribute in the foreign key of relation R1 has the same domain(s) as the primary key of relation R2, then the foreign key of R1 is said to reference or refer to the primary key of relation R2.
• The values of the foreign key in a tuple of relation R1 can either take the values of the primary key for some tuple in relation R2, or can take NULL values, but can't be empty.

​

​

​

PREPARED BY: MS. PRIYANKA KAMBLE RELATIONAL MODEL AND RELATIONAL ALGEBRA

11

Relational Integrity Constraints

• How to Display a Relational Database Schema and It’s Constraints?
• Each relation schema can be displayed as a row of attribute names.
• The name of the relation is written above the attribute names.
• The primary key attribute (or attributes) will be underlined.
• A foreign key (referential integrity) constraints is displayed as a directed arc (arrow) from the foreign key attributes to the referenced table.
• Can also point the primary key of the referenced relation for clarity
• Next slide shows the COMPANY relational schema diagram

​

​

​

PREPARED BY: MS. PRIYANKA KAMBLE RELATIONAL MODEL AND RELATIONAL ALGEBRA

12

Relational Integrity Constraints

PREPARED BY: MS. PRIYANKA KAMBLE RELATIONAL MODEL AND RELATIONAL ALGEBRA

13

Keys

• The values of the attribute values of a tuple must be such that they can
• 𝑢𝑛𝑖𝑞𝑢𝑒𝑙𝑦 𝑖𝑑𝑒𝑛𝑡𝑖𝑓𝑦 the tuple.
• i.e. no two tuples in a relation are allowed to have exactly the same value for all
• attributes.
• Key is a subset of attribute list of a relation that uniquely identifies a tuple.
• Why Keys?
• To identify any tuple in a relation.
• To establish and identify a relationship between relations.
• Enforce identity and integrity in the relationship.

PREPARED BY: MS. PRIYANKA KAMBLE RELATIONAL MODEL AND RELATIONAL ALGEBRA

14

Keys

PREPARED BY: MS. PRIYANKA KAMBLE RELATIONAL MODEL AND RELATIONAL ALGEBRA

15

Keys

PREPARED BY: MS. PRIYANKA KAMBLE RELATIONAL MODEL AND RELATIONAL ALGEBRA

16

• A 𝒔𝒖𝒑𝒆𝒓𝒌𝒆𝒚 is a set of one or more attributes that, taken collectively, allow us to uniquely identify a tuple in the relation.
• Let 𝑅 be the set of attributes in the schema of relation 𝑟 and 𝐾 ⊆ 𝑅.
• 𝑲 is a 𝒔𝒖𝒑𝒆𝒓𝒌𝒆𝒚 of relation 𝒓, if values for 𝑲 are sufficient to identify a unique tuple of each possible relation 𝒓(𝑹).
• E.g. attribute {𝐼𝐷} and {𝐼𝐷, 𝑛𝑎𝑚𝑒} are both are superkeys for 𝑡𝑒𝑎𝑐ℎ𝑒𝑟 relation.

Keys

PREPARED BY: MS. PRIYANKA KAMBLE RELATIONAL MODEL AND RELATIONAL ALGEBRA

17

• Superkey 𝑲 is a 𝒄𝒂𝒏𝒅𝒊𝒅𝒂𝒕𝒆 𝒌𝒆𝒚 of relation 𝒓, if 𝑲 is minimal.
• i.e. superkeys for which no proper subset is a superkey.
• Several distinct sets of attributes can serve as a candidate key.
• E.g. suppose 𝑛𝑎𝑚𝑒 and 𝑑𝑒𝑝𝑎𝑟𝑡𝑚𝑒𝑛𝑡 is sufficient to distinguish among members of the

𝑡𝑒𝑎𝑐ℎ𝑒𝑟 relation.

• Then both {𝐼𝐷} and {𝑛𝑎𝑚𝑒, 𝑑𝑒𝑝𝑡_𝑛𝑎𝑚𝑒} are candidate keys.
• But {𝐼𝐷, 𝑛𝑎𝑚𝑒} does not form a candidate key, since attribute {𝐼𝐷} alone is a candidate

key.

Keys

PREPARED BY: MS. PRIYANKA KAMBLE RELATIONAL MODEL AND RELATIONAL ALGEBRA

18

• A 𝒑𝒓𝒊𝒎𝒂𝒓𝒚 𝒌𝒆𝒚 to denote a candidate key that is chosen by the database designer as the principal means of identifying tuples within a relation.
• Must be chosen with care.
• should be chosen such that its attribute values are never, or very rarely, changed.
• List the primary key attributes of a relation schema before the other attributes.
• Primary key attributes are also underlined.

𝑡𝑒𝑎𝑐ℎ𝑒𝑟 = {𝐼𝐷, 𝑛𝑎𝑚𝑒, 𝑑𝑒𝑝𝑡_𝑛𝑎𝑚𝑒, 𝑐𝑖𝑡𝑦, 𝑠𝑎𝑙𝑎𝑟𝑦}

Keys

PREPARED BY: MS. PRIYANKA KAMBLE RELATIONAL MODEL AND RELATIONAL ALGEBRA

19

• Rules for defining a primary key:
• No two rows can have the same primary key value.
• Every row must have a primary key value.
• The primary key field cannot be null.
• The value of a primary key attribute can never be modified or updated if any foreign key refers to that primary key.

Keys

PREPARED BY: MS. PRIYANKA KAMBLE RELATIONAL MODEL AND RELATIONAL ALGEBRA

20

• A relationship can be established between two relations using 𝒇𝒐𝒓𝒆𝒊𝒈𝒏 𝒌𝒆𝒚.
• Consider, a relation 𝑟₁, may include among its attributes the primary key of an other relation 𝑟₂.
• This attribute is called as a 𝒇𝒐𝒓𝒆𝒊𝒈𝒏 𝒌𝒆𝒚 from 𝑟₁, referencing 𝑟₂.
• The relation 𝑟₁ is called the 𝒓𝒆𝒇𝒆𝒓𝒆𝒏𝒄𝒊𝒏𝒈 𝒓𝒆𝒍𝒂𝒕𝒊𝒐𝒏 of the foreign key dependency.
• The relation 𝑟₂ is called the 𝒓𝒆𝒇𝒆𝒓𝒆𝒏𝒄𝒆𝒅 𝒓𝒆𝒍𝒂𝒕𝒊𝒐𝒏 of the foreign key.

Keys

PREPARED BY: MS. PRIYANKA KAMBLE RELATIONAL MODEL AND RELATIONAL ALGEBRA

21

• Relation 𝑡𝑒𝑎𝑐ℎ𝑒𝑟 references relation 𝑑𝑒𝑝𝑎𝑟𝑡𝑚𝑒𝑛𝑡.
• Attribute 𝑑𝑒𝑝𝑡_𝑛𝑎𝑚𝑒 in 𝑡𝑒𝑎𝑐ℎ𝑒𝑟 is a foreign key from 𝑡𝑒𝑎𝑐ℎ𝑒𝑟, referencing relation 𝑑𝑒𝑝𝑎𝑟𝑡𝑚𝑒𝑛𝑡.
• 𝑑𝑒𝑝𝑡_𝑛𝑎𝑚𝑒 is a primary key of relation 𝑑𝑒𝑝𝑎𝑟𝑡𝑚𝑒𝑛𝑡

Foreign key

𝑡𝑒𝑎𝑐ℎ𝑒𝑟

Primary key of

𝑑𝑒𝑝𝑎𝑟𝑡𝑚𝑒𝑛𝑡

Referencing Relation 𝑟₁

Referenced Relation 𝑟₂

Relational Algebra

PREPARED BY: MS. PRIYANKA KAMBLE RELATIONAL MODEL AND RELATIONAL ALGEBRA

22

• The relational algebra defines a set of operations on relations, paralleling the usual algebraic operations such as addition, subtraction or multiplication, which operate on numbers.
• The relational algebra is a procedural query language.
• It consists of a set of operations that take one or two relations as input and produce a new relation as their result.

Fundamental Relational Algebra

PREPARED BY: MS. PRIYANKA KAMBLE RELATIONAL MODEL AND RELATIONAL ALGEBRA

23

Fundamental Relational Algebra

PREPARED BY: MS. PRIYANKA KAMBLE RELATIONAL MODEL AND RELATIONAL ALGEBRA

24

1. Unary relational Operations
1. SELECT (σ) Opertaion
1. used to filter out rows from a given table based on certain given condition.
2. allows us to retrieve only those rows that match the condition as per condition passed during SQL Query.

​

σ(condition)(Relation)

σ(Salary >= 55000)(Employee)

Fundamental Relational Algebra

PREPARED BY: MS. PRIYANKA KAMBLE RELATIONAL MODEL AND RELATIONAL ALGEBRA

25

1. Unary relational Operations

II. PROJECT (π) Opertaion

• The projection operation is used to retrieve specific columns from a relation, effectively removing other columns.

π(column1, column2, ...)(Relation)

π(Name, Salary)(Employee)

Fundamental Relational Algebra

PREPARED BY: MS. PRIYANKA KAMBLE RELATIONAL MODEL AND RELATIONAL ALGEBRA

26

1. Unary relational Operations

III. RENAME (ρ) Opertaion

• The rename operator in relational databases is used to change the name of a relation (table) or its attributes (columns).

ρ(Workers)(Emp_ID, Emp_Name, Emp_Salary, Emp_Dept)(Employee)

ρ(New_relation)(Attributes_of_relation)

(old_relation)

Fundamental Relational Algebra

PREPARED BY: MS. PRIYANKA KAMBLE RELATIONAL MODEL AND RELATIONAL ALGEBRA

27

Example:

π(Name)(σ(Salary > 55000)(Employee))

Unary relational Operations

PREPARED BY: MS. PRIYANKA KAMBLE RELATIONAL MODEL AND RELATIONAL ALGEBRA

28

Example:

1. Find all employees who work in the 'HR' department.
2. List the names and departments of all employees.
3. Find employees whose salary is greater than 55000.
4. List all unique departments from the Employee table.
5. Rename the Employee relation as Staff.
6. Rename attribute ‘Dept’ to ‘Department’.
7. Find names of employees in 'HR' department only.
8. Rename Employee to Emp, and list their IDs and Salaries.

​

Unary relational Operations

PREPARED BY: MS. PRIYANKA KAMBLE RELATIONAL MODEL AND RELATIONAL ALGEBRA

29

Solution:

Fundamental Relational Algebra

PREPARED BY: MS. PRIYANKA KAMBLE RELATIONAL MODEL AND RELATIONAL ALGEBRA

30

B. SET Theory Operations

I. Union (∪) Operation

The UNION operator is a set operator that combines the result sets of two or more SELECT statements into a single result set.

Rules for applying UNION

1. Both the SELECT statement must have the same number of columns.
2. Columns in the SELECT statement must be in the same order.
3. The Selected columns must have the same data type in the same order as the columns of the first table.

​

​

SET Theory Operations

PREPARED BY: MS. PRIYANKA KAMBLE RELATIONAL MODEL AND RELATIONAL ALGEBRA

31

Freelancer

Permanent

SELECT Name, CTC

FROM freelancer

UNION

SELECT Name, CTC

FROM permanent;

SET Theory Operations

PREPARED BY: MS. PRIYANKA KAMBLE RELATIONAL MODEL AND RELATIONAL ALGEBRA

32

UNION ALL

SET Theory Operations

PREPARED BY: MS. PRIYANKA KAMBLE RELATIONAL MODEL AND RELATIONAL ALGEBRA

33

II. Intersection (∩) Operation

The intersection operation returns only those rows that will be common to both of the SELECT statements.

Key Characteristics of SQL INTERSECT:

• Returns only the common rows between two result sets.
• Ensures uniqueness by automatically removing duplicate rows.
• Requires that both SELECT statements have the same number of columns.
• The data types of corresponding columns in both queries must be compatible.

SET Theory Operations

PREPARED BY: MS. PRIYANKA KAMBLE RELATIONAL MODEL AND RELATIONAL ALGEBRA

34

Freelancer

Permanent

SELECT CTC

FROM Permanent

INTERSECT

SELECT CTC

FROM Freelancer;

SET Theory Operations

PREPARED BY: MS. PRIYANKA KAMBLE RELATIONAL MODEL AND RELATIONAL ALGEBRA

35

III. Set Difference (-) Operation

A binary operation that gives tuples in one relation but not present in another relation.

In simple terms, it returns all the rows from the first relation, which is not present in the second one.

SET Theory Operations

PREPARED BY: MS. PRIYANKA KAMBLE RELATIONAL MODEL AND RELATIONAL ALGEBRA

36

SET Theory Operations

PREPARED BY: MS. PRIYANKA KAMBLE RELATIONAL MODEL AND RELATIONAL ALGEBRA

37

IV. Cartesian Product (X) Operation

• The Cartesian product operation will generate the possible combinations among the tuples from the relations resulting in table containing all the data.
• It combines the information of two or more relations in one single relation.

Color X Size

SET Theory Operations

PREPARED BY: MS. PRIYANKA KAMBLE RELATIONAL MODEL AND RELATIONAL ALGEBRA

38

Example:

Solve for:

1. Union
2. Union ALL
3. Intersection
4. Set Difference
5. Cartesian Product

​

Fundamental Relational Algebra

PREPARED BY: MS. PRIYANKA KAMBLE RELATIONAL MODEL AND RELATIONAL ALGEBRA

39

Binary Operations

PREPARED BY: MS. PRIYANKA KAMBLE RELATIONAL MODEL AND RELATIONAL ALGEBRA

40

C. Binary Operations

I. JOIN (⋈) Operation

• A JOIN clause is used to combine rows from two or more tables, based on a related column between them.
• Join is defined as the combination of a Cartesian Product followed by a selection process.
• Joins are used when a user is trying to extract data from tables which have

one to many or many to many relationships between them.

​

Types of Join

​

​

SQL INNER JOIN

PREPARED BY: MS. PRIYANKA KAMBLE RELATIONAL MODEL AND RELATIONAL ALGEBRA

41

I. JOIN (⋈) Operation

1. Inner Join (⋈) operation

It selects the values present in both the Table performing Inner join.

Inner Join is further classified into

• Theta Join
• Equi Join
• Natural Join

​

SQL INNER JOIN

PREPARED BY: MS. PRIYANKA KAMBLE RELATIONAL MODEL AND RELATIONAL ALGEBRA

42

I. JOIN (⋈) Operation

1. Inner Join (⋈) operation
1. Theta Join (⋈θ)
1. Theta Join is used to join two tables based on some conditions.
2. The condition can be on any attributes of the tables performing Theta join.
3. In conditional join, the join condition can include <, >, <=, >=, ≠ operators in addition to the '=' operator.

​

A ⋈θ B where θ is the condition for join.

• Syntax

SELECT column_name (s)

FROM table1

INNER JOIN table2

ON table1.column_name = table2.column_name;

SQL INNER JOIN

PREPARED BY: MS. PRIYANKA KAMBLE RELATIONAL MODEL AND RELATIONAL ALGEBRA

43

Student Table

StudentCourse Table

SELECT StudentCourse.COURSE_ID, Student.NAME, Student.AGE

FROM Student�INNER JOIN StudentCourse�ON Student.ROLL_NO = StudentCourse.ROLL_NO;

Result Table

SQL INNER JOIN

PREPARED BY: MS. PRIYANKA KAMBLE RELATIONAL MODEL AND RELATIONAL ALGEBRA

44

A ⨝ _(S<T) B

• Syntax

SELECT column_name (s)

FROM table1

INNER JOIN table2

ON table1.column_name = table2.column_name;

table1

table2

• SQL Query

SELECT table1.R,table1.S,table2.T,table2.U

FROM table1

INNER JOIN table2

ON table1. S < table2.T;

SQL INNER JOIN

PREPARED BY: MS. PRIYANKA KAMBLE RELATIONAL MODEL AND RELATIONAL ALGEBRA

45

I. JOIN (⋈) Operation

1. Inner Join (⋈) operation

ii. Equi Join (⋈θ)

• Equi Join is a type of inner join where the join condition uses the equality operator ('=') between columns.
• A ⋈θ B where θ is the condition for join.

A ⨝ _(S=T) B

• SQL Query

SELECT table1.R,table1.S,table2.T,table2.U

FROM table1

INNER JOIN table2

ON table1. S = table2.T;

SQL INNER JOIN

PREPARED BY: MS. PRIYANKA KAMBLE RELATIONAL MODEL AND RELATIONAL ALGEBRA

46

I. JOIN (⋈) Operation

1. Inner Join (⋈) operation

iii. Natural Join (⋈)

• Natural join is a type of inner join in which we do not need any comparison operators.
• In natural join, columns should have the same name and domain.
• There should be at least one common attribute between the two tables.

A ⨝ B

INNER JOIN

• Inner Join with multiple conditions
• One can use multiple conditions in inner join.
• For this, multiple columns should be common in two or more tables.
• Syntax:
• SELECT column_name(s)
• FROM table1
• INNER JOIN table2
• ON table1.column_name = table2.column_name
• AND table1.other_column = table2.other_column;

PREPARED BY: MS. PRIYANKA KAMBLE RELATIONAL MODEL AND RELATIONAL ALGEBRA

47

SQL OUTER JOIN

PREPARED BY: MS. PRIYANKA KAMBLE RELATIONAL MODEL AND RELATIONAL ALGEBRA

48

I. JOIN (⋈) Operation

b) Outer Join (⋈) operation

Outer join is a type of join that retrieves matching as well as non-matching records from related tables.

​

Types of outer join

• Left outer join
• Right outer join
• Full outer join

SQL OUTER JOIN

PREPARED BY: MS. PRIYANKA KAMBLE RELATIONAL MODEL AND RELATIONAL ALGEBRA

49

I. JOIN (⋈) Operation

b) Outer Join (⋈) operation

1. Left Outer Join

It retrieves all records from the left table and retrieves matching records from the right table.

​

Syntax:

SELECT table1.column1,table1.column2,table2.column1,....�FROM table1 �LEFT JOIN table2�ON table1.matching_column = table2.matching_column;

SQL LEFT OUTER JOIN

PREPARED BY: MS. PRIYANKA KAMBLE RELATIONAL MODEL AND RELATIONAL ALGEBRA

50

Student Table

StudentCourse Table

SELECT Student.NAME,StudentCourse.COURSE_ID

FROM Student

LEFT JOIN StudentCourse

ON StudentCourse.ROLL_NO = Student.ROLL_NO;

Result Table

SQL LEFT OUTER JOIN

PREPARED BY: MS. PRIYANKA KAMBLE RELATIONAL MODEL AND RELATIONAL ALGEBRA

51

A ⟕ B

• SQL Query

SELECT table1.Number,table1.Square,table2.Cube

FROM table1

INNER JOIN table2

ON table1. Number = table2.Number;

table1

table2

SQL RIGHT OUTER JOIN

PREPARED BY: MS. PRIYANKA KAMBLE RELATIONAL MODEL AND RELATIONAL ALGEBRA

52

I. JOIN (⋈) Operation

b) Outer Join (⋈) operation

ii. Right Outer Join

retrieves all records from the right table and retrieves matching records from the left table. And for the record which doesn't lies in Left table will be marked as NULL in result Set.

SELECT table1.column1,table1.column2,table2.column1,....�FROM table1 �RIGHT JOIN table2�ON table1.matching_column = table2.matching_column;

SQL RIGHT OUTER JOIN

PREPARED BY: MS. PRIYANKA KAMBLE RELATIONAL MODEL AND RELATIONAL ALGEBRA

53

Student Table

StudentCourse Table

SELECT Student.NAME,StudentCourse.COURSE_ID

FROM Student

RIGHT JOIN StudentCourse

ON StudentCourse.ROLL_NO = Student.ROLL_NO;

Result Table

SQL RIGHT OUTER JOIN

PREPARED BY: MS. PRIYANKA KAMBLE RELATIONAL MODEL AND RELATIONAL ALGEBRA

54

A ⟕ B

• SQL Query

SELECT table1.Number,table1.Square,table2.Cube

FROM table1

RIGHT JOIN table2

ON table1. Number = table2.Number;

table1

table2

SQL FULL OUTER JOIN

PREPARED BY: MS. PRIYANKA KAMBLE RELATIONAL MODEL AND RELATIONAL ALGEBRA

55

I. JOIN (⋈) Operation

b) Outer Join (⋈) operation

ii. Full Outer Join

• FULL JOIN creates the result set by combining the results of both LEFT JOIN and RIGHT JOIN.
• The result set will contain all the rows from both tables.
• For the rows for which there is no matching, the result set will contain NULL values.

SELECT table1.column1,table1.column2,table2.column1,....�FROM table1 �FULL JOIN table2�ON table1.matching_column = table2.matching_column;

SQL FULL OUTER JOIN

PREPARED BY: MS. PRIYANKA KAMBLE RELATIONAL MODEL AND RELATIONAL ALGEBRA

56

Student Table

StudentCourse Table

SELECT Student.NAME,StudentCourse.COURSE_ID

FROM Student

FULL JOIN StudentCourse

ON StudentCourse.ROLL_NO = Student.ROLL_NO;

Result Table

PREPARED BY: MS. PRIYANKA KAMBLE RELATIONAL MODEL AND RELATIONAL ALGEBRA

57

A ⟗ B

• SQL Query

SELECT table1.Number,table1.Square,table2.Cube

FROM table1

FULL JOIN table2

ON table1. Number = table2.Number;

SQL FULL OUTER JOIN

SQL Division Operations

PREPARED BY: MS. PRIYANKA KAMBLE RELATIONAL MODEL AND RELATIONAL ALGEBRA

58

II. Division Operation

The DIVISION operation is a binary relational operation that divides one set of rows into another set of rows based on specified conditions.

​

Practice Examples

PREPARED BY: MS. PRIYANKA KAMBLE RELATIONAL MODEL AND RELATIONAL ALGEBRA

59

Sample Relations:

∙ Employee(EmpID, Name, DeptID, Salary, HireDate)

∙ Department(DeptID, DeptName, Location)

∙ Products(ProductID, ProductName, Price, Quantity)

∙ Orders(OrderID, CustomerID, OrderDate, TotalAmount)

a) Select Operations:

1. Write a relational algebra expression to find all employees in the Employee table who work in

the IT department.

2. How would you select all products from the Products table with a price greater than $50?

3. Write an expression to retrieve all orders from the Orders table placed in the month of

January.

b) Project Operation:

1. Write a relational algebra expression to project the Name and Salary columns from the

Employee table.

2. How would you project only the ProductName and Price columns from the Products table?

3. Write an expression to create a list of all unique department names from the Department table.

Practice Examples

PREPARED BY: MS. PRIYANKA KAMBLE RELATIONAL MODEL AND RELATIONAL ALGEBRA

60

c) Rename Operations:

1. Write a relational algebra expression to rename the relation Employee to Staff.

2. How would you rename the attribute DeptID in the Employee table to DepartmentID?

3. Write an expression to rename the relation Products to Items.

Practice Examples

PREPARED BY: MS. PRIYANKA KAMBLE RELATIONAL MODEL AND RELATIONAL ALGEBRA

61

a) Union Operations:

1. Write a relational algebra expression to combine the EmployeeNY and EmployeeCA tables into a single relation.

2. How would you use Union to merge Products_A and Products_B into a single list of products?

3. Write an expression to find all unique students by combining Students_UG and Students_G.

4. How would you create a combined list of employees from both EmployeeNY and EmployeeCA using Union?

Q. Sample Relations:

∙ EmployeeNY(EmpID, Name, DeptID, Salary)

∙ EmployeeCA(EmpID, Name, DeptID, Salary)

∙ Products_A(ProductID, ProductName, Price)

∙ Products_B(ProductID, ProductName, Price)

∙ Students_UG(StudentID, Name, Major)

∙ Students_G(StudentID, Name, Major)

Practice Examples

PREPARED BY: MS. PRIYANKA KAMBLE RELATIONAL MODEL AND RELATIONAL ALGEBRA

62

b) Intersection Operations:

1. Write a relational algebra expression to find employees who are also managers.

2. How would you use Intersection to find products that are both in stock and have been sold?

3. Write an expression to identify students who are in both Students_A and Students_B.

4. How would you find employees who are assigned to the same department as managers using Intersection?

Sample Relations:

∙ Employee(EmpID, Name, DeptID)

∙ Manager(EmpID, DeptID)

∙ ProductStock(ProductID, Quantity)

∙ ProductSales(ProductID, QuantitySold)

∙ Students_A(StudentID, Name)

∙ Students_B(StudentID, Name)

Practice Examples

PREPARED BY: MS. PRIYANKA KAMBLE RELATIONAL MODEL AND RELATIONAL ALGEBRA

63

c) Set difference Operations:

1. Write a relational algebra expression to find current employees by subtracting EmployeeLeft from EmployeeAll.

2. How would you use Set Difference to find products in Products_A that are not in Products_B?

3. Write an expression to identify students who have not graduated by subtracting Graduates

from StudentsAll.

4. How would you use Set Difference to find employees who are not managers by subtracting

Manager from EmployeeAll?

Sample Relations:

• EmployeeAll(EmpID, Name, DeptID)
• EmployeeLeft(EmpID, Name, DeptID)
• Products_A(ProductID, ProductName)
• Products_B(ProductID, ProductName)
• StudentsAll(StudentID, Name)
• Graduates(StudentID, Name)

Practice Examples

PREPARED BY: MS. PRIYANKA KAMBLE RELATIONAL MODEL AND RELATIONAL ALGEBRA

64

a) Join Operations:

1. Write a relational algebra expression to join Employee with Department based on DeptID.

2. How would you join Orders with Customers to get a list of all orders along with the

customer names?

3. Write an expression to join OrderDetails with Products to get the product names for each

order.

4. How would you join Employee with Orders to find all employees who have placed

orders?

5. Write a relational algebra expression to join OrderDetails with Orders to get the order

dates for each product

Sample Relations:

∙ Employee(EmpID, Name, DeptID)

∙ Department(DeptID, DeptName)

∙ Orders(OrderID, CustomerID, OrderDate)

∙ Customers(CustomerID, CustomerName)

∙ Products(ProductID, ProductName)

∙ OrderDetails(OrderID, ProductID, Quantity)

Practice

PREPARED BY: MS. PRIYANKA KAMBLE RELATIONAL MODEL AND RELATIONAL ALGEBRA

65

Practice

PREPARED BY: MS. PRIYANKA KAMBLE RELATIONAL MODEL AND RELATIONAL ALGEBRA

66

1. Retrieve all students who are in Class 10.
2. Find students who are enrolled in Course Type 'A'.
3. Get students whose age is greater than 30.
4. List only the names of all students.
5. Display the C_name of all courses.
6. Get a list of unique course types from the Student table.
7. Rename the Student relation to Learner.
8. Rename the attribute C_name in the Course table to Course_Name.
9. Find all course types listed in both Student and Course relations.
10. Identify common C_type values present in both tables.
11. Find course types enrolled by students but not defined in the Course table.
12. Pair every student with every course (useful for full mapping scenarios).
13. Display student names with the name of their respective courses.
14. List all students along with their course names, if available.
15. Display all courses along with student details (if any enrolled).
16. Display all students and courses, matched wherever possible.

Extended relational algebra

• Extended relational algebra in DBMS refers to a set of operations that enhance the basic relational algebra operators to provide more complex data manipulation capabilities.

​

• Aggregation function
• Grouping Function

​

PREPARED BY: MS. PRIYANKA KAMBLE RELATIONAL MODEL AND RELATIONAL ALGEBRA

67

Extended Relational Algebra

• Aggregation Function
• Aggregation functions in DBMS are used to perform calculations on a set of values and return a single summarizing value.
• They are typically used with the GROUP BY clause but can also be used independently.

​

PREPARED BY: MS. PRIYANKA KAMBLE RELATIONAL MODEL AND RELATIONAL ALGEBRA

68

Syntax:

SELECT AGGREGATE_FUNCTION(column_name)

FROM table_name

[WHERE condition]

[GROUP BY column_name];

Sample SQL Queries Using Aggregation Functions

PREPARED BY: MS. PRIYANKA KAMBLE RELATIONAL MODEL AND RELATIONAL ALGEBRA

69

1. Count() function: Count the total number of employees

SELECT COUNT(*) AS TotalEmployees

FROM Employee;

​

Result Table:

Employee

Sample SQL Queries Using Aggregation Functions

PREPARED BY: MS. PRIYANKA KAMBLE RELATIONAL MODEL AND RELATIONAL ALGEBRA

70

2. SUM() – Total Salary of All Employees

SELECT SUM(Salary) AS TotalSalary

FROM Employee;

Result Table:

Employee

50000+60000+55000+65000+70000

Sample SQL Queries Using Aggregation Functions

PREPARED BY: MS. PRIYANKA KAMBLE RELATIONAL MODEL AND RELATIONAL ALGEBRA

71

3. AVG() – Average Salary of Employees

SELECT AVG(Salary) AS AverageSalary

FROM Employee;

Result Table:

Employee

Sample SQL Queries Using Aggregation Functions

PREPARED BY: MS. PRIYANKA KAMBLE RELATIONAL MODEL AND RELATIONAL ALGEBRA

72

4. MIN() and MAX() – Minimum and Maximum Salary

Result Table:

Employee

SELECT MIN(Salary) AS MinSalary, MAX(Salary) AS MaxSalary

FROM Employee;

Sample SQL Queries Using Aggregation Functions

PREPARED BY: MS. PRIYANKA KAMBLE RELATIONAL MODEL AND RELATIONAL ALGEBRA

73

5. SUM() Grouped by Department – Total Salary by Dept

SELECT Dept, SUM(Salary) AS DeptSalary

FROM Employee

GROUP BY Dept;

Result Table:

Employee

Sample SQL Queries Using Aggregation Functions

PREPARED BY: MS. PRIYANKA KAMBLE RELATIONAL MODEL AND RELATIONAL ALGEBRA

74

6. COUNT() Grouped by Department – Number of Employees per Dept

SELECT Dept, COUNT(*) AS EmployeeCount

FROM Employee

GROUP BY Dept;

Result Table:

Employee

WHERE vs HAVING in SQL

PREPARED BY: MS. PRIYANKA KAMBLE RELATIONAL MODEL AND RELATIONAL ALGEBRA

75

SELECT column1, column2

FROM table

WHERE condition;

SELECT column1, AGG_FUNCTION(column2)

FROM table

GROUP BY column1

HAVING condition;

WHERE Clause

HAVING Clause

WHERE in SQL

PREPARED BY: MS. PRIYANKA KAMBLE RELATIONAL MODEL AND RELATIONAL ALGEBRA

76

SELECT *

FROM Sales

WHERE Category = 'Electronics';

Sales

HAVING in SQL

PREPARED BY: MS. PRIYANKA KAMBLE RELATIONAL MODEL AND RELATIONAL ALGEBRA

77

SELECT Product, SUM(Quantity) AS TotalQuantity

FROM Sales

GROUP BY Product

HAVING SUM(Quantity) > 3;

Sales

WHERE + GROUP BY + HAVING in SQL

PREPARED BY: MS. PRIYANKA KAMBLE RELATIONAL MODEL AND RELATIONAL ALGEBRA

78

SELECT Product, SUM(Quantity) AS TotalQuantity

FROM Sales

WHERE Category = 'Electronics'

GROUP BY Product

HAVING SUM(Quantity) > 3;

Sales