UNIT – II

Relational Model : CODD’s Rule – Relational Data Model – Key

Integrity – Relational Algebra Operations – Advantages and limitations
– Relational Calculus – Domain Relational Calculus – QBE.
2.1 Relational Model
 E.F. Codd (Edgar Frank Codd) of IBM had written an
article “A relational model for large shared data banks” in
June 1970 in the Association of Com- puter Machinery
(ACM) Journal, Communications of the ACM.
 His work triggered people to work in relational model.
One of the most significant implementations of the
relational model was “System R,” which was develo- ped
by IBM during the late 1970s.
 System R was intended as a “proof of concept” to show
that relational database systems could really build and
work efficiently.
 It gave rise to major developments such as a structured
query language called SQL which has since become an ISO
standard and de facto standard relational language.
2.2. CODD’S Rules
 Dr Edgar F. Codd, after his extensive research on the Relational
Model of database systems, came up with twelve rules of his
own, which according to him, a database must obey in order to
be regarded as a true relational database.
 These rules can be applied on any database system that
manages stored data using only its relational capabilities. This is
a foundation rule, which acts as a base for all the other rules.
Rule 1: Information Rule
 The data stored in a database, may it be user data or metadata,
must be a value of some table cell.
 Everything in a database must be stored in a table format.
Rule 2: Guaranteed Access Rule
 Every single data element (value) is guaranteed to be accessible
logically with a combination of table-name, primary-key (row
value), and attribute-name (column value).
 No other means, such as pointers, can be used to access data.

Prepared By : Mrs. SHUNMUGA PRIYA K Page 1

 Rule 3: Systematic Treatment of NULL Values
 The NULL values in a database must be given a systematic and
uniform treatment.
 This is a very important rule because a NULL can be interpreted
as one the following − data is missing, data is not known, or data
is not applicable.
Rule 4: Active Online Catalog
 The structure description of the entire database must be stored
in an online catalog, known as data dictionary, which can be
accessed by authorized users.
 Users can use the same query language to access the catalog
which they use to access the database itself.
Rule 5: Comprehensive Data Sub-Language Rule
 A database can only be accessed using a language having linear
syntax that supports data definition, data manipulation, and
transaction management operations.
 This language can be used directly or by means of some
application. If the database allows access to data without any
help of this language, then it is considered as a violation.
Rule 6: View Updating Rule
 All the views of a database, which can theoretically be updated,
must also be updatable by the system.
Rule 7: High-Level Insert, Update, and Delete Rule
 A database must support high-level insertion, updation, and
deletion. This must not be limited to a single row, that is, it must
also support union, intersection and minus operations to yield
sets of data records.
Rule 8: Physical Data Independence
 The data stored in a database must be independent of the
applications that access the database.
 Any change in the physical structure of a database must not
have any impact on how the data is being accessed by external
applications.

Prepared By : Mrs. SHUNMUGA PRIYA K Page 2

 Rule 9: Logical Data Independence
 The logical data in a database must be independent of its user’s
view (application). Any change in logical data must not affect
the applications using it.
 For example, if two tables are merged or one is split into two
different tables, there should be no impact or change on the user
application. This is one of the most difficult rule to apply.
Rule 10: Integrity Independence
 A database must be independent of the application that uses it.
All its integrity constraints can be independently modified
without the need of any change in the application. This rule
makes a database independent of the front-end application and
its interface.
Rule 11: Distribution Independence
 The end-user must not be able to see that the data is distributed
over various locations. Users should always get the impression
that the data is located at one site only. This rule has been
regarded as the foundation of distributed database systems.
Rule 12: Non-Subversion Rule
 If a system has an interface that provides access to low-level
records, then the interface must not be able to subvert the
system and bypass security and integrity constraints.

2.3. Relational Data Model

The relational model uses a collection of tables to represent

both data and the relationships among those data. Tables are
logical structures maintained by the database manager. The
relational model is a combination of three components, such as
Structural, Integrity, and Manipulative parts.

2.3.1. Structural Part

The structural part defines the database as a collection of relations.

