1

Chapter 3: Relational Model

Relational database management system
 Maintains Data in table
 Relationship are created and maintained across and among the data and the table
 A relational database management system (RDBMS) is a database management
system (DBMS) that is based on the relational model as introduced by Dr Edgar F
.Codd. Most popular commercial and open source databases currently in use are based
on the relational model.
 A short definition of an RDBMS may be a DBMS in which data is stored in the form
of tables and the relationship among the data is also stored in the form of tables.
Relational Model Concept
 A relational database is a collection of relation.
Each row or a relation Instance is called a tuple and each
column is called attribute of the relation.
Definition of a Relational Database
A relational database uses relations or two-dimensional tables to store information.
For example, you might want to store information about all the employees in your
company. In a relational
database, you create several tables to store different pieces of information about your
employees, such as an employee table, a department table, and a salary table.

Codd’s rules
 E.F. Codd, the famous mathematician has introduced 12 rules for the relational model
for databases commonly known as Codd's rules.
 The rules mainly define what is required for a DBMS for it to be considered relational,
i.e., an RDBMS.
 There is also one more rule i.e. Rule00 which specifies the relational model should use
the relational way to manage the database. The rules and their description are as
follows:-

00. Foundation Rule : A relational database management system must manage its stored
data using only its relational capabilities.

DR. R K MAHTO CA & IT DBMS

 2

1. Information Rule : All information in the database should be represented in one and
only one way - as values in a table. The row and columns have to be strictly unordered

 Information is to be represented as data stored in cell

 All data should be presented to the user in table form
 Information store d in Cell , every cell contain only an atomic value(unique ).

2. Guaranteed Access Rule : Each and every datum (atomic value) is guaranteed to be
logically accessible by resorting to a combination of table name, primary key value and
column name.

SQL> select name from student where id=10;

Each unique piece of data (atomic value)should be accessible by the combination of:

TableName + Primary Key(Row) + Attribute(Column)

3. Systematic Treatment of Null Values : Null values (distinct from empty character
string or a string of blank characters and distinct from zero or any other number) are
supported in the fully relational DBMS for representing missing information in a
systematic way, independent of data type.

 Any field should be allowed to remain empty.

 Null May mean: Missing data, Not applicable, No value
 RDBMS must allow each attribute to remain null,specifically,it must support a
representation of missing information and inapplicable information
 Should be handled consistently –not zero or blank
 Primary Keys=Not null
 Middle name=Null

DR. R K MAHTO CA & IT DBMS

 3

4. Dynamic On-line Catalog Based on the Relational Model:

The database description is represented at the logical level in the same way as ordinary
data, so authorized users can apply the same relational language to its interrogation as
they apply to regular data.

 Data dictionary (Catalog) should be stored as relational tables and accessible through
the regular data access language
 Data dictionary should be stored as relational tables and accessible through the regular
data access language.
 The same query language to be used on catalog as on the application database
 SQL is used for both the purpose
 E.g. Create table………Data definition
 Desc Table Part of data dictionary

5. Comprehensive Data Sublanguage Rule :

 One well defined language to provide all manners of access to data.(E. g SQL)
 It supports data definition ,data manipulation ,security ,integrity constraints and
transaction management.

A relational system may support several languages and various modes of terminal use.
However, there must be at least one language whose statements are expressible, per
some well-defined syntax, as character strings and whose ability to support all of the
following is comprehensible(understandable)::

a. data definition
b. view definition
c. data manipulation (interactive and by program)
d. integrity constraints
e. authorization
f. transaction boundaries (begin, commit, and rollback).
6. View Updating Rule : All views that are theoretically updateable are also updateable
by the system(in View if you Insert ,update or delete should be updatable at the base
level also and vice versa).

DR. R K MAHTO CA & IT DBMS

 4

 View=“Virtual table ”,temporarily derived from base tables

Example : If a view is formed from tables ,changes to view should be reflected in base
tables

7. High-level Insert, Update, and Delete : The capability of handling a base relation or
a derived relation as a single operand applies nor only to the retrieval of data but also
to the insertion, update, and deletion of data.

 The system must support set at a time insert ,update and delete operation
 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

8. Physical Data Independence :

Physical storage of data should not matter to the system

If say, some file supporting table was renamed or moved from one disk to another ,it
should not effect the application

Application programs and terminal activities remain logically unimpaired whenever

any changes are made in either storage representation or access methods.

9. Logical Data Independence : If there is change in the logical structure (table

structure) of the database the user view of the data should not change implemented
through views.

Application programs and terminal activities remain logically unimpaired when

information preserving changes of any kind that theoretically permit unimpairment are
made to the base tables.

10. Integrity Independence : Integrity constraints specific to a particular relational

