Improving Conceptual Modelling in Database Design

Abstract

Conceptual Modeling is one of the most important stages of the database (DB) design methodology. A number of approaches for conceptual modeling have been devised in the literature amongst which the Entity-Relationship (ER) modeling technique is extensively used. Since the quality of a conceptual model impacts the quality of the end product, our research focuses on how the quality of an ER model can be improved. We have identified modeling problems in the existing ER modeling technique and have suggested an approach which solves these problems. The result is an improved ER model which closely represents the real-world problem thereby improving the semantic representation. Our proposed approach incorporates real-world constraints that can be described in the form of functional dependencies. This approach applies schema transformations iteratively for which a new set of rules has also been defined. New constructs namely single-valued relationship attribute and multi-valued relationship attribute have also been proposed for improving semantics of the relationship types in an ER model. The impact of the proposed approach on later stages of the database design methodology has also been studied which shows that the resulting relational database satisfies higher normal forms as compared to the existing technique. Quantitative aspect of measuring improvement in the quality of a conceptual model is also an integral part of the research. For this purpose, we have proposed new metrics called completeness index, normalization index, and overall quality index. Completeness index is further refined by applying fuzzy logic and thus a fuzzy completeness index is proposed. We have also defined quantitative metrics for the structural complexity of an ER model in terms of correctness and modifiability. These metrics help us compare the quality of two ER models quantitatively and objectively. We have shown with several examples the efficacy of our approach and proposed metrics. The ultimate result is a better database design and improved database designers’ productivity.