Prepared By : Mrs. SHUNMUGA PRIYA K Page 3

 2.3.2. Integrity Part

The database integrity is maintained in the relational model

using primary and foreign keys.
2.3.3. Manipulative Part

The relational algebra and relational calculus are the tools used
to manipulate data in the database. Thus relational model has a
strong mathematical background. The key features of relational
data model are as follows:
– Each row in the table is called tuple.
– Each column in the table is called attribute.
– The intersection of row with the column will have data value.
– In relational model rows can be in any order.
– In relational model attributes can be in any order.
– By definition, all rows in a relation are distinct. No two rows
can be exactly the same.
– Relations must have a key. Keys can be a set of attributes.
– For each column of a table there is a set of possible values
called its domain. The domain contains all possible values that
can appear under that column.
– Domain is the set of valid values for an attribute.
– Degree of the relation is the number of attributes (columns) in the
relation.
– Cardinality of the relation is the number of tuples (rows) in
the relation

Prepared By : Mrs. SHUNMUGA PRIYA K Page 4

 2.3.3. Table and Relation

The general doubt that will rise when one reads the relational
model is the difference between table and relation. For a table to
be relation, the following rules holds good:
– The intersection row with the column should contain single
value (atomic value).
– All entries in a column are of same type.
– Each column has a unique name (column order not significant).
– No two rows are identical (row order not significant).

Example of Relational Model

Representation of Movie data in tabular form is shown later.

MOVIE
Movie Director Actor Actress
Name
Titanic James Leonardo Kate
Cameron DiCapiro Winslet
Autograph Cheran Cheran Gopika
Roja Maniratnam AravindSwamy Madubala
In the earlier relation:
The degree of the relation (i.e., is the number of column in the
relation) = 4. The cardinality of the relation (i.e., the number of
rows in the relation) = 3.

2.4. Concept of Key

Key is an attribute or group of attributes, which is used to

identify a row in a relation. Key can be broadly classified into (1)
Superkey (2) Candidate key, and (3) Primary key

Prepared By : Mrs. SHUNMUGA PRIYA K Page 5

 2.4.1. Superkey

A superkey is a subset of attributes of an entity-set that uniquely

identifies the entities. Superkeys represent a constraint that
prevents two entities from ever having the same value for those
attributes.
2.4.2. Candidate Key

Candidate key is a minimal superkey. A candidate key for a

relation schema is a minimal set of attributes whose values
uniquely identify tuples in the corresponding relation.

2.4.3. Primary Key

The primary key is a designated candidate key. It is to be noted

that the primary key should not be null.

2.4.5. Foreign Key

Foreign key is set of fields or attributes in one relation that is

used to “refer” to a tuple in another relation.

2.5. Relational Integrity

Data integrity constraints refer to the accuracy and

correctness of data in the database. Data integrity provides a
mechanism to maintain data consistency for operations like
INSERT, UPDATE, and DELETE. The different types of data
integrity constraints are Entity, NULL, Domain, and Referential
integrity.

2.5.1. Entity Integrity

Entity integrity implies that a primary key cannot accept null

value. The primary key of the relation uniquely identifies a row in
a relation. Entity integrity means that in order to represent an
entity in the database it is necessary to have a complete
identification of the entity’s key attributes.

Prepared By : Mrs. SHUNMUGA PRIYA K Page 6

 2.5.2. Null Integrity

Null implies that the data value is not known temporarily.

Consider the relation PERSON. The attributes of the relation
PERSON are name, age, and salary. The age of the person
cannot be NULL.

2.5.3. Domain Integrity Constraint

Domains are used in the relational model to define the

characteristics of the columns of a table. Domain refers to the set
of all possible values that attribute can take. The domain
specifies its own name, data type, and logical size. The logical
size represents the size as perceived by the user, not how it is
implemented internally.

Example 1

The age of the person cannot have any letter from the alphabet.
The age should be a numerical value.

2.5.4. Referential Integrity

In the relational data model, associations between tables are

defined through the use of foreign keys. The referential
integrity rule states that a database

