Database Management Systems UNIT-III

Introduction

After designing the conceptual model of Database using ER diagram, we need

to convert the conceptual model in to

relational model which can be implemented using any RDBMS languages like
Oracle SQL, MySQL etc.

3.1 What is Relational Model?

It was proposed by E.F. Codd in 1970 to model data in the form of relations or
tables since then it was the most widely used data model and in fact, the only used

database management system today in the world.

It represents how data is stored in Relational Databases. A relational model

stores data in the form of relations (tables). Each relation has columns and rows

which are formally called attributes and tuples respectively. Each tuple in relation is
a real-world entity.

Relational Model Diagram

The basic structure of a relational model is tables. So, the tables are also
called relations in the relational model.

The figure below will help you identify the relation, attributes, and tuples in a

relational model.

It is an Employee relation and it is having entries of 6 employees (tuples) in it.

Ch. Vijayananda Ratnam @Dept. of CSE 1

Database Management Systems UNIT-III
Terminology
― Relations / Tables – Relations are saved in the table format. A table has two

properties rows and columns. Rows represent records and columns represent
attributes.

― Attribute: Attributes are the properties which define a relation.

― Tuple: It is nothing but a single row of a table.

― Degree / arity: The total number of attributes in a relation.

― Cardinality: Total number of rows present in the Table.

― Domain: Set of possible values for an attribute.

― Relation instance: It is a finite set of tuples. They never have duplicate tuples.

― Relation Schema: It represents the name of the relation with its attributes.
― Relation key: Every row has one, two or multiple attributes, which is called

relation key.

Characteristics

― Each relation in a database must have a distinct or unique name.

― Each attribute must have a distinct name (no two attributes should have

the same name).

― Duplicate tuples must not be present in a relation.

― Each tuple must have exactly one data value for an attribute.
― No ordering is required for tuples, similarly for attributes also.

Advantages

― Simplicity: This model is simpler as compared to the network and hierarchical

model.
― Scalable: This model can be easily scaled as we can add as many rows and

columns we want.
― Structural Independence: We can make changes in database structure

without changing the way to access the data. When we can make changes to
the database structure without affecting the capability to DBMS to access the
data we can say that structural independence has been achieved.

Ch. Vijayananda Ratnam @Dept. of CSE 2

Database Management Systems UNIT-III
― Easy to use: This model in DBMS is easy as tables consisting of rows and
columns are quite natural and simple to understand.

― Query capability: It makes possible for a high-level query language like SQL
to avoid complex database navigation.

― Data independence: The Structure of Relational database can be changed

without having to change any application.

Disadvantages

― Few relational databases have limits on field lengths which can't be exceeded.
― Relational databases can sometimes become complex as the amount of data

grows, and the relations between pieces of data become more complicated.
― Complex relational database systems may lead to isolated databases where

the information cannot be shared from one system to another.

3.2 Integrity Constraints

They are referred to as conditions that are applied to the relation/table to

ensure the correctness of the data in the database. These constraints are checked

before performing any operation in database. If there is a violation in any of

constrains, operation will fail. Thus, integrity constraint is used to guard against

accidental damage to the database.

There are mostly divided into three main categories are:

1. Domain Constraints
2. Entity Integrity Constraints

3. Key Constraints
4. Referential Integrity Constraints

1. Domain Constraint
― Domain constraint defines the domain or set of possible values for an

attribute.
― It specifies that the value taken by the attribute must be from its domain only.
― Example: Consider the following Student table

Ch. Vijayananda Ratnam @Dept. of CSE 3

Database Management Systems UNIT-III
Roll_no Name Age

S001 Akshay 20

S013 Krishna 23

S056 Vivek Z

S054 Raghu 21
― Here, value ‘Z’ is not allowed since only positive integer values can be taken
by the age attribute.

2. Entity Integrity constraint

Entity integrity constraint ensures that the primary key attribute in a relation,
should not accept a null value. This is because the primary key attribute value

uniquely defines an entity in a relation.

Example: Consider the following Student table

Roll_no Name Age

S001 Akshay 20

S013 Krishna 23

S056 Vivek 20

Raghu 21

Not allowed, as primary key can’t contain a NULL value.

3. Referential Integrity Constraints

A referential integrity constraint is specified between two tables to establish a

