Databases Reference

In-Depth Information

Incidentally, another argument in favor of including that master relvar P has to do with the shipments relvar

SP: Given that master relvar, we can retain the conventional foreign key constraint from SP to P; without it, life

becomes rather messier.

13.6 Every pair of attributes is a key. The specified JD doesn't hold, because the following is certainly a

legitimate value for the relvar:

a1 b1 c2

b1 a1 c2

a1 b2 c1

b2 a1 c1

a2 b1 c1

b1 a2 c1

(
a1
≠
a2
,
b1
≠
b2
,
c1
≠
c2
); that is, the tuples (
a1
,
b1
,
c2
), (
a1
,
b2
,
c1
), and (
a2
,
b1
,
c1
) most certainly don't force the

tuple (
a1
,
b1
,
c1
) to appear (!). The relvar is in 6NF. Note, however, that it's subject to a certain
symmetry

constraint; to be specific, the tuple (
a
,
b
,
c
) appears if and only if the tuple (
b
,
a
,
c
) appears (see the sample value above

for an illustration of this point).
12
As a consequence, the relvar is also subject to certain insertion and deletion

anomalies, and it isn't in DK/NF.

13.7 Let relation
r
have heading
H
. Then
r
will certainly satisfy all possible FDs and JDs that can be defined with

respect to
H
if
r
has cardinality either one or zero. Thus, all possible sets of dependencies (FDs and JDs) are

consistent (though some such sets might have the implication that any relation that satisfies them can have

cardinality at most one).

13.8

The following is certainly a legitimate value for relvar SPJ′─

s1 p1 j1

s2 p1 j1

(
s1
≠
s2
)─so {PNO,JNO} isn't a key. Likewise, the following is also a legitimate value for SPJ′─

s1 p1 j1

s1 p2 j1

(
p1
≠
p2
)─so {JNO,SNO} isn't a key either.

13.9

The thing to do here is to separate matches that have already been played from those that haven't:

PAST_MATCHES { DATE , OPPONENT , GOALS_FOR , GOALS_AGAINST , ... }

KEY { DATE }

FUTURE_MATCHES { DATE , OPPONENT , ... }

KEY { DATE }

These relvars are both in 5NF. PAST_MATCHES in particular probably shouldn't be replaced by 6NF projections.

13.10 Yes, we can define it as a view:

12
So do you think this relvar is subject to redundancy? Justify your answer!