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.

Types of Relational calculus:

Tuple Relational Calculus (TRC)

It is a non-procedural query language which is based on finding a number of tuple variables

also known as range variable for which predicate holds true. It describes the desired
information without giving a specific procedure for obtaining that information. The tuple
relational calculus is specified to select the tuples in a relation. In TRC, filtering variable uses
the tuples of a relation. The result of the relation can have one or more tuples.
Notation:
A Query in the tuple relational calculus is expressed as following notation
{T | P (T)} or {T | Condition (T)}
Where
T is the resulting tuples
P(T) is the condition used to fetch T.

Example:
{ T.name | Author(T) AND T.article = 'database' }

Output: This query selects the tuples from the AUTHOR relation. It returns a tuple with
'name' from Author who has written an article on 'database'.

TRC (tuple relation calculus) can be quantified. In TRC, we can use Existential (∃) and
Universal Quantifiers (∀).

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.

{ R| ∃T ∈ Authors(T.article='database' AND R.name=T.name)}

Output: This query will yield the same result as the previous one.

2. Domain Relational Calculus (DRC)

The second form of relation is known as Domain relational calculus. In domain relational
calculus, filtering variable uses the domain of attributes. Domain relational calculus uses the
same operators as tuple calculus. It uses logical connectives ∧ (and), ∨ (or) and ┓ (not). It uses
Existential (∃) and Universal Quantifiers (∀) to bind the variable. The QBE or Query by
example is a query language related to domain relational calculus.

Notation:
{ a1, a2, a3, ..., an | P (a1, a2, a3, ... ,an)}
a1,a2 are attributes
P stands for formula built by inner attributes

For example:
{< article, page, subject > | ∈ javatpoint ∧ subject = 'database'}
Output: This query will yield the article, page, and subject from the relational javatpoint,
where the subject is a database.
Functional Dependency

The functional dependency is a relationship that exists between two attributes. It typically
exists between the primary key and non-key attribute within a table.

A functional dependency is a constraint that specifies the relationship between two sets of
attributes where one set can accurately determine the value of other sets. It is denoted as X
→ Y, where X is a set of attributes that is capable of determining the value of Y. The attribute
set on the left side of the arrow, X is called Determinant, while on the right side, Y is called
the Dependent.

1. X → Y

The left side of FD is known as a determinant, the right side of the production is known as a
dependent.

Here Emp_Id attribute can uniquely identify the Emp_Name attribute of employee table
because if we know the Emp_Id, we can tell that employee name associated with it.

Functional dependency can be written as:

1. Emp_Id → Emp_Name

Armstrong’s axioms/properties of functional dependencies:

1. Reflexivity: If Y is a subset of X, then X→Y holds by reflexivity rule

For example, {roll_no, name} → name is valid.
2. Augmentation: If X → Y is a valid dependency, then XZ → YZ is also valid by
the augmentation rule.
For example, If {roll_no, name} → dept_building is valid, hence {roll_no,
name, dept_name} → {dept_building, dept_name} is also valid.→
3. Transitivity: If X → Y and Y → Z are both valid dependencies, then X→Z is
also valid by the Transitivity rule.
For example, roll_no → dept_name & dept_name → dept_building, then roll_no
→ dept_building is also valid.

Types of Functional dependency

1. Trivial functional dependency

2. Non-Trivial functional dependency
 3. Multivalued functional dependency
4. Transitive functional dependency

1. Trivial Functional Dependency

In Trivial Functional Dependency, a dependent is always a subset of the determinant.
i.e. If X → Y and Y is the subset of X, then it is called trivial functional dependency

For example,
roll_no Name age

42 Abc 17

43 Pqr 18

44 Xyz 18

Here, {roll_no, name} → name is a trivial functional dependency, since the

dependent name is a subset of determinant set {roll_no, name}
Similarly, roll_no → roll_no is also an example of trivial functional dependency.

2. Non-trivial Functional Dependency

In Non-trivial functional dependency, the dependent is strictly not a subset of the
determinant.
i.e. If X → Y and Y is not a subset of X, then it is called Non-trivial functional
dependency.
For example,
roll_no Name age

42 Abc 17

43 Pqr 18

44 Xyz 18

Here, roll_no → name is a non-trivial functional dependency, since the

dependent name is not a subset of determinant roll_no
Similarly, {roll_no, name} → age is also a non-trivial functional dependency,
since age is not a subset of {roll_no, name}

3. Multivalued Functional Dependency

