Slides01-5 - 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

Info iconThis preview shows page 1. Sign up to view the full content.

View Full Document Right Arrow Icon
This is the end of the preview. Sign up to access the rest of the document.

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 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 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 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 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 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 given dependency. Build a weak instance of the universal relation by taking the instances of the real relations and padding them with unique-value 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 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 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.

Ask a homework question - tutors are online