Transcript No Slide Title

Chapter 3
The Relational Model
History of the Relational Model
The objectives of the relational model :

allow a high degree of data independence, application
programs should not be affected by modifications to
the internal data representation, file organisation
record orderings or access paths

deal with data semantics, consistency and redundancy
problems, the database files should be based on
normalised relations

enable the expansion of set-oriented data manipulation
languages, using a many-records-at-a-time logic.
Terminology
Relation
 A relation is a table with rows and columns
 Mathematical definition
Attribute
 An attribute is a named column of a relation
Domain
 A domain is the set of allowable values for one ore more
attributes
Tuple
 A tuple is a row of a relation
The DOMAIN concept
The domain concept is used to describe a set of possible
values for an attribute. This offers more flexibility.
Domain description
Domain part-number
Domain part-name
Domain color
Domain weight
Domain location
Relation PART
(P#
pname
color
weight
city
character 6
character 20
character 6
numeric 4
character 15
: Domain part-number
: Domain part-name
: Domain color
: Domain weight
: Domain location
Sample Database
S
S#
S1
S2
S3
S4
S5
SNAME
Smith
Jones
Blake
Clark
Adams
STATUS
20
10
30
20
30
CITY
London
Paris
Paris
London
Athens
SP S#
S1
S1
S1
S1
S1
S2
S2
P P# PNAME COLOR WEIGHT CITY
S3
P1 Nut
Red
12
London
S4
P2 Bolt
Green
17
Paris
S4
P3 Screw
Blue
17
Rome
S4
P4 Screw
Red
14
London
P5 Cam
Blue
19
London
P6 Cog
Red
19
London
P#
P1
P2
P3
P5
P6
P1
P2
P2
P2
P4
P5
QTY
300
200
400
200
100
300
400
200
200
300
400
Terminology 2
Degree
 The degree of a relation is the number of attributes it contains
Cardinality
 The cardinality of a relation is the number of tuples it contains
Relational database
 A collection of normalised relations
Relation Schema
 A relation name followed by a set of attribute and domain name
pairs
Alternative Terminology
Formal
Logical term
Classical
Relation
Table
File
Tuple
Row
Record
Attribute
Column
Field
Properties of a Relation

A relation in a database has a unique name.

Each cell of the relation contains exactly one atomic
value.

Each attribute has a distinct name within a relation.

The values of an attribute are all from the same
domain.
column homogenious

The order of the attributes has no significance.

Each tuple is distinct; there are no duplicate tuples.

The order of tuples has no significance, theoretically.
Mathematical Relations
Mathematical definition of relation
– Consider two sets, D1 and D2, where D1 = {2, 4} and D2 =
{1, 3, 5}.
– Cartesian product is D1  D2, the set of all ordered pairs,
first element is member of D1 and second element is
member of D2.
– Alternative way is to find all combinations of elements
with first from D1 and second from D2.
D1 D2 = {(2, 1), (2, 3), (2, 5), (4, 1), (4, 3), (4, 5)}
Mathematical Relation

Any subset of Cartesian product is a relation.
For example
R = {(2, 1), (4, 1)}

May specify which pairs are in relation using
some condition for selection. For example,
second element is 1
R = {(x, y) | x D1, y D2, and y = 1}
Relational Keys

Superkey

An attribute or a set of attributes that uniquely
identifies a tuple within a relation.
Relational Keys

Candidate Key

A superkey (K) such that no proper subset is a
superkey within the relation.

In each tuple of R, the values of K uniquely identify
that tuple (uniqueness).

No proper subset of K has the uniqueness property
(irreducibility).
Relational Keys

Primary Key


Candidate key selected to identify tuples uniquely
within relation.
Alternate Keys

Candidate keys that are not selected to be the
primary key.
Relational Keys

Foreign Key

An attribute or set of attributes within one relation
that matches candidate key of some (possibly same)
relation.
Relational Integrity

Null (value)
Represents a value for an attribute that is currently
unknown or is not applicable for this tuple.
 Deals with incomplete or exceptional data.
 Null represents the absence of a value and is not the
same as zero or spaces, which are values.

Relational Integrity

Entity Integrity


In a base relation, no attribute of a primary key can
be null.
Referential Integrity

If foreign key exists in a relation, either the foreign
key value must match a candidate key value of some
tuple in its home relation or foreign key value must
be wholly null.
Relational Integrity

Enterprise Constraints

Additional rules specified by users or database
administrators.
Views
Base relation
 a named relation, corresponding to an entity in the conceptual
schema, whose tuples are physically stored in the database.
View
 a view is the dynamic result of one or more relational
operations operating on the base relations to produce another
relation. A view is a virtual relation that does not actually exist
in the database but is produced upon request by a particular
user, at the time of request.
Views

A view is a virtual relation that does not actually exist
in the database but is produced upon request, at time
of request.

Contents of a view are defined as a query on one or
more base relations.

Views are dynamic, meaning that changes made to
base relations that affect view attributes are
immediately reflected in the view.
Purpose of Views

Provides a powerful and flexible security
mechanism by hiding parts of database from
certain users.

Permits users to access data in a customized
way, so that same data can be seen by different
users in different ways, at same time.

It can simplify complex operations on base
relations.
Updating Views

All updates to a base relation should be
immediately reflected in all views that
reference that base relation.

If view is updated, underlying base relation
should reflect change.
Updating Views

However, there are restrictions on types of
modifications that can be made through views:
Updates are allowed if query involves a single base
relation and contains a candidate key of base
relation.
 Updates are not allowed involving multiple base
relations.
 Updates are not allowed involving aggregation or
grouping operations.

Updating Views

Classes of views are defined as theoretically
not updateable, theoretically updateable and
partially updateable.
Relational Database Architecture
Data Language
application
External level
view 1
view 2
Conceptual level
Base table
B1
Base table
B2
Base table
B3
Base table
B4
Stored file
S1
Stored file
S2
Stored file
S3
Stored file
S4
Internal level