relationship/link between them. They are implemented in the name of foreign keys.

It states that if a foreign key exists in a relation, then either the foreign key value
must match a primary key value of some tuple in its parent relation or its value

must be null.
The rules are:
1. You can't delete a record from a primary table if matching records exist in a
child table.

Ch. Vijayananda Ratnam @Dept. of CSE 4

Database Management Systems UNIT-III
2. You can't change a primary key value in the primary table if that record has
child records.

3. You can't enter a value in the foreign key field of the child table that doesn't
exist in the primary key of the parent table.

4. However, you can enter a Null value in the foreign key, specifying that the
records are unrelated.

Example
Consider 2 relations “Dept" and “Emp" where “D_No" is the primary key in the
“Dept" relation and foreign key in the “Emp" relation.

In this, if a foreign key in Emp table refers to the Primary Key of Dept table, then
every value of the Foreign Key in Emp table must be null or be available in Dept
Table.

4. Key Constraints

A Key Constraint is a statement that a certain minimal subset of the fields of a

relation is a unique identifier for a tuple.

There are 4 types of key constraints-

1. Super Key

2. Candidate key.
3. Primary key
4. Foreign key.

Ch. Vijayananda Ratnam @Dept. of CSE 5

Database Management Systems UNIT-III
Example: Let us understand the key constraints with the two relations STUDENT and
STUDENT-COURSE.

Relation: STUDENT
STUD_NO NAME AGE MOBILE_NO
Relation: STUDENT-COURSE
512 Akshara 20 9876723452
STUD_NO COURSE_NO COURSE_NAME
513 Krishna 23 9991165674
512 C1 DBMS
546 Vivek 20 7898756543
546 C2 PYTHON
557 Raghu 21 8987867898
512 C2 PYTHON
595 Krishna 22 9876543210

1. Candidate Key
― The minimal set of attributes which can uniquely identify a tuple is

known as a candidate key. For example, STUD_NO in STUDENT relation.

― A candidate key can never be NULL or empty and its value should be
unique.

― There can be more than one candidate keys for table. For example,
STUD_NO, as well as MOBILE_NO both, are candidate keys for relation

STUDENT.
― The candidate key can be simple or composite. For Example, {STUD_NO,

COURSE_NO} is a composite candidate key for relation STUDENT_COURSE.

2. Super Key

― The set of attributes which can uniquely identify a tuple is known as Super
Key. For Example, STUD_NO, (STUD_NO, STUD_NAME), MOBILE_NO, etc.

― Adding zero or more attributes to candidate key generates a super key.

― Super Key is a super set of Candidate key.

― A candidate key is a super key but vice versa is not true.

3. Primary Key

There can be more than one candidate key in a relation out of which one can
be chosen as a primary key.
For Example, STUD_NO as well as MOBILE_NO both are candidate keys for
relation STUDENT but STUD_NO can be chosen as primary key.
Ch. Vijayananda Ratnam @Dept. of CSE 6
Database Management Systems UNIT-III
4. Secondary or Alternative key
The candidate key other than the primary key is called an alternate key / Secondary

key.
For Example, STUD_NO, as well as MOBILE_NO both, are candidate keys for relation

STUDENT but STUD_PHONE will be alternate key.

5. 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 to the attribute to which it refers.

The relation which is being referenced is called referenced relation and the
corresponding attribute is called referenced attribute and the relation which refers

to the referenced relation is called referencing relation and the corresponding

attribute is called referencing attribute.

The referenced attribute of the referenced relation should be the primary key

for it. For Example, STUD_NO in STUDENT_COURSE is a foreign key to STUD_NO in

STUDENT relation.

Foreign key can be NULL as well as may contain duplicate tuples i.e., it need not

follow uniqueness constraint.

For Example, STUD_NO in STUDENT_COURSE relation is not unique. It has been

repeated for the first and third tuple. However, the STUD_NO in STUDENT relation is

a primary key and it needs to be always unique and it cannot be null.

3.3 Operations in Relational Model

Four basic operations performed on relational database model are

― Insert is used to insert data into the relation

― Delete is used to delete tuples from the relation.

― Modify allows you to change the values of some attributes in existing tuples.

― Select allows you to retrieve a specific range of data.