2.6. Relational Algebra

The relational algebra is a theoretical language with operations

that work on one or more relations to define another relation
without changing the original relation. Thus, both the operands
and the results are relations; hence the output from one
operation can become the input to another operation. This
allows expressions to be nested in the relational algebra.

Prepared By : Mrs. SHUNMUGA PRIYA K Page 7

 2.6.1. Role of Relational Algebra in DBMS

Knowledge about relational algebra allows us to understand

query execution and optimization in relational database
management system a SQL query has to be converted into an
executable code, first it has to be parsed to a valid relational
algebraic expression, then there should be a proper query
execution plan to speed up the data retrieval. The query
execution plan is given by query optimizer.

2.7. Relational Algebra Operations

Operations in relational algebra can be broadly classified into set

operation and database operations.

2.7.1 Unary and Binary Operations

Unary operation involves one operand, whereas binary operation

involves two operands. The selection and projection are unary
operations. Union, difference, Cartesian product, and Join
operations are binary operations:

–
Prepared By : Mrs. SHUNMUGA PRIYA K Page 8
 – Unary operation operate on one relation
– Binary operation operate on more than one relation
Relational algebra operations

Set Operations Database operations

Union  Selection
Intersection  Projection
Difference  Join
Cartesian
product
Three main database operations are SELECTION,
PROJECTION, and JOIN.

Selection Operation
 The selection operation works on a single relation R and
defines a relation that contains only those tuples of R that
satisfy the specified condition (Predicate). Selection
operation can be considered as row wise filtering.
Syntax of Selection Operation
The syntax of selection operation is: σPredicate (R). Here R
refers to relation and predicate refers to condition.

Illustration of Selection Operation

To illustrate the SELECTION operation consider the

STUDENT relation with the attributes Roll number, Name, and
GPA (Grade Point Average).

Prepared By : Mrs. SHUNMUGA PRIYA K Page 9

 Example

Consider the relation STUDENT shown later:

STUDENT
Student Name GP
Roll. No A
001 Aravind 7.2
002 Anand 7.5
003 Balu 8.2
004 Chitra 8.0
005 Deepa 8.5
006 Govind 7.2
007 Hari 6.5
Query 1: List the Roll. No, Name, and GPA of those students
who are having GPA of above 8.0
Query expressed in relational algebra as σGPA > 8
(Student). The result of the earlier query is:

Student Name GPA

Roll. No
003 Balu 8.2
005 Deepa 8.5

Projection Operation

 The projection operation works on a single relation R

and defines a relation that contains a vertical subject of R,
extracting the values of specified attributes and elimination
duplicates. The projection operation can be considered as
column wise filtering.

Syntax of Projection Operation

The syntax of projection operation is given by: a1,a2,. an (R).

Where a1, a2,........ an are attributes and R stands for relation.

Prepared By : Mrs. SHUNMUGA PRIYA K Page 10

 STAFF
Staff No Name Gende Date of Salary
r birth
SL21 Raghava M 1-5-76 15,000
n
SL22 Raghu M 1-5-77 12,000
SL55 Babu M 1-6-76 12,500
SL66 Kingsly M 1-8-78 10,000

Illustration of Projection Operation

To illustrate projection operation consider the relation STAFF,

with the attributes Staff number, Name, Gender, Date of birth,
and Salary.
Query 1: Produce the list of salaries for all staff showing only
the Name and salary detail. Relational algebra expression:
IT
[Link] (staff)

Output for the Query 1

Name Salary
Raghavan 15,000
Raghu 12,000
Babu 12,500
Kingsly 10,000
Rename operation (ρ)

 The rename operator returns an existing relation under a

new name. ρA(B) is the relation B with its name changed to
A. The results of operation in the relational algebra do not
have names.

 It is often useful to name such results for use in further

expressions later on. The rename operator can be used to
name the result of relational algebra operation.

Prepared By : Mrs. SHUNMUGA PRIYA K Page 11

 Example of Rename Operation

Consider the relation BATSMAN with the attributes name, nation,

