3.

RELATIONAL DATABASE MODELS

The relational model is today the primary data model for commercial data processing
applications. It attained its primary position because of its simplicity, which eases the job of the
programmer, compared to earlier data models such as the network model or the hierarchical
model.
The relational model uses a collection of tables to represent both data and the relationships
among those data. Tables are logical structures maintained by the database manager. The
relational model is a combination of three components;
 Structural part – this defines the database as a collection of relations.
 Integrity part – Integrity in a database is maintained in a relational model using primary
and foreign keys.
 Manipulative part – these are tools that are used to manipulate data in a database, usually
relational calculus and algebra.
Basics of a relational model
 Relation
The relational model gives us a single way to represent data, that is, as a two-dimensional table is
called a relation. A relational database consists of a collection of tables, each of which is
assigned a unique name. For instance, consider the table below named Instructor. The table has
four column headers: ID, name, dept_name, and salary. Each row of this table records
information about the instructor, consisting of their id, name, department, and salary.

1
In general, a row in a table represents a relationship among a set of values. Since a table is a
collection of such relationships, there is a close correspondence between the concept of a table
and the mathematical concept of relation, from which a relational data model derives its name.
In mathematical terminology, a tuple is simply a sequence (or a list) of values. A relationship
between n values is represented mathematically by an n-tuple, that is, a tuple of n values, which
corresponds to a row in a table.
NB: for a table to be a relation, the following rules must hold;
 The intersection of row with column should contain single (atomic) value.
 All entries in a column are of the same type.
 Each column has a unique name and column order not significant
 No two rows are identical and row order not significant.
 Attributes
The columns of a relation are named by attributes. Attributes appear at the tops of the columns.
Usually, an attribute describes the meaning of entries in below each column. For instance, the
column with attribute ID in the relation Instructor holds the Identity number of each instructor.
 Tuples
The rows of a relation, other than the header row containing the attribute names, are called
tuples. A tuple has one component for each attribute of the relation. For example, the first tuple
in the Instructor relation above has the components 10101, Srivansan, Comp. Sci. and 65000 for
attributes ID, name, dept_name and salary, respectively. When we wish to write a tuple in
isolation, that is, not as part of the relation, we normally use commas to separate components,
and parentheses to surround the tuple. For example,
(10101, Srivansan, Comp. Sci., 65000),
is the first isolated tuple of the instructor relation above. NB: when a tuple appears in isolation,
the attributes do not appear and therefore, some indication of the relation to which the tuple
belongs must be given. Usually, the order in which the attributes were listed in the relation
schema is used.
 Domains
For each attribute of a relation, there is a set of permitted values, called the domain of that
attribute, that is, a particular elementary type. The components of any tuple of the relation must
have, in each component, a value that belongs to the domain of the corresponding column. For

2
example, the tuple of the instructor relation above must have a first component that is integer,
second and third components that are strings, and the fourth component whose value is integer.
It is possible to include the domain, or data type, for each attribute in a relation schema. This is
done by appending a colon and a type after the attributes. For example, instructor schema could
be represented as Instructor (ID: Integer, name: String, dept_name: String, salary: Integer).
 Atomicity
The relational model requires that each component of each tuple be atomic, that is, it must be of
some elementary type such as integer or string. It is not permitted for a value to be a record
structure, set, list, array, or any other type that reasonably can have its values broken into smaller
components.
A domain is therefore, atomic if elements of the domain are considered to be indivisible units.
For instance, suppose a relation instructor had an attribute phone_number which can store a set
of phone numbers corresponding to the instructor. Then the domain of the phone_numnber
would not be atomic since element of the domain is a set of phone numbers, and it has subparts
namely individual phone number and country code.
The important issue is not what the domain itself is, but rather how we use domain elements in
databases. Suppose now the phone_number attribute stores a single phone number. Even then, if
we split the value from the phone number attribute into a country code and local number, we
would be treating it as a non-atomic value. However, if we treat each phone number as a single
indivisible unit, then, the attribute phone_number would have an atomic domain.
 Database Schema
In a database, one must differentiate between the database schema, which is the logical design
of the database, and the database instance, which is a snapshot of the data in a database at any
given period in time. A database is made up of several related relations. Similarly, we have a
relation schema which consists of a list of attributes and their corresponding domains, and
relation instance, which contains contents of attributes within a specific period in time. NB:
contents of a relation instance may change with time as the relation gets updated. In contrast, the
schema of a relation does not generally change.
For instance, consider the Instructor relation above, the schema for that relation is
Instructor (ID, name, dept_name, salary).

3
  Keys
Concept of key
In a database, the database designer must have a way to specify how tuples within a given
relation are distinguished. This is expressed in terms of their attributes, that is, the values of the
attributes of a tuple must be such that they can uniquely identify the tuple. In simpler words, no
two tuples in a relation are allowed to have exactly the same value for all attributes. This is
enforced using keys.
A key is an attribute or group of attributes, which is used to identify a tuple in a relation. Keys
can be broadly classified into:
 Superkey
 Candidate key
 Primary key
 Superkey – a superkey is a subset of attributes of an entity-set that uniquely identifies the
entities. They represent a constraint that prevents two entities from ever having the same
value for those attributes. For example, the ID attribute of the relation Instructor above is
sufficient to distinguish one instructor tuple from another. Thus ID is a superkey. The
name attribute of Instructor, on the other hand, is not a superkey because several
instructors might have the same name.
 Candidate key – this is a minimal superkey. For a relation schema, it is a minimal set of
attributes whose values uniquely identify tuples in the corresponding relation. For
example, the combination of ID and name is a superkey for the relation instructor. Such
minimal superkeys are called candidate keys.
 Primary key – this is a designated candidate key, that is, a candidate key that is chosen
by database designer as the principal means of identifying tuples within a relation. It is to
be noted that the primary key should not be null and must be chosen with care such that,
its attribute values are never or very rarely changed.
Foreign key – a relation, say R1, may include among its attributes the primary key of another
relation, say R2. This attribute is called foreign key from R1, referencing R2. It can therefore, be
defined as a set of fields or attributes in one relation that is used to “refer” to a tuple in another
relation.

4
Relational Integrity
Data integrity constraints refer to the accuracy and correctness of data in the database. Data
integrity provides a mechanism to maintain data consistency for operations like INSERT,
UPDATE, and DELETE. The different types of data integrity are Entity, NULL, Domain, and
Referential Integrity.
 Entity Integrity – this implies that a primary key cannot accept null value. It means that
in order to represent an entity in a database, it is necessary to have a complete
identification of the entity’s key attributes.
 Null Integrity – Null implies that data value is not known temporarily. Null integrity
ensures that a specific field is not null. For example, consider a relation PERSON with
attributes name, age and salary. The age of the person cannot be NULL.
 Domain Integrity – domains in relational model define characteristics of the attributes of
a relation. The domain specifies its own name, data type and logical size. Domain
integrity is used to specify the valid values that a defined attribute can take, that is, it
specifies that each attribute must have values derived from a valid range. For example,
the age of a person cannot have any letter from the alphabet but should have numerical
values only.
 Referential Integrity – in a relational data model, associations between tables are
defined through the use of foreign keys. The referential integrity states that a database
must not contain any unmatched foreign key values. Therefore, referential integrity is a
rule that states that either each foreign key value must match a primary key value in
another relation or the value must be null if relationship does not exist for a particular
instance. In simpler words, this constraint integrity requires that the values appearing in
specified attributes of any tuple in the referencing relation must also appear in specified
attributes of at least one tuple in the referenced relation.