database must be definable in the relational data sublanguage and storable in the
catalog, not in the application programs.

Integrity rule= Filter to allow correct data should be stored in data dictionary

DR. R K MAHTO CA & IT DBMS

 5

11. Distribution Independence : The distribution of portion of the database to various

location should be invisible to the user of the database .Database should work properly
regardless of its distribution across a network.

The data manipulation sublanguage of a relational DBMS must enable application

programs and terminal activities to remain logically unimpaired whether and whenever
data are physically centralized or distributed.

 The distribution of portions of the database to various locations should be invisible to

users of the database. Existing applications should continue to operate successfully :
when a distributed version of the DBMS is first introduced; and when existing
distributed data are redistributed around the system.

12 Non subversion Rule : If low level access is allowed to a system it should not be
able to subvert or bypass integrity rules to change data

 This may be achieved by some sort of locking or encryption

 Some low level access tools are provided by vendors that violate these rules for extra
speed If a relational system has or supports a low-level (single-record-at-a-time)
language, that low-level language cannot be used to subvert or bypass the integrity rules
or constraints expressed in the higher-level (multiple-records-at-a-time) relational
language.

Relational Algebra :
 Relational Algebra :
 is a procedural query language, which takes instances of relations as input and yields
instances of relations as output. It uses operators to perform queries. An operator can
be either unary or binary.
 A formal Query language based on a set of operations on relations.
 Procedural query language.
 It consists of set of operations
Fundamental operations: select, project, union, set difference & rename.
Define using Set theory concept:
 Select
 Project

DR. R K MAHTO CA & IT DBMS

 6

 Union
 Set differences
 Cartesian Product
 Rename

 Unary operations : Select, project, rename

 Binary operations: union, set difference, Cartesian products.
Additional Operations
 Natural Join
 Intersection
 Assignment
 Division
Select Operations:
 Selects tuples (rows) that satisfy a given conditions.
 Pick certain Rows
 Lowercase Greek letter sigma (σ)
 Unary operations

e.g. : Select * from emp where empno=1;

σempno=1 (emp)
 Select * from loan where branch_name=‘pune’;
 Select * from emp where branch_name=‘pune’ and amount>1000;
and : ∧ or : ∨ Not : ¬

 e.g. σ (card_no>=‘mca101’) ^ (Date_of_Issue>‘25/09/16’) (Borrow)

 e.g.
 Select * from Book where Acc_No>=2311 ;
 σ Acc_No>=2311 (Book)
 σ (Acc_No>=2311) ^ (yr_pub=1992) (Book)

Project Operations:
 Unary operations
 Pick certain Columns
 Uppercase Greek letter (π ) pi

DR. R K MAHTO CA & IT DBMS

 7

e.g. : Select loan_no, amt from loan

Π loan_no, amt (loan)
 Select rollno, name from student
 Select loan_no, amt from loan where amt>1000;
Π loan_no, amt (σamt>1000 (loan))
 Select rollno,name,city from emp where name=‘abc’ or city =‘pune’
 Find out the title of all the book in the library which have got year of publication
after 1992
 ΠTitle (σ yr_pub>1992 (Book))
 Project first and list later on in this example
 σ yr_pub=1991 (ΠTitle ,yr_pub(Book))

Set Operations:
 Union operations :
Select name from demo union select name from demo1
Π name (demo) ∪ Π name (demo1)
 Intersect Operations :
Select empno from demo Intersect select empno from demo1
Π empno(demo) ∩ Π empno (demo1)
 Except – Minus :
Select ename from demo minus select ename from demo1
Π ename(demo) - Π ename (demo1)
Set Difference (−)
 The result of set difference operation is tuples, which are present in one relation but are
not in the second relation.
 Notation (-): r − s
 Finds all the tuples that are present in r but not in s.
 ∏ author (Books) − ∏ author (Articles)
 Output − Provides the name of authors who have written books but not articles.
Rename (ρ) 'rename' operation is denoted with small Greek letter rho ρ.

 Notation − ρ x (E)
 Where the result of expression E is saved with name of x.

DR. R K MAHTO CA & IT DBMS

 8

Aggregate Functions:

DR. R K MAHTO CA & IT DBMS

 9

 Select sum(salary) from emp

Gsum(salary) (emp)
 Select avg(salary) from emp;
 Select max(salary) as ‘maximum salary’ from emp;
 Select name, sum(salary) from emp group by name;
name Gsum(salary) (emp)
 G average (Marks) (Record) = returns average of the Marks attribute of Record.
 G min (Marks) (Record) = returns the minimum of the Marks attribute of Record.
 G max (Marks) (Record) = returns the maximum of the Marks attribute of Record.

