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

ABSTRACT

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 elementary inclusion dependencies.

REFERENCES

• 1: Carlo Batini , Stefano Ceri , Shamkant B. Navathe, Conceptual database design: an Entity-relationship approach, Benjamin-Cummings Publishing Co., Inc., Redwood City, CA, 1991
• 2: Thomas A. Bruce, Designing quality databases with IDEF1X information models, Dorset House Publishing Co., Inc., New York, NY, 1992
• 3: R. Camps, "Transforming N-ary Relationships to Database Schemas: An Old and Forgotten Problem", Research Repot LSI-5-02R of Universitat Politècnica de Catalunya (Spain).
• 4: Peter Pin-Shan Chen, The entity-relationship model—toward a unified view of data, ACM Transactions on Database Systems (TODS), v.1 n.1, p.9-36, March 1976 [doi>10.1145/320434.320440]
• 5: Ramez A. Elmasri , Shankrant B. Navathe , Carter Shanklin, Fundamentals of Database Systems, Addison-Wesley Longman Publishing Co., Inc., Boston, MA, 1999
• 6: Trevor H. Jones , Il-Yeol Song, Analysis of binary/ternary cardinality combinations in entity-relationship modeling, Data & Knowledge Engineering, v.19 n.1, p.39-64, May 1996 [doi>10.1016/0169-023X(95)00036-R]
• 7: Trevor H. Jones , Il-Yeol Song, Binary equivalents of ternary relationships in entity-relationship modeling: a logical decomposition approach, Journal of Database Management, v.11 n.2, p.12-19, April-June 2000
• 8: Mark Levene , M. Levene , George Loizou, A Guided Tour of Relational Databases and Beyond, Springer-Verlag, London, 1999
• 9: Andrew J. McAllister , David Sharpe, An approach for decomposing N-ary data relationships, Software—Practice & Experience, v.28 n.2, p.125-154, Feb. 1998 [doi>10.1002/(SICI)1097-024X(199802)28:2<125::AID-SPE146>3.3.CO;2-Y]
• 10: A. Rochfeld and H. Tardieu, "MERISE: An information system design and development methodology", Information & Management, 6, 143-159 (1983).
• 11: James Rumbaugh , Ivar Jacobson , Grady Booch, The Unified Modeling Language reference manual, Addison-Wesley Longman Ltd., Essex, UK, 1998
• 12: Toby J. Teorey, Database modeling and design (3rd ed.), Morgan Kaufmann Publishers Inc., San Francisco, CA, 1998
• 13: Jeffrey D. Ullman , Jennifer Widom, A first course in database systems, Prentice-Hall, Inc., Upper Saddle River, NJ, 1997
• 14: Yourdon,Inc., Yourdon Method, Prentice-Hall (1993).