Databases Reference
In-Depth Information
S SN ST SC
┌─────┐ ┌─────┬───────┐ ┌─────┬────────┐ ┌─────┬────────┐
│ SNO │ │ SNO │ SNAME │ │ SNO │ STATUS │ │ SNO │ CITY │
├═════┤ ├═════┼───────┤ ├═════┼────────┤ ├─────┼────────┤
│ S1 │ │ S1 │ Smith │ │ S1 │ 20 │ │ S1 │ London │
│ S2 │ │ S2 │ Jones │ │ S2 │ 30 │ │ S2 │ Paris │
│ S3 │ │ S3 │ Blake │ │ S3 │ 30 │ │ S3 │ Paris │
│ S4 │ │ S4 │ Clark │ │ S4 │ 20 │ │ S4 │ London │
│ S5 │ │ S5 │ Adams │ │ S5 │ 30 │ │ S5 │ Athens │
└─────┘ └─────┴───────┘ └─────┴────────┘ └─────┴────────┘
Fig. 15.4: A revised version of Fig. 15.3
Note carefully, however, that even in this extreme case, the design doesn't violate orthogonality. What's
more, I say again that this kind of design would have quite a lot to recommend it given a well architected DBMS. In
particular, the equality dependencies, and therefore the redundancies, would be “automatically” managed and
maintained in such a system (see the section “Managing Redundancy,” later).
EXAMPLE 7
Consider a company in which every employee is required to be in exactly one department and every department is
required to have at least one employee. Fig. 15.5 shows sample values (in outline) for an RM/T design for this
situation:
13
EMP DEPT EMPDEPT
┌─────┐ ┌─────┐ ┌─────┬─────┐
│ ENO │ │ DNO │ │ ENO │ DNO │
├═════┤ ├═════┤ ├═════┼─────┤
│ E1 │ │ D1 │ │ E1 │ D1 │
│ E2 │ │ D2 │ │ E2 │ D2 │
│ E3 │ │ D3 │ │ E3 │ D2 │
│ E4 │ └─────┘ │ E4 │ D3 │
│ E5 │ │ E5 │ D3 │
└─────┘ └─────┴─────┘
Fig. 15.5: Employees and departments─sample values
With reference to those sample values, however, we see there are exactly five employees and exactly three
departments. Since every employee must be in exactly one department and every department must have at least one
employee, why not define one department─D3, say─to be the “default” one, and adopt a rule that says any
employee mentioned in EMP and not EMPDEPT is in that default department? In terms of Fig. 15.4, this rule
would allow us to omit the tuples (E4,D3) and (E5,D3) from EMPDEPT. Note that if we don't adopt such a rule,
then the design clearly involves some redundancy once again─to be specific, it's subject to the following equality
dependencies:
13
Exercise:
Is EMPDEPT in that figure a P-relvar for employees, departments, or both? Justify your answer! To pursue the point a moment
longer: An RM/T design might not be the best option in this example, because there's necessarily a one to one correspondence between EMP and
EMPDEPT, and there seems little reason not to collapse those two relvars into one.