and BA.
BATSMAN
Name Nation BA
Sachin Tendulkar India 45.5
Brian Lara West Indies 43.5
Inzamamulhaq Pakistan 42.5

The attributes of the relation BATSMAN can be renamed as

name, nation and batting average as name, nation, batting
average (BATSMAN) so that the relation BATSMAN after
rename operation as shown later.
BATSMAN
Name Nation Batting
average
Sachin Tendulkar India 45.5
Brian Lara West Indies 43.
5
Inzamamulhaq Pakistan 42.5
From the earlier operation it is clear that rename operation
changes the schema of the database and it does not change the
instance of the database.

Union Compatibility
 In order to perform the Union, Intersection, and the
Difference operations on two relations, the two relations
should be union compatible.
 Two relations are union compatible if they have same
number of attributes and belong to the same domain.
Mathematically UNION COMPATIBILITY it is given as:
 Let R(A1,A2,........An) and S(B1,B2,.............Bn) be the two
relations. The relation R has the attributes A1,A2,........An and
the relation S has the attributes B1,B2,. Bn. The two

Prepared By : Mrs. SHUNMUGA PRIYA K Page 12

 relations R and S are union compatible
 if dom(Ai) = dom(Bi) for i = 1 to n.

Union Operation
The union of two relations R and S defines a relation that contains
all the tuples of R or S or both R and S, duplicate tuples being
eliminated.

Relational Algebra Expression

The union of two relations R and S are denoted by R S. R S is
pictorially represented in the Fig. 3.4.

Illustration of UNION Operation

To illustrate the UNION operation consider the two relations
Customer 1 and Customer 2 with the attributes Name and city.

Prepared By : Mrs. SHUNMUGA PRIYA K Page 13

 Intersection Operation

The intersection operation defines a relation consisting of the set

of all tuples that are in both R and S.

Relational Algebra Expression

The intersection of two relations R and S is denoted by R ∩ S.

Illustration of Intersection Operation

The intersection between the two relations R and S is

pictorially shown in Fig. 3.5.
Example

Find the intersection of Customer 1 with Customer 2 in the

following table.

Difference Operation

The set difference operation defines a relation consisting of the

tuples that are in relation R but not in S.

Relational Algebra Expression

The difference between two relations R and S is denoted by R–S.

Example

Compute R–S for the relation shown in the following table.

Prepared By : Mrs. SHUNMUGA PRIYA K Page 14

 Division Operation

The division of the relation R by the relation S is denoted by R

÷ S, where R ÷ S is given by:
R ÷ S = ΠR−−S(r) − ΠR−−S((ΠR−−S(r) × s) − r)
To illustrate division operations consider two relations
STUDENT and MARK. The STUDENT relation has the attributes
Student Name and the mark in particular subject say
mathematics. The MARK relation consists of only one column
mark and only one row.

Cartesian Product Operation

The Cartesian product operation defines a relation that is the

concatenation of every tuples of relation R with every tuples of
relation S. The result of Cartesian product contains all
attributes from both relations R and S.

Relational Algebra Symbol for Cartesian Product:

The Cartesian product between the two relations R and S is

denoted by
R × S.

Prepared By : Mrs. SHUNMUGA PRIYA K Page 15

 Note: If there are n1 tuples in relation R and n2 tuples in S, then
the number of tuples in R × S is n1*n2.

Example

If there are 5 tuples in relation “R” and 2 tuples in relation “S”

then the number of tuples in R × S is 5 ∗ 2 = 10.

2.8. Join Operations

Join operation combines two relations to form a new relation.

The tables should be joined based on a common column. The
common column should be compatible in terms of domain.

Types of Join Operation

Natural Join

 The natural join performs an equi join of the two relations

R and S over all common attributes. One occurrence of each
common attribute is eliminated from the result.

 In other words a natural join will remove duplicate

attribute. In most systems a natural join will require that
the attributes have the same name to identity the
attributes to be used in the join.

Prepared By : Mrs. SHUNMUGA PRIYA K Page 16

 Input: Two relations (tables) R and S Notation: R t><l S
