Introduction to
Relational Model
Relational Model Concepts
• The model was first proposed by Dr. E.F. Codd
of IBM in 1970 in the following paper:
"A Relational Model for Large Shared Data Banks,"
Communications of the ACM, June 1970.
The above paper caused a major revolution in
the field of Database management and earned
E.F Codd ACM Turing Award.
Sample Relational Data Model
Alternative Terminology for Relational
Model
Informal Terms Formal Terms
Table/File Relation
Column/Field Attribute
Row/Record Tuple
Values in a column Domain
Table Definition Schema of a Relation
Populated Table Extension
Notion of Relation
A table is said to be a relation, if it satisfy following
properties: -
1. It is column homogeneous.
All items in a column are of the same kind.
2. Each column is atomic.
Each item is an integer or a character string.
Properties of Relations
1. Relation name is distinct from all other
relation names in relational schema.
1. Each cell of relation contains exactly one
atomic (single) value.
1. Each attribute has a distinct name.
1. Values of an attribute are all from the same
domain.
Properties of Relations
5. Each tuple is distinct. There are no duplicate
tuples.
5. Order of attributes has no significance.
5. Order of tuples has no significance,
theoretically.
S# P# Sc
10 1 Delhi
10 2 Delhi A valid relation
11 1 Mumbai
11 2 Mumbai
S# P# City Invalid relation
11 1 Delhi Two rows are not distinct.
11 1 Delhi
Name Child
Robert Johnny,12-04-1985
Invalid relation
Child field is not atomic.
Identify whether the given relation is valid or invalid. Justify reasons in
support.
Domains and Attributes
Domain - The set of values on which an attribute is
defined.
Domain is concerned with data of type integer or
character strings.D1 is of character strings, D2 of integer.
D1 D2
Customer-name Address Date-of-birth
Attribute
Instances of Branch and Staff Relations
Examples of Attribute Domains
Relational Schema
A relational schema is a collection of relation definitions
Schema
RD1 , RD2,……………………RDn
Relational Schema does not change over time.
Null Relation
At the time definition of relation is just given, a
relation with no tuples in it is created.
This relation is a Null Relation.
Integrity
Constraints
Ensures data consistency during insertion/
updation /deletion of database
• Key Constraint
• Entity Integrity Constraint
• Referential Integrity Constraint
Prime / Non Prime Attribute
Prime attribute : A set of attributes that
participates in the candidate key.
STUDENT( name, father-name, course,
enroll-number, grade)
If key is enroll-number, then
• Prime attribute : enroll-number.
• Non Prime attribute : name, father-name,
course, grade
Candidate Key
Consider a relation R on set of n-attributes, then a
candidate key K is a set of one or more fields if and
only if it satisfies following :
•Uniqueness : K uniquely identifies each tuple, i.e.
no two tuples can have same value of K
•Non - redundancy : K is non - redundant, i.e. no
proper subset of K has the uniqueness property
More than one candidate key may exist in a
Relation
Example
STUDENT( name, father-name, course, enroll-number,
grade)
Candidate keys :
• {name, father-name}
• {enroll-number}
Non Candidate Key :
• {name, enroll-number} violates property 2.
• {course} violates property 1.
Candidate Key
Consider the relation, CAR
CAR {License_number,Engine_serial_no, model
and year}.
Candidate keys:
1. License_number
2. Engine_serial_number.
Primary key
• A designated candidate key is a primary key.
Primary key must be fully defined i.e no
unknown values or NULL values are permitted.
Example:
• Let candidate key of STUDENT relation be
enroll-number, then
– Every student must have a enroll-number.
– enroll-number is a PRIME attribute
– name, fname, course, grade are NON
PRIME attributes.
Example
Given,
STUDENT( name, fname, course, enroll-number,
grade)
• Let candidate keys be (name, fname) and
(enroll-number) and (name, fname) be the primary
key, then
• There cannot be a student whose name or fname
is not known
• name, fname, enroll-number are PRIME
attributes
• course and grade are NON PRIME attributes
Super Key
A collection of attributes which is a unique
identifier but not minimal.
Let S# be the key of R(S#, P#, SP) then (S#,
P#) is the super key.
• Any set of attributes that includes
License_number in CAR relation such as
{License_number, model, year,make} is a
super key
Entity Integrity
• No primary key value can be NULL.
•Key constraints and entity integrity are
defined on individual relation.
Referential Integrity
Let there be a Relation R1 defined over domain D
having attribute A1,
where A1 is a primary key of R1.
And relation R2 defined over domain D having
attribute A2 that references A1 .
Referential integrity property states that values in A2
may be :
• Null, or
• a value V ( V is the value of A1 in some tuple of R1).
Properties of Referential integrity
• Specified between two relations
• Maintains consistency among two relations.
• States that a attribute value in one relation that
refers to another relation must refer to an
existing tuple in that relation.
Example of Referential Integrity
Consider relation Employee{Id_no,Name,Dept_no}
Id_no Name Dept_no
1101 Ram 01
1102 Shyam 04
Relation:Department{Dept_no,Name,no_of_employee}
Dept_no Name no_of_employee
01 R&M 20
04 Electrical 47
In above both relations value of Dept_no must match
Enrolled:
Students:
