DATABASE

MANAGEMENT SYSTEMS

Authors – Raghu Ramakrishna, Johannes Gehrke

Edition – 3
 UNIT -2
• Introduction to Relational Model-Integrity constants over
Relations, Enforcing Integrity constants-Querying Relational
Data, Logical Data Base Design- Introduction To views,
Destroying and Altering Tables, and Views, Relational
Algebra-Selection, Projection, and Set operations-
Renaming- Joins-Divisions.RelationalCalculus-
TupleRelationalcalculus-DomainRelationalCalculus
Relational Model
• Relational model can represent as a table with columns and rows. Each
row is known as a tuple. Each table of the column has a name or
attribute.
Key Terms:
• Tables: In this model relations are saved in the form of tables.
• Domain: It contains a set of atomic values that an attribute can take.
• Attribute: It contains the name of a column in a particular table. Each
attribute Ai must have a domain, dom (Ai)
• Relational instance: In the relational database system, the relational
instance is represented by a finite set of tuples. Relation instances do
not have duplicate tuples.
• Relational schema: A relational schema contains the name of the
relation and name of all columns or attributes.
• Relational key: In the relational key, each row has one or more
attributes. It can identify the row in the relation uniquely.
Properties of Relations
• Name of the relation is distinct from all other relations
• Each relation cell contains exactly one atomic (single) value
• Each attribute contains a distinct name
• Tuple has no duplicate value
• Order of tuple can have a different sequence
Importance of NULL values
• The SQL NULL is the term used to represent a missing value
• A NULL value in a table is a value in a field that appears to be blank
• A field with a NULL value is a field with no value
• It is very important to understand that a NULL value is different than a
zero value or a filled that contains space.
Syntax:
Create table customers (id int not null,
name varchar (20) not null,
age int not null,
address char (10),
salary decimal (18,2));
• Here, NOT NULL signifies that column should always accept an explicit value of
the given data type.
• A field with a NULL value is the one that has been left blank during the record
creation.

Example:
Select id , name, age, address, salary from customers where salary is not null;
Integrity Constraints
• Integrity constraints are a set of rules. It is used to maintain the quality
of information.
• Integrity constraints ensure that the data insertion, updating, and other
processes have to be performed in such a way that data integrity is not
affected. Types of integrity constraints:
Types of Integrity Constraints
1. 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
2. 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
3. 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 null or be available in Table 2.
4. 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
Example:
Constraints on table
1. Primary key
Constraints on table
2. NOT NULL
Constraints on table
3. CHECK
Constraints on table
4. DEFAULT
Constraints on table
5. UNIQUE
Constraints on table
6. FOREIGN KEY
Database Schema (Database Design)
• It defines how the data is organized and how the relations among them
are associated. It formulates all the constraints that are to be applied on
the data. A database schema defines its entities and the relationship
among them.
Querying Relational data
1. SQL Data Types:
• Data types are used to represent the nature of the data that can be
stored in the database table.
• For example, in a particular column of a table, if we want to store a
string type of data then we will have to declare a string data type of
this column.
• Data types mainly classified into three categories for every database.

1. String Data types

2. Numeric Data types
3. Date and time Data types
MySQL String Data Types
CHAR(Size) It is used to specify a fixed length string that can contain numbers, letters, and special
characters. Its size can be 0 to 255 characters. Default is 1.
VARCHAR(Size) It is used to specify a variable length string that can contain numbers, letters, and special
characters. Its size can be from 0 to 65535 characters.
BINARY(Size) It is equal to CHAR() but stores binary byte strings. Its size parameter specifies the column
length in the bytes. Default is 1.
VARBINARY(Size) It is equal to VARCHAR() but stores binary byte strings. Its size parameter specifies the
maximum column length in bytes.
TEXT(Size) It holds a string that can contain a maximum length of 255 characters.
TINYTEXT It holds a string with a maximum length of 255 characters.
MEDIUMTEXT It holds a string with a maximum length of 16,777,215.
LONGTEXT It holds a string with a maximum length of 4,294,967,295 characters.
ENUM(val1,val2...) It is used when a string object having only one value, chosen from a list of possible values.
It contains 65535 values in an ENUM list. If you insert a value that is not in the list, a blank
value will be inserted.
SET(val1,val2,....) It is used to specify a string that can have 0 or more values, chosen from a list of possible
values. You can list up to 64 values at one time in a SET list.
BLOB(size) It is used for BLOBs (Binary Large Objects). It can hold up to 65,535 bytes.
 MySQL Numeric Data Types