In Multivalued functional dependency, entities of the dependent set are not
dependent on each other.
i.e. If a → {b, c} and there exists no functional dependency between b and c, then it is
called a multivalued functional dependency.
For example,
 roll_no Name age

42 Abc 17

43 Pqr 18

44 Xyz 18

45 Abc 19

Here, roll_no → {name, age} is a multivalued functional dependency, since the

dependents name & age are not dependent on each other(i.e. name → age or age →
name doesn’t exist !)

4. Transitive Functional Dependency

In transitive functional dependency, dependent is indirectly dependent on determinant.
i.e. If a → b & b → c, then according to axiom of transitivity, a → c. This is a transitive
functional dependency
For example,
enrol_no Name dept building_no

42 Abc CO 4

43 Pqr EC 2

44 Xyz IT 1

45 Abc EC 2

Here, enrol_no → dept and dept → building_no,

Hence, according to the axiom of transitivity, enrol_no → building_no is a valid
functional dependency. This is an indirect functional dependency, hence called Transitive
functional dependency.

Full Functional Dependency

A functional dependency denoted as X \right arrow YX→Y where XX and YY are an attribute
set of a relation, is a full dependency, if all the attributes present in XX are required to
maintain the dependency.

An attribute is fully functional dependent on another attribute, if it is Functionally Dependent

on that attribute and not on any of its proper subset.
For example, an attribute Q is fully functional dependent on another attribute P, if it is
Functionally Dependent on P and not on any of the proper subset of P.
<ProjectCost>

ProjectID ProjectCost
001 1000
002 5000
<EmployeeProject>

EmpID ProjectID Days (spent on the project)

E099 001 320
E056 002 190
The above relations states:

EmpID, ProjectID, ProjectCost -> Days

However, it is not fully functional dependent.
Whereas the subset {EmpID, ProjectID} can easily determine the {Days} spent on the project
by the employee.
This summarizes and gives our fully functional dependency −

{EmpID, ProjectID} -> (Days)

Partial Dependency
Partial Dependency occurs when a nonprime attribute is functionally dependent on part of a
candidate key.
The 2nd Normal Form (2NF) eliminates the Partial Dependency. Let us see an example −
<StudentProject>

StudentID ProjectNo StudentName ProjectName

S01 199 Katie Geo Location

S02 120 Ollie Cluster Exploration

In the above table, we have partial dependency; let us see how −

The prime key attributes are StudentID and ProjectNo.
As stated, the non-prime attributes i.e. StudentName and ProjectName should be
functionally dependent on part of a candidate key, to be Partial Dependent.
The StudentName can be determined by StudentID that makes the relation Partial
Dependent.
The ProjectName can be determined by ProjectID, which that the relation Partial Dependent.
What is Normalization?
o Normalization is the process of organizing the data in the database.
o Normalization is used to minimize the redundancy from a relation or set of relations. It
is also used to eliminate undesirable characteristics like Insertion, Update, and Deletion
Anomalies.
o Normalization divides the larger table into smaller and links them using relationships.
o The normal form is used to reduce redundancy from the database table.

Why do we need Normalization?

The main reason for normalizing the relations is removing these anomalies. Failure to eliminate
anomalies leads to data redundancy and can cause data integrity and other problems as the
database grows. Normalization consists of a series of guidelines that helps to guide you in
creating a good database structure.

Data modification anomalies can be categorized into three types:

o Insertion Anomaly: Insertion Anomaly refers to when one cannot insert a new tuple
into a relationship due to lack of data.
o Deletion Anomaly: The delete anomaly refers to the situation where the deletion of
data results in the unintended loss of some other important data.
o Updating Anomaly: The update anomaly is when an update of a single data value
requires multiple rows of data to be updated.

Types of Normal Forms:

Normalization works through a series of stages called Normal forms. The normal forms apply
to individual relations. The relation is said to be in particular normal form if it satisfies
constraints.

First Normal Form –

If a relation contain composite or multi-valued attribute, it violates first normal form or a
relation is in first normal form if it does not contain any composite or multi-valued
attribute. A relation is in first normal form if every attribute in that relation is singled
valued attribute.

Second Normal Form –

