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.