PARUL INSTITUTE OF ENGINEERING & TECHNOLOGY

FACULTY OF ENGINEERING & TECHNOLOGY

PARUL UNIVERSITY

Unit 4: Relational Data Model

Dr. Vishwanath
OUTLINE

• Relational Data Model

• Constraints & Keys
• Relational Algebra Operations
 Relational Data Model
The relational data model describes the world as
“a collection of inter-related relations (or tables).”

The relational data model was introduced by

Edgar F. Codd in 1970, while working for IBM.

✓ Currently, it is the most widely used data model.

Relational Data Model
The relational data model has provided the basis for:
• Research on the theory of data/relationship/constraint
• Numerous database design methodologies
• The standard database access language called structured query
language (SQL)
• Almost all modern commercial database management systems.
 Relational Data Model
The relational model represents
how data is stored in Relational
Databases.

➢ Tables are also known as relations.

Each relation is a collection of columns

and rows, where the column represents
the attributes of an entity and the rows
(or tuples) represent the records.
Relational Data Model
Attribute: Attributes are the properties that define an entity.
e.g.; ROLL_NO, NAME, ADDRESS

Tuple: Each row in the

relation is known as a tuple.

This relation contains 4 tuples.

Relational Data Model
Column: The column represents the set of values for a particular attribute.
Null Values: The value which is not known or unavailable. It is represented by
blank space.

Degree: The number of attributes in the relation.

The STUDENT relation defined above has degree 5.
Cardinality: The number of tuples in a relation.
The STUDENT relation defined above has cardinality 4.
Relational Data Model
Advantages of the Relational Model
Simple model: It is simple and easy to use in comparison to other languages.
Flexible: It is more flexible than any other relational model present.
Secure: It is more secure than any other relational model.
Data Accuracy: Data is more accurate in the relational data model.
Data Integrity: The integrity of the data is maintained in the relational model.

Disadvantages of the Relational Model

• Relational Database Model is not very good for large databases.
• Sometimes, it becomes difficult to find the relation between tables.
• Because of the complex structure, the response time for queries is high.
Relation Key
The keys that are used to identify the rows uniquely and also
helps in identifying tables.

▪ Primary Key ▪ Super Key

▪ Foreign Key ▪ Candidate Key
Relation Key : Primary Key
A primary key is a column or a set of columns in a table whose values
uniquely identify a row in the table.
 Relation Key : Foreign Key
If an attribute can only take the values which are present as values of
some other attribute, it will be a foreign key.
• It acts as a primary key in one
table and it acts as secondary key in
another table.

• It combines two or more relations

(tables) at a time.

• They act as a cross-reference

between the tables.
 Relation Key : Super Key
The set of attributes that can uniquely identify a tuple is known as Super Key.
Example:
1. STUD_NO + STUD_NAME
2. STUD_NO + PHONE

A super key is a group of

single or multiple keys that
identifies rows in a table.
Relation Key : Candidate Key
The minimal set of attributes that can uniquely identify a tuple is known as
a candidate key.
Example: Stud ID, Roll No, Email
 Not Null Constraint
The constraint is typically used for columns that must contain essential information.

Not Null Constraint: It ensures that a specific column in a table cannot

contain null values.
Syntax: CREATE TABLE table_Name(column1 data_type(size) NOT NULL);
Example: CREATE TABLE Doctor(Doctor_ID INT NOT NULL,
Doctor_Name VARCHAR(100) NOT NULL) ;

✓ When you insert or update data in a column with “not null constraint”, you
are required to provide a valid value, and leaving it empty is not allowed.
Check Constraint
It allows you to ensure a value meets a certain condition before it is inserted
into a table.

Syntax: CREATE TABLE table_name (column1 datatype CONSTRAINT

constraint_name CHECK (expression));

Example: CREATE TABLE employees (name varchar(25) CONSTRAINT

age_check CHECK (age > 18));
Relational Algebra Operations
It is used to perform different operations in relational databases.

It consists of a set of operations enabling users to manipulate and handle data

stored in relational databases.

These are similar to the operations of set theory.

for example- union intersection and difference

And some personalized operations for relational databases.
Relational Algebra Operations
Types of Relational Algebra Operations:

1. Basic 2. Derived/advanced

Selection (σ) Join (⨝) (Natural & Outer Join)

Projection (π) Aggregate Functions
Rename (ρ)
Cross Product(X)
Set operators (Union,
Intersection, Set Difference)
Relational Algebra Operations
Here are two tables/relations which will be used for demonstrating the relational
algebra operations examples:
1.STUDENT (ROLL, NAME, AGE) 2. EMPLOYEE (EMPLOYEE_NO, NAME, AGE)

ROLL NAME AGE EMPLOYEE_NO NAME AGE

1 Aman 20 E-1 Anant 20
2 Atul 18 E-2 Ashish 23
3 Baljeet 19 E-3 Baljeet 25
4 Harsh 20 E-4 Harsh 20
5 Prateek 21 E-5 Pranav 22
6 Prateek 23
Selection (σ)
Selection Operator which is represented by "sigma"(σ).

It is used to retrieve tuples(rows) from the table where the given condition is satisfied.

Example: Suppose we want the row(s) from STUDENT Relation where "AGE" is 20

σ AGE=20 (STUDENT) ROLL NAME AGE

1 Aman 20
4 Harsh 20
Projection (∏)
It pulls out some specific columns (attributes) from a relation.
It is represented by "pi"(∏).

Example: Suppose we want the names of all students from STUDENT Relation.
∏ NAME(STUDENT) NAME
Aman
Atul
Baljeet
Harsh
Prateek
Rename (ρ)
It is denoted by "Rho"(ρ).

It is used to rename the output relation.

Example: If we want to rename the STUDENT relation as STUDENT_NAME

ρ(STUDENT_NAME,∏ NAME(STUDENT))
Cross Product(X)
It is denoted by symbol ‘X’.

It is used to perform operation on two relations.

Example: Consider two relations STUDENT(SNO, FNAME, LNAME) and
DETAIL(ROLLNO, AGE) below:

S. No. FNAME LNAME ROLLNO AGE

1 Albert Singh 5 18
2 Nora Fatehi 9 21
Cross Product(X)
On applying CROSS PRODUCT on STUDENT and DETAIL:

STUDENT ✕ DETAILS

SNO FNAME LNAME ROLLNO AGE

1 Albert Singh 5 18
1 Albert Singh 9 21
2 Nora Fatehi 5 18
2 Nora Fatehi 9 21
Set operators: Union
Union Operator which is represented by "union"(∪), same as the union NAME
operator Aman
from set theory. Anant
It selects all tuples/rows from both relations/tables but with the exception Ashish
that; both relations/tables must have the same set of Attributes. Atul
Baljeet
Example: Suppose we want all the names from STUDENT and
EMPLOYEE relation. Harsh
Pranav
∏ NAME(STUDENT) ∪ ∏ NAME(EMPLOYEE)
Prateek
Set operators: Intersection & Set Difference
Intersection is represented by (∩).

It selects all the tuples which are present in both relations.

Set difference gives the difference between two relations.

It is denoted by a "Hyphen"(-) and it returns all the tuples(rows) which are in relation
R but not in relation S.
Set operators: Intersection & Set Difference
Example:
FRENCH GERMAN
Student_Name Roll_Number Student_Name Roll_Number
Vivek 13
Ram 01
Geeta 17
Mohan 02
Shyam 21
Vivek 13
Rohan 25
Geeta 17
Set operators: Intersection
Example: If we want the names which are present in STUDENT as
well as in EMPLOYEE relation.
∏ NAME(STUDENT) ∩ ∏ NAME(EMPLOYEE)

NAME
Baljeet
Harsh
Set operators: Set Difference
Example: To know the names of students who are in STUDENT
Relation but not in EMPLOYEE Relation.
∏ NAME(STUDENT) - ∏ NAME(EMPLOYEE)

NAME
Aman
Atul
Prateek
Join (⋈)
A join is an operation that combines the rows/tuple of two or more tables
based on common attribute/columns.

The main purpose of Join is to retrieve the data from multiple tables.

It is denoted by ⨝.

Frequently used JOIN types:

1. Inner Join 2. Outer Join

Inner Join
Inner Join is a join operation that combines
two or more tables based on related columns.

It returns only rows that have matching

values among tables.

• Equi Join
• Natural Join
➢ Natural Join joins two tables based on the same attribute name and datatypes.
 Natural Join
Department Employee

Output:
SELECT * FROM department
NATURAL JOIN employee;
Outer Join
Outer joins retrieves matching as well as non-matching records from related tables.

Types:

1. Left outer join / Left Join

2. Right outer join / Right Join

3. Full outer join / Full Join

Left Join
It retrieves all records from the left table and retrieves matching records from
the right table.
Example: Table A Table B A⟕B
Number Square Number Cube Number Square Cube
2 4 2 8 2 4 8
3 9 3 27 3 9 27
4 16 5 125 4 16 -
 Left Join
Department Employee

Output:
SELECT * FROM department
LEFT OUTER JOIN employee ON
department.DEPT_NAME =
employee.DEPT_NAME;
 Right Join
It retrieves all records from the right table and retrieves matching records from the left table.

Example: Table A Table B A⟖ B

Number Square Number Cube Number Square Cube
2 4 2 8 2 4 8

3 9 3 27 3 9 27

4 16 5 125 5 - 125
 Right Join
Department Employee

Output:
SELECT * FROM department
RIGHT OUTER JOIN employee ON
department.DEPT_NAME =
employee.DEPT_NAME;
 Full Join
It combines the results of both LEFT JOIN and RIGHT JOIN. The result set
will contain all the rows from both tables.
Example: Table A Table B A⟗ B
Number Square Number Cube Number Square Cube

2 4 2 8 2 4 8

3 9 3 27 3 9 27

4 16 5 125 4 16 NULL

5 NULL 125
 Full Join
Department Employee

Output:
SELECT * FROM department
FULL OUTER JOIN employee ON
department.DEPT_NAME =
employee.DEPT_NAME;
Aggregate Function
Aggregation function is used to perform the calculations on multiple rows of a single
column of a table.
It returns a single value.
Aggregate Function
CREATE TABLE Employee (Id INT, Name CHAR(1), Id Name Salary
Salary DECIMAL(10)); 1 A 802
INSERT INTO Employee (Id, Name, Salary)
VALUES (1, 'A', 802), 2 B 403
(2, 'B', 403),
(3, 'C', 604), 3 C 604
(4, 'D', 705),
4 D 705
(5, 'E', 606),
(6, 'F', NULL); 5 E 606

6 F NULL
Aggregate Function
Aggregate Function Example:
--Count the number of employees
Total Employees: 6
SELECT COUNT(*) AS Total Employees FROM Employee;
-- Calculate the total salary
SELECT SUM(Salary) AS Total Salary FROM Employee; Total Salary: 3120
-- Find the average salary
SELECT AVG(Salary) AS Average Salary FROM Employee; Average Salary: 624
-- Get the highest salary
SELECT MAX(Salary) AS Highest Salary FROM Employee;
Highest Salary: 802
-- Determine the lowest salary
SELECT MIN(Salary) AS Lowest Salary FROM Employee;
Lowest Salary: 403
Thank You!!!
www.paruluniversity.ac.in