BIT(Size) It is used for a bit-value type. The number of bits per value is specified in size. Its size can be 1 to
64. The default value is 1.
INT(size) It is used for the integer value. Its signed range varies from -2147483648 to 2147483647 and
unsigned range varies from 0 to 4294967295. The size parameter specifies the max display width
that is 255.
INTEGER(size) It is equal to INT(size).
FLOAT(size, d) It is used to specify a floating point number. Its size parameter specifies the total number of digits.
The number of digits after the decimal point is specified by d parameter.
FLOAT(p) It is used to specify a floating point number. MySQL used p parameter to determine whether to use
FLOAT or DOUBLE. If p is between 0 to24, the data type becomes FLOAT (). If p is from 25 to
53, the data type becomes DOUBLE().
DOUBLE(size, It is a normal size floating point number. Its size parameter specifies the total number of digits. The
d) number of digits after the decimal is specified by d parameter.
DECIMAL(size, It is used to specify a fixed point number. Its size parameter specifies the total number of digits.
d) The number of digits after the decimal parameter is specified by d parameter. The maximum value
for the size is 65, and the default value is 10. The maximum value for d is 30, and the default value
is 0.
DEC(size, d) It is equal to DECIMAL(size, d).
BOOL It is used to specify Boolean values true and false. Zero is considered as false, and nonzero values
are considered as true.
MySQL Date and Time Data Types

DATE It is used to specify date format YYYY-MM-DD. Its supported

range is from '1000-01-01' to '9999-12-31'.
DATETIME It is used to specify date and time combination. Its format is YYYY-
(fsp) MM-DD hh:mm:ss. Its supported range is from '1000-01-01
00:00:00' to 9999-12-31 23:59:59'.
TIMESTA It is used to specify the timestamp. Its value is stored as the number
MP(fsp) of seconds since the Unix epoch('1970-01-01 00:00:00' UTC). Its
format is YYYY-MM-DD hh:mm:ss. Its supported range is from
'1970-01-01 00:00:01' UTC to '2038-01-09 03:14:07' UTC.
TIME(fsp) It is used to specify the time format. Its format is hh:mm:ss. Its
supported range is from '-838:59:59' to '838:59:59'
YEAR It is used to specify a year in four-digit format. Values allowed in
four digit format from 1901 to 2155, and 0000.
2. SQL Languages
• Database languages can be used to read, store and update the data in
the database.

• Data Definition Language (DDL)

• Data Manipulation Language (DML)
• Data Control Language (DCL)
• Transaction Control Language (TCL)
3. SQL Queries
• Create: create table table_name;
• Insert: Insert into table_name( col1,col2….)into (value1, value2..)
• Drop: drop table table_name
• Alter: alter table table_name add column_name datatype;
• Rename: rename old_name to new_name;
• Truncate: truncate table table_name;
• Update: update table_name set col=val1,col2=val2..where condition
• Delete: delete from table_name where condition;
• Select: select column from table_name where condition;
4. SQL operators
i) SQL Arithmetic Operators: two variables "a" and "b". Here "a" is
valued 50 and "b" valued 100.
ii) SQL Comparison Operators: two variables "a" and "b" that are valued 50 and
100.
iii) SQL Logical Operators:
5. SQL aggregation functions

• SQL aggregation function is used to perform the calculations on

multiple rows of a single column of a table. It returns a single value.
• COUNT(): COUNT function is used to Count the number of rows in a database
table. It can work on both numeric and non-numeric data types
Syntax: COUNT(*) or COUNT ([All /DISTINCT ] expression)
Example:
EXAMPLE:
• SELECT COUNT (*) FROM PRODUCT_MAST; //10

• SELECT COUNT(*) FROM PRODUCT_MAST WHERE RATE>=20; //7

• SELECT COUNT(DISTINCT COMPANY) FROM PRODUCT_MAST; //3

• SELECT COMPANY, COUNT(*) FROM PRODUCT_MAST GROUP BY

COMPANY;
// COM1 5, COM2 3, COM3 2

• SELECT COMPANY, COUNT(*) FROM PRODUCT_MAST GROU BY COMPANY

HAVING COUNT(*) > 2;
// COM1 5, COM2 3
• Sum() : used to calculate the sum of all selected columns.
Syntax: SUM([ALL/DISTINCT] EXPRESSION)
Example: SELECT SUM(COST) FROM PRODUCT_MAST; //670
Example: SELECT SUM(COST) FROM PRODUCT_MAST WHERE
QTY>3;
//320

