Relational Model

- Sayani Manna

1
Introduction
• A Relational Database management System(RDBMS)
is a database management system based on relational
model introduced by E.F Codd.
• In relational model, data is represented in terms of
tuples(rows).
• RDBMS is used to manage Relational database.
• Relational database is a collection of organized set of
tables from which data can be accessed easily.
• Relational Database is most commonly used database.
• It consists of number of tables and each table has its own
primary key.

2
Codd's twelve rules
• It is proposed by Edgar F. Codd .
• It is designed to define what is required from a database management system in
order for it to be considered relational, i.e., a relational database management
system (RDBMS).

Rules:
• Rule zero
This rule states that for a system to qualify as an RDBMS, it must be able to
manage database entirely through the relational capabilities.
• Rule 1 : Information rule
All information(including metadata) is to be represented as stored data in cells of
tables. The rows and columns have to be strictly unordered.
• Rule 2 : Guaranted Access
Each unique piece of data(atomic value) should be accesible by : Table Name +
primary key(Row) + Attribute(column).

3
• Rule 3 : Systemetic treatment of NULL
Null has several meanings, it can mean missing data, not applicable or no value. It
should be handled consistently. Primary key must not be null. Expression on NULL must
give null.

• Rule 4 : Active Online Catalog

Database dictionary(catalog) must have description of Database. Catalog to be
governed by same rule as rest of the database. The same query language to be used on
catalog as on application database.

• Rule 5 : Powerful language

One well defined language must be there to provide all manners of access to data.
Example: SQL. If a file supporting table can be accessed by any manner except SQL
interface, then its a violation to this rule.

• Rule 6 : View Updation rule

All view that are theoretically updatable should be updatable by the system.

• Rule 7 : Relational Level Operation

There must be Insert, Delete, Update operations at each level of relations. Set
operation like Union, Intersection and minus should also be supported. 4
 • Rule 8 : Physical Data Independence
The physical storage of data should not matter to the system. If say, some file supporting
table were renamed or moved from one disk to another, it should not effect the application.

• Rule 9 : Logical Data Independence

If there is change in the logical structure(table structures) of the database the user view of
data should not change. Say, if a table is split into two tables, a new view should give result as the
join of the two tables. This rule is most difficult to satisfy.

• Rule 10 : Integrity Independence

The database should be able to conforce its own integrity rather than using other programs.
Key and Check constraints, trigger etc should be stored in Data Dictionary. This also
make RDBMS independent of front-end.

• Rule 11 : Distribution Independence

A database should work properly regardless of its distribution across a network. This lays
foundation of distributed database.

• Rule 12 : Non subversion rule

If low level access is allowed to a system it should not be able to subvert or bypass integrity
rule to change data. This can be achieved by some sort of looking or encryption.

5
RDBMS Concepts
• Tables − It is a collection of data elements organised in terms of rows and columns.

• Tuple − A single row of a table, which contains a single record for that relation is
called a tuple.

• Attribute - It is also used to represent a column of the table.

• Domain − Every attribute has some pre-defined value scope, known as attribute
domain.

6
Relational Model Constraints

• Constraints Conditions that must hold on all valid relation

instances.

• Types of constraints:
-Domain constraints
-Key constraints
-Entity integrity constraints
-Referential integrity constraints

7
Domain Constraints

• The value of each attribute A must be an atomic value

from dom(A)
• Domain constraints specify the set of possible values that
may be associated with an attribute.
• It may also prohibit the use of null values for particular
attribute.
• Typical data types: integers, real numbers, characters,
boolean, strings
• Constraints on values –age>30

8
Key Constraints

• Superkey of R:
-A set of attributes SK of R, that specifies a
uniqueness constraint
-No two tuples in any valid relation instance r(R)will
have the same value for SK. That is, for any distinct
tuples t1 and t2 in r(R), t1[SK] ≠t2[SK].

• Key of R: A "minimal" superkey; that is, a superkey K

such that removal of any attribute from K results in a
set of attributes that is not a superkey.

9
Entity Integrity

• Relational Database Schema:

A set S of relation schemas that belong to the same
database. S is the name of the database.
S = {R1, R2, ..., Rn}

• Entity Integrity:
The primary key attributes PK of each relation schema R in S
cannot have null values in any tuple of r(R). This is because
primary key values are used to identify the individual tuples.
t[PK] ≠null for any tuple t in r(R)

• It ensure that changes made to the database by authorized

users do not result in a loss of data consistency.

10
Referential Integrity
• A constraint involving two relations (the previous constraints involve
a single relation).

• Used to specify a relationship among tuples in two relations: the

referencing relation and the referenced relation.

• Tuples in the referencing relation R1 have attributes FK (called

foreign key attributes) that reference the candidate key attributes
CK of the referenced relationR2. A tuple t1 in R1 is said to
reference a tuple t2 in R2 if t1[FK] = t2[CK].

• A referential integrity constraint can be displayed in a relational

database schema as a directed arc from R1. FK to R2.

• It ensure that a value that appear in one relation for a given set of
attributes also appear for a certain set of attributes in another
relationm.

11
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.
• They accept relations as their input and yield relations as
their output.

12
• Six basic operators
• select: σ
• project: ∏
• union: ∪
• set difference: –
• Cartesian product: x
• rename: ρ

13
Select Operation (σ)
A B C D
• It selects tuples that satisfy the given
α α 1 7
predicate from a relation.
α β 5 7
• Notation − σp(r)
β β 12 3
• Where σ -selection predicate
β β 23 10
, r -relation.
p - prepositional logic formula
◼ σA=B ^ D > 5 (r)
which may use connectors like and,
or, and not. These terms may use A B C D
relational operators like − =, ≠, ≥, < ,
α α 1 7
>, ≤.
β β 23 10

