The transformation from n-ary relationships to a relational database
schema has never been really fully analyzed. This paper presents one of
the several ternary cases ignored by the ER-to-RM literature. The case
shows that the following common belief is wrong: Given a set of FDs over
a table resulting in a non-3NF situation, it is always possible to
obtain a fully equivalent set of 3NF tables, without adding other
restrictions than candidate keys and inclusion dependencies.Postprint (published version

Camps Pare, Rafael

English

UPCommons. Portal del coneixement obert de la UPC

From Ternary Relationship to Relational Tables:A Case Against Common BeliefsRafael Camps ParéUniversitat Politècnica de Catalunya (Barcelona) RCAMPS@LSI.UPC.ESABSTRACTThe transformation from n-ary relationships to arelational database schema has never been really fullyanalyzed. This paper presents one of the several ternarycases ignored by the ER-to-RM literature. The caseshows that the following common belief is wrong: Givena set of FDs over a table resulting in a non-3NFsituation, it is always possible to obtain a fullyequivalent set of 3NF tables, without adding otherrestrictions than candidate keys and inclusiondependencies.1. INTRODUCTIONThe transformation from an n-ary relationship of the ERmodel to a relational database schema is considered bythe literature as an elementary and well known problem.But according to my knowledge this subject isanywhere fully and systematically described.It exists two popular approaches for expressing thecardinalities of the n-ary relationships: Chen approach[4] and Merise approach [10]. Chen approach is used byTeorey [12], Ullman-Widom [13], UML [11], IDEF1X[2], etc. Merise approach [10] is used by Elmasri-Navathe [5], Batini-Ceri-Navathe [1], Yourdon-Method[14], etc.In the literature about database design, when thetranslation from ER to relational tables is described, isusually limited to a very general description, and onlyone of the two popular approaches is assumed for thecardinality. But none of the two approaches, nor the twotogether, allow us to express all the possiblecardinalities (see McAllister [9]). So, transformations torelational tables from some ternary patterns are ignoredin the literature. A detailed analysis of thetransformation of all the patterns can be found in [3].As an example we will present here a ternary case(section 2 and 3) with interesting characteristics (section4) that challenge some very common beliefs (section 5).2. AN ELEMENTARY EXAMPLEWe assume that if R is a relationship between theentities A B and C, then the symbol 1 in the leg of Ameans that each pair of instances, b from B and c fromC  cannot be associated to more than one instance afrom A. Remember: each one of the instances of a ternaryrelationship involves actually three entity instances.We will study the ternary relationship with 1:N:Ncardinalities in Figure 1. The entities are Product(identified by Pro-id) State (identified by Sta-id) andDealer (identified by Dea-id). The semantics of the case isas follows: a dealer company can distribute several of ourproducts and a product can be distributed by severaldealers, but in each state a product cannot be distributedby more than one dealer. Each concession of distributionto a dealer of a product into a state, each instance of theternary relationship, has a date of concession (Con-date).The meaning of the cardinalities is; each pair of one dealerand one state, can be associated to many products. Eachpair of one state and one product cannot be associated tomore than one dealer. Each pair product-dealer can beassociated to more than one state.In terms of functional dependencies, FDs, this semanticscan be expressed as follows (the obvious internaldependencies in each entity between their identificationand their attributes are not represented):       (Pro-id , Sta-id) ➔ Dea-id            because the 1  in the side of Dea-id       (Pro-id, Sta-id, Dea-id) ➔  Con-date            because Con-Date is an attribute of the relationshipThese two dependencies can be reduced to one:       (Pro-id , Sta-id)  ➔  (Dea-id, Con-date)A relational representation of this ternary relationshipcould consist on one table for each entity and one table forthe Concession relationship (keys are underlined) :       State  (Sta-id, Sta-capital)DealerProduct StateConcession  Con-dateN NSta-idSta-capitalPro-idPro-typeDea-idDea-adressFigure 1:  Example of  1:N:N  relationship1       Product (Pro-id, Pro-type)      Dealer (Dea-id, Dea-address)       Concession  ( Pro-id, Sta-id,   Dea-id, Con-date )Each one of the attributes Dea-id, Pro-id and Sta-id inthe table Concession should be declared as foreign keyand not null.The possible lack of a dealer for a given pair product-state, is expressed by the non-presence of a row in theConcession table for that pair of Pro-id Sta-id. Note thatit is not possible to reduce the number of tables withoutloosing their normalization (BCNF).3.  ADDING MORE FDs TO THEEXAMPLENow we will add to our elementary example two newfunctional dependencies:Pro-id  ➔ Dea-id    and          Sta-id ➔ Dea-idThe two imposed structural constraints are, according tothe terminology of Jones-Song [6], two ConstrainingBinary Relationships. The semantics of the new ternarypattern is now more restricted: a) each product isdistributed only by one dealer, and b) in each statewhere it exists concessions, only one dealer exists.                  Figure 2: Two imposed FDsNote that a product can still be distributed in severalstates and in a state is still possible to distribute severalproducts.In Figure 2 we try to show graphically the new ternary.The dotted lines and arrows try to express the twoimposed binary constraints as subsets of the ternary. Infact the subset term is not strictly correct because aconstraint is not a subset of a relationship. Moreover, ifwe see the constraints as they were a set of instances ofbinary relationships, then a set of 2-tuples can not be asubset of a set of 3-tuples. In UML it exist the {subset}stereotype to express that an association is a subset ofanother association. Perhaps this UML stereotype can beused with the same meaning than the arrows in Figure 2,but the lack of a rigorous definition of UML in theirreference manual [11] do not allow us to be sure.There is no way to represent the pattern in Figure 2 (andsome other patterns) using any of the most popular ERmodels, without adding text (or special symbols) toexpress that the binary restrictions are part of the ternaryrelationship.Abstracting our case to a relationship R between the threeentities A, B and C, the set of functional dependenciesbetween the keys is:(A-id,C-id) ➔ B-idA-id ➔ B-idC-id ➔ B-idSo the case is apparently simple and can be represented as:A,  C,    BThis FD ternary pattern is exactly the pattern number 4 inthe analysis of McAllister [9] or the pattern number 11 inthe analysis of Jones-Song [7].In our example, a valid set of instances for  the newrelationship Concession can be:  Pro-id                Sta-id       Dea-id            Con-dateCocacola Idaho Smith&Sons 1996Cocacola Texas Smith&Sons 1994Fanta Idaho Smith&Sons 1994Sprite Kansas FreeDrink 1998Now it should be impossible to additionally insert thefollowing instances, because the binary imposed FDs:Cocacola            Kansas     Smith&Sons        2000Sprite                 Idaho        Smith&Sons        2000As a consequence of the additional constraints, Cocacolacannot have a dealer in Kansas because Cocacola hasalready a dealer -Smith&Sons (in Idaho and Texas)- andKansas already has a different dealer, FreeDrink (forSprite). Similar considerations can be made for the pairSprite/Idaho.It could seem that with only two binary FDs we cancharacterize this case, because applying the augmentationrule (one of the Armstrong's rules [12]) that statesif  X ➔ Y  then  (X,Z) ➔ Ywe derive the FD  (A-id,C-id) ➔ B-id  from both of ourtwo binary FDs. But remember that the ternaryrelationship, R, has its own attributes, r, and therefore theyProductConcessionN N1N11N StateDealerare functionally dependent on the pair of keys of A andC. So, the full set of FDs is:   Key FDs: Non-key FDs:   (A-id,C-id) ➔ B-id (A-id,C-id) ➔ r   A-id ➔ B-id A-id ➔ a   C-id ➔ B-id C-id ➔ cB-id ➔ b4. FROM TERNARY TO RELATIONALIf we transform our ternary relationship to relations,directly representing the full set of FDs of the pattern,we obtain the following database schema:   A (A-id, B-id, a)  B (B-id, b)    C (C-id, B-id, c)    R (A-id, C-id,  B-id , r)We are assuming that participation of entities A, B andC in the relationship R, is not mandatory. The attributesB-id of tables A and C are to be declared as acceptingnulls. The attribute B-id of table R must be declared asnon-null. The three B-id attributes of tables A, C and R,are to be declared as foreign keys of table B.This schema has intertable redundancy (because B-id)and the table R is not in 3NF (nor in 2NF) because ofthe following two dependencies that have non-keydeterminants:A-id➔ B-id             C-id➔ B-id.Moreover, the above relational schema is not fullyrepresenting the semantics of our ternary relationship.The two binary dependencies   A-id➔ B-id  and C-id➔B-id in tables A and C, should be defined as part of theternary, and in our schema they are not (they areindependent of R). We could add the followinginclusion dependencies [5]:I) π A-id,B-id R  ⊆  π A-id,B-id AII) π A-id,B -id R  ⊇  π A-id,B-id AIII) π C-id,B-id R  ⊆  π C-id,B-id CIV) π C-id,B -id R  ⊇  π C-id,B-id CThe inclusion dependencies I and III can be easilyexpressed in SQL92, adding keys and foreign keys asfollows:   - Table A:   UNIQUE (A-id, B-id)   - Table C:   UNIQUE (C-id, B-id)   - Table R:         FOREIGN KEY (A-id, B-id)  REFERENCES A (A-id, B-id)    and     FOREIGN KEY (C-id, B-id)  REFERENCES C (C-id, B-id)Note that the UNIQUE declarations are partly redundantwith the primary keys, but they  make easy the control ofthe inclusion restrictions.The inclusion dependencies II and IV are a little morecomplex to express in SQL:      - In table A       CHECK  ( (A-id, B-id)    MATCH                           (SELECT A-id, B-id  FROM  R ))      - In table C       CHECK  ( (C-id, B-id)   MATCH                          (SELECT C-id, B-id  FROM  R ))With all these restrictions, the schema is now fullyexpressing the semantics of our case and avoids thepossible update problems owning to the redundancy of B-id and to the lack of normalization of  R.Let us now start again with the original schema andassume that we desire to normalize table R. In order tonormalize R we can take out its attribute B-id, because theadditional FDs represented in tables A and C, alreadyimply the dependency (A-id,C-id) ➔ B-id. So, the schemaof R can be reduced to:R (A-id, C-id, r)Now the data base schema formed by the four tables A, B,C and R, is normalized in 3NF (and also in BCNF)without lost of data and preserving all the FDs. Obviously,we must define the attribute B-id of tables A and C, asforeign keys referencing table B, and accepting nulls.But as before, this data base schema is not representing theinclusion of the binary restrictions in the ternaryrelationship. So we must add restrictions to enforce that Ris not independent of the two binary FDs implied by thetables A and C. We could enforce: a)  π A-id,C-id R  ⊆  π A-id,C-id A*C b)  π A-id A ⊆ π A-id R  iff A.B-id is not null     π C-id C ⊆ π C-id R  iff C.B-id is not nullThe constrain a) is more complex than an inclusiondependency: it involves a natural join operation. It couldbe expressed in SQL92, for example, using the followingrestriction in table R:CHECK  ((A-id, C-id)   MATCH      (SELECT A-id, C-id  FROM  (A NATURAL JOIN C)))The two constraints b) could be expressed as an assertion:CHECK  (  NOT EXISTS   (   (SELECT A-id  FROM A WHERE B-id IS NOT NULL)                 EXCEPT (SELECT A-id  FROM R)  )    AND     NOT EXISTS    ((SELECT C-id  FROM C WHERE B-id IS NOT NULL)                EXCEPT (SELECT C-id  FROM R)  )  )A third possible transformation for our case is to takeout the foreign key B-id from table A (or,symmetrically, table C). Now the schema will be:A (A-id, a)           B (B-id, b)C (C-id, B-id, c)      R (A-id, C-id,  r)We must declare A-id and C-id of table R as foreignkeys of the corresponding tables, and B-id of the Ctable as foreign key accepting nulls.   But now the FDA-id ➔ B-id  is not enforced.  We can enforce it by:CHECK    (UNIQUE   (SELECT A-id  FROM        (SELECT DISTINCT A-id, B-id  FROM                                (R NATURAL JOIN C ))))Because R is ternary, the instances of C associated toinstances of A must be exactly the same ones that areassociated to instances of B. That can be enforced by anSQL check for equality (remember that C-id from tableR is already defined as foreign key of C):CHECK   ((SELECT COUNT(*) FROM  C                             WHERE B-id  IS NOT NULL) =   (SELECT COUNT(DISTINCT C-id)  FROM R                           WHERE  C-id NOT IN     (SELECT  C-id  FROM  C WHERE B-id IS NULL)))A fourth, and last, alternative transformation for ourternary case, consist of having B-id only in table R.Now we have no intertable redundancy but table R isnot in 3NF.  A-id, B-id and C-id in table R must bedefined as foreign keys and not null. But we mustenforce A-id➔ B-id   and  C-id➔ B-id in table R. So,we can write:CHECK  ((UNIQUE   (SELECT A-id  FROM                (SELECT DISTINCT A-id, B-id  FROM R)))   AND     (UNIQUE   (SELECT A-id  FROM                (SELECT DISTINCT A-id, B-id  FROM R))))As we have seen , the non-3NF tableR (A-id, C-id,  B-id , r)with the additional FDs A-id➔ B-id   and  C-id➔ B-id,cannot be decomposed in a fully equivalent set of 3NFtables if we use only candidate keys and inclusiondependencies.5. AGAINST A COMMON BELIEFThe ER-to-RM literature ignores some interesting FDpatterns. The case we have presented here is one ofthese cases. It looks very simple; apparently a harmlessFD pattern. But it has an interesting behavior that havemake us remember of Loizou[8] when he says that "thecentral idea in relational databases design is that all theintegrity constraints in the database should be describablein terms of keys and foreign keys" although we know it'snot always possible. It is a very common belief that givena set of FDs over a table resulting in a non-3NF situation,it is always possible to obtain a fully equivalent set of 3NFtables, without adding other restrictions than candidatekeys and inclusion dependencies (key or non-key based).But as we have seen in our case, it is not actually true.These restrictions are not powerful enough. If a correctdatabase state is to be guaranteed more complexrestrictions have to be used.6. REFERENCES[1]  C.Batini, S.Ceri and S.B.Navathe, Database Design: AnEntity-Relationship Approach, Prentice-Hall, (1991).[2]  T.Bruce, Designing Quality Databases with IDEF1XInformation Models, Dorset House Publishing, (1992).[3]  R.Camps,  "Transforming N-ary Relationships to DatabaseSchemas: An Old and Forgotten Problem",  Paper submited toER-2002 Conference.[4] P.P.S.Chen, "The entity relationship model: toward a unifiedview of data", ACM-Transactions on Database Systems, 1(1) 9-36 (1976).[5]  R.Elmasri and S.B.Navathe, Fundamentals of DatabaseSystems, 3th ed., Addison-Wesley (1999).[6]  T.Jones and I.Y.Song, "Analysis of binary/ternarycardinality combinations in entity-relationship modeling", Data& Knowledge Engineering, 19 (1), 39-64 (1996).[7]  T.Jones and I.Y.Song, "Binary Equivalents of TernaryRelationships in Entity-Relationship Modeling: a LogicalDecomposition Approach", Journal of Database Management,April-June 2000,  12-19 (2000).[8]  G.Loizou and M.Levene, A Guided Tour of RelationalDatabases and Beyond, Springer-Verlag (1999).[9]  A.J.McAllister and D.Sharpe, "An Approach forDecomposing N-ary Data Relationships" Software Practice &Experience, 28(2) , 125-154 (1998).[10]  A. Rochfeld and H. Tardieu, "MERISE: An informationsystem design and development methodology", Information &Management, 6, 143-159 (1983).[11]  J.Rumbaugh, I.Jacobson and G. Booch, The UnifiedModeling Language Reference Manual, Addison-Wesley (1999).[12]  T.J.Teorey, Database Modeling & Design, 3th ed., MorganKaufmann (1998).[13]  J.D.Ullman and J.Widom, A First Course in DatabaseSystems, Prentice-Hall (1997).[14]  Yourdon,Inc., Yourdon Method, Prentice-Hall (1993).

From ternary relationship to relational tables: a case against common beliefs

The transformation from n-ary relationships to a relational database

schema has never been really fully analyzed. This paper presents one of

the several ternary cases ignored by the ER-to-RM literature. The case

shows that the following common belief is wrong: Given a set of FDs over

a table resulting in a non-3NF situation, it is always possible to

obtain a fully equivalent set of 3NF tables, without adding other

restrictions than candidate keys and inclusion dependencies

UPCommons

https://upcommons.upc.edu/bitstream/2117/97609/2/R02-2.pdf

From ternary relationship to relational tables: a case against common beliefs

Abstract

Similar works

Full text

Available Versions

UPCommons. Portal del coneixement obert de la UPC

UPCommons