Unformatted text preview: Universal Relations
The idea keeps getting reinvented about 3
times a year somewhere in the world.
3 So let's see it once and for all, and then if
you ever need it you can just implement
it rather than reinventing it. Example of Problem that the UR Solves Suppose we have relations ED, EO, EP , and DM ,
connecting employees to departments, phones, and
o ces, respectively, and departments to managers.
To simplify SQL queries, we might de ne a view
create view EDOPM as
select ED.E, ED.D, EO.O, EP.P, DM.M
from ED, EO, EP, DM
where ED.E = EO.E and EO.E = EP.E
and DM.D = ED.D Database Schema as a Hypergraph
O E D P M Consider a query nd the o ces of employees
managed by Sally." Without the view:
from EO, ED, DM
where EO.E = EM.E and DM.D = ED.D
and M = 'sally' Using the view:
where M = 'sally' These two are not the same!
3 If, e.g., some employee reports to Sally
but has no listed phone, then the
query using the view fails to give that
employee's o ce.
1 Simple UR Model
The problem above can be xed if we take
advantage of the fact that the various relation
schemas use the same name for the same concept
and only for the same concept.
We can thus imagine a universal relation
whose attributes are all the attributes
appearing in any schema.
3 The relation instance for this imaginary
UR can be thought of as the natural join
of all the relations, padded with nulls if
3 Sort of a multiway outerjoin.
3 But in queries, we never want to see the
More precisely, to answer a query, we want
to join only those relations that somehow
connect the attributes of the query.
3 Thus, a dangling tuple in an irrelevant
relation will not a ect the answer. Example
Query connecting O and M only goes through EO,
ED, and DM , not EP .
O E D P M A UR Query-Answering Strategy
1. Construct the hypergraph for the schemas.
2. Mark the attributes nodes mentioned
explicitly by the query as sacred."
3. Do GYO reduction, but without deleting any
4. The remaining hyperedges need to be joined
and the query executed on that join.
2 Example For our running example, O and M are sacred.
Only ear EP can be removed.
The remaining three hyperedges must be
joined, so the query we answer is the same as
the SQL query that doesn't use the EDOPM
O E D P M Problems With the Simple UR The simple UR query-answering algorithm
is OK if the hypergraph is acyclic and all
apparent connections through the hypergraph are
meaningful. But at least two problems surface:
1. If the hypergraph has cycles, there can be
several paths connecting nodes, and the result
is that these paths are intersected" when we
take the join.
3 I.e., to have a connection between two
attributes, the data must support the
connection along all paths.
2. In an acyclic hypergraph, to say that the join
of hyperedges XY and Y Z makes sense, we
are asserting the multivalued dependency
Y ! X j Z holds.
3 If not, then we connect too many pairs of
X -values and Z -values. Example If employees can have several o ces and several
phones, joining EO and EP to get o ce-phone
connections is dubious.
More likely each phone is on one o ce, and
the hypergraph really should have a single
EOP hyperedge. Window Functions In general, a window function for a universal
3 relation maps the set X of attributes mentioned by
the query to a relation X over which the query is
answered. Example The simple" UR described above computes X
1. Reduce the hypergraph using X as sacred
2. Take the natural join of the relations that
3. Project this join onto X . The Weak-Instance Window Function A more conservative approach to establishing
links is to assume that nothing is related unless
it follows from a functional, multivalued, or other
Build a weak instance of the universal
relation by taking the instances of the real
relations and padding them with unique-value
3 We use ? for a null, where i is an integer
chosen di erent from that of any other
Then chase" the weak instance by applying
FD's to equate symbols favor a real symbol
over a null and MVD's to generate new
Finally, compute the total projection X by
projecting onto X only those tuples in the
resulting weak instance that have no nulls in
the columns for X .
i Example Assume relations ED and DM employeesdepartments-managers with functional
dependencies E ! D and D ! M .
Suppose also that the current instances are:
ED = fe1 ; d1; e2 ; d1; e3 ; d2g and DM =
fd1; m1 ; d3; m2 g.
Let the query be nd all the employeemanager pairs," i.e., X = fE; M g.
Then the weak instance starts out as
d3 m2 The only equalities one can deduce are using
D ! M , from which we can infer ?1=?2=
The weak instance becomes:
e1 d1 m1
e2 d1 m1
e3 d2 ?3
?4 d1 m1
?5 d3 m2
The total projection onto EM is thus
fe1 ; m1; e2 ; m1g. 5 ...
View Full Document
- Spring '09
- Relational model, Hypergraph, weak instance