Whenever one of these operations are applied, integrity constraints specified on the
relational database schema must never be violated.

Ch. Vijayananda Ratnam @Dept. of CSE 7

Database Management Systems UNIT-III
3.4 E – R Diagram to Relations/Tables
There are some points for converting the ER diagram to the table:

1. Entity type becomes a table.

2. All single-valued attribute becomes a column for the table.

3. A key attribute of the entity type represented by the primary key.

4. Composite attribute represented by components.

5. The multivalued attribute is represented by a separate table.

6. Derived attributes are not considered in the table.

Let us convert the College Management System E-R Model into Relations by
following the above rules.

Fig. E-R Model for College Management System

Ch. Vijayananda Ratnam @Dept. of CSE 8

Database Management Systems UNIT-III
Table structure for the given ER diagram is as below:

Relational Algebra
3.5 What is query language?

A language which is used to store and retrieve data from database.

(or)
It is a specialized language for asking questions, or queries, that involve the data in a
database.

There are two types of query language:

1. Procedural Query language

2. Non-procedural Query language

Procedural Query language

― It describes step-by-step procedure for computing the desired answer.

― Queries are specified in an operational manner. Useful for representing

execution plans.
― Example: Relational Algebra - conceptual procedural query language used

on relational model.
Ch. Vijayananda Ratnam @Dept. of CSE 9
Database Management Systems UNIT-III
Non - procedural Query language:
― It describes the desired answer without specifying how the answer is to be

computed.
― Non - operational, declarative.

― Example: Relational Calculus - conceptual non-procedural query language

used on relational model.

For example – Let’s take a real-world example to understand the procedural

language, you are asking your younger brother to make a cup of tea, if you are just

telling him to make a tea and not telling the process then it is a non-procedural
language, however if you are telling the step-by-step process like switch on the

stove, boil the water, add milk etc. then it is a procedural language.

― Relational algebra and calculus are the theoretical concepts used on

relational model.

― RDBMS is a practical implementation of relational model.

― SQL is a practical implementation of relational algebra and calculus.

3.6 What is Relational Algebra?

Relational algebra is a procedural query language that works on relational

model. The purpose of a query language is to retrieve data from database or perform

various operations such as insert, update, delete on the data. When I say that
relational algebra is a procedural query language, it means that it tells what data to
be retrieved and how to be retrieved.

Ch. Vijayananda Ratnam @Dept. of CSE 10

Database Management Systems UNIT-III
Types of operations

Two types of operations in relational algebra, they are:

― Basic Operations

1. Select (σ)
2. Project (∏)

3. Union (∪)
4. Set Difference (-)

5. Cartesian product (X)

6. Rename (ρ)

― Derived Operations
1. Natural Join (⋈)

2. Left, Right, Full outer join (⟕, ⟖, ⟗)

3. Set Intersection (∩)

4. Division (÷)

3.7 Basic Operations

1. Select Operator (σ)

It is denoted by sigma (σ), and it is used to find the tuples (or rows) in a relation (or

table) which satisfy the given condition.

Syntax of Select Operator (σ):

σ Condition/Predicate (Relation / Table_name)

Where

― σ indicates selection predicate

― Predicate/Condition is a propositional logic formula which may use relational

operators like and, or, and not.

Example:

σ account_type = ‘saving’ (Account)

Ch. Vijayananda Ratnam @Dept. of CSE 11

Database Management Systems UNIT-III
Output – It selects tuples from relation Account where the account type is
‘saving’.
2. Projection (∏)
It is denoted by pi (∏) symbol, and it is used to select desired columns (or

attributes) from a table (or relation). It is like the Select statement in SQL and syntax
is:

∏ column_name1, column_name2, ..., column_nameN (Relation/ Table_name)

Where Column_name1, Column_name2, Column_nameN are attributes of Relation.

Example: We have a relation STUDENT with four columns, we want to fetch only

two columns of the table, which we can do with the help of projection operator ∏.
Query:

∏ std_name, std_city (Student)

Output: It selects attributes std_name and std_city from relation Student.

3. Union (∪)

It is used to select all the rows (tuples) from two tables (relations). It is denoted by
symbol ∪. Its syntax is:

R1 ∪ R2

Where R1 and R2 are the relations.

