Copyright © 2016 Ramez Elmasri and Shamkant B.

Navathe
 CHAPTER 5

The Relational Data Model and

Relational Database Constraints

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

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
 We review the essentials of the formal relational model in
this chapter
 In practice, there is a standard model based on SQL –
this is described in Chapters 6 and 7 as a language

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

Relational Model Concepts
 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

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

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

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

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:
 <632895, "John Smith", "101 Main St. Atlanta, GA 30332",
"(404) 894-2000">
 This is called a 4-tuple as it has 4 values
 A tuple (row) in the CUSTOMER relation.
 A relation is a set of such tuples (rows)

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

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

Copyright © 2016 Ramez Elmasri and Shamkant B. Navathe Slide 5- 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.
 Example: attribute Cust-name is defined over the
domain of character strings 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

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>

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

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)

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

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- 14
Example – A relation STUDENT

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