CHAPTER 5

The Relational Data Model and

Relational Database Constraints

Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 1- 1

Chapter Outline
◼ Relational Model Concepts
◼ Relational Model Constraints and Relational
Database Schemas
◼ Update Operations and Dealing with Constraint
Violations

Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 5- 2

Relational Model Concepts
◼ A Relation is a mathematical
concept based on the ideas of sets

◼ 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
Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 5- 3
Example of Entity in ER Model

Home_phone

Student

Office_phone Age GPA

Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 5- 4

Informal Definitions of Relations

◼ 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

◼ Each column has a column header that gives an indication

of the meaning of the data items in that column

◼ In the formal model,

◼ rows are called tuples
◼ column header is called an attribute

Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 5- 5

Example of a Relation

Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 5- 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

Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 5- 7

Formal Definitions - Schema
◼ The Relation Schema (or description of a Relation):
◼ Denoted by R(A1, A2, .....An)
◼ R - name of the relation. A1, A2, ..., An - attributes

◼ 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.

Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 5- 8

 Formal Definitions - Tuple
◼ A tuple is an ordered set of values (‘< … >’)
◼ 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:
◼ CUSTOMER (Cust-id, Cust-name, Address, Phone#)
<632895, "John Smith", "101 Main St. Atlanta, GA 30332", "(404) 894-2000">

Relation name

CUSTOMER
Cust-id Cust-name Address Phone#
Tuple 1 632895 John Smith 101 Main St. Atlanta, GA 30332 (404) 894-2000

Tuple 2 624586 Brian Cobb 105 Main St. Atlanta, GA 30332 (404) 888-5000

Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 5- 9

Formal Definitions - Domain
◼ A domain has a logical definition:
◼ Ex: “Mobile numbers” are the set of 11-digit phone numbers in BD

◼ A domain also has a data-type or a format defined for it.

◼ The BD_phone_numbers may have a format: ddddd ddd ddd where
each d is a decimal digit. Ex: 01723 525123
◼ Dates have various formats as yyyy-mm-dd, or as dd,mm,yyyy

◼ 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

Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 5- 10

Formal Definitions - State
A relation state (or just relation) of the relation
schema , denoted by , is a set of tuples .
Each tuple is an ordered list of n values , and each
value is an element of or which means does not
exist or unknown.

◼ Example: attribute Cust-name is defined over the

domain of characters of maximum length 25
◼ dom(Cust-name) is varchar(25)
◼ The role these strings play in the CUSTOMER
relation is that of the name of a customer.

Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 5- 11

Definition Summary
Informal Terms Formal Terms
Table Relation
Column Header Attribute
All possible Column Domain
Values
Row Tuple

Table Definition Schema of a Relation

Populated Table State of the Relation
Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 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:

◼ Attributes in R(A1, A2, ..., An) and the values in t=<v1,
v2, ..., vn> needs to be ordered.

◼ CUSTOMER (Cust-id, Cust-name, Address, Phone#)

t = <632895, "John Smith", "101 Main St. Atlanta, GA 30332", "(404) 894-2000">

Alternative Example:
t = { <Cust-id, 632895>, <Cust-name, “John Smith” >, ……}
◼ This representation may be called as “self-describing”.
Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 5- 13
Same state as previous Figure (but
with different order of tuples)

Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 5- 14

Characteristics Of Relations
◼ Values in a tuple:
◼ All values are considered atomic (indivisible).
◼ Must be from the domain of the attribute
◼ 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 not applicable.

Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 5- 15

CONSTRAINTS
Constraints determine which values are permissible and
which are not in the database.

Inherent or Schema-based or Application based or

Implicit Constraints Explicit Constraints semantic constraints

E.g., relational model E.g., max. cardinality beyond the expressive

does not allow a list ratio constraint in the power of the model
as a value for any ER model. and must be specified
attribute and enforced by the
application programs

Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 5- 16

Relational Integrity Constraints

Schema-based or Explicit Constraints

Three main types expressed in the relational model:

◼ Key constraints
◼ Entity integrity constraints
◼ Referential integrity constraints
◼ Another schema-based 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)

Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 5- 17

Key Constraints
◼ Key:
An attribute or combination of attributes that uniquely
identify an entity/record in a relational table.

Purpose: Access or retrieve data rows from table

according to the requirement.

Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 5- 18

Types of Keys
1. Primary key (PK): The attribute or combination of attributes
that uniquely identifies a row or record in a relation.
◼ Composite key: PK made up of multiple attributes
◼ Surrogate key: A key with no business meaning. Ex: Seq#
◼ Natural key: formed of attribute exist in real world. Ex: SSN
2. Foreign key: Key with NOT NULL constraint

◼ Superkey: an attribute (or set of attributes) that forces every

tuple to be unique in a relation (support NULL value)
◼ Candidate key: least number of attributes that force every
tuple to be unique. One candidate key is chosen as PK
◼ Alternate key: A candidate key that is not the primary key
Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 5- 19
Types of Keys (continued)

Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 5- 20

Key Constraints (continued)
◼ Consider the STUDENT relation

Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 5- 21

Key Constraints (continued)

◼ In the STUDENT relation -

◼ the attribute set {Ssn} is a key of STUDENT

because no two student tuples can have the same

value for Ssn.
◼ Any set of attributes that includes Ssn—for

example, {Ssn, Name, Age}—is a superkey.

Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 5- 22

Common Keys

Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 5- 23

CAR table with two candidate keys –
LicenseNumber chosen as Primary Key

Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 5- 24