Transforming N-ary relationships to database schemas: an old and forgotten problem

The N-ary relationships, have been traditionally a source of confusion and still are. One important source of confusion is that the term cardinality in a relationship has several interpretations, two of them being very popular. But none of the two approaches, nor the two together, allow us to express all the possible cardinality patterns. The transformations from all the possible relationships to database schemas have never been described by the existing literature. Using the 14 ternary patterns as example, we discuss these transformations particularly the transformations from the patterns ignored in the literature.Postprint (published version

Justification for inclusion dependency normal form

Functional dependencies (FDs) and inclusion dependencies (INDs) are the most fundamental integrity constraints that arise in practice in relational databases. In this paper, we address the issue of normalization in the presence of FDs and INDs and, in particular, the semantic justification for Inclusion Dependency Normal Form (IDNF), a normal form which combines Boyce-Codd normal form with the restriction on the INDs that they be noncircular and key-based. We motivate and formalize three goals of database design in the presence of FDs and INDs: noninteraction between FDs and INDs, elimination of redundancy and update anomalies, and preservation of entity integrity. We show that, as for FDs, in the presence of INDs being free of redundancy is equivalent to being free of update anomalies. Then, for each of these properties, we derive equivalent syntactic conditions on the database design. Individually, each of these syntactic conditions is weaker than IDNF and the restriction that an FD not be embedded in the righthand side of an IND is common to three of the conditions. However, we also show that, for these three goals of database design to be satisfied simultaneously, IDNF is both a necessary and sufficient condition

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

Normalizing Relations: One-to-One Relationships

This paper discusses problems and confusion that can arise in normalizing certain relations, using the commonly accepted practices of identifying primary keys and defining normal forms. Specifically, normalization of relations that combines information on two different entities with a one-to-one relationship is discussed. It is shown that for such relations, using the standard normalization procedures may lead to the conclusion that a relation in Boyce-Codd normal form (BCNF) still suffers from insertion and deletion anomalies. Methods to deal with such cases are presented

A Logical Design Methodology for Relational Databases Using the Extended ER Model

https://deepblue.lib.umich.edu/bitstream/2027.42/154152/1/39015099114723.pd

A Comparative Analysis To Validate The Benefits Of Formal Versus Informal Software Model Transformation

In object -oriented development the Unified Modeling Language (UML) is the ISO/IEC standard for modeling language and is supported by major corporations. In relational database development, entity-relationship models have traditionally been use for modeling such systems. Transforming from one notation to another notation is of great importance in developmental environments where this is required. There are several techniques for transforming UML models to object-relational database systems. Prior assessment has been carried out on transforming UML class diagram models to object-oriented relational databases, which yield significant results. One approach to transformation may involve the use of formal (mathematical) techniques, while other approaches may rely on informal techniques to accomplish the transformation. The use of a formal technique to transform may incorporate graph-theory on UML class diagram. An informal technique may be utilized in transforming UML extension mechanisms, to represent object-relational concepts. A prior research effort examined the benefits of two such approaches in transforming UML class diagram models into object-relational database representation. That work, sought to determine the benefits of one approach (formal technique) versus the benefits of the other approach (informal technique), by way of comparative analysis. The researchers drew inferences from the comparative analysis as to the suitability of one approach versus the other on classes of problem domains. The results of such work have to be validated in order for it to become acceptable and its implication applied in software development decision making. In this work there will be an attempt to apply a similar xiv comparative analysis on a model from a different application domain (Kalman Filter Program Representation), from that which was used in the first study an (Airline Flight Reservation System). The goal of this research is to provide validation of the usefulness of this type of comparative analysis

Environmental information systems : the development and implementation of the Lake Rukwa Basin integrated project environmental information system (LRBIP-EIS) database, Tanzania

Bibliography: leaves 91-97.The quest for sustenance inevitably forces mankind to exploit natural resources found within their environs. In many cases, the exploitation results in massive environmental degradation that disrupts the ecosystem and causes loss of bio-diversity. There is generally a lack of information systems to monitor and provide quantitative information on the state of the affected environment. Decision-makers usually fail to make informed decisions with regard to conservation strategies. The need to provide decision-makers with quantitative environmental information formed the basis of this thesis. An integrated environmental information system (EIS) database was developed according to the Software Development Methodology for three of the identified environmental sectors. This involved detailed user needs assessment to identify the information requirements (both spatial and textual) for each sector. The results were used to design separate data models that were later merged to create an integrated data model for the database application. A fisheries application prototype was developed to implement the proposed database design. The prototype has three major components. The Geographic Information System (GIS) handles the spatial data such as rivers, settlements, roads, and lakes. A relational database management system (RDBMS) was used to store and maintain the non-spatial data such as fisherman ' s personal details and fish catch data. Customized graphical user interfaces were designed to handle the data visualization and restricted access to the GIS and RDBMS environments

XML documents schema design

The eXtensible Markup Language (XML) is fast emerging as the dominant standard for storing, describing and interchanging data among various systems and databases on the intemet. It offers schema such as Document Type Definition (DTD) or XML Schema Definition (XSD) for defining the syntax and structure of XML documents. To enable efficient usage of XML documents in any application in large scale electronic environment, it is necessary to avoid data redundancies and update anomalies. Redundancy and anomalies in XML documents can lead not only to higher data storage cost but also to increased costs for data transfer and data manipulation.To overcome this problem, this thesis proposes to establish a formal framework of XML document schema design. To achieve this aim, we propose a method to improve and simplify XML schema design by incorporating a conceptual model of the DTD with a theory of database normalization. A conceptual diagram, Graph-Document Type Definition (G-DTD) is proposed to describe the structure of XML documents at the schema level. For G- DTD itself, we define a structure which incorporates attributes, simple elements, complex elements, and relationship types among them. Furthermore, semantic constraints are also precisely defined in order to capture semantic meanings among the defined XML objects.In addition, to provide a guideline to a well-designed schema for XML documents, we propose a set of normal forms for G-DTD on the basis of rules proposed by Arenas and Libkin and Lv. et al. The corresponding normalization rules to transform from a G- DTD into a normal form schema are also discussed. A case study is given to illustrate the applicability of the concept. As a result, we found that the new normal forms are more concise and practical, in particular as they allow the user to find an 'optimal' structure of XML elements/attributes at the schema level. To prove that our approach is applicable for the database designer, we develop a prototype of XML document schema design using a Z formal specification language. Finally, using the same case study, this formal specification is tested to check for correctness and consistency of the specification. Thus, this gives a confidence that our prototype can be implemented successfully to generate an automatic XML schema design

Database Design and Implementation

The book of Database Design and Implementation is a comprehensive guide that provides a thorough introduction to the principles, concepts, and best practices of database design and implementation. It covers the essential topics required to design, develop, and manage a database system, including data modeling, database normalization, SQL programming, and database administration. The book is designed for students, database administrators, software developers, and anyone interested in learning how to design and implement a database system. It provides a step-by-step approach to database design and implementation, with clear explanations and practical examples. It also includes exercises and quizzes at the end of each chapter to help reinforce the concepts covered. The book begins by introducing the fundamental concepts of database systems and data modeling. It then discusses the process of database design and normalization, which is essential for creating a well-structured and efficient database system. The book also covers SQL programming, which is used for querying, updating, and managing data in a database. Additionally, it includes a comprehensive discussion on database administration, including security, backup and recovery, and performance tuning.https://orc.library.atu.edu/atu_oer/1002/thumbnail.jp