Issues and Guidelines in Modeling Decomposition of Minimum Participation in Entity-Relationship Diagrams

The entity-relationship model has long been employed for conceptual modeling of databases. Methodologies and heuristics have been developed, both for effective modeling and for translating entity-relationship models into relational models. One aspect of modeling that is often overlooked in design methodologies is the use of optional versus mandatory participation (i.e., minimum participation) on the development of relational databases. This tutorial complements existing instructional material on database design by analyzing the syntactic implications of minimum participation in binary, unary, and n-ary relationship sets and for the special case where the E-R diagram depicts a database where 3NF is not in BCNF. It then presents design modeling guidelines which demonstrate that (1) for binary 1:1 and 1:M relationship sets, the presence of optional participation sometimes means that the relationship set should be represented in the relational model by a separate relation, (2) unary relationship sets cannot have a (1,1) participation, (3) n-ary relationship sets that have a (1,1) participation can be simplified to be of lower connectivity, and (4) decomposition is not a substitute for normalization. Illustrative examples and modeling guidelines are provided