1
Unit-2 (Part-1) Notes
Relational Model in DBMS
Relational model makes the query much easier than in hierarchical or network database systems. In
1970, E.F Codd has been developed it. 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. It is well knows in database technology because it is usually used to represent
real-world objects and the relationships between them. Some popular relational databases are used
nowadays like Oracle, Sybase, DB2, MySQL Server etc.
Relational Model Terminologies:
Following are the terminologies of Relational Model:
Relation Table
Tuple Row, Record
Attribute Column, Field
Domain It consists of set of legal values
Cardinality It consists of number of rows
Degree It contains number of columns
Let's explain each term one by one in detail with the help of example:
Example: STUDENT Relation
Stu_No S_Name PHONE_NO ADDRESS Gender
10112 Rama 9874567891 Islam ganj F
12839 Shyam 9026288936 Delhi M
33289 Laxman 8583287182 Gurugram M
27857 Mahesh 7086819134 Ghaziabad M
17282 Ganesh 9028939884 Delhi M
Relation: A relation is usually represented as a table, organized into rows and columns. A relationship
consists of multiple records. For example: student relation which contains tuples and attributes.
2
Tuple: The rows of a relation that contain the values corresponding to the attributes are called
tuples. For example: in the Student relation there are 5 tuples.
The value of tuples contains (10112, Rama, 9874567891, islam ganj, F) etc.
Data Item: The smallest unit of data in the relation is the individual data item. It is stored at the
intersection of rows and columns are also known as cells. For Example: 10112, "Rama" etc are data
items in Student relation.
Domain: It contains a set of atomic values that an attribute can take. It could be accomplished explicitly
by listing all possible values or specifying conditions that all values in that domain must be
confirmed. For example: the domain of gender attributes is a set of data values "M" for male and "F"
for female. No database software fully supports domains typically allowing the users to define very
simple data types such as numbers, dates, characters etc.
Attribute: The smallest unit of data in relational model is an attribute. It contains the name of a column
in a particular table. Each attribute Ai must have a domain, dom(Ai). For example: Stu_No, S_Name,
PHONE_NO, ADDRESS, Gender are the attributes of a student relation. In relational databases a column
entry in any row is a single value that contains exactly one item only.
Cardinality: The total number of rows at a time in a relation is called the cardinality of that relation. For
example: In a student relation, the total number of tuples in this relation is3 so the cardinality of a
relation is 3. The cardinality of a relation changes with time as more and more tuples get added or
deleted.
Degree: The degree of association is called the total number of attributes in a relationship. The relation
with one attribute is called unary relation, with two attributes is known a binary relation and with three
attributes is known as ternary relation. For example: in the Student relation, the total number of
attributes is 5, so the degree of the relations is 5. The degree of a relation does not change with time
as tuples get added or deleted.
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.
Integrity Constraints
o Integrity constraints are a set of rules. It is used to maintain the quality of information.
o 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.
o Thus, integrity constraint is used to guard against accidental damage to the database.
Types of Integrity Constraint
3
1. Domain constraints
o Domain constraints can be defined as the definition of a valid set of values for an attribute.
o 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.
Example:
2. Entity integrity constraints
o The entity integrity constraint states that primary key value can't be null.
o 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.
o A table can contain a null value other than the primary key field.
Example:
3. Referential Integrity Constraints
4
o A referential integrity constraint is specified between two tables.
o 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.
Example:
4. Key constraints
o Keys are the entity set that is used to identify an entity within its entity set uniquely.
o 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 and null value in the relational table.
Example:
Introduction of Relational Algebra in DBMS
Relational Algebra is a procedural query language. Relational algebra mainly provides a theoretical
foundation for relational databases and SQL. The main purpose of using Relational Algebra is to
define operators that transform one or more input relations into an output relation. Given that these
operators accept relations as input and produce relations as output, they can be combined and used
to express potentially complex queries that transform potentially many input relations (whose data
are stored in the database) into a single output relation (the query results). As it is pure mathematics,
5
there is no use of English Keywords in Relational Algebra and operators are represented using
symbols.
Fundamental Operators
These are the basic/fundamental operators used in Relational Algebra.
1. Selection(σ)
2. Projection(π)
3. Union(U)
4. Set Difference(-)
5. Set Intersection(∩)
6. Rename(ρ)
7. Cartesian Product(X)
1. Selection(σ): It is used to select required tuples of the relations.
Example:
A B C
1 2 4
2 2 3
3 2 3
4 3 4
For the above relation, σ(c>3)R will select the tuples which have c more than 3.
A B C
1 2 4
4 3 4
Note: The selection operator only selects the required tuples but does not display them.
For display, the data projection operator is used.
2. Projection(π): It is used to project required column data from a relation.
Example: Consider Table 1. Suppose we want columns B and C from Relation R.
π(B,C)R will show following columns.
B C
2 4
2 3
6
B C
3 4
Note: By Default, projection removes duplicate data.
3. Union(U): Union operation in relational algebra is the same as union operation in set
theory.
Example:
FRENCH
Student_Name Roll_Number
Ram 01
Mohan 02
Vivek 13
Geeta 17
GERMAN
Student_Name Roll_Number
Vivek 13
Geeta 17
Shyam 21
Rohan 25
Consider the following table of Students having different optional subjects in their
course.
π(Student_Name)FRENCH U π(Student_Name)GERMAN
Student_Name
Ram
Mohan
Vivek
7
Student_Name
Geeta
Shyam
Rohan
Note: The only constraint in the union of two relations is that both relations must have
the same set of Attributes.
4. Set Difference(-): Set Difference in relational algebra is the same set difference
operation as in set theory.
Example: From the above table of FRENCH and GERMAN, Set Difference is used as
follows
π(Student_Name)FRENCH - π(Student_Name)GERMAN
Student_Name
Ram
Mohan
Note: The only constraint in the Set Difference between two relations is that both
relations must have the same set of Attributes.
5. Set Intersection(∩): Set Intersection in relational algebra is the same set intersection
operation in set theory.
Example: From the above table of FRENCH and GERMAN, the Set Intersection is used
as follows
π(Student_Name)FRENCH ∩ π(Student_Name)GERMAN
Student_Name
Vivek
Geeta
Note: The only constraint in the Set Difference between two relations is that both
relations must have the same set of Attributes.
6. Rename(ρ): Rename is a unary operation used for renaming attributes of a relation.
ρ(a/b)R will rename the attribute 'b' of the relation by 'a'.
7. Cross Product(X): Cross-product between two relations. Let’s say A and B, so the
cross product between A X B will result in all the attributes of A followed by each
attribute of B. Each record of A will pair with every record of B.
Example:
8
A
Name Age Sex
Ram 14 M
Sona 15 F
Kim 20 M
B
ID Course
1 DS
2 DBMS
AXB
Name Age Sex ID Course
Ram 14 M 1 DS
Ram 14 M 2 DBMS
Sona 15 F 1 DS
Sona 15 F 2 DBMS
Kim 20 M 1 DS
Kim 20 M 2 DBMS
Note: If A has ‘n’ tuples and B has ‘m’ tuples then A X B will have ‘ n*m ‘ tuples.
Relational Calculus
There is an alternate way of formulating queries known as Relational Calculus. Relational calculus is a
non-procedural query language. In the non-procedural query language, the user is concerned with the
details of how to obtain the end results. The relational calculus tells what to do but never explains how
to do. Most commercial relational languages are based on aspects of relational calculus including SQL-
QBE and QUEL.
Why it is called Relational Calculus?
9
It is based on Predicate calculus, a name derived from branch of symbolic language. A predicate is a
truth-valued function with arguments. On substituting values for the arguments, the function result in
an expression called a proposition. It can be either true or false. It is a tailored version of a subset of
the Predicate Calculus to communicate with the relational database.
Tuple Relational Calculus (TRC) in DBMS
Tuple Relational Calculus (TRC) is a non-procedural query language used in relational
database management systems (RDBMS) to retrieve data from tables. TRC is based on
the concept of tuples, which are ordered sets of attribute values that represent a single
row or record in a database table.
TRC is a declarative language, meaning that it specifies what data is required from
the database, rather than how to retrieve it. TRC queries are expressed as logical
formulas that describe the desired tuples.
Syntax: The basic syntax of TRC is as follows:
{ t | P(t) }
where t is a tuple variable and P(t) is a logical formula that describes the conditions that
the tuples in the result must satisfy. The curly braces {} are used to indicate that the
expression is a set of tuples.
For example, let’s say we have a table called “Employees” with the following attributes:
Employee ID
Name
Salary
Department ID
To retrieve the names of all employees who earn more than $50,000 per year, we can
use the following TRC query:
{ t | Employees(t) ∧ t.Salary > 50000 }
In this query, the “Employees(t)” expression specifies that the tuple variable t represents
a row in the “Employees” table. The “∧” symbol is the logical AND operator, which is
used to combine the condition “t.Salary > 50000” with the table selection.
The result of this query will be a set of tuples, where each tuple contains the Name
attribute of an employee who earns more than $50,000 per year.
TRC can also be used to perform more complex queries, such as joins and nested queries,
by using additional logical operators and expressions.
While TRC is a powerful query language, it can be more difficult to write and understand
than other SQL-based query languages, such as Structured Query Language (SQL).
10
However, it is useful in certain applications, such as in the formal verification of database
schemas and in academic research.
Tuple Relational Calculus is a non-procedural query language, unlike relational
algebra. Tuple Calculus provides only the description of the query but it does not provide
the methods to solve it. Thus, it explains what to do but not how to do it.
Tuple Relational Query
In Tuple Calculus, a query is expressed as
{t| P(t)}
where t = resulting tuples,
P(t) = known as Predicate and these are the conditions that are used to fetch t. Thus, it
generates a set of all tuples t, such that Predicate P(t) is true for t.
P(t) may have various conditions logically combined with OR (∨), AND (∧), NOT(¬).
It also uses quantifiers:
∃ t ∈ r (Q(t)) = ”there exists” a tuple in t in relation r such that predicate Q(t) is true.
∀ t ∈ r (Q(t)) = Q(t) is true “for all” tuples in relation r.
Domain Relational Calculus (DRC)
Domain Relational Calculus is similar to Tuple Relational Calculus, where it makes a list
of the attributes that are to be chosen from the relations as per the conditions.
{<a1,a2,a3,.....an> | P(a1,a2,a3,.....an)}
where a1,a2,…an are the attributes of the relation and P is the condition.
Tuple Relational Calculus Examples
Table Customer
Customer name Street City
Saurabh A7 Patiala
Mehak B6 Jalandhar
Sumiti D9 Ludhiana
Ria A5 Patiala
Table Branch
11
Branch name Branch City
ABC Patiala
DEF Ludhiana
GHI Jalandhar
Table Account
Account number Branch name Balance
1111 ABC 50000
1112 DEF 10000
1113 GHI 9000
1114 ABC 7000
Table Loan
Loan number Branch name Amount
L33 ABC 10000
L35 DEF 15000
L49 GHI 9000
L98 DEF 65000
Table Borrower
Customer name Loan number
Saurabh L33
Mehak L49
Ria L98
Table Depositor
Customer name Account number
Saurabh 1111
12
Customer name Account number
Mehak 1113
Suniti 1114
Example 1: Find the loan number, branch, and amount of loans greater than or equal to
10000 amount.
{t| t ∈ loan ∧ t[amount]>=10000}
Resulting relation:
Loan number Branch name Amount
L33 ABC 10000
L35 DEF 15000
L98 DEF 65000
In the above query, t[amount] is known as a tuple variable.
Example 2: Find the loan number for each loan of an amount greater or equal to 10000.
{t| ∃ s ∈ loan(t[loan number] = s[loan number]
∧ s[amount]>=10000)}
Resulting relation:
Loan number
L33
L35
L98
Example 3: Find the names of all customers who have a loan and an account at the
bank.
{t | ∃ s ∈ borrower( t[customer-name] = s[customer-name])
∧ ∃ u ∈ depositor( t[customer-name] = u[customer-name])}
Resulting relation:
Customer name
Saurabh
13
Customer name
Mehak
Example 4: Find the names of all customers having a loan at the “ABC” branch.
{t | ∃ s ∈ borrower(t[customer-name] = s[customer-name]
∧ ∃ u ∈ loan(u[branch-name] = “ABC” ∧ u[loan-number] = s[loan-number]))}
Resulting relation:
Customer name
Saurabh