To be in second normal form, a relation must be in first normal form and relation must not
contain any partial dependency. A relation is in 2NF if it has No Partial
Dependency, i.e., no non-prime attribute (attributes which are not part of any candidate
key) is dependent on any proper subset of any candidate key of the table.
Partial Dependency – If the proper subset of candidate key determines non-prime
attribute, it is called partial dependency.
Third Normal Form –
A relation is in third normal form, if there is no transitive dependency for non-prime
attributes as well as it is in second normal form.
A relation is in 3NF if at least one of the following condition holds in every non-trivial
function dependency X –> Y
1. X is a super key.
2. Y is a prime attribute (each element of Y is part of some candidate key).
3. Transitive dependency – If A->B and B->C are two FDs then A->C is
called transitive dependency.

Boyce-Codd Normal Form (BCNF) –

A relation R is in BCNF if R is in Third Normal Form and for every FD, LHS is
super key. A relation is in BCNF iff in every non-trivial functional dependency X –
> Y, X is a super key.
o BCNF is the advance version of 3NF. It is stricter than 3NF.
o A table is in BCNF if every functional dependency X → Y, X is the super key of
the table.
o For BCNF, the table should be in 3NF, and for every FD, LHS is super key.

NF RULES
• Each table cell should contain a single value.
1NF • Each record needs to be unique.

• Rule 1- Be in 1NF
2NF • Rule 2- Single Column Primary Key that does not functionally dependant
on any subset of candidate key relation

• Rule 1- Be in 2NF
3NF • Rule 2- Has no transitive functional dependencies

• Even when a database is in 3rd Normal Form, still there would be

BCNF anomalies resulted if it has more than one Candidate Key.
• Sometimes is BCNF is also referred as 3.5 Normal Form.

• If no database table instance contains two or more, independent and

4NF multivalued data describing the relevant entity, then it is in 4th Normal
Form.

• A table is in 5th Normal Form only if it is in 4NF and it cannot be

5NF decomposed into any number of smaller tables without loss of data.
• A relation is in 5NF. If it is in 4NF and does not contain any join
dependency, joining should be lossless.
Fourth normal form or 4NF:
4NF is nothing but the next level of BCNF. While the 2NF, 3NF, and BCNF are concerned
with functional dependencies, 4NF is concerned with multivalued dependency.
Let R be the relational schema F be the single and multivalued dependency X->-> is in 4NF
if:
• X is a candidate key or a super key of the relation,
or
• X union Y = R
Relational Decomposition

o When a relation in the relational model is not in appropriate normal form then the
decomposition of a relation is required.
o In a database, it breaks the table into multiple tables.
o If the relation has no proper decomposition, then it may lead to problems like loss of
information.
o Decomposition is used to eliminate some of the problems of bad design like anomalies,
inconsistencies, and redundancy.

Types of Decomposition
Lossless Decomposition
o If the information is not lost from the relation that is decomposed, then the
decomposition will be lossless.
o The lossless decomposition guarantees that the join of relations will result in the same
relation as it was decomposed.
o The relation is said to be lossless decomposition if natural joins of all the decomposition
give the original relation.

Dependency Preserving
o It is an important constraint of the database.
o In the dependency preservation, at least one decomposed table must satisfy every
dependency.
o If a relation R is decomposed into relation R1 and R2, then the dependencies of R either
must be a part of R1 or R2 or must be derivable from the combination of functional
dependencies of R1 and R2.
o For example, suppose there is a relation R (A, B, C, D) with functional dependency set
(A->BC). The relational R is decomposed into R1(ABC) and R2(AD) which is
dependency preserving because FD A->BC is a part of relation R1(ABC).
Equivalence of Functional Dependencies

If there are given two different sets of functional dependencies for a relation, then these two may or
may not be equivalent. Suppose F & G are the two sets of functional dependencies for a relational
schema R, then following four cases may possible:

F ⊆ G (F is the subset of G)

G ⊆ F (G is the subset of F)

F = G (F ⊆ G and G ⊆ F) (F is equivalent to G)

F ≠ G (F is not equivalent to G)

Steps to check equivalence of two different set of functional dependencies

Suppose F & G are two set of FDs in a relational schema R:

Step 1: Find the closure of attributes that are determinant attribute (left side attributes) in FD set F,
but important point is that the closures are supposed to calculate using FDs given in set G.

Step 2: Now check for each FD in F, the determinant and dependent attribute in FD set F are same as
calculated in closure. If same, and since closure is calculated using FD set G, it concludes that F is
subset of G (F ⊆ G) because G can determine all those attributes that are determined by F. If not
same F G.

Step 3: Likewise calculate whether G ⊆ F or not Using above, one of the following will come: I. F ⊆ G
but G F II. G ⊆ F but F G III. F ⊆ G and G ⊆ F i.e. F = G IV. F G and G F i.e. F ≠ G