14
Project Operation (∏)

• It projects column(s) that

A B C
satisfy a given predicate.
α 10 1
• Notation − ∏A, C (r)
α 20 1
• Where A1, B,C are attribute
names of relation r. β 30 1
β 40 2

A C A C
α 1 α 1
α 1 = β 1
β 1 β 2
β 2
15
Union Operation (∪)

• It performs binary union between two given relations and

is defined as −
r ∪ s = { t | t ∈ r or t ∈ s}
• Notation − r U s
where r and s are either database relations or relation
result set .
• For a union operation to be valid, the following conditions
must hold −
-r, and s must have the same number of attributes.
-Attribute domains must be compatible.
-Duplicate tuples are automatically eliminated.

16
• Relations r, s:
A B A B

α 1 α 2
α 2 β 3
β 1
s
r

A B

● r ∪ s: α •1 1
α •2 2
β •1 1

β •3 3

17
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.

18
Cartesian Product (Χ)

• Combines information of two

different relations into one.
• Notation − r Χ s
• Where r and s are relations and
their output will be defined as −
r Χ s = { q t | q ∈ r and t ∈ s}

19
Rename Operation (ρ)
• The results of relational algebra are also relations but
without any name.
• The rename operation allows us to rename the
output relation.
• 'rename' operation is denoted with small Greek
letter rho ρ.
• Notation − ρ x (E)
Where the result of expression E is saved with name
of x.

20
Relational Calculus

• Relational Calculus is a non-procedural query language,

that is, it tells what to do but never explains how to do it.
• Relational calculus exists in two forms −
-Tuple Relational Calculus (TRC)
-Domain Relational Calculus (DRC)

21
Tuple Relational Calculus (TRC)

• Filtering variable ranges over tuples

• Notation − {T | Condition}
• Returns all tuples T that satisfies a condition.
• For example −
{ [Link] | Author(T) AND [Link] = 'database' }
Output − Returns tuples with 'name' from Author who has
written article on 'database'.
• TRC can be quantified. We can use Existential (∃) and
Universal Quantifiers (∀).
• For example −
{ R| ∃T ∈ Authors([Link]='database' AND
[Link]=[Link])} Output − The above query will yield the
same result as the previous one.

22
Domain Relational Calculus (DRC)

• In DRC, the filtering variable uses the domain of attributes instead

of entire tuple values (as done in TRC, mentioned above).
• Notation −
{ a1, a2, a3, ..., an | P (a1, a2, a3, ... ,an)}
where a1, a2 are attributes and P stands for formulae built by
inner attributes.
• For example −
• {< article, page, subject > | ∈ TutorialsPoint ∧ subject =
'database'} Output − Yields Article, Page, and Subject from the
relation TutorialsPoint, where subject is database.
• Just like TRC, DRC can also be written using existential and
universal quantifiers. DRC also involves relational operators.
• The expression power of Tuple Relation Calculus and Domain
Relation Calculus is equivalent to Relational Algebra.

23
Extended Relational-Algebra-Operations
• Generalized Projection
• Aggregate Functions
• Outer Join

24
Generalized Projection
• Extends the projection operation by allowing arithmetic
functions to be used in the projection list.

• E is any relational-algebra expression

• Each of F , F , …, F are are arithmetic expressions
1 2 n
involving constants and attributes in the schema of E.
• Given relation credit_info(customer_name, limit,
credit_balance), find how much more each person can
spend:
∏
customer_name, limit – credit_balance 25
(credit_info)
Modification of the Database
• The content of the database may be modified using the following operations:
• Deletion
• Insertion
• Updating
• All these operations are expressed using the assignment operator.

26
Deletion
• A delete request is expressed similarly to a query, except
instead of displaying tuples to the user, the selected
tuples are removed from the database.
• Can delete only whole tuples; cannot delete values on
only particular attributes
• A deletion is expressed in relational algebra by:
r←r–E
where r is a relation and E is a relational algebra query

27
Deletion Examples

28
Insertion
• To insert data into a relation, we either:
• specify a tuple to be inserted
• write a query whose result is a set of tuples to be inserted
• in relational algebra, an insertion is expressed by:
r← r ∪ E
where r is a relation and E is a relational algebra expression.
• The insertion of a single tuple is expressed by letting E be a constant
relation containing one tuple.

29
Insertion Examples

30
Updating
• A mechanism to change a value in a tuple without charging all values in the
tuple
• Use the generalized projection operator to do this task

• Each Fi is either
• the I th attribute of r, if the I th attribute is not updated, or,
• if the attribute is to be updated Fi is an expression, involving only
constants and the attributes of r, which gives the new value for the
attribute

31
Update Examples

32
View
• A view is a “virtual table” or a “stored query” which
takes the output of a query and treats it as a table.
• The table upon which a view is created is called as base
table.
• A view is a way of portraying information in
the database.
• This can be done by arranging the data items in a
specific order, by highlighting certain items, or by
showing only certain items.

33
• Advantages of a view:
a. Additional level of table security.
b. Hides data complexity.
c. Simplifies the usage by combining multiple tables
into a single table.
d. Provides data in different perspective.
• Syntax:
Create [or replace ] view <view name> [column alias names]
as <query> [with <options> conditions];

• Example:
Create or replace view empview as select * from emp;

34
Types of view:

• Horizontal - enforced by where cause

• Vertical - enforced by selecting the required columns

35