Databases Reference
In-Depth Information
┌─────┬─────┬─────┐
│ CNO │ TNO │ XNO │
├═════┼═════┼═════┤
│ C1 │ T1 │ X1 │
│ C1 │ T1 │ X2 │
│ C1 │ T2 │ X1 │
│ C1 │ T2 │ X2 │
└─────┴─────┴─────┘
This sample value is equal to the join of its projections on {CNO,TNO} and {CNO,XNO}, but it clearly fails to
satisfy the FD {CNO} → {TNO} (or the FD {CNO} → {XNO}, come to that).
Note:
I'll have more to say about
this particular example in Chapter 12.
5.5
See the body of the chapter.
5.6 Suppose we start with a relvar with attributes D, P, S, L, T, and C corresponding to parameters of the
predicate in the obvious way. Then the following nontrivial FDs hold in that relvar:
{ L }
→
{ D , P , C , T }
{ D , P , C }
→
{ L , T }
{ D , P , T }
→
{ L , C }
{ D , P , S }
→
{ L , C , T }
A possible set of BCNF relvars is:
5
SCHEDULE { L , D , P , C , T }
KEY { L }
KEY { D , P , C }
KEY { D , P , T }
STUDYING { S , L }
KEY { S , L }
Note that the FD {D,P,S} → {L,C,T} is “lost” in this decomposition (see Chapter 6).
5.7
The simplest design is:
EMP { ENO , ENAME , SALARY }
KEY { ENO }
PGMR { ENO , LANG }
KEY { ENO }
FOREIGN KEY { ENO } REFERENCES EMP
Every employee has a tuple in EMP (and EMP has no other tuples). Employees who happen to be programmers
additionally have a tuple in PGMR (and PGMR has no other tuples). Note that the join of EMP and PGMR gives
full information─employee number, name, salary, and language skill─for programmers (only).
5
Subsidiary exercise:
What are the predicates for these relvars?