Purpose: Relate rows from second table and
– Enforce equality on all column attributes
– Eliminate one copy of common attribute
IT
* Short hand for L(R × S):
– L is the union of all attributes from R and S with duplicate
removed
– P equates all attributes common to R and S
Example of Natural Join Operation

 Consider two relations EMPLOYEE and DEPARTMENT. Let

the common attribute to the two relations be
DEPTNUMBER. The two relations are shown later:
 It is worth to note that Natural join operation is
associative. (i.e.,) If R, S, and T are three relations then
R 1><1 (S 1><1 T) = (R 1><1 S) 1><1 T

Equi Join

A special case of condition joins where the condition C

contains only equality.

Example of Equi Join

Given the two relations STAFF and DEPT, produce a list of staff
and the departments they work in.

Prepared By : Mrs. SHUNMUGA PRIYA K Page 17

 Theta Join

 A conditional join in which we impose condition other

than equality condition. If equality condition is imposed
then theta join become equi join.

 The symbol θ stands for the comparison operator

which could be >, <, >=, <=.

Expression of Theta Join

σθ(R × S)

Illustration of Theta Join

To illustrate theta join consider two relations FRIENDS and

OTHERS with the attributes Name and age.

Prepared By : Mrs. SHUNMUGA PRIYA K Page 18

 Outer Join

In outer join, matched pairs are retained unmatched values in

other tables are left null.

Types of Outer Join

The pictorial representation of the left and the right outer join of
two relations R and S are shown in Fig. 3.7:
1. Left Outer Join. Left outer joins is a join in which tuples
from R that do not have matching values in the common
column of S are also included in the result relation.
2. Right Outer Join. Right outer join is a join in which tuples
from S that do not have matching values in the common
column of R are also included in the result relation.
3. Full Outer Join. Full outer join is a join in which tuples
from R that do not have matching values in the common
columns of S still appear and tuples in S that do not have
matching values in the common columns of R still appear in
the resulting relation.

Prepared By : Mrs. SHUNMUGA PRIYA K Page 19

 Example of Full Outer Left Outer and Right Outer Join

Consider two relations PEOPLE and MENU determine the full

outer, left outer, and right outer join.

Prepared By : Mrs. SHUNMUGA PRIYA K Page 20

 Semi-Join

 The semi-join of a relation R, defined over the set of

attributes A, by relation S, defined over the set of
attributes B, is the subset of the tuples of R that
participate in the join of R with S.

 The advantage of semi-join is that it decreases the number

of tuples that need to be handled to form the join. In
centralized database system, this is important because it
usually results in a decreased number of secondary
storage accesses by making better use of the memory.

Expression for Semi-Join

rr
R t><F S = A(R t><F S) where F is the
predicate.

Example of Semi-Join

In order to understand semi-join consider two relations EMPLOYEE

and PAY

2.8. Advantages of Relational Algebra

The relational algebra has solid mathematical background. The

mathematical background of relational algebra is the basis of
many interesting developments and theorems. If we have two

Prepared By : Mrs. SHUNMUGA PRIYA K Page 21

 expressions for the same operation and if the expressions are
proved to be equivalent, then a query optimizer can automat-
ically substitute the more efficient form. Moreover, the
relational algebra is a high level language which talks in terms of
properties of sets of tuples and not in terms of for-loops.

2.9. Limitations of Relational Algebra

 The relational algebra cannot do arithmetic. For example, if we

want to know the price of 10 l of petrol, by assuming a 10%
increase in the price of the petrol, which cannot be done using
relational algebra.
 The relational algebra cannot sort or print results in various
formats. For example we want to arrange the product name in
the increasing order of their price. It cannot be done using
relational algebra.
 Relational algebra cannot perform aggregates. For example we
want to know how many staff are working in a particular
department. This query cannot be performed using relational
algebra.
 The relational algebra cannot modify the database. For
example we want to increase the salary of all employees by
10%. This cannot be done using relational algebra.
 The relational algebra cannot compute “transitive closure.” In