For example, in r1 ∪ r2, the union of two relations r1 and r2 produces an output

relation that contains all the tuples of r1, or r2, or both r1 and r2, duplicate tuples
being eliminated.

For a union operation to be valid, the following conditions must hold on to both
relations –

― Both Tables must be union compatible

o i.e., same set of attributes + domains.

― Duplicate tuples should be automatically removed.

Example 1: Consider the two relations R and S, each with two attributes A and B.
Ch. Vijayananda Ratnam @Dept. of CSE 12
Database Management Systems UNIT-III

Example 2: To find all customers with either an account or a loan

∏customer_name (Depositor) ∪ ∏customer_name (Borrower)

Output: It gives the customer_name from both relation Depositor and Borrower by

eliminating duplication.

4. Set Difference (−)

It is denoted by ― symbol. Let us say, we have two relation R and S, then the
set difference operation R – S, produces a relation consisting of the tuples that are in

relation R, but not in S. Both the relations R and S must be union compatible. The
syntax is:

Relation_name1 ― Relation_name2
Example 1: Consider the two relations R and S, each with two attributes A and B.

Example 2: To find only depositors who are not taking any loan.

Ch. Vijayananda Ratnam @Dept. of CSE 13

Database Management Systems UNIT-III
∏customer-name (Depositor) ― ∏customer-name (Borrower)
Output: It gives the customer_name which are present in relation Depositor but not

in relation Borrower.

5. Cartesian product (X)

It is denoted by X symbol. It is also known as Cross product. Cartesian product

of the two relations (R X S) would combine each tuple of first relation R with every
tuple of second relation S and its syntax is:

Relation_name1 X Relation_name2
Note: The result set contains attributes of both the relations.

Example 1: Consider the two relations R and S, each with one attribute A and B
respectively.

1. The cardinality (number of tuples) of Cross Product is m*n where m is number

of tuples in the first relation and n is number of tuples in the second relation.

2. The degree (number of attributes) of Cross Product is p+q where p is number

of attributes in the first relation and q is number of attributes in the second

relation.
For the above result set,

Cardinality = 2*3 =>6

Degree = 1+1 => 2

Ch. Vijayananda Ratnam @Dept. of CSE 14

Database Management Systems UNIT-III
3. Generally, we use Cartesian product followed by a Selection operation and
comparison on the operators as shown below:

σ condition (R ✕ S)
The above query gives meaningful results.

Example 2:
σ city = ‘Kolkata’ (Depositor Χ Borrower)

Output – It selects all tuples from both relations Depositor and Borrower where city
is Kolkata.

6. Set Intersection (∩)

It is denoted by ∩ symbol and it is used to select common rows (tuples) from

two tables (relations).

Let’s say we have two relations R and S both have same columns, and

we want to select all those tuples (rows) that are present in both the relations, then in
that case we can apply intersection operation on these two relations R ∩ S.

Note: Only those rows that are present in both the relations will appear in the result
set.

Syntax:
Relation_name1 ∩ Relation_name2
For an intersection operation to be valid, the following conditions must hold on both
relations –

• Both Relations must be union compatible

o i.e., same set of attributes + domains.

Example 1: Consider the two relations R and S, each with two attributes A and B.

Ch. Vijayananda Ratnam @Dept. of CSE 15

Database Management Systems UNIT-III
Example 2: Display customer names who is depositor as well as borrower.

∏ customer-name (Depositor) Ո ∏ customer-name (Borrower)

Output: It gives the customer_name which are present in both the relations.

7. Rename (ρ)
The results of relational algebra operations are always the relations, but they

are without any name. The rename operation allows user to rename the output
relation. It is denoted by using ρ (rho) and its syntax is:

ρ x (E)
Where E is the expression with different relational algebra operations and x is the
name given to their result.

Example:
Let’s say we have a relation CUSTOMER, we are fetching customer names and

we are renaming the resulted relation to CUST_NAMES.

ρ (CUST_NAMES, ∏ (Customer_Name) (CUSTOMER))

Output – The output relation from the expression, ∏ (Customer_Name) (CUSTOMER)

rename with CUST_NAMES.

8. Division operation ( / )

Consider two relation instances R and S in which R has (exactly) two fields x
and y and S has just one field y, with the same domain as in R.

