Lecture # 4 The Relational Data

Model and Relational Database

Constraints
Rashmi Dutta Baruah
Department of Computer Science & Engineering
 Outline

• Relational Model Concepts

• Relational Model Constraints and Relational
Database Schemas
• Update Operations and Dealing with Constraint
Violations

2
 Relational Model Concepts
• The relational Model of Data is based on the
concept of a Relation.

• A Relation is a mathematical concept based on

the ideas of sets.

• The strength of the relational approach to data

management comes from the formal foundation
provided by the theory of relations.

• We review the essentials of the relational

approach in this chapter.

Chapter 5-3
 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 Ted Codd the coveted
ACM Turing Award.

Chapter 5-4
 INFORMAL DEFINITIONS
• RELATION: A table of values

– A relation may be thought of as a set of rows.

– A relation may alternately be though of as a set of columns.
– Each row represents a fact that corresponds to a real-world entity or
relationship.
– Each row has a value of an item or set of items that uniquely
identifies that row in the table.
– Sometimes row-ids or sequential numbers are assigned to identify
the rows in the table.
– Each column typically is called by its column name or column header
or attribute name.

Chapter 5-5
 FORMAL DEFINITIONS
• A Relation may be defined in multiple ways.
• The Schema of a Relation: R (A1, A2, .....An)
Relation schema R is defined over attributes A1, A2, .....An
For Example -
CUSTOMER (Cust-id, Cust-name, Address, Phone#)

Here, CUSTOMER is a relation defined over the four

attributes Cust-id, Cust-name, Address, Phone#, each of
which has a domain or a set of valid values. For example,
the domain of Cust-id is 6 digit numbers.

Chapter 5-6
 FORMAL DEFINITIONS
• A tuple is an ordered set of values
• Each value is derived from an appropriate domain.
• Each row in the CUSTOMER table may be referred to as a
tuple in the table and would consist of four values.
• <632895, "John Smith", "101 Main St. Atlanta, GA 30332", "(404) 894-2000">
is a tuple belonging to the CUSTOMER relation.
• A relation may be regarded as a set of tuples (rows).
• Columns in a table are also called attributes of the relation.

Chapter 5-7
 FORMAL DEFINITIONS
• A domain has a logical definition: e.g.“USA_phone_numbers”

• A domain may have 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. E.g., Dates have various
formats such as monthname, date, year or yyyy-mm-dd, or
dd mm,yyyy etc.

• An attribute designates the role played by the domain. E.g.,

the domain Date may be used to define attributes “Invoice-
date” and “Payment-date”.

Chapter 5-8
 FORMAL DEFINITIONS
• The relation is formed over the cartesian product of the sets; each
set has values from a domain; that domain is used in a specific role
which is conveyed by the attribute name.

• For example, attribute Cust-name is defined over the domain of

strings of 25 characters. The role these strings play in the
CUSTOMER relation is that of the name of customers.

• Formally, Given R(A1, A2, .........., An)

r(R)  dom (A1) X dom (A2) X ....X dom(An)

• R: schema of the relation

• r of R: a specific "value" or population of R.
• R is also called the intension of a relation
• r is also called the extension of a relation

Chapter 5-9
 FORMAL DEFINITIONS
• Let S1 = {0,1}
• Let S2 = {a,b,c}

• Let R  S1 X S2

• Then for example: r(R) = {<0,a> , <0,b> , <1,c> }

is one possible “state” or “population” or
“extension” r of the relation R, defined over
domains S1 and S2. It has three tuples.

Chapter 5-10
 DEFINITION SUMMARY
Informal Terms Formal Terms

Table Relation
Column Attribute/Domain
Row Tuple
Values in a column Domain
Table Definition Schema of a Relation
Populated Table Extension
Chapter 5-11
 Example - Figure 5.1

Chapter 5-12
 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): We will consider the attributes
in R(A1, A2, ..., An) and the values in t=<v1, v2, ..., vn> to be
ordered .
(However, a more general alternative definition of
relation does not require this ordering).
• Values in a tuple: All values are considered atomic
(indivisible). A special null value is used to represent
values that are unknown or inapplicable to certain tuples.

