CHAPTER FOUR

RELATIONAL DATA MODEL

3.1 Relational Data Model Concepts
The relational model represents the database as a collection of relations. Informally, each relation
resembles a table of values or, to some extent, a "flat" file of records. When a relation is thought of as a
table of values, each row in the table represents a collection of related data values. We introduced entity
types and relationship types as concepts for modeling real-world data in Chapter 3. In the relational
model, each row in the table represents a fact that typically corresponds to a real-world entity or
relationship. The table name and column names are used to help in interpreting the meaning of the
values in each row.
EXAMPLE
STUDENT Name SSN HomePhone Address Age GPA
Benjamin Bayer 305-61-2435 373-1616 2918 Bluebonnet Lane 19 3.21
Dick Davidson 422-11-2330 Null 3452 Elgin Road 25 3.53
Charles Cooper 489-22-1100 376-9821 265 Lark Lane 28 3.93

The above table is called STUDENT because each row represents facts about a particular student entity.
The column names—Name, SSN, Homephone, address, etc.—specify how to interpret the data values in
each row, based on the column each value is in. All values in a column are of the same data type.
In the formal relational model terminology, a row is called a tuple, a column header is called an
attribute, and the table is called a relation. The data type describing the types of values that can appear
in each column is called a domain.
Alternative Terminology for Relational Model

1
 Informal term Formal term
Table  Relation
Column header  Attribute
All possible column values  Domain
Row  Tuple
Table definition  Relation schema

We now define these terms—domain, tuple, attributes, relation, and relation schema—more precisely.
Domains of attributes
A domain D is a set of atomic values. By atomic we mean that each value in the domain is indivisible
as far as the relational model is concerned. A common method of specifying a domain is to specify a
data type from which the data values forming the domain are drawn. It is also useful to specify a name
for the domain, to help in interpreting its values. Some examples of domains follow:
 USA_phone_numbers: The set of 10-digit phone numbers valid in the United States.
 Local_phone_numbers: The set of 7-digit phone numbers valid within a particular area code
in the United States.
 Social_security_numbers: The set of valid 9-digit social security numbers.
 Names: The set of names of persons.
 Grade_point_averages: Possible values of computed grade point averages; each must be a real
(floating point) number between 0 and 4.
 Employee_ages: Possible ages of employees of a company; each must be a value between 15
and 80 years old.
 Academic_department_names: The set of academic department names, such as Computer
Science, Economics, and Physics, in a university.
 Academic_department_codes: The set of academic department codes, such as CS, ECON, and
PHYS, in a university.
The preceding is called logical definitions of domains. A data type or format is also specified for each
domain. For example, the data type for the domain USA_phone_numbers can be declared as a character
string of the form (ddd)ddd-dddd, where each d is a numeric (decimal) digit and the first three digits
form a valid telephone area code. The data type for Employee_ages is an integer number between 15 and
80. For Academic_department_names, the data type is the set of all character strings that represent valid

2
department names. A domain is thus given a name, data type, and format. Additional information for
interpreting the values of a domain can also be given; for example, a numeric domain such as
Person_weights should have the units of measurement—pounds or kilograms.
A relation schema R, denoted by R(A1, A2, . . ., An), is made up of a relation name R and a list of
attributes A1, A2, . . ., An. Each attribute Ai is the name of a role played by some domain D in the
relation schema R. D is called the domain of Ai and is denoted by dom(Ai). A relation schema is used to
describe a relation; R is called the name of this relation. The degree of a relation is the number of
attributes n of its relation schema.
An example of a relation schema for a relation of degree 7, which describes university students, is the
following:
STUDENT (Name, SSN, HomePhone, Address, OfficePhone, Age, GPA)
For this relation schema, STUDENT is the name of the relation, which has seven attributes. We can
specify the following previously defined domains for some of the attributes of the STUDENT relation:
dom(Name) = Names; dom(SSN) = Social_security_numbers; dom(HomePhone) =
Local_phone_numbers, dom(OfficePhone) = Local_phone_numbers, and dom(GPA) =
Grade_point_averages.
The Cardinality of a relation is the number of tuples n of its relation.
Example 2. Instance of staff and branch

3
Characteristics of Relations
The earlier definition of relations implies certain characteristics that make a relation different from a file.
We now discuss some of these characteristics
Ordering of Tuples in a Relation
 A relation is defined as a set of tuples and columns
 Tuples in a relation don’t have any particular order
 But when we display a relation as a table, the rows are displayed in certain order.
 Tuple ordering is not part of a relation definition, because a relation attempts to represent facts
at a logical or abstract level and many logical orders can be specified on a relation. (Eg. Tuples
in a student table can be ordered by Name, SSN or by AGE or by some other attributes)
 The definition of a relation does not specify any order, and there is no preference for one
logical ordering over another.
Ordering of Values within a Tuple
 An n-tuple is an ordered list of n values, so the ordering of values in a tuple- and hence of
attributes in a relation schema- is important.

4
  Actually, at logical level, the order of attributes and their values is not that important as long as
the correspondence between attributes and values is maintained. (alternative definition if
possible)
Values and Nulls in the tuples.
 Each value in a tuple is an atomic value; that is, it is not divisible within the framework of the
basic relational model.
 Composite and multi valued attributes are not allowed.
 Multivalued attributes are represented by separate relations, and composite attributes are