• AVG(): Used to calculate the average value of a numeric type.

Syntax: AVG([ALL/DISTINCT] EXPRESSION)
Example: SELECT AVG(COST) FROM PRODUCT_MAST; //67.00
• Min(): used to find the minimum value of certain column
Syntax: MIN([ALL/DISTINCT] expression)
Example: SELECT MAX(RATE) FROM PRODUCT_MAST;
//10
• Max(): used to find the maximum value of certain column.
Syntax: MAX([ALL/DISTINCT] expression)
Example: SELECT MAX(RATE) FROM PRODUCT_MAST;
//30
6. Scalar Functions
• These functions are based on user input, these too return single
values.
• UPPER()/UCASE():
• LOWER()
• SUBSTR()
• LENGTH()
• ROUND()
• NOW()
• FORMAT()
• UPPER(), LOWER(),SUBSTR(),LENGTH(),ROUND()
NOW() AND FORMAT()
• NOW(): Returns the current system date and time.
Syntax: SELECT NOW() FROM TABLE_NAME;
Example: SELECT NAME, NOW() AS DATETIME FROM STUDENTDATA;

FORMAT(): Used to format how a field is to be displayed.

Syntax: SELECT FORMAT(COLUMN_NAME ,FORMAT) FROM TABLE_NAME;
Example: SELECT NAME,FORMAT(NOW(),’YYYY-MM-DD’) AS DATE FROM
STUDENTS;
SQL DATE AND TIME FUNCTIONS:

1. Sysdate(): returns the current date and time of the system.

2. Months_between(x,y): This function takes two values namely x and y which

are in the form of months. It returns the number of months between x and y.
Example: SELECT MONTHS_BETWEEN (SYSDATE, EMP_JOIN_DATE)
FROM EMP;
Consider the Employee joining date as 1-January-2018 and the system date as
1-August-2018. Therefore the above returns 7
7. SQL Numeric Functions
• ABS(X): Returns absolute value of X
• MOD(X,Y): remainder is returned
• SIGN(X): returns +1 when the number is positive and -1 for negative
• FLOOR(X):returns the largest int value that is either less than X or
equal to it
• CEIL(X): returns the smallest int value that is greater than or equal to
it.
• POWER(X,Y): returns the value of X raised to the power of Y
8. String conversion functions
• TO_CHAR()
• TO_NUMBER()
• TO_DATE()
Logical database Design
1. Entity Sets to Tables
2. Translating Relationship Sets with Key Constraints
3. Translating Weak Entity Sets
Entity Sets to Tables
• An entity set is mapped to a relation in a straightforward way: Each
attribute of the entity set becomes an attribute of the table. Note that
we know both the domain of each attribute and the (primary) key of an
entity set.
Relationship Sets with key constraints to Tables
Translating Weak Entity Sets
Introduction to Views
• A view is a table whose rows are not explicitly stored, a view is a virtual
table constructed from the existing tables.
• A view can be created from one or many tables which depends on the written
SQL query to create a view.
• A view is generated to show the information that the end-user requests the
data according to specified needs rather than complete information of the
table.
• Changes made in views reflects on table and vice versa.
Advantages:
• Using Views, we can join multiple tables into a single virtual table.
• In the database, views take less space than tables for storing data because the
database contains only the view definition.
• Views indicate the subset of that data, which is contained in the tables of the
Example

COLLEGE

view view view view view

CSE ECE EEE CIVIL MECH

College maintains database of different branches. When we try to modify the data in
any branch it will automatically reflects in the college data base by using VIEWS.
Syntax for creation of View
create view view_name as
select column1,column2, ………..
from table_name where condition;

Example:
create view cse_stud as
select * from student where branch=‘cse’;
Student table

Creation of a view from one table

Modifying views/ modifying master tables
Creating views from more than one table
• When we try to modify the marks_view table it displays an error
message “cannot modify” because the marks_view table is created
from the two existing tables called “student_details’ and
“student_marks” .
Criteria for View Updating

• The select statement used in the create view

statement should not include group by clause or order by
clause
• The select statement must not contain distinct keyword
• A view should not be created from nested or Complex
queries
• A view should be created from a single table but if the view
is created from more than one table then it is not allowed
for updating
Create / Replace Views
Syntax:
CREATE OR REPLACE VIEW VIEW_NAME AS
SELECT COLUMN1,COLUMN2,…
FROM TABLE_NAME WHERE CONDITION;
Deleting and updating views in DBMS
• Delete a view by using the DROP statement.
Syntax:
DROP VIEW VIEW_NAME;
Example:
DROP VIEW CSE_STUD;
DROP VIEW MARKS_VIEW;

