Program Studi Manajemen Teknologi

Sekolah Interdisiplin Manajemen & Teknologi

IT235251
MANAJEMEN DATA & INFORMASI
Chapter 02
Relational Data Model &
Relational Database Constraints
Prof. Ir. Arif Djunaidy, M.Sc., Ph.D.
[email protected]
[email protected]
Learning Objectives & Book Reading
Learning Objectives - To Understand:
• Basic principles of the relational model of data
• The modeling concepts and notation of the relational model
• Relational constraints that are considered an important part of
the relational model and are automatically enforced in most
relational DBMS
• Update operations of the relational model and how violations of
integrity constraints are handled

Book reading:
• Elmasri, Chapter 3

Relational Data Model Chapter 02 / 2

 Outline

Relational Model Concepts

Relational Model Constraints and

Relational Database Schemas

Update Operations and Dealing with

Constraint Violations

Relational Data Model Chapter 02 / 3

 Relational Model Concepts
• The relational Model of Data is based on the concept of a
Relation
– The strength of 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
– 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

Relational Data Model Chapter 02 / 4

 Informal Definitions (1)
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)

Relational Data Model Chapter 02 / 5

 Informal Definitions (2)
Example of a Relation

Relational Data Model Chapter 02 / 6

 Informal Definitions (3)
• 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

Relational Data Model Chapter 02 / 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.

Relational Data Model Chapter 02 / 8

 Formal Definitions - Tuple
A tuple is an ordered set of values (enclosed in angled
brackets ‘< … >’)

Each value is derived from an appropriate domain.

<632895, "John Smith", "101 Main St.

A row in the CUSTOMER relation Atlanta, GA 30332", "(404) 894-2000">
is a 4-tuple and would consist of This is called a 4-tuple as it has 4 values
four values, for example:
A tuple (row) in the CUSTOMER relation.

A relation is a set of such tuples

Relational Data Model Chapter 02 / 9

 Formal Definitions - Domain
Example: “USA_phone_numbers” are
A domain has a logical definition the set of 10 digit phone numbers
valid in the U.S.

The USA_phone_numbers may have a

format: (ddd) ddd-dddd where each d is a
A domain also has a data-type or a decimal digit.
format defined for it. Dates have various formats such as year,
month, date formatted as yyyy-mm-dd, or
as dd mm,yyyy etc.

Used to interpret the meaning of the data

elements corresponding to that attribute
The attribute name designates the
role played by a domain in a relation Example: The domain Date may be used to define
two attributes named “Invoice-date” and
“Payment-date” with different meanings

Relational Data Model Chapter 02 / 10

 Formal Definitions - 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.
• Formally: Given R(A1, A2, .........., An), then
r(R) ⊂ dom (A1) X dom (A2) X ....X dom(An)
– R is the name of the relation
– R(A1, A2, …, An) is the schema 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)
Relational Data Model Chapter 02 / 11
 Formal Definitions - Example
Let R(A1, A2) be a relation Let dom(A1) = {0,1}
schema: 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)

this is one possible state (or “population” or

For example: r(R) could be “extension”) r of the relation R, defined
over A1 and A2.
{<0,a> , <0,b> , <1,c> }
It has three 2-tuples: <0,a> , <0,b> , <1,c>

Relational Data Model Chapter 02 / 12

 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

Relational Data Model Chapter 02 / 13

 Example – A relation STUDENT

Relational Data Model Chapter 02 / 14

 Characteristics of Relations (1)

The tuples are not considered to

Ordering of tuples in a be ordered, even though they
relation r(R): appear to be in the tabular
form.

We will consider the attributes in

Ordering of attributes in a R(A1, A2, ..., An) and the values in
t=<v1, v2, ..., vn> to be ordered .
relation schema R (and of • (However, a more general
values within each tuple): alternative definition of relation
does not require this ordering).

Relational Data Model Chapter 02 / 15

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

State 1 of the
Relation STUDENT

State 2 of the
Relation STUDENT

Relational Data Model Chapter 02 / 16

 Characteristics of Relations (2)
• 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 to certain
tuples.
Relational Data Model Chapter 02 / 17
 Characteristics of Relations (3)
• 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

Relational Data Model Chapter 02 / 18

 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)

Relational Data Model Chapter 02 / 19

 Key Constraints (1)
• 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)

Relational Data Model Chapter 02 / 20

 Key Constraints (2)