represented only by their simple component attributes in the basic relational model
 Null is used to represent the values of attributes that may be unknown or mayu not apply to a
tuple.
Interpretation (Meaning) of a Relation
 The relational schema can be interpreted as a declaration of assertion
Example
The schema of STUDENT relation asserts that, in general, a student entity has a Name, SSN,
HomePhone, Address, Age, and GPA.
 Each tuple in the relation can then be interpreted as a fact or a particular instance of the
assertion.
Example
The first tuple in the relation STUDENT asserts the fact that there is a student whose name is
Benjamin Bayer, SSN is 305-61-2435, Age is 19 and so on.
 Some relations may represent facts about entities, whereas other relations may represent facts
about relationships. The relational model represents facts about both entities and relationships
uniformly as relations.

5
Relation Scheme

Consists of relation name, and a set of attributes or field names or column names. Each attribute has an

associated domain.
Example
STUDENT ( studentName : string,
rollNumber: string,
phoneNumber : integer,
Relation yearOfAdmission : integer,
name branchOfStudy : string )

Attribute Names Domains

Relation Instance
A finite set of tuples constitute a relation instance.
A tuple of relation with scheme R = (A1, A2, … , Am) is an ordered sequence of values(v1,v2, ... ,vm)
such that vi ∈ domain (Ai), 1≤i ≤m
STUDENT
studentName rollNumber phoneNumber yearofAdmission branchOfStudy
Abebe Adu/173/09 0911090909 2009 CS
Hagos Adu/`74/09 0911090909 2009 ES

N.B: No duplicate tuples (or rows) in a relation instance.

Keys for a Relation
•Key: A set of attributes K, whose values uniquely identify a tuple in any instance. And none of the
proper subsets of K has this property

6
Example: {rollNumber} is a key for student relation.
{rollNumber, name} – values can uniquely identify a tuple
• but the set is not minimal
•not a Key
•A key can not be determined from any particular instance data
 it is an intrinsic property of a scheme
 it can only be determined from the meaning of attributes
 A relation can have more than one key.
 Each of the keys is called a candidate key
Example: book (isbnNo, authorName, title, publisher, year )
(Assumption : books have only one author )
Keys : {isbnNo }, {authorName, title }
 A relation has at least one key
 the set of all attributes, in case no proper subset is a key.
 Superkey : A set of attributes that contains any key as a subset.
 A key can also be defined as a minimal superkey
 Primary Key : One of the candidate keys chosen for indexing purposes
Relational model constraints
Relational model constraint describes the various restrictions on data that can be specified on a relational
database schema in the form of constraints. These include domain constraints, key constraints, entity
integrity, and referential integrity constraints.
Domain constraint
Domain constraints specify that the value of each attribute A must be an atomic value from the domain
dom(A). The data types associated with domains typically include standard numeric data types for
integers (such as short-integer, integer, long-integer) and real numbers (float and double-precision float).
Characters, fixed-length strings, and variable-length strings are also available, as are date, time,
timestamp, and money data types. Other possible domains may be described by a sub range of values
from a data type or as an enumerated data type where all possible values are explicitly listed. Rather
than describe these in detail.
Key constraints

7
A relation is defined as a set of tuples. By definition, all elements of a set are distinct; hence, all tuples
in a relation must also be distinct. This means that no two tuples can have the same combination of
values for all their attributes. Usually, there are other subsets of attributes of a relation schema R with
the property that no two tuples in any relation state r of R should have the same combination of values
for these attributes. Suppose that we denote one such subset of attributes by PK (SK).
Entity Integrity, Referential Integrity, and Foreign Keys Constraints
The entity integrity constraint states that no primary key value can be null. This is because the primary
key value is used to identify individual tuples in a relation; having null values for the primary key
implies that we cannot identify some tuples. For example, if two or more tuples had null for their
primary keys, we might not be able to distinguish them.
The referential integrity constraint is specified between two relations and is used to maintain the
consistency among tuples of the two relations. Informally, the referential integrity constraint states that a
tuple in one relation that refers to another relation must refer to an existing tuple in that relation. For
example, the attribute DNO of EMPLOYEE gives the department number for which each employee
works; hence, its value in every EMPLOYEE tuple must match the DNUMBER value of some tuple in
the DEPARTMENT relation.
To define referential integrity more formally, we first define the concept of a foreign key. The conditions
for a foreign key, given below, specify a referential integrity constraint between the two relation
schemas R1 and R2. A set of attributes FK in relation schema R1 is a foreign key of R1 that references
relation R2 if it satisfies the following two rules:
1. The attributes in FK have the same domain(s) as the primary key attributes PK of R2; the attributes
FK are said to reference or refer to the relation R2.
2. A value of FK in a tuple t1 of the current state r1(R1) either occurs as a value of PK for some tuple t2
in the current state r2(R2) or is null. In the former case, we have t1[FK] = t2[PK], and we say that the
tuple t1 references or refers to the tuple t2. R1 is called the referencing relation and R2 is the
referenced relation.

8
Example RIC

Dtudent (rollNo, name, degree, year, sex, deptNo, advisor)

Department (deptId, name, hod, phone)

Professor (empId, name, sex, startYear, deptNo, phone)

Course (courseId, cname, credits, deptNo)

Enrollment (rollNo, courseId, sem, year,

grade)

Teaching (empId, courseId, sem, year, classRoom)

PreRequisite (preReqCourse, courseID)