Updating view:
• Views are updated only if certain conditions are met otherwise if any
one of the conditions are not met views will not be updated.
Inserting a row into a views
• To insert a row in a view just like inserting a row in an ordinary table.
 Deleting a row from a view
• A row in a view can be deleted just like simply deleting rows from a
Table using delete statement.
• But remember a row in a view can be deleted only if the row is actually
deleted in the original table from which it is created.

Syntax:
Delete from view_name
where condition;
Relational Algebra
• Relational algebra is a procedural query language. It gives a step by
step process to obtain the result of the query. It uses operators to
perform queries.
• It is a theoretical model and base for SQL.
• In relational mode, the data can retrieved theoretically or practically.
• Theoretical means -> Relational Algebra, Relational Calculus
• Practical means-> Through SQL queries
 RELATIONAL ALGEBRA

UNARY SET THEORY BINARY EXTENDED OR

OPERATIONS OPERATIONS OPERATIONS DERIVED
OPERATIONS
JOIN
SELECTION UNION OUTER JOIN
INNER JOIN
INTERSECTION LEFT JOIN
PROJECTION THETA JOIN
CROSS
NATURAL JOIN RIGHT JOIN
PRODUCT
RENAME
MINUS/SET DIVISION FULL JOIN
DIFFERENCE
AGGREGATION
FUNCTIONS
 UNARY OPERATIONS
• Unary operations performed on single relation / single table
• Selection operation
• Represented with sigma (σ)
• Horizontal subset of a relation (gives row /tuple as a result)
• Syntax: σ (condtion) (relation_name)
Student

Examples:
σ ( student) – displays all rows
σ (course =‘cs101’ and grade=‘A’ )
(student) -> display the entire row
σ (perc < 90) (student)
• Projection:
• used to select a subset of columns from a table.
• Projection (Π/ π)
• Vertical subset of a relation (columns)
• Syntax: π (col1,col2….coln) (relation)

Example:
Π ( Name) (student)
Π (Roll, per)(student)
Π (roll,per) (σ per>90) (student)
• Rename operation
• Represented with symbol RHO(ρ)
• Syntax: ρ (new_name) (relation_name)
Student

Example:
ρ (final_students) (student)
SET THEORY OPERATIONS
• Set Theory Operations can be performed on 2 tables or
relations.

UNION( U ) :
• Let R and S be two relations.
• Then-
• R ∪ S is the set of all tuples belonging to either R or S or both.
• In R ∪ S, duplicates are automatically removed.
Intersection (∩):
• Let R and S be two relations.
• Then-
• R ∩ S is the set of all tuples belonging to both R and S.
• In R ∩ S, duplicates are automatically removed.
• Intersection operation is both commutative and associative.
Minus/Difference operator:
• Let R and S be two relations.
• Then-
• R – S is the set of all tuples belonging to R and not to S.
• In R – S, duplicates are automatically removed.
• Difference operation is associative but not commutative.
Cross product operation (X):
• 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.
• Notation: E X D
• Example: (Employee X DEPARTMENT)

EMPLOYEE DEPARTMENT
EMP_ID EMP_NAME EMP_DEPT DEPT_NUM DEPT_NAME

1 SMITH A A MARKETING

2 HARRY C B SALES

3 JOHN B C LEGAL
JOINS
• Joins (⋈) in DBMS is used to combine tables.
• There are three types of joins: inner joins and outer joins.
• Inner joins are classified into three types: Theta Join(for relational
operators), natural join and Equi Join(for Equality).
• There are three types of outer joins in DBMS: left outer join, right outer
join, and full outer join.
• Natural join is only performed when at least one matching attribute exists in
both tables.
• No matter the Join condition, a left outer join always returns every row from
the left table.
• Regardless of the Join condition, Right Outer Join always returns all rows
from the right table.
• Regardless of the join condition, Complete Outer Join always returns all
Join = cartesian product + selection
RELATION R RELATION S
A B C D
1 2 5 6
3 4 7 8

RELATION R X S
A B C D
1 2 5 6
1 2 7 8
3 4 5 6
3 4 7 8
Inner join
Theta join: A Theta Join uses a condition other than equality to join

type of attribute. The attributes names may not be equal. [ ⋈(condition )]