We define the division operation R / S as the set of all x values such that for
every y value in S, there is a tuple ‹x, y › in R.

Example that illustrates division operation:

Ch. Vijayananda Ratnam @Dept. of CSE 16

Database Management Systems UNIT-III

3.8 Derived Operations

Join (⋈)

One of the most useful operations in relational algebra. This is the most
common way to combine information from two or more relations. It defined as a

cartesian product followed by selections and projections. A Join operation

combines two tuples from two different relations, if and only if a given condition

is satisfied. Thus, join is a subset of cartesian product.

There are mainly two types of joins which are further divided as follows:
1. Inner Join

a. Natural join.
b. Theta join.

c. Equi join.
2. Outer join

a. Left Outer Join.

b. Right Outer Join.

c. Full Outer Join.

Inner Joins

In an inner join, only those tuples that satisfy the matching criteria are included, while
the rest are excluded.
Ch. Vijayananda Ratnam @Dept. of CSE 17
Database Management Systems UNIT-III
1. Natural Join (⋈)
Natural join can only be performed if there is a common attribute

(column) between the relations. The name and type of the attribute must be same,
and its syntax is:

R⋈S
Where R an S are the two relations, joined using natural join.

Example: We have two relations of S (num, square) and C (num, cube). Now, we
will perform natural join on both the relations i.e.,

∏ num, square, cube(S⋈C)

Output: The result of natural join is the set of tuples of all combinations in S and C that are
equal on their common attribute names.

2. Theta (θ) Join

It is denoted by the symbol θ and it combines those tuples from different relations
which satisfies the condition. It can use any conditions in the selection criteria such as <, >,
>=, <=, etc. Its syntax is
R1 ⋈θ R2
Where R1 and R2 are relations such that they don't have any common attribute. It means R1
∩ R2 = Φ.

Example: We have two relations of Student (sid, sname, std) and Course (class, subject).
Now, we will perform theta join on both the relations i.e.,

Ch. Vijayananda Ratnam @Dept. of CSE 18

Database Management Systems UNIT-III

STUDENT ⋈student.std ≥ course.class COURSE

Output: The result of theta join is the set of tuples of all combinations in Student

and Course that are satisfying the given condition.

3. EQUI join

When a theta join uses only equivalence condition, it becomes an equi-join.

STUDENT ⋈student.std = course.class COURSE

Output:

OUTER JOIN

In outer join, along with tuples that satisfy the matching criteria, we also
include some or all tuples that do not match the criteria.

1. Left Outer Join (⟕)

This join returns all the tuples of the left relation and matching tuples for the

right relation. However, if there is no matching tuple is found in right relation, then
the attributes of right relation in the join result are filled with NULL values.

Syntax is:
R1 ⟕ R2

where R1 and R2 are relations.

Fig. Venn Diagram for Left join

Ch. Vijayananda Ratnam @Dept. of CSE 19

Database Management Systems UNIT-III
Example: We have two relations of S (num, square) and C (num, cube). Now, we
will perform left outer join on both the relations i.e.,

S⟕C

Output:

2. Right Outer Join (⟖)

This join returns all the tuples of the right relation and matching tuples for the

left relation. However, if there is no matching tuple is found in left relation, then the
attributes of left relation in the join result are filled with NULL values.

Syntax is:
R1 ⟖ R2
where R1 and R2 are relations.

Fig. Venn Diagram for Right join.

Example: By considering the previous relations, we obtain the following result when

we apply the right outer join on both relations. i.e.,

S⟖C
Output:

Ch. Vijayananda Ratnam @Dept. of CSE 20

Database Management Systems UNIT-III

3. Full Outer Join (⟗)

In a full outer join, all tuples from both relations are included in the result,
irrespective of the matching condition. The rows for which there is no matching, the

result-set will contain NULL.

Syntax is:

R1 ⟗ R2 Where R1 and R2 are relations.

Fig. Venn Diagram for Full join.

Example: By considering the previous relations, we obtain the following result when
we apply the full join on both relations. i.e.,

S⟗C
Output:

3.9 Relational Calculus

It is a non-procedural query language that tells the system what data to be

retrieved but doesn’t tell how to retrieve it. It only focusses on what to do, and not
on how to do it. It exists in two forms:

