Le, Van Bao

Но, Thuan

Repository of the Academy's Library

Közlemények 30/1984 7-36TRANSLATIONS OF RELATIONAL SCHEMASHO THUAN and LE VAN BAOComputer and Automation Institute,Hungarian Academy of SciencesInstitute of Computer Sciences and Cybernetics Hanoi, VietnamINTRODUCTIONIn this paper we shall be concerned with a class of trans­lations of relational schemas.Starting from a given relational schema, translations make it possible to obtain simpler schemas, i.e. those with a less number of attributes and with shorter functional dependencies so that the key-finding problem becomes less cumbersome, etc.On the other hand, from the set of keys of the run re­lational schema obtained in this way the corresponding keys of the original schema can be found by a single "translation".In §1 we introduce the notion of ^-translation of a re­lational schema, give a classification of the relational schemas and inverstigate the characteristic properties of some classes of z-transformations.In §2 we study some properties of the so called nontrats-i latable relational schemas.The notation used here is the same as in C13;• C  meansstrict inclusion.8Definition 1. 1. Let S = <*Q ,F> be a relational schema, where fi, = I ,•••,An I is the set of attributes,F — | L^ ' ■ > j i=1 , 2 , . . . , к ; L^ , Ri ^ Iis the set of functional dependencies, and 2 Ç  fi , be an arbitrary subset of fi . We define a new relational schema ^fi.,F.j^ by:n 1 = n Л  zF, = 5 L ,\ z —*R Л  Z (L . —*R , ) 6F , i=1,...,k 1 L i l 1 l lThen fi^,F^ is said to be obtained from ( fi., F >  by a Z- -translation, and the notation< fi 1 ,F1 ) =( fi,F) - Zis used.Remarks1 ) Depending on the characteristic properties of the classchosen, the corresponding class of translations has its own characteristic features.2) With the Z-translation just defined above, a functional dei^endency of type 0~>Y may occur i n ^ f i ^ F ^  that has no ordinary semantic but carries information from the old relational schema to the new one.In particular, the possibility that 0 turns out to be a key of ^ fi  ̂,F 'y is not excluded.The next lemma is fundamental for the paper.Lemma 1.1. Let < fi ,F> be a relational schema and <( fi 1, F y = C fi , F) - Z , Z S  fit§ 1 .J_ T------9a) X T  Y implies X\Z T *  Y4ZF 1b) X implies YUZwhere X-p*Y means (X — >Y)€F+ and similarly,(X— * Y)€f |.Proof.For the part a) of the lemma, we shall prove thatX S Z £ ( X \ Z ) + (1)F 1By the algorithm for finding the closure X4 of X in L2J with xi°}=X, (X\Z)j,o) = X \ Z  we haveГ ГÇ  (X \ z ) í o)Supposing that (1) holds for i, that isxi,l}\z C  (X \ z)lx) F F ̂ (2)we prove that (1) holds for (i+1) as well. Indeed we havex^l+1Ч Z (X^l)U( U RT))\ZLJeXF(Lj-R^eF(х^1}\г)и( U R \z)Г / . \ Uljs x f(Lr Rj)eF10(i) и(X \ z )  U ( U  ( R T \  Z ) )Fi (i)(by virtue of the inductive assumption (2)).On the other hand, from L TÇ X * ^  and the inductive assump-J Гtion (2), we have:L T \ Z  \  Z 5 ( X \ Z ) (l)J F FConsequently :X (i+1) (i) (i+1)„  \  Z C ( X \ Z )  U( U (RT\ Z ) ) ç ( X \ F )1 Lj S 4 Í }  F lThus (1) has been proved. Now, it is well known thatX-^*Y 4=> Y Ç X pHence, from X — , we have:Y  \  Z c  x j  \ Z  s ( X \ Z ) J1That is,X  \  Z ~r=r* Y  \  Z F 1Similarly, for the part b) of the lemma, we shall prove by induction thatxt U Z C ( X U Z ) + . F 1 F(3)- 11 -By the algorithm for finding the closure X+ of Xx l o ) U Z s  (X и z)lo).F 1 FSupposing that (3) holds with (i), that isX*1 * U Z £  ( X U Z ) (i)F1 Fwe shall prove that (3) also holds for (i+1).(i+1 ) _ v (i)Indeed we have : Xv uz = x;x 'u( U (r t \ z ) ) 1 1 lj4Zç^ }(X*l} UZ)U( U (R \ Z) ) £  (X и Z)ll} a ( Uг - ... и г / . чL -Д Zç Xj,1 L-SZexA1 '-Ь -j J г +(by the inductive assumption (4))On the other hand, from L^XZ S  Xp (i) and (4)(i) (i)LjGXj, U Z C ( X U Z ) pConsequently:X (l+1 1 U Z S ( X U Z ) (l)(J ( U F 1 F Lj NZSX^,“R j ) C  (XU Z)(i+1)FThus (3) has been proved.From X—*Y we have У £ Х Л hence F 1 F -1Y U Z C X ^ U  Z £  ( X U Z ) * F2we have(4)Z =Vwe have12showing that : X U Z — Y U ZThe proof is complete.Definition 1.2.Let S = ( ß , F ^  be a relational schema. Let X  (ß, F) be the set of all keys of S andH - U X±/ G =  ГЛX±€K(ß,F) X^frdvF)Now, we give a classification of the relational schemas as follows:2 0 = j^.ß 'F > I < ß , F > is^ 1 = \<.n.'F > ft .*< II LUR I= {<ß 'F> e 5CJL £ R - ß J% = {^ß 'F> e ^ | R  c L = ß I- { <ß 'F > e S blL = R = ß 1From the aboyé: classiflication,a relational schema ÍCÁ ) X  4(3 ) X  f X2- XoFigure 1 shows the hierarchy of classes 0 1 2  1 4 ve fication, it is easily seen that:Fig. 1.We are now in a position to prove the following theorems.Theorem 1.1. Let Q , F >  be a relational schema, Z S G< 0 1 Then X is a key iffx n z = 0  and XUZ is a key of < Q ,f )Proof.We first prove the necessity. Suppose that X is a key of Obviously XS a, , therefore X Л  Z=0. Since X as a key o f <  Q., / F ^ ,  -Q ̂ . Taking lemma 1.1. into account weget 1X U 7,  ̂ij Z = o? ,showing that X и z is a superkey of < ß,F>. Were X U Z notк -y of (.ft , F ) then there would exist a key X o f < i , F >  s-..hatConsegu' : П fc 1 y , there7 VLi X \ -*• »would exist anC* «X,SCX such that- X.. U Z , X . n z  - (5X14Since X is supposed to be a key of ^ fí ,F^, U Z ß .Applying lemma 1.1, clearly(X. U Z)\Z — ß \ z ,1 F -|that is X^-^  ß^.This contradicts the hypothesis that X is a key o f C ^ r F l >  . Thus X U Z  is a key of ̂  ß ., F^.We now turn to the proof of sufficiency. Suppose that X 0 Z = 0 and X U Z  is a key of^ß,F^. We have to show that X is a key of < n r F i>.Since X U Z  is a key of < П ,F> we haveX U Z  ß .By virtue of lemma 1.1, we get(X u z)\z -tr*ß 'Z .F 1Consequently (from X D Z  = 0) :X u 1 'showing that X is a superkey of ^  ^ 1'F 1^ • Assume that X is not a key of  ̂, F .J ̂  . Then, there would exist a key X of ß 1 / F 1 ̂  such thatX C  X and X -r* ß F 1Applying lemma 1.1, it follows:X U Z  fl-, u z = ß ,15where X U Z G  X U Z .This contradicts the fact that X U Z is a key of < fi,F> Hence X is a key of < Q 1,F1>.The proof is complete.Theorem 1.2.Let <.Q,F> is a relational schema, Z fift, Z H H  = 0 and <  fl1 ,F^y =<• fl,F>-Z.Then X is a key of ^ iff X is a key ofProof.(i) (The necessity)Suppose that X is a key of( fi1,F1̂ . Obviously X ~~ТГ* Í!.. By virtue of lemma 1.1, we haveX u z — ß,uz = ß ,г Ishowing that X U Z  is a superkey of К fí,F\ Hence, there exists a key X of ^íí,F^ such that X £ X  U Z.Since Z П  H = 0 then xf)Z = 0. From this, it is easy to see that X G X .  There are two possible cases:a) X = X Then obviously X is a key of ß,F>.b) X G X  Since X is a key of <• ß,F>, X - ^ ' ß .  .Applying lemma 1 . 1 . , we haveX \ Z  ß.\Z#F 1that is XThis contradicts the fact that X is a a key of \ Œ  ̂, F ^  (ii) (The sufficiency)Suppose that X is a key of < Q,F>. We have to prove that X is also a key of < V F ) ) ’ We have, by the definition of keys- 16 -Applying lemma 1.1:X \ Z , Й ,\ Z = ß .F 1 1Since Zf)H = 0, it follows Xf\Z = 0. Consequently,showing that XX '— * ß .F 1 1is a superkey of ( S2^,F^.Now, assume the contrary that X is not a key of C  ̂  1,Fl)* Then there would exist a key X of K ü -J / F .| ̂  such that X C  X. ObviouslyX — ► ß . .F . 1We invoke lemma 1.1. to deduceX U Z -j* ß ̂  Z = ß ,showing that X U Z is a superkey of < ß  / F>. Consequently, there exists a key X of <-H-,F> such thatX Ç X U Z ,  X A Z  = 0.From this X c  X C X .This contradicts the hypothesis that X is a key of К ß.,f X17The proof is complete.Based on theorems 1.1 and 1.2, in the following we invest igate only the class of Z - translations with Z^0,Z=Z1UZ2 , Z -| П Z 2 =0. Z.cG, Z2f)H=0.Bearing this in mind, if<ß1 ,F1> = < 0 , F > - Zthen applying tneorem 1.2 and 1.1 one after another to the Z2~translation and the Z^-translation, we have: X is a keyof <Q^,F^> if and only if X П Z=0 and X U  Z^ is a key of <Œ,F>. For the sake of convenience, we use in the sequel the notation<Œ,F> > <ß1 ,F1>p=(z1,z 1)where the meaning of p is obvious.To continue, let us recall a result in m i .  Let S=kfi,F> be a relationsl schema, whereЙ = {A^,...,A } - the set of attributes,F = {L^ R. | L ^ , k c  il, i=1,...,k} - the set offunctional dependencies.Let us denote к кL = U L. , R = (j R.i=1 i=1Then, the necessary condition for which X is a key of S is thatП NR E X c ( fl \R) U (L П R)For V £  Й we denote V= Я V. It is easily seen that.L U R  e f i \ R £ GL S R  ç ß \R c G18R \ L £  H, consequently (R\L) П H=0, and we have the follow­ing lemma:Lemma 1.2. Let s=<^,F> be a relational schema, Z £ G ,  where G is the intersection of all the keys of S.Then ( Z+ \ Z ) П H = 0,where H is t h e  union of all the keys of S.Proof. Assume the contrary that(Z+ \ Z ) n H Ï 0.Then, there would exist an attribute AGZ*1 , A€Z and A € H . Consequently, there exists a key X of S=<ß,F> such that A€X.Since A6Z+ and AGZ we infer that Z £ X \ A .Hence * * + *X \ A ->• Z -*■ Z -> Awith A€XThis contradicts to the fact that X is a key of S.«• The proof is complete.From the results mentioned just above the following theorems are obvious.Theorem 1.3. Let S=<ß,F> be a relational schema belongingto 2o' ------,F > = <Q,F> - L U R.Then<Í2,F> > <S1,F >p=(LUR,LUR< Í2 ̂ , F ̂ •with19Proof. As remarked above L U R c GApplying Theorem 1.1. to t he Z-tr ars lat ion with Z - T. \ R have: ■ <ß,F> - — —  < Й w  F >• ' p=(LUR,LUR) 'The theorem 1.3 is illustrated by Figure 2.Example 1. with We haveLet there be given £ = - .2,F> Œ = {a,b,c,d,e}, F = : c- t, g L U R = ab.ConsiderObviously— < í2, F> —  ab.= {c,d,e} , F ̂ = {c+d, d-*-e}.It is easily seen that c is the unique key of hence abc is the unique key of <£2,F .Theorem 1.4.Let < fi,F> be a relational schema of ,201 / F 1 > = <^,F> - (LUR U (L\R)).ThenwithProof.It is clear thatZ = LUR U (L\R) = i l \ R ç  GThe theorem 1.4 now follows from applying theorem 1.1 to the Z-translation.Theorem 1.4 is illustrated by figure 3.R< n , F > e S ; o ,F , >  €  S £ 2_f>=(l u r  u (l\r),1-uR uiL4R))< й, F>P =(LUR U (L\R) ,LUR U (L\R) )< , F ̂ > € o^2 •Fig. 3.21Theorem 1.5.Let S = <Œ,F> be a relational schema of X .<fl1,F1> = <n,F> - (LUR U (R\L)).Then< fif F) > < Œ -, / F - >p =(LURU(R\L) ,LUR)withProof. As remarked above, R \ L  H.Let Z = LUR U (R\L) = Z 1 U Z ?i.where Z 1 = LUR ç G, Z2 = R \ L, Z .П К = 0-The theorem 1.5 now follows from sequent.!. )plicati.or.~ theorems 1.2 and 1.1 one after another to the Z ̂ tion and the Z 1 - translation. Theorem 1.5 is illustr.. ° Fig. 4.Rp ä^LüRU(K4l)> LU&)<£l,F> e S f 0Fig. 4.22Theorem 1.6. Let S = <fi,F> be a relational schema of<fi1,F1> = <Í2,F> - (LUR U ( L \ R)  U (R \ L) ) .Then< ^,F> <Í21 ,F^>,:(LUR U (L R) U (R L) , LUR U (L R) )with< ^-|/F^> 6 oi  ̂.Proof. Let Z = LUR U ( L \ R ) U ( R \ L )  = U 7.2,where Z 1 = LUR U(L\R) = ft \  r  ̂  gZ2 = R 4 L ÇI H or equivalently Z2 П H = 0It is obvious that <ß^,F^> is obtained from <fi,F> by the Z - translation. The proof of theorem 1.6 is straight-forward. Theorem 1.6 is illustrated by Fig. 5.< n , F >  6 ^ o < n 1>F , > eFig. 5.23Similarly, we can prove the following theorems:Theorem 1.7.Let S = <H,F> be a relational schema of tJb, ,<«1 ,F 1 > = ,F> - (L \ R) .Then< ft,F ^  . *== (L R, L R)-> <fl1,Fl>,where < ilj , F.̂  >6 oi, •Theorem 1.7 is illustrated by Fig. 6.24Thenwhere< £2 / F>  ̂ <̂ £2.t/F.2>/P=(R4L, 0)<Í2 -J , F ̂ > e <^3*Theorem 1.8. is illustrated by Fig. 7.L R L<a, F  >  e  5Ç , <  - « . „F, >6  S£3Fig'. 7.Theorem 1.9. Let S ̂, F ̂ ><£2,F> be a relational schema of <iî,F>-((L\R) U (R \ L) ) ./Then< £2, F> -------- >P=((L\R)U(RVL) ,L\R)<£2 .J , F^ > € .where25Theorem 1.9 is illustrated by Fig. 8.<fi, f >  «  2 ^ < eFig. 8.Theorem 1.10. Let <r^,F> be a relational schema of rThenwhere<Í21,F1> = < fi,F> - (R\L). F ̂ P =(RVL,0) > ^  1 ,F1> 'Theorem 1.10 is illustrated by Fig. 9.»f R M  , 0 )L2  - U ,  > F, > ^Fig. 9.26L RFig. 10.Combining theorems 1 . 3 -  1.11 we have the diagram of trans­lations as illustrated on figure 11.Theorem 1.11. Let < Œ,F> be a relational schema of ̂' F 1 > = <^,F> - (L\R).Then<ft,F> ----- ----------> <ß 1 / F1 > ,= (L''R,L\R)where <arFl> et£vTheorem 1.11 is illustrated by Fig. 1027Fig. 11.Now, the following theorem follows from theorems 1. i, 1.2 and lemma 1.3.Theorem 1.12.Let <^,F> be a relational of %fjQ,<Ti 1 , F 1 > = <^,F> - {LÜR U ( L \ R ) + U ( R\L) } .28Then,F> ,+P =(LUR U (L \ R) U (R\L) , LUR U(L\R) )/ F 1 > ,where^  1, F 1 > e 2 4 .Proof.Put Z = L U R U (L\R) U C (L\R) \  (L\R) 3U(R\L) = Z ^ Z ^where Z  ̂ = LUR U (L\R) = fi\RcG,Z2 = C (L\R)+\(L\R) ]U( R\L) .Clearly Zf) H = 0.Applying theorem 1.2 to<ft ' , F ' > = < Q,F> - Z2 ,and then, theorem 1.1 to<Й -, / F 1 > - < П' , F ' > - Zythe proof of theorem 1.12 is easy.Theorem 1.12 is illustrated by Fig. 12.The "double hashing" part is (L\ R)Fig. 12.29From the just mentioned results, we have the following diagram of translations of relational schemas (Fig. 13).Fig. 13.Example 2. Letwe haveßFLa b h g q m n v w k l ,{a->b, b->h, g+q, kv-*w, w+vl}.abgkvw; R = bhqwvl; R4L = hql;30LVR = кдг1 ; ( LVR) +=kgabhq ; LUR = mn ;(RVL)U(LVR)+U(LUR) = mnkgabhql- mnkgabhql = <wv,{v-*w, w->-v}>.It is easily seen that v and w are keys of <^^,F^>. On the other hand(LUR)U(L\R) = m n к g aConsequently mnkgav and mnkgaw are keys of <^,F>.§2 .In this section we investigate some properties of the so-called nontranslatable relational schemas.Definition 2.1. Let S = <^,F> be a relational schema.S is called translatable if and only if there exist certainsets Z1 ,Z2 E  Œ  such that:(i) Z1 Ï 0( ü ) X is a key of iff XÍIZ2 = 0 and XÖZ2is a key of <^,F>, where <^,F.j> = A tO 4 V 1 NOtherwise S is called nontranslatable.Theorem 2.1. Let S =  <^,F> be a translatable relational schema with Z^,Z^ as defined above.Then H\G =  H14G1 ,where H and G (and similarly Ĥ  and Ĝ ) are defined in definition 1.2.31Proof.Let < ß1 ,F.j> = < £2,F> - VSince X is a key of <£2̂ ,f ^> iff XnZ2key of <^,F>, it follows:h = h 1u z 2 , z2nHi= 0 /G = UZ 2 / Z 2 П G 1 = 0,henceH4G = (H1UZ2 )N(G1UZ2 ) = ((h 1u z 2 )\z 2 )s g 1 H 1 S G 1(because Z2^H 1 =  0) -Combining theorems 1.1, 1.2 with theorem 2.1» the following theorem is obvious:Theorem 2.2. Let S = <£2,F> be a relational schema. <Œ,F> is non translatable iff H = £2 and G = 0.Theorem 2.3. Let S = <;i,F> be a relational schema,<£21 ,F 1 > = <£2,F> - (G\H)Then:-a) <£2,F>....  > <ß1,F1>.P =(GUH,G)b) <fi1 ,V is non translatable.c) <^1,VProof. Let z = GUH = Z 1UZ2 , . -where Z 1 = G g G ,  z2 = H (clearly Z2flH = 0) .Hence part a) of the theorem is obvious. To provehave only to show that32= 0 and H .J = .From a) it is clear that X is a key of <^^,F.> iffXHG = 0 and XUG is a key of A SO 4 VTherefore, G = GUG1, c n c 1 = 0H = GUH1, GilH2 = 0-HenceG 1 = G\G = 0  and H ̂ = H\G.On the otherhand we haveП1 = ft\(GUH) = (fi\H)\G = H\G = H ̂ .To prove c) we have to show thatL 1 = R 1 = fí1where L 1 and R 1 are the union of all the left sides and right sides of all functional dependencies of , respectiv­ely .It is known Cl] thatП 1 \ R 1 S  G 1 = 0 .On the other handR 1 £ •Hence R 1 = Í2 ̂ .There remained to prove L 1 =9...Were this false, there would exist an AGÍÍ^m A Since R 1 = Í2̂ , we haveA € R 1 and A € L 1.33From = H 1Since A ê L 1there exists a key XА б X amd X -»- й.it follows from C13 * лX \ A  0 1 SA.of <йthatF  ̂> such thatEvidentlyL * 1 £  ß-jNAand from this,X \ A * ß.j \ A * L ^ R 1 * A.This contradicts the fact that X is a key of <ß.,Fv: . п-л-и L 1 = Û 1.The proof is complete.From the proof of c) we conclude that all non translatai . relational schemeas are of type ci)̂  •Theorem 2.4. Let S - <ft,F> be a relational schema 1 cchsatisfying the following conditions:(i) Li n R ± = 0 Vi = 1 , 2 , . . . , к ,(ii) for each L. , i=1,...,k there exists a key X.. • suchthat L, £ X. .1 iThen < ß F> is a nontranslatable relational schema.Proof. We have to prove that H = Я and G = 0~In fact, from <й,F>6 we have L = R = .By virtue of the hypothesis of the theorem we have34к кß = L = U L. S  U X. ç H £ S ]  i=1 1 i=1 1Consequently, H = ß .To prove G = 0 we first show that if -*■ R^ and is akey such that L.ciX. then X.nR. = 0.Assume the contrary that X^OR ф 0.Then, there would exist an A 6 X.flR..' l lSince L -J П R^ = 0 clearly A € . Therefore L̂, с Х Л  A.On the other hand¥t ^X . \A -*■ L. -*• R. -*• A, l l lshowing that X is not a key of <ß,F>. We thus arrive at a contradiction. From X̂ fl R^ = 0/ it follows:X. £  ßvR. .l lThus к к кG £  П X. £ П (ß\R.) =ß ЧU R.. i=1 1 i=1 1 i=1 1Since R = ß clearlyG s  ß\ß = 0.showing that G = 0.The proof is complete.ACKNOWLEDGEMENTThe ‘authors would like to take this opportunity to express their sincere thanks to Prof. Dr. J. Demetrovics and Dr. A. Bêkêssy for their valuable comments and suggestions.REFERENCESHo Thuan and Le van Bao. Some results about keys of relational schemas (to appear)üllman, J. Principles of database systems,Prentice Hall, 1980.36ÖSSZEFOGLALÁSA RELÁCIÓS SÉMÁK ELTOLÁSAIНо Thuan és Le Van BaoA cikkben a szerzők bevezetik a relációs sémák eltolásainak fogalmát. Elindulva az adott sémából eltolással általában egyszerűbb sémák nyerhetők.A szerzők a következő kérdésekkel foglalkoznak:- a relációs sémák osztályozása az eltolhatóság szempont­jából ;- az eltolások bizonyos osztályainak tulajdonságai;- u.n. nem eltolható sémák tulajdonságai.ТРАНСЛЯЦИИ РЕЛЯЦИОННЫХ СХЕМВ статье вводится понятие трансляции реляционных схем и изу­чаются основные вопросы, такие как:- классификация схем с точки зрения их трансляций;- свойства некоторых классов трансляций;- свойства схем, которые не позволяют трансляций.

Translations of relational schemas

http://real.mtak.hu/177423/1/1_real_SZTAKIKozlemenyek_30.pdf

Translations of relational schemas

Abstract

Similar works

Full text

Available Versions

Repository of the Academy's Library