DR. R K MAHTO CA & IT DBMS

 10

 G count distinct (Address) (Record) = returns the number of distinct values of

Address attribute of Record.
Q. Find the maximum balance of each branch.
Code:
 Branch_Name G Max(Balance)(Account).
Q. To find the highest balance of all accounts regardless of branch
 Code:
 G Max(Balance)(Account).

Joins:
Natural join (⋈) : is a binary operator that is written as (R ⋈ S)
where R and S are relations. The result of the natural join is the set of all combinations
of tuples in R and S that are equal on their common attribute names.
For an example consider the tables Employee and Dept and their natural join:

Employee ⋈ Dept

Name EmpId DeptName Manager

Harry 3415 Finance George

Sally 2241 Sales Harriet

George 3401 Finance George

Harriet 2202 Sales Harriet

 Cross Join : X
 Inner Join : Natural / Equi : ⋈
 Left Outer Join :
 Right Outer Join :
 Full Outer Join :

DR. R K MAHTO CA & IT DBMS

 11

 Cross Join : it returns m*n rows

Select rollno, name, address from emp, demo;
Πemp.rollno,name,address (emp X demo)
 Inner Join :
Select emp.rollno, name, address from emp, Inner join demo where
emp.rollno=demo.rollno;
Πemp.rollno,name,address (σemp.rollno=demo.rollno (emp ⋈ demo)
 Left Outer Join :
The left outer join is written as R S where R and S are relations
The result of the left outer join is the set of all combinations of tuples in R and Sthat
are equal on their common attribute names, in addition to tuples in R that have no
matching tuples in S.
For an example consider the tables Employee and Dept and their left outer join:

Select emp.rollno, name, address from emp Left outer join demo where
emp.rollno=demo.rollno;
Πemp.rollno,name,address (σemp.rollno=demo.rollno (emp demo))
 Right Outer Join :
 The right outer join behaves almost identically to the left outer join, but the roles of the
tables are switched.

DR. R K MAHTO CA & IT DBMS

 12

 The right outer join of relations R and S is written as R ⟖ S. The result of the right outer
join is the set of all combinations of tuples in R and S that are equal on their common
attribute names, in addition to tuples in S that have no matching tuples in R.
 For example, consider the tables Employee and Dept and their right outer join:

Select emp.rollno, name, address from emp right outer join demo where
emp.rollno=demo.rollno;
Πemp.rollno,name,address (σemp.rollno=demo.rollno (emp demo))

 Full outer Join :

 The outer join or full outer join in effect combines the results of the left and right outer
joins.
 The full outer join is written as R ⟗ S where R and S are relations. The result of the
full outer join is the set of all combinations of tuples in R and Sthat are equal on their
common attribute names, in addition to tuples in Sthat have no matching tuples in R and
tuples in R that have no matching tuples in S in their common attribute names.
 For an example consider the tables Employee and Dept and their full outer join:

DR. R K MAHTO CA & IT DBMS

 13


Select emp.rollno, name, address from emp full outer join demo where
emp.rollno=demo.rollno;
Πemp.rollno,name,address (σemp.rollno=demo.rollno (emp demo))
 Assignment Operation
 The assignment operation (←) provides a convenient way to express complex queries
 –Write query as a sequential program consisting of a series of assignments followed by
an expression whose value is displayed as a result of the query
 –Assignment must always be made to a temporary relation variable
Example: compute r ÷s
temp1←ΠR-S(r),
temp2 ←ΠR-S((temp1x s) –ΠR-S,S(r))
result = temp1–temp2
•The result to the right of the ←is assigned to the relation variable on the left of the ←
–May use variable in subsequent expressions
 Insertion :
r r∪E
Where r is a relation
E is a relation algebra expression
Insert into emp values (1,’xyz’,200);
emp emp ∪ {(1,’xyz’,200)}
 Deletion :
Delete from emp where empid=1;
emp emp – σ empid=1 (emp)

DR. R K MAHTO CA & IT DBMS

 14

 Updating :
update loan set amt=amt*2;
loan Π amt=amt*2 (loan)
update loan set loan_no=111 where branch_name=‘pune’;
loan Π loan_no=111 (σ branch_name=‘pune’ (loan))
Examples:
Find the names of all customers who have a loan at the Perryridge branch
–Answer 1
Πcustomer_name(σbranch_name= “Perryridge”(
σborrower.loan_number= loan.loan_number(borrower x loan)))
–Answer 2
Πcustomer_name(σloan.loan_number= borrower.loan_number(
(σbranch_name= “Perryridge”(loan)) x borrower))

Note : For SQL Part please refer given sql notes

DR. R K MAHTO CA & IT DBMS