Relational Model

• This model was first proposed by E.F.Codd in

1970.
• He said about 13 rules which was the basis for
relational model.
• Any database that follows these rules are termed
as Relational database
 Purpose of Relational model

• Achieve program/data independence

• Treat data in a disciplined way (data is ordered
rows and column)
• Apply rules of mathematics (sum: total marks)
• Use set theory to improve programmer
productivity (join, union, cross product)
 Representations in relational model

• Relation is the base representation otherwise called as

table.
• Relation schema describes the structure of relation.
– For Example
– Student( student_id, student_name)
• Relational Instance describes the table with sample data.
 Relational Terms
• Record or Tuple: Each row in the relation.
• Domain: The set of allowable values for a column.
• Degree: The number of columns in a relation.
• Cardinality: The number of rows in a relation.
• Relational database: a collection of relations with
distinct relation names.
• Relational database schema: the collection of schemas
for the relations.
• Instance of a relational database is a collection of
relation instances
 Integrity Constraints

• They are rules that relation should satisfy for a

relational instance.
• Domain Constraints
– Unique
– Null
– Not Null
– Check
• Key Constraints
 Key Constraints
• Primary Key Constraints
– Rule that specifies a field to be a primary key.
– A Primary Key is a field (Column) which is used to identify the
whole record.
• Composite Key Constraints
– Rule specifies one or more field as a primary key.
• Foreign Key Constraints
– Rule specifies a field to be a foreign key
– Foreign key specifies a field which can be used to relate a table
with other table.
 Conversion of ER Model to
Tables
• Entity sets to Tables.
– Each Entity set can be represented into a
separate relation or table. i.e. the entity set
name is considered as the table name.
– Each attribute is represented as the field or
column in the table.
– Key attribute is represented as the primary
key for that table.
Conversion of ER Model to Tables

• If there is Mulitvalued attribute then a

separate table has to be represented only for
that mulitvalued attribute with each field
representing different values that the
attribute can hold for an entity instance and a
separate field which represents the primary
key of the parent table must also be
represented.
• If there is a composite attribute then each of
its subcomponent are represented as fields in
that table.
Example
Table
 Conversion of ER Model to Tables

• Relationship sets to tables (Without Constraints)

– A relationship set is also represented as a table in
relational model with fields as the key attributes of
the participating entity set.
– Descriptive attributes of the relationship set is also
represented as a field in the table.
 Conversion of ER Model to Tables

• Translating ER diagrams with Generalization and

Specialization.
• For each sub entities a separate relation is created
with fields as attributes of the sub entity and
another additional field as the primary key of the
parent entity set.
Conversion of ER Model to Tables

• Translating ER diagrams with Aggregation

• Representing aggregation is easy as the
relational model doesn’t have different
representation for relationship set and an
entity set.
 Relational algebra….

• Relational algebra is a mathematical concept used in

designing efficient Queries.
• Queries are questions asked to the database for
extracting data or to perform some action in the
database.
• When data is requested from the database using a
query
– What data is to be requested.
– From which source the data is to be requested
Are clearly specified
 Relational algebra operation
• The relational algebra operation are to describe
the formats of the query design that we use in
SQL.
• Some Basic Operations include
– Selection
– Projection
– Union
– Difference
– Cartesian Product……
 Selection(σ)

• Used to select some rows from a table

• Format
– σ (Predicate) (R)
– Predicate can be a condition on fields like
name>10
– R is a relation
• For Example
– σAge = Weight (Person)
 Projection(π)
• Projection is used to select only needed
columns
• Format
– π (a1, a2, a3….) ( R )
• For Example
– π (Name, Weight) (Person)
 Rename(ρ)
• Used to give different name to the relation
• Format
– ρ a / b(R)
• a and b are attribute names
• R is a relation
• For Example
– ρ EmployeeName / Name(Employee)
 Union (U)
• Used to join two relation where the row with same
values will be selected only once
• Format
π (a1, a2, a3….) ( R1 ) U π (a1, a2, a3….) ( R2 )
• For Example
π (Employee Number, Employee name, Address, GSM) (IT-Student ) U
π (Employee Number, Employee name, Address, GSM) (Bus-Student )
• The Union operation can be performed only on
compatible relations
 Intersection(∩)
• Used in two relation where the rows with the
same value is only selected.
• Format
– π (a1, a2, a3….) ( R1 ) ∩ π (a1, a2, a3….) ( R2 )
• For Example
π (Employee Number, Employee name, Address, GSM) (IT-Student ) ∩
π (Employee Number, Employee name, Address, GSM) (Bus-Student )
• Intersection can be performed only on
compatible relation
• Unfortunately, MySQL does not support
the INTERSECT operator.
 Difference(-)
• Used in two relation where the rows from the first
relation is displayed excluding the rows with same
values.
• Format
– π (a1, a2, a3….) ( R1 ) - π (a1, a2, a3….) ( R2 )
• For Example
π (Employee Number, Employee name, Address, GSM) (IT-Student )
-
π (Employee Number, Employee name, Address, GSM) (Bus-Student )
• The Difference can also be performed only on
compatible relation
 Difference(-)
• The Difference compares the results of two
queries and returns distinct rows from the
result set of the first query that does not
appear in the result set of the second query.
 Cartesian Product / (X)
Cross Product
• Used between more than one table where the first
row from the first table is combined with every row
in the second table and this combination is repeated
with each row of the first table.
For Example
 Division ÷
• Division operation uses two tables and the result will
be the columns excluding the common fields and
rows that have same values in common fields