1. Tuple Relational Calculus (TRC)

2. Domain Relational Calculus (DRC)
Ch. Vijayananda Ratnam @Dept. of CSE 21
Database Management Systems UNIT-III

1. Tuple Relational Calculus (TRC)

It is used for selecting those tuples that satisfy the given condition. In Tuple Calculus,
a query is expressed as:

{t | P(t)}
Where

t = resulting tuples
P(t) = known as Predicate or is the condition used to fetch t.

― P(t) may have various conditions logically combined with OR (∨), AND (∧),
NOT(¬).

For example:
{ T.name | Student (T) AND T.address = 'Guntur' }

Output:
This query selects the tuples from the STUDENT relation. It returns a tuple with

'name' from Student whose address is 'guntur'.

2. Domain Relational Calculus (DRC)

In domain relational calculus, filtering is done based on the domain of the attributes
and not based on the tuple values. In Domain Calculus, a query is expressed as:
{ c1, c2, c3, ..., cn | P (c1, c2, c3, ... ,cn) }
Where

c1, c2... etc represents domain of attributes(columns).

P defines the formula including the condition for fetching the data.

For example,
{< name, address > | ∈ Student ∧ address = 'guntur’}

This query selects the tuples from the STUDENT relation. It returns attributes name ,
address from Student whose address is 'guntur'.

Ch. Vijayananda Ratnam @Dept. of CSE 22

Database Management Systems UNIT-III
Example:
Consider the Sailors-Boats-Reserves database described in the text.

Sailors (sid: integer, sname: string, rating: integer, age: real)

Boats (bid: integer, bname: string, color: string)

Reserves (sid: integer, bid: integer, day: date)

Write each of the following queries in Relational Algebra:

1. Display the details of sailors who have rating greater than 8.

2. Display the details of red color boats.

3. Display the name and age of all sailors.

4. Find names of sailors who have ratings at least 8.

5. Find the names of sailors who have reserved boat 103.

6. Find the sids of sailors who have reserved green boat.

7. Find the names of sailors who have reserved a red boat.

8. Find the colors of boats reserved by Lubber.

9. Find the names of boats reserved by Dustin.

Relation: Sailors Relation: Reserves

sid sname rating age sid bid day
22 Dustin 7 45.0 22 101 10/10/98
29 Brutus 1 33.0 22 102 10/10/98
31 Lubber 8 55.5 22 103 10/8/98
32 Andy 8 25.5 22 104 10/7/98
58 Rusty 10 35.0 31 102 11/10/98
64 Horatio 7 35.0 31 103 11/6/98
71 Zobra 10 16.0 31 104 11/12/98
74 Horatio 9 35.0 64 101 9/5/98
85 Art 3 25.5 64 102 9/8/98
95 Bob 3 63.5 74 103 9/8/98

Relation: Boats
bid bname color
101 Interlake blue
102 Interlake red
103 Clipper green
104 Marine red

Ch. Vijayananda Ratnam @Dept. of CSE 23

Database Management Systems UNIT-III
1. Display the details of sailors who have rating greater than 8.
σ rating > 8 (SAILORS)

2. Display the details of red color boats.

σ color =’red’ (BOATS)

3. Display the name and age of all sailors.

∏ sname, age (SAILORS)

4. Find the names of sailors who have ratings at least 8?

∏ sname (σ rating >=8 (SAILORS))

5. Find the names of sailors who have reserved boat 103?

∏ sname ((σ bid = 8 (RESERVES) ⋈ SAILORS)

6. Find the sid of sailors who have reserved green boat.

∏ sid ((σ color=’green’ (BOATS) ⋈ RESERVES)

7. Find the names of sailors who have reserved a red boat.

∏ sname ((σ color =’red’ (BOATS) ⋈ RESERVES ⋈ SAILORS)

8. Find the colors of boats reserved by Lubber.

∏ color ((σ sname =’Lubber’ (SAILORS) ⋈ RESERVES ⋈ BOATS)

9. Find the names of boats reserved by Dustin.

∏ bname ((σ sname =’Dustin’ (SAILORS) ⋈ RESERVES ⋈ BOATS)

******** End of Unit - 3 ********

Ch. Vijayananda Ratnam @Dept. of CSE 24