• Example: Consider the CAR relation schema:
– CAR(State, Reg#, SerialNo, Make, Model, Year)
– CAR has two keys:
• Key1 = {State, 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

Relational Data Model Chapter 02 / 21

 Key Constraints (3)
If a relation has several
candidate keys, one is chosen • The primary key attributes are underlined.
arbitrarily to be the primary key.

Example: Consider the CAR • CAR(State, Reg#, SerialNo, Make, Model, Year)
relation schema: • We chose SerialNo as the primary key

The primary key value is used to

uniquely identify each tuple in a • Provides the tuple identity
relation

• General rule: Choose as primary key the

Also used to reference the tuple smallest of the candidate keys (in terms of size)
from another tuple • Not always applicable – choice is sometimes
subjective

Relational Data Model Chapter 02 / 22

 CAR table with two candidate keys
(License_number & Engine_serial_number)
License_number chosen as Primary Key

Relational Data Model Chapter 02 / 23

 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

Relational Data Model Chapter 02 / 24

Example: COMPANY Database Schema

Relational Data Model Chapter 02 / 25

 Entity Integrity Constraint
• 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.

Relational Data Model Chapter 02 / 26

Referential Integrity Constraint (1)
The previous constraints involve
A constraint involving two relations
a single relation.

Used to specify a relationship among The referencing relation and

tuples in two relations: the referenced relation.

Tuples in the referencing relation R1 have

A tuple t1 in R1 is said to
attributes FK (called foreign key attributes)
reference a tuple t2 in R2 if
that reference the primary key attributes PK
of the referenced relation R2.
t1[FK] = t2[PK].

Relational Data Model Chapter 02 / 27

 Referential Integrity Constraint (2)
• A referential integrity constraint can be displayed in a
relational database schema as a directed arc from
R1.FK to R2.
PK FK
R2

R1
FK

Relational Data Model Chapter 02 / 28

 Statement of Ref. Integ. 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.

R2

R1

Relational Data Model Chapter 02 / 29

 Displaying a relational database
schema and its constraints

Each relation schema can be displayed as a row of attribute names

The name of the relation is written above the attribute names

The primary key attribute (or attributes) will be underlined

A foreign key (referential integrity) constraints is displayed as a directed arc

(arrow) from the foreign key attributes to the referenced table
• Can also point the the primary key of the referenced relation for clarity
Next slide shows the COMPANY relational schema diagram

Relational Data Model Chapter 02 / 30

 Referential Integrity Constraints
for COMPANY database

Relational Data Model Chapter 02 / 31

 Other Types of Constraints
• Semantic Integrity Constraints:
– based on application semantics and cannot be
expressed by the model per se
– Example: “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

Relational Data Model Chapter 02 / 32

 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
• Next slide shows an example state for the COMPANY
database
Relational Data Model Chapter 02 / 33
Populated database state for COMPANY database

Relational Data Model Chapter 02 / 34

 Update Operations on Relations
INSERT a tuple.

DELETE a tuple.

MODIFY a tuple.

Integrity constraints should not be violated by the update

operations.
Updates may propagate to cause other updates automatically.
This may be necessary to maintain integrity constraints.

Relational Data Model Chapter 02 / 35

 Update Operations on Relations
• In case of integrity violation, several actions
can be taken:
– Cancel the operation that causes the violation
(RESTRICT or 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

Relational Data Model Chapter 02 / 36

 Possible violations for INSERT 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

Relational Data Model Chapter 02 / 37

Possible violations for DELETE 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
– 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

Relational Data Model Chapter 02 / 38

Possible violations for MODIFY operation

Similar to a DELETE followed by an

INSERT

MODIFY may violate domain Modifying the primary key (PK):

constraint and NOT NULL constraint
on an attribute being modified Need to specify similar options to
DELETE

Any of the other constraints may

also be violated, depending on the
attribute being modified: Modifying a foreign key (FK): May violate referential integrity

Modifying an ordinary attribute

Can only violate domain constraints
(neither PK nor FK):

Relational Data Model Chapter 02 / 39

 In-Class Exercise
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)
COURSE_QUARTER (Course#, Quarter, BookISBN)
ENROLL (SSN, Course#, Quarter, Grade)
TEXT (BookISBN, BookTitle, Publisher, Author)
Draw a relational schema diagram specifying the foreign keys for
this schema. The same attribute name is used for both the primary
key and the foreign key
Relational Data Model Chapter 02 / 40
End Chapter 02