two tables. In this relations will be join with respect to condition and

• Example: C ⋈ J.price > C.price J

Car_Model Price
Jeep_Model Price Car_Model Price
Car1 400000
Jeep1 600000 Car1 400000 Car2 500000
c
Jeep1 600000 Car2 500000 Car3 800000

Jeep2 1000000 Car1 400000

Jeep_Model Price
Jeep2 1000000 Car2 600000 Jeep1 600000 J
Jeep3 1000000 Car3 800000 Jeep2 1000000
Natural Join: Natural join can join tables based on the common
columns in the tables being joined. A natural join returns all rows by
matching values in common columns having same name and data type

Example: R ⋈ S
of columns and that column should be present in both tables.

SID SNAME DeptID DeptID DNAME

101 RAJU 1 1 CSE
102 RAVI 2 2 ECE
103 HARI 1 3 EEE
104 RAMU 3 4 MECH

SID SNAM DeptID DNAME

R⋈S
E
101 RAJU 1 CSE
102 RAVI 2 ECE
103 HARI 1 CSE
104 RAMU 3 EEE
Equi Join: R1 ⋈ condition R2
• R1 ⋈ (R1. DeptID = R2.DeptID) R2

SID SNAME DeptID DeptID DNAME

101 RAJU 1 1 CSE
102 RAVI 2 2 ECE
103 HARI 1 3 EEE
104 RAMU 3 4 MECH

SID SNAM DeptID DNAME

E
101 RAJU 1 CSE
102 RAVI 2 ECE
103 HARI 1 CSE
104 RAMU 3 EEE
Outer Join
Left Outer Join: A SQL operation that combines two tables, showing
all rows from the left table and matching rows from the right table. If
there is no match in the right table, it displays null values.(S ⟕ R).
SID SNAME DeptID
101 RAJU 1
102 RAVI 2
103 HARI 1 S⟕R
104 RAMU 3 SID SNAME DeptID DNAME
105 SURESH 5 101 RAJU 1 CSE
102 RAVI 2 ECE
DeptID DNAME
103 HARI 1 CSE
1 CSE 104 RAMU 3 EEE
2 ECE 105 SURESH 5 NULL
3 EEE
4 MECH
• Right outer join(⟖): RIGHT JOIN is similar to LEFT JOIN. This
join returns all the rows of the table on the right side of the join and
matching rows for the table on the left side of the join. (S ⟖ R)
SID SNAME DeptID
101 RAJU 1
102 RAVI 2
S⟖R
103 HARI 1 SID SNAME DeptID DNAME
104 RAMU 3 101 RAJU 1 CSE
105 SURESH 5 102 RAVI 2 ECE
104 RAMU 3 EEE
DeptID DNAME NULL NULL 4 MECH
1 CSE 103 HARI 1 CSE
2 ECE
3 EEE
4 MECH
• Full Join (⟗): FULL JOIN creates the result-set by combining 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. (S ⟗ R)
SID SNAME DeptID
101 RAJU 1 S⟗R
102 RAVI 2
SID SNAME DeptID DNAME
103 HARI 1
101 RAJU 1 CSE
104 RAMU 3
102 RAVI 2 ECE
105 SURESH 5
103 HARI 1 CSE
104 RAMU 3 EEE
DeptID DNAME
105 SURESH 5 NULL
1 CSE
NULL NULL 4 MECH
2 ECE
3 EEE
4 MECH
EXAMPLE 2 on joins

ROLL_N NAME ADDRESS PHONE AGE COURSE_ID ROLL_NO

O
1 1
1 HARSH DELHI 1234 18
2 2
2 PARTIK BIHAR 1243 19
2 3
3 RIYANKA SILGURI 5433 30
3 4
4 DEEP RAMNAGAR 6444 47
1 5
5 SAPTARHI KOLKATA 6666 39
4 9
6 DHANRAJ BARABAJAR 4444 28
5 10
7 ROHIT BALURGHAT 7777 56
4 11
8 NIRAJ ALIPUR 3333 32
INNER JOIN

ROLL_NO NAME ADDRESS PHONE AGE COURSE_ID

1 HARSH DELHI 1234 18 1

2 PARTIK BIHAR 1243 19 2

3 RIYANKA SILGURI 5433 30 2

4 DEEP RAMNAGAR 6444 47 3

5 SAPTARHI KOLKATA 6666 39 1

OUTER JOIN – LEFT OUTER JOIN

ROLL_NO NAME ADDRESS PHONE AGE COURSE_ID

