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!
