CSCI 5333 Data Base Management
System
Spring 2002 (1/14-5/6)
Chapter 7: Relational
Data Model & the Relational Algebra
7.1. The Relational Data
Model
- The relational model represents the database as a collection
of relations.
- Question: Is a relation
the same as a "flat" file of records? Why or why not?
The answer
.
- relation name, tuples, attributes, values (See Fig.
7.1, p.198)
|
ER model
|
Relational Model
|
|
entity types
or many-to-many relationships
|
relations
(or tables, informally)
|
|
entities (or entity instances)
|
tuples
|
|
attribute
|
column heading
|
|
|
cell
|
|
1-to-many relationships
|
?
|
|
1-to-1 relationships
|
?
|
- A domain D is a set of atomic values. A
domain is similar to a (user-defined) data type.
- Compare: Logical definition of a domain
versus its data type (or format)
- 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: STUDENT(Name, SSN, HomePhone, Address,
OfficePhone, Age, GPA)
- A relation (or relation state) r of the
relation schema R(A1, A2, . . ., An), also denoted by r(R), is a set
of n-tuples r = {t1, t2, . . ., tm}. Each n-tuple t is an ordered
list of n values t = <v1, v2, . . ., vn>, where each value vi, i >=
1 and i <= n, is an element of dom(Ai) or is a special null value.
- The ith value in tuple t, which corresponds to the attribute
Ai, is referred to as t[Ai].
- Relation intension = Relation schema
- Relation extension = Relation (state)
A relation r(R) is a mathematical relation of
degree n on the domains dom(A1), dom(A2), . . ., dom(An), which is a subset
of the Cartesian product of the domains that define R:
- If we denote the number of values or cardinality
of a domain D by |D|, and assume that all domains are finite,
the total number of tuples in the Cartesian product is:
| dom(A1) | * | dom(A2) | * . . . * | dom(An) |
A relation is different from a file or a record:
- A relation is defined as a set of tuples. Mathematically,
elements of a set have no order among them; hence tuples in a relation
do not have any particular order. However, in a file, records are physically
stored on disk so there always is an order among the records.
- According to the preceding definition of a relation,
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 definition—is important.
However, at a logical level, the order of attributes and
their values are not really important as long as the correspondence between
attributes and values is maintained.
- An alternative definition of relation:
A relation schema R = {A1, A2, . . ., An}
is a set of attributes, and a relation r(R) is a finite set of mappings
r = {t1, t2, . . ., tm}, where each tuple ti is a mapping from R to
D, and D is the union of the attribute domains; that is, D = dom(A1) U dom(A2)
U . . . U dom(An).
- According to this definition, a tuple can be considered
as a set of (<attribute>, <value>) pairs, where each pair
gives the value of the mapping from an attribute Ai to a value vi from dom(Ai).
The ordering of attributes is not important, because
the attribute name appears with its value. (See Fig. 7.3,
p.200)
- Each value in a tuple is an atomic value;
that is, it is not divisible into components within the framework of the
basic relational model. Hence, composite and multivalued attributes (see
Chapter 3) are not allowed. (The first normal form
assumption)
- Different interpretations of the
null value: "value unknown," "value exists but not available,"
or "attribute does not apply to this tuple."
Summary of Notations:
- A relation schema R of degree n is denoted
by R(A1, A2, . . ., An).
- An n-tuple t in a relation r(R) is denoted
by t = <v1, v2, . . ., vn>, where vi is the value corresponding to
attribute Ai. The following notation refers to component values of tuples:
- Both t[Ai] and t.Ai refer to the value vi in
t for attribute Ai.
- Both t[Au, Aw, . . ., Az] and t.(Au, Aw, .
. ., Az), where Au, Aw, . . ., Az is a list of attributes from R, refer
to the subtuple of values <vu, vw, . . ., vz> from t corresponding
to the attributes specified in the list.
- The letters Q, R, S denote relation names.
- The letters q, r, s denote relation states.
- The letters t, u, v denote tuples.
- In general, the name of a relation schema such
as STUDENT also indicates the current set of tuples in that relation—the
current relation state—whereas STUDENT(Name, SSN, . . .) refers only to the
relation schema.
- An attribute A can be qualified with the relation
name R to which it belongs by using the dot notation R.A—for example, STUDENT.Name
or STUDENT.Age. This is because the same name may be used for two attributes
in different relations. However, all attribute names in a particular relation
must be distinct.
Ø
Go to the
Index
7.2 Relational
Constraints
- Domain constraints
specify that the value of each attribute A must be an atomic value from
the domain dom(A).
A superkey SK specifies a uniqueness
constraint that no two distinct tuples in a state r of R can have the
same value for SK.
A key K of a relation schema R is
a superkey of R with the additional property that removing any attribute A
from K leaves a set of attributes K’ that is not a superkey of R. Hence, a
key is a minimal superkey.
In general, a relation schema may have
more than one key. In this case, each of the keys is called a candidate
key.
The primary key of a relation
is the candidate key whose values are used to identify tuples in the relation.
NOT NULL constraint
- A relational database schema
S is a set of relation schemas S = {R1, R2, . . ., Rm} and a set of
integrity constraints IC. (Example: See Fig. 7.5, p.204)
- A relational database state
DB of S is a set of relation states DB = {r1, r2, . . ., rm} such that
each ri is a state of Ri and such that the ri relation states satisfy the
integrity constraints specified in IC. (Example: See
Fig. 7.6, p.205)
- Integrity constraints are
specified on a database schema and are expected to hold on every database
state of that schema.
- Entity integrity constraint
states that no primary
key value can be null.
- Referential integrity constraint
states that a tuple in
one relation that refers to another relation must refer to an existing tuple
in that relation. NOTE: between two relations.
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:
- 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.
- 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.
- Other constraints: functional
dependencies, multivalued dependencies (see Ch. 14, 15)
Ø
Go to the
Index
7.3. Violation of Integrity Constraints
(See examples on pp. 209-211)
Ø
Go to the
Index
|
Main
page