order to understand the term transitive closure consider the
relation RELATIONSHIP, which describes the relationship
between persons.
2.10. Relational Calculus
– The purpose of relational calculus is to provide a formal basis
for defining declarative query languages appropriate for
relational databases. Relational Calculus comes in two
flavors (1) Tuple Relational Calculus (TRC) and
– (2) Domain Relational Calculus (DRC).
– The basic difference between relational algebra and relational
calculus is that the former gives the procedure of how to
evaluate the query whereas the latter gives only the query

Prepared By : Mrs. SHUNMUGA PRIYA K Page 22

 without giving the procedure of how to evaluate the query:
– The variable in tuple relational calculus formulae range over
tuples.
– The variable in domain relational calculus formulae range over
individual values in the domains of the attributes of the
relations.
– Relational calculus is nonoperational, and users define queries
in terms of what they want, not in terms of how to compute
it. (Declarativeness.)
Relational Calculus and Relational Algebra:
The major difference between relational calculus and relational
algebra
– A relational calculus query specifies what information is
retrieved
– A relational algebra query specifies how information is
retrieved

2.10.1 Tuple Relational Calculus

– Tuple relational calculus is a logical language with variables
ranging over tuples. The general form of tuple relational
calculus is given by:
{<tuple variable list> | <conditions>}
{t | COND(t)}
– Here t is the tuple variable, which stands for tuples of relation.
COND
– (t) is a formula that describes t. The meaning of the earlier
expression is to return all tuples T that satisfy the condition
COND:
– T/R(T) means return all tuples T such that T is a tuple in
relation R.
– For example, [Link]/FACULTY(T) means return all the names
of fac- ulty in the relation FACULTY.
– [Link]/ FACULTY(T) AND [Link] = ‘EEE’ means return
the value of the name of the faculty who are working in EEE
department.

Prepared By : Mrs. SHUNMUGA PRIYA K Page 23

 Free Variable
Any variable that is not bound by a quantifier is said to be free.

Bound Variable
Any variable which is bounded by universal or existential quantifier
is called bound variable.
Example of selection operation in TRC:
1. To find details of all staff earning more than Rs. 10,000:
{S | Staff(S) ∧ [Link] > 10000}
Example of projection operation in
TRC:
Limitations of TRC
TRC cannot express queries involving:
– Aggregations.
– Groupings.
– Orderings.

2.10.2 Domain Relational Calculus (DRC)

– Domain relational calculus is a nonprocedural query language
equivalent in power to tuple relational calculus. In domain
relational calculus each query is an expression of the form:
 {<X1,X2,............., Xn >/P(X1,X2,. ,Xn)} where
– X1,X2,. ............,Xn represent domain variables
– P represents a formula similar to that of the predicate calculus.
– Domain variable: A domain variable is a variable whose value
is drawn from the domain of an attribute.

Prepared By : Mrs. SHUNMUGA PRIYA K Page 24

 2.10.3. Queries in Domain Relational Calculus:

Consider the ER diagram:

3.2 QBE
* QBE stands for Query By Example. QBE uses a terminal
display with attribute names as table headings for queries.
* QBE was developed originally by IBM in the 1970s to help
users in their retrieval of data from a database.
* QBE represents a visual approach for accessing data in a
database through the use of query templates. QBE can be
considered as GUI (Graphical User Interface) based on
domain calculus.
* QBE allows users to key in their input requests by filling in
empty tables on the screen, and the system will also display its
response in tabular form.
* QBE is user-friendly because the users are not required to
formulate sentences for query requests with rigid query-
language syntax.
* In QBE the request is entered in the form of tables whose
skeletons are initially constructed by QBE.
* Some of the QBE query template examples
Example 1. Projection operation
In this template P. implies “Print.” The meaning is: Print the
PLAYER ADDRESS who belong to the country INDIA. To make a
projection only put
* P. in any column of the projection. QBE will enforce
uniqueness of projections automatically

Prepared By : Mrs. SHUNMUGA PRIYA K Page 25