# RELATIONAL DATABASES

Click here for audio-text lecture and feed it to the speech agent

Click here for an audio lecture that can be played using RealPlayer
A relational database is a collection
of time-varying, normalized relations
of assorted degrees.

The following intiutitve correspondence
can be made:

1. A relation is a file

2. Each file contains only one record type

3. The records have no particular order

4. Every field is single-valued

5. The records have a unique identifying field
or composite field, called the primary key field.

A relational database consists of a collection
of tables.

All data values are atomic. No repeating groups
are allowed.

A relational database is a pointerless database,
User does not see or is made aware of pointers.

# Relational Database Concepts

Relation

Tuple

Attribute

Cardinality

Degree

Primary key

Domain

# Equivalent Database Concepts

Relation <-> Table

Tuple <-> Row or record

Attribute <-> Column or field

Cardinality <-> Number of rows

Degree <-> Number of columns

Primary key <-> Unique identifier

Domain <-> Pool of legal values

# DOMAINS

A domain is a named set of scalar values,
all of the same type.

Example 1: Domain of P# is the set of character
strings of length 6.

Example 2: Domain of WEIGHT is the set of
small integers less than 10,000.

Example 3: Domain of QTY is the set of
integers less than one billion.

Therefore, a "domain" is a "data type".

# DOMAINS

Domains (or data types) can be used to
impose semantic constraints.

Example 1:

SELECT P.*, SP.*

FROM P, SP

WHERE P.P# = SP.P# ;

The comparison in the conditional clause
is sensible.

Example 2:

SELECT P.*, SP.*

FROM P, SP

WHERE P.WEIGHT = SP.QTY ;

The comparision involves two attributes
of different types, and therefore should
not be allowed.

# RELATIONS

A relation R on a collection of domains
D1, D2, ..., Dn, consists of two parts:
a "heading" and a "body".

The heading consists of a fixed set of
attribute-domain pairs,

{ (A1:D1),(A2:D2), ... , (An,Dn) }

The heading is also called the schema.

The body consists of a time-varying
set of tuples, where each tuple
consists of a set of attribute-value pairs,

{ (A1:vi1), (A2:vi2),..., (An:vin) }

and i = 1, 2, ..., m.

The body is also called the instance.

# RELATIONS

In the above, n is the degree of the relation
and m is the cardinality of the relation.

A relation R can be considered as a variable.

The heading of a relation is the "type"
of the variable R.

The type of R is (D1,D2,...,Dn).

The body of a relation is the "value"
of the variable R.

The value of R is a subset of the Cartesian
Product of **D1, D2, ..., Dn**.

# AN EXAMPLE OF A RELATION

The heading of R is
(S#, SNAME, STATUS, CITY )

This is a shorthand notation for
{ (S#:S#), (SNAME:NAME),
(STATUS:STATUS), (CITY,CITY) }

The body of R is a subset of the
product of the underlying domains.

A typical tuple is an element of
this product of domains.

( S1, Smith, 20, London )

This is a shorthand notation for
{ (S#: S1), (SNAME: SMith),
(STATUS: 20), (CITY: London) }

# CARTESIAN PRODUCT OF DOMAINS

Suppose the domain of S# is {1,2,3}
and the domain of NAME is {Smith, Doe}

The Cartesian Product of the two
domains is denoted by

S# x NAME

The Cartesian Product is:

{(1,Smith),
(1, Doe),
(2,Smith),
(2,Doe),
(3,Smith),
(3, Doe) }

Later we will use the Cartesian
Product to explain "equijoin" and
"natural join".

# PROPERTIES OF RELATIONS

P1: There are no duplicate tuples.

P2: Tuples are unordered.

P3: Attributes are unordered.

P4: All attribute values are atomic.

*Notice the implementation of a relational
database system often deviates from the
above properties (which ones?).

# KINDS OF RELATIONS

Base relations: The real relations.
Called "base table" in SQL.

Views: The virtual relations. A view
is a named, derived relation.

Snapshots: A snapshot is a real, not
virtual, named derived relation.

Query results: The final output
relation from a specified query.
It may not be named and has no
permanent existence.

Temporary relations: A nonpermanent
named derived relation.