Dealing with Relationship Cardinality Constraints in Relational Database Design

Conceptual models are well-known tools to achieve a good design of information systems. Nevertheless, the understanding and use of all the constructs and constraints which are presented in such models are not an easy task and sometimes it is cause of loss of interest. In this chapter we have tried to study in depth and clarify the meaning of the features of conceptual models. The disagreements between main conceptual models, the confusion in the use of some of their constructs and some open problems in these models are shown. Another important topic treated in this chapter is the conceptual-to-logic schemata transformation process. 701 E. Chocolate Avenue, Hershey PA 17033-1117, USA Tel: 717/533-8845; Fax 717/533-8661; URL-http://www.irm-press.com IRM PRESS This chapter appears in the book, Effective Databases for Text & Document Management by Shirley A. Becker. Copyright © 2003, IRM Press, an imprint of Idea Group Inc. Copying or distributing in print or electronic forms without written permission of Idea Group Inc. is prohibited. Dealing with Relationship Cardinality Constraints 289 Copyright © 2003, Idea Group Inc. Copying or distributing in print or electronic forms without written permission of Idea Group Inc. is prohibited. Some solutions are presented in order to clarify the relationship construct and to extend the cardinality constraint concept in ternary relationships. How to preserve the cardinality constraint semantics in binary and ternary relationships for their implementation in a DBMS with active capabilities has also been developed. INTRODUCTION Database modeling is a complex task that involves conceiving, understanding, structuring and describing real Universes of Discourse (UD) through the definition of schemata using abstraction processes and data models. Traditionally, three phases are identified in database design: conceptual, logical and physical design. The conceptual modeling phase represents the most abstract level since it is independent of any Database Management System (DBMS) and, consequently, it is very close to the user and allows him to collect almost completely the semantics of the real world to be modeled. A conceptual schema, independently of the data formalism used, plays two main roles in the conceptual design phase: a semantic role, in which user requirements are gathered together and the entities and relationships in a UD are documented, and a representational role that provides a framework that allows a mapping to the logical design of database development. Three topics are involved in the database conceptual modeling process: data modeling formalism, methodological approach and CASE tool support. One of the most extended data modeling formalisms, the Extended Entity Relationship (EER) model has proven to be a precise and comprehensive tool for representing data requirements in information systems development, mainly due to an adequate degree of abstraction of the constructs that it includes. Although the original ER model was proposed by Chen (1976), many extensions and variations as well as different diagrammatic styles have been defined (Hull & King, 1987; McAllister, 1998; Peckhan & Maryanski, 1988). In database conceptual analysis, among the most difficult concepts to be modeled are relationships, especially higher-order relationships, as well as their associated cardinalities. Some textbooks (Boman et al., 1997; Ullman & Widom, 1997) assume that any conceptual design can be addressed by considering only binary relationships since its aim is to create a computer-oriented model. We understand the advantages of this approach although we believe that it may produce certain loss of semantics (some biases are introduced in user requirements) and it forces one to represent information in rather artificial and sometimes unnatural ways. Concerning the logical design, the transformation process of conceptual schemata into relational schemata should be performed trying to completely preserve the semantics included in the conceptual schema; the final objective is to keep the semantics in the database itself and not in the applications accessing the database. Nevertheless, sometimes a certain loss of semantics is produced, for instance, foreign key and not null options in the relational model are not enough to control ER cardinality constraints. This chapter is devoted to the study of the transformation of conceptual into logical schemata in a methodological framework focusing on a special ER construct: the relationship and its associated cardinality constraints. The section entitled “EER Model Revisited: Relationships and Cardinality Constraint” reviews the relationship and cardinality constraint constructs through different methodological approaches in order 28 more pages are available in the full version of this document, which may be purchased using the "Add to Cart" button on the publisher's webpage: www.igi-global.com/chapter/dealing-relationship-cardinalityconstraints-relational/9217