Relational Data Model

Presented by
Dr. B.Thirumala Rao,
Professor, CSE
 Agenda
 Relational Model Concepts
 Relational Model Constraints
 Relational Database Schemas
 Update Operations
 Dealing with Constraint Violations

Dr.B.Thirumala Rao, Professor, CSE 2

 Relational Model Concepts
 The Relational Model of Data is based on the
concept of a Relation
 The relational approach to data management
comes from the formal foundation provided by
the theory of relations
 A Relation is a mathematical concept based on
the ideas of sets

Dr.B.Thirumala Rao, Professor, CSE 3

 Relational Model Concepts
 The model was first proposed by Dr. E.F. Codd
of IBM Research 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
Dr. Codd the coveted ACM Turing Award

Dr.B.Thirumala Rao, Professor, CSE 4

 Informal Definitions
 Informally, a relation looks like a table of values.
 A relation typically contains a set of rows.
 The data elements in each row represent certain facts
that correspond to a real-world entity or
relationship
◦ In the formal model, rows are called tuples
 Each column has a column header that gives an
indication of the meaning of the data items in that
column
◦ In the formal model, the column header is called an attribute
name (or just attribute)

Dr.B.Thirumala Rao, Professor, CSE 5

 Example of a Relation

Dr.B.Thirumala Rao, Professor, CSE 6

 Informal Definitions
 Key of a Relation:
 Each row has a value of a data item (or set of
items) that uniquely identifies that row in the
table
◦ Called the key
 In the STUDENT table, SSN is the key
 Sometimes row-ids or sequential numbers are
assigned as keys to identify the rows in a table
◦ Called artificial key or surrogate key

Dr.B.Thirumala Rao, Professor, CSE 7

 Formal Definitions - Schema
 The Schema (or description) of a Relation:
◦ Denoted by R(A1, A2, .....An)
◦ R is the name of the relation
◦ The attributes of the relation are A1, A2, ..., An
 Example:
◦ CUSTOMER (Cust-id, Cust-name, Address, Phone#)
◦ CUSTOMER is the relation name
◦ Defined over the four attributes: Cust-id, Cust-name, Address,
Phone#
 Each attribute has a domain or a set of valid values.
◦ For example, the domain of Cust-id is 6 digit numbers.

Dr.B.Thirumala Rao, Professor, CSE 8

 Formal Definitions - Domain
 A domain has a logical definition:
◦ Example: “USA_phone_numbers” are the set of 10 digit phone numbers
valid in the U.S.
 A domain also has a data-type or a format defined for it.
◦ The USA_phone_numbers may have a format: (ddd)ddd-dddd where
each d is a decimal digit.
◦ Dates have various formats such as year, month, date formatted as yyyy-
mm-dd, or as dd mm,yyyy etc.
 The attribute name designates the role played by a
domain in a relation:
◦ Used to interpret the meaning of the data elements corresponding to
that attribute
◦ Example: The domain Date may be used to define two attributes named
“Invoice-date” and “Payment-date” with different meanings

Dr.B.Thirumala Rao, Professor, CSE 9

 Formal Definitions - Tuple
 A tuple is an ordered set of values (enclosed in angled
brackets ‘< … >’)
 Each value is derived from an appropriate domain.
 A row in the CUSTOMER relation is a 4-tuple and
would consist of four values, for example:
 Schema: CUSTOMER (Cust-id, Cust-name, Address, Phone#)
◦ Row/ Tuple: <632895, "John Smith", "101 Main St. Atlanta, GA
30332“, "(404) 894-2000">
◦ This is called a 4-tuple as it has 4 values
 A relation is a set of such tuples (rows)

Dr.B.Thirumala Rao, Professor, CSE 10

 Formal Definitions – Relation or
Relation State
 The relation state is a subset of the Cartesian
product of the domains of its attributes
◦ each domain contains the set of all possible values the attribute
can take.
 A relation (or relation state) r of the relation schema
R(A1, A2, ..., An), also denoted by r(R), is a set of n-tuples r =
{t1, t2, ..., tm}.
 Each n-tuple t is an ordered list of n values t =<v1, v2, ..., vn>,
where each value vi, 1 ≤ i ≤ n, is an element of dom(Ai) or is
a special NULL value.

Dr.B.Thirumala Rao, Professor, CSE 11

 Formal Definitions - Summary
 Formally,
◦ Given R(A1, A2, .........., An)
◦ r(R) ⊂ dom (A1) X dom (A2) X ....X dom(An)
 R(A1, A2, …, An) is the schema of the relation
 R is the name of the relation
 A1, A2, …, An are the attributes of the relation
 r(R): a specific state (or "value" or “population”) of
relation R – this is a set of tuples (rows)
◦ r(R) = {t1, t2, …, tn} where each ti is an n-tuple
◦ ti = <v1, v2, …, vn> where each vj element-of dom(Aj)

Dr.B.Thirumala Rao, Professor, CSE 12

 Formal Definitions - Example
 Let R(A1, A2) be a relation schema:
◦ Let dom(A1) = {0,1}
◦ Let dom(A2) = {a,b,c}
 Then: dom(A1) X dom(A2) is all possible combinations:
{<0,a> , <0,b> , <0,c>, <1,a>, <1,b>, <1,c> }
 The relation state r(R) ⊂ dom(A1) X dom(A2)
 For example: r(R) could be {<0,a> , <0,b> , <1,c> }
◦ this is one possible state (or “population” or “extension”) r of
the relation R, defined over A1 and A2.
◦ It has three 2-tuples: <0,a> , <0,b> , <1,c>

Dr.B.Thirumala Rao, Professor, CSE 13

 Definition Summary

Informal Terms Formal Terms

Table Relation
Column Header Attribute
All possible Column Values Domain
Row Tuple
Table Definition Schema of a Relation
Populated Table State of the Relation

Dr.B.Thirumala Rao, Professor, CSE 14

 Example of a Relation

Dr.B.Thirumala Rao, Professor, CSE 15

 Characteristics Of Relations
 Ordering of tuples in a relation r(R):
◦ The tuples are not considered to be ordered, even
though they appear to be in the tabular form.
 Ordering of attributes in a relation schema R
(and of values within each tuple):
◦ n-tuple is an ordered list of n values
◦ If we consider the attributes in R(A1, A2, ..., An) and
the values in t=<v1, v2, ..., vn> to be ordered .

Dr.B.Thirumala Rao, Professor, CSE 16

 Same state as previous Figure (but
with different order of tuples)

Dr.B.Thirumala Rao, Professor, CSE 17

 Characteristics Of Relations
 Values in a tuple:
 All values are considered atomic (indivisible).
 Each value in a tuple must be from the domain
of the attribute for that column
◦ If tuple t = <v1, v2, …, vn> is a tuple (row) in the
relation state r of R(A1, A2, …, An)
◦ Then each vi must be a value from dom(Ai)
 A special null value is used to represent values
that are unknown or inapplicable or missing to
certain tuples

Dr.B.Thirumala Rao, Professor, CSE 18

 Characteristics Of Relations
 Notation:
 We refer to component values of a
tuple t by:
◦ t[Ai] or t.Ai
◦ This is the value vi of attribute Ai for tuple t
 Similarly, t[Au, Av, ..., Aw] refers to the
subtuple of t containing the values of
attributes Au, Av, ..., Aw, respectively in t

Dr.B.Thirumala Rao, Professor, CSE 19

 Relational Integrity Constraints
 Constraints are conditions that must hold on
all valid relation states.
 There are three main types of constraints in the
relational model:
◦ Key constraints
◦ Entity integrity constraints
◦ Referential integrity constraints
 Another implicit constraint is the domain
constraint
◦ Every value in a tuple must be from the domain of its
attribute (or it could be null, if allowed for that attribute)
Dr.B.Thirumala Rao, Professor, CSE 20
 Key Constraints
 Superkey of R:
 Is a set of attributes SK of R with the following
condition:
◦ No two tuples in any valid relation state 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]
◦ This condition must hold in any valid state r(R)
 Key of R:
◦ A "minimal" superkey
◦ That is, a key is a superkey K such that removal of any attribute
from K results in a set of attributes that is not a superkey (does
not possess the superkey uniqueness property)

Dr.B.Thirumala Rao, Professor, CSE 21

 Key Constraints (continued)
 Example: Consider the CAR relation schema:
◦ CAR(State, Reg#, SerialNo, Make, Model,Year)
◦ CAR has two keys:
 Key1 = {Reg#}
 Key2 = {SerialNo}
◦ Both are also superkeys of CAR
◦ {SerialNo, Make} is a superkey but not a key.
 In general:
◦ Any key is a superkey (but not vice versa)
◦ Any set of attributes that includes a key is a superkey
◦ A minimal superkey is also a key

Dr.B.Thirumala Rao, Professor, CSE 22

 Key Constraints (continued)
 If a relation has several candidate keys, one is chosen
arbitrarily to be the primary key.
◦ The primary key attributes are underlined.
 Example: Consider the CAR relation schema:
◦ CAR(State, Reg#, SerialNo, Make, Model,Year)
◦ We chose SerialNo as the primary key
 The primary key value is used to uniquely identify each
tuple in a relation
◦ Provides the tuple identity
 Also used to reference the tuple from another tuple
◦ General rule: Choose as primary key the smallest of the
candidate keys (in terms of size)
◦ Not always applicable – choice is sometimes subjective
Dr.B.Thirumala Rao, Professor, CSE 23
 CAR table with two candidate keys –
LicenseNumber chosen as Primary Key

Dr.B.Thirumala Rao, Professor, CSE 24

 Relational Database Schema
 Relational Database Schema:
 A set S of relation schemas that belong to the
same database.
 S is the name of the whole database schema
 S = {R1, R2, ..., Rn}
 R1, R2, …, Rn are the names of the individual
relation schemas within the database S
 Following slide shows a COMPANY database
schema with 6 relation schemas

Dr.B.Thirumala Rao, Professor, CSE 25

 COMPANY Database Schema

Dr.B.Thirumala Rao, Professor, CSE 26

 Entity Integrity
 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)
◦ If PK has several attributes, null is not allowed in any of these
attributes
 Note: Other attributes of R may be constrained to
disallow null values, even though they are not members
of the primary key.

Dr.B.Thirumala Rao, Professor, CSE 27

 Referential Integrity
 A constraint involving two relations
 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
primary key attributes PK of the referenced relation
R2.
 A tuple t1 in R1 is said to reference a tuple t2 in R2 if
t1[FK] = t2[PK].
 A referential integrity constraint can be displayed in a
relational database schema as a directed arc from R1.FK
to R2.
Dr.B.Thirumala Rao, Professor, CSE 28
 Referential Integrity (or foreign key)
Constraint
 Statement of the constraint
 The value in the foreign key column (or columns) FK of
the the referencing relation R1 can be either:
◦ (1) a value of an existing primary key value of a corresponding
primary key PK in the referenced relation R2, or
◦ (2) a null.
 In case (2), the FK in R1 should not be a part of its
own primary key.

Dr.B.Thirumala Rao, Professor, CSE 29

 Referential Integrity Constraints for
COMPANY database

Dr.B.Thirumala Rao, Professor, CSE 30

 Other Types of Constraints
 Semantic Integrity Constraints:
◦ based on application semantics and cannot be expressed by the
model
◦ Example: “the max. no. of hours per employee for all projects he
or she works on is 56 hrs per week”

Dr.B.Thirumala Rao, Professor, CSE 31

 Populated database state
 Each relation will have many tuples in its current
relation state
 The relational database state is a union of all the
individual relation states
 Whenever the database is changed, a new state
arises
 Basic operations for changing the database:
◦ INSERT a new tuple in a relation
◦ DELETE an existing tuple from a relation
◦ MODIFY an attribute of an existing tuple

Dr.B.Thirumala Rao, Professor, CSE 32

Dr.B.Thirumala Rao, Professor, CSE 33
 Possible violations for each
operation
 INSERT may violate any of the constraints:
◦ Domain constraint:
 if one of the attribute values provided for the new tuple is not of the
specified attribute domain
◦ Key constraint:
 if the value of a key attribute in the new tuple already exists in
another tuple in the relation
◦ Referential integrity:
 if a foreign key value in the new tuple references a primary key value
that does not exist in the referenced relation
◦ Entity integrity:
 if the primary key value is null in the new tuple

Dr.B.Thirumala Rao, Professor, CSE 34

 Possible violations for each
operation
 DELETE may violate only referential integrity:
◦ If the primary key value of the tuple being deleted is
referenced from other tuples in the database
 Can be remedied by several actions: RESTRICT, CASCADE,
SET NULL (see Chapter 8 for more details)
 RESTRICT option: reject the deletion
 CASCADE option: propagate the new primary key value into
the foreign keys of the referencing tuples
 SET NULL option: set the foreign keys of the referencing
tuples to NULL
◦ One of the above options must be specified during
database design for each foreign key constraint

Dr.B.Thirumala Rao, Professor, CSE 35

 Possible violations for each
operation
 UPDATE may violate domain constraint and NOT
NULL constraint on an attribute being modified
 Any of the other constraints may also be violated,
depending on the attribute being updated:
◦ Updating the primary key (PK):
 Similar to a DELETE followed by an INSERT
 Need to specify similar options to DELETE
◦ Updating a foreign key (FK):
 May violate referential integrity
◦ Updating an ordinary attribute (neither PK nor FK):
 Can only violate domain constraints

Dr.B.Thirumala Rao, Professor, CSE 36

Logical Database Design / ER to
Relational Mapping
1.Converting Strong entity types
2. Converting Weak entity types
3. Converting relationships
(1: 1, 1:M, M:N and Unary, Binary, Ternary)
Converting Strong entity types
Each entity type becomes a Relation

Each single-valued attribute becomes a Attribute

Derived attributes are ignored

Composite attributes are represented by components

Multi-valued attributes are represented by a separate relation

The key attribute of the entiry type becomes the primary key of the
Relation
Entity example
Here address is a composite attribute

Years of service is a derived attribute

(can be calculated from date of
joining and current date)

Skill set is a multi-valued attribute

The relational Schema

Employee (E#, Name, Door_No, Street, City, Pincode, Date_Of_Joining)

Emp_Skillset( E#, Skillset)

Entity Example (Contd…)

Employee
SkillSet
EmpCode PK
EmpCode FK
EmpName
Skills
DateofJoining
SkillSet
 Converting weak entity types
Weak entity types are converted
into a relation of their own, with
the primary key of the strong
entity acting as a foreign key in the
relation
This foreign key along with the key
of the weak entity form the
composite primary key of this
relation

The Relational Schema

Employee (E# ,…….)

Dependant (Employee_ID, Dependant_ID, Name, Address)
 Converting weak entity types
(Contd…)

Employee
Dependent
EmpCode PK
EmpCode PK /FK
EmpName
Dependent_ID PK
DateofJoining
Name
SkillSet
Address
Converting relationships
The way relationships are represented depends on the cardinality
and the degree of the relationship

The possible cardinalities are:

 1:1, 1:M, N:M

The degrees are:

 Unary
 Binary
 Ternary …
Binary 1:1
1 head 1
Employee department
of

Case 1: Combination of participation types

◦ The primary key of the partial participant will become the foreign key of the total particip
◦ Employee( E#, Name,…)

◦ Department (Dept#, Name…,Head)

 Binary 1 : 1

Employee Department
EmpCode PK DeptCode PK
EmpName DeptName
DateofJoining Location
SkillSet Head FK
Binary 1:1
Employee Sits_on CHAIR

Case 2: Uniform participation types

The primary key of either of the participants can become a foreign key in
the other
◦ Employee (E#,name…)

◦ Chair( item#, model, location, used_by)

(or)
◦ Employee ( E#, Name….Sits_on)

◦ Chair (item#,….)
Binary 1 : 1
Employee
Chair
EmpCode PK
ItemNo PK
EmpName
DateofJoining Model
SkillSet Location
Used_By FK
Employee
EmpCode PK Chair
EmpName
ItemNo PK
DateofJoining
SkillSet Model
Sits_On FK Location
Binary 1:N
1 N
Teacher teaches Subject

The primary key of the relation on the “1” side of the relationship
becomes a foreign key in the relation on the “N” side

◦ Teacher (ID, Name,Telephone, ...)

◦ Subject (Code, Name, ...,Teacher)

Binary 1 : N

Teacher Subject
TeacherID PK SubCode PK
Name SubName
Telephone Duration
Cabin TeacherID FK
Binary M:N
M N
Enrolls Course
Student

A new relation is created to represent the relationship

Contains two foreign keys - one from each of the participants in the relationship

The primary key of the new relation is the combination of the two foreign keys

◦ Student (Sid#,Title…) Course(C#,CName,…)

◦ Enrolls (Sid#, C#)

Binary M : N

Course
CourseID PK
Coursename Enrolls
StudentCode PK / FK
CourseID PK / FK
Student
DOIssue
StudentID PK Status
StudentName
DOB
Address
 Unary 1:1

• Consider employees who are also a

couple
• The primary key field itself will become
foreign key in the same relation

Employee( E#, Name,... Spouse)

Unary 1 : 1

Employee
EmpCode PK
EmpName
DateofJoining
SkillSet
Spouse FK
 Unary 1:N
• The primary key field
itself will become foreign
key in the same relation

• Same as unary 1:1

Employee( E#, Name,…,Manager)

Unary 1 : N

Employee
EmpCode PK
EmpName
DateofJoining
SkillSet
Manager FK
Unary M:N
Guarantor_of
M

Employee N

There will be two resulting relations. One to represent the entity and anoth
to represent the M:N relationship as follows

◦ Employee( E#, Name,…)

◦ Guaranty( Guarantor, beneficiary)

 Unary M : N

Employee
Guaranty
EmpCode PK
Guarantor PK/FK
EmpName
Beneficiary PK /FK
DateofJoining
SkillSet
Ternary relationship
Represented by a new relation.

The new relation contains three

foreign keys - one from each of the
participating Entities

The primary key of the new relation

is the combination of all three foreign
keys

Prescription (Doctor#, Patient #, Medicine_Name)

 Ternary relationship….

Prescription
Doctor
DocID PK / FK
DocID PK
PatCode PK / FK
Title
MedName PK/ FK
NextVisit
Patient
PatCode PK
PatName Medicine
DOB MedName PK
Address ExpDate
 ER-to-Relational Mapping Algorithm
(cont’d.)
Step 6: Mapping of Multivalued attributes
I. For each multivalued attribute A, create a new relation R. This relation R
will include an attribute corresponding to A, plus the primary key attribute K-as a
foreign key in R-of the relation that represents the entity type of relationship type
that has A as an attribute.
II. The primary key of R is the combination of A and K. If the multivalued attribute is
composite, we include its simple components.

Dnum
References
Dnumber
DEPARTM
ENTS

Primary
60 Key
 ER-to-Relational Mapping Algorithm
(cont’d.)
Step 7: Mapping of N-ary Relationship Types

I. For each n-ary relationship type R, where n>2, create a new

relationship S to represent R.
II. Include as foreign key attributes in S the primary keys of the
relations that represent the participating entity types.
III. Also include any simple attributes of the n-ary relationship type
(or simple components of composite attributes) as attributes of
S.

61
 Relational Schema Diagram

62
 Mapping EER Model Constructs to
Relations
• Step8: Options for Mapping Specialization or
Generalization.
Convert each specialization with m subclasses {S1, S2,….,Sm} and
generalized superclass C, where the attributes of C are {k,a1,…an} and k
is the (primary) key, into relational schemas using one of the four following
options:
Option 8A: Multiple relations-Superclass and subclasses.
Create a relation L for C with attributes Attrs(L) = {k,a1,…an} and
PK(L) = k.
Create a relation Li for each subclass Si, 1 < i < m, with the
attributesAttrs(Li) = {k} U {attributes of Si} and PK(Li)=k.
This option works for any specialization (total or partial, disjoint of
over-lapping).

63
 Option 8 a)
Converting
Specialization

64
 Mapping of Specialization or
Generalization
 Step8: Options for Mapping Specialization or
Generalization
Option 8B: Multiple relations-Subclass relations only
Create a relation Li for each subclass Si, 1 < i < m, with the
attributes Attr(Li) = {attributes of Si} U {k,a1…,an} and PK(Li) = k.
This option only works for a specialization whose subclasses are
total (every entity in the superclass must belong to (at least) one
of the subclasses).

65
 Option 8 b)
Converting
Generalization

66
 Mapping of Specialization or
Generalization
Option 8C: Single relation with one type attribute.
Create a single relation L with attributes Attrs(L) = {k,a1,…an}
U {attributes of S1} U…U {attributes of Sm} U {t} and PK(L) = k.
The attribute t is called a type (or discriminating) attribute that
indicates the subclass to which each tuple belongs

Dr.B.Thirumala Rao, Professor, CSE 67

 Option 8 c)
Mapping specialization
or
Generalization
with one option

68
 Mapping of Specialization or
Generalization
Option 8D: Single relation with multiple type
attributes.
Create a single relation schema L with attributes
Attrs(L) = {k,a1,…an} U {attributes of S1} U…U
{attributes of Sm} U {t1, t2,…,tm} and PK(L) = k. Each ti, 1
< I < m, is a Boolean type attribute indicating whether a
tuple belongs to the subclass Si.

Dr.B.Thirumala Rao, Professor, CSE 69

 Option 8 d)
Mapping specialization
or
Generalization

Dr.B.Thirumala Rao, Professor, CSE 70

 Mapping of Shared Subclasses
(Multiple Inheritance)
 Apply any of the options discussed in step 8 to a shared subclass.

71
 Mapping of Shared Subclasses
(Multiple Inheritance)
 option 8A for PERSON/{EMPLOYEE, ALUMNUS, STUDENT}
 option 8C for EMPLOYEE/{STAFF, FACULTY,
STUDENT_ASSISTANT} by including the type attribute
Employee_type, and
 option 8D for STUDENT_ASSISTANT/{RESEARCH_ASSISTANT,
TEACHING_ ASSISTANT} by including the type attributes Ta_flag
and Ra_flag in EMPLOYEE, STUDENT/ STUDENT_ASSISTANT by
including the type attributes Student_assist_flag in STUDENT, and
STUDENT/{GRADUATE_STUDENT,
UNDERGRADUATE_STUDENT} by including the type attributes
Grad_flag and Undergrad_flag in STUDENT
 all attributes whose names end with type or flag are type fields.

Dr.B.Thirumala Rao, Professor, CSE 72

 Step 9: Mapping of Union Categories

73