1 HARSH DELHI 1234 18 1

2 PARTIK BIHAR 1243 19 2

3 RIYANKA SILGURI 5433 30 2

4 DEEP RAMNAGAR 6444 47 3

5 SAPTARHI KOLKATA 6666 39 1

6 DHANRAJ BARABAJAR 4444 28 NULL

7 ROHIT BALURGHAT 7777 56 NULL

8 NIRAJ ALIPUR 3333 32 NULL

OUTER JOIN – RIGHT OUTER JOIN

ROLL_NO NAME ADDRESS PHONE AGE COURSE_ID

1 HARSH DELHI 1234 18 1

2 PARTIK BIHAR 1243 19 2

3 RIYANKA SILGURI 5433 30 2

4 DEEP RAMNAGAR 6444 47 3

5 SAPTARHI KOLKATA 6666 39 1

9 NULL NULL NULL NULL 4

10 NULL NULL NULL NULL 5

11 NULL NULL NULL NULL 4

OUTER JOIN – FULL JOIN
ROLL_NO NAME ADDRESS PHONE AGE COURSE_ID
1 HARSH DELHI 1234 18 1
2 PARTIK BIHAR 1243 19 2
3 RIYANKA SILGURI 5433 30 2
4 DEEP RAMNAGAR 6444 47 3
5 SAPTARHI KOLKATA 6666 39 1
6 DHANRAJ BARABAJAR 4444 28 NULL
7 ROHIT BALURGHAT 7777 56 NULL
8 NIRAJ ALIPUR 3333 32 NULL
9 NULL NULL NULL NULL 4
10 NULL NULL NULL NULL 5
11 NULL NULL NULL NULL 4
DIVISON OPERATION
• The division operator is used for queries which involve the 'all'.
• R1 ÷ R2 = tuples of R1 associated with all tuples of R2.
Example
• Retrieve the name of the subject that is taught in all courses.

Name Course
System Btech Course
Database Mtech Name
÷ Btech =
Database Btech database
Mtech
Algebra Btech
Example : Retrieve names of employees who work on all the projects
that John Smith works on.
 Relational calculus
• Relational calculus in DBMS is a non-procedural language, which only
focuses on what kind of data is required and it does not care about how to
get these data.
Example: Let’s take an example to understand what is relational calculus in DBMS.
SELECT the tuples from EMPLOYEE relation with DEPARTMENT= 'NETWORKING’
Tuple Relational Calculus (TRC)
• TRC in DBMS is based on the concept of selecting tuples (rows) from
a relation (table) that satisfies certain conditions.
• Tuple Relational Calculus uses variables to represent tuples and
logical predicates to specify the conditions that must be met for the
tuples to be selected.
• The resulting expression is a formula that describes a set of tuples that
meet the specified conditions.
• The general syntax for Tuple Relational Calculus is:

• t is the resulting tuples, and P(t) is the condition used to get t

Example: Now, we will take the database table Employee and try to apply
the Tuple Relational Calculus expression on that table
Emp_id Emp_name Department
output
101 Naimish Computer
Emp_nam
102 Sahil Finance Emp_id Department
e
103 Divyesh Computer 101 Naimish Computer
104 Nikunj Account 103 Divyesh Computer
105 Gautam Computer 105 Gautam Computer
106 Vishal HR
107 Meet Business
108 Milan Business

TRC Query : {t | t ∈ Employee ∧ t.Department = ’Computer’} or

TRC Query: {t | Employee(t) ∧ t[Department] = ‘Computer’}
Domain Relational Calculus :
• Domain Relational Calculus in DBMS is based on the concept of selecting
values from a relation (table) that satisfy certain conditions.
• Domain Relational Calculus uses variables to represent individual values,
and logical predicates to specify the conditions that must be met for the
values to be selected.
• The resulting expression is a formula that describes a set of values that meet
the specified conditions.
• General Syntax:

• where, < x1, x2, x3, …, xn > represents resulting domains variables and P (x1,
x2, x3, …, xn ) represents the condition or formula equivalent to the Predicate
calculus
Example : Now, we will take the database table Employee and try to apply
the Domain Relational Calculus expression on that table.

Emp_id Emp_name Department

101 Naimish Computer

102 Sahil Finance
103 Divyesh Computer
104 Nikunj Account Emp_id Emp_name Department
105 Gautam Computer 101 Naimish Computer
106 Vishal HR 103 Divyesh Computer
107 Meet Business
105 Gautam Computer
108 Milan Business