This preview shows page 1. Sign up to view the full content.
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:
select EO.O
from EO, ED, DM
where EO.E = EM.E and DM.D = ED.D
and M = 'sally' Using the view:
select O
from EDOPM
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
necessary.
3 Sort of a multiway outerjoin.
3 But in queries, we never want to see the
nulls.
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 QueryAnswering 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
sacred node.
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
view.
O E D P M Problems With the Simple UR The simple UR queryanswering 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 cephone
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
by:
1. Reduce the hypergraph using X as sacred
nodes.
2. Take the natural join of the relations that
remain.
3. Project this join onto X . The WeakInstance 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
given dependency.
Build a weak instance of the universal
relation by taking the instances of the real
relations and padding them with uniquevalue
nulls.
3 We use ? for a null, where i is an integer
chosen di erent from that of any other
null.
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
tuples.
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 employeesdepartmentsmanagers 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
4 E
e1
e2
e3 ?4
?5 DM
d1 ?1
d1 ?2
d2 ?3
d1 m1
d3 m2 The only equalities one can deduce are using
D ! M , from which we can infer ?1=?2=
m1 .
The weak instance becomes:
EDM
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
This document was uploaded on 01/06/2012.
 Spring '09

Click to edit the document details