Chapter 5-13
 CHARACTERISTICS OF RELATIONS

• Notation:
- We refer to component values of a tuple t by t[Ai]
= vi (the value 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.

Chapter 5-14
 CHARACTERISTICS OF RELATIONS- Figure 5.2

Chapter 5-15
 Relational Integrity Constraints

• Constraints are conditions that must hold on all

valid relation instances. There are three main types
of constraints:
1. Key constraints
2. Entity integrity constraints
3. Referential integrity constraints

Chapter 5-16
 Key Constraints and Constraints
on NULL values
• Superkey of R: A set of attributes SK of R such that 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.
Example: The CAR relation schema:
CAR(State, Reg#, SerialNo, Make, Model, Year)
has two keys Key1 = {State, Reg#}, Key2 = {SerialNo}, which are also
superkeys. {SerialNo, Make} is a superkey but not a key.

• If a relation has several candidate keys, one is chosen

arbitrarily to be the primary key. The primary key attributes are
underlined.
• Another constraint on attributes specifies whether NULL values
are or are not permitted.
Chapter 5-17
 Key Constraints

5.4

Chapter 5-18
 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)
• Note: Other attributes of R may be similarly constrained
to disallow null values, even though they are not members
of the primary key.

Chapter 5-19
 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 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.

Chapter 5-20
 Referential Integrity
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 the
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.

Chapter 5-21
 Other Types of Constraints
Semantic Integrity Constraints:
- based on application semantics and cannot be
expressed by the model per se
- E.g., “the max. no. of hours per employee for all
projects he or she works on is 56 hrs per week”
- A constraint specification language may have to
be used to express these
- SQL-99 allows triggers and ASSERTIONS to allow
for some of these

Chapter 5-22
 5.5

Chapter 5-23
 5.6

Chapter 5-24
 5.7

Chapter 5-25
 Update Operations on Relations
• INSERT a tuple.
• DELETE a tuple.
• MODIFY a tuple.

• Integrity constraints should not be violated by the update

operations.
• Several update operations may have to be grouped
together.
• Updates may propagate to cause other updates
automatically. This may be necessary to maintain integrity
constraints.

Chapter 5-26
 Update Operations on Relations
• In case of integrity violation, several actions can
be taken:
– Cancel the operation that causes the violation (REJECT
option)
– Perform the operation but inform the user of the
violation
– Trigger additional updates so the violation is corrected
(CASCADE option, SET NULL option)
– Execute a user-specified error-correction routine

Chapter 5-27
 Update Operations and Constraints
violations
• Insert <‘Cecilia’, ‘F’, ‘Kolonsky’, NULL,’1960-04-05’, ‘6375 Windy Lane,
Katy, Tx’, F, 28000, NULL, 4>
• Delete the EMPLOYEE tuple with Ssn = ‘333445555’
• Update the Ssn of the EMPLOYEE tuple with Ssn = ‘999887777’
• Update the salary of the EMPLOYEE tuple with Ssn = ‘999887777’ to
28000.

• Can these operations violate any constraints?

28
 In-Class Exercise
(Taken from Exercise 5.15)
Consider the following relations for a database that keeps
track of student enrollment in courses and the books adopted
for each course:
STUDENT(SSN, Name, Major, Bdate)
COURSE(Course#, Cname, Dept)
ENROLL(SSN, Course#, Quarter, Grade)
BOOK_ADOPTION(Course#, Quarter, Book_ISBN)
TEXT(Book_ISBN, Book_Title, Publisher, Author)
Draw a relational schema diagram specifying the foreign
keys for this schema.

Chapter 5-29
 Summary
• We discussed the modeling concepts, data structures, and
constraints provided by the relational model of data.
– Domains, attributes, and tuples
– Relation, relation schema, relation state
• Characteristics of relations are discussed
• The schema constraints pertaining to relational model are
discussed.
– Domain constraints, key constraints (including concepts of superkey,
key, and primary key), and NOT NULL constraints on attributes.
– Entity integrity and referential integrity constraint
• Modification operations on relational model are discussed.
– Insert, Delete, Update

30